PacktLib: SQL Server Analysis Services 2012 Cube Development Cookbook

SQL Server Analysis Services 2012 Cube Development Cookbook

Credits

About the Authors

About the Reviewers

www.PacktPub.com

Preface

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

Miscellaneous Analysis Services Topics

Miscellaneous Analysis Services Topics

Miscellaneous Analysis Services Topics

Miscellaneous Analysis Services Topics

Miscellaneous Analysis Services Topics

Index