UNION multiple MDX queries in SSAS (powerpivot)

Question

I have some sort of difficulties trying to join 2 MDX queries together. When running them separately they work fine. The script below

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];

The most important bit for me is that I need the label column to show. So I want to UNION the 2 queries together so that the ParameterCaption captures values from "Priority" dimension and "Directorate" dimension....

Please someone help me to achieve this?

Short answer - `UNION` works only with member from same hierarchy. So no, it won't work. If you try to `UNION` them, you'll get the below error message : `Members, tuples or sets must use the same hierarchies in the UNION function.`. Why don't you instead try to cross join them? — SouravA, Jun 01 '15 at 03:01
if `[Priority]` and `[usr_directorate]` were two levels in a multi-level user hierarchy then your request would be easy enough. Shame they are seperate hierarchies. I wonder — whytheq, Jun 01 '15 at 20:20

score 1 · Answer 1 · answered Aug 20 '17 at 00:07

This is a bit complex, but definitely possible.

Union in MDX only works for members of the same hierarchy, so to achieve this we need to make the row members into Tuples that combine the two hierarchies. We can do this by cross joining each of the ALLMEMBERS sets to the [All] member for the other hierarchy. Then we just need to change the Parameter Caption, Value and Level to conditionally get the value from the appropriate hierarchy.

This could look something like the code below:

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];

UNION multiple MDX queries in SSAS (powerpivot)

1 Answers1