Best Practice on ordering calculations!

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 2^nd Measure depands on the 1^st Measure as showing undernith. So far so good because we will place de dependente Measure after the 1^st one. 1^st Performance and 2^nd 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?

1^ST 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;

2^ND 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.