MDX - Counting Members at a Hierarchy Level

Counting Members in an MDX Query

MDX is a challenging query language. It is completely different to the SQL query language despite having a SELECT statement!

MDX queries are all about hierarchies, measures and members and when it comes to carrying out tasks that are simple in SQL it may not be so simple in MDX.

Most of the time when we work with MDX queries we are interested in bringing out aggregate values that are stored in our cube, but occasionally we may want to derive values from the stored cube data.

In the following examples I will be using the Microsoft sample cube, based on the AdventureWorksDW2012 database.

Counts Stored as Measures

Take the following example, In the Internet Customers Measure Group is a measure called Customer Count, which is a count of the customers in the cube data. (Customers are related to Internet Sales in the AdventureWorks cube).

The first MDX query simply retrieves the Customer Count measure from the cube:

SELECT [Measures].[Customer Count] ON 0
FROM [Adventure Works]

Counts Derived With The MDX Count Function

The same result as the measure value above is obtained by using the MDX Count function with the leaf level of any of Customer dimension hierarchies that go down to the customer member level:

WITH MEMBER Measures.x AS [Customer].[Customer].[Customer].Count 
SELECT Measures.x ON 0
FROM [Adventure Works]
--18484

WITH MEMBER Measures.x AS [Customer].[Customer Geography].[Customer].Count 
SELECT Measures.x ON 0
FROM [Adventure Works]
--18484

The Count function counts the number of members at the sprcified hierarchy level. Here is an extract from the [Customer].[Customer].[Customer] attribute hierarchy level:

The following example will only produce a count of 2 rather than 18484:

WITH MEMBER Measures.x AS [Customer].[Home Owner].[All Customers].Children.Count 
SELECT Measures.x ON 0
FROM [Adventure Works]

The reason for this? well the Home Owner attribute hierarchy only has two leaf members: Yes and No.

So for cases where there is no measure in the cube for a count of members we can use the Count function, as in the case of the Products. There is no measure in the cube for a count of the number of product members that exist. The following MDX query can be used to produce this figure:

The following two queries both return the same result (397 products), using two different hierarchies:

WITH MEMBER Measures.x AS [Product].[Product].[Product].Count 
SELECT Measures.x ON 0
FROM [Adventure Works]
--397
GO

WITH MEMBER Measures.x AS [Product].[Product Categories].[Product].Count 
SELECT Measures.x ON 0
FROM [Adventure Works]
--397

Combining The MDX Count Function With Other Functions

The Count function can be appended to any MDX set, including those created by an MDX Set Function. The following example counts the members in a set created by the Descendants function:

WITH MEMBER Measures.x AS Descendants([Product].[Product Categories].CurrentMember,
			              [Product].[Product Categories].[Product]
				     ).Count 
SELECT Measures.x ON 0,
	[Product].[Product Categories].[Category] ON 1
FROM [Adventure Works]

The following screen shot shows the results from this query:

The Descendants function returns the set of all members at the product level in the Product Categories hierarchy, for the given category (CurrentMember in the measure expression takes on  each category identified by the [Product].[Product Categories].[Category] tuple in the SELECT axis 1.

In other words we are counting the number of products in each product category.