OBIEE 11G - Lookup Function

About

A lookup in OBIEE is performed:

• in 10g with the help of a outer of inner join
• in 11G with the lookup function

In 11g, the lookup function was introduced in a localization perspective. It permits to support Multilingual data (the ability to display data from database schemas in multiple languages). Lookup tables contain then translations for descriptor/display columns in several languages, while the base tables contain the data in the base language. An example of a descriptor/display column might be Product_Name, which provides textual descriptions for a Product_Key column.

The following article talks about the lookup function introduce in 11g.

A LOOKUP function is either:

• dense (The default)
• or sparse.

In conjunction with the indexcol function, it's possible to create a calculation that inhibit the lookup when the language of the user is the base language.

A LOOKUP function can be used in :

• a physical column
• or a logical column

Articles Related

Calculation Syntax

Dense

For DENSE lookup, the translation table is joined to the base table through an inner join. In a dense Lookup, if a translation does not exist, the lookup key (line) is not displayed/present.

LookUp([DENSE] value_column, expression_list )

Where

• value_column is a valid value column from a valid lookup table that is defined in the logical layer.
• expression_list should be equal to the number of the lookup key columns that are defined in the lookup table, which is defined by the value_column. The expression that is specified in the expression list should also match the lookup key columns one by one in order.

For example:

LOOKUP( DENSE 
        SnowflakeSales.ProductName_TRANS.ProductName, 
        SnowflakeSales.Product.ProductID, VALUEOF(NQ_SESSION."LANGUAGE")
        )

where:

• SnowflakeSales.ProductName_TRANS is the lookup table
• SnowflakeSales.ProductName_TRANS.ProductName is the return value
• SnowflakeSales.Product.ProductID, VALUEOF(NQ_SESSION.“LANGUAGE”) define the primary key of the lookup table

The meaning is:

• return the translated value of ProductName (SnowflakeSales.ProductName_TRANS.ProductName)
• from the translation table *((SnowflakeSales.ProductName_TRANS)
• with the condition of

Product_code = SnowflakeSales.Product.ProductID and Language_Key = VALUEOF(NQ_SESSION."LANGUAGE")

Sparse

For SPARSE lookup, a left outer join is performed. In a sparse Lookup, if a translation does not exist, then the lookup key is displayed with the default value.

LookUp(SPARSE 
       value_column, 
       base_column, 
       expression_list )

Where

• value_column is a valid value column from a valid lookup table that is defined in the logical layer.
• base_column must be a column that provides the default value of the value_column.
• expression_list should be equal to the number of the lookup key columns that are defined in the lookup table, which is defined by the value_column. The expression that is specified in the expression list should also match the lookup key columns one by one in order.

For example:

LOOKUP( SPARSE 
        SnowflakeSales.ProductName_TRANS.ProductName, 
        SnowflakeSales.Product.ProductName, 
        SnowflakeSales.Product.ProductID, VALUEOF(NQ_SESSION."LANGUAGE")
        )

where:

• SnowflakeSales.ProductName_TRANS is the lookup table
• SnowflakeSales.ProductName_TRANS.ProductName is the return value
• SnowflakeSales.Product.ProductName is the default value when no matching key is found
• SnowflakeSales.Product.ProductID, VALUEOF(NQ_SESSION.“LANGUAGE”) define the primary key of the lookup table

Indexcol

In the context of localization (multilingual data), if the user use the based language, you don't need to perform a lookup.

To model this behaviour, you just need to envelop the lookup function with an indexcol function such as:

INDEXCOL( 
   VALUEOF(NQ_SESSION."LAN_INT"), 
   "Translated Lookup Tables"."Product"."ProductName", 
   LOOKUP( DENSE
           "Translated Lookup Tables"."Product Translations"."ProductName", 
           "Translated Lookup ables"."Product"."ProductID", 
           VALUEOF(NQ_SESSION."WEBLANGUAGE")
           )
   )

where:

• LAN_INT is a boolean session variable (1,O) to indicate if the user language is the base language.
• WEBLANGUAGE is the language selected when a user logs in.

Note: You cannot use a derived logical column that is the result of a LOOKUP function as part of a primary logical level key. This limitation exists because the LOOKUP operation is applied after aggregates are computed, but level key columns must be available before the aggregates are computed because they define the granularity at which the aggregates are calculated.

You can use a derived logical column that is the result of a LOOKUP function as a secondary logical level key.

Type of lookup

Logical

To create a logical lookup, you need to:

1. design a logical table as a lookup table
2. enter the expression in the derived from existing logical column

Lookup table

To designate a logical table as a lookup table, you must first import the lookup table into the physical layer and drop it into the Business Model and Mapping layer using the Administration Tool. Then, for each logical lookup table, you must select the Lookup table option in the Logical Table dialog box.

• A lookup table has only one lookup key.
• The lookup key and value column should both belong to the same physical table.
• A lookup table is standalone without joining to any other logical tables.
• Consistency checking rules are relaxed for lookup tables such that if a table is designated as a lookup table, it need not be joined with any other table in the subject area (logical tables would normally be joined to at least one table in the subject area).
• The order in which the columns are specified in the lookup table primary key determines the order of the corresponding arguments in the LOOKUP function. For example, if the lookup table primary key consists of the RegionKey, CityKey, and LanguageKey columns, then the matching arguments in the LOOKUP function must be specified in the same order

Logical Measure

To create a logical column with a lookup, you just need to enter your expression in the “derived from a logical column” box.

Physical

Physical lookup tables are similar to logical lookup tables in both semantics and usage. The only difference are that:

• all the references to logical tables and columns are replaced by physical tables and columns.
• you don't need to designate a physical table as lookup table.

You just need to enter the formula/expression in the “Derived from Physical Mapping” field.

Physical lookup address the following scenarios that logical lookup cannot handle:

• The lookup table source is fragmented.

In this case, use multiple physical lookup tables to hold the values. For example, translation values for fragmented product name data can be distributed in two physical lookup tables called productname_trans_AtoM and productname_trans_NtoZ.

• Different levels of translation tables are available.

For example, translations are available in both an Essbase data source and a relational data source. It is preferable to use the same source as the base query.

Documentation / Reference

• 11g - Supporting Multilingual Data
• In the sample application 11g, the section 7.1 Logical Modelling / Lookup columns for the picture.