OBIEE 11g exchange rate calculation on the reports

L00ka

Hi all,

I would like to know if it is possible to develop OBIEE 11g repository structure in combination with analysis formulas to calculate exchange rate on the report with the next logic:

Table FACT:

GL_DATE      VALUE_ORIG     CURR_ORIG     COMPANY     VALUE_COMP     CURR_COMP

18/2/2016.     100                      USD                    1000               90.22                    EUR

19/2/2016.     110                      EUR                    1000               110                       EUR

19/2/2016.     150                      USD                    1100               135.18                  EUR

Exchange rate table:

DATE          FROM_CURR     TO_CURR     RATE

22/2/2016    USD                    EUR               0.9069

19/2/2016.   USD                    EUR               0.9012

18/2/2016.   USD                    EUR               0.9022

On the report there is a prompt for Date and Currency. Lets say 22/2/2016. is selected as default DATE and EUR as default Currency. Presentation variable PV_Date_1 has the DATE prompt value.

The report calculates the SUM of VALUE_COMP column of all the records which have DATE column less than prompt date.

The result for 22/2/2016. in EUR is 335.4.

Customer wants to see the value in USD calculated in a way explained below:

Column CURR_ORIG (if not in USD) calculated in USD, but with the exchange rate on prompt date (22/2/2016.), in our test case that would be the record in the middle in fact table. VALUE_ORIG = 110 * (1/0.9069) = 121.292

So the SUM would be 100 + 121.292 + 150 = 371.292.

Tricky thing is that the date of exchange rate is determined on the prompt. We can't have precalcualted values for running sum of all the possible dates. Second tricky thing is that we can't sum VALUE_COMP and multiply the result with exchange rate on prompt date, because VALUE_COMP value reflects the VALUE_ORIG value with exchange rate on GL_DATE.

Thank you

Thomas Dodds

Yes it is possible ...  here's an example right to the point:  Oracle BI EE 11g - Lookup Tables - Sparse and Dense Lookups - Rittman Mead Consulting

You'll just have to change the date key going in to be a variable set by the prompt ... OBIEE - How and where can I set a Request variable (SET VARIABLE) ? [Gerardnico]  note you need to set a session variable for this to work ...

Here's a bit about the request variable:  Everything Oracle  <- it's good despite the 10g content

L00ka

Thank you for the hint of using session variable in LOOKUP function and applying presentation variable value to session variable (request variable).

L00ka

Hi,

After I developed this method on development environment and tested with test case data, one thing came up.

As it is inner or left join in business model layer, it first SUMs all the data (in my case column VALUE_ORIG) and then multiples with the first rate he gets from lookup table.

In example, if I want to show the upper example data on the report with GL_DATE, RATE, CURR_ORIG and SUM(VALUE_ORIG multiplied with RATE) with 22/2/2016 in prompt date the report would give the next data:

GL_DATE          RATE          CURR_ORIG        VALUE_ORIG multiplied with rate

18/2/2016.          0.9069        USD                    100 * 0.9069

19/2/2016.          1                 EUR                    110 * 1

19/2/2016.          0.9069        USD                    150 * 0.9069

If I remove RATE and CURR_ORG columns the report would return the next data:

GL_DATE          VALUE_ORIG multiplied with rate

18/2/2016.         100 * 0.9069

19/2/2016.         (110 + 150) * 1     <- because RATE 1 is the first he finds in lookup table for 2 records with GL_DATE 19/2/2016

I guess this is how it works when the join is in Business Model with SUM measures.

Is there any other thing that could be done to multiply every record with RATE from lookup table?

I guess it needs to be physical join, but in physical join variables can't be used.

Thanx

Thomas Dodds

You can use the dense/sparse lookup functionality in the PHYSICAL mapping expression of the logical column as well as doing it in the logical expression.  So You can decide to multiply IN the retrieval, or multiply AFTER the retrieval.

in this blog entry (Oracle BI EE 11g - Lookup Tables - Sparse and Dense Lookups - Rittman Mead Consulting) the first example is doing it in the physical expression, the second is doing it in the logical expression ... both are valid and should allow you to achieve what you are working at.

L00ka

Thank You for the feedback.

I tried PHYSICAL mapping expression and still the same.

The thing is that the records are in different currency. In example below they are in RSD and USD. If I put CURRENCY on the report and calcualtions group by CURRENCY the calculation is correct. Example no2.

If I don't group by CURRENCY it will sum VALUE_ORIG and than multiply with the first rate he gets.

In first example below which is incorrect  (1878983.63 RSD + 31026.89 USD) * 0.00891 = 17016.20   (<- the result ont he picture is /1000 what is not the case on the second report below)

instead of (1878983.63 RSD * 0.00891) + (31026.89 USD * 1) = 47766,6724

Is there any way to multiply the record from the fact with the value in lookup table before SUM?

Thank you

Thomas Dodds

Is the key to the lookup for the rate taking into account BOTH origin and convert-to currency codes in addition to the date? 

Typically when doing this type of work the ETL is used to baseline the currency at the time of recording then you have a single origin currency to start from ... so you have a configured local currency (converted at ETL time) and a document currency (the original), then via lookup you can convert at run time from the local currency to the user-selected/preferred currency.

Since you don't have your data structured like the above, you lookup has to be a little more complicated ... so you need a composite style key, or at least that's how I'm seeing it

L00ka

Hi,

Thank you for your feedback.

Yes, the key to the lookup table for the rate is taking both origin and convert-to currency codes in along with date.

Lookup formula for the Exchange rate logical column is:

LOOKUP(SPARSE  "Exchange rate ALL"."RATE" , 1,VALUEOF(NQ_SESSION."SV_DLY_DATE_1"), "Dim Transaction Currency"."CURRENCY_CODE",  VALUEOF(NQ_SESSION."SV_DLY_TCURR"))

where SV_DLY_DATE_1 is report date, "Dim Transaction Currency"."CURRENCY_CODE" is document currency and SV_DLY_TCURR is report currency (convert-to currency).

I do have data in local currency converted at ETL time, but business users want data in local currency converted at report date.

I guess there needs to be group by document currency on the reports.

Thomas Dodds

"business users want data in local currency converted at report date" -- they want the rate at the run-time of the report or they want the rate at the time the original fact was recorded? 

The latter is typical - the former leads to a moving target for which each time the report is run a different answer is given.