Introduction
Hierarchies are a popular method of structuring application data to facilitate its consumption.
In CDS Views: Core Data Services, hierarchies provide access to data that is hierarchically linked.
If your company is divided into various legal entities and/or sub-units, you may wish to perform complex queries based on a given element structure. For example, an employee/manager model would be appropriate for a query involving the organisational structure of your company.
Different examples of hierarchies in a company :
- Time hierarchies : Year -> Month -> Week -> Day
- Geographical hierarchies : Continent -> Country -> Department -> City
- Account hierarchies such as P&L : Account group -> Cost element -> Account
- Item hierarchies : Item type -> Item group -> Item
- …
The functioning of hierarchies
Principle
In general, a CDS view generating hierarchies will be a DIMENSION view.
Several hierarchy components can be distinguished :
- Hierarchy node : this is a component associating the desired data with a hierarchy and a hierarchy level (root node(s), and X → Y level nodes)
- Hierarchy directory : this is the hierarchy itself. A hierarchy can have several root nodes.
- Hierarchy Node Texts : contains the names of the master data associated with the hierarchy nodes, if any (e.g. name/label of a general account.)
The hierarchical relationship of the nodes is constructed via two methods :
- By two sets of columns, one identifying a node as a child, often the key columns of the node, and the other identifying the associated parent node
- By an auto-association of the view with cardinality [1: 0..1] pointing to the parent node of the node
Root nodes do not have parents by definition. Below, on the database side, the first nodes of a hierarchy (level 0 corresponds to the root node) :
Subscribe to the Rapid Views Newsletter !
Stay up to date with our latest blog posts, upcoming webinars and news!
Decomposition of a hierarchy view
Here we use SAP CDS annotations. In order to declare a parent-child hierarchy, we use the following annotation :
@Hierarchy.parentChild:
{ recurse: { parent: 'ParentNode', child: 'HierarchyNode' },
siblingsOrder: { by: 'SequenceNumber', direction: 'ASC' },
directory: '_Hierarchy' }
We then declare the processing of nodes of hierarchies :
@ObjectModel: {usageType: {
dataClass: #MASTER,
serviceQuality: #A,
sizeCategory: #XL},
dataCategory: #HIERARCHY} Finally, we use associations to link our hierarchy nodes to a hierarchy directory, as well as to labels describing the master data returned by these hierarchy nodes :
association [1..1] to I_CostCenterHierarchy as _Hierarchy
on $projection.CostCenterHierarchy = _Hierarchy.CostCenterHierarchy
and $projection.ControllingArea = _Hierarchy.ControllingArea
and $projection.ValidityEndDate = _Hierarchy.ValidityEndDate
association [0..*] to I_CostCenterHierarchyNodeT as _Text
on $projection.CostCenterHierarchy = _Text.CostCenterHierarchy
and $projection.HierarchyNode = _Text.HierarchyNode
and $projection.ControllingArea = _Text.ControllingArea
The tree of hierarchy nodes can be represented as follows :
Our opinion : although a strong technical competence is required to implement this type of component, a number of standard hierarchy CDS are made available by SAP, which can be used for standard requirements.
Consumption of hierarchies
Data exposure via CDS views is done via a last level query view.
Below is an example of a view tree for an income statement query using several dimensions including 2 hierarchies (cost centres and general accounts).
It is necessary in a last level view to re-report hierarchies, directly associated with a master data element, for example for a cost centre identifier :
@EndUserText.label: 'Cost Center Key'
@AnalyticsDetails.query.variableSequence: 86
@Consumption.filter: { selectionType: #HIERARCHY_NODE, multipleSelections: true, mandatory: false,
hierarchyBinding : [ { type: #USER_INPUT, value: 'CostCenterHierarchy', variableSequence: 85 } ] }
@AnalyticsDetails.query.displayHierarchy: #FILTER
@AnalyticsDetails.query.axis: #ROWS
@AnalyticsDetails.query.totals: #SHOW
@ObjectModel.text.element:['CostCenterName']
CostCenterKey
Once this query is consumed via a compatible reporting tool such as Web Intelligence, Analysis for Office, but also SAP FIORI, one can access the tree structure extracted from the hierarchy, as well as the associated fold/unfold function.
For example, in Web Intelligence :
Our opinion : This functionality is indispensable in reporting and BI tools. It allows you to take advantage of the drill-down/roll-up function, and allows you to quickly perform totals and subtotals calculations spread over all levels of the hierarchy. With these hierarchies you can organise reporting in accordance with the hierarchical organisations of your company.
Expert's conclusion
Based on the CDS Views : Core Data Services technology, this hierarchy functionality allows you to find your company structure in your BI reports on the classic tools of the SAP ecosystem (Analysis For Office, SAP Business Objects, …), but also on the new CDS compatible tools such as SAP FIORI.
In addition to the highly technical implementation of the hierarchies, a standard repository is delivered by SAP, on several business domains, which makes it possible to start with the standard. Additional developments are to be expected if you wish to integrate your company’s specific requirements.
It should be noted that it is also possible to reuse in the CDS Views all the hierarchies customised and parameterised in the SAP ERP.
This is a concrete advantage for functional users, who will thus find the same structure in their reporting as in the restitutions, screens and extractions of the ERP. Moreover, in the reporting tools, these hierarchies can be combined with more advanced functionalities of the reporting tools.
