Answered by:
Currency Conversion and Role Playing Dimensions

Question
-
All,
I have a applied a many-to-one currency conversion solution in my Cube, to convert currencies depending on a currency type (day of booking and current day), along a Booking Date Role Playing Dimension :-
I need to be able to convert currencies along several other Role Playing Date Dimensions (Invoice Date for example), BUT use the day of booking to do the actual conversion.
For example, I have several bookings that are not yet invoiced, and have been given a default Invoice Date of 01/01/1900. Of course, there is no Currency Conversion Audit for 01/01/1900, so I will need to use the Booking Date to apply the Conversion, despite seeing, say, Revenue, at Invoice Date.
It will be very difficult for me to do the conversions in the fact Table due to the Numbers of Measures and "Conversion Types" that need to take place. It will mean updating and re-procesing the whole fact table each day
Here is the Conversion Code :-
SCOPE { [Measures].[Revenue] }; SCOPE ( Leaves([Booking Date]) ,[Reporting Currency].[Great British Pounds] ,Leaves([Currency]) ); // Convert Local value into Pivot currency for selected Measures that must be converted with Measure rate [Inverse] SCOPE [Currency Type].[Currency Type].&[1]; // Day of Booking THIS = [Reporting Currency].[Local] * Measures.[Inverse]; END SCOPE; SCOPE [Currency Type].[Currency Type].&[2]; // Currenct Day THIS = [Reporting Currency].[Local] * Measures.[Current Day Inverse]; END SCOPE; END SCOPE; END SCOPE;
Thanks
Paul
Answers
-
Hi Jerry,
I was trying to find a way of showing a "revenue" measure along one date dimension, where the revenue was "currency converted" using the values of antoher role-playing date dimension.
For example, I wanted to show all revenue at the date they were invoiced, BUT I wanted to convert the revenues by a booking date.
I managed to solve this however by using snippets of the following article:-
The trick, it would appear, is to have two date dimensions joined to the currency conversion audit fact table. Only one of which is joined to the bookings fact (on booking date)
Thanks
Paul
- Marked as answer by Paul Wallington Wednesday, April 11, 2012 9:24 AM
All replies
-
-
Hi Jerry,
I was trying to find a way of showing a "revenue" measure along one date dimension, where the revenue was "currency converted" using the values of antoher role-playing date dimension.
For example, I wanted to show all revenue at the date they were invoiced, BUT I wanted to convert the revenues by a booking date.
I managed to solve this however by using snippets of the following article:-
The trick, it would appear, is to have two date dimensions joined to the currency conversion audit fact table. Only one of which is joined to the bookings fact (on booking date)
Thanks
Paul
- Marked as answer by Paul Wallington Wednesday, April 11, 2012 9:24 AM