MDX with SSAS 2012 Cookbook

MDX with SSAS 2012 Cookbook - Second Edition

Overview of this book

MDX is the BI industry standard for multidimensional calculations and queries. Proficiency with this language is essential for the realization of your Analysis Services' full potential. MDX is an elegant and powerful language, and also has a steep learning curve.SQL Server 2012 Analysis Services has introduced a new BISM tabular model and a new formula language, Data Analysis Expressions (DAX). However, for the multi-dimensional model, MDX is still the only query and expression language. For many product developers and report developers, MDX is the preferred language for both the tabular model and multi-dimensional model. MDX with SSAS 2012 Cookbook is a must-have book for anyone who wants to be proficient in the MDX language and to enhance their business intelligence solutions.MDX with SSAS 2012 Cookbook is packed with immediately usable, practical solutions. It starts with elementary techniques that lay the foundation for designing advanced MDX calculations and queries. The discussions after each solution will provide you with a solid foundation and best practices. It covers a broad range of real-world topics and solutions and provides you with learning materials to become proficient in the language.This book will guide you through the hands-on and practical MDX solutions, best practices, and many intricacies that hide within the MDX calculations and queries. We will start by working with sets, creating time-aware, context-aware calculations, and business analytics solutions, through to the techniques of enhancing the cube design when MDX is not enough. We will then move on to capturing MDX generated by SSAS front-ends and using SSAS stored procedures, and we will explore the whole range of MDX solutions for real-world BI projects.

MDX with SSAS 2012 Cookbook

Credits

About the Authors

About the Reviewers

www.PacktPub.com

Preface

Free Chapter

Elementary MDX Techniques

Introduction

Putting data on x and y axes

Skipping axes

Using a WHERE clause to filter the data returned

Optimizing MDX queries using the NonEmpty() function

Using the PROPERTIES() function to retrieve data from attribute relationships

Basic sorting and ranking

Handling division by zero errors

Setting a default member of a hierarchy in the MDX script

Working with Sets

Introduction

Implementing the NOT IN set logic

Implementing the logical OR on members from different hierarchies

Implementing the logical AND on members from the same hierarchy

Iterating on a set in order to reduce it

Iterating on a set in order to create a new one

Iterating on a set using recursion

Dissecting and debugging MDX queries

Working with Time

Introduction

Calculating the YTD (Year-To-Date) value

Calculating the YoY (Year-over-Year) growth (parallel periods)

Calculating moving averages

Finding the last date with data

Getting values on the last date with data

Calculating today's date using the string functions

Calculating today's date using the MemberValue function

Calculating today's date using an attribute hierarchy

Calculating the difference between two dates

Calculating the difference between two times

Calculating parallel periods for multiple dates in a set

Calculating parallel periods for multiple dates in a slicer

Concise Reporting

Introduction

Isolating the best N members in a set

Isolating the worst N members in a set

Identifying the best/worst members for each member of another hierarchy

Displaying few important members, others as a single row, and the total at the end

Combining two hierarchies into one

Finding the name of a child with the best/worst value

Highlighting siblings with the best/worst values

Implementing bubble-up exceptions

Navigation

Introduction

Detecting a particular member in a hierarchy

Detecting the root member

Detecting members on the same branch

Calculating various percentages

Calculating various averages

Calculating various ranks

Business Analytics

Introduction

Forecasting using the linear regression

Forecasting using the periodic cycles

Allocating the nonallocated company expenses to departments

Analyzing fluctuation of customers

Implementing the ABC analysis

When MDX is Not Enough

Introduction

Using a new attribute to separate members on a level

Using a distinct count measure to implement histograms over existing hierarchies

Using a dummy dimension to implement histograms over nonexisting hierarchies

Creating a physical measure as a placeholder for MDX assignments

Using a new dimension to calculate the most frequent price

Using a utility dimension to implement flexible display units

Using a utility dimension to implement time-based calculations

Advanced MDX Topics

Introduction

Displaying members without children (leaves)

Displaying members with data in parent-child hierarchies

Displaying random values

Displaying a random sample of hierarchy members

Displaying a sample from a random hierarchy

Performing complex sorts

Using recursion to calculate cumulative values

On the Edge

Introduction

Clearing the Analysis Services cache

Using Analysis Services stored procedures

Executing MDX queries in T-SQL environments

Using SSAS Dynamic Management Views (DMV) to fast-document a cube

Using SSAS Dynamic Management View (DMVs) to monitor activity and usage

Capturing MDX queries generated by SSAS frontends

Performing a custom drillthrough

Index

Customer Reviews

5 star

4 star

3 star

2 star

1 star

Basic sorting and ranking

Sorting and ranking are very common requirements in most business analysis, and MDX provides several functions for this purpose. They are:

TopCount and BottomCount
TopPercent and BottomPercent
TopSum and BottomSum
Order
Hierarchize
Rank

All of these functions operate on sets of tuples, not just on one-dimensional sets of members. They all, in some way, involve a numeric expression, which is used to evaluate the sorting and the ranking.

Getting ready

We will start with the classic Top 5 (or Top-n) example using the TopCount() function. We will then examine how the result is already pre-sorted, followed by using the Order() function to sort the result explicitly. Finally, we will see how we can add a ranking number by using the Rank() function.

Here is the classic Top 5 example using the TopCount() function

TopCount (
        [Product].[Subcategory].children,
        5,
        [Measures].[Internet Sales Amount] 
  )

It operates on a tuple ([Product].[Subcategory].children, [Measures].[Internet Sales Amount]).

The result is the five [Subcategory] that has the highest [Internet Sales Amount].

The five subcategory members will be returned in order from the largest [Internet Sales Amount] to the smallest.

How to do it…

In SSMS, let us write the following query in a new Query Editor, against the Adventure Works DW 2012 database. Follow these steps to first get the top-n members:

We simply place the earlier TopCount() expression on the rows axis.
On the columns axis, we are showing the actually sales amount for each product subcategory.
In the slicer, we use a tuple to slice the result for the year 2008 and the Southwest only.

The final query should look like the following query:

SELECT
    [Measures].[Internet Sales Amount] on 0,
    TopCount (
        [Product].[Subcategory].children,
        5,
        [Measures].[Internet Sales Amount] 
    ) ON 1
FROM
   [Adventure Works]
WHERE
   ( [Date].[Calendar].[Calendar Quarter].&[2008]&[1],
     [Sales Territory].[Sales Territory Region].[Southwest]
   )

Run the query. The following screenshot shows the Top-n result:
Notice that the returned members are in order from the largest numeric measure to the smallest.

Next, in SSMS, follow these steps to explicitly sort the result:

This time, we will put the TopCount() expression in the WITH clause, creating it as a Named Set. We will name it [Top 5 Subcategory].
On the rows axis, we will use the Order() function, which takes two parameters: which members we want to return and what value we want to evaluate on for sorting. The named set [Top 5 Subcategory] is what we want to return, so we will pass it to the Order() function as the first parameter. The .MemberValue function gives us the product subcategory name, so we will pass it to the Order() function as the second parameter. Here is the Order() function expression we would use:
```
ORDER (
         [Top 5 Subcategory],
         [Product].[Subcategory].MEMBERVALUE
    )
```

Here is the final query for sorting the result:

-- Order members with MemberValue
WITH
SET [Top 5 Subcategory] as
   TopCount (
       [Product].[Subcategory].CHILDREN,
       5,
       [Measures].[Internet Sales Amount]
   )

SELECT
    [Measures].[Internet Sales Amount] on 0,
    ORDER (
        [Top 5 Subcategory],
        [Product].[Subcategory].MEMBERVALUE
    ) ON 1
FROM
    [Adventure Works]
WHERE
    ( [Date].[Calendar].[Calendar Quarter].&[2008]&[1],
      [Sales Territory].[Sales Territory 
        Region].[Southwest] )

Executing the preceding query, we get the sorted result as the screenshot shows:

Finally, in SSMS follow these steps to add ranking numbers to the Top-n result:

We will create a new calculated measure [Subcategory Rank] using the Rank() function, which is simply putting a one-based ordinal position of each tuple in the set [Top 5 Subcategory]. Since the set is already ordered, the ordinal position of the tuple will give us the correct ranking. Here is the expression for the Rank() function:
```
   RANK (
       [Product].[Subcategory].CurrentMember,
       [Top 5 Subcategory]
   )
```

The following query is the final query. It is built on top of the first query in this recipe. We've added the earlier Rank() function and created a calculated measure [Measures].[Subcategory Rank], which is placed on the columns axis along with the Internet Sales Amount.

WITH
SET [Top 5 Subcategory] AS
   TopCount (
       [Product].[Subcategory].children,
       5,
       [Measures].[Internet Sales Amount] 
   )
MEMBER [Measures].[Subcategory Rank] AS
    RANK ( 
       [Product].[Subcategory].CurrentMember, 
       [Top 5 Subcategory]
   )

SELECT
    { [Measures].[Internet Sales Amount],
      [Measures].[Subcategory Rank]
    } ON 0,
    [Top 5 Subcategory] ON 1	
FROM
    [Adventure Works]
WHERE
    ( [Date].[Calendar].[Calendar Quarter].&[2008]&[1],
      [Sales Territory].[Sales Territory Region].[Southwest] )

Run the preceding query. The ranking result is shown in the following screenshot:

How it works…

Sorting functions, such as TopCount(), TopPercent(), and TopSum() operate on sets of tuples. These tuples are evaluated on a numeric expression and returned pre-sorted in the order of a numeric expression.

Using the Order() function, we can sort members from dimensions explicitly using the .MemberValue function.

When a numeric expression is not specified, the Rank() function can simply be used to display the one-based ordinal position of tuples in a set.

There's more…

Like the other MDX sorting functions, the Rank() function, however, can also operate on a numeric expression. If a numeric expression is specified, the Rank() function assigns the same rank to tuples with duplicate values in the set.

It is also important to understand that the Rank() function does not order the set. Because of this fact, we tend to do the ordering and ranking at the same time. However, in the last query of this recipe, we actually used the Order() function to first order the set of members of the subcategory. This way, the sorting is done only once and then followed by a linear scan, before being presented in sorted order.

As a good practice, we recommend using the Order() function to first order the set and then ranking the tuples that are already sorted.

MDX with SSAS 2012 Cookbook - Second Edition

MDX with SSAS 2012 Cookbook - Second Edition

Overview of this book

Related Content you might be interested in

Current Title:

MDX with SSAS 2012 Cookbook - Second Edition

Basic sorting and ranking

Getting ready

How to do it…

How it works…

There's more…