🌲Hierarchy

NEW

Also known as: Tree Structure

A hierarchy is an ordered parent–child structure organizing items into levels for aggregation, navigation, and access control.

DESCRIPTION

Hierarchy organizes concepts or entities into layered structures, improving navigation, inheritance, and summarization. In data contexts, hierarchies enable roll-ups and drill-downs for analytics and classification. They underpin taxonomies, file systems, organizational charts, and dimensional models. Hierarchies may be strict (monohierarchy) or allow multiple parents (polyhierarchy). SKOS encodes hierarchical relations via skos:broader and skos:narrower. Good governance defines clear levels, labels, and maintenance processes to ensure consistency and usability.

ALTERNATIVE DEFINITIONS

Wikipedia

A hierarchy is an arrangement of items in which the items are represented as being “above”, “below”, or at the same level as one another.

Stresses ordering relationships across levels independent of data system context.

Source

Oracle

A hierarchy is a logical structure that uses ordered levels as a means of organizing data.

Connects hierarchies to analytical roll-up and drill-down within OLAP.

Source

RELATED TERMS

Class Hierarchy Recursive Relationships Parent-Child Relationship Adjacency List Nested Set Model Closure Table Ragged Hierarchy OLAP Kimball Methodology Graph Monohierarchy Polyhierarchy Knowledge Representation Simple Knowledge Organization System Hierarchical Relationship Parent-Child Thesaurus Value Set

COMPETING TERMS

Heterarchy Network

RELATIONS

• Narrower: Class Hierarchy Is-a Hierarchy Part-of Hierarchy Subsumption Hierarchy

KNOWLEDGE AREAS

Semantic & Knowledge Modeling Data Modeling Data Warehousing & BI Metadata Management Data Architecture

TARGET AUDIENCE

Data Modeler Data Engineer BI Specialist Data Architect Information Analyst Data Steward Enterprise Architect Data Governance Manager Data Management Consultant

EXTERNAL SOURCES

• Wikipedia - Hierarchy
• Microsoft - Recursive CTEs for hierarchies
• Oracle - Hierarchical queries in SQL
• Microsoft - Hierarchies in BI modeling

TOP QUESTIONS

Why are Hierarchies essential in BI and analytics?

They enable drill-down and roll-up across levels like product and geography, simplifying aggregation and consistent KPI reporting.

Category: Relevancy

What is a ragged hierarchy in data modeling?

A ragged hierarchy has uneven depths across branches, requiring special handling for roll-ups and navigation.

Category: Definition

How do I store Hierarchies in a relational database?

Use adjacency lists, nested sets, or closure tables; choose based on read/write patterns and query complexity.

Category: Implementation

What is a common mistake when modeling Hierarchies?

Assuming balanced trees and ignoring ragged levels leads to broken roll-ups and incorrect totals.

Category: Mistakes

How do Hierarchies relate to dimensions in a star schema?

Dimension tables often include hierarchical attributes enabling level-based aggregations in OLAP cubes or BI tools.

Category: Context

When should I use a closure table for Hierarchies?

When querying transitive ancestors/descendants frequently, closure tables speed lookups at the cost of write complexity.

Category: Guidance

What’s the risk of cycles in Hierarchies?

Cycles break parent–child semantics, leading to infinite traversals and incorrect aggregations; enforce constraints to prevent them.

Category: Risks

How do Hierarchies map to graph databases?

They are trees in graph terms; graph databases handle traversal efficiently and support richer relationships when needed.

Category: Comparison

How to validate Hierarchies during ETL?

Check for orphan nodes, cycles, and level consistency; enforce referential integrity and use unit tests for paths.

Category: Testing