About Best Practices and Tips to Extend the Semantic Model
Before extending your semantic model, review the best practices and tips to ensure that your extensions work as expected.
- Prefix a custom object with
X_ZZZ_whereZZZis 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
- 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 ready-to-use 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 Analytics Warehouse environment occur. Keep local branch for testing and merge to 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 Analytics Warehouse 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 Analytics Warehouse else the applicable semantic model extension fails.
- 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 Analytics Warehouse 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.
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.
Best practice while adding a fact is to always set the content levels for the custom dimensions that are joining to the custom fact.
Best practise regarding editing the main branch: 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.
Best practice for number of 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.
Best practice while 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.