MDS hierarchies: what they were, why they mattered, and what replaces them

MDS hierarchies had a reputation for being confusing. The explicit hierarchy UI was limited. Derived hierarchies took real effort to configure and debug. Most of the MDS shops I've talked to relied on at least one: product category trees, cost center rollups, geographic reporting structures. Many never fully understood how derived hierarchies worked. They just knew touching them caused problems.

When MDS goes away, so do the hierarchy tools. What that means for your data model depends on which type you relied on.

What explicit hierarchies were

An explicit hierarchy in MDS was a manually maintained tree. You took entity members (product records, cost centers, suppliers) and arranged them into a parent-child structure independent of the underlying attribute data. The same set of 500 cost center records could appear in three different explicit hierarchies: one for financial reporting, one for the budget process, one for the org chart.

The practical limitation was maintenance overhead. Every time a department reorganized or a new product line launched, someone had to log into MDS and move nodes around. The hierarchy had no idea that a new entity record existed. It only knew what you told it.

What derived hierarchies were

Derived hierarchies were more interesting. Instead of manually arranging members in a tree, MDS computed the hierarchy from domain attribute relationships between entities.

The standard example: a Product entity has a Category domain attribute pointing to a Category entity. Category has a ParentCategory attribute pointing back to itself. From those two relationships, MDS derived a full product-category tree automatically. Change a product's category attribute and the hierarchy updated instantly. No manual tree maintenance.

This was the more powerful of the two types. Self-maintaining, driven by the data model rather than manual work. But it was hard to configure and harder to debug. When a member appeared in the wrong branch, tracing why could take hours.

What you're actually losing

Most organizations are not losing as much as they think. I've talked to teams who panicked about their hierarchy module, then realized three weeks later their reporting queries already worked against the raw parent-child data directly. The hierarchy UI was something nobody used.

Explicit hierarchies were a UI layer over parent-child data. The entity records with parent references still exist after migration. You lose the MDS screen for browsing and editing trees visually. You can query the same hierarchy with a recursive CTE, and many reporting setups already did exactly that, bypassing the MDS hierarchy UI entirely. The hierarchy module was a convenience, not a data structure.

Derived hierarchies are a different matter. The automatic computation from domain attribute relationships was a real feature. That step disappears with MDS. What replaces it depends on your new tool and how you structure the data model.

The parent-reference pattern

Whether or not your new MDM tool has a built-in hierarchy module, the foundation is a self-referencing domain attribute. If you have a Category entity and want a category tree, add a ParentCategory attribute that points back to the Category entity itself. Any category record can now reference another category as its parent. Records with no parent are root nodes. Unlimited depth from one attribute.

The same pattern covers product categories, org units, geographic hierarchies, cost center trees, asset classification. The data model is identical. Only the entity name changes.

A standard recursive CTE traverses the tree at any depth:

WITH CategoryTree AS (
    -- Anchor: root categories (no parent)
    SELECT
        category_id,
        category_name,
        parent_category_id,
        0 AS depth,
        CAST(category_name AS NVARCHAR(MAX)) AS path
    FROM Categories
    WHERE parent_category_id IS NULL

    UNION ALL

    -- Recursive: children of already-found nodes
    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.depth + 1,
        ct.path + N' > ' + c.category_name
    FROM Categories c
    INNER JOIN CategoryTree ct
        ON c.parent_category_id = ct.category_id
)
SELECT * FROM CategoryTree
ORDER BY depth, category_name;

This handles ragged hierarchies (branches at different depths) without any special configuration. Electronics at depth 1, Laptops at depth 2, Gaming Laptops at depth 3. Some branches stop at two levels. Others go four deep. The CTE handles both in the same query.

The mistake most teams make

When migrating from MDS, teams often try to flatten the hierarchy into fixed levels: Level1, Level2, Level3 as separate columns or separate tables.

I've seen this go wrong twice. Both times, the business was certain there would never be more than three category levels. There were four within six months. The data model needed a restructure, existing queries broke, and reports that assumed three fixed levels had to be rebuilt.

The self-referencing parent attribute handles variable depth. Add records at depth four and nothing in the data model changes. The recursive CTE picks them up.

If your BI tool requires a fixed-depth structure, that is a reporting transformation problem. Solve it in the BI layer. Keep the MDM data model clean and recursive.

What to ask MDM vendors about hierarchy support

When evaluating tools to replace MDS, ask these four questions:

Hierarchy changes and approval workflows

MDS hierarchy changes were uncontrolled edits. Anyone with edit permissions could move a node, and that change went live immediately with no review step.

That is a real governance gap. Reorganizing a cost center tree ripples into any report that rolls up to that structure. Moving a product to a different category changes how it shows up in pricing rules, catalog filters, and BI dimensions referencing it. These are not small changes.

When hierarchy changes go through the same approval workflow as attribute changes (propose, review, approve, commit), that gap closes. A data steward proposes the reorganization. A reviewer approves it. The change commits. Every step is audited. Ask whether the tools you evaluate treat hierarchy edits as governed changes or as freeform edits.

Frequently asked questions