Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Best Way to Model a role playing Dimension if you want to search all columns (OR logic)

Answered Best Way to Model a role playing Dimension if you want to search all columns (OR logic)

  • Tuesday, February 19, 2013 12:54 AM
     
     
    Sign In to Vote
    0

    Hi,

    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?

    Thanks,

    Chris

     

All Replies

  • Tuesday, February 19, 2013 2:19 PM
     
     Answered
    Sign In to Vote
    0

    Hi there,

    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.

    http://www.sqlbi.com/articles/many2many/

    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.

    cheers,

    Andrew

    Andrew Sears, T4G Limited, http://www.performancepointing.com

    • Marked As Answer by Smithochris Wednesday, February 20, 2013 3:04 AM
    •