Convert MDX calculated measure, to scope in SSAS calculations

Question

I have got a measure, getting the last value of a currency exchange rate. The fact having the exchange rates, is configured in the dimension usage with the time dimension, and is daily based. So, using MDX, I am successfully getting my converted measure in my currencies, by using a calculated measure:

with member [Measures].[Calculated Comp Money In] as 
SUM([Dim Time].[Date Key].CurrentMember,
        [Measures].[Comp Money In]/[Measures].[Last Currency Rate])

And then in the where clause, I would filter out what currency I am reporting figures on

[Dim Currency].[Currency Key].&[200]

However, what I don't like is that I have a [Measures].[Comp Money In], and a [Measures].[Comp Money In Calculated].... Can I use the SCOPE function in MDX, so that [Measures.[Comp Money In] is configured with the calculation above? I would then add this calculation in the calculations section of SSAS.

FrankPl FrankPl · Accepted Answer · 2014-07-14T07:54:46

If you use the Enterprise edition of SSAS, you can use the measure expression property of your measure do currency conversion. This is not very well documented in the Analysis Services documentation, but works as follows: You use only one measure (i. e. either [Measures].[Calculated Comp Money In] or [Measures].[Comp Money In], and in the "Measure expression" property of this measure, enter the expression that you have in your question above:

[Measures].[Comp Money In] / [Measures].[Last Currency Rate]

Then the Analysis Services engine will take care of the rest for you. Note that this expression may only contain the measure on which you define it, times or divided by one other measure (normally he exchange rate) and nothing else.

If you do not have The Enterprise edition of Analysis Services, you can more or less copy your definition to a calculated member:

To be able to continue using the existing measure, possibly rename the measure [Measures].[Comp Money In] to [Measures].[_Comp Money In] (and make that measure invisible when you have checked that everything is fine). Then define a calculated member with the same name as the original measure:

create member CurrentCube.[Measures].[Comp Money In] as 
       SUM([Dim Time].[Date Key].CurrentMember,
           [Measures].[_Comp Money In]/[Measures].[Last Currency Rate])

Better still, use SCOPE to calculate the measure, and avoid the aggregation for single days: Assuming the only common dimension between the measure group containing [Measures].[Comp Money In] and the measure group containing [Measures].[Last Currency Rate] is [Dim Time] and this has the key attribute [Dim Time].[Date Key], and your date hierarchy is named [Dim Time].[Date], you could just use

CREATE MEMBER CurrentCube.[Measures].[Comp Money In] as NULL;
SCOPE([Dim Time].[Date Key].[Date Key].members);
    [Measures].[Comp Money In] =
        [Measures].[_Comp Money In] / [Measures].[Last Currency Rate];
END SCOPE;
SCOPE([Dim Time].[Date].Members);
    [Measures].[Comp Money In] =
        Sum(EXISTING [Dim Time].[Date Key].[Date Key].members);
END SCOPE;

Convert MDX calculated measure, to scope in SSAS calculations

1 Answers