MDX Distinct Count

[foreveryoung]

Can someone help me to construct an MDX expression. Its easier if I explain the final result.

For admissions to hospital. Number of unique attendances as rows and months for a specific year as columns. The measures to show in the middle would be number of unique patients. So from the above I could easily identify, for example that 10 unique patients had 6 attendances in April 09.

I originally tried to do this by constructing a dimension of unique attendances (rows) and a distinctcount of patient IDs as the measures. I got kinda stuck then as the results were proving to be incorrect when browsing the cube.

I hope this makes sense and that there are some MDX experts out there that can help.

Many thanks
David

 

[RiverGuy]

Can you describe what you mean by "unique attendance"?

 

[foreveryoung]

Sorry what I meant was number of attendances as a dimension rather. Eg below

Attendances April May Jun ...
3 4 5 3
7 1 6 1
2 2 0 12

So for example from above 6 Unique patients attended 7 times in May.

This was my attempt

select [NumberOfUniqueAttendance].members on rows,
{[Time].[All Time].[2009/10].[Q1].[April] : [Time].[All Time].[2009/10].[Q4].[March]} on columns
from ae_all
where 
[Measures].[No_of_Unique_Patients]

but it does not work correctly. Hope this is helpful

 

[RiverGuy]

Well, firstly, you will definitely need that Attendance Range dimension. I say range because I see a definite possibility to a hierarchy here. For example, 1,2,3,4 & 5 Attendances could roll up into the 01-05 Attendances range.

Secondly, I would design a fact table to track the Attendances by Patient, by Time Period. Let's say your Time grain is a month. Your fact table would look something like this:

factPatientAttendances
------------------------
PatientKey INT
MonthKey INT
AttendanceRangeKey INT
NumberOfAttenadances INT

From here, you should be able to do different types of analysis. You could get the total number of attendances per patient. You could get the count of patients for each range per month. Or, if you want to look at this at a higher level of time, you could get a Distinct Count of patients who have had attendances in a monthly range.

 

[foreveryoung]

And use the fact key in a many to many relationship as defined in the dimension usage?

Thanks Mate