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
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