ABAP CDS Views Series — Part 9: Hierarchies, Recursive Structures, and Tree-Based Data Modeling in SAP S/4HANA
If you’ve been following this series, you already know how powerful CDS views can be for data modeling in SAP S/4HANA. We’ve covered everything from associations and joins to performance optimization and buffering strategies. But there’s one topic that consistently trips up even experienced ABAP developers: hierarchical data modeling using CDS views. Today, we’re going deep into recursive structures, hierarchy annotations, and how to model tree-based data properly in S/4HANA.
Whether you’re dealing with cost center hierarchies, bill of materials (BOM) structures, organizational hierarchies, or product category trees, understanding how SAP’s CDS hierarchy concept works will save you hours of painful workarounds and give your Fiori apps a serious analytical edge.
Let’s dive in.
Why Hierarchical Data Is Notoriously Tricky in SAP
Let me be honest with you: hierarchies in SAP have historically been a mess. Between SET/GET parameters, SETLEAF/SETNODE logic in classic ABAP, and the painful JOIN gymnastics needed to traverse parent-child relationships, many developers simply gave up and flattened everything into a table — paying the performance price later.
With SAP HANA as the underlying database engine and CDS as the modeling layer, that era is thankfully behind us. The HANA engine natively supports recursive SQL and hierarchical functions. CDS exposes these capabilities through a dedicated DEFINE HIERARCHY syntax that is both elegant and surprisingly powerful when used correctly.
The challenge? Documentation is sparse, examples are often oversimplified, and the interaction between CDS hierarchy views and Fiori/OData consumption has some sharp edges. That’s exactly why we’re here.
Understanding the CDS Hierarchy Building Blocks
Before writing a single line of code, let’s align on the three core concepts you need to master.
1. The Source View (Parent-Child Association)
Every hierarchy starts with a flat, relational source — a CDS view or table that contains a node key and a reference to its parent. Think of the classic KOSTL (Cost Center) to KOKHIER (Cost Center Hierarchy) relationship, or a custom product category table where each category row holds a PARENT_ID.
2. The Hierarchy View (DEFINE HIERARCHY)
This is a special CDS entity type — not a regular view, not an association. It tells the HANA engine: “treat this as a recursive traversal.” You define the parent and child keys, configure the traversal direction, and optionally set cycle handling.
3. The Consumption Layer
You don’t query a hierarchy view directly in ABAP Open SQL in the traditional sense. Hierarchy views are consumed via the HIERARCHY function in ABAP SQL, or exposed through OData using specific VDM annotations for Fiori tree tables and drilldown scenarios.
Building Your First CDS Hierarchy: A Practical Example
Let’s walk through a realistic example — modeling a product category hierarchy. Imagine an e-commerce or retail SAP implementation where categories nest: Electronics → Mobile Phones → Smartphones → Android Phones.
Step 1: Define the Source Data View
Start with the base CDS view that maps to your category table. In real S/4HANA projects, this often wraps a custom Z-table or an SAP standard object.
@AbapCatalog.sqlViewName: 'ZPROD_CAT_BASE'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Product Category Base View'
define view ZCDS_PRODUCT_CATEGORY_BASE
as select from zprod_category as pc
{
key pc.category_id as CategoryId,
pc.category_name as CategoryName,
pc.parent_id as ParentId,
pc.level as HierarchyLevel,
pc.sort_order as SortOrder,
pc.is_active as IsActive
}
where pc.is_active = 'X'
What’s happening here: This is a straightforward projection view. The critical columns are CategoryId (the node key) and ParentId (the reference to the parent node). Root nodes will have ParentId as null or a designated root marker value — decide this consistently in your data model.
Step 2: Define the CDS Hierarchy View
Now comes the interesting part. The DEFINE HIERARCHY keyword creates a special entity that encapsulates the recursive traversal logic.
@EndUserText.label: 'Product Category Hierarchy Definition'
define hierarchy ZCDS_PROD_CAT_HIERARCHY
as parent child hierarchy(
source ZCDS_PRODUCT_CATEGORY_BASE
child to parent association _ParentCategory
start where ParentId is initial
siblings order by SortOrder ascending
)
{
CategoryId,
CategoryName,
ParentId,
HierarchyLevel,
SortOrder,
$node.node_id as NodeId,
$node.parent_id as NodeParentId,
$node.hierarchy_level as NodeLevel,
$node.is_root as IsRoot,
$node.is_leaf as IsLeaf
}
Key annotations explained:
parent child hierarchy: Declares the traversal type. SAP CDS supports both parent-child and leveled hierarchy types.
child to parent association: References the association defined on the source view that links a node to its parent.
start where ParentId is initial: Defines the root condition. Nodes with no parent are traversal roots.
$node.hierarchy_level: A virtual node attribute computed by the engine — no need to maintain this manually.
$node.is_leaf: Automatically set to true for nodes with no children. This is gold for Fiori tree table rendering.
Important: You need to define the _ParentCategory association on the source view. Let me show you how to add that properly.
@AbapCatalog.sqlViewName: 'ZPROD_CAT_BASE'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Product Category Base View with Association'
define view ZCDS_PRODUCT_CATEGORY_BASE
as select from zprod_category as pc
association [0..1] to ZCDS_PRODUCT_CATEGORY_BASE as _ParentCategory
on $projection.ParentId = _ParentCategory.CategoryId
{
key pc.category_id as CategoryId,
pc.category_name as CategoryName,
pc.parent_id as ParentId,
pc.level as HierarchyLevel,
pc.sort_order as SortOrder,
pc.is_active as IsActive,
_ParentCategory -- Association to self
}
where pc.is_active = 'X'
Notice the self-referencing association — the view joins back to itself. This is the cornerstone of any parent-child hierarchy in CDS and what enables the recursive traversal at the database level.
Consuming a Hierarchy View in ABAP Open SQL
Here’s where many developers get confused. You don’t SELECT FROM a hierarchy view like a regular CDS view. Instead, you use the HIERARCHY function as a data source in your SQL statement.
CLASS zcl_product_category_reader DEFINITION
PUBLIC FINAL CREATE PUBLIC.
PUBLIC SECTION.
TYPES:
BEGIN OF ty_category_node,
category_id TYPE zprod_category-category_id,
category_name TYPE zprod_category-category_name,
parent_id TYPE zprod_category-parent_id,
node_level TYPE i,
is_leaf TYPE abap_bool,
is_root TYPE abap_bool,
END OF ty_category_node,
tt_category_nodes TYPE STANDARD TABLE OF ty_category_node
WITH EMPTY KEY.
METHODS:
get_full_hierarchy
RETURNING VALUE(rt_nodes) TYPE tt_category_nodes,
get_subtree
IMPORTING iv_root_id TYPE zprod_category-category_id
RETURNING VALUE(rt_nodes) TYPE tt_category_nodes.
ENDCLASS.
CLASS zcl_product_category_reader IMPLEMENTATION.
METHOD get_full_hierarchy.
"""
Traverse the entire product category hierarchy.
The HIERARCHY function returns all nodes with
computed depth and leaf/root indicators.
"""
SELECT
CategoryId,
CategoryName,
ParentId,
NodeLevel,
IsLeaf,
IsRoot
FROM HIERARCHY(
SOURCE ZCDS_PRODUCT_CATEGORY_BASE
CHILD TO PARENT ASSOCIATION _ParentCategory
START WHERE ParentId IS NULL
SIBLINGS ORDER BY SortOrder ASCENDING
)
ORDER BY NodeLevel ASCENDING, SortOrder ASCENDING
INTO TABLE @rt_nodes.
IF sy-subrc <> 0.
" Handle empty result - this is a valid state
" Log it if needed, but don't raise an exception
RETURN.
ENDIF.
ENDMETHOD.
METHOD get_subtree.
"""
Retrieve only the subtree below a specific root node.
Useful for lazy loading in Fiori tree tables.
"""
SELECT
CategoryId,
CategoryName,
ParentId,
NodeLevel,
IsLeaf,
IsRoot
FROM HIERARCHY(
SOURCE ZCDS_PRODUCT_CATEGORY_BASE
CHILD TO PARENT ASSOCIATION _ParentCategory
START WHERE CategoryId = @iv_root_id
SIBLINGS ORDER BY SortOrder ASCENDING
)
ORDER BY NodeLevel ASCENDING
INTO TABLE @rt_nodes.
ENDMETHOD.
ENDCLASS.
This is clean, readable, and — critically — pushes the recursion down to HANA. No ABAP-side looping, no recursive method calls, no buffer-busting SELECT loops. The entire hierarchy traversal happens at the database layer where it belongs.
Hierarchy Annotations for Fiori and OData Exposure
If your goal is to expose a hierarchy through OData for a Fiori tree table or analytical drill-down, you need to add specific annotations to your consumption view. This bridges directly into what we covered in Part 7 on consumption views and OData exposure.
@OData.publish: true
@Analytics.query: true
@Hierarchy.parentChild: [
{
name: 'ProductCategoryHierarchy',
source: 'ZCDS_PROD_CAT_HIERARCHY',
parentNodeField: 'ParentId',
childNodeField: 'CategoryId',
levelField: 'NodeLevel',
isLeafField: 'IsLeaf',
drillDownState: #EXPANDED
}
]
@EndUserText.label: 'Product Category Hierarchy - Consumption'
define view ZCDS_PRODCAT_HIER_CONSUMPTION
as select from ZCDS_PROD_CAT_HIERARCHY
{
CategoryId,
CategoryName,
ParentId,
NodeLevel,
IsLeaf,
IsRoot,
SortOrder
}
The @Hierarchy.parentChild annotation is what tells the OData framework to render this as a hierarchical dataset rather than a flat list. Combined with @Analytics.query: true, this enables tree tables in SAP Analytics Cloud and Fiori Elements with almost zero additional UI configuration.
Handling Real-World Complexity: Cycles, Orphans, and Missing Parents
In theory, your hierarchy is clean and consistent. In practice — especially when migrating legacy data — you’ll encounter cycles (A is parent of B, B is parent of A), orphan nodes (ParentId points to a non-existent node), and inconsistent depth levels.
Cycle Detection and Handling
CDS hierarchy views support cycle detection out of the box. Use the WITH CYCLE clause to handle cycles gracefully instead of letting the query run into infinite recursion:
define hierarchy ZCDS_PROD_CAT_HIERARCHY
as parent child hierarchy(
source ZCDS_PRODUCT_CATEGORY_BASE
child to parent association _ParentCategory
start where ParentId is initial
siblings order by SortOrder ascending
with cycle node $node.is_cycle as IsCycle -- Flag cyclic nodes
)
{
CategoryId,
CategoryName,
ParentId,
$node.node_id as NodeId,
$node.hierarchy_level as NodeLevel,
$node.is_root as IsRoot,
$node.is_leaf as IsLeaf,
IsCycle -- Include cycle indicator in output
}
With WITH CYCLE, the engine will traverse until it detects a revisited node, flag it, and stop — rather than crashing. You can then filter cyclic nodes in your consumer layer and trigger a data quality alert.
Orphan Node Strategy
My recommendation here is pragmatic: handle orphans at the data layer with a dedicated data quality CDS view that runs as a scheduled check, not by making your hierarchy view overly defensive. Keep the hierarchy clean by design; validate on inbound data entry. This aligns with the clean code principles we covered in our clean code refactoring guide.
Performance Considerations for Hierarchy Queries
A few hard-won lessons from production systems:
Never traverse full hierarchies on every request. Cache the flattened structure in a buffer if it changes infrequently. Cost center hierarchies rarely change daily — don’t pretend they do.
Use subtree queries for UI-driven scenarios. The get_subtree pattern above is your friend for lazy-loaded tree tables. Only load what the user expands.
Index your parent key columns. Sounds obvious, but I’ve seen production systems with multi-million row hierarchy tables and no index on PARENT_ID. The HANA engine’s recursive traversal benefits enormously from proper indexing.
Limit hierarchy depth in abnormal cases. Use the WHERE $node.hierarchy_level <= 10 guard clause on systems where data quality is uncertain. Runaway recursion on corrupted data is a real outage risk.
For a deeper dive into query tuning strategies, revisit Part 8 of this series on performance optimization and buffering — the techniques there apply directly to hierarchy consumption views as well.
A Note on Leveled vs. Parent-Child Hierarchies
Everything above covers parent-child hierarchies, which are the most flexible and common type. SAP CDS also supports leveled hierarchies — structures where the hierarchy is fixed and defined by a set of levels rather than a recursive parent reference. Think: Region → Country → State → City.
Leveled hierarchies are simpler to model and perform slightly better because there’s no recursion — each level is a separate join. However, they’re rigid. If your business needs to add an intermediate level (e.g., Sub-Region between Region and Country), you have a schema change on your hands.
My rule of thumb: Use leveled hierarchies for stable, well-defined dimensions (geography, time, fixed org structures). Use parent-child for dynamic, user-maintained hierarchies where depth is variable (product categories, custom org structures, BOM-like relationships).
Key Takeaways
CDS hierarchy views push recursive traversal to the HANA layer — stop doing recursion in ABAP application code.
A self-referencing association on the source view is the foundation of every parent-child CDS hierarchy.
The $node virtual attributes (hierarchy_level, is_leaf, is_root) are automatically computed — use them freely.
The WITH CYCLE clause is non-negotiable for any hierarchy that accepts user-maintained data.
For Fiori exposure, the @Hierarchy.parentChild annotation is the bridge between your CDS hierarchy and the UI tree table component.
Cache aggressively for hierarchies that change infrequently — full traversals on every page load are a red flag.
What’s Next in the Series?
In Part 10, we’ll tackle one of the most requested topics from readers: CDS-based analytical queries, cube views, and dimension views — the backbone of embedded analytics in S/4HANA. If you’ve wondered how to replace custom BW extractors with CDS-native analytical models, that’s exactly where we’re headed.
If you found this article useful, drop a comment below — especially if you’ve hit any specific hierarchy modeling challenges I haven’t covered here. I read every comment, and real-world questions often shape the direction of future articles. And if you’re sharing this with your team, the next time someone asks “how do I model a tree structure in CDS,” you’ll know exactly where to send them.
