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.
No comments:
Post a Comment