Monthly Archives: November 2012
Querying dimension members in MDX
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