Hierarchies in Datasphere

What you will learn

• Choose between parent-child and level-based hierarchy design for a given business dimension
• Model a Type-2 SCD hierarchy that survives org reorgs without breaking historical reports
• Define hierarchies once on the analytic model so SAC, Joule, and Excel consumers stay in sync
• Position hierarchy design as a rate-relevant skill in a client conversation

Module overview

Hierarchies = the structure that turns flat dim tables into "drill from Group → Region → Country → Cost Center" navigation. Without proper hierarchies, every SAC story rebuilds them ad-hoc; with them, every story inherits Group standard.

Two flavours.

1. Parent-child hierarchies — variable depth (e.g. cost center hierarchy with 3-12 levels). Modeled as recursive structure with parent id pointing within the same table.
2. Level-based hierarchies — fixed depth (e.g. fiscal year > quarter > month). Modeled with explicit level columns.

Senior pattern. Define hierarchies on the analytic model, not on the view. The analytic model is the trust layer; SAC stories, Joule skills, and Excel pivots all read it. Define ONCE; consume everywhere.

Time hierarchies are the ones every engagement uses. Build the V_DIM_CALENDAR in the foundation space with: fiscal year, fiscal quarter, fiscal month, fiscal week, calendar year, calendar month, calendar quarter, period-to-date flags. Reuse across all domains.

Cost center hierarchies drift constantly (reorgs). Build a Type-2 SCD pattern: keep history, query at as-of-date. Junior consultants overwrite; the next reorg breaks every historical report.

Prerequisites

• Review core concepts first: C008, C006, C005

Outcomes

• Work through a realistic scenario: Industrial group, 12 entities, fiscal year != calendar year, cost center hierarchy 8-level, monthly reorgs.
• Recognize and avoid the anti-pattern: Type 1 SCD on org dim — Reorg breaks all historical reports.
• Apply the module's core decision: Parent-child vs level-based — choose Parent-child for org/cost center; level-based for time/product, not Forcing level-based on variable depth (lossy).
• Track mastery with the KPI: Hierarchy reuse (target: 1 definition per dim, ≥ 3 consumers; red flag: Per-consumer redefinition).