MDX : combining data from different tables

1
votes

How can I combine data coming from different tables.

Let's assume I have 2 tables:

First with sales:

  • id shop
  • id product
  • date
  • amount

Second with stocks:
actually, with the same structure

  • id shop
  • id product
  • date
  • amount

I need to analyze for how many days' stock there is in the shop now. For that I need to calculate the average sale per shop per day for last 20 weeks and then divide the remaining stock by the average sales rate.

How can I achieve this?

1
mdxiccube

1 Answers

1
votes

This is not a actual problem in MDX as you can combine dimension over different fact tables.

You need to create your 3 dimension (using reference table or similar) :

id_shop -> [Shop] id_product -> [Product] date -> [Time]

Now we need to add the two tables as 'fact' tables. Recall that Fact Tables are the ones defining measures.

In icCube create a default Cube, e.g. [Cube], and for each table create a 'measure group' (just click the '+' ).

enter image description here

Bind your tables to the dimension, the 'magic'wand will do the work and create a measure for each table (e.g. [Stock] & [Sales] ).

Once the schema is defined and deployed you can use both measures without taking even noticing they are coming from different tables :

     [Measures].[Sales] / [Measures].[Stocks]