Create Logical Lookup Tables and Logical Lookup Columns

Create Logical Lookup Tables

You create a logical lookup table object in the business model to define the necessary metadata for a translation lookup table.

A lookup table is a logical table with a property that designates it as a lookup table, as described in Designate a Logical Table as a Lookup Table. The figure below provides an example of a lookup table.

Product_Translation Table

Product_Code	Language_Key	Description
A123	DE	Brot
A123	IT	Pane
B234	DE	Marmelade
B234	IT	Marmelde di agrumi
C345	DE	Milch
C345	IT	Latte

Each of the lookup table's primary keys are considered together as a Lookup Key and perform the lookup. The lookup can be performed only when the values for all lookup key columns are provided. For example, in the figure above, the combined Product_Code and Language_Key form the primary key of this lookup table.
A lookup key is different from a logical table key because lookup key columns are order sensitive. For example, Product_Code and Language_Key are considered a different lookup key to Language_Key and Product_Code. All columns of the lookup key must be joined in the lookup function.
A lookup table has a combination of lookup keys.
A lookup table has at least one value column. In the figure above, the value column is Description, and it contains the translated value for the product description.
There must be a functional dependency from a lookup key to each value column. That is the lookup key can identify the value column. 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.

The consistency check 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 aggregation results when using lookup columns should match the results from the base column. For example, the following code
```
SELECT productname_trans.productname, sales.revenue FROM snowflakesales;
```
should return the same results as
```
SELECT product.productname, sales.revenue FROM snowflakesales;
```
If the lookup table productname_trans in this example uses the lookup key ProductID and LANGUAGE, then both queries return the same aggregation results.

If the lookup key contains a column with a different aggregation level to productname, then the query grain changes and this affects the aggregation.

Designate a Logical Table as a Lookup Table

A logical table must be designated as a lookup table (using the Semantic Modeler) before you can use it as a lookup table.

To designate a logical table as a lookup table, you must first import the lookup table into the physical layer and drag and drop it into the logical layer.

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. You use the Semantic Modeler to change the order of primary key columns.

About the LOOKUP Function Syntax

A LOOKUP function is typically used in the logical layer, as an expression in a translated logical table column.

The syntax of the LOOKUP function is as follows:

Lookup ::= LookUp([DENSE] value_column, expression_list ) | LookUp(SPARSE value_
column, base_column, expression_list )

expression_list ::= expr {, expression_list }

expr ::= logical_column | session_variable | literal

For example:

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

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

Note the following:

A LOOKUP function is either dense or sparse, and is specified using the keyword DENSE or SPARSE. The default behavior is dense lookup, if neither DENSE or SPARSE is specified. For DENSE lookup, the translation table is joined to the base table through an inner join, while for SPARSE lookup, a left outer join is performed.
The first parameter (the parameter after the DENSE or SPARSE keyword) must be a valid value column from a valid lookup table that's defined in the logical layer.
If the SPARSE keyword is given, then the second parameter must be a column that provides the base value of the value_column. For DENSE lookup, this base column isn't required.
The number of expressions in the expression_list must 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's specified in the expression list must also match the lookup key columns one by one in order.

For example:
- The lookup key for lookup table ProductName_TRANS is both Product_code and Language_Key
- The expressions in expression_list are SnowflakeSales.Product.ProductID and VALUEOF(NQ_SESSION."LANGUAGE")
- The meaning of the lookup is:
  
  return the translated value of ProductName from the translation table with the condition of Product_code = SnowflakeSales.Product.ProductID and Language_Key = VALUEOF(NQ_SESSION."LANGUAGE")

Create Logical Lookup Columns

You use the Expression Builder in the Semantic Modeler to create a logical column that includes the lookup function.

The value of the logical column depends on the language that is associated with the current user.

You create a logical column using a derived column expression in the column properties pane located in the logical table's Columns tab. See Create Derived Columns.

For example to get the translated product name:

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

LAN_INT is a session variable that's populated by the session initialization block MLS and represents either the base language or other languages:

0 for base language (for example, en - English)
1 for other language codes (for example, fr - French, or cn - Chinese)

WEBLANGUAGE is a session variable that is initialized automatically, based on the language selected when a user logs in.

The INDEXCOL function helps to select the appropriate column. In the preceding example, the expression returns the value of the base column (ProductName) only if the user language is the base language (that is, when the value of session variable LAN_INT is 0). If the user language isn't the base language (when the value of the session variable LAN_INT is 1), then the expression returns the value from the lookup table of the language that's passed in the WEBLANGUAGE session variable.

When you use the DENSE function (shown in the previous example), if there's no value for a column in the translated language, then the lookup function displays a blank entry.

When you use the SPARSE function (shown in the following example), and there is no value for a column in the translated language, then the lookup function displays a corresponding value in the base language.

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