How to load and generate a custom hierarchy in SAP Business Warehouse (SAP BW) without using flat files 沧海

Introduction

Often reports need to show data in a tree-like structure. This is enabled via characteristic hierarchies in SAP BW. High level data is displayed using the top nodes of the hierarchy. More detailed data can be visualized by drilling down to the lower nodes of the hierarchy.

Consider an example hierarchy ‘Customer Contacts Hierarchy' that is used to qualify the telephone calls a telecommunications call center receives.

Image 1: Customer Contacts Hierarchy

click to enlarge

 

Standard mechanisms for loading hierarchies

There are several possibilities to supply the hierarchy with data in SAP BW:

• Hierarchies can be maintained manually in SAP BW, however, doing this for large or frequently changing hierarchies is of course time consuming. 
• Hierarchies can be fetched automatically from all types of source systems. SAP delivers numerous BI Content datasources to extract standard data from SAP source systems for all kinds of industries and modules. This is also the case for lots of standard hierarchies, like profit center, organizational unit… However, very often the hierarchy data is available in custom tables in the source system. SAP does not allow for custom hierarchies to be extracted with a generic datasource. 
• Hierarchies can also be extracted from external systems (non-SAP) using staging BAPI's (Business Application Programming Interface) or flat files, which can be generated automatically via a program and then loaded into the hierarchy InfoObject. When automating this procedure a problem is introduced that is not always easy to overcome; the physical flat file that is to be uploaded should be placed on the SAP BW server. This is not always possible for several reasons.
• Last option to load a hierarchy is the data mart interface which allows loading data from within a SAP BW system or from another SAP BW system. The problem is that the hierarchy is not (yet) available in a SAP BW system. However, this option will prove to be very useful to enable a custom hierarchy extraction.

If you want to load a standard SAP hierarchy or a hierarchy based on a flat file, you can use one of the above mechanisms to fetch the data. However, when loading non-standard SAP hierarchies or hierarchies from other sources, you are facing one big problem. SAP BW only allows standard hierarchy datasources or flat file based hierarchy datasources and one thing is for sure, loading any type of hierarchy requires a datasource of type hierarchy to insert the data into the corresponding InfoObject tables! So there is no standardized way to load a custom hierarchy from a source system.
 

How can we work around this problem?

In short, the solution is to get the data into a DataStore Object (DSO) [1], make sure the InfoObject has the correct properties [2], load a dummy hierarchy (using the data mart interface) [3] and enhance this extraction in order to replace the dummy hierarchy with the data from the DSO [4]. This procedure will be explained in more detail below.

Image 2: Solution overview

 

1. DSO containing the Hierarchy Data

The first step is to get the hierarchy data into a DSO. We can use a transactional datasource for this purpose. A combination of the node ID and its parent node ID is enough to build a simple hierarchy.

Image 3: DSO ZDSOCUST

click to enlarge

It is important to note that we do not need a hierarchy datasource. No matter what source of data and type of extractor, SAP allows various procedures to fetch the "transactional” data.

The DSO ZDSOCUST used in this example was populated with a simple flat file.

 

Contact;Description;Parent Contact
1001;Commercial;
1002;Technical Support;
1003;Change Contract;1001
1004;End Contract;1001
1005;New Contract;1001
1006;TV;1002
1007;Telephone;1002
1008;Internet;1002
1009;Up-sell;1003
1010;Down-sell;1003
1011;Side-sell;1003
1012;End;1004
1013;New;1005
1014;Digital TV;1006
1015;Analog TV;1006
1016;Mobile;1007
1017;Fixed;1007

 

2. Hierarchy InfoObject

Image 4: InfoObject Customer Contact

click to enlarge

Using transaction code RSD1, maintain the InfoObject as an ‘InfoSource with Direct Update'.

Choose an Application Component after enabling the corresponding checkbox. Depending on the InfoObject properties, this will allow loading attribute, text and hierarchy data for the characteristic.

Mark the characteristic as an InfoProvider by assigning it to an InfoArea. The InfoObject will then be displayed in the InfoProvider tree in the Data Warehousing Workbench.

Enable the characteristic as an export data source. This allows the extraction of the InfoObject data using the data mart interface.

Image 5 InfoObject Properties

click to enlarge

Obviously you will also need to specify that the InfoObject contains hierarchies. The exact settings, like time-dependent and versions, affect the tables being generated and this will in turn require specific coding in the datasource enhancement afterwards.
 

Image 6 InfoObject with hierarchies

click to enlarge

3. Dummy Hierarchy Extraction

Now the InfoObject is correctly maintained and activated, we can build a dummy hierarchy, which we will use afterwards as a starting point for the data extraction. This manual process needs to be done only once (per SAP BW system in your landscape). Use the button ‘Maintain Hierarchies' in order to create the dummy hierarchy.

Image 7 Creating a dummy hierarchy

click to enlarge

Adding one (dummy) node to this hierarchy is sufficient to activate the hierarchy. A text node is the easiest to add.
 

Image 8 Using a dummy node

Image 9 Dummy hierarchy

 
The next step is generating an export datasource. This will in fact generate a data mart for extracting attributes (M), texts (T) and hierarchies (H), depending on the InfoObject settings. Go to the ‘Data Warehousing Workbench' (transaction code RSA1) and right-click on the InfoObject. In the context menu, choose ‘Additional Functions' > ‘Generate Export DataSource'. The datasource typically gets the technical name 8 <‘M', ‘T' or ‘H'> and can be found in the application component called ‘Data Marts Master Data' (DM-IO) after replicating this application component in the datasources area of the Data Warehousing Workbench (remember to check the BW system itself as a source system on the top left of the screen). If this specific application component is not yet activate in your BW system, the new datasource can be found in the application component ‘Unassigned Nodes' (NODESNOTCONNECTED).

Now we have a hierarchy datasource and we can link it to our InfoObject hierarchy via transfer rules. Hierarchy datasources are not yet supported using SAP Netweaver 2004s/7.0 technology and therefore we still need to use a SAP BW 3.5 datasource and transfer rules to build the connection. In fact we connect the InfoObject to itself. In order to create transfer rules, right-click on the hierarchy and use the context menu to go to ‘Additional Functions' > ‘Create transfer rules'. Next we need to select the correct source system. As we want to employ the export datasource we created earlier, select the same SAP BW system as the one you are logged on to. A list of all available datasources for that source system will pop up. Select the export datasource we created earlier. It should have the name 8 <‘H'>. The transfer rules are generated automatically and in fact they establish the link between the export datasource and the InfoObject hierarchy.

Create a new Infopackage for the hierarchy datasource by right-clicking on the datasource and selecting ‘Create Infopackage…'. Give the Infopackage a descriptive name and refresh the ‘available hierarchies from OLTP'. This will load all hierarchies that are available in the source. As we only created one dummy hierarchy, this will be the only hierarchy we can extract. Select this dummy hierarchy and save the Infopackage. It is advisable to rename the target hierarchy.

Image 10 InfoPackage 

click to enlarge

4. Extraction Enhancement

So far we have built a data flow that allows the extraction of a dummy hierarchy into a new hierarchy that is an exact copy of the original one. Now we still need to insert the hierarchy data that is currently residing in the DSO. This can be done in a user exit, an enhancement of the generated hierarchy datasource.

Image 11 Hierarchy dataflow

Hierarchy datasources can be enhanced like any other type of datasource. Each type has its own function module, within the enhancement "RSAP0001 - Customer function calls in the service API” that is called automatically during extraction:

• EXIT_SAPLRSAP_001: Transactional data
• EXIT_SAPLRSAP_002: Master data attributes or texts
• EXIT_SAPLRSAP_004: Hierarchies

If enhancement RSAP0001 is not yet active in your system, it can simply be activated and assigned to a project.

The function module ‘EXIT_SAPLRSAP_004' is called every time a hierarchy datasource is executed. It is part of the function group ‘XRSA - Customer Exits in the Service API'. This function module contains an include called ‘ZXRSAU04'. This include can be enhanced using ABAP coding to fetch the data from the DSO we loaded earlier. The most important table in this exit is table C_T_HIENODE which should be filled with the actual hierarchy data. This table has the type RSAP_T_HIENODE which is defined like the structure ROSHIENODE.

Image 12 Structure ROSHIENODE 

click to enlarge

Note that not all fields are required in order to successfully build your hierarchy. Depending on the hierarchy properties in the InfoObject settings, other fields and even other tables can become required.

The above example hierarchy is quite straight-forward and therefore it is sufficient to populate only a selection of fields of the table C_T_HIENODE; NODEID, IOBJNM, NODENAME, TLEVEL and PARENTID.

Let's have a more detailed look at these fields. The field NODEID has to be uniquely defined. The field IOBJNM should be filled with the InfoObject name ‘ZCUSTCONT' or the value ‘0HIER_NODE' if you want to load a text node. The field NODENAME should be filled with the actual value (InfoObject value or the description of the text node). Compounded InfoObjects can also be used, but then we need to specify the compounded value as well. Imagine that our InfoObject ZCUSTCONT is compounded with country. If we fill the field NODENAME with the value "BE 1003”, this value will be automatically split into the country "BE” and the Customer Contact "1003”.

The next field is called TLEVEL and contains the hierarchy level of the node. If the hierarchy level of a node is not equal to the sum of the hierarchy level of its parents and 1, this will generate an error when loading the hierarchy. The top node of a hierarchy is called a root and multiple roots are allowed in a single hierarchy. This root node always has hierarchy level 1. PARENTID contains the node id of the parent.

Table 1 Populating C_T_HIENODE

According to your hierarchy properties, other fields and/or tables will be required. For example, if you have a time-dependent hierarchy, the fields DATEFROM and DATETO should be filled as well.

The field NODENAME has to be unique, except when the field LINK contains the value ‘X' (in capitals!) which allows for one hierarchy node to be included several times in the hierarchy (at different hierarchy branches). In other words, one NODENAME can have multiple parent nodes. When using these duplicate hierarchy nodes in a report, the key figure value for that InfoObject value will be displayed multiple times in the hierarchy. Of course this key figure value will only be taken into account once in the result rows! When using intervals, you will also have to fill the table C_T_HIEINTV.

If we have a DSO that only contains the node id and its parent node id, we have enough information to build our hierarchy. However we need to calculate the hierarchy level manually. Best practice is to start with the root(s) and find its children, then find the children of those children and so on. Note that each root should of course be listed separately as well.

Let's take a closer look at the coding. As the include ‘ZXRSAU04' is executed each time a hierarchy datasource is executed, it is recommended to make a distinction between the datasources.

This procedure makes sure that your coding is not executed when other hierarchy datasources are executed.

The first thing to do in the ABAP program is to delete the dummy hierarchy data from the internal table C_T_HIENODE.

Now we can set the hierarchy description ‘Customer Contacts'.

Next we need to fetch the hierarchy data from the DSO into an internal table.

For each node we need to find out the hierarchy level and the node ID of its parent. Make sure all relevant fields are filled. As mentioned earlier, this varies according to the InfoObject properties. The easiest way is to work top-down and start with the root(s). A root is a node that does not have a parent. A hierarchy can consist of multiple roots. Once we have identified the roots, we can search for its children. Then we need to search for the children of the children. Every record is saved in the table C_T_HIENODE. We continue this process until every record is processed.

Now that we have completed the datasource enhancement, the last step is executing the Infopackage. This will populate the hierarchy based on the dataset that we previously loaded into a DSO. The complete loading procedure can be automated by implementing a process chain.

That's it! The hierarchy is ready for use in the SAP BW reports.

Conclusion

Using SAP NetWeaver BW 7.1 (or prior), there is no standardized way to load a custom hierarchy. Hierarchies can only be loaded using the BW 3.x data flow and datasource migration is impossible. The procedure described in this insight is a good workaround to enable an automated custom hierarchy extraction. With the brand new hierarchy framework that is delivered with SAP NetWeaver BW 7.30 , this will become easier as any type of datasource can be used.

Original document: http://www.element61.be/e/resourc-detail.asp?ResourceId=189