Creating Custom Hierarchies in BW 7.5
This article aims to provide background information on BW hierarchies and present a detailed outline of a technique that allows users to generate custom hierarchies in BW 7.5. This is not a step-by-step, how-to guide but an informative paper that gives the feel for what it takes for a consultant to implement this feature when the need for it arises. A simple example is loosely followed to put the implementation steps into context, but it is not the focal point of the article.
BW Hierarchies in a Nutshell:
When using Characteristics’ master data for planning and reporting, sometimes the expectation is to organize this data in a particular way in order to present a specific view over a query’s dataset. Characteristic hierarchies in BW allow one to do exactly that! They empower the user to change the semantics of any dataset by restructuring and redefining the context in which it is displayed. Technically, hierarchies are tree structures that use nodes and the relationships amongst them to arrange Characteristics’ master data (or a portion of it) in a flexible manner. There are 3 main types of hierarchy nodes in BW - Basic Characteristic, External Characteristic and Text[1]. Basic Characteristic nodes are nodes that contain master data values for the Characteristic the hierarchy is defined for (the basic Characteristic). External Characteristic nodes are just like Basic Characteristic nodes, except they carry master data values of other(external) Characteristics. For example, if InfoObjects Product and Product Group are available and a hierarchy for Product is being defined, one could use values from Product Group (an External Characteristic) in their Product hierarchy definition [2]. Last but not least is the Text node type. Text nodes are used when one wants to define the node’s value as free text. It’s important to note that these nodes are not based on Characteristic values but carry the exact same functionality as External Characteristic nodes. Lastly, it’s worth mentioning that the main difference between the aforementioned node types is their posting capability. [3]
Creating a Custom Hierarchy in BW:
The strategy used to create a custom hierarchy can be outlined in the following sequence of steps:
Create a Data Flow for loading hierarchies into a Characteristic InfoObject
Create a Data Flow for defining custom hierarchies.
Implement ABAP code to generate a custom hierarchy inside the hierarchy load Transformation
Load the custom hierarchy and use it in a query
Note: Steps 1 and 2 are independent and can be performed in any order.
Filling in the Details…
1. Loading Hierarchies in BW
When it’s decided which Characteristic the custom hierarchy feature has to be implemented for, one must create a dummy hierarchy which will be used as the source (and also as the target) for the hierarchy load. It suffices to insert a single Text node in order to activate the dummy hierarchy.
Figure 1. Dummy Hierarchy for the Characteristic Product (ZA_PROD) used in this scenario.
To create the hierarchy load data flow, create a DTP and a Transformation by right-clicking on the Characteristic.
Figure 2. DTP and Transformation settings for hierarchy loads.
This is what the data flow should look like when it’s complete.
2. Defining a Custom Hierarchy
The first thing that needs to be done when building a new custom hierarchy is to put together a data flow that will allow for the generation and storage of data that will later be used to define the custom hierarchy's nodes. Each column in the resulting table will represent a Characteristic involved in the hierarchy’s definition. Consequently, all table entries will be Characteristic values which are meant to be used as node values in the new hierarchy. [4] In order to populate the aforementioned table, a planning query needs to be built on top of a Direct-Update DSO. To match the expected structure, the DSO will contain all Characteristics required and the query will have the exact same layout as the presumed hierarchy definition table. This scenario uses a simple planning query that is based on two Characteristics - Product (ZA_PROD) and Product Category (ZA_P_CTGY).
Figure 3. The settings for the hierarchy planning query and its underlying DSO.
Because there are no actual Key Figures involved in this data flow, Characteristics need to be used as such. In order to do this, the Characteristic must not be master data-bearing and must be listed as characteristic as a key figure in the DSO. [5] Defining the custom hierarchy is essentially equivalent to master data planning for all the non-master data-bearing Characteristics used in the hierarchy’s definition. The example query uses Product Category as a Key Figure.
Figure 4.The settings for a Characteristic that is used as a Key Figure.
Finally, it’s time to use the planning query to define the custom hierarchy. This scenario aims to create a hierarchy that categorizes Product values as Local and Imported.
3. Implementing the ABAP Code for the Hierarchy Load
Once the custom hierarchy definition is in place, the next step is to use this data to generate an actual hierarchy in BW. This is where the hierarchy load data flow comes in handy. The DTP will be used to trigger a load while an implementation of the Transformation’s End Routine will be used to transfer the custom hierarchy definition into an internal table that the system will then use to create the new hierarchy.
Since the Transformation uses multiple source/target segments (tables) the End Routine allows one to change multiple internal tables which are all used during the hierarchy load. The table that the system uses to store the node values of the source hierarchy is SOURCE_PACKAGE_03. The mandatory table fields that must be maintained in the table are:
OBJECTID – Object ID in Segments (it’s like a hierarchy ID) (system-generated) [5]
NODEID – The node’s ID (user-defined)
IOBJNM – The data type of the Node (user-defined)
PARENTID – the node ID of the parent node (user-defined)
CHILDID – the node ID of the child node [6]
NEXTID – the node ID of the node’s next sibling. [7]
TLEVEL – The node’s hierarchy level (user-defined) [8]
H_HIERNODE - The node’s value (user-defined)
Figure 5. The SOURCE_PACKAGE_3 record definition for each of the nodes shown in the diagram above.
It’s worth noting that the “IMPORTED” node has a NEXTID reference to the “LOCAL” subtree that makes up the remaining portion of the hierarchy. Unless this relationship is present, the two subtrees will be practically independent, implying that there are two separate hierarchies defined simultaneously. It’s safe to say that each hierarchy has its basic Characteristic serving as the ultimate parent. One can see this when using a hierarchy in a query – the column header is always the top most level of the hierarchy.
Figure 6. SOURCE_PACKAGE_3 containing all the data required to generate the custom hierarchy's structure.
4. Loading and Using the Custom Hierarchy
When all the necessary logic to populate SOURCE_PACKAGE_3 as per the requirements is in place, all that’s left to be done is to start the load via the DTP in order to generate the custom hierarchy in BW. Once the load is finished, one should be able to see the new hierarchy structure in the Hierarchy Maintenance Screen of the Characteristic for which the hierarchy was loaded.
Figure 7. The custom hierarchy generated for Product.
To exploit the new hierarchy, one should first enable hierarchy usage for their Characteristic in the query definition. Once done, one should select the hierarchy either at runtime or in the query definition, in order to view the data according to its structure.
Figure 8. Different hierarchies providing different viewpoints on the same data set.
In conclusion, having the capability to define custom hierarchies in such a way makes planning and reporting more flexible and powerful. It allows users to view and analyze datasets from many different angles and more importantly, gives them the ability to create unique structures that best suit their needs. Hopefully, the overall approach to implementing this feature is now clear and one can apply it on demand to fulfill their clients’ requests.
Additional Information
Note: The 4th and last node type available is called Intervals. It is not covered in this article, but you can read more about it here.
In general, all External Characteristics must be defined beforehand in the Hierarchies tab of the basic Characteristic InfoObject definition under the External Characteristics in Hierarchies tab. The check for the external characteristic definitions is bypassed when a hierarchy is generated programmatically. However, it is still strongly recommended that this definition list is properly maintained at all times to meet the good practice standards.
All you need to know about Postable Nodes can be found in this SAP Wiki.
You can use the key values in the hierarchy definition table as values for Text Nodes as well.
Characteristics used as Key Figures in a DSO, cannot be used in the key definition of that DSO.
The OBJECTID for the target hierarchy value can be copied from SOURCE_PACKAGE_01. If omitted, it will result in an error during the load – see note 0002099495
If the node has multiple children, CHILDID refers to the node’s first child.
A child that is the last sibling of its parent has a NEXTID of 0.
The root node has a hierarchy level of 1.
For more SAP BI consulting - contact us.