Use Case 4: Add a Custom Fact

You can add a custom fact containing aggregable metrics and join it to prebuilt dimensions with defined keys.

The prebuilt SCM – Sales Order subject area doesn’t contain the required Fact – My Custom fact metrics. In this use case, you add a folder Fact – My Custom to the SCM – Sales Orders prebuild subject area. This folder contains a sum aggregation metric labelled Custom Measure. The custom fact is joined to three prebuilt dimensions Dim – Inventory Organization, Dim – Inventory Item, and Dim – Date Gregorian Calendar using foreign keys defined in the custom fact.

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. Join Custom Fact to Prebuilt Dimensions
3. Present the Custom Fact in the Semantic Model
4. Apply and Publish the Customizations
5. Validate the Results

Use the following to complete this task:

• Existing dimensions:
  • Dim – Inventory Item
  • Dim – Inventory Organization
  • Dim - Date Gregorian Calendar

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.
• Create the sample dimension view titled FDI_X_CUSTOM_FACT_F_V using this SQL script:

  CREATE OR REPLACE VIEW FDI_X_CUSTOM_FACT_F_V AS 
  (
  SELECT ORDERED_DATE, ORGANIZATION_ID, INVENTORY_ITEM_ID, COUNT(*) AS CUSTOM_MEASURE 
  FROM DW_OM_FULFILLMENT_CF
  GROUP BY ORDERED_DATE, ORGANIZATION_ID, INVENTORY_ITEM_ID
  );
  

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

  GRANT SELECT ON "OAX_USER"."FDI_X_CUSTOM_FACT_F_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_CUSTOM_FACT_F_V;

Add Custom Fact to the Semantic Model

You create a logical star to define the autonomous data warehouse object, fact measure, display labels, keys, aggregation rules, and content levels.

In this step, you create a logical star to define the custom fact, Fact – My Custom, that references the autonomous data warehouse object FDI_X_CUSTOM_FACT_F_V.

1. On the Semantic Model Extensions page, click the MySandbox5Mar25 sandbox.
2. In Perform Action, click Manage Logical Star, then Create Logical Star, and then click Next.
   
   

   

   
   
3. On the Logical Star: Fact page, click Add Fact.
   
   

   

   
   
4. On the Add a Fact page, in step 1 of the wizard, select OAX_USER in Schema, select FDI_X_CUSTOM_FACT_F_V in Object, rename Fact Name as Fact – My Custom, select keys (most likely unselect fact for keys), and select attribute as fact as follows:
   • Select the Use for Key check box for ORGANIZATION_ID, ORDERED_DATE, INVENTORY_ITEM_ID under Source Column.
   • Select the Select Fact check box for CUSTOM_MEASURE under Source Column.

   
   

   

   
   
5. Click Next.
6. In step 2 of the wizard, set the Aggregation Rule for the Fact Column as Sum.
   
   

   

   
   
7. Click the Content Level icon.
8. In Content Level, click Add Dimension to add these dimensions, set the level as suggested in the table, click OK, and then click Finish:

   Dimension	Level
   Inventory Organization	Detail
   Inventory Item	Detail
   Gregorian Calendar	Day

   
   
   

Join Custom Fact to Prebuilt Dimensions

You add prebuilt dimensions and join them to the custom fact using foreign keys defined in the custom fact.

In this step, you add three prebuilt dimensions, Dim – Inventory Organization, Dim – Inventory Item, and Dim – Date Gregorian Calendar, and join them to the custom fact Fact – My Custom using foreign keys ORGANIZATION_ID, ORDER_DATE, INVENTORY_ITEM_ID defined in the custom fact.

1. On the Logical Star: Fact page, click Manage Dimension, and then click Add Existing Dimension.
   
   

   

   
   
2. In Add Table, search and add these three existing dimensions, and then click OK:
   • Dim – Inventory Item
   • Dim – Inventory Organization
   • Dim - Date Gregorian Calendar

   
   
   

3. Review the additions in the graphical mode.
   
   

   

   
   
4. Join each of the prebuilt dimensions (Dim – Inventory Organization, Dim – Inventory Item, and Dim – Date Gregorian Calendar) to the custom fact (Fact – Custom Fact) by dragging from the dimension circle to the fact circle to open the Join dialog. In Join, specify the Join Type as Inner, click Add Join Condition, add join conditions as follows, and click Done after adding all applicable join conditions:
   • For Fact – Custom Fact, select Organization Id (DOUBLE) and for Dim – Inventory Organization, select Inventory Organization Key (DOUBLE).

     
     
     

   • For Fact – Custom Fact, select Organization Id (DOUBLE) and for Dim – Inventory Item, select Organization ID (DOUBLE). For Inventory Item Id (DOUBLE) custom fact, select the Item ID (DOUBLE) dimension.

     
     
     

   • For Fact – Custom Fact, select Ordered Date (DATE) and for Dim – Date Gregorian Calendar, select Date (DATE).

     
     
     

5. Review the joins in the graphical mode.
   
   

   

   
   
6. Review the joins in the tabular mode.
   
   

   

   
   

Present the Custom Fact in the Semantic Model

You modify the subject area to present the new custom fact and its aggregable metrics. The system presents the custom fact as a folder containing measure columns in the subject area.

In this step, you modify the SCM – Sales Order subject area to present the new fact folder Fact – My Custom containing the Custom Measure measure column.

1. On the Semantic Model Extensions page, navigate to MySandbox5Mar25 sandbox, click Perform Action, click Manage Subject Areas, select Create a Subject Area, and then click Next.

   Note:

   You can either create a subject area or modify an existing subject area; this use case creates a subject area.

   
   
   

2. On the Create a Subject Area page, in step 1 of the wizard, select Create a subject area based on an existing one, select SCM – Sales Orders, enter My Custom Subject Area in New Subject Area Name, and then click Next.
   
   

   

   
   
3. In step 2 of the wizard, click Manage Elements, then click Manage New Customizations.
   
   

   

   
   
4. In Add a Subject Area, select Custom Measure from Fact – My Custom and click Add.
   
   

   

   
   
5. Unselect everything under Available Data Elements for SCM - Sales Order subject area.
   
   

   

   
   
6. Select the prebuilt dimensions that you joined to the custom fact in the logical star and click Next.
   
   

   

   
   
7. In step 3 of the wizard, review how it will appear in the model, rename Custom Measure as My Count, and click Next.
   
   

   

   
   
8. Review the custom subject area and click Next.
   
   

   

   
   
9. In step 4 of the wizard, 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 My Custom Subject Area, and then click Add to Workbook.
3. Build a report that pulls one column from each of the prebuilt dimensions and the custom measure from the custom fact, Fact - My Custom. Ensure that no duplication occurs as you add columns from each dimension columns. This report confirms that the selected prebuilt dimensions can be joined successfully to the custom fact measure.