BusinessObjects Board

Contexts issue

building semantic layer
#1

Hi,

Can some one please help me on this issue.

I have one fact table and some 3 dimension tables. ( i have more tables in my uni but i have question on this). Lets name the fact table f, dimesnion tables A,B and C.

A is related with F table with one to many relation.
Table C is the association of A and B tables.
A is linked to C with one to many.
B is linked with C with one to many.

I i look at the above relation I have one chas trap.
A -> F (one to many) (created one context for this)
A -> C (one to many) (created 2nd context for this)

When I build the report using the deski and include objects from both contexts, it is not allowing me to put the fields in the same table. The common objects for the F and C tables are in A. since in different contexts I am having this issue. how to resolve this and please provide me the solution.

Thanks

REACHLP (BOB member since 2008-10-06)

#2

One to many realtionship will only cause a fan trap. For better understanding go through the Designer FAQs in BOB.

sijasethu :india: (BOB member since 2009-01-07)

#3

Traps explained - https://bobj-board.org/t/15227/14

sijasethu :india: (BOB member since 2009-01-07)

#4

Hi sijasethu,

Thanks for your help.

Chasm trap is formed, not the Fan trap becoz 2 many to one relations converge on Table A.

Thanks

REACHLP (BOB member since 2008-10-06)

#5

It’s not a problem that can be solved on the report without some sort of restriction on the data.

A fan trap can be resolved because the data ultimately “fits together” once you resolve the measure issues. A chasm trap really doesn’t have a solution, because by the nature of the relationship you simply cannot put F and C together.

Here’s a real-world example. I have a fact table with sales information. I have a product table with inventory items in it. Clearly each item can have more than one sales transaction, so there is a one-many relationship there.

Items are made of components. I have a component table that relates to the item table as well. There is a one-many relationship where multiple components go into the finished goods (items) that are sold.

Now, can I determine sales of the components? No, because components are not sold. And there are multiple components in each item, and each item has multiple sales. If an item has ten components and ten invoices then the combination of the results will show 100 rows, resulting in drastically overstated sales.

The problem doesn’t go away when you get the data on the report, which is why you’re not able to combine data from the two sides into a single report block.

Dave Rathbun :us: (BOB member since 2002-06-06)

#6

Hi Dave,

Thanks a lot for the example.

I have one more question about chas trap. Please bare with me.

I have 3 tables:
Fact table ‘F’: which has all the meassure and date fields.
Look up table ‘E’ and look up table ‘H’: ‘E’ is parent table to ‘H’. And I have some messures in table ‘H’.

‘E’----> ‘F’ with one to many. (one context created here)
‘E’-----> ‘H’ with one to many. (2nd context on this)

for the above relation designer propose 2 contexts.

1.What happens if I select objects from 3 tables and including measures from ‘F’ and ‘H’? is it the right way to do it?

2.What happens if I do not create contexts and select the objects from 3 tables and including measure?

3.What if the child table ‘H’ has only dimensions and mesures only in Fact, then do I still need to create context?

4.Please explain me when to go for contexts? even though designer detects contexts is it ok to ignore them?

I read the posts on the forum but I don’t know I am still confusing with my situvation. Please help me out. I would really appreciate your help.

Thanks

REACHLP (BOB member since 2008-10-06)

#7

With contexts you will get two sql statements generated. The answers should be correct. WIthout contexts you will get one sql statement generated and the answers likely will be wrong.

See above

Yes, because the dimension rows from “H” will inflate the result rows from your fact unless they are in a separate query.

Basically the only “safe” option you have in your example is to create two contexts. The best case scenario would have only measures from F and H and all dimension values from E but that may not be possible. Either way (measures only or measures + dimensions) you need contexts.

Try it without them and it will help you learn what’s going on. :slight_smile:

Dave Rathbun :us: (BOB member since 2002-06-06)

#8

Dave,

Thanks a lot for the quick response and clarifying my questions. I am really sorry to bother you. I have one more issue, please see below.

I have created 2 contexts, for table E-F and for E-H. Now I have one more problem.

In my report I want to pull:

3 dimensions say a,b,c from Table E

2 dimensions say d,e and 3 measures say f,g,h from table F ( I do have date fields in my fact table, in my reports I may need to pull date fields from fact with other objectd from other 2 tables)

3 dimensions say i,j,k from table H.

Since all these 8 objects are coming from 2 different contexts, I have created 2 queries.

1st Query includes objects from E & F tables: a,b,c,d,e,f,g,h.
2nd query includes object from E & H tables: a,b,c,i,j,k

Now I could not able to put all these 8 objects in one single block, though I merged the common dimensions. I am able to put a,b,c,f,g,h,i,j,k but not d,e objects. Since d,e are not common to the 2nd data provider.

In deski I can create detail objects for d,e and combine all 8 in one single block, but in webi if i create detail object I get “Data Sync” and “Context” errors. Is it the right approach to create a detial objects and combine.

Why the detail objects works in deski but not in webi?

My universe has one fact table and some look up tables are directly related to fact with one to many with out any child tables, but for some look up tables I have one child table with one- to-many which is forming chasm traps. So for each lookup-fact-child table i am creating to create 2 contexts.

I do not have any data in my child tables. So I coulld not able to test and see how it works with contexts and with out contexts including measures from child. So I created a one measure count object on my child table and combined all the objects from 3 tables with out contexts in 2 queires I got the correct results.

I hope u understand my problem. I will wait for your reponse. Please corret me if I am wrong.

REACHLP (BOB member since 2008-10-06)