Oracle BI EE 10.1.3.4.1 – Currency Conversions & FX Translations – Part 1
One of the common requirements when implementing BI EE is the ability to handle multiple input currencies. This is a pretty common requirement especially in business scenarios where multiple countries/currencies are involved. In such cases, many of the finance related measures like Sales etc will come in local currencies. So, as part of the BI EE setup we need to ensure that such local currency transactions are converted to a common reporting currency. There are 2 types of currency conversions
- Local Currency to Reporting Currency Conversion – This is the most common requirement where individual transactions are converted into a common Reporting currency and then rolled up for reporting.
- Reporting Currency Restatements – This generally is a finance requirement where the common input reporting currency(assuming input data itself comes in reporting currency) will have to be analyzed for varying rates. I will cover this in the next blog post.
I will be covering the first requirement in this blog post i.e. converting local currency to reporting currency. I shall be using a modified form of the Oracle Sample SH schema. The high level physical schematic diagram is given below
Basically every transaction in the SALES fact table is a transaction that was done in the individual countries. For example, this fact table will have a AMOUNT_SOLD of say 100 EUR if the customer of the transaction is from say Belgium(i.e. product was bought in Belgium). The same fact table will also have an AMOUNT_SOLD of say 150 USD if the product was bought by a customer in United States.
Every Country will have a single local currency(USD, EUR, GBP etc). So, basically the Countries table above will have CURRENCY_CODE as an attribute of a Country.
Finally we have a rates table called CURRENCY_RATES which will basically store the daily fluctuating rates. For the purposes of this blog post, i will assume that there is only one common reporting currency which is USD.
There are 2 ways to do currency conversion. I will list them below
- Do the Rate Multiplication only at the grain of the rates (Time & Customer dimension) and not for every transaction.
- Do the Rate Multiplication to each and every transaction.
I will discuss both the above techniques here. I generally prefer the first one as in many cases that turns out to be much faster than the other.
Rate Multiplication at Grain of Rates:
Consider the following query
SELECT CURRENCY_CODE, COUNTRY_NAME, A.TIME_ID, AMOUNT_SOLD FROM SALES A, TIMES B, CUSTOMERS C, COUNTRIES D WHERE A.TIME_ID = B.TIME_ID AND A.CUST_ID = C.CUST_ID AND C.COUNTRY_ID = D.COUNTRY_ID ORDER BY 2,3
The above query produces the following data.
In our Rates table we have, one rate for every Day/Currency combination
As you see above, we can arrive at the FX rate conversion in 2 ways. Multiply each row in the SALES fact table with the rate and then do the roll-ups. For example,
Or we can Roll-up each transaction at the Day/Currency Level and then multiply with the rates. In plain math, all we are trying to do is
a*d+b*d+c*d = (a+b+c)*d
We start off with doing the latter i.e. do the multiplication once the roll-ups are done (but at the grain of the rates). To implement this in the repository, we need to model the rates as a separate fact table. The RATE measure will take Average (or any aggregation except none as we will always enforce the lower-most level multiplication and hence it does not matter) as the aggregation. Since RATE’s do not conform to other dimensions like Product, Promotions & Channels, we need to assign the measure to the Total level of each of the non-conforming dimensions. Also, we need to assign the RATE to the Day level of the Time dimension and the Country Level of the Customer dimension. This will ensure that we get a unique and the correct rate. To Test whether the rates work correctly, lets create a very simple report as shown below
As you see, we are able to produce the RATE values even for non-conforming CHANNEL dimension. Also, for all the cities within a Country we get the same RATE. This is what assigning of the levels do to the RATE measure. Now create a Logical Column which will multiply the RATE with the Sales Measure
Since we are enforcing the levels in the RATE measure, the same enforcement will happen for the resulting measure as well. Lets create a report as shown below
If we look at the SQL Query, we will notice that the joins of the Rates and the Sales Measure will be enforced only at the RATE grain (through an outer query block) as against every transaction which is not necessary.
WITH
SAWITH0 AS (select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5,
D1.c6 as c6,
D1.c7 as c7,
D1.c8 as c8,
D1.c9 as c9
from
(select sum(T12697.AMOUNT_SOLD) as c1,
T12623.CHANNEL_CLASS as c2,
T12638.CUST_CITY as c3,
T12623.CHANNEL_CLASS_ID as c4,
T12710.FISCAL_YEAR as c5,
T13704.COUNTRY_NAME as c6,
T12710.TIME_ID as c7,
T12710.FISCAL_YEAR_ID as c8,
T13704.COUNTRY_ID as c9,
ROW_NUMBER() OVER (PARTITION BY T12623.CHANNEL_CLASS_ID, T12638.CUST_CITY, T12710.TIME_ID, T13704.COUNTRY_ID ORDER BY T12623.CHANNEL_CLASS_ID ASC, T12638.CUST_CITY ASC, T12710.TIME_ID ASC, T13704.COUNTRY_ID ASC) as c10
from
TIMES T12710,
COUNTRIES T13704,
CUSTOMERS T12638,
CHANNELS T12623,
SALES T12697
where ( T12638.COUNTRY_ID = T13704.COUNTRY_ID and T12623.CHANNEL_ID = T12697.CHANNEL_ID and T12638.CUST_ID = T12697.CUST_ID and T12697.TIME_ID = T12710.TIME_ID )
group by T12623.CHANNEL_CLASS, T12623.CHANNEL_CLASS_ID, T12638.CUST_CITY, T12710.FISCAL_YEAR, T12710.FISCAL_YEAR_ID, T12710.TIME_ID, T13704.COUNTRY_ID, T13704.COUNTRY_NAME
) D1
where ( D1.c10 = 1 ) ),
SAWITH1 AS (select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5,
D1.c6 as c6
from
(select avg(T17009.RATE) as c1,
T12710.FISCAL_YEAR as c2,
T13704.COUNTRY_NAME as c3,
T12710.TIME_ID as c4,
T12710.FISCAL_YEAR_ID as c5,
T13704.COUNTRY_ID as c6,
ROW_NUMBER() OVER (PARTITION BY T12710.TIME_ID, T13704.COUNTRY_ID ORDER BY T12710.TIME_ID ASC, T13704.COUNTRY_ID ASC) as c7
from
TIMES T12710,
COUNTRIES T13704,
CURRENCY_RATES T17009
where ( T12710.TIME_ID = T17009.RATE_DATE and T13704.CURRENCY_CODE = T17009.FROM_CURRENCY )
group by T12710.FISCAL_YEAR, T12710.FISCAL_YEAR_ID, T12710.TIME_ID, T13704.COUNTRY_ID, T13704.COUNTRY_NAME
) D1
where ( D1.c7 = 1 ) ),
SAWITH2 AS (select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5,
D1.c6 as c6,
D1.c7 as c7,
D1.c8 as c8,
D1.c9 as c9,
D1.c10 as c10
from
(select SAWITH0.c2 as c1,
case when SAWITH0.c5 is not null then SAWITH0.c5 when SAWITH1.c2 is not null then SAWITH1.c2 end as c2,
case when SAWITH0.c6 is not null then SAWITH0.c6 when SAWITH1.c3 is not null then SAWITH1.c3 end as c3,
SAWITH0.c3 as c4,
SAWITH0.c1 as c5,
SAWITH0.c1 * SAWITH1.c1 as c6,
case when SAWITH1.c4 is not null then SAWITH1.c4 when SAWITH0.c7 is not null then SAWITH0.c7 end as c7,
SAWITH0.c4 as c8,
case when SAWITH0.c8 is not null then SAWITH0.c8 when SAWITH1.c5 is not null then SAWITH1.c5 end as c9,
case when SAWITH1.c6 is not null then SAWITH1.c6 when SAWITH0.c9 is not null then SAWITH0.c9 end as c10,
ROW_NUMBER() OVER (PARTITION BY SAWITH0.c2, SAWITH0.c3, SAWITH0.c4, case when SAWITH0.c5 is not null then SAWITH0.c5 when SAWITH1.c2 is not null then SAWITH1.c2 end , case when SAWITH0.c6 is not null then SAWITH0.c6 when SAWITH1.c3 is not null then SAWITH1.c3 end , case when SAWITH0.c8 is not null then SAWITH0.c8 when SAWITH1.c5 is not null then SAWITH1.c5 end , case when SAWITH1.c4 is not null then SAWITH1.c4 when SAWITH0.c7 is not null then SAWITH0.c7 end , case when SAWITH1.c6 is not null then SAWITH1.c6 when SAWITH0.c9 is not null then SAWITH0.c9 end ORDER BY SAWITH0.c2 ASC, SAWITH0.c3 ASC, SAWITH0.c4 ASC, case when SAWITH0.c5 is not null then SAWITH0.c5 when SAWITH1.c2 is not null then SAWITH1.c2 end ASC, case when SAWITH0.c6 is not null then SAWITH0.c6 when SAWITH1.c3 is not null then SAWITH1.c3 end ASC, case when SAWITH0.c8 is not null then SAWITH0.c8 when SAWITH1.c5 is not null then SAWITH1.c5 end ASC, case when SAWITH1.c4 is not null then SAWITH1.c4 when SAWITH0.c7 is not null then SAWITH0.c7 end ASC, case when SAWITH1.c6 is not null then SAWITH1.c6 when SAWITH0.c9 is not null then SAWITH0.c9 end ASC) as c11
from
SAWITH0 full outer join SAWITH1 On SAWITH0.c7 = SAWITH1.c4 and SAWITH0.c9 = SAWITH1.c6
) D1
where ( D1.c11 = 1 ) )
select SAWITH2.c1 as c1,
SAWITH2.c2 as c2,
SAWITH2.c3 as c3,
SAWITH2.c4 as c4,
SAWITH2.c5 as c5,
SAWITH2.c6 as c6
from
SAWITH2
order by c1, c2, c3, c4
Though the query above might look big, this performs really well as the multiplication happens only for a select set of records. But there is one downside to this approach though. If we do not have Time or Customer dimension in the report, the currency converted measure will still go at the grain of Day and the Country as shown below
The only way to roll-these up in such cases is to use the Pivot Table(or custom Logical SQL) as there is no capability currently in BI Server to roll-up a level based measure (after enforcing the levels). The other option is to enforce a filter whenever either Time or Customer dimensions are not chosen. The biggest advantage of this method though is in doing Currency Conversions for YTD, MTD measures. In the Case YTD, MTD measures, there might be a requirement to multiply the latest rate for that Month/Year as against multiplying the rate for each day. In such cases, all we need to do is to create Rate YTD, Rate MTD fact tables as shown below
The Rate Measures in each of the separate logical fact tables will be assigned to the Month & Year level respectively.
And their respective Logical Table Source will have the filters applied as shown below (shown for YTD).
This will ensure that we have the capability to determine which rates we need to multiply with what measure.
Rate Multiplication for every transaction:
This method is suited if we have the Rate Stored as an attribute of the Fact Measures themselves (as against a separate fact table with differing grain). But sometimes we might have a need to actually multiply the rates for each and every transaction. In such cases, we can use the approach wherein we bring in the Rates table as an inner-joined table to the main fact(or we can model it as a dimension depending on what is required).
Now if we create a report using this
you will notice that the join is pushed into the main fact table itself. Sometimes this might perform better especially when we apply filters properly. The SQL produced for this is given below
WITH
SAWITH0 AS (select T12623.CHANNEL_CLASS as c1,
T13704.COUNTRY_NAME as c2,
sum(T12697.AMOUNT_SOLD * T18227.RATE) as c3,
T12623.CHANNEL_CLASS_ID as c4,
T13704.COUNTRY_ID as c5
from
COUNTRIES T13704,
CUSTOMERS T12638,
CHANNELS T12623,
SALES T12697,
CURRENCY_RATES_FACT T18227
where ( T12623.CHANNEL_ID = T12697.CHANNEL_ID and T12638.COUNTRY_ID = T13704.COUNTRY_ID and T12638.CUST_ID = T12697.CUST_ID and T12697.CUST_ID = T18227.CUST_ID and T12697.TIME_ID = T18227.TIME_ID )
group by T12623.CHANNEL_CLASS, T12623.CHANNEL_CLASS_ID, T13704.COUNTRY_ID, T13704.COUNTRY_NAME)
select SAWITH0.c1 as c1,
SAWITH0.c2 as c2,
SAWITH0.c3 as c3
from
SAWITH0
order by c1, c2
The important point to note though is the fact that we cannot easily achieve the MTD & YTD rate conversion functionality that we saw above in the first method. Though it is possible, it will take some amount of work to make it perform well.
As you see both the methods above have their own pros and cons. Of course, in your case the actual scenario might be completely different (like you might have both local currency as well as Reported Currency stored in DW etc) but this should hopefully be useful for people who are starting with a currency conversion requirement in BI EE.