Fact Table Many-to-Many Relationship

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.

Fact Dimension Relationships on Partitioned Cube

Hi,

I have Fact Dimension Relationship on my cube, my concern is that the source of the dimension (Invoice Number) is just the 1st Partition of the cube. Should I only use a single partition on my cube to use the Fact Dimension Relationship?

What is the best practice to implement a Fact Dimension Relationship on a partitioned cube?

Thanks,

Renold

If you decided to build your fact dimension based on the same table as your partitions are, you should create a view joining all paritition tables and base your fact dimension off that view.

Otherwise the fact dimension is only going to show the invoice numbers that appear only in the first partition table.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.