MDX to calculate Start of month values across individual dates

Question

I am dealing with a requirement as below, the solution i have is either not addressing the complete problem or is extremely slow and in many cases just not feasible. Need a little help on the same.

Problem: Need a query which displays a bunch of measures with cross join of 4 dimension (10 levels) and a date dimension, i need to determine a measures's first available value across time periods such as Start of month, start of year Etc..

I have

dimA - Level A1
dimB - Leve B1,B2,B3
dimC - Level C1
dimD - Level D1,D2,D3,D4,D5
Date Dimension - Date, Month, Year as levels.
Measure - M1 To M5

Query:

SELECT 
   NON EMPTY {[Measures].[M1],
              [Measures].[M2],
              [Measures].[M3],
              [Measures].[M4],
              [Measures].[M5],
              [Measures].[M1SOM]} ON COLUMNS
  ,NON EMPTY 
            {
    [DimA].[A1].[A1].ALLMEMBERS*
    [DIMB].[B1].[B1].ALLMEMBERS*
    [DIMB].[B2].[B2].ALLMEMBERS*
    [DIMB].[B3].[B3].ALLMEMBERS*
    [DIMB].[B4].[B4].ALLMEMBERS*
    [DIMB].[B5].[B5].ALLMEMBERS*
    [DIMC].[C1].[C1].ALLMEMBERS*
    [DIMD].[D1].[D1].ALLMEMBERS*
    [DIMD].[D2].[D2].ALLMEMBERS*
    [DIMD].[D3].[D3].ALLMEMBERS*
    [Date].[Date].[Date].ALLMEMBERS*
    [Date].[Month].[Month].ALLMEMBERS*
} ON ROWS
FROM [Cube]

***The above query has all those cross joins as this query would power an SSRS report dataset.

I Need to create a new calculated measure or a real (persisted measure) that displays the Start of month & Start of year Values. Below are the options i have currently. Lets assume, Measure M1 is the one for which i need the SOM and SOY values.

Create a persisted measure (M1SOM) based off the measure M1 and provide an aggregation function of "FirstNonEmpty" : This works perfectly when we run a query at Month level i.e. in the above query have [Date].[Month].[Month].Allmembers instead of [Date].[Date].[Date].Allmembers. It displays the first available non empty value of the month. However, this means the rest of the measures are aggregated at month level. If i want to display the daily values of the other measures along with SOM values for this measure it wont work, cause the scope now changes to a single date and i just get the daily value in the M1SOM measure as well.
Create a calculated measure (M1SOM) as below, along with the query above and the same thing happens. Cant display the SOM values at a daily date level.

WITH MEMBER [Measures].[M1SOM] AS ([Date].[Date].FirstChild,[Measures].[M1])
Create a calculated member as below, This works! however, the query absolutely kills my machine and takes forever to execute, if i run it with more than 3 levels in the cross join.

WITH MEMBER M1SOM AS Head(NonEmpty({[Date].[Date].Parent.Children}*[Measures].[M1SOM])).Item(0)

Is there a better way to achieve the SOM and SOY calculations at a daily level?

Thanks

Srikanth

whytheq whytheq · Accepted Answer · 2014-10-04T12:04:24

(this is untested)

Does this return the first date of the month:

WITH MEMBER [Measures].[FirstDayOfMonth] AS 
    OpeningPeriod(
      [Date].[Date].[Date],
      [Date].[Date].CURRENTMEMBER.PARENT
    ).MemberValue 
SELECT 
   NON EMPTY {[Measures].[M1],
              [Measures].[M2],
              [Measures].[M3],
              [Measures].[M4],
              [Measures].[M5],
              [Measures].[FirstDayOfMonth] } ON COLUMNS
  ,NON EMPTY 
            {
    [DimA].[A1].[A1].ALLMEMBERS*
    [DIMB].[B1].[B1].ALLMEMBERS*
    [DIMB].[B2].[B2].ALLMEMBERS*
    [DIMB].[B3].[B3].ALLMEMBERS*
    [DIMB].[B4].[B4].ALLMEMBERS*
    [DIMB].[B5].[B5].ALLMEMBERS*
    [DIMC].[C1].[C1].ALLMEMBERS*
    [DIMD].[D1].[D1].ALLMEMBERS*
    [DIMD].[D2].[D2].ALLMEMBERS*
    [DIMD].[D3].[D3].ALLMEMBERS*
    [Date].[Date].[Date].ALLMEMBERS*
    [Date].[Month].[Month].ALLMEMBERS*
} ON ROWS
FROM [Cube]

(Again untested)

WITH MEMBER [Measures].[FirstDayOfMonthM1] AS 
    (
      OpeningPeriod(
        [Date].[Date].[Date],
        [Date].[Date].CURRENTMEMBER.PARENT
      ),
    [Measures].[M1]
    )
SELECT 
   NON EMPTY {[Measures].[M1],
              [Measures].[M2],
              [Measures].[M3],
              [Measures].[M4],
              [Measures].[M5],
              [Measures].[FirstDayOfMonthM1] } ON COLUMNS
  ,NON EMPTY 
            {
    [DimA].[A1].[A1].ALLMEMBERS*
    [DIMB].[B1].[B1].ALLMEMBERS*
    [DIMB].[B2].[B2].ALLMEMBERS*
    [DIMB].[B3].[B3].ALLMEMBERS*
    [DIMB].[B4].[B4].ALLMEMBERS*
    [DIMB].[B5].[B5].ALLMEMBERS*
    [DIMC].[C1].[C1].ALLMEMBERS*
    [DIMD].[D1].[D1].ALLMEMBERS*
    [DIMD].[D2].[D2].ALLMEMBERS*
    [DIMD].[D3].[D3].ALLMEMBERS*
    [Date].[Date].[Date].ALLMEMBERS*
    [Date].[Month].[Month].ALLMEMBERS*
} ON ROWS
FROM [Cube]

This seems to be quite a nice reference for playing with time periods: http://www.mssqltips.com/sqlservertip/2877/sql-server-analysis-serviceslead-lag-openingperiod-closingperiod-time-related-functions/

MDX to calculate Start of month values across individual dates

2 Answers