MDX Training - Basics II

Author: Ian Britz

Set Functions

All the functions below are considered set functions. This is because they return set of members. These are important, 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.

All of the example queries below will be taken from the Integrated Financial Planning database. 

• MDX Training - Basics I
• MDX Training - Basics II

DESCENDANTS

Here's an example of the DESCENDANTS function. DESCENDANTS grabs every member below the specified member. In this example, it's finding the descendants of the All Member on the lowest level. The 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. In the example below...

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

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 this is denoting a set of a set. 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, one of the first steps you should do is simplify any extraneous sets, to better understand the structure of the query.