Use Case 7: Create Custom Dim with Hierarchy

You can create a dimension with a hierarchy by defining a parent-child level-based hierarchical structure.

The prebuilt SCM – Sales Order subject area doesn’t contain the required Dim – Party Hierarchy Dim dimension attributes and hierarchical levels. In this use case, you add a folder Dim - Party Hierarchy Dim to the SCM – Sales Orders prebuilt subject area. This folder contains the Accounts Level Hierarchy hierarchy with four levels, plus a detailed lowest level. The custom dimension with hierarchy is joined to the prebuilt Fact – OM Sales Orders fact using the Party ID key.

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 Custom Fact to the Semantic Model
2. Add Physical Column to a Prebuilt Fact to Expose Foreign Key to Join On
3. Join the Custom Dimension Hierarchy to a Prebuilt Fact
4. Present the Custom Dimension Hierarchy in the Semantic Model
5. Apply and Publish the Customizations
6. Validate the Results

Use the following to complete this task:

• Prebuilt Subject area: SCM – Sales Orders

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.
• Generate the sample dimension view FDI_X_PARTY_HIERARCHY_D_V using this SQL script:

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "OAX_USER"."FDI_X_PARTY_HIERARCHY_D_V" ("PARTY_ID", "LVL1", "LVL2", "LVL3", "LVL4", "LVL_DETAIL") DEFAULT COLLATION "USING_NLS_COMP"  AS 
    (
  SELECT DISTINCT
  PARTY_ID,
  NVL(COUNTRY,'~No Value~') as LVL1,
  NVL(STATE,'~No Value~') as LVL2,
  NVL(CITY,'~No Value~') as LVL3,
  NVL(POSTAL_CODE,'~No Value~') as LVL4,
  NVL(PARTY_NAME,'~No Value~') as LVL_DETAIL
  FROM
  OAX$OAC.DW_PARTY_D
  where (CUSTOMER_FLAG <> 'N' or CUSTOMER_FLAG is null)
  );

• Grant semantic model access to the FDI_X_PARTY_HIERARCHY_D_V view for the OAX$OAC schema using the following SQL script:

  GRANT SELECT ON "OAX_USER"."FDI_X_PARTY_HIERARCHY_D_V" TO "OAX$OAC";

• Validate that the data is loading from the sample as expected using the following SQL script:

  SELECT * FROM OAX_USER.FDI_X_PARTY_HIERARCHY_D_V;

• Confirm that the foreign key is unique to avoid inaccurate results caused from possible duplication due to incorrect granularity by entering this SQL script under SQL Worksheet on the right pane in SQL Developer Client:

  SELECT PARTY_ID, COUNT(*)
  FROM FDI_X_PARTY_HIERARCHY_D_V
  GROUP BY PARTY_ID
  HAVING COUNT(*) > 1;

Add Custom Fact to the Semantic Model

You edit the logical star to define the autonomous data warehouse objects, attributes, display labels, keys, and hierarchy.

In this step, you edit the Fact – OM Sales Orders logical star to add a custom dimension Dim – Party Hierarchy Dim referencing the autonomous data warehouse object FDI_X_PARTY_HIERARCHY_D_V and define a multi-level hierarchy named Accounts Level Hierarchy. For each level, the use case defines a unique primary key and display attribute.

1. On the Semantic Model Extensions page, click the MySandbox5Mar25 sandbox.
2. In Perform Action, click Manage Logical Star, then 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, click Manage Dimension, and then click Add Dimension.
   
   

   

   
   
4. On the Add a Dimension page, in step 1 of the wizard, select OAX_USER in Schema, select FDI_X_PARTY_HIERARCHY_D_V in Object, and rename Dimension Name as Party Hierarchy Dim. Select the Use for Key check box for PARTY_ID source column, select the Add Attributes check box for LVL2, LVL_DETAIL, LVL3, LVL4, LVL1, and PARTY_ID source columns, and click Next.
   
   

   

   
   
5. Select the Add hierarchy to Subject Area check box, enter Accounts Level Hierarchy in Name, and drag each attribute to its corresponding level.
   
   

   

   
   
6. For each level, click on the level, click the pencil icon and then set the primary key and display attribute.

   Note:

   You may prefer to use an Internal ID / integer key for each level, if available. For the Detail Level, this use case use Party ID for the key.

   
   
   

7. Click Finish.

Add Physical Column to a Prebuilt Fact to Expose Foreign Key to Join On

You extend the prebuilt fact to expose physical columns that will be used as keys to join the new custom dimension in the logical star. You must give the logical columns unique names.

In this step, you extend Fact – OM Sales Orders to expose physical column BILL_TO_PARTY_ID to use as a key to join to the new custom dimension Dim – Party Hierarchy Dim. BILL_TO_PARTY is given a unique name Party ID.

1. On the Semantic Model Extensions page, click the MySandbox5Mar25 sandbox.
2. In Perform Action, click Manage Logical Star, then 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, right click Fact – OM Sales Orders, and then click Manage Extension.
   
   

   

   
   
4. On the Add Columns page, click Add Physical Column, and in Select Physical Column, select BILL_TO_PARTY_ID from Fact_DW_OM_FULFILLMENT_CF and then click OK.
   
   

   

   
   
5. Under the Logical Column, enter Party ID as the unique name and click Finish.
   
   

   

   
   

Join the Custom Dimension Hierarchy to a Prebuilt Fact

You join the custom dimension hierarchy to the prebuilt fact by defining the join type, join condition, and context level. You can define complex joins; however, it's advised to try and use standard joins where possible.

In this step, you define a standard Inner join on the prebuilt Fact – OM Sales Orders fact to the custom dimension hierarchy Dim – Party Hierarchy Dim using the Party ID key from each table. You set the content level to Detail.

1. On the Semantic Model Extensions page, click the MySandbox5Mar25 sandbox.
2. In Perform Action, click Manage Logical Star, then 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, drag from the custom dimension hierarchy to the prebuilt fact.
   
   

   

   
   
4. In Join dialog, join on Party ID (New physical column added) and Party ID (from Custom Dim Hierarchy). This example uses an Inner Join. Others may require a Left (or other Join type). Set the Context Level as Detail and click Done.
   
   

   

   
   
5. Review the join in graphical mode.
   
   

   

   
   
6. Click the back arrow on the Logical Star: Fact page to navigate back to the Sandbox: MySandbox5Mar2025 page.

Present the Custom Dimension Hierarchy in the Semantic Model

You modify the subject area to present the new custom dimension attributes and hierarchy levels.

The system represents the dimension as a folder containing columns in the subject area and represents the hierarchy levels at the bottom of the custom dimension folder. Hierarchy levels used in Workbooks can expand and collapse to reveal or hide detailed rows.

In this step, you modify the SCM – Sales Order subject area to present the new custom Dim – Party Hierarchy Dim folder containing the custom hierarchy Accounts Level Hierarchy with four levels (Level 1, Level 2, Level 3, Level 4) and the lowest Detailed level.

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, click Manage New Customizations, and in Add a Subject Area, select the hierarchy and dimensions elements, and then click Add.
   
   

   

   
   
4. 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 the new dimension with the hierarchy appears at the bottom of the folder by default.
   
   

   

   
   
4. Add level 1 and a measure to the visualization.
   
   

   

   
   
5. Expand and collapse the levels and confirm they work as expected.
   
   

   

   
   
6. Verify that the dimension fields are also available to use (without expand or collapse).
   
   

   

   
   
7. View the report.