data from two fact tables

[Former Member]

Hello all,

I have two fact tables. One is summary fact table and another is detail fact tables. I need to display data from both the fact tables in my report. I need to dispaly a summary of the total cost and break down of that cost in consequent columns. I know I need to use cross tab report for that.

I have 4 dimension tables common to both the fact table. I have joined both fact tables with these 4 confirmed dimension tables. Once I joined these fact tables with 4 conformed dimension, I think there will be loop created. I know I need to break down the loop now. Do I need to create cotext for this?
I need data from both fact tables in single query? How do I create a context for this so that I can get from both the facts. Here is an example.

I have a provider ABC whose total cost is 100,000 for june 2012. This is in first fact summary table. Then I have break down of that 100,000 in detail fact table. Eg. Xray cost 1000, citiscan cost 2000, blood test cost 97000.

This is my requirement.
Could you please let me know what do I need to do in univese side for this things to work in report side.

[abkshatriya · Accepted solution]

Hi,

I am assuming is your Scenario is like below...

A-Dim

B-Fact/Dim

C-Fact

Thanks,

Please Close the thread, if you found the Answer Helpful.....

[Former Member · Accepted solution]

I have attached a kind of data model excluding some dimension tables. This is what it looks like.

I need a cross tab report where first colume from cross tab will be name of doctors which will come from summar fact. I need header row in cross tab as department names which will come from detail fact. I need cost of those department breakdown in the cross tab metrics. Right now , my numbers are very high.

[Former Member · Accepted solution]

Hi,

judging only from your picture you do not need contexts (you dont have any loops there).

But judging from your first comment you should have a loop and need contexts:

I have 4 dimension tables common to both the fact table

-> Maybe you have solved the loop by creating table aliases as described in your screenshot.

If you have a loop and try to create a query from the different tables, an error message will appear.

Best regards,

Victor

[Former Member · Accepted solution]

yes, I have created aliase tables to break loops. But do I need context if I want to query getting measures from summary and detail fact table? It is generally said that dont join two fact tables directly. We are are doing so here. So, I was wondering if I need to define context for each fact tables.

[Former Member · Accepted solution]

Further, I think that there is Fan Trap at the botton where there is one to many to many join. Do you think that is a Fan trap? Do I need to resolve that?

[abkshatriya · Accepted solution]

Hi Alex,

as i can see in your image there is no any loop but when you will run a query by using measure object from Mid fact table and any kind of object from last Fact table the result will be wrong because of FAN Trap.

So you need Context to solve this and enable Multiple SQL Statement for each Context and Disable Multiple SQL statement for Each Measure.

Thanks..

Image will show you the Ex of FAN TRAP..

[Former Member · Accepted solution]

Yes, there is a fan trap. There are more than 30 dimension tables which are join to summary fact. In the diagram above I have just shown 3 dim tables. My query wiil have one dimension from dim on the left hand and measure from middle (summary) fact and detail fact(Right). ,So, when I define a context, how many context I have to define? Could you please show me with th diagram that you have shown above?

[abkshatriya · Accepted solution]

Hi,

Please Go through Below Points...

This is a common structure and will not normally result in a fan trap. You only get incorrect

results from the fan trap when the query includes a measure object on the middle table (B) of

the table path and an object (of any kind) from the subsequent table (C). The trap only occurs

where (due to the database design) a column in table B holds data values which are already a

sum of those values held at table C. The results are normally noticeably wrong.

Solution:

1. 1. Identify the potential fan trap by analyzing the one-to-many-to-one-to-many join path

relations in the schema.

1. 2. Create an alias for the table that is producing the multiplied aggregation.
2. 3. Create a join between the new alias table, and the table that holds the dimension information.
3. 4. Set cardinality.
4. 5. Set contexts.
5. 6. Change the SELECT clause of the measure object so that it refers to the alias table rather than

the original table.

Avoiding fan traps altogether

In certain situations, it is possible to avoid the fan trap completely, as shown in the diagram

1. below.

To avoid the trap, the database column in table B to which the Y measure object relates must

represent a preaggregation of more detailed data in table C. If this is the case, you can change

Resolving SQL Traps—Learner’s Guide 199

the code of the Y measure object so that it refers to table C. Hence, there is no longer a

‘one-to-many’ relationship incurred.

Thanks,

Please Close the thread, if you found the Answer Helpful.....

[Former Member · Accepted solution]

Thanks for your suggestions.

You wrote:

"3. Create a join between the new alias table, and the table that holds the dimension information."

I created alise of middle fact table. I had 30 dim tables joined to original fact table. So, I created joins between new alise tables and 30 dim tables. Do I need to delete joins between original fact table and 30 dims or leave the join as it is?  It will create a loop if I leave those joins as all 30 dims will be joins to original fact table and alise fact table.

[abkshatriya · Accepted solution]

Hi Alex,

now u have alias of mid table.now join the alias table with its original table....and move all the objects which u have created on mid table to the alias table...

like...if u have created name from mid table then again create the same object from the alias table.....and test the result...and let me know and if still u r getting wrong result then we will try other way which is just regarding your fact tables..

thank....

[Former Member · Accepted solution]

Thanks all

[former_member182521]

Alex,

It would be good to share your universe structure pane screen to understand better