ssas - 在 SSAS (powerpivot) 中联合多个 MDX 查询

Question

我在尝试将 2 个 MDX 查询连接在一起时遇到了一些困难。分别运行它们时，它们工作正常。下面的脚本

WITH 
  MEMBER [Measures].[ParameterCaption] AS 
    [Main_Incidents].[Priority].CurrentMember.Member_Caption 
  MEMBER [Measures].[ParameterValue] AS 
    [Main_Incidents].[Priority].CurrentMember.UniqueName 
  MEMBER [Measures].[ParameterLevel] AS 
    [Main_Incidents].[Priority].CurrentMember.Level.Ordinal 
SELECT 
  {
    [Measures].[ParameterCaption]
   ,[Measures].[# Incidents]
   ,[Measures].[%SLA]
  } ON COLUMNS
 ,[Main_Incidents].[Priority].ALLMEMBERS ON ROWS
FROM [Model];


WITH 
  MEMBER [Measures].[ParameterCaption] AS 
    [Main_Incidents].[usr_directorate].CurrentMember.Member_Caption 
  MEMBER [Measures].[ParameterValue] AS 
    [Main_Incidents].[usr_directorate].CurrentMember.UniqueName 
  MEMBER [Measures].[ParameterLevel] AS 
    [Main_Incidents].[usr_directorate].CurrentMember.Level.Ordinal 
SELECT 
  {
    [Measures].[ParameterCaption]
   ,[Measures].[# Incidents]
   ,[Measures].[%SLA]
  } ON COLUMNS
 ,[Main_Incidents].[usr_directorate].ALLMEMBERS ON ROWS
FROM [Model];

对我来说最重要的一点是我需要显示标签列。所以我想 UNION这 2 个查询放在一起，以便 ParameterCaption 从“优先级”维度和“部门”维度中捕获值....

请有人帮我实现这一目标？

Answer 1

这有点复杂，但绝对有可能。

Union在 MDX 中仅适用于相同层次结构的成员，因此为了实现这一点，我们需要将行成员设置为 Tuples结合了两个层次结构。我们可以通过将每个 ALLMEMBERS 集交叉连接到另一个层次结构的 [All] 成员来做到这一点。然后我们只需要更改 Parameter Caption、Value 和 Level 即可有条件地从适当的层次结构中获取值。

这可能类似于下面的代码:

WITH 
  MEMBER [Measures].[ParameterCaption] AS
    IIF([Main_Incidents].[Priority].CurrentMember.Level.Ordinal = 0, [Main_Incidents].[usr_directorate].CurrentMember.Member_Caption, [Main_Incidents].[Priority].CurrentMember.Member_Caption)
  MEMBER [Measures].[ParameterValue] AS
    IIF([Main_Incidents].[Priority].CurrentMember.Level.Ordinal = 0, [Main_Incidents].[usr_directorate].CurrentMember.UniqueName, [Main_Incidents].[Priority].CurrentMember.UniqueName)
  MEMBER [Measures].[ParameterLevel] AS
    IIF([Main_Incidents].[Priority].CurrentMember.Level.Ordinal = 0, [Main_Incidents].[usr_directorate].CurrentMember.Level.Ordinal , [Main_Incidents].[Priority].CurrentMember.Level.Ordinal)
SELECT 
  {
    [Measures].[ParameterCaption]
   ,[Measures].[# Incidents]
   ,[Measures].[%SLA]
  } ON COLUMNS
 ,{
    [Main_Incidents].[Priority].ALLMEMBERS * [Main_Incidents].[usr_directorate].[All],
    [Main_Incidents].[Priority].[All] * [Main_Incidents].[usr_directorate].ALLMEMBERS
  } ON ROWS
FROM [Model];