Using Lookup tables in OBIEE (11g)

1.   Using lookup tables in OBIEE 11g

Lookups are new feature with obiee 11g. we can make use of this functionality to retrieve a value w.r.t the input given. using this will be very useful in situation like retrieving a multilingual attribute, reporting a conversion rate for the currency passed etc. Lets see how this works with a sample scenario.

1.1    Scenario

The Fact table contains the measure value in USD. Need to provide 2 more presentation measure cols in 2 different currencies (say AUD and INR) for reporting.

Constrains:

1.     Should not change the fact by adding the cols,

2.     Should not join the exchange rate table in the rpd.

Lets take the below sample tables

Table 1 : W_SAMPLE_FACT_LKP_JJN_TEST

PO_NUM	PART_NUM	LINE_AMT	CURRENCY	PO_DATE
6882741	0300-3261	109.34	USD	2/1/2009
6882742	0814-2150	9.99	USD	2/1/2009
6882743	0403-5751-02	29.47	USD	2/5/2009
6882744	0503-1660-01	9.87	USD	2/10/2009
6882745	0130-4639	146.97	USD	2/14/2009
6882746	0327-1354	977.92	USD	2/19/2009
6882747	0300-4725	1011.84	USD	2/20/2009
6882748	0416-1301-02	90.12	USD	2/21/2009
6882749	0319-2561	14.74	USD	2/23/2009
6882750	0130-4719	39.15	USD	2/25/2009

Table 2: CURRENCY_LKP_JJN_TEST

FROM_CURRENCY	TO_CURRENCY	EFFECTIVE_DATE	EXCHANGE_RATE
USD	INR	2/1/2009	48.79000781
USD	AUD	2/1/2009	1.45285486
USD	INR	2/2/2009	48.96998972
USD	AUD	2/2/2009	1.515381118
USD	INR	2/3/2009	48.96998972
USD	AUD	2/3/2009	1.515381118
……….	………..	………….	………..

 

1.2    Physical Layer – OBIEE Repository

·         Step 1

Import the two tables into the physical layer. Also imported the existing Time Dimension table for modeling

Define keys for the tables

1. W_SAMPLE_FACT_LKP_JJN_TEST , 2. CURRENCY_LKP_JJN_TEST

·         Step 2

Joined the Sample fact table with the Time Dimension

Physical Layer Join

(Time.CAL_DT = Fact.PO_DATE)

Note: there is no join between the exchange rate table and fact

1.3    BMM Layer – OBIEE Repository

·         Step 1

Bring the tables in BMM Layer

Mark the currency lkp table as a “lookup Table”

·         Step 2

Create the measure cols using lookup in the fact tables

Note:.

Now, the lookup table can be Sparse or Dense in nature. Basically Dense and Sparse are lookup operation rather than being type of tables.

Dense Lookup:

A Dense lookup table contains translations for all records in the base table.

Syntax :-
Lookup(DENSE <<lookupColumn>>, <<commaSeparatedExprs>>)

Where

<<lookupColumn>> is the col which we want the resut. Example:- Exchange rate column

<<commaSeparatedExprs>> is the cols we need to pass to the look up table to retrieve the value (lookup col). Example:- PO(Fact) table . Date, To currency.

Note that the <<commaSeparatedExprs>> should equate the keys we defined for the lookup table and should be passed in the order. In our example, the lookup table have the keys as EFFECTIVE_DATE and TO_CURRENCY. So while looking up this table, the passing value to the lookup table should be PO_DATE and the CURRENCY to which we need to convert the value for to retrieve

Sparse Lookup:

A sparse lookup table will have translations only for some records in the base table.

Syntax:-

Lookup(SPARSE <<lookupColumn>>, <<alternateColumn>>,  <<commaSeparatedExprs>>)

Where

<<lookupColumn>> is the col which we want the result

<<alternateColumn>> is the col or value you want to specify if the translation value is not available

<<commaSeparatedExprs>> is the cols we need to pass to the look up table to retrieve the value (lookup col)

Its also possible that lookup tables can be are both dense and sparse in nature.
Example:- Lets say the lookup table for product category, product commodity translation might contain complete translation for the product commodity field but only partial translation for the product category field.

Logical measure Cols

1.     Line Amt AUD (Line amount in AUD)

Logic

1.     We have the Line Amount in USD available as Line_AMT in the fact table

2.     We need the USD to AUD exchange rate for the PO_DATE so that the Line_amount * exchange rate will give us the Line Amount in AUD currency

So the formula here will be,

Lookup(DENSE   "Multi_Curr_Test"."CURRENCY_LKP_JJN_TEST"."EXCHANGE_RATE" ,  "Multi_Curr_Test"."W_SAMPLE_FACT_LKP_JJN_TEST"."PO_DATE" ,'AUD')

*

"Multi_Curr_Test"."W_SAMPLE_FACT_LKP_JJN_TEST"."LINE_AMT"

Note: I’ve passed the to_currency as ‘AUD’ hardcoded here as the second parameter to retrieve the exchange rate

2.     Line Amt GBP (Line amount in GBP)

Lets create one more logical col to evaluate the sparse lookup function. Here we don’t have the exchange rate available in GBP. So lets give the alternate rate as “0” so that in the report we can see the lone amount in GBO coming as 0.

Formula:

Lookup(SPARSE   "Multi_Curr_Test"."CURRENCY_LKP_JJN_TEST"."EXCHANGE_RATE"  , 0,  "Multi_Curr_Test"."W_SAMPLE_FACT_LKP_JJN_TEST"."PO_DATE" ,'GBP')

*

 "Multi_Curr_Test"."W_SAMPLE_FACT_LKP_JJN_TEST"."LINE_AMT"

·         Step 3

Bring the cols to the presentation layer

1.4    OBIEE Reports - Test / Results

Lets test the results

1.     Dense Lookup

Created a simple report with Time.Cal_Date, Fact.PO_NUM, LINE_AMT (amount in USD in our case), Line Amt AUD (the new logical col we created with DENSE Lookup

·         Query Generated

Logical Request (before navigation)

RqList  distinct 
    0 as c1 GB,
    TIME.CAL_DT as c2 GB,
    W_SAMPLE_FACT_LKP_JJN_TEST.LINE_AMT * lookup( DENSE CURRENCY_LKP_JJN_TEST.EXCHANGE_RATE, W_SAMPLE_FACT_LKP_JJN_TEST.PO_DATE, 'AUD')  as c3 GB,
    W_SAMPLE_FACT_LKP_JJN_TEST.LINE_AMT as c4 GB,
    W_SAMPLE_FACT_LKP_JJN_TEST.PO_NUM as c5 GB
OrderBy: c1 asc, c2 asc NULLS LAST, c5 asc NULLS LAST, c4 asc NULLS LAST, c3 asc NULLS LAST, c2 asc NULLS LAST 

Physical Query

WITH 
SAWITH0 AS (select T1892596.PO_DATE as c1,
     T1892596.LINE_AMT as c2
from 
     W_SAMPLE_FACT_LKP_JJN_TEST T1892596),
SAWITH1 AS (select distinct 0 as c1,
     D1.c2 as c2,
     D1.c1 as c3
from 
     SAWITH0 D1),
SAWITH2 AS (select T1892602.EXCHANGE_RATE as c1,
     T1892602.EFFECTIVE_DATE as c2
from 
     CURRENCY_LKP_JJN_TEST T1892602
where  ( T1892602.TO_CURRENCY = 'AUD' ) ),
SAWITH3 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4
from 
     (select D1.c1 as c1,
               D1.c2 * D2.c1 as c2,
               D1.c2 as c3,
               D1.c3 as c4,
               ROW_NUMBER() OVER (PARTITION BY D1.c2 * D2.c1, D1.c2, D1.c3 ORDER BY D1.c2 * D2.c1 ASC, D1.c2 ASC, D1.c3 ASC) as c5
          from 
               SAWITH1 D1 inner join SAWITH2 D2 On D1.c3 = D2.c2
     ) D1
where  ( D1.c5 = 1 ) )
select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3
from 
     SAWITH3 D1
order by c1, c3, c2
 

2.       Sparse Lookup

Created a simple report with Time.Cal_Date, Fact.PO_NUM, LINE_AMT (amount in USD in our case), Line Amt GBP (the new logical col we created with SPARSE Lookup which don’t have an exchange rate in the table and we set to return 0 as excg rate in the function and hence the Line Amt GPB should return 0

·         Query Generated

Logical Request (before navigation)

 RqList  distinct 
    0 as c1 GB,
    TIME.CAL_DT as c2 GB,
    W_SAMPLE_FACT_LKP_JJN_TEST.LINE_AMT * lookup( SPARSE CURRENCY_LKP_JJN_TEST.EXCHANGE_RATE, 0, W_SAMPLE_FACT_LKP_JJN_TEST.PO_DATE, 'GBP')  as c3 GB,
    W_SAMPLE_FACT_LKP_JJN_TEST.LINE_AMT as c4 GB,
    W_SAMPLE_FACT_LKP_JJN_TEST.PO_NUM as c5 GB
OrderBy: c1 asc, c2 asc NULLS LAST, c5 asc NULLS LAST, c4 asc NULLS LAST 

Physical Query

 WITH 
SAWITH0 AS (select T1892596.PO_DATE as c1,
     T1892596.PO_NUM as c2,
     T1892596.LINE_AMT as c3,
     T1473138.CAL_DT as c4
from 
     TIME T1473138,
     W_SAMPLE_FACT_LKP_JJN_TEST T1892596
where  ( T1473138.CAL_DT = T1892596.PO_DATE ) ),
SAWITH1 AS (select distinct 0 as c1,
     D1.c4 as c2,
     D1.c3 as c3,
     D1.c2 as c4,
     D1.c1 as c5
from 
     SAWITH0 D1),
SAWITH2 AS (select T1892602.EXCHANGE_RATE as c1,
     T1892602.EFFECTIVE_DATE as c2
from 
     CURRENCY_LKP_JJN_TEST T1892602
where  ( T1892602.TO_CURRENCY = 'GBP' ) ),
SAWITH3 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 D1.c1 as c1,
               D1.c2 as c2,
               D1.c3 * nvl(D2.c1 , D1.c1) as c3,
               D1.c3 as c4,
               D1.c4 as c5,
               D1.c5 as c6,
               ROW_NUMBER() OVER (PARTITION BY D1.c3 * nvl(D2.c1 , D1.c1), D1.c2, D1.c3, D1.c4, D1.c5 ORDER BY D1.c3 * nvl(D2.c1 , D1.c1) ASC, D1.c2 ASC, D1.c3 ASC, D1.c4 ASC, D1.c5 ASC) as c7
          from 
               SAWITH1 D1 left outer join SAWITH2 D2 On D1.c5 = D2.c2
     ) D1
where  ( D1.c7 = 1 ) )
select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c4 as c4,
     D1.c5 as c5
from 
     SAWITH3 D1
order by c1, c2, c5, c4

/*--------------------------------CREATED_BY : JJN ; CREATED_DATE : 14-Feb-2012---------------------------------*/