OBIEE 11g – Lookup Tables – Sparse and Dense Lookups

Lookup table are mainly used for,

1. Resolve code columns and get description/name values from a lookup table.
2. Currency conversion.

There are 2 types of lookup tables.

1. Sparse Lookups – A sparse lookup basically means that the main driving table does not necessarily have corresponding lookup values in the lookup table for all the id values. This can be considered to be an equivalent of a Left Outer Join.

2. Dense Lookups – A dense lookup basically means that the main driving table will have matching lookup values in the lookup table for each of its unique id value. This can be considered to be an equivalent of an inner join.

In short, we can summarise all this in one equation,

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

To illustrate this in more detail,

Consider the following schema, PLAYERS & TEAM_LKP. You can download this schema from this link.

• PLAYERS table contains all the information of players with TEAM_ID being the unique primary key.

• TEAM_LKP has Team information with TEAM_ID being the primary key. 

The main difference between these 2 tables is, not all players in the main PLAYERS table have a corresponding team assigned in TEAM_LKP table.

1. Import those two corresponding table in Physical layer of your repository.

2. It is mandatory to define a table as a Lookup table, you must define a primary key for that particular table.

3. Take the lookup table to the BMM layer. Here, you will 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.

4. Double click on TEAM_LKP (Here in ref, Lookup table) 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.

5. Now, in the Business Model and Mapping layer, lets create a two columns called TEAM_DENSE & TEAM_SPARSE. After that one, lets go to the LTS mapping and apply the following the function for each of them resp. Both column should be derived from, "Derived from existing columns using an expression".

Note: If you have more than one column as a primary key, the order of columns used in the key should match with the column order in the Lookup function.

1. TEAM_DENSE:

Formula:  

LOOKUP( DENSE  "Loopkup Example"."TEAM_LKP"."TEAM" , "Loopkup Example"."PLAYERS"."TEAM_ID")

2. TEAM_SPARSE:

Formula:

 LOOKUP( SPARSE  "Loopkup Example"."TEAM_LKP"."TEAM" , 'Team Not Assigned', "Loopkup Example"."PLAYERS"."TEAM_ID")

6. We now have the 2 lookup columns in the PLAYERS dimension as follows:

7. Take the newly created columns to the presentation layer into the customer dimension display folder and save your work. Check consistency of your repository.

8. Create a sample analysis as follows and verify the result.

9. Notice that, you are getting only those players information whose correspondence value are present in Lookup table i.e TEAM_LKP.

By seeing query log, you can get to know that BI server is applying inner join between TEAM_LKP & PLAYERS table. So, we are not getting any records for Mario & Naymar.

10. Next we create a report with the “SPARSE TEAM”.

11. Notice that, you are getting all players information. The result will be as follows. Notice that, 'TEAM_ID' doesn’t have a corresponding lookup value in TEAM_LKP lookup table and we see the string “Team not assigned”.

Happy blogging. :-)