Search code examples
ssasmdx

MDX: Aggregate [Date].[Calendar].[Quarter] level to create new [Date].[Calendar].[Semi Annual] Level

I have a Date Dimension that has Month Hierarchy

Year -> Quarter -> Month -> Day -> Hour

I have two measures. Measure_A (Aggregation is Max), Measure_B (Aggregation is SUM)

I need to Multiply Measure_A by 24 on "Day" level then I need to SUM it Semi Annually.

Is it possible to create a new hierarchy by just using an MDX query? Below is the output that i am trying to achieve

Year | CY H1 | Measure_C | Measure_B

Where: Measure_C is the SUM of (Measure_A * 24) from January 1 to June 30, and CY H1 will be the new level.

I don't have any problem with Measure_B because its aggregation is SUM. Only with Measure_A beacause the aggregation is Max.

Is this possible to achieve without altering the cube, just do an MDX? Thanks.

Updated:

This is my query and output as of the moment,

Query:

With
  MEMBER [Measures].[Measure_C] AS
    SUM (   Descendants ( [Date].[Date Calendar],  [Date].[Date Calendar].[Day]), [Measures].[Measure_A] * 24 )
    , Format_String = "#,##0"

Select 
  {
    [Measures].[Measure_C]
  } ON Columns,
  NON Empty
  {
    [Date].[Date Calendar].[Quarter].ALLMEMBERS
  } ON Rows
From [Cube]

Output:

+------+---------+-----------+
| Year | Quarter | Measure_C |
+------+---------+-----------+
| 2011 | Q1      |        12 |
| 2011 | Q2      |        45 |
| 2011 | Q3      |        12 |
| 2011 | Q4      |        25 |
+------+---------+-----------+

What I am trying to do is to merge Q1 and Q2 to get Calendar Semester for the first half (CY H1)

Required output:

+------+---------+-----------+
| Year | Half Yr | Measure_C |
+------+---------+-----------+
| 2011 | CY H1   |        57 |
| 2011 | CY H2   |        37 |
+------+---------+-----------+

Please note that we don't have a natural Hierarchy for CY H1. But according to the comment below, this is not possible using mdx alone.

By the way, I am doing this for reporting services so I guess I will just do the magic in the presentation layer since this is not achievable using MDX query alone. :)

Solution

  • Assuming that your year 2011 is named [Date].[Date Calendar].[2011], then you can add calculated members as children of it like this:

    With
  MEMBER [Measures].[Measure_C] AS
    Aggregate ( Descendants ( [Date].[Date Calendar],  [Date].[Date Calendar].[Day]),
                [Measures].[Measure_A] * 24 
              )
    , Format_String = "#,##0"
  MEMBER [Date].[Date Calendar].[2011].[CY H1] AS
    Aggregate({ [Date].[Date Calendar].[Q1], [Date].[Date Calendar].[Q2] })
  MEMBER [Date].[Date Calendar].[2011].[CY H2] AS
    Aggregate({ [Date].[Date Calendar].[Q3], [Date].[Date Calendar].[Q4] })

Select 
  {
    [Measures].[Measure_C]
  } ON Columns,
  NON Empty
  {
    [Date].[Date Calendar].[2011].[CY H1],
    [Date].[Date Calendar].[2011].[CY H2]
  } ON Rows
From [Cube]