Microsoft SQL Server is a relational database management system. As a database, it is a software product whose primary function is to store and retrieve data as requested by other software applications. SQL Server Analysis Services adds OLAP and data mining capabilities for SQL Server databases. OLAP (online analytical processing) is a technique for analyzing business data for effective business intelligence.

This practical guide teaches you how to build business intelligence solutions using Microsofts core product SQL Server Analysis Services. The book covers the traditional multi-dimensional model which has been around for over a decade as well as the tabular model introduced with SQL Server 2012.

Starting with comparing MultiDimensional and tabular models discussing the values and limitations of each, you will then cover the essential techniques for building dimensions and cubes. Following on from this, you will be introduced to more advanced topics, such as designing partitions and aggregations, implementing security, and synchronizing databases for solutions serving many users.

The book also covers administrative material, such as database backups, server configuration options, and monitoring and tuning performance. We also provide a primer on MultiDimensional eXpressions (MDX) as well as Data Analysis expressions (DAX) languages.

This book provides you with data cube development techniques, and also the ongoing monitoring and tuning for Analysis Services.

Approach

A practical cookbook packed with recipes to help developers produce data cubes as quickly as possible by following step by step instructions, rather than explaining data mining concepts with SSAS.

Who this book is for

If you are a BI or ETL developer using SQL Server Analysis services to build OLAP cubes, this book is ideal for you. Prior knowledge of relational databases and experience with Excel as well as SQL development is required.

Frequently asked questions

Simply head over to the account section in settings and click on “Cancel Subscription” - it’s as simple as that. After you cancel, your membership will stay active for the remainder of the time you’ve paid for. Learn more here.

At the moment all of our mobile-responsive ePub books are available to download via the app. Most of our PDFs are also available to download and we're working on making the final remaining ones downloadable now. Learn more here.

Both plans give you full access to the library and all of Perlego’s features. The only differences are the price and subscription period: With the annual plan you’ll save around 30% compared to 12 months on the monthly plan.

We are an online textbook subscription service, where you can get access to an entire online library for less than the price of a single book per month. With over 1 million books across 1000+ topics, we’ve got you covered! Learn more here.

Look out for the read-aloud symbol on your next book to see if you can listen to it. The read-aloud tool reads text aloud for you, highlighting the text as it is being read. You can pause it, speed it up and slow it down. Learn more here.

Yes, you can access SQL Server Analysis Services 2012 Cube Development Cookbook by Baya Dewald, Steve Hughes, Paul Turley in PDF and/or ePUB format, as well as other popular books in Informatica & Programmazione in SQL. We have over one million books available in our catalogue for you to explore.

Information

Publisher

Packt Publishing

Year

2013

ISBN

9781849689809

Edition

Topic

Informatica

Subtopic

Programmazione in SQL

SQL Server Analysis Services 2012 Cube Development Cookbook

Credits

About the Authors

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers and more

Why Subscribe?

Free Access for Packt account holders

Instant Updates on New Packt Books

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code

Errata

Piracy

Questions

1. Introduction to Multidimensional Data Model Design

Introduction

The business value of Business Intelligence

Challenges and barriers of effective BI

Overcoming BI challenges and barriers

Choosing multidimensional or Tabular Models

Star- or Snowflake-relational schema

A sample scenario for choosing the Snowflake schema

2. Defining Analysis Services Dimensions

Introduction

Defining data sources

How to do it...

How it works...

There's more...

Defining data source views

Getting started

How to do it...

Defining entity relationships in DSV

How to do it...

Extending data source views

How to do it...

Creating named calculations and queries

How to do it...

Creating simple dimensions

Getting ready

How to do it...

How it works...

Building dimension hierarchies

How to do it...

There's more

Setting dimension properties

Setting essential attribute properties

How to do it...

Browsing dimension data

Getting ready

How to do it...

How it works...

Sorting the attributes

How to do it...

Customizing advanced attribute properties

How to do it...

How it works...

Creating parent-child dimensions

How to do it...

How it works...

There's more...

Creating the date and time dimensions

How to do it...

There's more...

3. Creating Analysis Services Cubes

Introduction

Defining measure groups and measures

Getting ready

How to do it...

Setting measure properties

How to do it...

There's more...

Browsing the cube data

How to do it...

Dimension usage with measure group

How to do it...

Examining cube file structures

How to do it...

There's more...

Partitioning strategies

How to do it...

Creating partitions through the wizard

Creating partitions through AMO

Partition storage mode options

Defining partition slice

How to do it...

Merging partitions

How to do it...

Defining aggregation designs

Using BIDS Helper for customizing aggregations

How to do it...

There's more...

Distinct count measure groups

How to do it...

There's more...

Enabling write-back feature

How to do it...

Deployment options

How to do it...

4. Extending and Customizing Cubes

Introduction

Defining calculated measures

Getting ready

How to do it...

Defining named sets

How to do it...

Defining drillthrough actions

How to do it...

How it works...

Defining URL actions

How to do it...

Defining reporting actions

How to do it...

Defining key performance indicators

How to do it...

Defining perspectives

How to do it...

Defining translations

How to do it...

Defining measure expressions

How to do it...

5. Optimizing Dimension and Cube Processing

Introduction

Understanding dimension processing options

How it works...

Learning about basic dimension processing

Getting ready

How to do it...

How it works...

Learning advanced dimension processing options

How to do it...

Using out-of-line bindings for dimension processing

How to do it...

Dealing with partition processing options

How to do it...

How it works...

Using SQL Server Integration Services to process Analysis Services objects

How to do it...

Monitoring and tuning processing performance

How to do it...

6. MDX

Introduction

Returning data on the query axes

Getting ready

How to do it...

Limiting the query output

How to do it...

Sorting the query output

How to do it...

Defining query level calculations and named sets

How to do it...

Navigating dimension hierarchies

How to do it...

Working with the Time dimensions

How to do it...

MDX script's functionality

How to do it...

Monitoring and tuning MDX queries

How to do it...

There's more...

7. Analysis Services Security

Introduction

Managing instance-level administrative security

How to do it...

Managing database-level security

How to do it...

Managing cube-level security

How to do it...

Managing dimension hierarchy-level security

How to do it...

How it works...

Implementing dynamic dimension security

How to do it...

There's more...

Implementing cell-level security

How to do it...

There's more...

8. Administering and Monitoring Analysis Services

Introduction

SSAS instance configuration options

How to do it...

How it works...

There's more...

Creating and dropping databases

Getting ready

How to do it...

Monitoring SSAS instance using Activity Viewer

How to do it...

How it works...

Monitoring SSAS instance using DMVs

How to do it...

How it works...

There's more...

Cancelling a session

How to do it...

Checking whether cubes are accessible

How to do it...

Checking SSAS object sizes programmatically

How to do it...

There's more...

Scaling out SSAS solutions

Backup and restore

How to do it...

How it works...

Synchronizing databases

How to do it...

How it works...

Detaching and attaching databases

How to do it...

How it works...

9. Using Tabular Models

Introduction

Creating a Tabular Model

Getting ready

How to do it…

There's more…

Working with data sources and loading data

How to do it…

There's more…

Modeling the data

Getting ready

How to do it…

How it works…

There's more…

See also

Creating a hierarchy

How to do it…

There's more…

Creating a calculated measure

Getting ready

How to do it…

See also

Creating a calculated column

How to do it…

There's more…

See also

Creating a KPI

Getting ready

How to do it…

There's more…

See also

Analyzing your model in Excel

How to do it…

There's more…

See also

Deploying Tabular Models

Getting ready

How to do it…

How it works…

There's more…

See also

Scripting Tabu...