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

Author Topic: Cube with Mutliple facts  (Read 2187 times)

Offline bwalikar

  • Full Member
  • ***
  • Join Date: Mar 2015
  • Posts: 38
  • Forum Citizenship: +0/-0
Cube with Mutliple facts
« on: 30 Jan 2016 11:34:54 pm »
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.

Offline Ricardo Julio "Ricky" Villa

  • Senior Member
  • ****
  • Join Date: May 2013
  • Posts: 53
  • Forum Citizenship: +3/-0
  • If you don't know who I am, Google me...
Re: Cube with Mutliple facts
« Reply #1 on: 24 Mar 2016 12:21:32 pm »

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.