Thursday, September 08, 2011

Defining Dimension Granularity within a Measure Group

When dealing with multiple Measure Groups in a cube, you could have items repeating if they are at a higher level than the detailed rows.  Here are a couple articles and MSDN info identifying best practices around this.

All but the simplest data warehouses will contain multiple fact tables, and Analysis Services allows you to build a single cube on top of multiple fact tables through the creation of multiple measure groups. These measure groups can contain different dimensions and be at different granularities, but so long as you model your cube correctly, your users will be able to use measures from each of these measure groups in their queries easily and without worrying about the underlying complexity.

Users will want to dimension fact data at different granularity or specificity for different purposes. For example, sales data for reseller or internet sales may be recorded for each day, whereas sales quota information may only exist at the month or quarter level. In these scenarios, users will want a time dimension with a different grain or level of detail for each of these different fact tables. While you could define a new database dimension as a time dimension with this different grain, there is an easier way with Analysis Services.

Defining Dimension Granularity within a Measure Group

No comments: