MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition

Making a two–by–eight table (that is shown following) in a spreadsheet is quite simple. Writing an 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 with 0 and rows with 1. Throughout this book, we will use the number 0 for columns, which is the x axis, and 1 for rows, which is the y axis.

We are going to use the Adventure Works 2016 Multidimensional Analysis Service database enterprise edition in our cookbook. If you open the Adventure Works cube, and hover your cursor over the Internet Sales Amount measure, 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, the same as in 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 crossjoin to combine multiple hierarchies into one query axis.

Putting more hierarchies on x and y axes with cross join

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

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 [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:

The notation for an 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 up of one member, and only one member, from each hierarchy.

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

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

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 2013.

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 the year 2008. The cell values are the Internet Sales Amount for each country and each product category, sliced by the 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 column or row 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 are both cross joined to the original hierarchies on the two query axes:

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

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

Start a new query in SSMS and make sure that you are working on the Adventure Works DW 2016 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 1,000 USD.

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

Now let us 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.

This is because the Filter() function needs to iterate over the complete set of data in every cell in order to isolate a single cell. For this reason, the Filter() function can be slow when operating on large dimensions or cross–join result of even medium-sized dimensions.

Fortunately, the NonEmpty() function exists. This function can be used to reduce any set, especially multidimensional sets that are the result of a crossjoin operation.

The NonEmpty() function 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, the NonEmpty() function should be used with caution. Here are some good practices regarding the NonEmpty() function:

We will start with 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 is easy enough to place the customer on the rows, and the Internet sales amount on the columns. What about each customer's yearly income?

The Customer Geography is a user-defined hierarchy in the Customer dimension. In SSMS, if you start a new query against the Adventure Works DW 2016 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 Customer attribute. This is 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 2016 database:

Attributes correspond to columns in the dimension tables in our data warehouse. Although we do not 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 property, 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 most 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] 

The following is the result:

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 AttributeHierarchyEnabled property is set to False.

In the employee dimension in the Adventure Works cube, employees' SSN numbers are not enabled as an Attribute Hierarchy. Its AttributeHierarchyEnabled property 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 five (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 five 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 have 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 2016 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 the Adventure Works database. Then write and execute this query:

WITH 
MEMBER [Date].[Calendar Year].[CY 2012 vs 2011 Bad] AS 
   [Date].[Calendar Year].[Calendar Year].&[2012] / 
   [Date].[Calendar Year].[Calendar Year].&[2011], 
   FORMAT_STRING = 'Percent' 
SELECT 
   { [Date].[Calendar Year].[Calendar Year].&[2012], 
     [Date].[Calendar Year].[Calendar Year].&[2011], 
     [Date].[Calendar Year].[CY 2012 vs 2011 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 2011 is null and the nominator CY 2012 is not null.

We will need help from the IIF() function, which takes three arguments: IFF(<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 2011, 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 2012 vs 2011 Good] is now showing (null) correctly when the denominator CY 2011 is null and the nominator CY 2012 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 whether [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].&[2011] = 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 a NULL condition if we want to, by using the IsEmpty() function.

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

MEMBER [Date].[Calendar Year].[CY 2012 vs 2011 Good] AS 
   IIF(IsEmpty([Date].[Calendar Year].[Calendar Year].&[2011]), 
       null, 
       [Date].[Calendar Year].[Calendar Year].&[2012] / 
       [Date].[Calendar Year].[Calendar Year].&[2011] 
      ), 
   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 2016 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 non-existing 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, then deploy it:

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

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

Let's analyze the result with the following query:

SELECT  
  [Measures].[Sales Amount] ON 0, 
  [Date].[Fiscal].[Fiscal Year] ON 1 
FROM 
  [Adventure Works] 

The result is shown in the following screenshot:

The analysis of the Sales Amount measure for each fiscal year returns empty results except in FY 2011 and FY 2012. They are empty because the intersection between the fiscal year and the calendar year 2012 (the latter being the default member in the calendar hierarchy) is a non-existing combination, except FY 2011 and FY 2012. Remember, the calendar year 2012 doesn't get overwritten by the fiscal year 2011 or 2012. It gets combined (open the Date dimension in SSDT and observe the relationships in the corresponding tab). Moreover, when you put the fiscal year 2011 or 2012 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. The complete MDX is shown in the following query. Only then will we get correct results using fiscal hierarchies. The question is—will the end users remember that every time?

SELECT [Measures].[Sales Amount] ON 0, 
    [Date].[Calendar Year].[All] * 
    [Date].[Fiscal].[Fiscal Year] ON 1 
FROM 
  [Adventure Works] 

The correct results from this query can be seen in the following screenshot:

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 2012 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 2012 from the [Date].[Calendar Year] hierarchy in the slicer, you get an empty result. Again, the intersection formed a non-existing 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 2016 database. There, you will find good examples of using default members.