home
competences
elementary
team
knowledge base
careers
news & events
company
contact us
site search
english eng lish | nederlands | fr ançais
insights webcasts w ebcasts
How to load and generate a custom hierarchy in SAP Business Warehouse (SAP BW) without using flat files
books b ooks case c ase studies studies BI-kipedia history of performance management useful links
Introduction
by Dirk Francis Thursday, February 03, 2011
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.
RELATED COMPETENCES
Consider an example hierarchy ‘Customer Contacts Hierarchy' that is used to qualify the telephone calls a telecommunications call center receives.
SAP BW Performance Tuning SAP Business Warehouse SAP Business Objects 4
Image 1: Customer Contacts Hierarchy
SAP BO Upgrading & Migration SAP BO Performance Tuning SAP BO Implem Implementation entation & Know ledge Transfer SAP Business Objects Architecture SAP Business Objects Xi Business Intelligence Best Practice ETL Architecture
click to enlarge
Business Intelligence - Data Integration
Standard mechanisms for loading hierarchies
Business Intelligence - Data Warehousing & Data Modeling Business Intelligence
There are several possibilities to supply the hierarchy with data in SAP BW:
Functional Performance Management Expertise
Hierarchies can be maintained manually in SAP BW, however, doing this for large or PM & BI Audit frequently changing hierarchies is of course time consuming. PM & BI Roadmap Hierarchies can be fetched automatically from all types of sou rce system s. 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, organ izational unit… Ho wever, very often the hierarchy data is available in custom tables in the sou rce system . SAP does not allow for custom hierarchies to be extracted with a generic datasource. Hierarchies can als o be extracted from external s ystems (non-SAP) usin g 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 sho uld be placed on the SAP BW server. This is not always possible for several reasons. Last option to load a hie rarchy is the data m art interface which allows loading data from within a SAP BW system or from anothe r SAP BW syste m. The probl em is that the hie rarchy is not (yet) availab le 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 hierarch 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'. Choos e an Application Com ponent after ena bling the corresp onding checkbox. Depending on the InfoObject properties, this will allo 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
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 b e found in the application compo nent calle d ‘D ata Marts Master D ata' (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
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 hiera rchy data. This table has the type RSAP_T_HIENODE whi ch i s defin ed like the s tructure ROSHIENODE. Image 12 Structure R OSHIENODE
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 onl y 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 fieldIOBJNM should be filled with the InfoObject name ‘ZCUSTCONT' or the value ‘0HIER_NODE' if you want to load a text node. The fieldNODENAME 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.
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 fieldLINK 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 close r loo k at the codin g. As the inclu de ‘ZXRSAU04' is executed each time a h ierarchy datasource is executed, it is recommended to m ake a dis tinction 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. « back
Share
13
Advisory | IBM Business Intelligence & CPM | SAP Business Intelligence & CPM | Microsof t Business Intelligence | Interim Management | Hosting
2009-2011 | copyright by element61 | all rights re served
home | sitemap | contact
