Monthly Archives: July 2005
Lunchtime diversion
Partitioning in AS2K and AS2005
Changes to certification for SQL Server
Time Ranges in AS2005
- Create a calculated measure called [Measures].[Sales Compare Period] to show the value of my original measure [Measures].[Sales], but for the second Period dimension and ignoring the first one
- Populate the values of this new calculated measure by using an assignment like ([Measures].[Sales Compare Period]) = ([Measures].[Sales], ROOT([Period]))
- Make sure the value of this calculated measure didn’t change as a result of the next assignment by using the FREEZE statement, so: FREEZE([Measures].[Sales Compare Period])
- Then make sure the values of the original [Measures].[Sales] measure ignored the [Compare Period] dimension by making its value ([Measures].[Sales]) = ([Measures].[Sales], ROOT([Compare Period]))
So the MDX Script ended up looking like this:
CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales Compare Period]
AS NULL,
VISIBLE
= 1;
([MEASURES].[Sales Compare Period])=([Measures].[Sales],
ROOT([Period]));
FREEZE([MEASURES].[Sales Compare Period]);
([Measures].[Sales]) = ([Measures].[Sales],
ROOT([Compare Period]));
I found several problems though. First of all, the ROOT([MyDimension]) function only did what I wanted it to do (ie produce a tuple of all the All Members of every attribute hierarchy on MyDimension) when I didn’t have a set containing multiple members from MyDimension in the WHERE clause, and also failed for the Grand Total column for the original Sales measure in the OWC cube browser. I thought this was a bit rubbish and have raised this to the dev team, so hopefully it’ll get fixed. What I then did was to hard code the tuple instead of using ROOT(), so that my script looked like this:
CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales Compare Period]
AS NULL,
VISIBLE
= 1;
([MEASURES].[Sales Compare Period])=([Measures].[Sales],[Period].[Month].[All],[Period].[Quarter].[All],[Period].[Year].[All]);
FREEZE
([MEASURES].[Sales Compare Period]);
([Measures].[Sales]) = ([Measures].[Sales],[Compare Period].[Month].[All],[Compare Period].[Quarter].[All],[Compare Period].[Year].[All]);
The next issue was with overwriting the values of [Measures].[Sales] – it seemed to have different problems in the OWC cube browser in the Grand Total cells, gaving me an infinite recursion error, so I guess there was some kind of conflict with the VisualTotals functionality that OWC uses. Anyway at this point I thought I’d simply create another calculated measure to show these values instead, and not overwrite the values of the original measure but just hide it.
So, my MDX Script got changed again:
CALCULATE
;
CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales Original Period]
AS ([Measures].[Sales],[Compare Period].[Month].[All],[Compare Period].[Quarter].[All],[Compare Period].[Year].[All] ),
VISIBLE
= 1 ;
CREATE
MEMBER CURRENTCUBE.[MEASURES].[Sales Compare Period]
AS ([Measures].[Sales],[Period].[Month].[All],[Period].[Quarter].[All],[Period].[Year].[All] ),
VISIBLE
= 1 ;
{[Period].[Month].&[1], [Period].[Month].&[2]}
ON 1
FROM CUBE1
WHERE({[Compare Period].[Month].&[1], [Compare Period].[Month].&[3]})