Today we’re back looking at MDX with a quick how-to. How do you extract a list of dimension members using MDX?

This could be used for example to populate a user interface/parameter of a report/dashboard.

Let’s look at two scenarios; normal dimensions, and then hierarchies. All the queries here use the “Adventure Works DW 2008 R2″ “Adventure Works” cube.

Standard dimension attributes

So how do we get a list of dimension members? Well lets divide the query up into rows and columns. We want the members listed on the rows.

1
2
3
4
SELECT
  xxx ON COLUMNS,
  [Geography].[Country].ALLMEMBERS ON ROWS
FROM [Adventure Works]

Why use .ALLMEMBERS instead of .MEMBERS? .ALLMEMBERS will include calculated members, whereas .MEMBERS won’t.

So what do we put on the rows? We want the display name for showing to the user, and the unique name so we can refer back to each exact member. We can get these by using properties of the current member, namely MEMBER_CAPTION and UNIQUENAME. Get a full list of dimension member properties here. To make this work, we just define a number of query-scoped calculated members using WITH, and then include them in the columns set…

1
2
3
4
5
6
7
8
WITH 
   MEMBER [Measures].[Label] AS [Geography].[Country].CURRENTMEMBER.MEMBER_CAPTION 
   MEMBER [Measures].[UniqueName] AS [Geography].[Country].CURRENTMEMBER.UNIQUENAME 
SELECT {[Measures].[Label], 
        [Measures].[UniqueName]
        } ON COLUMNS , 
      [Geography].[Country].ALLMEMBERS ON ROWS 
FROM [Adventure Works]

If you run this, you get the following output:

This may be what you want, but note that the first row of the result set contains the ‘ALL’ member, which you probably don’t want. You can remove this by altering the ROWS clause to exclude them. This is simply a case of repeating the hierarchy name, e.g. [Geography].[Country].[Country].ALLMEMBERS

1
2
3
4
5
6
7
8
WITH 
   MEMBER [Measures].[Label] AS [Geography].[Country].CURRENTMEMBER.MEMBER_CAPTION 
   MEMBER [Measures].[UniqueName] AS [Geography].[Country].CURRENTMEMBER.UNIQUENAME 
SELECT {[Measures].[Label], 
        [Measures].[UniqueName]
        } ON COLUMNS , 
      [Geography].[Country].[Country].ALLMEMBERS ON ROWS 
FROM [Adventure Works]

Hierarchies

With simple dimension attributes, you get a flat list of values. With hierarchies, whether standard user hierarchies, ragged or parent-child, you need to return a multi-level list. To do this we need to know what level each member is at. We can query this using the LEVEL.ORDINAL property. Adding this into the query, and replacing Geography for Employee, we get the following query:

1
2
3
4
5
6
7
8
9
10
WITH 
   MEMBER [Measures].[Label] AS [Employee].[Employees].CURRENTMEMBER.MEMBER_CAPTION 
   MEMBER [Measures].[UniqueName] AS [Employee].[Employees].CURRENTMEMBER.UNIQUENAME 
   MEMBER [Measures].[Ordinal] AS [Employee].[Employees].CURRENTMEMBER.LEVEL.ORDINAL 
SELECT {[Measures].[Label], 
        [Measures].[UniqueName], 
        [Measures].[Ordinal]
        } ON COLUMNS , 
      [Employee].[Employees].ALLMEMBERS ON ROWS 
FROM [Adventure Works]

With the following results:

You can then do all sorts of funky things using basic MDX navigation functions. e.g. returning the parent of each member, by simply adding …PARENT.UNIQUENAME as another column

1
2
3
4
5
6
7
8
9
10
11
12
WITH 
   MEMBER [Measures].[Label] AS [Employee].[Employees].CURRENTMEMBER.MEMBER_CAPTION 
   MEMBER [Measures].[UniqueName] AS [Employee].[Employees].CURRENTMEMBER.UNIQUENAME 
   MEMBER [Measures].[Ordinal] AS [Employee].[Employees].CURRENTMEMBER.LEVEL.ORDINAL 
   MEMBER [Measures].[Parent] AS [Employee].[Employees].PARENT.UNIQUENAME
SELECT {[Measures].[Label], 
        [Measures].[UniqueName], 
        [Measures].[Ordinal],
        [Measures].[Parent]
        } ON COLUMNS , 
      [Employee].[Employees].ALLMEMBERS ON ROWS 
FROM [Adventure Works]

Now go forth and query those dimension members…
FrogBlog-Out

© Alex Whittles, Purple Frog Systems Ltd

4 Responses to “Querying dimension members in MDX”

  • Jeff Hadden:

    I’ve been scouring the internet looking for a solution to an MDX query problem. Common business practice would dictate that a hierarchy is more commonly used in pieces, most likely descendants of a specified member(s). This requires some sort of filtering mechanism. SUBCUBE and SUB SELECTS would be an obvious choice if it weren’t for the fact that they always return all members above and below the selected member(s). That kind of defeats the purpose of the filter. This MDX will demonstrate the point. If you toggle SELF to SELF_AND_AFTER the results do not change even though SELF eliminates the descendants.

    SELECT {[Measures].[Sales Amount Quota]} ON COLUMNS,
    [Employee].[Marital Status].MEMBERS
    ON ROWS
    FROM (SELECT
    {DESCENDANTS(
    NONEMPTY(
    FILTER([Employee].[Employees].MEMBERS
    ,[Employee].[Employees].PROPERTIES(“Gender”) = “Female”)
    )
    ,,SELF)} ON COLUMNS
    FROM [Adventure Works]
    )

    What is the best way to filter a parent/child hierarchy in this circumstance?

    • Alex:

      Hi Jeff

      Despite the query results staying the same, changing SELF to SELF_AND_AFTER does actually change the functionality of the query. To explain, run the following queries…

      SELECT {[Measures].[Sales Amount Quota]} ON COLUMNS,
      {DESCENDANTS(
      NONEMPTY(
      FILTER([Employee].[Employees].MEMBERS
      ,[Employee].[Employees].PROPERTIES(“Gender”) = “Female”)
      )
      ,,SELF)} ON ROWS
      FROM [Adventure Works]

      and

      SELECT {[Measures].[Sales Amount Quota]} ON COLUMNS,
      {DESCENDANTS(
      NONEMPTY(
      FILTER([Employee].[Employees].MEMBERS
      ,[Employee].[Employees].PROPERTIES(“Gender”) = “Female”)
      )
      ,,SELF_AND_AFTER)} ON ROWS
      FROM [Adventure Works]

      They return different numbers of rows. The reason is due to the nature of what your query is doing. In the first query we’re finding all female employees. Which includes female employees and female bosses. (‘SELF’ prevents the query from returning subordinates of the female bosses, so the DESCENDANTS function is irrelevant here).

      The second query is finding all descendants of all female employees. This includes female employees, female bosses, as well as all descendants (male or female) for all female bosses.

      In a parent/child hierarchy the values of children are included in the value of the parent. So the value of a female boss is the same whether or not you include her subordinates, unless you ask for the member’s DataMember, which specifically excludes the child values from the aggregation.

      Does that make any sense?

      There are countless ways of writing descendants, filters, etc. for parent/child dimensions. If you let me know exactly what query results you’re trying to get then I can put together a query for you.

      Alex

      • Jeff Hadden:

        The point is on filtering or limiting the Employees parent child hierarchy. You’ve moved the DESCENDANT function to the ROW axis which eliminates the filtering problem I am explaining, but also changes the granularity of the ROW axis. I want to use Employees hierarchy strictly as a filter, unfortunately executing it in a SUBCUBE or SUB SELECT causes the result to always include members above and below the selected members. If you toggle between SELF and SELF_AND_AFTER the results are the same.

        SELECT {[Measures].[Sales Amount Quota]} ON COLUMNS,
        [Employee].[Employees].MEMBERS ON ROWS
        FROM (SELECT
        {DESCENDANTS(
        NONEMPTY(
        FILTER([Employee].[Employees].MEMBERS
        ,[Employee].[Employees].PROPERTIES(“Gender”) = “Female”)
        )
        ,,SELF)} ON COLUMNS
        FROM [Adventure Works]
        )
        How would I predictably limit the members of Employees and use that set to evaluate across another attribute as my original example shows (Marital Status)? I need the option of parametizing between SELF and SELF_AND_AFTER. Unfortunately, the sub select behavior invalidates the DESCENDANT using SELF.

        • Alex:

          Yes I changed the nature of the query in order to explain what your queries were doing behind the scenes. You said they were doing the same thing, which is incorrect. They are doing different things albeit with the same results. Working with any MDX it’s vital to have a clear understanding of what the filtering and functions are doing in order to diagnose any query problems.

          The problem in your example is not that the subselect is invalidating the descendents, it’s that the value for each parent includes the values of all children. Therefore when you’re aggregating the results it makes no difference whether the children are there or not, so the difference between SELF and SELF_AND_AFTER is totally meaningless in your example, and so you shouldn’t be parameriterizing it in the first place!

          e.g. using the adventureworks data…
          The total quota for Amy E. Alberts is $24,202,000. The total value for Amy E. Alberts, and all of her descendants, is still $24,202,000
          If you want the value for JUST Amy, not including her descendants, then you have two options
          1) Use the .DataMember of each employee in the hierarchy, which means use the value of this employee EXCLUDING the values of any children
          2) Don’t use the hierarchy. Just use [Employee].[Employee].MEMBERS instead of the [Employee].[Employees] hierarchy. Then each employee will be treated individually.

          Use the first option when you want to retain filtering on the hierarchy, use the 2nd when you don’t.

          To get the value of the datamember, just use ([Measures].[Sales Amount Quota], [Employee].[Employees].DATAMEMBER) instead of [Measures].[Sales Amount Quota]. To make this work you can’t use a subselect, so you need to restructure the query somewhat.

          Be careful though when using DESCENDANTS on top of a filter statement, you run the risk of double counting values. e.g. a female employee will be counted twice if she has a female boss. This is because both women will be returned from the filter, therefore descendants will operate on them both. So you should use the DISTINCT function to protect against this.

          Basically, the following query should show you a way of doing what you want, leaving you to parameterize the SELF/SELF_AND_AFTER

          WITH MEMBER [Measures].[Individual Quota] AS
          ([Measures].[Sales Amount Quota]
          , [Employee].[Employees].DATAMEMBER)

          MEMBER [Measures].[Quota for Female with subordinates] AS
          (
          SUM(
          DISTINCT(DESCENDANTS(
          FILTER([Employee].[Employees].MEMBERS
          ,[Employee].[Employees].PROPERTIES(‘Gender’) = ‘Female’)
          ,,SELF_AND_AFTER))
          , [Measures].[Individual Quota]
          )
          ),FORMAT_STRING=’Currency’

          MEMBER [Measures].[Quota for Female without subordinates] AS
          (
          SUM(
          DISTINCT(DESCENDANTS(
          FILTER([Employee].[Employees].MEMBERS
          ,[Employee].[Employees].PROPERTIES(‘Gender’) = ‘Female’)
          ,,SELF))
          , [Measures].[Individual Quota]
          )
          ),FORMAT_STRING=’Currency’

          SELECT {[Measures].[Sales Amount Quota], [Measures].[Quota for Female with subordinates], [Measures].[Quota for Female without subordinates]} ON COLUMNS,
          [Employee].[Marital Status].MEMBERS
          ON ROWS
          FROM [Adventure Works]

Leave a Reply

The Frog Blog

I'm Alex Whittles.

I specialise in designing and implementing SQL Server business intelligence solutions, and this is my blog! Just a collection of thoughts, techniques and ramblings on SQL Server, Cubes, Data Warehouses, MDX, DAX and whatever else comes to mind.

Organising SQLRelay

Submit a session for SQLBits