OBIEE 11g Lookup tables

1. Create the two tables in the database

  Customer Table

  State Table

    2.   Import the State Table in the physical layer and create Alias Tables.

        3.  Define the Key column for the State table.

   4.    Drag the Lookup State Table in the BMM layer.

       5.   Go to the Lookup state table and select Lookup Table in the Properties General Tab

 6.  Create a new logical column “Dense Looked Up State Name”

Using the Derived From existing Columns using an Expression

Write in the formula below:

Lookup(DENSE "Sample Sales"."LKP_State_Table"."STATE_NAME",

"Sample Sales"."Dimn_Customer_D"."CUST_STATE"

)

      7.     Create a new logical column “Parse Looked Up State Name”

Using the Derived From existing Columns using an Expression

Write in the formula below:

Lookup(SPARSE "Sample Sales"."LKP_State_Table"."STATE_NAME", 'Lookup State Name not Found',

"Sample Sales"."Dimn_Customer_D"."CUST_STATE")

      8.   Now We have Dense and Sparse Lookup Columns

      9.      Drag the columns to the presentation layer. 

10. Create Analysis and check your results , once using Sparse Look up and then Dense Lookup

NOTE: DENSE Lookup Executes an inner join between the customer and lookup table

NOTE: SPARSE lookup causes an outer join between the customer and the lookup Table.