MDX with SSAS 2012 Cookbook: In this book you'll find 90 clearly written recipes to help developers advance their skills with the demanding but powerful language MDX and SQL Server Analysis Services. All leading to greatly improved business intelligence solutions. , Second Edition

Making a two by eight table below in a spreadsheet is quite simple. Writing a MDX query to do that can also be very simple. Putting data on the x and y axes is a matter of finding the right expressions for each axis.

All we need are three things from our cube:

Once, we have the preceding three things, we are ready to plug them into the following MDX query, and the cube will give us back the two by eight table:

SELECT
   [The Sales Expression] ON COLUMNS,
   [The Territory Expression] ON ROWS
FROM
   [The Cube Name]

The MDX engine will understand it perfectly, if we replace columns by 0 and rows by 1. Throughout this book, we will use the number 0 for columns that is the x axis, and 1 for rows that is the y axis.

We are going to use the Adventure Works 2012 Multidimensional Analysis Service database enterprise edition in our cookbook. If you open the Adventure Works cube, and hover your cursor over the measure Internet Sales Amount, you will see the fully qualified expression, [Measures].[Internet Sales Amount]. This is a long expression. Drag-and-drop in SQL Server Management Studio works perfectly for us in this situation.

Follow these two steps to open the Query Editor in SSMS:

Follow these steps to save the time spent for typing the long expressions:

This should be your final query:

SELECT
   [Measures].[Internet Sales Amount] ON 0,
   [Sales Territory].[Sales Territory Country].[Sales Territory Country] ON 1
FROM
   [Adventure Works]

When you execute the query, you should get a two by eight table, same as the following screenshot:

We have chosen to put Internet Sales Amount on the Axis(0), and all members of Sales Territory Country on the Axis(1). We have fully qualified the measure with the special dimension [Measures], and the sales territory members with dimension [Sales Territory] and hierarchy [Sales Territory Country].

You might have expected an aggregate function such as SUM somewhere in the query. We do not need to have any aggregate function here because the cube understands that when we ask for the sales amount for Canada, we would expect the sales amount to come from all the provinces and territories in Canada.

SSAS cubes are perfectly capable of storing data in more than two dimensions. In MDX, we can use the technique called "cross join" to "combine" multiple hierarchies into one query axis.

Putting more hierarchies on x and y axes with cross join

In MDX query, we can specify how multi-dimensions from our SSAS cube layout onto only two x and y axes. Cross joining allows us in both SQL and MDX to get every possible combination of two lists.

We wish to write an MDX query to produce the following table. On the columns axis, we want to see both Internet Sales Amount and Internet Gross Profit. On the rows axis, we want to see all the sales territory countries, and all the products sold in each country.

		Internet Sales Amount	Internet Gross Profit
Australia	Accessories	$138,690.63	$86,820.10
Australia	Bikes	$8,852,050.00	$3,572,267.29
Australia	Clothing	$70,259.95	$26,767.68
Australia	Components	(null)	(null)
Canada	Accessories	$103,377.85	$64,714.37
Canada	Bikes	$1,821,302.39	$741,451.22
Canada	Clothing	$53,164.62	$23,755.91
Canada	Components	(null)	(null)

This query lays two measures on columns (from the same dimension and hierarchy [Measures]), and two different hierarchies [Sales Territory Country] and [Product Categories] on rows.

SELECT
   { [Measures].[Internet Sales Amount],
     [Measures].[Internet Gross Profit] 
   } ON 0,
   { [Sales Territory].[Sales Territory Country].[Sales Territory 
     Country] *
     [Product].[Product Categories].[Category]
   } ON 1
FROM
   [Adventure Works]

To return the cross product of two sets, we can use either of the following two syntaxes:

Standard syntax: Crossjoin(Set_Expression1, Set_Expression2)
Alternate syntax: Set_Expression1 * Set_Expression2

We have chosen to use the alternate syntax for its convenience. The result from the previous query is shown as follows:

Notation for empty set is this: {}. So for the axis 0, we would simply do this:

{ } ON 0

Follow these steps to open the Query Editor in SQL Server Management Studio (SSMS):

Follow these steps to get a one-dimensional query result with members on rows:

Although we can't skip axes, we are allowed to provide an empty set on them. This trick allows us to get what we need – nothing on columns and a set of members on rows.

Skipping the Axis(0) is a common technique to create a list for report parameters. If we want to create a list of customers whose name contains "John", we can modify the preceding base query to use two functions to get only those customers whose name contains the phrase John. These two functions are Filter() and InStr():

SELECT
   { } ON 0,
   { Filter(
           [Customer].[Customer].[Customer].MEMBERS,
           InStr(
                 [Customer].[Customer].CurrentMember.Name,
                'John'
               ) > 0
         )
   } ON 1
FROM
   [Adventure Works]

In the final result, you will notice the "John" phrase in various positions in member names:

WITH
MEMBER [Measures].[Dummy] AS NULL

SELECT
   { [Measures].[Dummy] } ON 0,
   { [Customer].[Customer].[Customer].MEMBERS } ON 1
FROM
   [Adventure Works]

The MDX WHERE clause points to a specific intersection of cube space. We use tuple expressions to represent cells in cube space. Each tuple is made of one member, and only one member, from each hierarchy.

The following tuple points to one year, 2008 and one measure, the [Internet Sales Amount]:

( [Measures].[Internet Sales Amount],
  [Date].[Calendar Year].&[2008]
)

Using a tuple in an MDX WHERE clause is called "slicing" the cube. This feature gives the WHERE clause another name, slicer. If we put the previous tuple in the WHERE clause, in MDX terms, we are saying, "show me some data from the cube sliced by sales and the year 2008".

That is what we are going to do next.

Open the Query Editor in SSMS, and then follow these steps to write a query with a slicer and test it:

We can slice the data by pointing to a specific intersection of cube space. We can achieve this by putting a tuple in the WHERE clause.

In the preceding example, the cube space is sliced by sales and year 2008. The cell values are the Internet Sales Amount for each country and each product category, sliced by year 2008.

Notice that the data returned on the query axes can be completely different from the tuple in the WHERE clause. The tuples in the slicer will only affect the cell values in the intersection of rows and columns, not what are on the columns or rows axes.

If you need to display sales and year 2008 on the query axes, you would need to move them to the query axes, and not in the WHERE clause.

This query has moved the sales to the columns axis, and the year 2008 to the rows axis. They both are "crossjoined" to the original hierarchies on the two query axes:

SELECT
   { [Measures].[Internet Sales Amount] *
     [Customer].[Customer Geography].[Country]
   } ON 0,
   { [Date].[Calendar Year].&[2008] *
     [Product].[Product Categories].[Category]
   } ON 1
FROM
   [Adventure Works]

Run the query and you will get the following result. The call values are the same as before, but now we have the year 2008 on the rows axis, and the Internet Sales Amount on the columns axis.

Start a new query in SSMS and make sure that you're working on the Adventure Works DW 2012 database. Then write the following query and execute it:

SELECT 
    { [Measures].[Internet Sales Amount] } ON 0,
    NON EMPTY
    Filter(
            { [Customer].[Customer].[Customer].MEMBERS } *
            { [Date].[Date].[Date].MEMBERS },
            [Measures].[Internet Sales Amount] > 1000
           ) ON 1
FROM
   [Adventure Works]

The query shows the sales per customer and dates of their purchases, and isolates only those combinations where the purchase was over 1000 USD.

On a typical server, it will take more than a minute before the query will return the results.

Now let's see how to improve the execution time by using the NonEmpty() function.

Follow these steps to improve the query performance by adding the NonEmpty() function:

Both the Customer and Date dimensions are medium-sized dimensions. The cross product of these two dimensions contains several million combinations. We know that typically, the cube space is sparse; therefore, many of these combinations are indeed empty. The Filter() operation is not optimized to work in block mode, which means a lot of calculations will have to be performed by the engine to evaluate the set on rows, whether the combinations are empty or not.

Fortunately, the NonEmpty() function exists. This function can be used to reduce any set, especially multidimensional sets that are the result of a cross join operation. It removes the empty combinations of the two sets before the engine starts to evaluate the sets on rows. A reduced set has fewer cells to be calculated, and therefore the query runs much faster.

Regardless of the benefits that were shown in this recipe, NonEmpty() should be used with caution. Here are some good practices regarding the NonEmpty() function:

We will start from a classic Top 10 query that shows the Top 10 Customers. Then we will use the Properties() function to retrieve each top 10 customer's yearly income.

This table shows what our query result should be like:

Once we get only the top 10 customers, it's easy enough to place the customer on the rows, and the Internet sales amount on the columns. What about each customer's yearly income?

Customer geography is a user-defined hierarchy in the customer dimension. In the SSMS, if you start a new query against the Adventure Works DW 2012 database, and navigate to Customer | Customer Geography | Customer | Member Properties, you will see that the yearly income is one of the member properties for the attribute Customer. This is a good news, because now we can surely get the Yearly Income for each top 10 customer using the PROPERTIES() function:

In SSMS, let us write the following query in a new Query Editor against the Adventure Works DW 2012 database:

Attributes correspond to columns in the dimension tables in our data warehouse. Although we don't normally define the relationship between them, in the relationship database, we do so in the multidimensional space. This knowledge of attribute relationships can be used by the Analysis Services engine to optimize the performance. MDX has provided us the Properties() function to allow us to get from members of one attribute to members of another attribute.

In this recipe, we only focus on one type of member properties, that is, the user-defined member property. Member properties can also be the member properties that are defined by Analysis Services itself, such as NAME, ID, KEY, or CAPTION; they are the intrinsic member properties.

The Properties() function can take another optional parameter, that is the TYPED flag. When the TYPED flag is used, the return value has the original type of the member.

The preceding example does not use the TYPED flag. Without the TYPED flag, the return value is always a string.

In many business analysis, we perform arithmetical operations numerically. In the next example, we will include the TYPED flag in the Properties() function to make sure that the [Total Children] for the top 10 customers are numeric.

WITH
MEMBER [Measures].[Yearly Income] AS
    [Customer].[Customer Geography].currentmember.Properties("Yearly Income")
MEMBER [Measures].[Total Children] AS
    [Customer].[Customer Geography].currentmember.Properties("Total Children", TYPED)
MEMBER [Measures].[Is Numeric] AS
    IIF(
       IsNumeric([Measures].[Total Children]),
       1,
       NULL
       )

SELECT
    { [Measures].[Internet Sales Amount],
      [Measures].[Yearly Income],
      [Measures].[Total Children],
      [Measures].[Is Numeric]
    } ON 0,
    TopCount(
     [Customer].[Customer Geography].[Customer].MEMBERS,
     10,
     [Measures].[Internet Sales Amount]
     ) ON 1
FROM
    [Adventure Works]

Attributes can be simply referenced as an attribute hierarchy, that is, when the attribute is enabled as an Attribute Hierarchy.

In SSAS, there is one situation where the attribute relationship can be explored only by using the PROPERTIES() function, that is when its property AttributeHierarchyEnabled is set to False.

In the employee dimension in the Adventure Works cube, employees' SSN numbers are not enabled as an Attribute Hierarchy. Its property AttributeHierarchyEnabled is set to False. We can only reference the SSN number in the PROPERTIES() function of another attribute that has been enabled as Attribute Hierarchy, such as the Employee attribute.

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.

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:

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

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

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.

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.

Start a new query in SQL Server Management Studio and check that you're working on Adventure Works database. Then write and execute this query:

WITH
MEMBER [Date].[Calendar Year].[CY 2006 vs 2005 Bad] AS
   [Date].[Calendar Year].[Calendar Year].&[2006] /
   [Date].[Calendar Year].[Calendar Year].&[2005],
   FORMAT_STRING = 'Percent'
SELECT
   { [Date].[Calendar Year].[Calendar Year].&[2005],
     [Date].[Calendar Year].[Calendar Year].&[2006],
     [Date].[Calendar Year].[CY 2006 vs 2005 Bad] } *
     [Measures].[Reseller Sales Amount] ON 0,
   { [Sales Territory].[Sales Territory].[Country].MEMBERS }
   ON 1
FROM
   [Adventure Works]

This query returns six countries on the rows axis, and two years and a ratio on the column axis.

The problem is that we get 1.#INF on some ratio cells. 1.#INF is the formatted value of infinity, and it appears whenever the denominator CY 2005 is null and the nominator CY 2006 is not null.

We will need help from the IIF() function, which takes three arguments: iif(<condition>, <then branch>, <else branch>). The IIF() function is a Visual Basic for Applications (VBA) function and has a native implementation in MDX. The IIF ( ) function will allow us to evaluate the condition of CY 2005, then decide what the ratio calculation formula should be.

Follow these steps to handle division by zero errors:

The result shows that the new calculated measure has corrected the problem. The last column [CY 2006 vs 2005 Good] is now showing (null) correctly when the denominator CY 2005 is null and the nominator CY 2006 is not null.

A division by zero error occurs when the denominator is null or zero and the numerator is not null. In order to prevent this error, we must test the denominator before the division and handle the two scenarios in the two branches using the IIF() statement.

In the condition part of the IIF statement, we've used a simple scalar number zero to determine if [Measures].[Reseller Sales Amount] in the following slicer is zero or not. If it is zero, then it will be true and the calculated member will be NULL:

[Date].[Calendar Year].[Calendar Year].&[2005] = 0

What about the NULL condition? It turned out for a numerical value; we do not need to test the NULL condition specifically. It is enough to test just for zero because null = 0 returns true. However, we could test for NULL condition if we want to, by using the IsEmpty() function.

For the calculated member [CY 2006 vs 2005 Good] we could wrap the member with the IsEmpty() function. The result will be the same:

MEMBER [Date].[Calendar Year].[CY 2006 vs 2005 Good] AS
   IIF(IsEmpty([Date].[Calendar Year].[Calendar Year].&[2005]),
       null,
       [Date].[Calendar Year].[Calendar Year].&[2006] /
       [Date].[Calendar Year].[Calendar Year].&[2005]
      ),
   FORMAT_STRING = 'Percent'

SQLCAT's SQL Server 2008 Analysis Services Performance Guide has a lot of interesting details regarding the IIF() function, found at http://tinyurl.com/PerfGuide2008R2.

Additionally, you may find the blog article MDX and DAX topics by Jeffrey Wang explaining the details of the IIF() function, found at http://tinyurl.com/IIFJeffrey.

Follow these steps to set up the environment for this recipe:

Follow these steps to set a new default member:

The ALTER CUBE statement changes the default member of a hierarchy specified in the UPDATE DIMENSION part of the statement. The third part is where we specify which member should be the default member of that hierarchy.

Don't mind that it says UPDATE DIMENSION. SSAS 2005 interprets that as a hierarchy.

Setting the default member on a dimension with multiple hierarchies can lead to unexpected results. Due to attribute relations, related attributes are implicitly set to corresponding members, while the non-related attributes remain on their default members, that is, the All member (also known as the root member). Certain combinations of members from all available hierarchies can result in a nonexisting coordinate. In that case, the query will return no data. Other times, the intersection will only be partial. In that case, the query will return the data, but the values will not be correct, which might be even worse than no data at all.

Enter the following expression in the MDX script, deploy it, and then analyze the result in the Cube Browser tab:

ALTER CUBE CurrentCube 
    UPDATE DIMENSION [Date].[Calendar],
      Default_Member = [Date].[Calendar]
                      .[Calendar Year].&[2007];
                       -- "current" year on the user hierarchy

The expression sets the year 2007 as the default member of the [Date].[Calendar] user-defined hierarchy.

The analysis of the Sales Amount measure in the Cube Browser shows good results in almost all cases except in a few. Fiscal hierarchies that have the fiscal year level in them return empty or incomplete results when used in a slicer. They are empty because the intersection between the fiscal year 2006 and the calendar year 2007 (the latter being the default member in the calendar hierarchy) is a nonexisting combination. Remember, the calendar year 2007 doesn't get overwritten by the fiscal year 2006. It gets combined (open the Date dimension in SSDT and observe the relationships in the corresponding tab). Moreover, when you put the fiscal year 2007 into the slicer, you only get a portion of data, the portion which matches the intersection of the calendar and the fiscal year. That's only one half of the fiscal year, right? In short, you have a potential problem with this approach.

Can we fix the result? Yes, we can. The correct results will be there when we explicitly select the All member from the Date.Calendar hierarchy in the slicer. Only then will we get good results using fiscal hierarchies. The question is – will the end users remember that every time?

The situation is similar when the default member is defined on an attribute hierarchy, for example, on the Date.Calendar Year hierarchy. By now, you should be able to modify the previous expression so that it sets the year 2007 as the default member on the [Date].[Calendar Year]. Test this to see it for yourself.

Another scenario could be that you want to put the current date as the default member on the Date.Date hierarchy. Try that too, and see that when you use the year 2006 from the Date.Calendar Year hierarchy in the slicer, you get an empty result. Again, the intersection formed a nonexisting coordinate.

To conclude, you should avoid defining default members on complex dimensions. Define them where it is appropriate: on dimensions with a single non-aggregatable attribute (that is, when you set the IsAggregatable property of an attribute to False) or on dimensions with one or more user hierarchies where that non-aggregatable attribute is the top level on each user hierarchy, and where all relationships are well defined.

The Account dimension used in this example is not such a dimension. In order to correct it, two visible attributes should be hidden because they can cause empty results when used in a slicer. Experimenting with a scope might help too, but that adds to the complexity of the solution and hence the initial advice of keeping things simple when using default members should prevail.

Take a look at other dimensions in the Adventure Works DW 2012 database. There you will find good examples of using default members.