none
MDX: generic time intelligence utility dimension with Prior Period YTD RRS feed

  • Question

  • I'm using http://sqlmag.com/sql-server-analysis-services/optimizing-time-based-calculations-ssas as my date tool

    Problem i have is comparing current incomplete periods (eg current quarter which is only 1 month in) vs prior quarter/semester/year which will include data for all months. So I'd be comparing 1 month of sales data vs 3 months of prior qtr sales data.

    I want to only compare the prior periods that correspond to the current that contain data

    I can't prune my date dim as I have forecasts and budgets in the cube which go into the future.

    My attempts at incorporating it into the existing structure linked above hasn't worked too well

    What would be the recommended way to go? should I include a static 'last current date' in my date dim (or a flag) and use that as a filter? get the MAX() date for the selected current period?

    I want to incorporate all of this inside the cube MDX definition, and it needs to be measure agnostic (eg incorporated as a generic member in my utility date calculations dimension)


    Jakub @ Melbourne, Australia Blog

    Wednesday, May 4, 2016 8:10 AM

All replies

  • Hi Jakub,

    This can be done in MDX, although will be a bit tedious to write. You will need to first find this year's existing period with sales (nonempty with the sales measure), and then get its parallel from last year. You can do it with the range operator as parallelperiod(start):parallelperiod(end), although I haven't yet lost all hope that a more concise syntax is possible.

    Wednesday, May 4, 2016 2:46 PM
  • Thanks Alexei,

    do you mean using the last nonempty aggregation type?

    Tedious and MDX. My two favourite things!


    Jakub @ Melbourne, Australia Blog

    Friday, May 6, 2016 4:20 AM
  • Jakub, aggregation types are a property of physical measures, and we're discussing calculated dimension member. I was referring to the nonempty function, which is a natural way of finding this period's dates that have sales. They are the starting point of your calculation's logic:
    a. find this period's dates with sales
    b. move these dates 1 period back
    c. find the sales for b

    The biggest problem I see here is how to distinguish current dates that have zero sales from current dates for which sales have not yet been reported.

    Friday, May 6, 2016 9:08 AM
  • bump

    Still haven't figured this out (i've run out of other features to implement so it's up again)

    I'm struggling with the mdx that creates the correct relative range to use. Both with setting the end date and getting to the correct level. The range needs to be at the month level, but my SCOPE()s are based on the attributes (not hierarchy levels) so that my date utilities work whether i'm using the hierarchy or not.  This is causing my issues with how to traverse across the attributes (eg [Dim Date].[Fiscal Year].currentmember <-> [Dim Date].[Month].[Current Member]

    The other issue i forsee is performance; is there a way to 'collapse' the range date set? What I mean is, if I want the range Jul:Dec, then that's 6 month members, but only 1 'half/semester' member in the hierarchy:- Semester 1.

    Jul:Nov collapsed would be {Q1,Oct,Nov}. It would greatly reduce the number of intersections (or is SSAS smart enough to do this internally now?)


    Jakub @ Melbourne, Australia Blog

    Tuesday, December 6, 2016 4:28 AM
  • Jakub, with a correct attribute relationship tree for the Date dimension, calendar month's current member will always give you the correct fiscal year's current member (unless, heaven forbid, one month is split between two adjacent fiscal years). However, [Dim Date].[Fiscal Year].&[2016] will translate into [Dim Date].[Month].&[All] because granularity.

    For the time utility dimension, my current approach is to utilize a hidden dynamic set of members of DimDate's attribute with the lowest level required (from your description it appears to be Month). Its benefit is capturing subselects (what'd you return for a YTD were Sep'16 and Nov'16 selected?). Having the set of months in user's selection proved enough for time intelligence so far.

    While I'm not sure SSAS is intelligent enough to just return the value of ( [Measures].[Sales], [Date].[Calendar].&[2016].&[Q3] ) when asked for SUM ( [Date].[Calendar].&[2016].&[7]:[Date].[Calendar].&[2016].&[9], [Measures].[Sales] ), I certainly wouldn't want to code all the IIFs to force such behavior from the calculation. And, there'd be that overhead of parsing those IIFs, so it's hard to say outright whether there'd be a speedup.

    Tuesday, December 6, 2016 7:56 AM
  • yeah the attribute relationships are all set up and it's a 'traditional' calendar in that it naturally rolls into one parent

    Let me see if i understand, you have a dynamic set which has the month numbers 1..12, year agnostic

    then you create a dynamic set that only holds the month numbers up to specific number ({1,2,3,4,5} would be what I would expect at the moment as it is for july to nov)

    then you overwrite measures for previous periods with a scope and set them to be null when they're outside the dynamic set (or conversely you only include ones that are in the dynamic set)?


    Jakub @ Melbourne, Australia Blog

    Thursday, December 8, 2016 12:22 AM
  • No, not a set of month numbers 1..12, but a set of all calendar's months: 20xx.1, 20xx.2,...2017.11,2017.12...

    MDX-wise, having the Time Utility dimension unrelated to measure groups, its default member repeats the value of whatever measure in whatever context is queried (for measure groups with the default, True, setting of IgnoreUnrelatedDimensions). All other members can then be coded as necessary, and it is technically possible to put all MDX in member definitions without utilizing scopes.

    Thursday, December 8, 2016 7:27 AM
  • Ok yeah, that's how my time utility dim is set up - http://sqlmag.com/sql-server-analysis-services/optimizing-time-based-calculations-ssas

    I've got it half working, or working for 'year ago'.

    I ended up 'blanking' out the dates from the 'last date with data' to the tail of the date hierarchy.currentmember

    that seemed to cascade well with 'year ago', and should work with my other benchmarks (to budget, to forecast)

    I've got it hardcoded with the month after the last date with data (july 2015) for now just to get it to work

    CREATE STATIC SET CurrentCube.DateFilter AS (
        [Dim Date].[Fiscal Hierarchy].[Fiscal Month].&[201508]:
        TAIL(DESCENDANTS([Dim Date].[Fiscal Hierarchy].CURRENTMEMBER,,LEAVES),1).ITEM(0)
    );
    
    SCOPE (DateFilter);
        THIS = NULL ;
    END SCOPE;

    But for the life of me I can't figure out what i need to do to get prior period working. This is what's happening (Prior Period is wrong)


    ^^ PCP is right but PP is wrong

    this is what it should be because I only have the 1st member of the period so PP needs to only return the first member of the prior period



    Jakub @ Melbourne, Australia Blog


    • Edited by jakubk Thursday, December 8, 2016 11:37 PM
    Thursday, December 8, 2016 11:35 PM
  • Jakub, do you not feel there's something wrong with using the CurrentMember function, outside of set-iterating functions, in a static set definition?

    I find it impossible to debug your prior period calculated member without seeing its underlying MDX. Date Dimension(s) structure might well come in handy, too.

    And PCP is 'right' only for a given value of 'right'. Some may disagree that the value for Jul 2014-15 is the correct PCP for e.g. 2015-16 S1. So your specific PP definition is also required to troubleshoot it.

    Oh and as a side note, figures of this magnitude could really use some thousands separator.

    Friday, December 9, 2016 8:23 AM
  • (sorry long break over xmas)

    you're right re:currentmember, my test data wasn't changing so it was just working

    We're going to leave PP for now implement PCP only. Our definition of 'YTD prior corresponding period' is the same period year with data for future months filtered out. BEcause it doesn't make sense to compare the results from the first 6 months of last year (S1) if you only have one month of results for this year.

    here's my PP and PCP MDX

     -- Fiscal Month
        Scope( [Dim Date].[Month Key].[Month Key].members);        
            -- Prior Period
            Scope([Dim Date Calculations].[Date Calculations].[Prior Period]);        
                this  =    
                ( [Dim Date].[Month Key].CurrentMember.PrevMember
                 ,[Dim Date Calculations].[Date Calculations].[Current]
                );        
            END scope;                     
            -- Year Ago. Exclude for attributes that have no concept of prior year
            Scope ( [Dim Date Calculations].[Date Calculations].[Prior Corresponding Period]);        
                this =    
                ( [Dim Date].[Month Key].CurrentMember.Lag(12)
                 ,[Dim Date Calculations].[Date Calculations].[Current]
                );        
            END Scope;
                           
        End Scope;        
    
    
        -- Fiscal Quarter
        Scope( [Dim Date].[Fiscal Quarter].[Fiscal Quarter].members);        
            -- Prior Period
            SCOPE( [Dim Date Calculations].[Date Calculations].[Prior Period]);        
                THIS = ( [Dim Date].[Fiscal Quarter].CurrentMember.PrevMember
                 ,[Dim Date Calculations].[Date Calculations].[Current]
                );        
            END SCOPE;                 
            -- Year Ago. Exclude for attributes that have no concept of prior year
            SCOPE ( [Dim Date Calculations].[Date Calculations].[Prior Corresponding Period]);        
                THIS = ( [Dim Date].[Fiscal Quarter].CurrentMember.Lag(4)
                 ,[Dim Date Calculations].[Date Calculations].[Current]
                );        
            END SCOPE;        
        END SCOPE;        
    
        -- Fiscal Semester
        Scope( [Dim Date].[Fiscal Semester].[Fiscal Semester].members);        
            -- Prior Period
            SCOPE( [Dim Date Calculations].[Date Calculations].[Prior Period]);        
                THIS = ( [Dim Date].[Fiscal Semester].CurrentMember.PrevMember
                 ,[Dim Date Calculations].[Date Calculations].[Current]
                );        
            END SCOPE;                 
            -- Year Ago. Exclude for attributes that have no concept of prior year
            SCOPE ( [Dim Date Calculations].[Date Calculations].[Prior Corresponding Period]);        
                THIS = ( [Dim Date].[Fiscal Semester].CurrentMember.Lag(2)
                 ,[Dim Date Calculations].[Date Calculations].[Current]
                );        
            END SCOPE;                   
        End Scope;        
    
        -- Fiscal Year
        Scope( [Dim Date].[Fiscal Year].[Fiscal Year].members);                     
            -- Prior Period
            SCOPE({ [Dim Date Calculations].[Date Calculations].[Prior Period],[Dim Date Calculations].[Date Calculations].[Prior Corresponding Period]});        
                THIS =    
                ( [Dim Date].[Fiscal Year].CurrentMember.PrevMember
                 ,[Dim Date Calculations].[Date Calculations].[Current]
                );        
            END SCOPE;
              
        End Scope;  


    Jakub @ Melbourne, Australia Blog

    Wednesday, February 8, 2017 12:38 AM
  • Jakub, this is one straightforward piece of code, saying for PCP 'just go back 1 year and return the full period's sales'. This will do exactly nothing for filtering this year's periods without sales.

    Having seen your dimension names, I can be more specific with MDX that implements my original advice:

    create hidden dynamic set currentcube.[mnths] as [Dim Date].[Month Key].[Month Key];

    scope ( [Dim Date].[Fiscal Hierarchy] );
    scope ( [Dim Date Calculations].[Date Calculations].[Prior Corresponding Period] );
    this = aggregate(generate(
    existing nonempty([mnths],([Measures].[Sales],[Dim Date Calculations].[Date Calculations].[Current]))
    ,{[Dim Date].[Month Key].CurrentMember.Lag(12)}
    )
    , [Dim Date Calculations].[Date Calculations].[Current]
    );
    end scope;
    end scope;

    You might want to exclude Fiscal Hierarchy's All level from the outer scope.

    Expect me to help you solve your problems, not to solve your problems for you.

    Wednesday, February 8, 2017 7:52 AM