BusinessObjects Board

Universe Design Issue

building semantic layer
#1

Hi
I have a scenario like

D1 —> F1 ------> F2 <-----D2

I need to create report from D1, D2, M1 (FROM F1) AND M2 ( FROM F2).
F1 --> F2 has 1 to many cardinality.

I’m getting incorrect results for M1 (higher values)

LOOKS like FAN TRAP…

Created Alias for F1 as F1_Alias, and created contexts like

CONTEXT1 : D1–>F1_Alias

CONTEXT2 : D1 —> F1 ------> F2 <-----D2

M1 is from F1_Alias.

Even I’m getting incorrect results.

Could you please help to understand and fix the problem.

Thanks
Venkat

venkat4838 (BOB member since 2013-03-19)

#2

What does the generated SQL look like? You should be getting two queries, one with D1 and F1_Alias, and another with D1, F1, F2, and D2.

joepeters :us: (BOB member since 2002-08-29)

#3 
CONTEXT2 : D1 ---> F1 ------> F2 <-----D2

Why are you directly joining the fact tables together? General rule of thumb, one context per fact.

Mak 1 :uk: (BOB member since 2005-01-06)

#4

To add to what Mak1 has said, perhaps you have F1 as a mixture of facts and dimensions, for example an order header.

What you should do is have two contexts.
Alias F1 (let’s call it F1A for now)

Context 1:
D1 -< F1 -< F1A
Context 2
D1 -< F1 -< F2 >- D2

Any measures from F1 should be changed to come from F1A
Any dimension from F1 should stay with F1 because they are still relevant to F2

#5

Hi Mark,

I would usually have a seperate context one for the header and one for the lines.
We have both discussed this a long time before :mrgreen:, here:-

https://bobj-board.org/t/166900

However your method would still generate the correct results.

On another note, I’m trying to organise :cheers: for next week, if you are game.

Cheers,

Mark.

Mak 1 :uk: (BOB member since 2005-01-06)

#6

Thank you Mark and Mak1. Issue resolved.

venkat4838 (BOB member since 2013-03-19)

#7

The way I’ve done it, there are separate contexts for the header and the lines - header is F1A…

#8

Mine is slightly different, in the fact that Orders is a seperate physical table from Order lines. However, both models would produce the correct results :).

Mak 1 :uk: (BOB member since 2005-01-06)