Poor performing dynamic date calculations in MDX

• Amit Chandra

  Old Hand

  Points: 372

  More actions

  April 19, 2017 at 7:34 am

  #146457

  All,

  I have run into this issue which I am stuck on. Any pointers will help.

  Background -
  We have created a 'Date Calculation' dimension having different attributes for different time calculations like YTD, QTD, Rolling months etc. These attributes are to be used across any measure which have a relation with 'Time' dimension. 'Date Calculation' dimension is not related to any fact in the dimension usage. There is a scope calculation defined for each attribute in 'Date Calculation'. Time dimension in my cube is called - 'Date'. It has different hierarchies like 'Fiscal' and 'Calendar'.

  Problem statement -
  Above date calculations work fine when analyzing measures/calculated-measures by a single date calculation. But if user wants to see 2 or more different date calculations alongside each other, excel keeps refreshing the pivot and does not return results. Can anyone suggest a better way to do this so that query performance is not impacted even when user is trying to browse more than one data calculations at a time?

  Code snipped of sample calculations below -

  ******************************************************************************************************************************************************************************************************
  -- Rolling 3 Months
    SCOPE
    ([Date Calculations].[Date Calculations].[Rolling 3 Month],[Date].[Fiscal Month].members);      
    THIS = Aggregate(lastperiods(3, [Date].[Fiscal Month]), [Date Calculations].[Date Calculations].[Real Value]);     
    END SCOPE;     

    -- Rolling 6 Months
    SCOPE
    ([Date Calculations].[Date Calculations].[Rolling 6 Month],[Date].[Fiscal Month].members);                         
    THIS = Aggregate(lastperiods(6, [Date].[Fiscal Month]), [Date Calculations].[Date Calculations].[Real Value]);                       
    END SCOPE; 

  --Financial Year To Date
  SCOPE
  [Date Calculations].[Date Calculations].[Fiscal Year To Date];                         
  THIS =
    Aggregate
    (
    PeriodsToDate
    (
     [Date].[Fiscal Hierarchy].[Fiscal Year]
     ,[Date].[Fiscal Hierarchy].CurrentMember
    )
    ,[Date Calculations].[Date Calculations].[Real Value]
    );                         
  END SCOPE; 
  ******************************************************************************************************************************************************************************************************