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. The 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.
Thanks
(Just so you know, measures do not have to be linked to every dimension. )
It sounds to me like you have put surrogate keys into the facts that do not have those keys in the dimensions. You should create members for these in the dimensions. Typically, the surrogate keys for such members in a dimension will have a value of -1, and a description like "Unknown" or "Not applicable" or something similar.
Thanks for the response bdbits :)
When I have a calculated measure which has numerator coming from Fact X and denominator coming from Fact Y, in those cases I thought both facts should have the scope across all the dimensions in transformer ? Am I Wrong in assuming so ?
Not positive, but I do not think you do have to have the same scope. I guess I would try it both ways to be sure.
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