Counting the rows in a dimension

This should be extremely simple, but I haven't found the answer yet in any MDX reference.

How do you count all the rows in a dimension using MDX?

I need to do the equivalent of this SQL below. (CustomerID is NOT the primary key by the way.)

SELECT COUNT(DISTINCT CustomerID)

FROM DimCustomer

My eventual goal is to find the count of all customers over any given time period like so:

SELECT COUNT(DISTINCT CustomerID) FROM DimCustomer WHERE

CustomerOpenDate <= CONVERT(DATETIME, '5/1/2006') AND CustomerCloseDate >= CONVERT(DATETIME, '5/31/2006')

I believe I can use Time.CurrentMember for the comparrison, correct?

Thanks,

Terry

Hi Terry,

From your problem description, you may be able to just use the standard "DistinctCount" measure aggregation function (assuming that you're using AS 2005): suppose you create a Measure Group on the DimCustomer table, with CustomerCount as a Distinct Count on the CustomerID field. If you have a Date dimension down to the daily level (similar to Adventure Works), it can be related to the this measure group via 2 roles: CustomerOpenDate and CustomerCloseDate. The query could then be like:

select {[Measures].[CustomerCount]} on 0

from CustomerCube

where (NULL:[CustomerOpenDate].[Day].[05/01/2006],

[CustomerCloseDate].[Day].[05/31/2006]:NULL)

Of course, depending on the day range sizes, these could be optimized to use higher levels of the hierarchy.

|||

Thanks for your help. I think what I'm really trying to get to is a calculated measure with the where clause for the CustomerOpenDate and CustomerCloseDate built into it so that the customer count will total correctly whatever the the current date granularity is.

I think I can can handle the where clause part (although your syntax of "NULL:" and ":NULL" is something I haven't seen before), but I can't get the syntax for doing a distinct count on Customer.CustomerID in the calculated measure. I get error messages with the various ways I've tried it saying that it's either not a tuple or that no hierarchy has been specified.

|||

Hi Terry:

I think you're looking for something like the following the calculated member. The following query works against the Adventure Works DW so you can exercise it and see the results. The calculated member, Measures.[My Customer Count], counts the distinct number of customers who had sales activity during the period selected. I imply sales activity by filtering on the [Internet Sales Amount].

Hope this helps.

PGoldy

WITH
MEMBER Measures.[My Customer Count] AS
'
DISTINCTCOUNT(FILTER([Customer].[Customer Geography].[Customer].Members * [Date].[Calendar].CurrentMember, NOT ISEMPTY([Measures].[Internet Sales Amount])))
'

SELECT
{Measures.[My Customer Count]}
ON COLUMNS
,{[Date].[Calendar].[Month].&[2003]&[4]
,[Date].[Calendar].[Month].&[2003]&[5]}
ON ROWS
FROM [Adventure Works]

|||Thank you for your help. I still haven't pulled it all together yet, but I'll look into it some more using the help you've provided.