The scenario is this: I am developing some SQL Server Analysis Services Date Calculations in MDX for a client. They have three different Date Hierarchies, and wanted the standard, Previous Period, Period to Date, etc calculations. (For a very good explanation of the techniques involved, read this PDF first, then study this refinement from Mosha Pasumansky. )
The MDX looks like this:
Scope([Calculation].[Calculation].[Previous Period]);
([Calendar].[Calendar].[Date]) = ([Calculation].[Current Period], ParallelPeriod([Calendar].[Calendar].[Calendar Year],1));
([Calendar].[Week].[Calendar Week].MEMBERS) = ([Calculation].[Current Period], [Calendar].[Week].Lag(52));
([Calendar].[Calendar].[Calendar Month].MEMBERS) = ([Calculation].[Current Period], [Calendar].[Calendar Month].Lag(12));
([Calendar].[Calendar].[Calendar Quarter].MEMBERS) = ([Calculation].[Current Period], [Calendar].[Calendar Quarter].Lag(4));
([Calendar].[Calendar].[Calendar Year].MEMBERS) = ([Calculation].[Current Period], [Calendar].[Calendar Year].Lag(1));
([Calendar].[NRF].[NRF Week].MEMBERS) = ([Calculation].[Current Period], ParallelPeriod([Calendar].[NRF].[NRF Week],1));
([Calendar].[NRF].[NRF Period].MEMBERS) = ([Calculation].[Current Period], [Calendar].[NRF Period].Lag(13));
([Calendar].[NRF].[NRF Quarter].MEMBERS) = ([Calculation].[Current Period], [Calendar].[NRF Quarter].Lag(5));
([Calendar].[NRF].[NRF Year].MEMBERS) = ([Calculation].[Current Period], ParallelPeriod([Calendar].[NRF].[NRF Year],1));
End Scope;
Pretty standard stuff (sorry about the line wraps):
The symptom was that my Calendar Data Hierarchies were working fine, but the other two (Week and NRF [National Retail Federation]) were not. I worked my way through the data, and it all looked correct. Even a dynamic MDX query in SSMS worked fine. But it was wrong in the cube.
When debugging MDX, I find that it pays to go in small increments and to do patently obvious things like setting Scoped statements to constant values, as in:
Scope([Calculation].[Calculation].[Previous Period]);
([Calendar].[Calendar].[Date]) = 7777;
End Scope;
I had done this with this MDX code in an attempt to see what was going on, and it showed me that the scoped overwrites were working, but the calculations they were over-writing with were not. Then I got an idea to change how I was debugging:
Scope([Calculation].[Calculation].[Previous Period]);
([Calendar].[Calendar].[Date]) = 1;
([Calendar].[Week].[Calendar Week].MEMBERS) = 10;
([Calendar].[Calendar].[Calendar Month].MEMBERS) = 100;
([Calendar].[Calendar].[Calendar Quarter].MEMBERS) = 1000;
([Calendar].[Calendar].[Calendar Year].MEMBERS) = 10000;
([Calendar].[NRF].[NRF Week].MEMBERS) = .0001;
([Calendar].[NRF].[NRF Period].MEMBERS) = .001;
([Calendar].[NRF].[NRF Quarter].MEMBERS) = .01;
([Calendar].[NRF].[NRF Year].MEMBERS) = .1;
End Scope;
Essentially I am using a bit-mask, one position for each level. I split the two different hierarchies at the decimal. This allowed me to see which scoped overwrite statement was in effect as I browsed the cube, and it led me to the solution.
It turns out I had defined my scoped overwrite assignment statements in reverse order. (i.e. from highest level (Year) to lowest (day)) Once I switched them so the highest level statement was evaluated last, everything worked as desired. This was a very stupid mistake, and once it was defined as part of the cube MDX script, was a difficult one to spot. Luckily the debugging technique described above made it pretty obvious what was happening.
