Hierarchy Rollups in Master Data: A Practical Guide with SQL Examples

When data teams talk about “trusted reporting” or “a single source of truth,” what they often need is something simpler: a working hierarchy.

Hierarchy rollups are one of the most misunderstood and under-implemented features of master data management. Without them, reporting becomes fragmented, forecasting goes off track, and analytics lose context.

In this article, you’ll learn:

• What a hierarchy rollup is
• A real-world business example using a customer structure
• Common failure patterns
• A working SQL Server schema and queries to model it correctly

What Is a Hierarchy Rollup?

A hierarchy rollup is the process of aggregating data across structured relationships, typically parent-child relationships, defined in your master data.

Rollups help answer questions like:

• How much revenue came from all subsidiaries under a corporate parent?
• How many support tickets were opened by customers within a business unit?
• What is total cost rolled up across all levels of a product BOM?

Without rollups, these insights require manual effort, fragile joins, or duplicate reporting logic.

Real-World Example: The GlobalTech Hierarchy

Let’s say your company does business with GlobalTech Inc., a multinational corporation with this structure:

GlobalTech Inc. (Parent)
├── GlobalTech USA (Subsidiary)
│   ├── GlobalTech Billing East (Billing Entity)
│   └── GlobalTech Billing West (Billing Entity)
├── GlobalTech Europe (Subsidiary)
└── GlobalTech Asia (Subsidiary)

Each of these entities may exist independently in your systems. But when leadership asks, “What is our total revenue from GlobalTech?” you need to roll up data across that entire structure.

SQL Server Schema: Modeling the Hierarchy

We’ll create two tables:

1. Customer – for master data with a self-referencing hierarchy
2. CustomerRevenue – for fact data tied to individual customer records

Customer Table

CREATE TABLE Customer (
	CustomerID INT PRIMARY KEY,
	CustomerName NVARCHAR(100),
	ParentCustomerID INT NULL, -- Self-referencing FK
	CustomerType NVARCHAR(50), -- e.g. 'Parent', 'Subsidiary', 'Billing Entity'
	Region NVARCHAR(50),
	CONSTRAINT FK_Customer_Parent FOREIGN KEY (ParentCustomerID)
	REFERENCES Customer(CustomerID)
);

CustomerRevenue Table

CREATE TABLE CustomerRevenue (
	RevenueID INT PRIMARY KEY,
	CustomerID INT,
	RevenueAmount DECIMAL(18, 2),
	RevenueDate DATE,
	CONSTRAINT FK_CustomerRevenue_Customer FOREIGN KEY (CustomerID)
	REFERENCES Customer(CustomerID)
);

Sample Data

Customer Records

INSERT INTO Customer (
	CustomerID, CustomerName, ParentCustomerID, 
	CustomerType, Region
) 
VALUES
	(1, 'GlobalTech Inc.', NULL, 'Parent', 'Global'),
	(2, 'GlobalTech USA', 1, 'Subsidiary', 'North America'),
	(3, 'GlobalTech Europe', 1, 'Subsidiary', 'EMEA'),
	(4, 'GlobalTech Asia', 1, 'Subsidiary', 'APAC'),
	(5, 'GlobalTech Billing East', 2, 'Billing Entity', 'East US'),
	(6, 'GlobalTech Billing West', 2, 'Billing Entity', 'West US');

CustomerRevenue Records

Query: Rolling Up Revenue to the Root

We’ll use a recursive CTE to walk the customer hierarchy and roll up revenue to the top-level parent.

-- Recursive CTE to build a full customer hierarchy starting from top-level (root) customers
WITH CustomerHierarchy AS (
    SELECT
        CustomerID,                   -- Current customer ID
        ParentCustomerID,             -- Parent customer ID (NULL for root customers)
        CustomerName,                 -- Name of the customer
        CustomerID AS RootCustomerID  -- For root customers, the root is themselves
    FROM Customer
    WHERE ParentCustomerID IS NULL    -- Select only root-level customers

    UNION ALL

    SELECT
        c.CustomerID,                 -- Child customer ID
        c.ParentCustomerID,           -- Child's parent ID
        c.CustomerName,               -- Child customer name
        h.RootCustomerID              -- Pass down the original root customer ID
    FROM Customer c
    INNER JOIN CustomerHierarchy h 
        ON c.ParentCustomerID = h.CustomerID  -- Join child to parent in the hierarchy
)

-- Aggregate revenue by root customer across all levels of the hierarchy
SELECT
    r.RootCustomerID,                          -- Top-level (root) customer ID
    rc.CustomerName AS RootCustomerName,       -- Name of the root customer
    SUM(cr.RevenueAmount) AS TotalRevenue      -- Total revenue for all customers under the root
FROM CustomerHierarchy r
JOIN CustomerRevenue cr 
    ON r.CustomerID = cr.CustomerID            -- Join revenue to each customer in the hierarchy
JOIN Customer rc 
    ON r.RootCustomerID = rc.CustomerID        -- Join to get root customer's name
GROUP BY r.RootCustomerID, rc.CustomerName;    -- Group results by root customer

Output:

RootCustomerID	RootCustomerName	TotalRevenue
1	GlobalTech Inc.	5,600,000.00

This includes:

• $1.2M from Billing East
• $0.9M from Billing West
• $2.1M from Europe
• $1.4M from Asia

Common Hierarchy Issues and Fixes

Problem	Cause	Fix
Missing relationships	Incomplete or manual data	Model hierarchies explicitly in MDM
Duplicate entities	No survivorship rules	Match, merge, and assign hierarchy carefully
Rollups include wrong entities	Loosely defined structures	Use filtered rollups by role or type
Multiple hierarchy types (legal vs reporting)	One structure doesn’t fit all uses	Store multiple hierarchy types with separate metadata
Outdated hierarchy paths	Acquisitions, splits, restructuring	Track effective dates and versions for hierarchy records

Best Practices

• Use self-referencing relationships for flexible rollup modeling
• Include entity type and region to help classify and filter rollups
• Apply stewardship and governance to maintain hierarchy integrity
• Use recursive CTEs for elegant rollup queries in SQL Server
• Version your hierarchies if reporting requires historical views

Final Thought

Hierarchy rollups aren’t just a technical detail—they’re a core MDM capability that powers accurate reporting, trusted analytics, and organizational alignment.

If your revenue reports or customer metrics are constantly in question, start by asking:

“Do we have a working hierarchy?”