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.

This entry was posted in Analysis Services, Business Intelligence, MDX, SQL Server. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *