none
fact table design question RRS feed

  • Question

  • SSAS 2005 - I have the following 3 tables:

     

    T1:  dimA_id, dimB_id, dimC_id, prod_id, dollar_amt_1

    T2:  dimA_id, dimB_id, dimC_id, cat_id, dollar_amt_2

    T3:  cat_id, prod_id.

     

    cat_id and prod_id has a parent to child relationship.

     

    In this case, I have to build two fact tables, right? There is no way to combine T1 and T2 into one fact table because the dollar_amt 1 and 2 are at a different level of prod_id and cat_id. I just wanted to make sure that I am doing the right thing. It seems that there are repeated data (dimA_id, dimB_id, dimC_id) in both tables.

     

    Thanks.

    Thursday, July 12, 2007 11:52 PM

Answers

  • But if cat_id/prod_id are modelled as a Parent-Child dimension (ie. using a single key for members at all levels, with a parent key), then data could be loaded at both levels from a single fact table.
    Friday, July 13, 2007 2:40 AM
    Moderator

All replies

  • But if cat_id/prod_id are modelled as a Parent-Child dimension (ie. using a single key for members at all levels, with a parent key), then data could be loaded at both levels from a single fact table.
    Friday, July 13, 2007 2:40 AM
    Moderator
  • Good point. I will give it a try. Would this be a better appoach than the two fact-table one?
    Friday, July 13, 2007 3:38 PM
  • Should be more straightforward, at least - but large parent-child dimensions can cause performance problems. If this dimension is small, it may not be a concern.
    Friday, July 13, 2007 3:44 PM
    Moderator
  • The cat table has more than 1/2 million records and the prod table is 2-3 times bigger than the cat table. Is this considered large?
    Friday, July 13, 2007 4:02 PM
  • According to the AS 2005 Performance Guide, that is large, but you could try it and check:

    Parent-child hierarchies

    Parent-child hierarchies are hierarchies with a variable number of levels, as determined by a recursive relationship between a child attribute and a parent attribute. Parent-child hierarchies are typically used to represent a financial chart of accounts or an organizational chart. In parent-child hierarchies, aggregations are created only for the key attribute and the top attribute, i.e., the All attribute unless it is disabled. As such, refrain from using parent-child hierarchies that contain large numbers of members at intermediate levels of the hierarchy. Additionally, you should limit the number of parent-child hierarchies in your cube.

    If you are in a design scenario with a large parent-child hierarchy (greater than 250,000 members), you may want to consider altering the source schema to re-organize part or all of the hierarchy into a user hierarchy with a fixed number of levels.

    Saturday, July 14, 2007 12:43 AM
    Moderator