Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 16 Oracle Analytics Lounge
- 215 Oracle Analytics News
- 43 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 79 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
OBIEE 11g exchange rate calculation on the reports
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
Answers
-
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
0 -
Thank you for the hint of using session variable in LOOKUP function and applying presentation variable value to session variable (request variable).
0 -
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
0 -
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.
0 -
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
0 -
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
0 -
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.
0 -
"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.
0