Add new Account Hierarchy Snapshot folder under Customer in OTBI

To improve performance for OTBI reports that use account hierarchy and parent account attributes, there is now an option to enable a new Account Hierarchy Snapshot subfolder under Customer in the Sales - CRM Opportunities and Products Real Time, Sales - CRM Pipeline, and Sales - CRM Customer Overview subject areas. 

When enabled, the new Customer -> Account Hierarchy Snapshot (a point-in-time copy of the account hierarchy) includes hierarchy and parent attributes such as: 

  • Sales Account Id
  • Parent: Account Id, Account Name, Account Unique Name, D-U-N-S Number, Registry Id
  • Base Level (Level 0): Account Id, Account Name, Account Unique Name, Registry Id
  • Levels 1–8: Account Id, Account Name, Account Unique Name, Registry Id
  • Top Level (Level 9): Account Id, Account Name, Account Unique Name, Registry Id
  • Fixed Hierarchy Level, Distance
  • Account Hierarchy Snapshot Insert Date, Account Hierarchy Snapshot Update Date

This change enables customers to build more performant OTBI reports that require account hierarchy attributes (levels 0–9) and parent account attributes, helping reduce long-running queries when reporting on hierarchies.

Steps to enable and configure

This feature is controlled by a profile option. Users will only see the Account Hierarchy Snapshot folder and its attributes when the profile option is enabled.

Profile Option Code: ORA_ENABLE_ACCTHIER_SNAPSHOT_OTBI

Profile Option Name: Enable Account Hierarchy Snapshot in OTBI

Profile Option Description: Enable the Account Hierarchy Snapshot folder in OTBI.

Tips and considerations

The above point-in-time set of hierarchy columns are sourced from HZ_PARTY_HIER_CF_DENORM, which is refreshed by the Refresh Denormalized Data for Account Hierarchy scheduled job. Visibility of the Account Hierarchy Snapshot folder is controlled by the profile option ORA_ENABLE_ACCTHIER_SNAPSHOT_OTBI. 

Key resources

Subject Areas for Transactional Business Intelligence

Access requirements

Anyone who has access to the following OTBI subject areas will have access to the newly added Account Hierarchy Snapshot folder and columns when the  ORA_ENABLE_ACCTHIER_SNAPSHOT_OTBI profile option is enabled:

  • Sales - CRM Opportunities and Products Real Time
  • Sales - CRM Pipeline
  • Sales - CRM Customer Overview