Use Case 6: Add Derived Columns

You can add derived columns to a folder that uses a function or formula referencing custom or prebuilt elements that enables you to perform calculations and display derived values.

The Dim – Customer Sold To folder in the prebuilt subject area SCM – Sales Order doesn’t contain the required derived column Customer Index. In this use case, you add the Custom Index derived column, calculated using a CASE statement function, into the prebuilt Dim – Custom Sold To folder to associate the Customer Index with the Sold To Customer attributes. Since the derived column is created within the Dim – Customer Sold To dimension, it is automatically joined.

This use case requires Fusion SCM Analytics subscription. However, you can apply the concepts covered in this use case to any other Fusion Data Intelligence subscription. The use case may reference Autonomous Data Warehouse prebuilt tables and synonyms and mock custom database tables and views. If you’ve activated the specified Fusion Data Intelligence subscription, you may use the samples provided. The recommendation is to substitute the sample objects for your own custom Autonomous Data Warehouse objects such as custom table, materialized view, view, custom synonym, or data augmentation dataset synonym.

In this task, you:

1. Add a Derived Column to the Semantic Model
2. Present the Derived Column in the Semantic Model
3. Apply and Publish the Customizations
4. Validate the Results

Use the following to complete this task:

• Dim – Customer Sold To folder
• SCM – Sales Order prebuilt subject area

Ensure that you refer to Upload Samples for the Semantic Model Extensions Use Cases and complete these prerequisites:

• Create a sandbox titled MySandbox5Mar25. See Create Sandbox. Or, edit an existing sandbox on the Semantic Model Extensions page; for example, by clicking the MySandbox5Mar25 sandbox.

Add a Derived Column to the Semantic Model

You edit the logical star to define the custom derived column expression formula. You can use the Fx Functions for syntax assistance, or copy the formula and paste it or manually type.

In this step, you edit the Fact – OM Sales Orders logical star to extend the prebuilt Dim – Custom Sold To dimension, creating a new derived column Custom Index. At runtime, Custom Index executes a CASE statement function evaluating the Sold-to-Customer column return a custom value based on defined if-then-else conditions.

1. On the Semantic Model Extensions page, click the MySandbox5Mar25 sandbox.
2. In Perform Action, click Manage Logical Star, click Edit Logical Star, select Out of the box, select SCM – Sales Orders subject area, select Fact – OM Sales Orders, and then click Next.
3. On the Logical Star: Fact page, locate the Dim – Customer Sold To dimension to add the derived column to. Right click on the dimension, and then click Manage Extension.
   
   

   

   
   
4. On the Sandbox: MySandbox5Mar25 page, click Manage Extension and then click Add Columns.
5. On the Add Columns page, click Add Derived Column.
   
   

   

   
   
6. In Create Column, enter Customer Index in Name, and the following script in the space under the Name field:

   CASE
   WHEN UPPER(SUBSTRING("Core"."Dim - Customer Sold To"."Sold-to Customer" FROM 1 FOR 1)) BETWEEN  'A' AND 'H' THEN 'A-H'
   WHEN UPPER(SUBSTRING("Core"."Dim - Customer Sold To"."Sold-to Customer" FROM 1 FOR 1)) BETWEEN  'I' AND 'P' THEN 'I-P'
   WHEN UPPER(SUBSTRING("Core"."Dim - Customer Sold To"."Sold-to Customer" FROM 1 FOR 1)) BETWEEN  'Q' AND 'Z' THEN 'Q-Z'
   ELSE 'Other' 
   END

   
   
   

7. In Create Column, after providing the applicable script, click Validate, then Save, and then Finish on the Add Columns page.
   
   

   

   
   

Present the Derived Column in the Semantic Model

You modify the subject area to present the new custom derived column and select the folder to display it in.

The system automatically joins the new column to the dimension it's added to. In this step, you modify the SCM – Sales Order subject area to present the new derived column Custom Index, adding it to the pre-built Dim – Custom Sold To folder.

1. Navigate to the MySandbox5Mar25 page, click Perform Action, click Manage Subject Areas, select Modify a Subject Area, select SCM Sales Orders subject area, and then click Next.
2. On the Modify a Subject Area page, in step 1 of the wizard, view the details and click Next.
3. In step 2 of the wizard, click Manage Elements, and then click Manage Factory Customizations.
4. In Add a Subject Area, select Customer Index, and then click Add Folders.
   
   

   

   
   
5. In Add a Subject Area, select the Sold-to Customer folder and click Add Folders.
   
   

   

   
   
6. In step 2 of the wizard, review the selected elements and click Finish.
   
   

   

   
   

Apply and Publish the Customizations

You apply the changes to compile the sandbox and ensure that the sandbox is error free.

Then, you use the Activity tab to debug, resolve errors, and confirm that the Apply Changes action completes and is successful. Finally, you merge the changes to the main sandbox and publish the main user extensions to share the new extensions with consumers.

In this step, you apply the changes, use the Activity tab to monitor the status, merge the MySandbox5Mar25 sandbox to the Main sandbox, and then publish the main user extension.

1. On the Semantic Model Extensions page, hover over the MySandbox5Mar25 sandbox to view Actions, and then click Apply Changes.
2. Click the Activity tab to monitor the Apply Changes task on the Activity page.
3. After the changes are successfully applied, click Publish Model on the Semantic Model Extensions page.
4. In Publish Model, in User Extensions, select MySandbox5Mar25 and in Security Configurations, select All.
5. Click Publish.
6. On the Semantic Model Extensions page, click the Activity tab to monitor the Publish Customizations task.
   See Apply Changes and Publish Model.

Validate the Results

Verify that your customizations are visible in the semantic model.

1. On the Fusion Data Intelligence Console, click Go to Home Page.
2. On the Oracle Analytics Home page, click Create, click Workbook, in Add Data, select SCM – Sales Orders, and then click Add to Workbook.
3. Verify that that new column Customer Index is visible at the end of the Sold-to Customer folder.
   
   

   

   
   
4. Build a report displaying the Customer Index and Sold-to Customer columns.