1. Building Semantic Models in Oracle Analytics Cloud
  2. Create and Build Your Model
  3. Build a Semantic Model's Logical Layer
  4. Work with Logical Columns

Work with Logical Columns

This topic provides information about how to create and modify logical columns.

Topics:

About Logical Columns

Each logical table contains one or more logical columns. A logical column can be an attribute or a measure that is mapped or calculated.

In most cases you create logical columns by dragging tables from the physical layer to the logical layer. The logical columns you create in this way map to one or more physical columns and they inherit the physical column's data types.

You can also manually create logical columns that are derived from calculations based on other logical columns.

For example, you have a dimension table with two mapped attribute columns: a First Name column and a Last Name column. In the dimension table you can also have a calculated column named Full Name that is calculated by concatenating the Last Name column with the First Name column.

In the same example, you have a fact table with two mapped measure columns: a Revenue measure column with an aggregation of Sum, and a Billed Quantity measure column with an aggregation of Count. In the fact table you can also have a calculated measure column named Actual Unit Price that is calculated by dividing Revenue by Billed Quantity.

Add or Modify a Logical Column

You can add a new column to a logical table, or update an imported column's properties.

In most cases you create logical columns by dragging a physical table from the physical layer to the logical layer. This action creates a logical table with logical columns based on the physical table and its physical columns.
  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 Logical layer icon.
  4. In the Logical Layer pane, browse for and double-click the table where you want to add or modify a column.
  5. In the logical table, click the Columns tab.
  6. Click Add Column Add column icon and specify how you want to add the column:
    • Click Create New Column to create an empty column.
    • Click Add Physical Column and in Select Physical Column browse for and select one or more columns. Click Select.
  7. Optional: To duplicate a column, in the column table, hover over the column you want to duplicate and click its Row Menu and click Duplicate.
  8. In the column table, click a column to highlight it, and then click Detail view to view or modify its properties.
  9. Specify the column's properties.
  10. Click Save.

Delete a Logical Column's Logical Table Source

Adding a logical table source to a logical table automatically adds logical columns and maps them to physical columns. You can delete a column's logical table source.

  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 Logical layer icon.
  4. In the Logical Layer pane, browse for and double-click the table with the column you want to modify.
  5. In the logical table, click the Columns tab.
  6. In the columns list, click a column to select it and then click Detail view to open the properties pane.
  7. Scroll to the Sources section, click the logical table source that you want to remove, and click Delete.
  8. Click Save.

Base a Logical Column's Sort Order on a Different Column

Change the sort order of a logical column when you don't want to order a column's values alphabetically (lexical order).

In a lexical order sort, columns are ordered by their alphabetic spelling and not divided into a separate group. For example, if you sorted on month (using a column such as MONTH_NAME), the results return February, January, March in their lexicographical sort order.

Suppose you want to sort months in chronological order, so January, February, and March. Your table needs to have a month key such as MONTH_KEY with values of 1 (January), 2 (February), 3 (March) to achieve the chronological sort order. You set the Sort order column field for the MONTH_NAME column to the MONTH_KEY and then a request to order by MONTH_NAME would return January, February, and March.

  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 Logical layer icon.
  4. In the Logical Layer pane, browse for and double-click the table with the logical column you want to change the sort order for.
  5. In the logical table, click the Columns tab.
  6. In the column table, click the column to highlight it, and then click Detail view to view its properties.
  7. In the logical column's General properties, click Sort By and select the column that you want to sort by.
  8. Click Save.

Add Double Column Support

Double column support allows you to associate two columns. One column provides the display and description values such as the description of an item. The second column provides a descriptor ID or code column.

For example, you can use the actual column to provide the project list, and hide the ID column associated with the first column, as in Clinic and Clinic ID. Only the Clinic description is displayed to the user.

Using double columns can help improve performance because filtering is done on the ID column, which is numeric and indexed.

When multilingual columns are based on a lookup function, you can specify the non-translated lookup key column as the descriptor column of the translated column. You can use double column support to defining language-independent filters. For example, in analyses users see the display column, but the query filters on the hidden descriptor ID column.

See Support Multilingual Data.

  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 Logical layer icon.
  4. In the Logical Layer pane, browse for the table with the logical column you want to use as a double column.
  5. In the logical table, click the Columns tab.
  6. In the column table, click the column to highlight it, and then click Detail view to view its properties.
  7. In the logical column's General properties, click the Descriptor Column field and select the column you want to use.
  8. Click Save.

Create Derived Columns

Columns can be derived from other logical columns as a way to apply post-aggregation calculations to measures. You use the Expression Editor to specify the derived column expression.

You can use a derived column to create a lookup function to display data from multilingual database schemas. See Create Logical Lookup Columns.

The Oracle Analytics query engine prevents errors in divide-by-zero situations. The Oracle Analytics query engine creates a divide-by-zero prevention expression using nullif() or a similar function when it writes the physical SQL. Because of this, you don't have to use CASE statements to avoid divide-by-zero errors.

To optimize performance and avoid errors on the aggregation level, don't define aggregations in Expression Editor. Instead, set the logical column's Aggregation Rule field. See Set Aggregation Rules for a Measure Column.

  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 Logical layer icon.
  4. In the Logical Layer pane, browse for the table with the logical column you want to add the derived column expression to.
  5. In the logical table, click the Columns tab.
  6. In the column table, click the column to highlight it, and then click Detail view to view its properties.
  7. In the logical column's Sources properties, pane, click Logical Expression, and then click Open Expression Editor and create and validate the expression.
  8. In the Expression Editor, click Save.

Configure Logical Columns for Multicurrency Support

You can configure logical columns to allow users to select the currency that they want to display their visualizations, analyses, and dashboards currencies columns in.

You can set up this feature so that all users see the same static list of currency options, or you can provide a dynamic list of currency options that changes based on a Logical SQL statement you specify.

When you use session variables in an expression, you must use this format: VALUEOF(NQ_SESSION.var_name). Edit any logical columns that display currency values to use the appropriate conversion factor using the PREFERRED_CURRENCY session variable.

See Create an Initialization Block and Create a Session Variable.

The following logical column expression uses the value of the NQ_SESSION.PREFERRED_CURRENCY variable to switch between different currency columns. The currency columns are expected to have the appropriate converted values. 

INDEXCOL( CASE VALUEOF(NQ_SESSION.PREFERRED_CURRENCY) WHEN 'gc1' THEN 0
WHEN 'gc2' THEN 1 WHEN 'orgc' THEN 2 WHEN 'lc1' THEN 3 ELSE 4 END,
"Paint"."Sales Facts"."USDCurrency",
"Paint"."Sales Facts"."DEMCurrency" ,
"Paint"."Sales Facts"."EuroCurrency" ,
"Paint"."Sales Facts"."JapCurrency" ,
"Paint"."Sales Facts"."USDCurrency" )
An Administrator must set up Oracle Analytics Currencies XML system setting to enable multicurrency support. For information about this configuration, see About Advanced Configurations and Set Advanced Options.
  1. Click Variables.
  2. Click Create, click Create Initialization Block, and create the session variable's initialization block.
  3. Create a session variable and name it PREFERRED_CURRENCY. Make sure to select the Enable any user to set the value field for the session variable.
  4. Click Save.
  5. Click Logical Layer Logical layer icon.
  6. In the Logical Layer pane, browse for the table with the logical column you want to configure for multicurrency.
  7. In the logical table, click the Columns tab.
  8. In the column table, click the column to highlight it, and then click Detail view to view its properties.
  9. In the logical column's Sources properties, click the Logical Expression field and the click Open Expression Editor.
  10. In the Expression Editor, create and validate a derived expression that uses the PREFERRED_CURRENCY variable.
  11. Click Save to save the expression.
  12. Optional: To provide a dynamic list of currency options, create a table in your data source that provides the entries you want to display for the user-preferred currency. This table must include the following columns:

    • The first column contains the values used to set the session variable PREFERRED_CURRENCY. Each value in this column is a string that uniquely identifies the currency (for example, gc2).

    • The second column contains currency tags from the Currencies XML system setting. The displayMessage values for each tag are used to populate the Currency box and currency prompts, for example, int:euro-1.

    • You can provide a third column that contains the values used to set the presentation variable currency.userPreference. Each value in this column is a string that identifies the currency, for example, Global Currency 2. If you omit this column, then the values for the displayMessage attributes for the corresponding currency tags located in the Currencies XML system setting are used.

    Sample user-preferred currency entries:

    • UserPreference: orgc1, CurrencyTag: loc:en-BZ, UserPreferenceName: Org currency
    • UserPreference: gc2, CurrencyTag: int:euro-1, UserPreferenceName: Global currency 2
    • UserPreference: lc1, CurrencyTag: int:DEM, UserPreferenceName: Ledger currency
    • UserPreference: gc1, CurrencyTag: int:USD, UserPreferenceName: Global Currency 1