CODEWITHSUNDEEP
ssasmdxolap
whytheq
whytheq

Reputation: 35557

Extracting days in CurrMth/PrevMth/PrevMth-1/EquivMthLastYr/EquivMthLastYr-1

I'm attempting to speed up a current script.

I feel I may have over complicated the extraction of the days relevant to the script. Currently I'm using EXISTS across levels of the date dimension. Also I am creating a custom set:

WITH 
  SET [Last24Mths] AS 
    Tail
    (
      {[Date].[Calendar].[Month].MEMBERS}
     ,25
    ) 
  SET [TargetDays] AS 
    Union
    (
      Exists
      (
        {[Date].[Calendar].[Date].MEMBERS}
       ,SubSet
        (
          [Last24Mths]
         ,24
         ,1
        )
      )
     ,Exists
      (
        {[Date].[Calendar].[Date].MEMBERS}
       ,SubSet
        (
          [Last24Mths]
         ,23
         ,1
        )
      )
     ,Exists
      (
        {[Date].[Calendar].[Date].MEMBERS}
       ,SubSet
        (
          [Last24Mths]
         ,22
         ,1
        )
      )
     ,Exists
      (
        {[Date].[Calendar].[Date].MEMBERS}
       ,SubSet
        (
          [Last24Mths]
         ,0
         ,1
        )
      )
     ,Exists
      (
        {[Date].[Calendar].[Date].MEMBERS}
       ,SubSet
        (
          [Last24Mths]
         ,12
         ,1
        )
      )
    ) 
SELECT 
  {} ON 0
 ,
  {[TargetDays]} ON 1
FROM [Adventure Works]
;

In the context of the complete query there is no reason why this set needs to be calculated using custom sets so in my 'improved' script I've moved this out of the WITH. Also as really we are using different levels of the same hierarchy I've switched to the use of DESCENDANTS. Is the following the best I can do in terms of performance? Actually, is the following even actually more efficient?

SELECT
  {} ON 0
 ,
 DESCENDANTS(
  {
     Tail({[Date].[Calendar].[Month].MEMBERS}),
     Tail({[Date].[Calendar].[Month].MEMBERS}).ITEM(0).LAG(1),
     Tail({[Date].[Calendar].[Month].MEMBERS}).ITEM(0).LAG(2),
     Tail({[Date].[Calendar].[Month].MEMBERS}).ITEM(0).LAG(12),
     Tail({[Date].[Calendar].[Month].MEMBERS}).ITEM(0).LAG(24)
  }
  ,[Date].[Calendar].[Date]
 )
     ON 1
FROM [Adventure Works]
;

Upvotes: 1

Views: 56

Answers (1)

FrankPl
FrankPl

Reputation: 13315

I would think that for all calculations that can be done purely within the date dimension, efficiency is normally not critical, as there are just a little more than one thousand members in the whole dimension. Hence it is important to not need measure groups etc. to do the calculations, but once you did this, you can normally stop optimizing.

Having said that, you can optimize your expression slightly: You can combine the first three Tail expressions to one:

{
    Tail({[Date].[Calendar].[Month].MEMBERS}),
    Tail({[Date].[Calendar].[Month].MEMBERS}).ITEM(0).LAG(1),
    Tail({[Date].[Calendar].[Month].MEMBERS}).ITEM(0).LAG(2),
    Tail({[Date].[Calendar].[Month].MEMBERS}).ITEM(0).LAG(12),
    Tail({[Date].[Calendar].[Month].MEMBERS}).ITEM(0).LAG(24)
}

is equivalent to

{
    Tail({[Date].[Calendar].[Month].MEMBERS}, 3),
    Tail({[Date].[Calendar].[Month].MEMBERS}).ITEM(0).LAG(12),
    Tail({[Date].[Calendar].[Month].MEMBERS}).ITEM(0).LAG(24)
}

And if you need the last month in many queries, it might make sense to create a set on cube level

CREATE SET [LastMonth] as Tail({[Date].[Calendar].[Month].MEMBERS})

and then reference it in your calculations:

{
    Tail({[Date].[Calendar].[Month].MEMBERS}, 3),
    [LastMonth].ITEM(0).LAG(12),
    [LastMonth].ITEM(0).LAG(24)
}

This could improve caching, but I am not absolutely sure about that. At least it might make your calculations slightly more readable - and developer performance is something you should not forget completely ;-) Sometimes that is still more important than software performance.

Upvotes: 1

Related Questions