SQL Server Analysis Services 2012 Cube Development Cookbook

SQL Server Analysis Services 2012 Cube Development Cookbook

Overview of this book

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 Microsoft’s 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.

SQL Server Analysis Services 2012 Cube Development Cookbook

Credits

About the Authors

About the Reviewers

www.PacktPub.com

Preface

Free Chapter

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

Defining Analysis Services Dimensions

Introduction

Defining data sources

Defining data source views

Defining entity relationships in DSV

Extending data source views

Creating named calculations and queries

Creating simple dimensions

Building dimension hierarchies

Setting essential attribute properties

Browsing dimension data

Sorting the attributes

Customizing advanced attribute properties

Creating parent-child dimensions

Creating the date and time dimensions

Creating Analysis Services Cubes

Introduction

Defining measure groups and measures

Setting measure properties

Browsing the cube data

Dimension usage with measure group

Examining cube file structures

Partitioning strategies

Defining partition slice

Merging partitions

Defining aggregation designs

Distinct count measure groups

Enabling write-back feature

Deployment options

Extending and Customizing Cubes

Introduction

Defining calculated measures

Defining named sets

Defining drillthrough actions

Defining URL actions

Defining reporting actions

Defining key performance indicators

Defining perspectives

Defining translations

Defining measure expressions

Optimizing Dimension and Cube Processing

Introduction

Understanding dimension processing options

Learning about basic dimension processing

Learning advanced dimension processing options

Using out-of-line bindings for dimension processing

Dealing with partition processing options

Using SQL Server Integration Services to process Analysis Services objects

Monitoring and tuning processing performance

MDX

Introduction

Returning data on the query axes

Limiting the query output

Sorting the query output

Defining query level calculations and named sets

Navigating dimension hierarchies

Working with the Time dimensions

MDX script's functionality

Monitoring and tuning MDX queries

Analysis Services Security

Introduction

Managing instance-level administrative security

Managing database-level security

Managing cube-level security

Managing dimension hierarchy-level security

Implementing dynamic dimension security

Implementing cell-level security

Administering and Monitoring Analysis Services

Introduction

SSAS instance configuration options

Creating and dropping databases

Monitoring SSAS instance using Activity Viewer

Monitoring SSAS instance using DMVs

Cancelling a session

Checking whether cubes are accessible

Checking SSAS object sizes programmatically

Backup and restore

Synchronizing databases

Detaching and attaching databases

Using Tabular Models

Introduction

Creating a Tabular Model

Working with data sources and loading data

Modeling the data

Creating a hierarchy

Creating a calculated measure

Creating a calculated column

Creating a KPI

Analyzing your model in Excel

Deploying Tabular Models

Scripting Tabular Models using XMLA

Processing Tabular Models

Partitioning Tabular Models

Implementing perspectives

Implementing security in Tabular Models

Automating Tabular Model processing

DAX Calculations and Queries

Introduction

Combining tables using calculated columns

Adding a calculated column

Creating measures

Testing a Tabular Model in Excel

Using the CALCULATE function

Querying a Tabular Model

Performance Tuning and Troubleshooting Tabular Models

Introduction

Understanding usability limits

Optimizing and managing a model's design

Diagnosing performance issues

Using performance tools

Investigating query performance with SQL Server Profiler

Miscellaneous Analysis Services Topics

Working with non-SQL Server data sources

Common yet confusing SSAS errors

Dimension properties

Performance considerations for many-to-many dimension relationships

DirectQuery with Tabular Models

Index

Customer Reviews

5 star

4 star

3 star

2 star

1 star

DirectQuery with Tabular Models

DirectQuery is a deployment option available in Tabular Models, which allows the developer to use the relational data instead of the in-memory data in the model. This feature is still immature and needs to be understood to determine how this can be used.

Data sources

DirectQuery can only use SQL Server and SQL Server PDW databases as sources. No other relational or non-relational sources are supported including Oracle, SQL Server Analysis Services or files. In this case, using DirectQuery would be valuable if using a SQL Server data source and either real-time changes or large data volume are required in your solution.

Security

The role-based security in the Tabular Model is not supported with DirectQuery. The security within the data source, however, is supported.

Design limitations

Calculated columns are not supported in a table based on a DirectQuery source. While calculated measures and KPIs are supported, the DAX formula support is limited. It is possible to use derived columns in the source to overcome some of the calculation issues. Even these workarounds may not have the desired results as there are also cases where the calculations differ between the standard Tabular Model and DirectQuery model. This is caused because the SQL Server engine and the xVelocity engine can handle these calculations slightly differently.

Client restrictions

Only clients who work with DAX are able to interact with a model using DirectQuery. This eliminates many tools such as SQL Server Reporting Services and Excel Pivot Tables. They do work with Power View reports.

When choosing whether to use DirectQuery, you will need to evaluate the preceding limitations to determine if it is a good choice in your solution. In most cases, the limitations seem to outweigh the benefits.

SQL Server Analysis Services 2012 Cube Development Cookbook

SQL Server Analysis Services 2012 Cube Development Cookbook

Overview of this book

Related Content you might be interested in

Current Title:

SQL Server Analysis Services 2012 Cube Development Cookbook

DirectQuery with Tabular Models

Data sources

Security

Design limitations

Client restrictions