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.