Perform drill across in MDX (SSRS) / joining two fact tables on one conformed dimension

Question

How can I join two fact tables in MDX by means of one conformed dimension?

For example (in SQL) I have 2 fact tables: shipment_facts, return_facts and one common dimension product, it would be:

SELECT
  COALESCE (shp.product, rtn.product) as Product,
  quantity_returned / quantity_shipped as ReturnRate
FROM
  ( SELECT product, sum(quantity_shipped)as quantity_shipped
    FROM shipment_facts, product
    WHERE .....
  ) shp
FULL OUTER JOIN
  ( SELECT product, sum(quantity_returned) as quantity_returned
    FROM return_facts, product
    WHERE....
  ) rtn
 ON
    shp.product = rtn.product

How can I implement this using MDX (or SSRS)?

Answer 1

This is handled automatically by Analysis Services if you query the cube. Just select the hierarchies you want on rows, and measures from any measure group (which is the Analysis Services construct derived from fact tables), and the results should be correct.

This just requires the cube design to be correct. In this context, this mainly means the configuration on the "Dimension Usage" tab of the Cube Editor in Business Intelligence Development Studio.