Hi
I've just started using SSAS2005 in a health application.
My main Fact table is a Patient Details table with Patient ID as primary key. The cube built around it uses age and gender as dimensions, with median age and Male-Female ratio as some of the measures.
Now in the same Data Source View I've added a Treatment Fact table with multiple treatment rows per Patient ID, each row containing treatment details and the name of the hospital which provided the treatment.
How do I create a dimension or link between these two tables so that in the same cube I can summarize, by hospital, both the number of treatments (and other treatment-related measures and dimensions) as well as the characteristics (age/gender) of patients treated in each hospital? Sounds like a many-to-many relationship but I don't know how to implement it in this case.
Thanks in advance.
You could set up a Patient measure group with a PatientCount measure and a Treatment measure group with a TreatmentCount measure. Age and Gender dimensions are directly related to the Patient measure group, and Hospital to the Treatment measure group. If you then create a PatientDetails fact dimension for the Patient measure group, which is also directly related to the Treatment measure group, the Hospital dimension can have a Many-to-Many relation with the Patient measure group, via the PatientDetails intermediate dimension. You can refer to the Many-to-Many relation of SalesReason to Internet Sales in Adventure Works, for a similar example.|||
Thanks Deepak, that worked. Much appreciated.
