Perhaps that should be - Do as I say, not as Adventure Works and Project Real do
SSAS modeling question: What's the best approach to handling header/detail records?
Approach #1) Use Measure Groups
Approach #2) Consolidate via either ETL or relational operations
Most clients I deal with have some form of Header/Details modeled in some of their source systems (for example Invoices/Line Items). In looking at the way MS addresses this in sample databases (AdventureWorks and Project Real), I discovered that these examples solve this problem by not solving it. Both contain a single SalesFact that presents values from what would be the header record, but also include Item measures and attributes.
Conventional SSAS wisdom would have us create 2 measure groups, 1 for Header and 1 for Details. The approaches used in the MS example cubes consolidate the two differently grained tables relationally and thus use a single measure group. (More correctly, they simply present a single Sales table, but it's reasonable to assume that a real-world system would have both Header and Detail)
I definitely know how to model this relationally, and I know how to implement this using either measures groups or a single table. I can see pros and cons to each approach:
Using Measure Groups Approach
- Stays "pure" each fact has correct grain
- Calculations must be modeled in MDX
- Provides most efficient movement of relational data
- Minimizes null or N/A columns
- Most orthodox
Consolidate via either ETL or relational operations
- Allows row-wise calculations
- Simplifies cube schema
- Removes need for "SalesOrder" dimension (needed to relate details to headers)
My current opinion would be to use proper measure groups because creating a relational structure with multiple grains violates one of Dr. Kimball’s fundamental tenets.
As is often the case with BI modeling, there may well be cases where the second approach proved more advantageous. But absent of a specific requirement that highlights any advantage, I will stick with established best practices, which to my thinking is Kimball. I can understand why Adventure Works takes a short-cut, but it seems a shame that "Project Real" isn't really "Real" so-to-speak. It's supposed to be a real world model.
