Merge two MDX resultsets from the same measures group

Question

I am trying to merge the result of two separate MDX queries, where the measures are from the same measure group. The cube design is based on a lifecycle transaction model, where the date are all role playing dimensions. The queries are:


select [Measures].[Issued] on columns,
[Product].[Product Name].[Product Name] on rows
from [MET2DW]
where ([Product].[Product Type].&[IP], [Product].[Component Type].&[SD],[Issued Date].[Fiscal Year Name].&[Fiscal Calendar 2010])

select [Measures].[Last hits] on columns,
[Product].[Product Name].[Product Name] on rows
from [MET2DW]
where ([Product].[Product Type].&[IP], [Product].[Component Type].&[SD],[Last Sync Date].[Fiscal Year Name].&[Fiscal Calendar 2010])

I've tried using a CROSSJOIN, but then it complained about the fact that the measures I was trying to cross join are in the same heirarchy. If I run the query with both the measures on the same set, then the date filters don't really work anymore. The issue is basically that they are different date dimensions for each of those counts, and they slice different ways.....

Is there any way to do this?

ic3 ic3 · Accepted Answer · 2011-09-11T07:39:23

You can create a MDX calculated member adding both values for two tuple of different dimensionality (aka same hierarchies):

 with
  member [Measures].[-Issued] as ([Issued Date].[Fiscal Year Name].&[Fiscal Calendar 2010],[Measures].[Issued])
  member [Measures].[-Last hits] as ([Last Sync Date].[Fiscal Year Name].&[Fiscal Calendar 2010],[Measures].[Last hits])
 select 
    {[Measures].[-Issued],[Measures].[-Last hits]} on columns,
    [Product].[Product Name].[Product Name] on rows
 from 
    [MET2DW]
 where 
    ([Product].[Product Type].&[IP], [Product].[Component Type].&[SD])

Merge two MDX resultsets from the same measures group

1 Answers