Data Warehouse Dimension Design for Factless Fact Table


  • I have a question regarding a new Cube i am starting.

    The cube is related to a risk management process. the fact table wont hold any facts, just the list of Risks present.

    I have a question regarding Dimensions to this table, there are a lot of attributes for a Risk ex:

    Probability of Occurrence Before Project Start: (High, Med, Low)

    Probability of Occurrence During Project : (High, Med, Low)

    Probability of Occurrence After Project End: (High, Med, Low)

    Severity of Risk : ( High, Med, Low) Strategy: (Accept, Reduce, Share)

    I am not sure if i should treat these types of attributes as Dimension or just leave them as part of the Fact Row. Then users can filter by the values.

    And if they should be Dimensions how to handle them:

    1) Fact Dimension ( degenerate dimensions)

    2) New Dimensions ( move data into its own tables)

    3) Combine Dimension of similar concept, ex: Probability of Occurrence into one Dimension and add a Type Attribute [note: there are alot of these types of attributes that i did not mention]

    Any help is appreciated

    • Edited by ame54 Tuesday, June 25, 2013 12:20 PM
    Tuesday, June 25, 2013 12:19 PM


  • If you want filter data then it has to be a dimension. Consider the below to approaches,

    1. Since I see Project, let me assume it as a dimension, create snowflaked dimension with the project

    2. Create a dummy measure in the fact table with value populated as 1 or 0, and then create a fact dimension

    You might Which is bettter is a matter of discussion !.  If you ask me I would say Option 1.

    Monday, July 01, 2013 7:45 AM