A lookup in OBIEE is performed:
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:
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 :
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
For example:
LOOKUP( DENSE
SnowflakeSales.ProductName_TRANS.ProductName,
SnowflakeSales.Product.ProductID, VALUEOF(NQ_SESSION."LANGUAGE")
)
where:
The meaning is:
Product_code = SnowflakeSales.Product.ProductID and Language_Key = VALUEOF(NQ_SESSION."LANGUAGE")
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
For example:
LOOKUP( SPARSE
SnowflakeSales.ProductName_TRANS.ProductName,
SnowflakeSales.Product.ProductName,
SnowflakeSales.Product.ProductID, VALUEOF(NQ_SESSION."LANGUAGE")
)
where:
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:
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.
To create a logical lookup, you need to:
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.
To create a logical column with a lookup, you just need to enter your expression in the “derived from a logical column” box.
Physical lookup tables are similar to logical lookup tables in both semantics and usage. The only difference are that:
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:
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.
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.