Recommendations and Tips to Extend the Semantic Model

Before extending your semantic model, review the recommendations and tips to ensure that your extensions work as expected.

Semantic Model Extensions Framework

Branch Framework

If you're still on the Semantic Model Branch framework, now is the time to migrate to the Sandbox framework. See Migrate to the Sandbox Framework for Semantic Model Extensions.

Sandbox Framework

Only retain the in use sandboxes. Delete the sandboxes that you used for testing and aren't in use any longer. Maintaining additional unused sandboxes degrades performance of the system.

Database Objects

Database Naming Standards for Autonomous Data Warehouse Objects

  • Prefix a custom object with X_ZZZ_ where ZZZ is an abbreviation of your organization.
  • Suffix different objects as:
    • _A = Aggregate
    • _D = Dimension
    • _DH = Dimension Hierarchy
    • _F = Fact
    • _H = Helper
    • _M = Map Dimension
    • _MD = Mini Dimension
    • _V = Views
    • _MV = Materialized View
    • _DS = Data Augmentation Dataset
    • _EXT = Data Augmentation Extension
  • As a best practice, don't create any custom database objects starting with "DW" as this may result in conflicts with the prebuilt object names. Custom database objects starting with "DW" may cause inconsistent behavior in the in the Semantic Model Extensions wizard.
  • Don't use these system-reserved suffixes for the custom schema names: DW, OAC, INFRA, SECURITY, USER, or CORE.

    If you use one of these system-reserved suffixes for the custom schema names, you risk granting unintended privileges to your custom schema during pipeline processing.

Data Augmentation Datasets and Flexfields

  • Ensure that the changes in source are addressed in your Oracle Fusion Data Intelligence instance. For example, if a descriptive flexfield used in a custom subject area has been disabled in the source, then you must replace or remove the applicable descriptive flexfield in Oracle Fusion Data Intelligence else the applicable semantic model extension fails.
  • You can reference synonyms from the data augmentation datasets in the semantic model extensions after the initial full load for the data augmentation has completed. Use the "Run Immediately" option in the data augmentation to execute the full load straightaway.

Extending

General
  • When naming objects (dimensions, facts, and columns) remove all leading and trailing spaces. You can use underscores and spaces in names but avoid all other special characters.
  • When adding custom attributes or defining keys used for joining, ensure that the display name is unique and doesn’t conflict with any of the prebuilt column names.
Extend Dimension
  • Important: You must keep the extensions to a minimum and combine them to prevent unnecessary overhead and degraded performance. When extending a dimension (if the extension granularity is one to one [1:1] with the prebuilt dimension) combine multiple extensions for the same dimension in a single source (table/view/synonym) in Autonomous Data Warehouse. It's preferable to have one extension with many columns, rather than have multiple extensions per column.
  • Caution: When extending a dimension, it's highly recommended to join with the base dimension key. If it isn't possible to join on the base dimension key, you can join to another base column with caution, validating data grain and cardinality. It's strongly not recommended to join on another extension column. The system processes the extensions with dependencies sequentially, lengthening the time taken to apply and publish the sandbox, and could have a negative impact on the query performance.
  • When extending DEGEN Dimensions ("Details" folders), always maintain the same level of granularity by joining on the Primary key(s) of the fact with a one to one [1:1] relationship. Don't define many to many [M:M] joins because it may cause performance degradation and data duplication.
  • Be cautious when extending dimensions that have one to many (1:M) relationships (such as multi-select), because:
    • They may result in data duplication due to the extended data being of a lower grain than the parent dimension.
    • Maximum extension index length may be exceeded.

    To avoid the constraint, name the table/view/synonym as short as feasible. For example, FDI_X_SZ_V (Size) and FDI_X_PR_V (Price).

  • Before extending a logical object, validate that the associated presentation table is exposed in the subject area. Refer to the applicable semantic model lineage spreadsheets:
  • While extending a dimension, if the table that you select is an augmentation table, select it from the OAX$OAC schema instead of OAX_USER. The main schema for augmentation tables is OAX$OAC even though the augmentation tables are present in the OAX_USER as well.
  • Extended attribute column size can't exceed 256 characters.

Create Dimension

  • When creating a custom dimension, you may unselect "Add hierarchy to Subject Area". However, it is still necessary to define a Hierarchy Primary Key and Display Attribute. Click on Selected Data Elements Detail folder, then the Properties edit icon to define the Hierarchy Primary Key and Display Attribute.

Create Degenerate Dimensions

  • Avoid adding non-degenerate dimension attributes to fact tables. Instead, select the degenerate flag for the attribute when defining the fact table definition. Non-flagged dimension attributes within fact tables will negatively impact your report structure, increase the risk of runtime errors, and complicate system maintenance.
Create Fact
  • When adding a custom fact, always set the content levels for the custom dimensions that are joining to the custom fact.
  • When joining facts to dimensions, ensure that the columns being joined are of compatible data types.
Create Hierarchy
  • When adding a custom hierarchy, avoid aiming to display the grand total levels in visualizations because custom hierarchies are exposed only from the first level. The prebuilt hierarchies too don’t expose the total levels. The Grand Total level just gives the grand total amount; hence use it only when there is no join between a fact and dimension and the metric has to be set at a total level.
  • When defining a hierarchy it is necessary to map all available data elements to a level or details.
  • Before editing a hierarchy (such as add, remove, or change levels), first remove the hierarchy from the Subject Area, edit the hierarchy, and then add the hierarchy back to the Subject Area.
  • The Sandbox framework currently doesn't support adding value-based hierarchies to a subject area.

Adding Columns

  • Apply functions and aggregations directly to base columns. Avoid nesting derived columns to prevent unnecessary dependencies, processing overhead, delays, and potential calculation errors in the semantic model.
  • Avoid adding columns that are already present in the existing Semantic Model; this creates redundant joins and increases processing overhead.
  • When working with custom facts and dimensions, avoid using derived columns. Instead, perform the necessary calculations within the database layer, or define the expression directly on the object during its creation process.

Using Custom Data Configurations

  • If you utilize a specialized Custom Data Configuration such as Configurable Account Analysis, Fusion Accounting Hub, or Supply Chain Planning Application, you should implement all necessary customization exclusively within the application. Avoid creating duplicate configurations in the semantic model, as this introduces redundant dependencies and unnecessary processing overheads.

Deployment

  • Migrations must flow in a single direction only. Choose one environment to be the master Development environment. After user acceptance testing, generate and deploy a Semantic Extensions bundle to migrate changes to Production and other environments.
  • If the semantic model secures objects with newly configured application roles, groups, or users, ensure to import and deploy the Security bundle prior to importing and deploying the Semantic Model bundle.