Use Case 9: Create a Subject Area

You can create a subject area to present and secure a custom view of a business model to focus on a particular business function.

The prebuilt semantic model doesn’t contain the required Google Analytics business model and metrics. In this use case, you create, model, and present the Google Analytics subject area that contains data from an external source such as Google Analytics. To this subject area, add a custom fact Fact – Google and custom dimension Google Date.

You can apply the concepts covered in this use case to any of the Fusion Data Intelligence subscriptions. 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 Fact to the Semantic Model
  2. Add a Custom Dimension to the Semantic Model
  3. Join the Custom Fact to the Custom Dimension
  4. Present Custom Fact and Custom Dimension in the Semantic Model
  5. Apply and Publish the Customizations
  6. Validate the Results
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 fact table, FDI_X_DUMMY_GOOGLE_DATA_F, in the autonomous data warehouse associated with your Fusion Data Intelligence instance using the sample files FDI_X_DUMMY_GOOGLE_DATA_F.xlsx or FDI_X_DUMMY_GOOGLE_DATA_F.sql.
  • Grant semantic model access to the table FDI_X_DUMMY_GOOGLE_DATA_F view for the OAX$OAC schema using this SQL script:
    GRANT SELECT ON "OAX_USER"."FDI_X_DUMMY_GOOGLE_DATA_F" TO "OAX$OAC";
  • Create the FDI_X_GOOGLE_DATE_D_V view using this SQL script:
    CREATE OR REPLACE FORCE EDITIONABLE VIEW "OAX_USER"."FDI_X_GOOGLE_DATE_D_V" ("CALENDAR_DATE", "CAL_MONTH_NUMBER", "CAL_YEAR_ID") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  (
SELECT CALENDAR_DATE, CAL_MONTH_NUMBER, CAL_YEAR_ID
FROM DW_DAY_D
);
  • Grant semantic model access to the FDI_X_GOOGLE_DATE_D_V view or the OAX$OAC schema using this SQL script:
    GRANT SELECT ON "OAX_USER"."FDI_X_GOOGLE_DATE_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_GOOGLE_DATE_D_V;
  • Validate that the data is loading from the sample as expected using the following SQL script:
    SELECT * FROM OAX_USER.FDI_X_DUMMY_GOOGLE_DATA_F;

Add a Custom Fact to the Semantic Model

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

In this step, you create a logical star to define custom fact Fact – Google referencing the autonomous data warehouse object FDI_X_DUMMY_GOOGLE_DATA_F.
  1. On the Semantic Model Extensions page, click the MySandbox5Mar25 sandbox.
  2. Click Perform Action, click Manage Logical Star, select Create Logical Star, and then click Next.
  3. On the Logical Star: Fact page, click Add Fact.

    The Logical Star: Fact page displaying the Add Fact button

  4. On the Add a Fact page, in step 1 of the wizard, select OAX_USER in Schema, select FDI_X_DUMMY_GOOGLE_DATA_F in Object, and rename Fact Name as Fact - Google. Select the Use for Key check box for GOOGLE_SESSION_DATE source column, select the Select Fact check box for GOOGLE_ANALYTICS_ACTIVEUSERS and GOOGLE_ANALYTICS_NEWUSERS source columns, and click Next.

    Step 1 of the wizard on the Add a Fact page

  5. Leave the aggregation rules as Sum and click Finish.

    Step 2 of the wizard on the Add a Fact page

  6. Review the custom fact in the graphic mode.

    The custom fact in the graphic mode

  7. Continue to the next task without exiting the wizard, Add a Custom Dimension to the Semantic Model.

Add a Custom Dimension 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 Fact – Google logical star to add a custom dimension Dim – Google Date referencing the autonomous data warehouse object FDI_X_GOOGLE_DATE_D_V and define a multi-level hierarchy named Google Hierarchy. For each level, the use case defines a unique primary key and display attribute.
  1. On the Logical Star: Fact page, click Manage Dimension and then click Add Dimension.

    The Logical Star: Fact page displaying the Manage Dimension list of values

  2. On the Add a Dimension page, in step 1 of the wizard, select OAX_USER in Schema, select FDI_X_GOOGLE_DATE_D_V in Object, rename Dimension Name as Google Date, select the Use for Key check box for CALENDAR_DATE and the Add Attributes check box for CALENDAR_DATE, CAL_MONTH_NUMBER, and CAL_YEAR_ID. Click Next.

    Step 1 of the wizard on the Add a Dimension page

  3. Under Display Name, rename as follows:
    • Calendar Date as Date
    • Cal Month Number as Month
    • Cal Year Id as Year

    Note:

    For unique names, you can prefix Google for Date, Month, and Year.
  4. Specify the Date Hierarchy for the dimension Google Date as follows:
    1. Enter Google Hierarchy in Hierarchy Name.

      Step 2 of the wizard on the Add a Dimension page displaying the entered hierarchy name

    2. Under Selected Data Elements, right click the top level, Total, and click Add 'n' Child Levels. In Number of Levels, specify 2 as Number of Hierarchy Levels and click Done.

      Number of Levels dialog

    3. Rename the levels and drag corresponding columns from Google Date under Available Data Elements into each level folder.
      Drag as follows:
      • Drag Google Month to Month level folder.
      • Drag Google Year to Year level folder.
      • If not already there by default, drag Google Date to Detail level folder.

      Step 2 of the wizard on the Add a Dimension page displaying how to rename the levels and drag corresponding columns into each level folder

    4. Set Primary Key and Display Attribute for each level.

      • Year Folder (Data Element) - In Properties, select the pencil to set Primary Key and Display Attribute for Year level to Google Year.


        Step 2 of the wizard on the Add a Dimension page displaying how to set the primary key and display attribute at the Year level

      • Month Folder (Data Element) - In Properties, select the pencil to set Primary Key and Display Attribute for Month level to Google Month.


        Step 2 of the wizard on the Add a Dimension page displaying how to set the primary key and display attribute at the Month level

      • Detail Folder (Data Element) - In Properties, select the pencil to set Primary Key and Display Attribute for Detail level to Google Date.


        Step 2 of the wizard on the Add a Dimension page displaying how to set the primary key and display attribute at the Detail level

  5. Click Finish. Continue to the next task without exiting the wizard, Join the Custom Fact to the Custom Dimension.

Join the Custom Fact to the Custom Dimension

You join the custom dimension hierarchy to the custom fact by defining the join type and join condition. 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 custom Fact – Google fact to the custom dimension hierarchy Dim – Google Date using the Google Session Date key.
  1. On the Logical Star: Fact page, drag from the custom fact (Fact - Google) circle to the custom dimension (Dim - Google Date) circle to open the Join dialog.

    The Logical Star: Fact page displaying how to join the custom fact and custom dimension

  2. In Join, specify Inner as Join Type, under Join Condition, select Google Session Date (DATE) for Fact - Google, select Google Date (DATE) for Dim - Google Date, and click Done.

    The Join dialog

  3. View the join in graphic mode.

    The join in graphic mode

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

Present Custom Fact and Custom Dimension in the Semantic Model

You modify the subject area to present the new custom fact and its aggregable metrics, along with the new custom dimension attributes and hierarchy levels.

The fact is represented as a folder containing measure columns in the subject area. The dimension is represented as a folder containing columns in the subject area. The hierarchy levels are represented 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 create a new subject area Google Analytics to present the new fact folder Fact – Google containing the Google Analytics Activeusers and Google Analytics Newusers measures, along with the new custom Google Date folder containing the custom hierarchy Google Hierarchy with two levels (Year, Month) and lowest Detailed level.

  1. On the Sandbox: MySandbox5Mar2025 page, click Perform Action, click Manage Subject Area, select Create a Subject Area, and then click Next.
  2. On the Create a Subject Area page, in step 1 of the wizard, enter Google Analytics in New Subject Area Name, and click Next.

    Step 1 of the wizard on the Create a Subject Area page

  3. In step 2 of the wizard, click Manage Elements and then click Manage New Customizations.
  4. In Add a Subject Area, select the custom dimension, Dim - Google Date dimension folder (including the hierarchy), and the custom fact, Fact - Google along with all the measures, and then click Add.

    The Add a Subject Area dialog displaying the selected custom dimension and fact

  5. Review and click Next.

    Review the custom fact, Fact - Google

  6. In step 3 of the wizard, right click the items in the custom subject area to rename as desired or drag the items to reorder and click Next.

    Step 3 of the wizard on the Create a Subject Area page displaying how to rename and reorder items

  7. Review and click Finish.

    Step 4 of the wizard on the Create a Subject Area page displaying the Finish button

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 Google Analytics, and then click Add to Workbook.

    The Add Data dialog displaying the selected custom subject area, Google Analytics

  3. Create a report based on Google Analytics, the custom subject area that you created. Use the columns from the Google Date folder and measures from Fact - Google to confirm that the fact and dimension are joined correctly.

    Report based on the custom subject area, Google Analytics