MDX Training - Basics II

Author: Ian Britz

In this tutorial (part II of our MDX training series), we will cover set functions, which return member sets. Set functions are useful to know, as you can use set functions to define your axes. Remember that sets are always defined by either a set of curly braces { }, or a function which returns a set.

Tip: Please review part I of our MDX training series before following this guide. 

DESCENDANTS

Here's an example of the DESCENDANTS function. The DESCENDANTS function grabs every Member below the specified Member. In this example, it's finding the descendants of the All Member on the lowest level. 10,000 is a limit on how far it goes down the tree in its search.

DESCENDANTS([Account].[Account].[All], 10000, LEAVES)

This is useful when we want to dynamically grab a set of Members without specifying each one: 

SELECT 
{
 DESCENDANTS([Account].[Account].[Salaries & Benefits], 10000, LEAVES)
} 
ON ROWS,
{
 DESCENDANTS([Department].[Department].[All], 10000, LEAVES)
}
ON COLUMNS
FROM [Workforce-Reporting] 
WHERE
{[Scenario].[Scenario].[Actual]}

There's also a lesser used ASCENDANTS function, which returns members above the specified Member in the Hierarchy.

SELECT
{
ASCENDANTS([Account].[Account].[Salaries & Benefits])
}
ON ROWS,
{
DESCENDANTS([Department].[Department].[All], 10000, LEAVES)
}
ON COLUMNS
FROM [Workforce-Reporting]
WHERE
{[Scenario].[Scenario].[Actual]}

HIERARCHIZE

The HIERARCHIZE set function takes in a set and returns the Members in the order defined by the Hierarchy.

SELECT 
{
 DESCENDANTS([Account].[Account].[Salaries & Benefits], 10000, LEAVES)
} 
ON ROWS,
{
 HIERARCHIZE({[Department].[Department].[Sales]
 ,[Department].[Department].[All]
 ,[Department].[Department].[Finance]})
}
ON COLUMNS
FROM [Workforce-Reporting] 
WHERE
{[Scenario].[Scenario].[Actual]}

Types of Sets

1. An Array of Members (within the same hierarchy)

{[Department].[Department].[Sales]
,[Department].[Department].[Marketing] 
,[Department].[Department].[Finance]}

2. A Cross Join (between one or more sets)

{[Department].[Department].[Sales]}
*{[Account].[Account].[Headcount]}

3. An Array of Sets (where each set has the same set of hierarchies)

{ 
{[Department].[Department].[Sales]}*{[Account].[Account].[Headcount]}
,{[Department].[Department].[Finance]}*{[Account].[Account].[Salaries]}
}

Nested sets of curly braces can usually be removed for improved readability, as they denote sets of sets. In this example, these two sets are functionally the same:

{{[Department].[Department].[Sales]}}
{[Department].[Department].[Sales]}

As are these sets:

{DESCENDANTS([Account].[Account].[Salaries & Benefits], 10000, LEAVES)}
DESCENDANTS([Account].[Account].[Salaries & Benefits], 10000, LEAVES)

Tip: When working in MDX, you should always simplify any extraneous sets, to better understand the structure of the query.

Next Steps

Complete our MDX training by going through MDX Training - Basics III.