MDX Guide for SQL Folks: Part II - Hierarchies and Functions

Introduction

This is a continuation of the series to introduce some core MDX and cube concepts by first drawing on the similarity and differences between MDX and SQL.

In Part I we looked at how tables translate directly into to cube dimensions. We also looked at how MDX is different or similar to SQL, by looking at both in the context of the basic SQL SELECT statement. For example we saw why all three queries in listing1 below yield the same result. All three queries display the Sales Territory Group(s) with more than 10 million in bike sales.

Please refer to Part I for the prerequisites needed to execute all SQL and MDX queries in this series.

Listing 1.

T-SQL:

SELECT
 t.SalesTerritoryGroup
,Sum(s.SalesAmount) AS SalesAmount
FROM dbo.FactInternetSales s
     INNER JOIN dbo.DimSalesTerritory t
       ON s.SalesTerritoryKey = t.SalesTerritoryKey
     INNER JOIN dbo.DimProduct p
       ON s.ProductKey = p.ProductKey
     INNER JOIN dbo.DimProductSubcategory ps
       ON p.ProductSubcategoryKey = ps.ProductSubcategoryKey
     INNER JOIN dbo.DimProductCategory pc
       ON ps.ProductCategoryKey = pc.ProductCategoryKey
 WHERE pc.EnglishProductcategoryName = 'Bikes' 
 GROUP BY t.SalesTerritoryGroup,pc.EnglishProductcategoryName
 HAVING Sum(s.SalesAmount) > 10000000

Results:

MDX1:

SELECT
 ([Measures].[Internet Sales Amount]) ON COLUMNS,
 ([Sales Territory].[Sales Territory Group].[Sales Territory Group])
HAVING
 [Measures].[Internet Sales Amount] >10000000
ON ROWS
FROM [Adventure Works]
WHERE [Product].[Product Categories].[Category].[Bikes]

Results:

MDX2:

SELECT
 ([Measures].[Internet Sales Amount], [Product].[Product Categories].[Category].[Bikes]) ON COLUMNS,
 ([Sales Territory].[Sales Territory Group].[Sales Territory Group])
HAVING [Measures].[Internet Sales Amount] >10000000
ON ROWS
FROM [Adventure Works]

Results:

By comparing the SQL and MDX queries in Listing 1, let's recap the similarities and differences that we established in Part I. We learned that:

In SQL the SELECT list is derived from tables and the FROM clause is also directly on tables. On the other hand in MDX the SELECT list derived from dimensions but unlike SQL the FROM clause is always on the cube not on dimensions.
By default SQL display results on COLUMNS and ROWS. MDX on the other hand can display results on axes from 0, 1, and 2 …up to 128. As a result MDX requires that you always explicitly specify a display axis for all elements in the SELECT list.
In SQL if you select from two or more tables you have to explicitly join the tables with a “JOIN” word. On the other hand in MDX if you require attributes from two or more dimensions you don’t need to explicitly join them in the statement with the JOIN word, the cube does all the joining for you.
MDX by default always implicitly aggregates the attributes in the Measures dimension and implicitly group the result by any other dimensional attribute in the SELECT list. The aggregation function applied is a default defined at design time. On the other hand SQL will return detail records unless you explicitly tell it to aggregate attributes by including the aggregate function and the Group By clause in the statement as shown in Listing1.
A “WHERE” clause acts as a filter in SQL but as a Slicer dimension in MDX. This means that, in MDX, the predicate can either be used with the WHERE clause as in MDX1 or in the SELECT list without the WHERE clause as in MDX2 as shown in Listing1 above.

Refer to Part 1 for more on the similarities and differences between MDX and SQL and also how to navigate the cube structure with MDX queries. In the next section we are going to see how to use brackets to format the MDX SELECT list and also look at a few nomenclature.

**MDX Brackets ( { }, ( ), [ ] ) and some Cube Concepts**

Because MDX does not require joins when you are querying more than one dimension there is often the edge to throw anything into the MDX SELECT list and on any axis and expect MDX to be able to handle it. I will advise you to think of MDX result sets as if you are generating reports and thus whatever you are trying to display on every axis and how you combine them should make logical sense. This means that you may have to move attributes around and on different axes till you get the result that you are looking for, and that’s OK.

MDX uses brackets to ensure that you generate the right report. In the rest of this section I will illustrate the concept of dimensionality and cube slicing by the correct use of brackets and commas in MDX.

The MDX query in listing 2 below displays Internet Bike sales for the "Europe" Sales Territory Group on the COLUMNS Axis.

Listing 2.

MDX:

SELECT
 (
 [Measures].[Internet Sales Amount]
 ,[Product].[Category].[Bikes]
 ,[Sales Territory].[Sales Territory Group].&[Europe]
 )
ON COLUMNS
FROM [Adventure Works];

The one cell result of the query above is shown as figure 1 below.

Figure1: Shows a cube cell

Now, let’s say in addition to “Europe” we want to retrieve the same bike sales for “North America” and the “Pacific” Sales Territory Groups. There will be the propensity to add the rest of the groups namely “North America” and “Pacific” (shown in red) to the earlier select list as shown in listing 3 below.

Listing 3.

MDX:

SELECT
 (
  [Measures].[Internet Sales Amount]
 ,[Product].[Category].[Bikes]
 ,[Sales Territory].[Sales Territory Group].&[Europe]
 ,[Sales Territory].[Sales Territory Group].[North America]
 ,[Sales Territory].[Sales Territory Group]. [Pacific]
 )
ON COLUMNS
FROM [Adventure Works];

Now, when you run the MDX query above and you get the error.

Error: Query (2, 3) The 'SalesTerritoryGroup' hierarchy appears more than once in the tuple.

In reality, what the logic in Listing3 above tried to do is put all the other members onto one column as shown on figure2 below. The problem is, even if MDX returned the result set on one column as below, it wouldn’t make much sense to anyone looking at the report, more so if a lot more members are added onto that one column.

Figure 2: Illustrates multiples cells improperly displayed on an axis.

To display the result correctly, the logical thing to do is to separate “North America” and the “Pacific” Territory groups onto separate columns with the same "dimensionality" as the “Europe” territory group. To do this you separate the three Groups with parenthesis ( ) separated with commas as below.

(
  [Measures].[Internet Sales Amount]
 ,[Product].[Category].[Bikes]
 ,[Sales Territory].[Sales Territory Group].&[Europe]
),
(
  [Measures].[Internet Sales Amount]
 ,[Product].[Category].[Bikes]
 ,[Sales Territory].[Sales Territory Group].&[North America]
),
(
  [Measures].[Internet Sales Amount]
 ,[Product].[Category].[Bikes]
 ,[Sales Territory].[Sales Territory Group].&[Pacific]
)

Now MDX requires that to display one or more similar dimensionality on an axis they must be explicitly placed in the set { } bracket symbols, so the complete logic will be as in Listing 4 below.

Listing 4.

MDX:

SELECT
{
 (
   [Measures].[Internet Sales Amount]
 ,[Product].[Category].[Bikes]
 ,[Sales Territory].[Sales Territory Group].&[Europe]
 ),
 (
   [Measures].[Internet Sales Amount]
 ,[Product].[Category].[Bikes]
 ,[Sales Territory].[Sales Territory Group].&[North America]
 ),
 ( 
  [Measures].[Internet Sales Amount]
 ,[Product].[Category].[Bikes]
 ,[Sales Territory].[Sales Territory Group].&[Pacific]
 )
}
ON COLUMNS
FROM [Adventure Works]

Now if you run the logic in listing4 you get the right results on three columns with the same dimensionality and referencing 3 cells on the COLUMNS axis as shown in figure 3 below.

Figure 3: Illustrates the results of properly formatted MDX Set result.

As demonstrated in listing 4 above, using logical selection, dimensionality and the correct use of brackets enables us to appropriately display a complete set of result on an MDX Axis.

Before we move on, let me mention the fact that, for the most part you will not be generating convoluted tuples (explained below) in the MDX you write. The times that you will have to do that is when you personally want to display very specific items on different Axes. For instance the query MDX1 below is simpler but pretty much yields the same values as the MDX in listing 4 above, albeit the Sales Territory Groups are displayed on the ROWS axis and “Bikes” is not displayed as part of the results in the MDX query below.

MDX 1

SELECT
 [Measures].[Internet Sales Amount] ON COLUMNS
 {
  [Sales Territory].[Sales Territory Group].&[Europe]
 ,[Sales Territory].[Sales Territory Group].&[North America]
 ,[Sales Territory].[Sales Territory Group].&[Pacific]
 } ON ROWS
FROM [Adventure Works]
WHERE [Product].[Category].[Bikes]

Results:

Notice that both of the MDX queries above are equivalent to the SQL below.

SELECT
   t.SalesTerritoryGroup
  ,Sum(s.SalesAmount) AS SalesAmount
 FROM dbo.FactInternetSales AS s
      INNER JOIN dbo.DimSalesTerritory AS t
        ON s.SalesTerritoryKey = t.SalesTerritoryKey
      INNER JOIN dbo.DimProduct AS p
        ON s.ProductKey = p.ProductKey
      INNER JOIN dbo.DimProductSubcategory AS ps
        ON p.ProductSubcategoryKey = ps.ProductSubcategoryKey
      INNER JOIN dbo.DimProductCategory AS pc
        ON ps.ProductCategoryKey = pc.ProductCategoryKey
 WHERE pc.EnglishProductcategoryName = 'Bikes'
 and t.SalesTerritoryGroup in ('North America','Europe','Pacific')
 GROUP BY t.SalesTerritoryGroup,pc.EnglishProductcategoryName

Results:

Notice also that, the MDX query becomes even simpler if we wanted to display all the Sales Territory Groups instead of listing each individually as shown in the MDX query below

SELECT
  [Measures].[Internet Sales Amount] ON COLUMNS
 ,Non Empty([Sales Territory].[Sales Territory Group].children) ON ROWS
FROM [Adventure Works]
WHERE [Product].[Category].[Bikes]

Results:

MDX Nomenclature

I am going to use the logic in listing4 to explain a few MDX terminologies as illustrated on figure 4 below.

Figure 4: Showing MDX Tuples and Set

Tuple

As illustrated in the MDX on figure 4 above, a tuple is a way of referencing the value of any cell in the cube. It is represented by logical comma separated list of unique names of members from different hierarchies in the cube, surrounded by round brackets ( ).

If a tuple is composed of only one member from a single dimension hierarchy the ( ) are optional. As shown in listing5 below, both syntaxes in MDX1 and MDX2 are correct.

Listing5.

MDX1:

SELECT
( [Product].[Category].[Bikes] )
ON COLUMNS
FROM [Adventure Works]

MDX2:

SELECT
 [Product].[Category].[Bikes]
ON COLUMNS
FROM [Adventure Works]

Sets

As illustrated on figure above, a set constitutes collection of zero, one or more tuples with the same dimensionality and explicitly enclosed in the set { } symbol.

Note that { } brackets are optional if you have only one tuple in your SELECT list, for instance from figure 4 if we want to select only one of the tuple then both MD1 and MD2 in listing6 below are correct.

Listing6.

MDX1:

SELECT
  {
  (
   [Measures].[Internet Sales Amount]
  ,[Product].[Category].[Bikes]
  ,[Sales Territory].[Sales Territory Group].&[Europe]
  )
  }
  ON COLUMNS
  FROM [Adventure Works]

MDX2:

SELECT
  (
  [Measures].[Internet Sales Amount]
  ,[Product].[Category].[Bikes]
  ,[Sales Territory].[Sales Territory Group].&[Europe]
  )
  ON COLUMNS
  FROM [Adventure Works]

Also be careful of uniqueness of dimensionality in a tuple on an axis, for instance MDX1 in Listing 7below is correct but MDX2 is not, and it fails when you try to execute it.

Listing 7.

MDX1:

  SELECT
  (
  [Measures].[Internet Sales Amount]
  ,[Product].[Category].[Bikes]
  )
  ON COLUMNS
  FROM [Adventure Works]

MDX2:

  SELECT
  (
  [Measures].[Internet Sales Amount]
  ,[Measures].[Reseller Sales Amount]
  )
  ON COLUMNS
  FROM [Adventure Works]

Error: Query (2, 3) The 'Measures' hierarchy appears more than once in the tuple.

Notice that in the tuple in MDX2, the two members are from the same (Measures) dimension. Because they are of the same dimensionality, they must be separated into two tuples. When separated into two tuples you must explicitly enclosed them in the set curly brackets as in MDX3 below.

MDX3:

  SELECT
  {
  ([Measures].[Internet Sales Amount])
  ,([Measures].[Reseller Sales Amount])
  }
  ON COLUMNS
  FROM [Adventure Works]

Remember that if a tuple has only one member you can ignore the ( ) around the tuple. So MDX3 above can also be represented as MDX4 below.

MDX4:

  SELECT
  {
  [Measures].[Internet Sales Amount]
  ,[Measures].[Reseller Sales Amount]
  }
  ON COLUMNS
  FROM [Adventure Works]

Cube Slicing and Dimensionality

To illustrate the concept of slicing and "dimensionality" of a cube, let's look at the listings and figures below.

In listing8 below, the two tuples on ROWS in MDX1 has the same number and ordering of the dimension members. On the other hand the number of dimension members in the two tuples on the ROWS axis in MDX2 does not conform. The second tuple has only two dimension members as oppose to three in the first tuple so it fails when you execute it.

Listing8.

MDX1:

  SELECT
  [Measures].[Internet Sales Amount]
  ON COLUMNS
  ,{
  (
  [Date].[Fiscal Year].&[2002]
  ,[Product].[Category].[Bikes]
  ,[Sales Territory].[Sales Territory Group].&[Europe]
  )
  ,(
  [Date].[Fiscal Year].&[2002]
  ,[Product].[Category].[Bikes]
  ,[Sales Territory].[Sales Territory Group].&[North America]
  )
  }
  ON ROWS
  FROM [Adventure Works];

MDX2:

  SELECT
  [Measures].[Internet Sales Amount]
  ON COLUMNS
  ,{
  (
  [Date].[Fiscal Year].&[2002]
  ,[Product].[Category].[Bikes]
  ,[Sales Territory].[Sales Territory Group].&[Europe]
  )
  ,(
  [Date].[Fiscal Year].&[2002]
  ,[Sales Territory].[Sales Territory Group].&[North America]
  )
  }
  ON ROWS
  FROM [Adventure Works];

Error: Query (2, 2) Two sets specified in the function have different dimensionality.

I have tried to illustrate the tuples in MDX1 and MDX2 in the figure below.

Figure 5: Illustration of two dimensional view of two cube slices.

The missing dimension in the second tuple in MDX2 makes it an incomplete cubes slice and thus result in difference in dimensionality on the ROWS axis. MDX2 fails execution with the error shown above.

Also listing9 below is to further illustrate the concept of dimensionality. Both queries in MDX1 and MDX2 in listing9 have three dimension members in each of the two tuples on the COLUMNS axis.

Listing9.

MDX1:

  SELECT 
  { 
  ( 
  [Measures].[Internet Sales Amount] 
  ,[Product].[Category].[Bikes] 
  ,[Sales Territory].[Sales Territory Group].[Europe] 
  ) 
  ,( 
  [Measures].[Internet Sales Amount] 
  ,[Product].[Category].[Bikes] 
  ,[Sales Territory].[Sales Territory Group].[North America] 
  ) 
  } 
  ON COLUMNS 
  FROM [Adventure Works];

MDX2:

  SELECT 
  { 
  ( 
  [Measures].[Internet Sales Amount] 
  ,[Product].[Category].[Bikes] 
  ,[Sales Territory].[Sales Territory Group].&[Europe] 
  ) 
  ,( 
  [Measures].[Internet Sales Amount] 
  ,[Sales Territory].[Sales Territory Group].&[North America] 
  ,[Product].[Category].[Bikes] 
  ) 
  } 
  ON COLUMNS 
  FROM [Adventure Works];

Error: Query (2, 2) Two sets specified in the function have different dimensionality.

Note that in MDX2 the ordering of the members in the two tuples does not conform so it fails execution with the error shown above.

The dimensionality of MDX1 and MDX2 are illustrated in figure 6 below. MDX2 does not represent a properly form MDX syntax.

Figure 6: Showing dimensionality on COLUMNS axis

From the examples and illustrations above it is clear that to generate the right MDX logic, you always have to keep a consistent order and number of tuple members and also apply brackets correctly in other to achieve the right dimensionality on any axis you choose to display on.

As a note, the square bracket characters are used if the name of a member has a space or a number in it, the right bracket (]) can be used as an escape character in MDX if the member name or member key contains a right bracket. It mostly has the same use as in SQL.

The concepts in the last two sections may seem a little confusing initially but with little practice you should get a good handle on it.

Most of the syntax differences between MDX and SQL will also be due to hierarchies, the fact that cube dimensions have them but relational tables don’t have them. In the next section I am going to spend some time explaining how hierarchies determine the MDX you write.

Navigating Hierarchies

In Part I we learned how the cube turns each regular dimensional attributes into a two-Level hierarchy called Attribute Hierarchies. All Attribute Hierarchies have a top Level called "All" and an Attribute level and are created by default by the cube for every dimensional attribute as shown in figure 7 below.

Figure 7: Shows Transformation of Tables Attributes translates into Attribute Hierarchies. It also shows two User-Defined Hierarchies.

User-Defined Hierarchies

Besides attributes hierarchies generated by the cube, cube developers are allowed to create their own hierarchies called user-defined hierarchies on any dimension and as many as they want. The best practice is to create user-defined hierarchies based on the natural relationships in the underlying Data. Some obvious candidate dimensions with natural such relationships are for e.g. Geography (Country, State, City and Zip) or Time (Year, Quarter, Month, week and day). At design time a cube developer will design hierarchy levels base on these naturally occurring hierarchies in the data. For instance in the cube a user defined hierarchy called “Sales Territory” has been defined for the Sales Territory dimension as shown in Figure8 below. Note that the “Sales Territory” user-defined hierarchy has been defined on natural relationships within the underlying data of the dimension i.e. Sales Territory Group, Sales Territory Country and Sales Territory Region renamed to Group, Country and Region respectively in the cube.

Figure 8: Shows 3 Attribute and 1 User-defined hierarchies under the Sales Territory dimension

If you locate the Sales Territory dimension in the cube and drill down on it you will see the two hierarchy types as shown in figure 9 below.

Figure 9: Shows 3 Attribute hierarchies and 1 User-defined hierarchy under the Sales Territory dimension

Note the symbols used to identify each hierarchy type as shown on figure 10 below.

Figure 10: Showing cube symbols for Attribute and User-defined hierarchies.

Remember, cube developer are allowed to design as many user defined hierarchies as they want on any dimension, so when you are writing MDX you have to know the hierarchies available, know their names and the levels in their definition before you can navigate them or include them in your MDX queries.

Referencing Hierarchies in MDX

Once you’ve identified and checked out the definitions of user-defined hierarchies you can referenced them in your MDX just as we have been referencing attribute hierarchies all this while. We have been referencing attribute hierarchies by the name of the dimension they belong to, the attribute hierarchy name and then the attribute level by name as below.

[<Dimension Name>].[<Attribute Hierarchy Name>].[<Attribute Level Name>]

In the same way, you navigate a user-defined hierarchy by referencing the dimension it belongs to, the user-defined hierarchy Name and then the levels under the hierarchy by Name as below.

[<Dimension Name>].[<User-Defined Hierarchy Name>].[<Level Name>]

For instance MDXI and MDX2 in listing 10 below show how to retrieve the Sales Territory Group attribute level (on rows) through the Sales Territory Group Attribute Hierarchy and the Sales Territory User-Defined Hierarchy respectively.

Listing 10.

MDX1: (Referencing Attribute Hierarchy)

  SELECT 
  {} ON COLUMNS, 
  [Sales Territory].[Sales Territory Group].[Sales Territory Group] 
  ON ROWS 
  FROM [Adventure Works]

Results:

MDX2: (Referencing User-Defined Hierarchy)

  SELECT 
  {} ON COLUMNS, 
  [Sales Territory].[Sales Territory].[Group] 
  ON ROWS 
  FROM [Adventure Works]

Results:

Note that we retrieved the same information from an attribute hierarchy and user-defined hierarchy. As a matter of fact any attribute that you can retrieve from a user-defined hierarchy would be available as an attribute hierarchy. So now the questions is, if you can obtain the same results using either hierarchy types then what is the need for going the extra step to create User-Defined Hierarchies?

The answer is in the way each type is designed and how you use each in your MDX queries. Let’s look at how each hierarchy type is designed in the next section.

Hierarchy Architecture

First let’s look at the raw data in the SalesTerritory table and how the data from this table is structured into attribute and user-defined hierarchies for the Sales Territory dimension in the cube. Table 1 below shows the detailed data in the SalesTerritory table.

Table 1: Showing the raw data SalesTerritorry table.

Now let’s look at how the cube models this data as attribute hierarchies. Figure11 below shows a representation of the 3 attribute hierarchies generated by the cube for each of the 3 dimension attributes, each of which represent a column in the underlying table.

Attribute hierarchies

Figure 1: Showing a dimensional attribute hierarchy architecture.

Notice that the overall attribute hierarchy architecture is not very different from the table architecture, the attribute level in each of the three attribute hierarchies contains the detail record from a table column, the only difference is the “All” level member (renamed “All Sales Territory” ) is added to the attribute hierarchies but not present in the table data.This can also be shown from the SQL and MDX queries and results shown below.

SQL:

  SELECT DISTINCT 
  SalesTerritoryGroup 
  FROM dbo.DimSalesTerritory

Results:

MDX:

  SELECT 
  { } ON COLUMNS, 
  [Sales Territory].[Sales Territory Group]. 
  members ON ROWS 
  FROM [Adventure Works]

Results:

From the two results above we can see that the only difference is the "All Sales Territory" present in the MDX resultset but absent in the SQL resultset.

Now let’s look at how the cube developer modeled Sales Territory user-defined hierarchy. Figure 12 below shows a representation of the Sales Territory user-defined hierarchy in the cube.

Notice that there is only one user-defined hierarchy as oppose to three attribute hierarchies but this one user-defined hierarchy captures all the detail data in the dimension for that matter all the records in the underlying table in this one hierarchy. In this architecture the levels are designed to much the natural relationships in the underlying data.

User-defined hierarchies

Figure 12: Shows a User-Defined Sales Territory Hierarchy

As you can see from the MDX query below, the design is such that by referencing this one hierarchy you can have access to all or subsets of the detail level data in the dimension.

MDX

  SELECT { } ON COLUMNS, 
  [Sales Territory].[Sales Territory].members ON ROWS 
  FROM [Adventure Works]

Results:

There are great performance benefits for designing and using user-defined hierarchies in your cube, a complete discussion of these benefits are out of scope in this series. By the nature of their internal architecture user-defined hierarchies results in faster MDX queries when referenced in MDX, so bottom line is, if they are available in your cube identify them and used them in your MDX queries.

Introduction to Hierarchical Functions

In part I I mentioned that creating hierarchical structures allows the cube to navigate to the different levels of the dimensions quickly, and by the use of functions like members, children, siblings, cousin, ancestors, descendants it know where to go and what to retrieve. From the previous sections it becomes quite obvious that these functions are more useful for user-defined hierarchies than attribute hierarchies because the later has only two levels.

The Descendants Function

The descendant hierarchical function is one of the very useful functions you will use in MDX. The function takes three parameters, one version is shown below.

Descendants (member, Level, Desc_Flag)

Remember that dimension have two types of hierarchies therefore hierarchical functions are applicable to both types.

Let’s see the use of the descendant hierarchical function with the user-defined hierarchy from Sales Territory dimension , as shown in MDX1 below.

MDX1:

  SELECT { } ON COLUMNS, 
  Descendants 
  ( 
  [Sales Territory].[Sales Territory].[Region] --upper member 
  ,[Sales Territory].[Sales Territory].[Region] --lower level 
  ,self_and_Before --Description flag 
  ) ON ROWS 
  FROM [Adventure Works]

Results:

In this example the upper member and the lower levels are the same so no matter what description flag you specify the results will be the descendants of the region level.

Now let’s change the upper member to the country level as shown in MDX2, as you can see the result now include the descendants of the country level as well as the region level.

MDX2:

  SELECT { } ON COLUMNS, 
  Descendants 
  ( 
  [Sales Territory].[Sales Territory].[country] --upper member 
  ,[Sales Territory].[Sales Territory].[Region] --lower level 
  ,self_and_Before --Description flag 
  ) ON ROWS 
  FROM [Adventure Works]

Results:

Now let’s see some examples of the use of the descendant function with attribute hierarchies from the Sales Territory dimension .

MDX3:

  SELECT { } ON COLUMNS, 
  Descendants 
  ( 
  [Sales Territory].[Sales Territory Country].[Sales Territory Country] --upper member 
  ,[Sales Territory].[Sales Territory Country].[Sales Territory Country] --lower level 
  ,self_and_Before 
  ) ON ROWS 
  FROM [Adventure Works] 
  Results:

MDX4:

  SELECT { } ON COLUMNS, 
  Descendants 
  ( 
  [Sales Territory].[Sales Territory Country].[All Sales Territories] --upper member 
  ,[Sales Territory].[Sales Territory Country].[Sales Territory Country] --lower level 
  ,self_and_Before --Description flag 
  ) ON ROWS 
  FROM [Adventure Works]

Note that if you have several levels in a hierarchy, by using the upper member, the lower levels and the flag you can manipulate the syntax to display sets from different levels of user-defined hierarchies on the other hand some of these function are useless on attribute hierarchies since attribute hierarchy types have only two levels as shown in MDX3 and MDX4.

There are many argument and flag options for the Descendants function. For a complete list of MDX functions and how they are used check out the MDX function reference at (http://msdn.microsoft.com/en-us/library/ms146075.aspx)

Other Functions

Cross join function

The Cross join function automatically returns the cross-product of two sets and thus allows you to display the result on an axis.

The Syntax to display a cross join sets on an axis is as below;

  Select Cross join ( { set1}, { set2 } ) on Axis(n).. 
  from Cube

another variant of this syntax is as below.

  Select { set1}*{ set2 } on Axis(n).. 
  from Cube

Let’s say we want display the cross join sets of all Years from the Date Calendar hierarchy and a set of all Sales Territory Groups on the ROWS axis. MD1, MDX2 and the resultset in listing11 below shows both cross join syntax variants we saw above.

Listing11

MDX1:

  SELECT 
  { } on columns 
  ,cross join 
  ( 
  {[Date].[Calendar].[Calendar Year].members} 
  ,{[Sales Territory].[Sales Territory].[Group].members} 
  ) on rows 
  FROM [Adventure Works]

MDX2:

  SELECT 
  { } on columns 
  ,( 
  {[Date].[Calendar].[Calendar Year].members} 
  * {[Sales Territory].[Sales Territory].[Group].members} 
  ) on rows 
  FROM [Adventure Works]

Result:

Note that unlike SQL “Cross Join” without a where clause which returns a full Cartesian product, MDX cross join limits returns to the set of tuples that actually exist in the cube. For example, examine the results from the execution of the query below.

MDX:

  SELECT 
  {} on columns 
  ,Cross join 
  ( 
  --From attribute hierarchy 
  {([Sales Territory].[Sales Territory Group].[Europe]) 
  ,([Sales Territory].[Sales Territory Group].[North America]) 
  } 
  --from user defined hierarchy 
  ,{[Sales Territory].[Sales Territory].[Group].members} 
  ) on rows 
  FROM [Adventure Works]

Result:

Note that even though the second set has more than two members (all Sales Territory Group members) the cross join returned only two members because first set has two tuples in the set.

Another thing to note is that, I derived the first Sales Territory set from attribute hierarchies and the second set from user-defined hierarchy in the Sales Territory dimension. This is because MDX expects the sets you are cross joining to always come from different hierarchies.

You can cross join multiple set by nesting cross joins as below.

  Select Cross join ({ set1}, 
  Cross join ( { set2}, { set3 } ) 
  ) on Axis(n).. 
  from Cube

The example below shows how to cross join three sets.

MDX

  SELECT 
  { } on columns 
  ,Cross join ( 
  {[Date].[Calendar].[Calendar Year].members} 
  ,cross join 
  ( 
  {([Sales Territory].[Sales Territory Group].[Europe]) 
  ,([Sales Territory].[Sales Territory Group].[North America]) 
  } 
  ,{[Sales Territory].[Sales Territory].[Group].members} 
  ) 
  )on rows 
  FROM [Adventure Works]

Result:

......

Non Empty function

The Non Empty function is used to filter out nulls from a specified set. in the example below MDX1 displays nulls in the resultset. Applying the Non Empty function to the same query eliminates the nulls as shown in MDX2 below.

MDX1:

  select 
  [Measures].[Internet Sales Amount] 
  ON COLUMNS 
  ,( 
  [Date].[Fiscal Year]. 
  members 
  ,[Product].[Product Categories].[Product].[ML Road Tire] 
  ) 
  ON ROWS 
  FROM 
  [Adventure Works];

result:

MDX2:

  select 
  [Measures].[Internet Sales Amount] 
  ON COLUMNS 
  ,Non Empty 
  ( 
  [Date].[Fiscal Year]. 
  members 
  ,[Product].[Product Categories].[Product].[ML Road Tire] 
  ) 
  ON ROWS 
  FROM 
  [Adventure Works];

result:

Summary

In this Part we've looked at some indispensable cube terminologies and definitions. We also looked at the use of brackets and concept of Dimensionality and Cube slicing. We finally looked at the differences between attribute hierarchies and user-defined hierarchies, and the importance of identifying user-defined hierarchies in your cubes and using them in your MDX queries.

Understanding the concepts introduced here is key to moving on to write advanced MDX queries which you will find out are just extensions of these concepts.

Next Steps

We will look at some other important functions and finally write some advanced MDX queries by combining everything that we've learned in these series.

MDX Guide for SQL Folks: Part II - Hierarchies and Functions

Introduction

**MDX Brackets ( { }, ( ), [ ] ) and some Cube Concepts**

MDX Nomenclature

Cube Slicing and Dimensionality

Navigating Hierarchies

User-Defined Hierarchies

Referencing Hierarchies in MDX

Hierarchy Architecture

Introduction to Hierarchical Functions

The Descendants Function

Other Functions

Non Empty function

Summary

Next Steps

Rate

Share

Categories

Share

Rate

MDX Guide for SQL Folks: Part II - Hierarchies and Functions

Introduction

MDX Brackets ( { }, ( ), [ ] ) and some Cube Concepts

MDX Nomenclature

Cube Slicing and Dimensionality

Navigating Hierarchies

User-Defined Hierarchies

Referencing Hierarchies in MDX

Hierarchy Architecture

Introduction to Hierarchical Functions

The Descendants Function

Other Functions

Non Empty function

Summary

Next Steps

Rate

Share

Categories

Share

Rate

Related content

MDX Guide for SQL Folks: Part III - Advanced Analysis

MDX Guide for SQL Folks: Part I - Navigating The Cube

**MDX Brackets ( { }, ( ), [ ] ) and some Cube Concepts**