Intoduction
Always create dimensional hierarchies, even when there’s only one level. This is a fundamental best practice in Oracle Analytics that cannot be overstated.
Establishing dimensional hierarchies from the outset of your semantic model design ensures optimal query performance. It provides the foundation for sophisticated analytical capabilities.
We recommend dimensional hierarchies for many critical reasons. These reasons directly impact both the technical performance of your analytics platform and the end-user experience when exploring data.
- Oracle Analytics uses dimensional hierarchies to select the most optimized logical table source by way of content levels. When you define dimensional hierarchies with proper content levels, the query engine can intelligently determine which aggregate tables contain the pre-calculated data. This optimization dramatically reduces query execution time by avoiding unnecessary joins and aggregations at runtime, resulting in faster dashboard loads and more responsive interactive analysis.
- Dimensional hierarchies are required to drill up and down between levels. Without properly configured dimensional hierarchies, users lose the ability to seamlessly navigate through different levels of detail in their reports. This drill-down capability is essential for exploratory data analysis, allowing business users to start with high-level summaries and progressively investigate the underlying details.
Sometimes drilling is intuitive and follows natural business relationships.
For example, if you’re analyzing a brand, you’ll probably want to drill down to its corresponding Universal Product Codes (UPC) to understand performance at the individual product level.
This type of hierarchical relationship mirrors how business users naturally think about their products—starting with brand performance and then investigating which specific products are driving that performance.
Other types of drill-downs might not be obvious at first glance but still prove incredibly useful in practice.
For example, you might want to drill down from a contact type to a contact name. This enables your sales team to transition from analyzing patterns across customer segments to identifying specific individuals for targeted outreach campaigns.
- Dimensional hierarchies are useful when Oracle Analytics joins two result sets. For example, if you combine two fact tables in the same report—such as joining sales data with inventory data—the dimensional hierarchies ensure that the join happens at the correct grain level. Without dimensional hierarchies, the system may struggle to determine the appropriate level of aggregation, potentially leading to incorrect results.
- Dimensional hierarchies are required to create level-based measures. These specialized calculations depend on understanding the hierarchical structure of your dimensions. Level-based measures allow you to create sophisticated metrics like “percent of parent” calculations, where you compare each member’s value to its immediate ancestor.
- Time dimensions are required in some time series calculations. For example, where the calculation is based on a specific level, such as the year. Functions like AGO (calculating values from previous periods), TODATE (year-to-date aggregations), and period-over-period comparisons all rely on the time dimension hierarchy to understand the relationships between time periods. Without a properly configured time dimension, these powerful analytical functions simply won’t work.
4 Critical Types of Dimensional Hierarchies
Oracle Analytics supports several types of dimensional hierarchies. Each type is designed to accommodate different data structures and business requirements.
Understanding the characteristics and appropriate use cases for each hierarchy type is crucial for building an effective semantic model. This ensures your dimensional hierarchies accurately represent your organization’s data relationships.
1-Balanced level-based hierarchy
The balanced level-based hierarchy is the most common type of dimensional hierarchies used in Oracle Analytics.
It forms the foundation of dimensional modeling in most business intelligence scenarios. In all level-based dimensional hierarchies, the detail levels roll up into higher levels through clearly defined parent-child relationships.
This structure mirrors the way most business data is naturally organized. Detailed transactional data aggregates up through progressively broader categories.
- In a balanced level-based hierarchy, all members of the dimensional hierarchies have ancestors at all levels as shown here. This means that every leaf node (the most detailed level) has a complete path through all intermediate levels up to the root. For instance, in a geographic hierarchy, every city belongs to a state, every state belongs to a region, and every region belongs to a country. There are no gaps or missing levels in the traversal path, which simplifies query logic and ensures consistent aggregation behavior.
- Balanced dimensional hierarchies are ideal for dimensions with consistent structure across all members. Examples include standard time periods, organizational structures with uniform reporting layers, or product categorizations where every item follows the same classification scheme.
- The predictable structure of balanced dimensional hierarchies enables Oracle Analytics to optimize query performance. The system can rely on consistent relationships between levels when determining which pre-calculated summaries to use.
Real-World Examples of Balanced Hierarchies:
Example 1: Time Dimension Hierarchy
time dimension is a special level-based hierarchy that is used specifically for time-based dimensional hierarchies.
A time dimension is required if you want to use time series calculations such as AGO and TODATE. These functions depend on understanding the sequential nature of time periods and the hierarchical relationships between different granularities of time.
To define a time dimension, you select Time in the properties of the dimensional hierarchy. This signals to Oracle Analytics that this dimension should be treated with special time-aware logic.
The time dimension typically includes levels such as Year, Quarter, Month, Week, and Day. Each level contains the appropriate keys and descriptions.
When configured as a time dimension, Oracle Analytics can automatically calculate period-over-period changes, cumulative totals, moving averages, and other temporal analytics that are fundamental to business reporting.
The time hierarchy also enables users to easily switch between different time grains in their analyses. They can view annual trends one moment and drill down to daily patterns the next—without requiring separate measures for each time level.
Example 2: Geographical Dimension Hierarchy
Geographical Dimension level based hierarchy :
A geographical hierarchy organizes location data from broad regions down to specific locations. This enables analysis at multiple levels of geographic granularity.
This structure is essential for businesses with distributed operations, retail chains with multiple store locations, or any organization that needs to analyze performance across different geographic markets.
The hierarchy typically flows from Country to Region to State/Province to City to specific locations like stores or warehouses.
This structure supports questions like “How are sales performing by region?” with the ability to drill down to see which specific cities or stores are driving regional performance.
Geographic dimensional hierarchies are particularly valuable for territory management, logistics optimization, and identifying geographic trends in customer behavior or product demand.
Example 3: Product Dimension Hierarchy
Product Dimension level based hierarchy
The product hierarchy represents how your organization categorizes and organizes its products or services.
This typically includes levels such as Product Line, Brand, Category, Subcategory, and individual Product or SKU.
This hierarchical structure mirrors how product managers and merchandisers think about their offerings. They start with broad product lines and drill down through increasingly specific classifications.
A well-designed product dimensional hierarchy enables product managers to analyze performance at the brand level to understand overall brand health.
They can then drill down to categories to identify which product types are driving brand performance. Ultimately, they can examine individual SKUs to understand performance at the most granular level.
This structure is fundamental to merchandising analysis, inventory planning, promotional effectiveness studies, and product portfolio optimization.
2-Parent-Child Hierarchy
Parent child hierarchies are a specialized type of dimensional hierarchies used to organize and represent hierarchical information.
They are particularly useful for organizing data in a way that reflects the relationships between different levels of data. Examples include organizational structures or sales hierarchies.
Unlike level-based dimensional hierarchies where the structure is predefined with fixed levels, parent-child hierarchies are flexible and self-referencing.
Each member points to its parent within the same dimension table. This approach is ideal for scenarios where the depth of the hierarchy varies across different branches, or where the hierarchy structure changes frequently.
Parent-child dimensional hierarchies can accommodate unlimited depth and asymmetric structures. This makes them perfect for representing organizational charts where different departments may have different numbers of management layers.
They’re also ideal for chart of accounts structures where some account categories have many subcategories while others have few.
The flexibility of parent-child dimensional hierarchies comes with some performance considerations. Queries may be slower than with level-based dimensional hierarchies because the system must recursively traverse the parent-child relationships at query time.
However, this trade-off is often worthwhile for dimensions where the structural flexibility is essential.
Example 1: Organizational Reporting Structure
A common real-life parent-child hierarchy occurrence is an organizational reporting hierarchy chart.
In most organizations, the reporting structure is not uniform. Some departments may have five layers of management while others have only three.
The CEO sits at the top, with various executive vice presidents reporting directly. Some EVPs may have multiple layers of directors, senior managers, and managers beneath them, while others may have a flatter structure.
A parent-child hierarchy naturally represents this variable-depth structure. Each employee record includes a “reports to” field pointing to their manager’s employee ID.
This allows the dimensional hierarchies to accommodate new positions, restructuring, and varying organizational depths without requiring changes to the dimensional model itself.
Example 2: Chart of Accounts
Chart of Accounts :
Financial chart of accounts structures are classic examples of parent-child dimensional hierarchies.
In accounting systems, accounts are organized into categories (Assets, Liabilities, Equity, Revenue, Expenses) with multiple levels of sub-accounts beneath each category.
The depth varies significantly. Some expense categories might have detailed sub-accounts for different types of costs, while other categories might be relatively flat.
For example, under Operating Expenses, you might have Marketing Expenses with subcategories for Digital Marketing, Traditional Marketing, and Events.
Digital Marketing might be further broken down into Social Media, Search Engine Marketing, and Display Advertising. Meanwhile, other expense categories might not require such detailed breakdown.
A parent-child hierarchy elegantly handles this variability. It allows accountants to add new accounts or reorganize the structure without requiring database schema changes.
3-Skipped-Level Hierarchy
A skipped-level hierarchy is a special level-based hierarchy where not all members of the dimensional hierarchies have ancestors at all levels.
To define a skipped-level hierarchy, you select Skipped Levels in the properties of the hierarchy.
This configuration tells Oracle Analytics that it should not expect every member to have a value at every level. It should handle null or missing values appropriately during aggregation and navigation.
Skipped-level dimensional hierarchies are common in real-world data where certain exceptions exist to the standard hierarchy structure.
This example shows a skipped-level hierarchy where Washington DC doesn’t belong to a state, so the state/province level is skipped.
While most US cities fall within states and follow the standard Country > Region > State > City hierarchy, Washington DC is a federal district that doesn’t belong to any state.
Similarly, you might encounter other scenarios requiring skipped levels. These include international customers who don’t fit into your regional sales territories, direct sales channels that bypass the usual distributor level, or special product classifications that don’t follow the standard category structure.
By properly configuring the dimensional hierarchy as skipped-level, you ensure that these exceptions are handled gracefully in reports and calculations. This prevents errors or misleading aggregations.
4-Ragged or Unbalanced Hierarchy
A ragged or unbalanced hierarchy is another special level-based hierarchy where not all the data is present at all levels of the dimensional hierarchies.
- To define a ragged hierarchy, you select Ragged in the properties of the hierarchy. This tells Oracle Analytics that different branches of the dimensional hierarchies have different depths, and the system should handle these variations appropriately when aggregating data and enabling drill-down operations.
- Ragged dimensional hierarchies are particularly common in distribution channel structures, where different channels may have different numbers of intermediary levels between the manufacturer and the end customer. Direct web sales might go straight from the manufacturer to the consumer, while traditional retail might involve distributors, wholesalers, and then retail stores.
- When working with ragged dimensional hierarchies, it’s important to understand how your BI tool handles the “missing” levels during aggregation. Oracle Analytics can be configured to skip over missing levels appropriately, ensuring that totals at higher levels include all relevant data regardless of how many intermediate levels exist in different branches.
This example shows a ragged and skipped-level hierarchy where the Distributor and Store levels are missing from the Web branch of the hierarchy.
In a typical multi-channel sales organization, traditional retail channels flow through distributors who sell to stores, which then sell to customers.
However, web-based sales skip these intermediate levels entirely, going directly from the company to the customer. This creates a ragged structure where the web branch is shallower than the retail branch.
By configuring this as a ragged hierarchy, reports can correctly aggregate sales across all channels while still allowing users to drill down through the distributor and store levels for the retail channel specifically.
Here, you select both Ragged and Skipped Levels in the properties of the hierarchy.
This combination provides maximum flexibility for handling real-world dimensional hierarchies that don’t fit neatly into uniform hierarchical patterns.
When both options are enabled, Oracle Analytics understands that the dimensional hierarchy has both variable depth (ragged) and that some members may not have values at certain levels (skipped).
This ensures robust behavior across all analytical scenarios. This configuration is essential for accurately representing complex business structures and delivering reliable analytics regardless of the variations in your dimensional data.
Best Practices for Implementing Dimensional Hierarchies
When designing dimensional hierarchies in Oracle Analytics, always start with understanding your business users’ analytical requirements.
Map out how they naturally think about drilling through data. Create dimensional hierarchies that match these mental models for intuitive navigation.
Test your dimensional hierarchies thoroughly before deployment. Verify that aggregations calculate correctly at each level and that drill-down paths work as expected.
Document your dimensional hierarchies structure clearly. Include business definitions for each level and explain any exceptions or special handling for skipped or ragged levels.
This documentation becomes invaluable for ongoing maintenance and training new team members.
Conclusion: Mastering Dimensional Hierarchies
Understanding and properly implementing dimensional hierarchies is fundamental to creating effective Oracle Analytics solutions.
Whether you’re working with balanced, parent-child, skipped-level, or ragged dimensional hierarchies, each type serves specific business requirements and data structures.
By following the examples and best practices outlined in this guide, you can design dimensional hierarchies that optimize query performance, enable intuitive data exploration, and deliver accurate analytical insights.
Remember that dimensional hierarchies are not just technical constructs—they’re representations of how your business organizes and thinks about data.
Invest time in designing dimensional hierarchies that truly reflect your organizational structure and analytical needs, and you’ll create analytics solutions that users find natural and powerful.
