Help - Complex Currency Conversion - MDX

Dear all,

I have implemented Conversion conversion in my SSAS cube (One - Many) and is working fine. (Reporting Currency dimension that has currencies to which data can be converted, Fx rates measure group that has FX rates for those currencies from base currency (GBP) and calculation to convert it) The calculation I use is as below

Code Snippet

Scope ( {Measures.[YTD Value Reporting CCY], Measures.[LTD Value Reporting CCY]});

Scope (Leaves ([Calendar]), Except([Reporting Currency].[Reporting Currency].[Reporting Currency].Members , [Reporting Currency].[Reporting Currency].&[GBP]));

Scope ( {Measures.[YTD Value Reporting CCY], Measures.[LTD Value Reporting CCY]});

This = [Reporting Currency].[Reporting Currency].[GBP] *

( Measures.[Rate],

[Reporting Currency].[Reporting Currency].CurrentMember

);

End Scope;

End Scope;

End Scope;

Now I have a situation in which the FX rates can be different for each record in the fact table. So I have introducted the FX Rate Set ID dimension (degenerate dimension for the rate measure group) and also have it as a measure (non-additive, visible=false) for the Balance measure group that contain the data to be converted. Now, how do I acheive the actual conversion by modifying the above MDX?

Something similar to the below is what I'm trying to acheive but no idea how to get it right in MDX. Please help in the correct approach and the right MDX.

Code Snippet

This = [Reporting Currency].[Reporting Currency].[GBP] *

( Measures.[Rate],

[Reporting Currency].[Reporting Currency].CurrentMember,

[FX Rate Set].[Set ID].[<<'Value of Measures.[Rate Set ID]'>>]

);

Thanks in advance.

Cheers,

Arun

Pl can anyone help me on this?

|||Could you explain this statement with an example: "I have a situation in which the FX rates can be different for each record in the fact table"? Are you referring to the Balance or the FX Rate fact table - and does this mean that FX rates may vary on the same day?|||

Below is the sample data

Fx Rates : (the pivot currency is GBP)

Date RateSet ToCurrency Rate

03 May 2007 1 AUD 2.54

03 May 2007 2 AUD 2.51

03 May 2007 1 GBP 1

03 May 2007 2 GBP 1

03 May 2007 1 EUR 1.51

03 May 2007 2 EUR 1.6

Fact Table:

ID Date <<Other dimension keys>>> Currency Balance BalanceInGBP FXRateSet

101 03 May 2007 USD 100 195 1

102 03 May 2007 USD 100 194 2

Measure Group:

Balance MG:

Balance (FactTable.Balance)

BalanceReportingCurrency (FactTable.BalanceInGBP)

Rates:

Rate ([Fx Rates].Rate)

Calculation :

In my first post... (the column names may be bit different.)

In the above scenario, the for the same date, I may have to use different "Fx rate Set" whose ID is present in both Fact table and Fx rates table. I have a dimension called Currency that joins with the Currency column in fact table. I also have another dimension called [Reporting Hierarchy] that will hold the ToCurrency in Fx Rates and based on which the conversion will be done dynamically using calculations (The BalanceReportingCurrency measure will be calcualted dynamically using corresponding rates).

|||Forgot to mention - I have the FXRateSetID from the fact table as a measure in the balance MG as it may help in doing the calculation|||One idea is to slightly change the original solution by creating a new dimension like DailyRateSet, whose key attribute is a collection of the 2 fields: Date, RateSetID. This dimension would be used as the intermediate dimension, instead of Calendar. Another approach would be with a separate RateSet dimension, but I haven't been able to work that out yet.|||

Finally got it working. I'm having the Rate Set as another dimension referred by both fact table and the Fx rates table, then whilst doing the calculation, I perform the calc at leaf levels of both Fx rate set and Time - and using the fx rate set dimension for getting the correct rate.

At present, I convert all values to GBP when laoding to my cube. To make the data independent of rate change problems, now I'm trying to work out many-to many currency conversion.