Use Case 5: Add a Degen Custom Fact

You can add a degenerate (degen) custom fact containing both aggregable metrics and descriptive attributes and join the degen custom fact to a prebuilt dimension with a defined key.

The prebuilt SCM – Sales Order subject area doesn’t contain the required Fact – Holiday Calendar metrics and Dim – Fact – Holiday Calendar – degendim attributes. In this use case, you add the Fact – Holiday Calendar and Dim – Fact – Holiday Calendar – degendim folders to the SCM – Sales Orders prebuilt subject area. The Fact – Holiday Calendar folder contains aggregable metrics. The Dim – Fact – Holiday Calendar – degendim folder contains descriptive attributes. The degen custom fact is joined to the prebuilt Dim – Data Gregorian Calendar using the Date 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 Fact with Degen Attributes to the Semantic Model
  2. Join Custom Fact with Degen Attributes to a Prebuilt Dimension
  3. Present the Custom Fact with the Degen Attributes in the Semantic Model
  4. Apply and Publish the Customizations
  5. Validate the Results
Use the following to complete this task:
  • SCM – Sales Orders 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.
  • Create the holiday dimension table, FDI_X_HOLIDAY_CALENDAR_D, in the autonomous data warehouse associated with your Fusion Data Intelligence instance using the sample files FDI_X_HOLIDAY_CALENDAR_D.xls or FDI_X_HOLIDAY_CALENDAR_D.sql.
  • Create the sample dimension view FDI_X_HOLIDAY_CALENDAR_F_V using this SQL script:
    CREATE OR REPLACE FORCE EDITIONABLE VIEW "OAX_USER"."FDI_X_HOLIDAY_CALENDAR_F_V" ("CALENDAR_DATE", "HOLIDAY", "BUSINESS_DAY", "TODATE_BUSINESS_DAYS", "MONTH_BUSINESS_DAYS") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  (
  SELECT 
  CALENDAR_DATE,
  HOLIDAY,
  BUSINESS_DAY,
  SUM(BUSINESS_DAY) OVER (PARTITION BY TO_CHAR(CALENDAR_DATE,'MM-YYYY') ORDER BY CALENDAR_DATE) AS TODATE_BUSINESS_DAYS,
  SUM(BUSINESS_DAY) OVER (PARTITION BY TO_CHAR(CALENDAR_DATE,'MM-YYYY')) AS MONTH_BUSINESS_DAYS
  FROM
  (
  SELECT
  d.CALENDAR_DATE,
  d.DAY_LOCALE_NAME,
  c.HOLIDAY,
  CASE WHEN DAY_LOCALE_NAME NOT IN ('Saturday','Sunday') AND HOLIDAY IS NULL THEN 1 ELSE 0 END AS BUSINESS_DAY
  FROM DW_DAY_D d
  LEFT JOIN
  FDI_X_HOLIDAY_CALENDAR_D c
  ON c.COL_DATE = d.CALENDAR_DATE
  )
  );
  • Grant semantic model access to the FDI_X_HOLIDAY_CALENDAR_F_V view for the OAX$OAC schema using the following SQL script:
    GRANT SELECT ON "OAX_USER"."FDI_X_HOLIDAY_CALENDAR_F_V" TO "OAX$OAC";
  • Validate that the data is loading from the sample as expected using the following SQL script:
    SELECT * FROM FDI_X_HOLIDAY_CALENDAR_F_V
WHERE HOLIDAY IS NOT NULL;

Add Fact with Degen Attributes to the Semantic Model

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

In this step, you create a logical star to define the custom fact, Fact – Holiday Calendar, and implicitly create Dim – Fact – Holiday Calendar – degendim. This custom fact references the autonomous data warehouse object FDI_X_HOLIDAY_CALENDAR_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_HOLIDAY_CALENDAR_F_V in Object, rename Fact Name as Fact – Holiday Calendar. Select the applicable check box as follows and click Next.
    • Select Degen Attribute – HOLIDAY
    • Select Fact – TODATE_BUSINESS_DAYS, MONTH_BUSINESS_DAYS, and BUSINESS_DAY.
    • Use for Key – CALENDAR_DATE


    Step 1 of the wizard on the Add a Fact page

  5. In step 2 of the wizard, set the aggregation rules as follows:
    Fact Column Aggregation Rule
    Todate Business Days (NUMERIC) Max
    Month Business Days (NUMERIC) Average
    Business Day (NUMERIC) Sum


    Step 2 of the wizard on the Add a Fact page

  6. Click the Content Level icon, set the content level as follows, click OK, and then click Finish.
    Dimension Level
    Gregorian Calendar Day


    The Gregorian Calendar dimension and its content level, Day

Join Custom Fact with Degen Attributes to a Prebuilt Dimension

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

In this step, you add the prebuilt dimension, Dim – Data Gregorian Calendar, and join it to the custom degen fact Dim – Fact – Holiday Calendar using the foreign key CALENDAR_DATE defined in the custom degen fact.
  1. On the Logical Star: Fact page, click Manage Dimension.

    The Logical Star: Fact page displaying the Manage Dimension button

  2. From the Manage Dimension list of values, click Add Existing Dimension, and in Add Table, select Dim – Date Gregorian Calendar and click OK.

    The Add Table dialog displaying the Dim – Date Gregorian Calendar

  3. On the Logical Star: Fact page, drag from the custom fact (Fact - Holiday Calendar) circle to the custom dimension (Dim - Date Gregorian Calendar) circle to open the Join dialog. In Join, select Inner as Join Type, click Add Join Condition to add as follows, and then click Done:
    Fact – Holiday Calendar Dim – Date Gregorian Calendar
    Calendar Date (DATE) Date (DATE)


    Join dialog displaying the Holiday Calendar fact and its corresponding Date Gregorian Calendar dimension

  4. Review the join in the graphical mode.

    The join that you created in graphical mode

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

Present the Custom Fact with the Degen Attributes in the Semantic Model

You modify the subject area to present the new custom degen fact and its aggregable metrics, along with the degen dimension and its attributes. The system presents the degen fact as a folder containing the measure columns and the degen dimension as a separate folder containing the attribute columns.

In this step, you modify the SCM – Sales Order subject area to present the new degen fact folder Fact – Holiday Calendar containing three measures, Business Day, Month Business Days, and Todate Business Days, along with the new degen dim folder Dim – Fact – Holiday Calendar – degendim containing the Holiday attribute.

  1. On the Semantic Model Extensions page, click the MySandbox5Mar25 sandbox.
  2. Click Perform Action, click Manage Subject Areas, select Modify a Subject Area, select SCM – Sales Orders subject area, and then click Next.
  3. On the Modify a Subject Area - SCM - Sales Orders page, in step 1 of the wizard, view the subject area details and click Next.
  4. In step 2 of the wizard, click Manage Elements, click Manage New Customizations, and then in Add a Subject Area, select the Fact - Holiday Calendar folder and all the fact measures, and then click Add.

    Add a Subject Area dialog displaying the selected Holiday (Degen attribute) and the applicable measures

  5. Review the selected elements and click Finish.

    Step 2 of the wizard on the Modify a Subject Area - SCM - Sales Orders page displaying the selected data elements

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, and then click Add to Workbook.
  3. Build a report by dragging these:
    • Year
    • Month
    • Date
    • Holiday
    • Business Day
    • Month Business Days
    • Todate Business Days


    Build a report by dragging applicable data elements onto the visualization pane

  4. Filter on 2024/07 which has 2 holidays. All columns add up the same 21 business days in the month, but each column may be useful for different calculations.

    Report filtered on 2024/07 and displaying 2 holidays