If you are unable to create a new account, please email support@bspsoftware.com



MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Cube with Mutliple facts

Started by bwalikar, 30 Jan 2016 11:34:54 PM

Previous topic - Next topic


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


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.