Add Derived Columns

Add a derived column to an existing subject area.

  1. Sign in to your service.
  2. In Oracle Fusion Data Intelligence Console, click Semantic Model Extensions under Application Administration.
  3. On the Semantic Model Extensions page, click User Extensions.
    You see the main and existing customization branches.
  4. In the User Extensions region, under Customization Branches, click a branch to open the Branch page.
  5. On the Branch page, click Add Step.
  6. In Add Step, select Add a Column.
    You see the wizard sequence to add a column.
  7. In step 1 of the wizard, enter a name for your customization step, for example, Regional Revenue and add a brief description.
  8. Select a target subject area to which you want to add the column. For example, Profitability.
    You see the details of the selected subject area.
  9. Select the presentation folder within the selected subject area and the logical table to which you want to add the column.
  10. Click Next.
    You see the Create Column dialog in step 2 of the wizard.
  11. In step 2 of the wizard, define your new column using these instructions:
    1. In Create Column, enter a display name.
    2. Under Data Elements, search for a data element from the subject area that you had selected previously.
    3. From the search results, double-click the data element to place it in the text pane.
    4. Under Functions, search for a function to construct a column using expressions. From the search results, double-click the applicable result to add it to the central text pane. For example, search for functions like "Filter" or "Avg" to construct expression-based columns. A sample expressions to derive the average supplier payment days is avg(ROUND(((CASE WHEN Invoice Received Date is not null THEN (Financials - AP Payments.Payment Date.Payment Date - Invoice Received Date) ELSE (Financials - AP Payments.Payment Date.Payment Date - Financials - AP Invoices.Invoice Date.Invoiced Date) END)/Financials - AP Payments.Facts - Analytics Currency.Total Payment Count),0)).
    5. Click Validate, and then click Save.
  12. Optional: If you want the underlying measure of the column to be calculated to a specific level of a predefined dimensional hierarchy, then complete these steps:
    1. Click the Hierarchy Level-Based Aggregation icon.
    2. In the Hierarchy Level-Based Aggregation dialog, select the dimension, level, and then click OK.
    3. Click Add Dimension to add more dimensions.
  13. Click Next.
  14. Optional: Select additional subject areas to add the fact.
  15. Click Finish.
    You see a message that your step is being applied to the customization branch. After it's applied, you see the new customization step in the customization branch. You can now apply the customization branch to the main branch or edit it to add more steps.