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
  • All new instances no longer support database objects starting with "DW" in the semantic model. The recommendation is to name the custom objects with a unique prefix such as X_ZZZ where ZZZ is an abbreviation of your organization. For existing instances, the database objects starting with "DW" may cause conflicts in the semantic model extensions.

Data Augmentation Datasets and Flexfields

  • Ensure that the changes in source are addressed in your Oracle NetSuite 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 NetSuite Analytics Warehouse 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, ensure that the name is unique and doesn't conflict with any other prebuilt or custom attribute name.
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.
  • 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.
  • 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 that could have a negative impact on the query performance.

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.

Impact of Data Flows and Data Replication

If you’re using data flows and data replication capabilities in Oracle Analytics Cloud associated with your Oracle NetSuite Analytics Warehouse instance and creating semantic model extensions in Oracle NetSuite Analytics Warehouse, note that it may impact the performance of Oracle Analytics Cloud. You can schedule data flows outside of business hours because running complex data flows during business hours may have a negative impact on the overall system performance.