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 your home page, click Navigator Navigator icon 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.