SSAS & OLAP cube: twice same measure

0
votes

I'm not very experienced in OLAP Cube + MDX, and I'm having a hard time trying to use twice the same measure in a cube.

Let's say that we have 3 Dimensions: D_DATE, D_USER, D_TYPE_OF_SALE_TARGET and 3 tables of Fact: F_SALE, F_MEETING, F_SALE_TARGET

  • F_SALE is linked to D_USER (who make the sale) and D_DATE (when)
  • F_SALE_TARGET is linked to D_USER, D_DATE, D_TYPE_OF_SALE_TARGET (meaning: user has to reach various goals/targets for a given month).

I can browse my cube:

  • Rows = Date * User
  • Cols = Number of sale, Total amount of sale + the value of 1 target (in the WHERE clause, I filter on [Dim TYPE SALE TARGET].[Code].&[code.numberOfSales])

How can I add other columns for other targets? As all the targets are in the same table, I don't see how to add a second measure from [Measures].[Value - F_SALE_TARGET] linked to a different code, ie. [Dim TYPE SALE TARGET].[Code].&[code.amountOfSale].

1
ssasmdxolapbusiness-intelligencemeasure

1 Answers

1
votes

your question is not clear to me but it seems like one way to accomplish that is by creating Calculated Members. Basically, select you cube in BIDS, go to the Calculations tab and create Calculated Members. You would be able to insert your MDX query there. For each target type you can create a different calculation such as: ([Measures].[Value - F_SALE_TARGET], [Dim TYPE SALE TARGET].[Code].&[code.amountOfSale])