1 person found this helpful
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
Thank you for the hint of using session variable in LOOKUP function and applying presentation variable value to session variable (request variable).
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.
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.
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?
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
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.
"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.