Fact table design question RRS feed

  • Question

  • I had a huge fact table with the following structure.

    fact1 (Fact1ID, D1, D2,... M1, M2, M3....)
    where PK is Fact1ID, D1, D2,... are dimension key and M1, M2, M3 are measures.

    Now I need add a new fact table fact2, which has many to one relationship ([1-365] to 1) to fact1,

    fact2(Fact1ID, Date, Dx, Dy,..., Mx, My....),
        which Fact1ID and Date are PK of this fact table. (fact2 are the daily data of fact1)

    Some reporting queries need to join these two huge table and they are very slow. Any better design?
    Monday, January 28, 2008 10:07 PM

All replies

  • Let me first see if I understand your model.  Fact1 has dimensions D1, D2, etc. and has a fact dimension (Fact1) based on Fact1ID.  Fact2 has dimensions Dx, Dy, etc. as well as dimension Fact1.  In classic Kimball terminology, Fact1ID is a degenerate dimension.


    In your cube, you create two measure groups, Fact1 and Fact2.  Both measure groups have relationships to the Fact1 dimension.  You are then using many-to-many relationships between the Fact2 measure group and dimensions D1, D2, etc. using the Fact1 measure group as your intermediate fact table?


    Here are some ideas.  First, make sure you have an adequate partitioning strategy in place for your two measure groups.  Next, make sure you have aggregations built on all these.  If these are all in place and your performance is still not adequate, consider "migrating" the D1, D2, etc. references to Fact2 to eliminate the many to many relationship.  To do this, replace Fact2 in your DSV with a query that joins Fact2 to Fact1.  Add the dimension references from Fact1 to the Fact2 table. 


    Hope that helps,


    Wednesday, January 30, 2008 3:18 PM