Use Case 2: Extend a Degenerate Dimension

You can extend a transaction details dimension folder by adding new columns that represent additional descriptive attributes related to the degenerate (degen) dimension.

The Sales Order Details folder (a sub-folder in the Fulfilment Line Details folder) available in the prebuilt SCM – Sales Order subject area doesn’t contain the required descriptive attribute Fulfil Partner.

In this use case, you add the Fulfil Partner column from an external source into the prebuilt Sales Order Details folder to associate the details of the partner who is fulfilling the applicable sales order. The custom extension is joined to the prebuilt Dim – OM Sales Order Details degen dimension using the Fulfill Line ID column.

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 Transaction Details Extension to the Semantic Model
  2. Present the Transaction Details Extension in the Semantic Model
  3. Apply and Publish the Customizations
  4. Validate the Results
Use the following to complete this task:
  • Attribute - Fulfil Partner column
  • Subject area - SCM - Sales Orders
  • Parent Dimension Folder - Sales Orders
  • Dimension folder - Dim - OM Sales Orders Details
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 Transaction Details Extension view titled FDI_X_OM_FULFILLMENT_CF_V using this SQL script:
    CREATE OR REPLACE VIEW FDI_X_OM_FULFILLMENT_CF_V
AS
(
SELECT FULFILL_LINE_ID,
CASE ORDER_LINE_NUMBER
WHEN 1 THEN 'Sysco Corporation'
WHEN 2 THEN 'US Foods'
WHEN 3 THEN 'Performance Food Group (PFG)'
WHEN 4 THEN 'McLane Company'
WHEN 5 THEN 'Gordon Food Service (GFS)'
WHEN 6 THEN 'The H.T.'
WHEN 7 THEN 'KeHE Distributors.'
WHEN 8 THEN 'Reyes Holdings'
WHEN 9 THEN 'Ben E. Keith Foods'
WHEN 10 THEN 'Shamrock Foods'
WHEN 11 THEN 'Maines Paper and Food Service'
WHEN 12 THEN 'SpartanNash'
WHEN 13 THEN 'UNFI'
WHEN 14 THEN 'C and S Wholesale Grocers'
WHEN 15 THEN 'Core-Mark Holding Company'
WHEN 16 THEN 'Cheney Brothers'
ELSE 'Unknown'
END AS FULFIL_PARTNER
FROM OAX_USER.DW_OM_FULFILLMENT_CF
);

    This view is based on the OAX_USER.DW_OM_FULFILLMENT_CF fact table. However, you can switch it to any other fact table.

  3. Grant semantic model access to the FDI_X_OM_FULFILLMENT_CF_V view for the OAX$OAC schema using the following SQL script:
    GRANT SELECT ON OAX_USER. FDI_X_OM_FULFILLMENT_CF_V TO "OAX$OAC";
  4. Validate that the data is loading from the sample as expected using the following SQL script:
    SELECT * FROM FDI_X_OM_FULFILLMENT_CF_V;

Add a Transaction Details Extension to the Semantic Model

You edit the logical star to define the autonomous data warehouse object, attributes, display labels, and keys, and then join the custom object to the prebuilt degen dimension being extended.

In this step, you edit Fact – OM Sales Orders logical star to add a dimension extension on Dim – OM Sales Order Details referencing the autonomous data warehouse object FDI_X_OM_FULFILLMENT_CF_V. The custom extension is joined to the prebuilt Dim – OM Sales Order Details degen dimension using Fulfill Line ID.
  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, locate the Dim - OM Sales Orders Details dimension table, right click on it, and then click Manage Extension.

    The Logical Star: Fact page displaying the Dim - OM Sales Orders Details dimension table

  4. In the Manage Extension list of values, select Extend Dim.
  5. On the Extend a Dimension page, in step 1 of the wizard, select OAX_USER in Schema and select FDI_X_OM_FULFILLMENT_CF_V in Object. Select the Use for Key check box for the FULFIL_LINE_ID source column and select the Add Attributes check box for the FULFIL_PARTNER source column. Click Next.

    The Extend a Dimension page

  6. In step 2 of the wizard, select Inner in Select Join Type. Select Order Fulfillment Line Identifier (DOUBLE) as Dimension Key and FULFILL_LINE_ID (DOUBLE) as Extended Dimension Key to join on the Line Identifier and ID as defined in the FDI_X_OM_FULFILLMENT_CF_V view. Click Finish.

    The step 2 of the wizard on the Extend a Dimension page displaying join type and keys to join on

  7. Review the extended dimension.

    The Sandbox: MySandbox5Mar2025 page displaying the extended dimension

Present the Transaction Details Extension in the Semantic Model

You modify the subject area to present the extended columns in a subject area folder.

In this step, you modify the SCM – Sales Order subject area to present the extended Fulfil Partner column in the prebuilt Sales Order Details folder.
  1. Navigate to the MySandbox5Mar25 sandbox.
  2. In the MySandbox5Mar2025 sandbox, click Perform Action, then Manage Subject Areas, then Modify a Subject Area, select SCM - Sales Orders as the subject area, and then click Next.
  3. In step 1 of the wizard on the Modify a Subject Area – SCM – Sales Orders page, view the details and click Next.
  4. In step 2 of the wizard, select Manage Elements, and then click Manage Factory Customizations.

    The step 2 of the wizard on the Modify a Subject Area – SCM – Sales Orders page displaying the Manage Elements list of values

  5. In Add a Subject Area, select Dim – OM Sales Orders Details dimension and Fulfil Partner attribute to extend these to the transaction details and click Add Folders.

    The Add a Subject Area dialog displaying the Dim – OM Sales Orders Details dimension and Fulfil Partner attribute

  6. In Add a Subject Area, select Sales Orders Details folder and click Add Folders.

    The Add a Subject Area dialog displaying the selected Sales Orders Details folder

  7. Review the additions and click Finish.

    The Modify a Subject Area – SCM – Sales Orders page displaying the additions

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.

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.

    The Add Data displaying the selected SCM – Sales Orders subject area

  3. Expand the Fulfillment Line Details folder, then expand the Sales Order Details folder, and verify that the new field Fulfil Partner is available to use and data remains accurate.

    The expanded Fulfillment Line Details folder displaying the newly added Fulfil Partner field

  4. Build a workbook that contains the new extended attribute Fulfil Partner as well as measure from the Sales Order Details folder to validate that the join between the extension and fact table is returning the expected results.

    The workbook containing the new extended attribute Fulfil Partner and measure from the Sales Order Details folder