Problem While Taking Ratio's of Measures from Two Fact Tables

[nphani77]

Hi,
I have got three tables like two Fact tables and one confirmed time dimension.I need to take the ratio of two facts existing in each of the fact tables and aggregate them at month level.When I try to achive this I am getting the wrong results basically because of the cartesian product.I know that this is happening due to Chasm trap formed among the tables.My constraint is that I should be able to create the "Ratio" Object,which is (Fact1/Fact2) only at universe level as I will be using this at AF(Application Foundation) level.I have tried using context to resolve the trap still I am not getting proper Results.How Can I achieve this?

Fact table-1
------------------------------------
Time_key product_key Fact1
1 10 100
1 11 50
1 12 200
-------------------------------------
Fact Table-2
------------------------------------
Time_key Customer_key Fact2
1 10 100
1 11 100
1 12 100
1 15 100
-------------------------------------
Time_dim
-------------------------------------
Time_key Month
1 Jan
2 Feb
3 Mar (etc....)
---------------------------------
My Object is when I drag "Month" object and "Ratio" Object I should get the correct result.

Month Ratio
-----------------------------
Jan 0.875 which is (350/400)
-------------------------------------
But, Instead I am the value
-------------------------------
Jan 1.16
which is due to the cartesian Product.

Please suggest the solution ASAP

Thanks in advance

Regards,
Phani

 

[skrandel]

I'm not going to take the time to figure out the entire solution, but.....

You really should do this calculation at the report level. Otherwise, you're trying to do a fact to fact join. Which is really not a good idea.



Steve Krandel
Westbay Solutions

http://www.westbaysolutions.com

 

[blom0344]

If your database supports it you probably can get away with a special database view over the two facttables. The view would hold two temp aggregated datasets joined internally over the time_key field. However it would involve expanding the existing universe with the view object......

T. Blom
Information analyst
tbl@shimano-eu.com