Use Case 1: Extend a Conformed Dimension

You can extend a dimension folder by adding new columns that represent additional descriptive attributes related to the conformed dimension.

The Customer Bill-to Details folder, (a sub-folder of Customer Site folder, of the prebuilt Subject Area Financials - AR Adjustments), already contains the attribute Bill-to-Country containing the two-letter country abbreviation. However, it's missing the additional descriptive attribute Country Full Name.

In this use case, you add the Country Full Name column from an external source into the prebuilt Customer Bill-to Details folder to associate the full country attribute with the customer site. You join the custom extension to the prebuilt Dim – Customer Bill To Location conformed dimension using Customer Location ID.

This use case requires the Fusion ERP 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 the 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:

Use the following to complete this task:

Sample dimension extension table - FDI_X_COUNTRY_D
Attribute - Country FullName
Subject area - AR Adjustments
Parent Dimension Folder - Customer
Dimension folder - Dim - Customer Bill To Location
Prebuilt ADW table - DW_CUSTOMER_LOCATION_D

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 extension table, FDI_X_COUNTRY_D, in the autonomous data warehouse associated with your Fusion Data Intelligence instance using the sample files FDI_X_COUNTRY_D.xlsx or FDI_X_COUNTRY.sql.
Create a view titled FDI_X_CUSTOMER_LOCATION_D_V to join the custom table FDI_X_COUNTRY_D to the prebuilt dimension table DW_CUSTOMER_LOCATION_D that contains the primary key CUSTOMER_LOCATION_ID, which will be used to join when extending the dimension. Use the following SQL script to create the view:
```
CREATE OR REPLACE FORCE EDITIONABLE VIEW "OAX_USER"."FDI_X_CUSTOMER_LOCATION_D_V" ("CUSTOMER_LOCATION_ID", "COUNTRY_FULLNAME") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  (
SELECT p.CUSTOMER_LOCATION_ID, NVL(c.COUNTRY_FULLNAME,'~NOVALUE~') AS COUNTRY_FULLNAME
FROM OAX$OAC.DW_CUSTOMER_LOCATION_D p
LEFT JOIN FDI_X_COUNTRY_D c
ON p.COUNTRY_CODE = c.COUNTRY_CODE
);
```
Grant semantic model access to the FDI_X_CUSTOMER_LOCATION_D_V view for the OAX$OAC schema using the following SQL script:
```
GRANT SELECT ON "OAX_USER"."FDI_X_CUSTOMER_LOCATION_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_CUSTOMER_LOCATION_D_V;
```

Add a Dimension 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 conformed dimension being extended.

In this task, you edit Fact – Fins – AR Adjustments logical star to add a dimension extension on Dim – Customer Bill To Location referencing the autonomous data warehouse object FDI_X_CUSTOMER_LOCATION_V. The custom extension is joined to the prebuilt Dim – Customer Bill To Location conformed dimension using Customer Location ID.

On the Semantic Model Extensions page, click the MySandbox5Mar25 sandbox.
In Perform Action, click Manage Logical Star, then Edit Logical Star, then Out of the box, then select Financials - AR Adjustments as the subject area, select Fact – Fins – AR Adjustments as the corresponding fact, and then Next.
On the Logical Star: Fact page, locate the Dim - Customer Bill To Location dimension table, right click on it, and then click Manage Extension.
In the Manage Extension list of values, select Extend Dim.
On the Extend a Dimension page, in step 1 of the wizard, select OAX_USER in Schema and select FDI_X_CUSTOMER_LOCATION_D_V in Object. Select the Use for Key check box for CUSTOMER_LOCATION_ID source column, select the Add Attributes check box for COUNTRY_FULLNAME source column, and click Next.

Note:
Ensure that the display name is unique and doesn’t conflict with any of the prebuilt column names; for example, rename CUSTOMER_LOCATION_ID as FDI Customer Location ID.
In step 2 of the wizard, select Left Outer in Select Join Type.

Note:
The join type is mostly Inner (match) or Left (all from original Dim). Join type Left ensures that no data is lost.
Select Customer Location ID (DOUBLE) as Dimension Key and CUSTOMER_LOCATION_ID (DOUBLE) as Extended Dimension Key. Click Finish.
Review the extended dimension.

Present the Dimension 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 Financials - AR Adjustments subject area to present the extended Country Full Name column in the prebuilt Customer Bill-to Details folder.

Navigate to the MySandbox5Mar25 sandbox.
In MySandbox5Mar25 sandbox, click Perform Action, then Manage Subject Areas, then Modify a Subject Area, select Financials – AR Adjustments as the subject area, and then Next.
In step 1 of the wizard on the Modify a Subject Area – Financials – AR Adjustments page, view the details and click Next.
In step 2 of the wizard, select Manage Elements, and then click Manage Factory Customizations.
In Add a Subject Area, select Country Fullname and click Add Folders.
In Add a Subject Area, select the Customer Bill-to Details folder and click Add Folders.
Review the additions 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.

On the Semantic Model Extensions page, hover over the MySandbox5Mar25 sandbox to view Actions, and then click Apply Changes.
Click the Activity tab to monitor the Apply Changes task on the Activity page.
After the changes are successfully applied, click Publish Model on the Semantic Model Extensions page.
In Publish Model, in User Extensions, select MySandbox5Mar25 and in Security Configurations, select All.
Click Publish.
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.

On the Fusion Data Intelligence Console, click Go to Home Page.
On the Oracle Analytics Home page, click Create, click Workbook, in Add Data, select Financials - AR Adjustments, the subject area that is extended in this use case, and then click Add to Workbook.
Expand the Customer Site folder, then expand the Customer Bill-to Details folder, and verify that the extended attribute Country Fullname is visible at the bottom of the list of attributes.
Build a report to display that the extended dimension attribute has been successfully joined to the desired fact measures.