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

Question

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. :)

I am not sure what you mean by Year | CY H1 | Measure_C | Measure_B. Is this meant to be e. g. the column header? This is not possibly in MDX. You can only have members of the same hierarchy on the same level of the column headers. — FrankPl
Yes you are right about the column headers, I just thought if it is possible to do in mdx. thanks for your comment :) — ggarcia

FrankPl FrankPl · Accepted Answer · 2014-09-19T10:28:58

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]

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

2 Answers