如何使用MDX在共享Manufacturer维度上连接两个SSAS立方体？

MDX确实不支持SQL的JOIN语法，针对你的需求，可通过以下几种方案实现：

方法1：使用LookupCube函数跨立方体查询 #

LookupCube是MDX内置的跨立方体查询函数，可通过共同维度的成员键值关联两个立方体的数据。示例代码如下：

WITH 
-- 计算当前制造商的总销售额
MEMBER [Measures].[Total Sales] AS 
  SUM([Manufacturer].[Manufacturer].CurrentMember, [Measures].[Sales Amount])
-- 从Cube2中获取对应制造商的总原材料采购额
MEMBER [Measures].[Total Raw Material Procurement] AS 
  LookupCube("Cube2", 
    "SUM([Manufacturer].[Manufacturer].&[" + [Manufacturer].[Manufacturer].CurrentMember.Member_Key + "], [Measures].[Proc Raw Mat])"
  )
-- 计算收益，处理除数为0的异常情况
MEMBER [Measures].[Benefits] AS 
  IIF([Measures].[Total Sales] = 0, NULL, [Measures].[Total Raw Material Procurement] / [Measures].[Total Sales])
SELECT 
  {[Measures].[Total Sales], [Measures].[Total Raw Material Procurement], [Measures].[Benefits]} ON COLUMNS,
  [Manufacturer].[Manufacturer].Members ON ROWS
FROM Cube1

方法2：创建链接立方体（Linked Cube） #

在SSAS中创建链接立方体，将Cube2链接到Cube1所在的数据库，确保两个立方体共享结构一致的Manufacturer维度。完成后即可在同一个MDX查询中直接引用两个立方体的度量值：

WITH 
MEMBER [Measures].[Benefits] AS 
  IIF([Measures].[Sales Amount] = 0, NULL, [Measures].[Proc Raw Mat] / [Measures].[Sales Amount])
SELECT 
  {[Measures].[Sales Amount], [Measures].[Proc Raw Mat], [Measures].[Benefits]} ON COLUMNS,
  [Manufacturer].[Manufacturer].Members ON ROWS
FROM LinkedCubeCombined

这种方式逻辑更简洁，查询性能也优于LookupCube，适合频繁跨立方体查询的场景。

方法3：数据模型层整合（优先推荐） #

如果业务场景允许，直接将销售、采购两个事实表添加到同一个立方体中，复用Manufacturer维度。这种方案从根源上避免了跨立方体查询的复杂度，维度一致性更易维护，查询性能最佳：

WITH 
MEMBER [Measures].[Benefits] AS 
  IIF([Measures].[Sales Amount] = 0, NULL, [Measures].[Proc Raw Mat] / [Measures].[Sales Amount])
SELECT 
  {[Measures].[Sales Amount], [Measures].[Proc Raw Mat], [Measures].[Benefits]} ON COLUMNS,
  [Manufacturer].[Manufacturer].Members ON ROWS
FROM CombinedCube

内容的提问来源于stack exchange，提问作者Sam