SSAS Relationships and Granularity

The creation of measure groups at varying levels of granularity is used when two related measures differ by some dimensional level. A classic example of this occurs in budget comparisons to actual reporting. In this situation, the budget data is typically stored at a higher level of granularity. For example, it may exclude product information (or be set at high level product classes) and be targeted at monthly totals. Actual data is presumed to be recorded at a detail sale level (where { date x product x store x ref# } gives { revenue & quantity sold }). This post looks at how attribute relationships determine how data is aggregated in dimensions where measure groups are related to dimensions through non key attributes.