Hierarchy Capabilities

Hierarchies provide the structural model that Casabase Cube uses to organize dimension members, navigate relationships, aggregate values, resolve security scope, and support hierarchy-aware formulas.

Casabase Cube builds dimension structures from source hierarchy metadata, typically represented as parent-child relationships in Snowflake tables.

Fact data is not duplicated into every hierarchy level. Instead, the hierarchy structure is used at query time to derive parent values dynamically from stored leaf-level data.

Hierarchy Structure

A typical hierarchy might look like:

Total Entity
├── North America
│   ├── United States
│   │   ├── US East
│   │   └── US West
│   └── Canada
└── EMEA
    ├── United Kingdom
    └── Germany

The structure defines relationships such as:

• Parent
• Child
• Ancestor
• Descendant
• Sibling
• Generation
• Level
• Leaf status

These relationships can then be used throughout the application for querying, formulas, security, and navigation.

Dimensions Are Built

Dimension metadata is processed into application-managed hierarchy structures.

Conceptually:

Parent-Child Source Table
          │
          ▼
     Dimension Build
          │
          ▼
Built Hierarchy Structure
          │
          ▼
Query / Formula / Security

The built structure allows Casabase Cube to resolve hierarchy relationships efficiently when queries execute.

A change to hierarchy metadata requires the affected dimension to be rebuilt.

A change to fact data does not require a dimension rebuild.

Source Data vs. Built Hierarchy

The source hierarchy definition and the built dimension serve different purposes.

Source Table
    │
    │ Defines relationships
    ▼
Built Dimension
    │
    │ Provides query-time structure
    ▼
Casabase Cube Engine

The source table remains customer-owned.

The built dimension is managed internally by Casabase Cube and should be accessed through supported application interfaces rather than through internal storage objects.

See Data Storage & Boundaries.

Dynamic Parent Aggregation

Parent members do not require permanently stored aggregate values.

Instead:

Leaf Data
   │
   ▼
Hierarchy Relationships
   │
   ▼
Consolidation Operators
   │
   ▼
Dynamic Parent Value

For example:

Total Expense
├── Salaries       100
├── Benefits        25
└── Travel          10

can produce:

Total Expense = 135

at query time.

Because aggregation is dynamic:

• no aggregate processing step is required
• new fact data is immediately reflected in parent values
• hierarchy depth does not multiply aggregate storage
• metadata changes require rebuilds, while fact-data changes do not

These are core properties of the Casabase Cube hierarchy model.

Consolidation Operators

Each hierarchy member can define how it contributes to its parent.

The architecture supports consolidation behavior such as:

Add
Subtract
Exclude

This allows a hierarchy to represent business structures that are more sophisticated than a simple sum.

For example:

Net Revenue
├── Gross Revenue      +
└── Returns            -

or:

Total Company
├── Operating Units    +
└── Eliminations       -

The hierarchy itself therefore participates in the business calculation model.

Member consolidation operators provide the mechanism for expressing addition, subtraction, exclusion, netting, and elimination structures.

Hierarchy Navigation

The query engine can resolve members relative to their position in a hierarchy.

For example, a POV can request a branch rather than enumerating every member individually.

Conceptually:

Request:
children of Q1
      │
      ▼
Hierarchy Resolution
      │
      ▼
Jan
Feb
Mar

or:

Request:
descendants of North America
      │
      ▼
United States
US East
US West
Canada

This allows query definitions to follow the hierarchy rather than hardcoding all of its members.

For complete operator syntax, see Point of View (POV).

Inclusive and Exclusive Selections

Some hierarchy selections include the anchor member while others return only related members.

Conceptually:

descendants
    │
    └── members below anchor

versus:

idescendants
    │
    └── anchor + members below anchor

The distinction is useful when a report needs:

Detail only

or:

Subtotal + Detail

The exact selection syntax belongs to the Querying documentation rather than the Architecture section.

Generations and Levels

Casabase Cube maintains structural depth information for hierarchy members.

Two common ways of describing member position are:

Generation

and:

Level

Generation measures position relative to the top of the hierarchy.

Level measures position relative to the leaves.

For example:

Total Entity        Generation 1
└── North America   Generation 2
    └── US          Generation 3
        └── US West Generation 4

while leaf-oriented levels conceptually run in the opposite direction.

These structural properties are available to supported hierarchy-aware functions and dimension metadata interfaces.

Leaf Members

A leaf member has no children.

Leaf status matters because stored fact data is generally associated with stored multidimensional intersections, while parent values can be derived dynamically through hierarchy aggregation.

Conceptually:

Parent
├── Child
│   ├── Leaf
│   └── Leaf
└── Leaf

The hierarchy structure therefore allows the engine to distinguish:

Stored detail

from:

Derived aggregate

Hierarchy-Aware Formulas

Member formulas can inspect hierarchy relationships at query time.

For example, formulas can determine whether the current member is:

• A leaf
• A child of another member
• A descendant of an ancestor
• An ancestor of another member
• At a particular generation
• At a particular level

This allows formula behavior to adapt to the multidimensional structure rather than relying on hardcoded member lists.

The detailed function syntax belongs in Hierarchy-Aware Functions.

Shared and Alternate Members

Casabase Cube supports shared and alternate hierarchy relationships.

These allow the same underlying member to participate in more than one reporting structure without duplicating the underlying business data.

Conceptually:

Primary Hierarchy

Total Product
└── Electronics
    └── Product A


Alternate Hierarchy

Strategic Products
└── Product A

The hierarchy placements may differ, but the shared representation resolves to the same underlying member.

This is also important for security. Shared and alternate hierarchy members resolve to their base member when security scope is derived, so granting a shared member conveys the same underlying leaves as granting the original member.

Hierarchies and Security

Row-level security uses hierarchy relationships to determine permitted data.

A security grant on a member conveys:

Granted Member
      +
All Descendants

at their full values.

For example:

Grant:
North America

can provide access to:

North America
├── United States
│   ├── US East
│   └── US West
└── Canada

without requiring a separate rule for each descendant.

Ancestors Under Security

Security inheritance is not limited to hiding ungranted branches.

Members above the user’s grant can remain visible.

Their value is calculated only from the user’s permitted leaves.

For example:

Total Company
├── North America
└── EMEA

If the user has access only to North America, a query for Total Company can still return:

Total Company

but its value represents:

North America only

rather than the unrestricted company total.

This is a partial rollup.

Partial Rollups and Hierarchy Meaning

This has an important reporting consequence.

The same hierarchy member label can represent different numerical populations for different users.

Conceptually:

Unrestricted User

Total Company
    =
All Leaves

Restricted User

Total Company
    =
Permitted Leaves Only

Neither result is inherently incorrect.

They reflect different security scopes.

If a report requires a complete aggregate, the user’s grant should cover a hierarchy branch where that aggregate is complete rather than only a subset of its descendants.

See Security Model for the complete behavior.

Hierarchy Expansion and Security

A hierarchy operator can structurally request more members than the user is allowed to see.

For example:

{
  "ENTITY": [
    {"idescendants": "Total Entity"}
  ]
}

might structurally represent the entire dimension.

The engine then applies the user’s permitted scope.

Conceptually:

Hierarchy Expansion
       │
       ▼
Full Structural Member Set
       │
       ▼
Apply Security Scope
       │
       ▼
Permitted Hierarchy Result

Hierarchy selection does not bypass row-level security.

Hierarchy Metadata Through SQL

Casabase Cube exposes supported hierarchy metadata through:

<CUBE>_DIMENSIONS

The secure dimension view provides metadata that can be used by BI tools and custom applications.

Depending on the dimension, available metadata can include concepts such as:

• Member
• Parent
• Alias
• Generation
• Level
• Leaf status
• Sort order
• UDA
• Time-balance information

This allows consumers to build hierarchy-aware applications without depending on internal application tables.

Hierarchy Metadata vs. Calculation Engine

The secure dimension view exposes the hierarchy structure.

It does not itself perform multidimensional aggregation.

For example:

<CUBE>_DIMENSIONS

can tell an application:

Product A
  parent = Electronics

while:

QUERY_CUBE

uses that structure to calculate:

Electronics
  =
sum / consolidation of applicable descendants

This distinction allows consumers to choose between:

Use Casabase Cube hierarchy semantics

and:

Consume hierarchy metadata and model independently

Attribute Dimensions

Casabase Cube also supports attribute-based analysis.

Attributes associate member properties with a base dimension without requiring the main hierarchy to be restructured.

Conceptually:

Product
├── Product A
│   ├── Brand = Alpha
│   └── Category = Premium
└── Product B
    ├── Brand = Beta
    └── Category = Standard

An attribute can then be used to slice or filter the base dimension.

The architecture guide establishes that attributes are associated with base members and resolved at query time, allowing a query to filter on an attribute even when that attribute dimension is not itself present in the POV.

Attribute Filtering

Conceptually:

Base Dimension
      │
      ▼
Member Attributes
      │
      ▼
Query-Time Attribute Filter
      │
      ▼
Matching Base Members

This allows business analysis such as:

Products where Brand = Alpha

without creating a separate primary reporting hierarchy solely for that classification.

Time Hierarchies

Time dimensions participate in the same hierarchy architecture but can also carry time-specific metadata.

Time-balance settings can determine how members aggregate across periods.

For example:

Flow Measure
  └── Sum periods

Balance Measure
  └── Closing value

These settings are dimension metadata, not formula expressions, and therefore apply consistently when the engine processes queries.

Detailed time-intelligence behavior belongs in the relevant Configuration and Querying documentation.

Hierarchy Changes

Hierarchy metadata is different from fact data.

If a parent-child relationship changes:

Before

North America
└── Canada

to:

After

International
└── Canada

the affected dimension metadata must be rebuilt so Casabase Cube can reconstruct the hierarchy structure.

By contrast, loading new values for Canada does not require rebuilding the hierarchy.

The architecture is therefore:

Hierarchy Metadata Change
        │
        ▼
Dimension Rebuild Required

versus:

Fact Data Change
        │
        ▼
No Hierarchy Rebuild Required

This distinction follows directly from Casabase Cube’s dynamic aggregation model.

Hierarchy Depth and Scale

Because aggregates are not stored at every hierarchy level, adding hierarchy depth does not multiply aggregate storage.

Dimension depth generally scales well because dynamic aggregation avoids storing a separate aggregate layer for each hierarchy level.

This does not mean every possible query has unlimited cost.

Broad member expansions still increase result size and computation, particularly when combined with calculated members.

Queries should request the grain actually required by the consumer.

Hierarchies as a Shared Business Model

A hierarchy is more than a navigation tree.

It defines business structure used by multiple parts of Casabase Cube:

Hierarchy
   │
   ├── Query Navigation
   ├── Dynamic Aggregation
   ├── Consolidation
   ├── Security Inheritance
   ├── Formula Context
   ├── Time Intelligence
   └── BI Metadata

This shared model is what allows the same structural definition to be used consistently across querying, security, calculations, and integrations.

Key Principles

• Dimensions are built from hierarchy metadata.
• Hierarchy changes require a dimension rebuild.
• Fact-data changes do not require hierarchy rebuilds.
• Parent values are dynamically derived from leaf data.
• Consolidation operators determine how members contribute to parents.
• Query operators navigate hierarchy relationships dynamically.
• Generations and levels describe structural position.
• Shared and alternate members resolve to the same underlying business member.
• Security grants inherit through descendants.
• Ancestors can remain visible under security with partial rollups.
• Attribute dimensions provide query-time slicing without restructuring the base hierarchy.
• Secure dimension views expose supported hierarchy metadata to relational consumers.
• Hierarchy depth does not create stored aggregate copies at every level.

Related Topics

• High-Level Architecture
• Query Architecture
• Formula Capabilities
• Security Model
• Data Storage & Boundaries
• Point of View (POV)
• Hierarchy-Aware Functions
• Access Control