BusinessObjects Board

Context help

building semantic layer
#1

Hello everyone,

I hope I can explain this correctly (it is my first post)

I have the folowing situation

Fact 1 and Fact 2.

Fact 1 has multiple dimension linked to it. The Fact 2 has to be added.
I have been advised by the DBA to link both fact tabels as follows.

Fact1 <–Fact2 on two keys. Why? To make sure that the Fact1 records will show up on my report even when these
do not have a matching Fact2 record.

On fact 1 I have multiple measures.
Fact 2 has one measures and a description

The measures from the 2 fact tables are not at the same level.
I have created two contexts. When I select Fact1 measure and Fact 2 measure those will be displayed in two tables which
is not what I or my users are looking for.
I have tried making an alias of Fact 2 and base my Fact2 measure on that.
They are being displayed in one table but then my amounts are doubled or tripled and so on.

An example is

Id1 Fact1 Measure Fact2 Measure Fact2 DetailDescr.

001 300.000 0.5 Discount1
002 300.000 0.9 Discount2

With other words when a user will make a sum on Fact1 measure will get the wrong results :slight_smile:
What I am looking for is that the Fact1 measure will appear only once in case there are more then one Fact2 DetailDescr

Can you please advise?

Thank you in advance.

Elena77 (BOB member since 2006-10-20)

#2

Hello,

it looks that you are experiencing a fan trap.

Please have a look at this post where Andreas gave an excellent description of what fan trap is and how to solve it.

Look also at this Designer’s FAQ.

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

#3

Thank you Marek!

I will try again tomorrow. I have done the same think today by I have included de join between Fact1 -->Fact 2 in both context. Maybe that is the reason why it did not work. :roll_eyes:

I have created an alias on Fact 2 and changed the definition of the fact2 measure to look at the fact2 alias.

Elena77 (BOB member since 2006-10-20)

#4

I think that I have made a mistake in my example. :hb: My situation is as follows:
FACT1 FACT2 DETAIL

FACT1.ID1 -------< FACT2.ID1 DETAIL.DESCR1
FACT1.ID2 -------< FACT2.ID2 DETAIL.DESCR2
FACT1.MEASURE1 FACT2.MEASURE2 DETAIL.DESCR3
FACT2.DESCR_ID >------- DETAIL.DESCR_ID

ID1 MEASURE2 FACT2.DESCR_ID DETAIL.DESCR1

001 300.000 31 DISCOUNT1
002 300.000 25 DISCOUNT2
003 300.000 12 DISCOUNT3

THE WANTED SITUATION

ID1 MEASURE2 FACT2.DESCR_ID DETAIL.DESCR1

001 300.000 31 DISCOUNT1
002 0 25 DISCOUNT2
003 0 12 DISCOUNT3

Can you please advise?

Thank you in advance!

Elena77 (BOB member since 2006-10-20)

#5

I think you need 2 contexts here, if I understood your design properly, FACT1 —< FACT2 >— DETAIL.
The 2 contexts would be:
1: FACT1 —< FACT2
2: DETAIL —< FACT2.
And make sure you’ve ‘Multiple SQL statements for each context’ checked.

Luckys :united_arab_emirates: (BOB member since 2006-08-02)

#6

Hello,

Thank you for the reply. I ahve added a document that shows how my tables are linked.

I have tried making a n alias of fact 1 and base all my measures on that…it is not working. I have tried to make an alias on fact 2 and it is also not working.

Knowing that others have got something like that working…I am doing something wrong and i do not know what.

Thank you!
FanTrap.doc (147.0 KB)

Elena77 (BOB member since 2006-10-20)

#7

I think you are creating unnecessary complications, looking at your design all you need here, are 3 tables. namely, D_TIME, FACT, and D_DETAIL. having linked as D_TIME -< FACT >- D_DETAIL, so your tables would look like:


D_TIME
---------
DATE_ID
, CALENDAR_DATE

FACT TABLE
--------------
DATE_ID
, LOAN_NR
, LOAN_COST
, DISCOUNT
, DISCOUNT_REASON_ID

D_DETAIL
-----------
DISCOUNT_REASON_ID
, DISCOUNT_DESCR

Luckys :united_arab_emirates: (BOB member since 2006-08-02)

#8

Thank you
i have the second fact table too and that has to be linked with fact table 1 :cry:

Elena77 (BOB member since 2006-10-20)