What is the best Practice on ordering calculations, dependente calculations and scope statments?
We have a cube with more then 100 calculations, and because of that we are having proplems on how to better organazi them.
For example we have two calculated measures, where the 2nd Measure depands on the 1st Measure as showing undernith. So far so good because we will place de dependente Measure after the 1st one. 1st Performance and 2nd Relative Performance.
Now additionally we have a scope stament for Performance that calculates the YTD on the Period dimension. And that is where the confusion starts.
Lets’ say I whant to perform a query on those two measures for Current Period and YTD for a geaven period.
Row Labels | Performance | Relative Performance |
Current Period | 0.76% | 0.67% |
YTD | 11.60% | 10.23% |
My question then is. Where should the Performance Scope statment be? Before the Relative Performance Calculation or after?
1ST MEASURE
CREATE MEMBER CURRENTCUBE.[MEASURES].[Performance]
AS Case
//Refer also to Scope
When (X is Y)
Then [Measures].[Portfolio Performance]
else [Measures].[Holding Performance]
End,
FORMAT_STRING = "Percent",
NON_EMPTY_BEHAVIOR = { [Value Base] },
VISIBLE = 1;
2ND MEASURE
CREATE MEMBER CURRENTCUBE.[MEASURES].[Relative Performance]
AS CASE
WHEN isempty([MEASURES].[Performance]) THEN Null
ELSE ((1 + [MEASURES].[Performance])/(1 + ([MEASURES].[Performance],[Asset].[Asset].defaultmember)) - 1)
END,
FORMAT_STRING = "Percent",
VISIBLE = 1;
//Scope for Performance Measure
Scope([Measures].[Performance]);
//YTD Performance
([Period].[Periods To Date].[YTD], [Period].[Month].[Month].Members)=
CASE
WHEN Isempty
(([Period].[Periods To Date].&[Current Period], [Period].[Year Month Hierarchy].currentmember))
THEN NULL
WHEN([Period].[Year Month Hierarchy].currentmember) is
([Period].[Year Month Hierarchy].currentmember.firstsibling)
THEN ([Period].[Periods To Date].DefaultMember)
ELSE
(([Period].[Periods To Date].DefaultMember)+1)*(([Period].[Periods To Date].[YTD],[Period].[Year Month Hierarchy].prevmember)+1)-1
END;
FORMAT_STRING ([Measures].[Performance]) = "Percent”;
End Scope;
Few comments:
1. Even though [Relative Performance] depends on [Performance] - their order inside MDX Script doesn't matter from the functionality point of view - both combinations will work the same. However, you are right, that it is a best practice to put Performance measure first - not only it is better for readability, it also may result in better query performance in certain cases.
2. Same comment w.r.t. of SCOPE of Performance vs. [Relative Performance] - they don't intersect and don't depend on each other, so order doesn't matter.
3. The position of SCOPE on YTD w.r.t. position of [Relative Performance] - is very important. I am glad you raised this question. In pre AS2005 you would resolve the calculation precedence between YTD and Relative Performance using solve order or calculation pass. In AS2005 their position inside MDX Script determines the calculation precedence. Since Relative Performance is a ratio, and YTD is a SUM - they are not commutative, and depending on the order of statements results will be different. My guess is that you want to compute ratio of YTD's rather than YTD of ratios - therefore Relative Performance should be put after formula for YTD. Now, in your example you don't show how YTD is computed - but it should be done before Relative Performance.
HTH,
Mosha.
