1. Building Semantic Models in Oracle Analytics Server
  2. Create and Build Your Model
  3. Support Multilingual Data
  4. Create Physical Lookup Tables and Physical Lookup Columns

Create Physical Lookup Tables and Physical Lookup Columns

You can create physical lookup table objects in the logical layer to define the necessary metadata for translation lookup tables. Physical lookup tables are similar to logical lookup tables in both semantics and usage.

Physical lookup tables address the following scenarios that logical lookup tables can't 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. It's preferable to use the same source as the base query.

Unlike logical lookup tables, you configure physical lookup tables by constructing lookup functions in the logical table source mapping.

For example, suppose that you have the following physical tables:

  • A base table called Categories, with columns such as categoryid and categoryname.

  • A translation table called Categories_Trans, with columns such as categoryid, language_key, and categoryname. The translated value of categoryname is determined through a combination of the categoryid and language_key columns.

Suppose that you have a logical table called Categories. In that table, you add a new logical column called categoryname_p, which is a translation column that depends on the current language. The column isn't derived from any other logical column (unlike logical lookup columns).

The following procedure explains how to configure a physical lookup translation column using the previous example.

  1. On the Home page, click Navigator and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. Click Logical Layer and browse for and double-click the table (for example, Categories) where you want to add a logical column.
  4. In the logical table, click Columns and then click Add Column. In the New Column_1 row enter a column name (for example, categoryname_p). Press Enter.
  5. Click Sources.
  6. In the logical table sources list table, click the logical table source and then click Detail view to open the properties pane.
  7. Scroll to Column Mapping, click the Show field, and select Unmapped.
  8. Locate and click the new logical column (for example, categoryname_p) to select it, and then click it again to display the expression field. Click the expression editor icon and create an expression similar to the following:
    INDEXCOL(VALUEOF(NQ_SESSION."LAN_INT"),
"DB_Name"."My_Category"."My_Schema"."Categories"."CategoryName", LOOKUP(SPARSE 
"DB_Name"."My_Category"."My_Schema"."CATEGORIES_TRANS"."CATEGORYNAME",
"DB_Name"."My_Category"."My_Schema"."Categories"."CategoryName",
"DB_Name"."My_Category"."My_Schema"."Categories"."CategoryID",
VALUEOF(NQ_SESSION."LANGUAGE")))
  9. In Logical Table Source, click OK.

The Categories_trans physical translation table doesn't need to be incorporated into the logical table source. The INDEXCOL function checks that if the LAN_INT session variable is 0, then the categoryname column is fetched from the base table. Note the following about the LOOKUP function:

  • The physical LOOKUP function works the same as a logical LOOKUP function. The only difference is that all the references to logical tables and columns are replaced by physical tables and columns.

  • The first column of the LOOKUP function is a value column, which is a translation value column from a translation table. The second column is the base value column, if a sparse lookup exists. The remaining columns are columns or values to be joined to the physical translation table, which is the table that's implied by the value column of the LOOKUP function.

Because you can't use a dialog box to configure a physical lookup table, you must ensure that the order of the join columns and values is compatible with the column sequence displayed in the Additional Keys section of the physical table's General tab for the physical translation table. For example, the Additional Keys section for the Categories_trans table indicates that the primary key is composed of the CategoryID and Language_Key columns.

The columns that are specified in the LOOKUP function correspond to these columns:

  • The following line:

    "DB_Name"."My_Category"."My_Schema"."Categories"."CategoryID"

    corresponds to the Categories_trans.CategoryID column.

  • The following line:

    valueof(NQ_SESSION."LANGUAGE")

    corresponds to the Categories_trans.Language_key column.

See Create Logical Lookup Columns for information about lookup concepts like the LAN_INT and LANGUAGE session variables and full syntax information for the LOOKUP function.