Cube with Mutliple facts

[bwalikar]

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.

[Ricardo Julio "Ricky" Villa]

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