Business value
In 2023, SAP introduced a new feature called hierarchy with directory. A hierarchy with directory is an entity that contains one or more parent-child hierarchies and has an association to a directory dimension containing a list of the hierarchies. These types of hierarchy entities can include nodes from multiple dimensions (for example, country, cost center group, and cost center) and are commonly imported from SAP S/4HANA Cloud and SAP BW systems.
In this blog, I will provide a very simple company code hierarchy example and what is required to create one in Datasphere.
Required Hierarchy Elements
When building a hierarchy with directory, a few components are required to complete the simple below flow.
Example ER Diagram
Required Tables/Views
- Transactional Data
- Fact data
- Data
- FI, Sales, etc. with KPIs and attributes associating to master data
- Association to Company Code
- Semantic Type - Fact
- Master Data (Company Code)
- This is the attribute data for the respective company code master data
- Data
- The attribute data for company code
- Association to Company Code Hierarchy
- Semantic Type - Dimension
- Hierarchy w/ Directory (Company Code Hierarchy)
- Defines the multiple parent-child hierarchies and nodes from multiple dimensions
- Data
- HierarchyID (Key)
- ChildID (Key)
- ParentID
- Nodetype
- TextnodeID
- The attribute for mapping to Company Code (dimension)
- Semantic Type - Hierarchy w/ Directory
- Hierarchy Directory
- Defines and stores all the hierarchy IDs and allows for time-dependency
- Data
- HierarchyID (Key)
- Validto (Key)
- Validfrom
- Semantic Type - Dimension
- Hierarchy Directory Texts
- Defines the texts for the hierarchy name. This is required or a label error will occur upon activation of the hierarchy with directory.
- Data
- HierarchyID (Key)
- Language (Key)
- Hierarchy Description
- Semantic Type - Text
Walkthrough
In this section, I will explain what the Hierarchy w/ directory structure should look like and how to define the settings.
Company Code Hierarchy - Hierarchy w/ Directory
When defining a Company Code Hierarchy w/ Directory, it requires some further understanding and following of some rules
- It requires the key to be a unique ID for each hierarchy (in the example below, this is 1)
- It requires the other key to be CHILDID, this allows multiple hierarchies to exist having the same CHILDID
- NODETYPE is what will be defined as part of the hierarchy. This is required and this is what will allow us to define the Hierarchy with Directory Settings.
- The first visible Node Type in NODETYPE column is TextNode. This is defined as the text node for the hierarchy, this will be mapped to the TEXTNODEID column in this case. You'd see values like SAP US and SAP EMEA and these will not be leaf nodes.
- The next visible Node Type in that column is CompanyName. In this examples, it will have the associated company code values (like 2085, 2086) in the below example. This will be mapped to the COMPCODEID column in this case. This will be associated to the dimension Company Code but also will be the leaves in the hierarchy.
- Note, that the values are NULL for COMPCODEID column when it is a NODETYPE TextNode and vice versa. This is expected as COMPCODEID is not a text node.
- If required, more nodes can be added and additional columns per node & new nodes is allowed
Hierarchy w/ Directory Dataset
Now that the data that is needed for the Hierarchy is acquired, the settings need to be configured.
Prior to doing this configuration
- An association in the Hierarchy w/ Directory needs to be made to the HierarchyDirectory on the unique HierarchyID identified.
- HierarchyDirectory text association should be created to HierarchyDirectoryText
Multiple Hierarchy Settings Configuration
- Parent - Maps to ParentID
- Child - Maps to ChildID
- Hierarchy Name Column - Maps to the unique hierarchyID column
- Hierarchy Directory Entity - The associated HierarchyDirectory after you associate the dimension.
- Node Type Column - Maps to the NODETYPE column in the example
- Node Type Values - Node type values will be definable and will be manually mapped to the values inserted into the NODETYPE column. From each node you will define:
- Node Type Value
- Set as Leaf - Define check or unchecked
- Columns per node type
Hierarchy with Directory Settings
Once this is complete, associate the cost center dimension to the fact artifact. Then, create an analytic model based on the fact.
Previewing the Hierarchy
Analytic Model
Analytic Model Preview
When previewing in the Analytic Model and adding Company Code, you'll first see the ID displayed with no hierarchy selected.
To select a hierarchy, click the three dots next to company code -> Hierarchy -> select hierarchy.
Once selected it will prompt you to select the appropriate hierarchy that's been defined. If other languages are defined in the HierarchyDirectoryText table, depending on the configured language for your user, you can see the appropriate Hierarchy name in the respective language. In this case i am displaying in english and french which i enabled by changed my data access language to French and adding the language to the Hierarchy Directory Text table.
After selecting, if configured properly, a hierarchy will be displayed in the results showing the ID values.
To change the ID values to description or ID and Description or vise versa, select the 3 dots next to company code -> presentations and then the appropriate selection.
Summary
Hierarchies with directories created with now should be viewable in Analytic Models and from SAP Analytics Cloud.
Please like, comment, share or post a question!
Helpful Links
SAP Help - Creating a Hierarchy with Directory - Link
- SAP Managed Tags:
6 Comments
