MDX Calculations Across Multiple Calendars Using Nested SCOPEs

  • PB_BI

    SSCoach

    Points: 16981

    Comments posted to this topic are about the item MDX Calculations Across Multiple Calendars Using Nested SCOPEs


    I'm on LinkedIn

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the education.

  • RonKyle

    SSC-Dedicated

    Points: 31457

    What do you mean by unpredictable results if you mix calendar hierarchies? Blank? #ERR? I can't think of a reason these would be mixed either, but I wonder if there's a way to make that clear to the users. In real life I don't need this because the calendar and fiscal year are the same. But we do have some client calendars I'd like to add at some point.

  • PB_BI

    SSCoach

    Points: 16981

    RonKyle (10/11/2016)


    What do you mean by unpredictable results if you mix calendar hierarchies? Blank? #ERR? I can't think of a reason these would be mixed either, but I wonder if there's a way to make that clear to the users. In real life I don't need this because the calendar and fiscal year are the same. But we do have some client calendars I'd like to add at some point.

    No, not blank or "#ERR". Just, well, unpredictable. If you were to crossjoin the gregorian calendar with customer calendar number 2 then the fact you would be looking at would be a product of (at year level) the gregorian year and the customer year. Add the calculations into that and you end up with a calculation on that product. So I guess unpredictable is the wrong term, maybe just "useless" or "pointless" would be better.

    I hadn't thought of restricting users from doing this (aside from mentioning it in training documentation). I guess you could try something along the lines of:

    SCOPE (DESCENDANTS([Time].[Calendar],,AFTER) , DESCENDANTS([Time].[C1],,AFTER) , DESCENDANTS([Time].[C2],,AFTER) , DESCENDANTS([Time].[C3],,AFTER) , DESCENDANTS([Time].[C4],,AFTER);

    THIS = "Nope"; //or NULL or whatever

    END SCOPE;

    In my situation, only certain "power" users have access to more than just the gregorian calendar and they know better, so I can't say I've tested this (indeed, I just pulled it out of thin air to be honest). If anyone else tried it they'd end up with an empty result set due to the security settings of their role.

    Edit: struck through the code as it would not work at all.


    I'm on LinkedIn

  • RonKyle

    SSC-Dedicated

    Points: 31457

    Thanks. A blank would be good enough. I know you weren't trying to tie a bow on the "Nope" answer, but a better way in this case would be to format a Null value using the fourth part of the format string. You must have advanced MDX skills to have written this article, and so probably know this, but thought I'd point it out anyway just in case.

    In any case, a great example of using scope. I also liked how you showed how the attributes were set out. I don't know that I would have figured that out except with your example.

  • PB_BI

    SSCoach

    Points: 16981

    RonKyle (10/11/2016)


    Thanks. A blank would be good enough. I know you weren't trying to tie a bow on the "Nope" answer, but a better way in this case would be to format a Null value using the fourth part of the format string. You must have advanced MDX skills to have written this article, and so probably know this, but thought I'd point it out anyway just in case.

    In any case, a great example of using scope. I also liked how you showed how the attributes were set out. I don't know that I would have figured that out except with your example.

    Cheers. Yeah, returning "nope" was just a silly joke 😀

    Edit: So I'll have to say for now that the best way of preventing cross joining two calendars is either role based security or education, as that code I threw together would not work at all. Yesterday was a long day, forgive me. I'll have a play around and see if there is a way to prevent this outright though....


    I'm on LinkedIn

  • bingbing.yu

    SSC Journeyman

    Points: 95

    Very good article. Is it possible to share the definition of your date dimension table? Thanks.

  • PB_BI

    SSCoach

    Points: 16981

    bingbing.yu (10/18/2016)


    Very good article. Is it possible to share the definition of your date dimension table? Thanks.

    It was just a standard date table, examples of which you can find all over the internet, with the specific customer attributes added. Something like:

    CREATE TABLE TableName

    (DateID INT,

    CalendarWeekID INT,

    CalendarMonthID INT,

    CalendarQuarterID INT,

    CalendarYearID INT,

    Customer1WeekID INT,

    Customer1MonthID INT,

    Customer1QuarterID INT,

    Customer1YearID INT

    ...etc.

    Then in a view I added display columns, so "2016, Wk30" instead of "30" for when someone selected calendar week. I'm afraid I have to keep it generic due to NDAs :rolleyes:


    I'm on LinkedIn

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply