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

Sorting the query output

To make the output easier to decipher, you could use the ORDER function to sort the returned dimension members based on some criteria. In addition, many reports require limiting the results only to the best- or worst-performing hierarchy members. You can exploit the TOPCOUNT and BOTTOMCOUNT functions to meet such requirements.

How to do it...

Let's get started with sorting the query output.

The ORDER function accepts a set as the parameter and allows sorting in an ascending or descending manner, depending on the sorting expression. For example, the following query returns those cities in which the reseller sales amount for components exceeded $200000, ordering results based on the reseller sales amount:
```
SELECT ORDER (
  FILTER([Geography].[City].members, [Measures].[Reseller Sales Amount] > 200000
  AND [Geography].[City].CurrentMember.Name<> 'All Geographies'),  
  [Measures].[Reseller Sales Amount], DESC)ON 0,
[Measures].[Reseller Sales Amount] ON 1
FROM ...
```

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

Sorting the query output

How to do it...