Oracle BI EE 11g – Lookup Tables – Sparse and Dense Lookups

August 17th, 2010 by

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.

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

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

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.


  1. Arvind Says:

    wonderful explanation. I guess it would be better to use physical lookups since database joins should perform better then in memory joins. It will reduce some network traffic as well.

  2. John Lilley Says:

    Works fine when looking up a character column. If you ty a numeric column then you may well get an error in Answers complaining that there is an incompatibble data type. The LOOKUP function appears to expect a VARCHAR. Just changing the column type in the physical layer won’t work, you will need to go back to your source table and change the type to VARCHAR, or slap a view on top instead, sacrilege!

  3. Srini Chandaka Says:

    Hi Venkat,

    Thanks for this great article and is quite useful info. Just a quick question – can we apply this sparse lookup to a fact table? As we have a report requirement that should display all days in the month from the date dimension with a value from sales fact, when there is no sale on a day the report should still show the day but with a zero sale. Is this something possible?

    Many thanks.

  4. Arvind Kumar Says:

    Hi Venkat,
    I was checking the query in version and for logical lookup also joins is pushed into Physical query not in memory. Is it some change.

  5. Kapil patil Says:

    I had followed the same method you given above .but when i pulling the columns having data type Clob with other dim it gives me View Display error as

    Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 14025] No fact table exists at the requested level of detail:

  6. Vikram Says:

    Works like a gem! thanks for details!

  7. Vikram Says:

    This used to work well in (which was generating physical outer joins) for lookups specified in BMM. However after we upgraded to, this stopped working. I am seeing that does not generate physical joins. Instead it tries to pass parameters :param1

  8. Rahul Gautam Says:

    Hi Venkat,

    I am using Obiee, given Dense Lookup syntax using 2 dimensions is not working. It gives an below errror on consistent check:
    [nQSError: 23019] The number of operands in lookup function does not match the number of lookup key columns

  9. Rahul Gautam Says:

    Hi Venkat,

    As per given example of Dense lookup.. I have a scenario with one lookup table and 2 dimension at BMM layer like
    “ALM BI”.”FTP Interest Rate and Method Lookup”.”INTEREST_RATE_CD” ,
    “ALM BI”.”Dim Currency”.”Currency Cd” ,
    “ALM BI”.”Dim FTP”.”N_FTP_ID”
    but gives an below error on consistent check
    [nQSError: 23019] The number of operands in lookup function does not match the number of lookup key columns.

    Can you please let me know it is right way to use dense look up

  10. sam Says:

    hi rahul gautam,
    You need to include all the primary key columns mentioned in the lookup table.

Website Design & Build: