MDX Calculations Across Multiple Calendars Using Nested SCOPEs

Question

Post reply

MDX Calculations Across Multiple Calendars Using Nested SCOPEs

PB_BI

SSCoach

Points: 17463
More actions
October 10, 2016 at 12:23 am

#330906

Comments posted to this topic are about the item MDX Calculations Across Multiple Calendars Using Nested SCOPEs
I'm on LinkedIn

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

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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 1

akljfhnlaflkj

SSC Guru

Points: 76202

October 10, 2016 at 8:44 am

#1905673

Thanks for the education.

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 2

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: 17463 More actions · Answer 3

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: 31556 More actions · Answer 4

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: 17463 More actions · Answer 5

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 More actions · Answer 6

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

PB_BI SSCoach Points: 17463 More actions · Answer 7

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