Search code examples
sqlssasmdx

SSAS how to turn the following SQL script into MDX?

I want to calculate the daily number of children as a measure in SSAS. The logic should be written in SQL as follows:

Select Count(distinct ChildID)
From Child
Group by CurrentDate

How could I translate this script into MDX for calculation? I'm new to SSAS.

Solution

  • It depends a lot on how your dimensions are set up but shooting from the hip you could set up a measure that is a count of childid. To do that in SSAS under the cube structure create a new measure and select count under the usage property and the proper table under the source table. You could call this measure Child Count or something like that.

    With a distinct child count measure set up the MDX would be something like this:

    SELECT NON EMPTY 
{ [Measures].[Child Count] } ON COLUMNS, 
{ ([Dim Child].[CurrentDate].[CurrentDate].ALLMEMBERS ) } ON ROWS 
FROM [Your Cube]