Oracle Cloud Infrastructure Documentation

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

Recommended naming conventions: Ensure that you follow these naming conventions to avoid loss of customizations after an upgrade and to easily identify the custom 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
  • Use unique names for objects in your semantic model extensions to avoid issues caused by duplicate names.
Recommendations applicable across the types of extensions:
  • Perform all development activities in one environment that you consider as the master development environment. Subsequently, you must move the extensions from that environment to other non-production or production environments.
  • When you need to create a star schema (custom dimensions and custom facts), always create the custom dimensions first followed by the custom facts.
  • When you define joins in any customization wizard, ensure that the join columns are of compatible data types.
  • Don’t extend a prebuilt fact to alter the structure. For example, don’t add Invoice Distribution ID into the Invoice Line fact table.
  • Don’t keep changes in a local branch for long periods of time. Local branches can become out of synchrony when changes to the Oracle Fusion Data Intelligence environment occur. Use a local branch for testing and merge to the main branch soon and frequently. Promote the main branch to Production using the Test to Production (T2P) process.
  • 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.
  • Ensure that the table and column names don't have leading or trailing spaces in the semantic model extension steps.
  • Ensure to check and correct the errors and warnings on the Semantic Model Extensions page regularly.
  • If you want to use any dataset that has been brought in through data augmentation to build the joins or incorporate any object from the dataset into your semantic model, then you must ensure to run an incremental load before using the dataset.
  • While reapplying any extension, ensure that you cleanup the view in schema OAX_CUSTOM_DATA, re-edit the extension in the Main branch, and re-apply it.
  • Don’t create any table starting with "DW" in custom schemas and the OAX_User schema because this may result in conflict with the prebuilt object names. If you create tables starting with "DW", then these tables won't show as custom tables in the Semantic Model Extensions wizard.
  • Ensure that you don't use system variables in your extensions.
Recommendations while adding a dimension:
  • While defining the joins, you can choose to skip the joins and proceed to the next action. This results in the step ending up in a warning state but the process adds the custom facts. Oracle Fusion Data Intelligence clears the warning when you define the joins later.
  • Always specify the primary key and display attribute when you define the hierarchies for the custom dimension.
When extending a dimension, combine multiple extensions for the same dimension in the database. Same dimension extensions always join to the base dimension on the same columns. Hence, in the database, combine the extensions into one object and then extend all columns using that object.
Note

Multiple steps defined for the same dimension eventually lead to a constraint causing the extensions to fail. For example, in step 1, you use table A and add a set of columns and then in step 5, you use the same table A, extend the same dimension, and add the same set of columns. This results in failure because in step 1, the columns are already added; hence, step 5 fails and the user extensions aren’t available. Combining the extensions into one database object and then defining all extensions using one step is highly recommended.

When adding a fact, always set the content levels for the custom dimensions that are joining to the custom fact.

Whenever a system extension run is replaying the master branch, you must not edit the main branch during this time as that might cause a conflict leading to failure of the semantic extensions.

Minimize the number of semantic extensions as much as possible. For examples, don’t create multiple extensions to modify the same subject area; instead, you must consolidate the modifications to the same subject area. If you’ve to modify the same dimension, then consolidate changes to a single logical table source.

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 ready-to-use 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.

Don’t export the semantic model extensions bundle separately and then do security promotion through test to production. Generate the semantic model extensions bundle and include the extensions you want and then include security as well.