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.
- 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
- 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.
Present the Dimension Extension in the Semantic Model
You modify the subject area to present the extended columns in a subject area folder.
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.