I have two calculated measures C1 and C2 and C1 is used to calculate C2.
In this case is it better to write complete formula to calculate C2 or
use C1 in C2. Do they make any difference?

For example:

Option 1:

WITH MEMBER [Measures].[C1] AS 'Avg([Date].[2002]:[Date].[2006])'

WITH MEMBER [Measures].[C2] AS
'Sum([Date].[2002]:[Date].[2006])/Avg([Date].[2002]:[Date].[2006])'


Option 2:

WITH MEMBER [Measures].[C1] AS 'Avg([Date].[2002]:[Date].[2006])',
SOLVE_ORDER = 1

WITH MEMBER [Measures].[C2] AS
'Sum([Date].[2002]:[Date].[2006])/[Measures].[C1])', SOLVE_ORDER = 2


In my experiment they are not any making significant difference?

Thanks
Mahesh


*** Sent via Developersdex http://www.developersdex.com ***

Option 1:
WITH MEMBER [Measures].[C1] AS 'Avg([Date].[2002]:[Date].[2006])'

MEMBER [Measures].[C2] AS
'Sum([Date].[2002]:[Date].[2006])/Avg([Date].[2002]:[Date].[2006])'


Option 2:
WITH MEMBER [Measures].[C1] AS 'Avg([Date].[2002]:[Date].[2006])',
SOLVE_ORDER = 1

MEMBER [Measures].[C2] AS
'Sum([Date].[2002]:[Date].[2006])/[Measures].[C1])', SOLVE_ORDER = 2

Option 1:
WITH MEMBER [Measures].[C1] AS 'Avg([Date].[2002]:[Date].[2006])'
MEMBER [Measures].[C2] AS
'Sum([Date].[2002]:[Date].[2006])/Avg([Date].[2002]:[Date].[2006])'

Option 2:
WITH MEMBER [Measures].[C1] AS 'Avg([Date].[2002]:[Date].[2006])',
SOLVE_ORDER = 1
MEMBER [Measures].[C2] AS
'Sum([Date].[2002]:[Date].[2006])/[Measures].[C1])', SOLVE_ORDER = 2


*** Sent via Developersdex http://www.developersdex.com ***

Hi,

Please respond.

Thanks
Mahesh

Hi Mahesh,

Are these hypothetical MDX calculated measures, because they result (at
least, in AS 2000) in infinite recursion, unless a measure is explicitly
specified in the Sum() and Avg()? With that modification, it may be more
efficient to simply count the number of non empty members, as in
[Measures].[C3] in the Foodmart query below:

'Avg([Time].[1997].[Q1].[1]
:[Time].[1997].[Q2].[6],
[Measures].[Unit Sales])'
MEMBER [Measures].[C2] AS
'Sum([Time].[1997].[Q1].[1]
:[Time].[1997].[Q2].[6],
[Measures].[Unit Sales])/
Avg([Time].[1997].[Q1].[1]
:[Time].[1997].[Q2].[6],
[Measures].[Unit Sales])'
Member [Measures].[C3] as
'iif(Count(Filter([Time].[1997].[Q1].[1]
:[Time].[1997].[Q2].[6],
Not IsEmpty([Measures].[Unit Sales])) as FS) = 0,
NULL, Count(FS))'
select {[Measures].[C1], [Measures].[C2],
[Measures].[C3]} on 0,
[Product].[Product Subcategory].Members on 1
from Sales
where [Customers].[All Customers].[USA].[WA].[Seattle]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak,

Thanks for the response.

The MDXs are hypothetical examples to clarify my questions. I am
wondering if any one option is better than another or there is no
difference.

For example, i need to display two calculated measures C1 and C2 and C1
is a part of formula to calculate C2. In this case, is it better to use
C1 to calculate C2 or write complete formula instead of using C1.

C1 AS '[abc].[def]-[uvw].[xyz]'
C2 AS '[mno].[pqr] * C1'

C1 AS '[abc].[def]-[uvw].[xyz]'
C2 AS '[mno].[pqr] * ([abc].[def]-[uvw].[xyz])'

In second option [abc].[def]-[uvw].[xyz] is calculated twice. The first
option will be better if it calculates [abc].[def]-[uvw].[xyz] in C1 and
uses the calculated value to obtain C2.

Any response will be greatly appreciated.

Thanks
Mahesh



*** Sent via Developersdex http://www.developersdex.com ***

The first option should work better, because the value of C1 should get
cached and reused in computing C2.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks Deepak.


*** Sent via Developersdex http://www.developersdex.com ***