MDX Query to show number of child members in a Dimension

I was validating some assumptions about my dimensions’ data distribution and hierarchies and wrote some MDX to confirm the number of members. You could also do this by writing a SQL query against the relational table, but in this case I definitely wanted to see exactly what was coming from the OLAP dimensions.

Here’s the same query morphed to work with Adventure Works:

WITH
  MEMBER [mbrcnt] AS [Date].[Calendar].Children.Count
  MEMBER [mbrcnt2] AS Descendants([Date].[Calendar].CurrentMember, 2).Count
SELECT
  {[mbrcnt],[mbrcnt2]} ON 0
  , FILTER([Date].[Calendar].Members, [mbrcnt] > 0) ON 1
FROM
  [Adventure Works]

This only returns rows for members that have more than 1 child member.

Leave a comment

Your email address will not be published.