BusinessObjects Board

Report requires data from 2 fact tables

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.

Thanks,
–H


hourigan (BOB member since 2007-04-06)

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.

Hope that clears it up for you.

Regards,
Mark

Hi Mark,

Could you please clarify below:

  1. What is the use of creating Alias of Header fact ? Not getting use of it.

  2. Can we create Alias of Dimension table say AliasDim and create below two contexts to resolve the issue:

Dim---->Header fact
AliasDim---->DetailFact


chinmay1383 :india: (BOB member since 2010-05-11)

Hi,

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.


Marek Chladny :slovakia: (BOB member since 2003-11-27)

Thanks Mark… That’s true.

But if consider first case, I am bit confused as why we are creating Alias for Header fact table? Wt purpose does it solve?

My apology if it sounds very basic level doubt


chinmay1383 :india: (BOB member since 2010-05-11)

  1. 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.

  1. 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.

Thanks Mark. Concept is clear with your example.

Appreciate your time and help :smiley:


chinmay1383 :india: (BOB member since 2010-05-11)

And you can read more about a fan trap here:

Designer’s FAQ:

A good explanation of a fan trap by Andreas:

Few of my posts:


Marek Chladny :slovakia: (BOB member since 2003-11-27)