ssasmdxolap

How to join two cubes on a shared dimension using MDX?


I am using SQL Server 19.1 and SSAS to deploy two cubes that have a common dimension say "manufacturer". The first cube has a fact table consisting of the amount of sales and the second one has the amount of procurement of raw material. I want to perform a Join between the two cubes on the "manufacturer" dimension to get the benefits (aggregation on fact table 1 with fact table 2) using MDX. In pseudo- SQL, I want to implement this:

SELECT
  manufacturer ,
  proc_raw_mat / sales as benefits
FROM
  ( SELECT manufacturer, sum(sales)as sales
    FROM cube1
     
  ) 
FULL OUTER JOIN
  ( SELECT manufacturer, sum(proc_raw_mat) as proc_raw_mat
    FROM cube2
    
  ) 
 ON
    cube1.manufacturer = cube2.manufacturer

It seems that MDX doesn't support JOINS, so how to do that?


Solution

  • Probably not the answer you want but I don't believe it is possible to write a single MDX script that pulls data from two cubes.

    Better to go with one of the following:

    1. Decide if you could alter/expand one of the cubes and bring in the required data from the other cube, and then query the amended cube.
    2. Do the join in the client of the cube data - so do 2 separate MDX queries and join the results in say, Excel if that is the target client.