we follow a general rule that there should be one fact table for each context.But i have 2 fact tables a header table and a detail table and the reports need fields from both the tables.How do i design my universe in this case.
So you have Dimension -< Header Fact -< Detail Fact?
Create an alias of Header Fact so you now have four tables in the universe:
Dimension (DIM)
Header Fact (HF)
Header Fact Alias (HFA)
Detail Fact (DF)
Join DIM to HF 1:n
Join HF to HFA 1:n
Join HF to DF 1:n
If you detect contexts based on that it will offer you two contexts to accept
Header Fact Alias will contain the first two listed above and Detail Fact will contain the first and third.
In terms of your objects that are sourced from Header Fact, source measures from Header Fact Alias and dimensions from Header Fact. This avoids the fan trap that you had - creating a chasm trap is the standard way of avoiding fan traps and contexts are used to manage the chasm trap.
You could, however that’s not practical because you would need to create the same set of objects from both dimensions - the original Dim as well as from the AliasDim.
The alias means that you can use Header and Detail information together.
Let’s look at an example.
A header has a code, a cost value and and delivery date with values of:
H123, 120.00, 5/5/2012
Say a header has three detail lines. It has a header code, a line code, a product code and a sale value. The three lines are as follows:
H123, L456, P12, 100
H123, L457, P13, 99
H123, L458, P14, 50
Thinking back to the example I gave and consider the aliases.
In my universe, I’d have objects defined as:
Header Code = HF.Header_Code
Header Date = HF.Header_Date
Header Cost = sum(HFA.Header_Cost)
Line Code = DF.Line_Code
Line Product Code = DF.Product_Code
Line Sale Value = Sum(DF.Sale_Value)
Let’s assume Header Code is unique across the business and I wanted to know the profit (or loss) for each header delivered on 5/5/2012.
If you don’t alias header and just use it all in one sql query, then you’d get three rows, each with a Header_Cost of 120. That would drag me into a fan trap and falsely report that header H123 had a cost of 120 * 3 = 360 with a sales value of 100+50+99 = 249, reporting a loss of 111, when in fact there was a 129 profit made.
If that isn’t clear, then please say.
As Marek says, not a recommended solution. You don’t alias a dimension that means the same thing to both facts; it completely defeats the object of conformed dimensions.