Oracle Cloud Infrastructure Documentation

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.

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).

  • 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.

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 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.

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.