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

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.
• Don’t export the Semantic Extensions bundle separately and then do security promotion through test to production. Generate the Semantic Extensions bundle and include the extensions you want and then include security as well.

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

• When joining facts to dimensions, ensure that the columns being joined are of compatible data types.
• 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.
• 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.
• 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 is preferable to have one extension with many columns, rather than have multiple extensions per column.
• If it's necessary to have multiple extensions on the same dimension due to varying one to many (1:M) relationships (such as multi-select), then be cautious of an index length limitation that may be hit. 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).
• When adding a custom fact, always set the content levels for the custom dimensions that are joining to the custom fact.
• 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 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.