Answered by:
Designing cubes using multiple fact tables
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?
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 Marked as answer by RaymondLeeModerator Monday, March 15, 2010 1:55 AM
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 Marked as answer by RaymondLeeModerator Monday, March 15, 2010 1:55 AM



Thanks Thomas.
I was wondering how would be the performance of a cube with multiple measure groups visavis 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? 
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 Proposed as answer by Charles Wang  MSFTModerator Tuesday, March 9, 2010 11:40 AM