Tuesday, February 19, 2013 12:54 AM
I have data that contains a primary, secondary, and tertiary diagnosis. All diagnoses come from the same dimension table (the diagnosis dimension is a role playing dimension). This creates a structure in the OLAP cube that allows me to search 1 diagnosis at a time. For example, if I want to find all patients with a diagnosis of "headache", I need a pivot on the primary diagnosis, another pivot on the secondary diagnosis, and 3rd pivot on the tertiary diagnosis. If I filter "headache" in the primary, secondary, and teritiary dimensions in the OLAP cube, it will of course give me patients that have "headache" in all three diagnosis fields.
How can I restructure the model so that I can search for all patients with a diagnosis of "headache", whether it was in the primary, secondary, or tertiary column?
Tuesday, February 19, 2013 2:19 PM
One way would be to union the primary, secondary, and tertiary diagnosis keys in the fact as a single key. Then you should be able to have a single dimension for that key which you can then filter on.
Another option would be to have a many to many design.
If you need to split between primary, secondary, and tertiary, you could have another dimension which controls this, or modify the existing dimension to include an attribute / hierarchy.
Andrew Sears, T4G Limited, http://www.performancepointing.com
- Marked As Answer by Smithochris Wednesday, February 20, 2013 3:04 AM