Exp of Lookup Tables in OBIEE 11g

Lookup table is a new feature in obiee 11g.

This is used mainly to:
• Resolve code columns and get description/name values from a lookup table.
• It can also be used for checking the currency conversion values from a daily updated source.

Lets try to see how this works.Assume that our customer table XW_CUSTOMER_D, has a column cust_state which is just the state code and not the state name:

We also have a table called STATE in the database which contains the state code and state names:

If we had a customer dimension and want to show the state name, instead of state code we need to create a lookup based on the STATE table.

Step-1

Import the STATE table into the physical layer of OBIEE and make an alias out of it.

Step-2

Define the Key column for the STATE table in the physical layer.

Step-3

Pull the lookup table to the BMM layer. You notice that it appears as a fact table with a # symbol in its icon(see arrow below). This is because the lookup table is not joined to any other table and OBIEE assumes it to be a fact table by default.

Step-4

Double click on Lkup_State_Name logical table in the BMM layer and check the “Lookup table” checkbox. In the Keys tab add a key which is based on the ID column.

Step-5

Define 2 new logical columns in the Dimn_Customers_D logical dimension table by right clicking on the lodical table name as follows :

These 2 columns are “Derived from existing columns using an expression”

1-Dense Looked Up State Name

Formula:
Lookup(DENSE "Sales"."Lkup_State_Name"."STATE_NAME",-> Lookup Value
"Sales"."Dimn_Customer_D"."Customer State"-> Lookup column
)

2-Sparse Looked Up State Name

Formula:
Lookup(SPARSE "Sales"."Lkup_State_Name"."STATE_NAME", -> Lookup Value
‘Lookup State Name not Found', ->String used when code not found(remove)
"Sales"."Dimn_Customer_D"."Customer State" -> Lookup column
)

We now have the 2 lookup column in place in the customer dimension as follows :

Step -6

Pull the new columns to the presentation layer into the customer dimension display folder and save your work.

---

Once done we create a report with the “Customer State” and “Sparse Looked up State Name”. The result will be as follows. Notice that code ‘UT1’ doesn’t have a corresponding lookup value in STATE lookup table and we see the string “Lookup State Name not Found”

This shows that “SPARSE” lookup causes an outer join between the customer and the lookup table.

The OBIEE server fires 2 SQL’s as follows and joins the results in the reporting layer.

SAWITH0 AS (select distinct T13265.CUST_STATE as c1
from
XW_CUSTOMER_D T13265 /* Dimn_CUSTOMER_D */ )
select distinct 0 as c1,
D1.c1 as c2
from
SAWITH0 D1

And

select T42371.STATE_NAME as c1,
T42371.ID as c2
from
STATE T42371 /* Lkup_State_Name */

Next we create a report with the “Dense Looked UP State Name” and we see the following result.

This shows that DENSE lookup executes an inner join between the customer and lookup table

We had a value ‘UT1’ which was not brought forward due to the inner join by the dense lookup, since that value was not present in STATE lookup table.

Moral :

Dense lookup = Inner join between Dimension and lookup table
Sparse Lookup=Outer join (with Nulls having a custom name) Dimension and lookup table

Hope you enjoyed this blog !!