none
Designing cubes using multiple fact tables RRS feed

  • Question

  • I have a question on cube design:

    I have 4 fact tables in my DW. All of them share 2 dimensions and they have private dimensions, 1 each. Facts are at same granularity on shared dimensions.

    For the 20% of my analytical requirements, I will access measures from all of them simultaneously. For 20%, measures from 3 of them and for 30%, measures from 2 of them. Rest of the analytical requirements will need to access only a single fact at a time.

    What would be the best cube design?
    A single cube with four measure groups. Or 4 different cubes and link the measure groups. Or something else?

    Monday, March 1, 2010 8:22 AM

Answers

  • Hi,

    One cube with four measure groups is the best option.

    This approach is easier to maintain and to use when you have calculations that will need more than one measure group.


    HTH
    Thomas Ivarsson
    Monday, March 1, 2010 9:22 AM
    Moderator

All replies

  • Hi,

    One cube with four measure groups is the best option.

    This approach is easier to maintain and to use when you have calculations that will need more than one measure group.


    HTH
    Thomas Ivarsson
    Monday, March 1, 2010 9:22 AM
    Moderator
  • Hi Thomas, Thanks for the reply. We can have calculations using measures from different measure groups even if they are linked measure groups. Performance-wise, will there be any difference? If yes, why?
    Monday, March 1, 2010 9:39 AM
  • Hi,

    Yes you can but linked measure groups are slower than having the measure groups in the same cube. I think that it is quicker to do calculations within a cube than doing it between cubes.

    HTH
    Thomas Ivarsson 
    Monday, March 1, 2010 10:51 AM
    Moderator
  • Thanks Thomas.

    I was wondering how would be the performance of a cube with multiple measure groups vis-a-vis a cube with single measure group when I access only 1 measure group at a time? Will those additional measure groups call for more resources? Can you throw some light on how SSAS load measure groups when a cube is accessed?
    Tuesday, March 2, 2010 9:40 AM
  • Hi,

    Think of a cube as the intersection of each dimension's attributes and attribute members with all measures. A cube with many measure groups is probably larger than a cube with single measure group depending on the number of measures.

    HTH
    Thomas Ivarsson
    Tuesday, March 2, 2010 12:01 PM
    Moderator