Hi All,
Require your help !
We have a requirement to create a calculated measure (CR) where CR = Measure1/Measure2.
Measure1 comes from Fact X which is joined with Dimensions A,B,C and D
Measure2 comes from Fact Y which is joined with Dimensions A and B only.
Just to force join Fact Y with dimensions C and D, I have created dummy surrogate keys in cube source report and imported to Transformer.I have also mimic the Measure1 in Fact Y with calculation as 0 and vice-versa.But these dummy joins is creating a (Blank) category in the dimensions in C and D. And also selecting any value in C and D makes Measure 2 to null. i am not sure if the approach followed is right.
Please suggest if you see any issue with above approach or any alternative approach to achieve the same.
Hi,
Conceptually they just don't relate. Not even thinking about Transformer, if you are given a piece of paper with a number (measure 1) for a particular C and D combination there is no way you can relate it to a measure 2 number (as it has no matching C and D combination).
The CR does not relate to C and D as Fact Y also does not.
Even with providing dummy C and D values for Fact Y, there is no corresponding dummy value for Fact X.
You can't force link 2 pieces of information together without the necessary info to do so.
You can remove the C and D dummy values in Fact Y and remove allocation for CR in properties of dimensions C and D.
Hope that helps.
Cheers,
Ricky