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?
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:
MDX
queries and join the results in say, Excel if that is the target client.