Use Case 3: Add Custom Dimension to a Prebuilt Fact

You can add a custom dimension folder containing descriptive attributes and join the custom dimension to a prebuilt fact table with defined keys.

The prebuilt SCM – Sales Order subject area doesn’t contain the required Custom Item Department dimension attribute. In this use case, you add a folder Custom Item Department to the SCM – Sales Orders prebuilt subject area. This folder contains the Department attribute. The custom dimension is joined to the prebuilt Fact – OM Sales Orders fact using a composite key of Organization ID and Inventory Item ID.

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

Use the following to complete this task:

• Attribute - Department
• Subject area - SCM - Sales Orders
• Fact folder - Dim - OM Sales Orders

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

1. 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.
2. Generate the sample dimension view FDI_X_ITEM_CUSTOM_DEPARTMENT_D_V using this SQL script:

   CREATE OR REPLACE FORCE EDITIONABLE VIEW "OAX_USER"."FDI_X_ITEM_CUSTOM_DEPARTMENT_D_V" ("ORG_ITEM_KEY", "ORGANIZATION_ID", "INVENTORY_ITEM_ID", "DEPARTMENT") DEFAULT COLLATION "USING_NLS_COMP"  AS 
     (
   SELECT ORGANIZATION_ID || INVENTORY_ITEM_ID AS ORG_ITEM_KEY,
   ORGANIZATION_ID, INVENTORY_ITEM_ID,
   CASE SUBSTR(INVENTORY_ITEM_ID,-1,1)
   WHEN '1' THEN 'Clothing'
   WHEN '2' THEN 'Shoes'
   WHEN '3' THEN 'Cosmetics'
   WHEN '4' THEN 'Furniture'
   WHEN '5' THEN 'Gardening'
   WHEN '6' THEN 'Hardware'
   WHEN '7' THEN 'Home Appliances'
   WHEN '8' THEN 'Houseware'
   WHEN '9' THEN 'Paint'
   WHEN '0' THEN 'Sporting Goods'
   ELSE 'Other'
   END DEPARTMENT
   FROM OAX$OAC.DW_INVENTORY_ITEM_D
   );

3. Grant semantic model access to the FDI_X_ITEM_CUSTOM_DEPARTMENT_D_V view for the OAX$OAC schema using the following SQL script:

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

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

   SELECT * FROM OAX_USER.FDI_X_ITEM_CUSTOM_DEPARTMENT_D_V;

Add a Custom Dimension to the Semantic Model

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

In this step, you edit the Fact – OM Sales Orders logical star to add a custom dimension Custom Item Department referencing the autonomous data warehouse object FDI_X_ITEM_CUSTOM_DEPARTMENT_D_V.

1. On the Semantic Model Extensions page, click the MySandbox5Mar25 sandbox.
2. In Perform Action, click Manage Logical Star, then Edit Logical Star, then Out of the box, then select SCM - Sales Orders as the subject area, select Fact - OM Sales Orders as the corresponding fact, and then click Next.
3. On the Logical Star: Fact page, click Manage Dimension, and then click Add Dimension.
   
   

   

   
   
4. In step 1 of the Add a Dimension wizard, select OAX_USER in Schema, select FDI_X_ITEM_CUSTOM_DEPARTMENT_D_V in Object, and enter Custom Item Department in Dimension Name. Select the Add Attributes check box for DEPARTMENT and ORG_ITEM_KEY. Select the Use for Key check box for INVENTORY_ITEM_ID, ORGANIZATION_ID, and ORG_ITEM_KEY. Click Next.
   
   

   

   
   
5. In step 2 of the wizard, leave the Add hierarchy to Subject Area check box unselected to omit a hierarchy. However, it's a requirement to name the hierarchy and map a primary key and display attribute in the logical star, although the hierarchy won't appear in the subject area. Complete this step as follows:
   • Enter Custom Item Department Hierarchy in Hierarchy Name.
   • In Properties, define the primary key and display attribute as follows:
     • Click the pencil icon for Primary Key and select Org Item Key.
     • Click the pencil icon for Display Attribute and select Org Item Key.
   • Under Selected Data Elements, from the Available Data Elements, drag and add Department and Org Item Key to the Detail level.

     Note:

     You can add the dimension display attributes to either a level or the Detail level.

   
   

   

   
   
6. Click Finish. Continue to the next task without exiting the wizard, Add Physical Column to a Prebuilt Fact.

Add Physical Column to a Prebuilt Fact

You extend the prebuilt fact to expose physical columns that you use as keys to join the new custom dimension in the logical star.

In this step, you extend Fact – OM Sales Orders to expose physical columns ORGANIZATION_ID and INVENTORY_ITEM_ID to use as keys to join to the new custom dimension Custom Item Department. You must provide unique names for the logical columns; for example, ORGANIZATION_ID is given a unique name UC Organization ID and INVENTORY_ITEM_ID is given a unique name UC Inventory Item ID.

1. On the Logical Star: Fact page, right click Fact – OM Sales Orders, and then click Manage Extension.
   
   

   

   
   
2. On the Sandbox: MySandbox5Mar25 page, click Manage Extension and then click Add Columns.
   
   

   

   
   
3. On the Add Columns page, click Add Physical Column.
   
   

   

   
   
4. In Select Physical Columns, select the check box for ORGANIZATION_ID and INVENTORY_ITEM_ID, and then click OK.
   
   

   

   
   

   Note:

   These columns come from the Fact_DW_OM_FULFILLMENT_CF table.
5. On the Add Columns page, in Logical Column, enter unique logical column names such as UC Organization ID and UC Inventory Item ID, and then click Finish.

   Note:

   It is very important that the Logical Column Name is unique and doesn’t conflict with the prebuilt column name.

   
   

   

   
   
6. Continue to the next task without exiting the wizard, Join the Custom Dimension to the Prebuilt Fact.

Join the Custom Dimension to the Prebuilt Fact

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

In this step, you define a standard left outer join on the prebuilt Fact – OM Sales Orders fact to the custom dimension Dim – Custom Item Department using a composite key comprising of UC Organization ID and UC Inventory Item ID from each table. The Content level is set to Detail.

1. On the Logical Star: Fact page, click and drag the custom dimension Dim – Custom Item Department circle to the prebuilt fact Fact – OM Sales Orders circle in the graphical format to open the Join dialog.
   
   

   

   
   
2. In the Join dialog, set Join Type as Left Outer, set Content level to Detail, set Join Condition as follows and then click Done:
   • For Fact – OM Sales Orders, select UC Organization ID (DOUBLE) and for Dim – Custom Item Department, select Organization Id (DOUBLE).
   • For Fact – OM Sales Orders, select UC Inventory Item ID (DOUBLE) and for Dim – Custom Item Department, select Inventory Item Id (DOUBLE).

   
   

   

   
   
3. Review the custom joins in the tabular format.
   
   

   

   
   
4. Review the custom joins in the graphical format.
   
   

   

   
   

Present the Custom Dimension in the Semantic Model

You modify the subject area to present the new custom dimension and its attributes. The dimension is represented as a folder containing columns in the subject area.

In this step, you modify the SCM – Sales Order subject area to present the new custom dimension folder Custom Item Department containing the Department attribute column.

1. Navigate back to the MySandbox5Mar25 sandbox, click Perform Action, then Manage Subject Areas, and then click Modify a Subject Area. Select SCM – Sales Orders and click Next.
2. On the Modify a Subject Area - SCM - Sales Orders page, review the subject area details in step 1 of the wizard and click Next.
3. In step 2 of the wizard, click Manage Elements and then click Manage New Customizations.
   
   

   

   
   
4. In Add Subject Area, under Custom, select the Department attribute column, and then click Add.

   This adds the Dim - Custom Item Department folder to the semantic model and places the Department column within the folder.

   
   

   

   
   
5. Review the selected data 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, the subject area that is extended in this use case, and then click Add to Workbook.
3. Expand the Custom Item Department folder and verify that the Department attribute column appears towards the bottom of the subject area by default. Drag the Department column onto the workbook.
   
   

   

   
   
4. Drag a measure such as Lines Count from the Facts - Sales Orders folder to confirm that the fact can join to the custom dimension.
   
   

   

   
   
5. Verify the results.