Oracle BI EE 11g - Lookup Tables - Sparse and Dense Lookups
A very important feature that has been introduced in 11g is the ability to model lookup tables in the repository. If you have worked with ETL tools before, lookup tables are quite common especially when we want to do a lot of lookup operations (id to description mappings). In 10g, to model lookup tables the only way was to make inner joins (equi join or outer joins) to the lookup tables through the Logical Table sources. But now in 11g, this ability has been added to reference both physical tables and logical tables.
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.
There are 2 different ways of modeling lookup tables. Lets go through each one of them in this blog entry.
1. Physical Lookups - To understand physical lookups, lets take a very simple example given below
We have 2 tables, CUSTOMERS and CUSTOMER_LKP. CUSTOMERS table has all the details of a customer with CUST_ID being the unique primary key. CUSTOMER_LKP has 3 columns CUST_ID, CUST_INCOME_LEVEL and CUST_MARITAL_STATUS with CUST_ID being the primary key. The main difference between these 2 tables is, not all customers in the main CUSTOMERS table have a corresponding income level and marital status.
To model the CUSTOMER_LKP as a lookup table, we need to first define a primary key. If there is more than one column acting as a primary key, ensure that the key contains all the columns. In the physical layer, there is no join needed on the CUSTOMERS and the CUSTOMER_LKP table.
Now, in the Business Model and Mapping layer, lets create a new column called Customer Income Level. After that is created, lets go to the LTS mapping and apply the following the function. 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.
Lookup( SPARSE "ORCL - SH".."SH"."CUSTOMER_LKP"."CUST_INCOME_LEVEL" , 'No Income Defined', "ORCL - SH".."SH"."CUSTOMERS"."CUST_ID")
What we have done here is we have directly referenced the lookup value column in our lookup function. Since not all customers have an income level set, SPARSE is used. The basic syntax of lookup functions is given below
LOOKUP ( SPARSE/DENSE #Lookup Value column from the Lookup Table#, #Default Value if there is no lookup value in the Lookup table# (only needed for SPARSE lookups), #Primary key columns from the main table )
Lets now create a report as shown below
Wherever the Customer Income Level is not defined those customers get defaulted to No Income Defined value. If we look at the underlying database query, there would be a left outer join that would be pushed automatically into the query due to this lookup function.
In the Physical Lookups option, the lookup operations are pushed to the database layer (wherever possible). Lets now look at the 2nd approach where the lookup operations will be pushed to the BI Server layer.
2. BMM Lookups:
11g now supports doing lookup operation in its own memory by modeling a logical table as a lookup table. For example, lets consider the below currency conversion exchange rates table.
This rates table has a composite primary key. To use the BMM lookups, lets create a new logical table in the BMM layer to hold the exchange rates table as shown below
To denote a table as a BMM lookup table, we need to enable the lookup option (if you notice, there is no more bridge table check box option like in 10g but just a lookup option)
When we enable a logical table as a lookup table, that means that this logical table does not require any BMM joins to either the fact or the dimension. So we can now have standalone tables in the BMM layer in 11g.
After enabling this, lets create a new logical column for extracting the rates. Since we will have a rate for every country and day, we will use DENSE lookups in this case. The function used for doing this is given below
Lookup ( DENSE "SH - Lookups"."Rates Lookup"."RATE" , "SH - Lookups"."Customers"."COUNTRY_ISO_CODE", "SH - Lookups"."Times"."TIME_ID" )
The syntax for the lookup functions remain the same as the one that we used in the physical lookups. But here we need to use logical table names instead of physical table names. To use this rate as part of a FX restatement, multiply the measures with the above rate lookup column. Lets now create a report and look at the query generated
The BMM lookup is now fired as a separate query. BI Server will do the in-memory joins between the rates and the measures & will do the aggregation as well.