GRC
HR
SCM
CRM
BI


Article

 

Using Hierarchies to Unlock Information in SAP BW

by John Asfar | SAPinsider

April 1, 2001

by John Asfar, Information Builders Incorporated SAPinsider - 2001 (Volume 2), April (Issue 2)
 

     SAP Business Information Warehouse (SAP BW) provides the ability to support hierarchies for master data. This enables you to access reports that answer questions like "What were the total sales last month summarized by my company's regional structure?" Then you can see the answer to such questions as "What are the sales figures for the region that is leading in sales?"

     In this article, I will introduce the basic techniques for loading hierarchies into SAP BW. To that end, the following examples take a look at a fictitious company to illustrate the usefulness of hierarchies.

The Power of Hierarchies

Figure 1 shows a basic company report of sales listed by customer. It is just a tabular report - it does not make use of hierarchical information. Under close scrutiny, you can determine that the company's biggest customer is in the West, and that the next biggest is in the Central region, but it is difficult to determine much else.

     In contrast to the limited usefulness of the report in Figure 1, a report driven by the use of hierarchies has much broader use. Reviewing even the simple customer sales report shown in Figure 2, it is easy to see that the Central region is attaining exceptional sales. From this information, you can argue that the techniques and strategies employed by the Central region's sales staff should be deployed throughout the company, because those strategies are obviously succeeding.

Figure 1 Sales by Customer

Figure 2 Customer Sales by Region

     SAP BW allows you to expand and collapse the nodes of the hierarchy, revealing different levels of detail. Figure 3 shows the Customer Sales report with the hierarchy fully expanded. It allows you to see totals rolled up, but also allows you to drill down to more detail when questions arise. In Figure 2, you see that the Central region is excelling in sales, but drill down to Figure 3 and you can see not only that the Central region has more customers, but that the customers in this region are actually buying more as well.

Defining the Hierarchy for BW

How do you define a hierarchy in SAP BW? We'll go through the basic concepts of identifying and loading your hierarchy, and creating the database that is the foundation for these hierarchy-driven reports. The following sections also include some of the code you need to get started.

Figure 3 Customer Sales by Region Fully Expanded

     First, begin with the structure of a simple customer hierarchy, like the one illustrated in Figure 4. This chart has three levels:

  • The top of the tree (Regions)
  • The company-defined regions (East, Central, and West)
  • The customers in each region (listed by company name)
Figure 4 Customers by region

     For the purposes of our discussion, this fictitious company has only nine customers divided among three regions. In this chart, each node is identified in relation to its "parent," for example:

  • The highest level of the tree is node 1, REGIONS. It has no parent, so we represent this with a parent of 0.

  • The next item, EAST, is node 2. The parent of this node is REGIONS, node 1.

  • CENTRAL is node 3. Its parent is REGIONS, node 1.

  • WEST is node 4. Its parent is REGIONS, node 1.

  • The first item on the next level of the tree is COAST, node 5. The parent of this item is EAST, node 2.

  • Nodes 6 and 7, OCEAN and PIER, also have as their parent EAST, node 2.

  • Nodes 8, 9, and 10 have as their parent CENTRAL, node 3.

  • Finally, Nodes 11, 12, and 13 have as their parent WEST, node 4.

     To load a hierarchy into SAP BW, every one of these relationships must be explicitly identified.

     SAP BW uses the same mechanism to load hierarchies as it does to load other types of data into BW: InfoSources. An InfoSource is a group of InfoObjects required for the transport of one logical collection of data for a particular process (for example, all the data required for our hierarchy). All InfoSources have the same basic structure, which is illustrated in the hierarchy maintenance screen in Figure 5. In SAP BW 2.0B, you can reach this screen by changing the transfer rules for a Master Data InfoSource, choosing "Hierarchy DataSource," and then clicking the "Hierarchy Maintenance" button.

Figure 5 Maintenance Screen for Hierarchy InfoSource

Column Definition
NODEID Unique ID for the hierarchy node (local to each hierarchy)
INFOOBJECT Name of the InfoObject that this hierarchy node refers to
NODENAME Name of the key value of this hierarchy node
LINK Flag: X is a link node
PARENTID The node ID of the parent node
LANGU Language of the descriptions
TXTSH 20 character description
TXTMD 40 character description
TXTLG 60 character description
Figure 6 Description of the Fields in the Hierarchy InfoSource

     Figure 6 lists all fields in the Hierarchy InfoSource and their descriptions.

     It will take a few steps to create the data required by this target InfoSource - a staging table will be populated with three queries in order to create the necessary data. The resulting staging table (Figure 7) will contain the relationship between the parent and the child nodes in our hierarchy.

NODE ID NODE NAME PARENT NODE ID PARENT NODE NAME
1 Regions 0 N/A
2 East 1 Regions
3 Central 1 Regions
4 West 1 Regions
5 Coast 2 East
6 Ocean 2 East
7 Pier 2 East
8 Frost 3 Central
9 Happy 3 Central
10 Lake 3 Central
11 Valley 3 Central
12 Mountain 4 West
13 Northern 4 West
Figure 7 Parent-Child Relationships of Hierarchy Nodes

     Let's assume we have three tables to start with. First, the Customers table, which holds the Customer number and Customer name, and also has a field containing the region (Figure 8).

CUST_NO CUST_NAME REGION
17723453 Lake Auto CENTRAL
25200295 Coast Brokers EAST
33560401 Happy Vacuum CENTRAL
48239123 Mountain Software WEST
54324062 Frost Electronics CENTRAL
69439193 Ocean Cola EAST
71648329 Valley Tractor CENTRAL
80153434 Pier Applications EAST
91823049 Northern Airline WEST
Figure 8 The Customers Table

     Then, the Regions table (Figure 9), which contains the company's regions ordered sequentially.

REGION_NO REGION
1 EAST
2 CENTRAL
3 WEST
Figure 9 The Regions Table

     Finally, we have the initial layout of the Staging table (HISTAGE), which will allow us to build the hierarchy in steps. HISTAGE is made up of five fields:

  • NODE_ID: The unique id for the child node

  • INFO_OBJ: The type of field being loaded (e.g., OHIER_NODE, OKUNNR)

  • NODE_NAME: The text describing the node or the key for the object being loaded

  • NODE_DESC: A short description of the node

  • PAR_NODE: The node ID of the parent node

     With the three standard SQL statements shown in the listings that follow, we can populate this staging table with necessary data (Listing 1).


INSERT INTO HISTAGE (NODE_ID, INFO_OBJ, NODE_NAME, NODE_DESC, PAR_NODE) 
VALUES (
'1', '0HIER_NODE', 'REGIONS', 'REGIONS', '0');
Listing 1 Inserting the Parent Node REGIONS into the Staging Table

     The statement in Listing 1 will insert the top node of the tree into the staging table. After this statement, the staging table should contain the record in Figure 10.

NODE_ID INFO_OBJ NODE_NAME NODE_DESC PAR_NODE
1 0HIER_NODE REGIONS REGIONS 0
Figure 10 The Staging Table, with Parent Node Included

     After the statement in Listing 2, the staging table will contain the records in Figure 11.


INSERT INTO HISTAGE (NODE_ID, INFO_OBJ, NODE_NAME, NODE_DESC, PAR_NODE) 
	(SELECT REGION_NO + 1, '0HIER_NODE', 
                REGION, REGION, '1'
	FROM REGIONS);
	
Listing 2 Inserting Company Regions (Nodes 2-4) into the Staging Table

NODE_ID INFO_OBJ NODE_NAME NODE_DESC PAR_NODE
1 0HIER_NODE REGIONS REGIONS 0
2 0HIER_NODE EAST EAST 1
3 0HIER_NODE CENTRAL CENTRAL 1
4 0HIER_NODE WEST WEST 1
Figure 11 The Staging Table, with Regions Nodes Inclueded

     The final insert statement (Listing 3) will join the staging table with the customer table joined by the region. This will allow us to determine the node ID of the parents for each of the customers.


INSERT INTO HISTAGE (NODE_ID, INFO_OBJ, NODE_NAME, NODE_DESC, PAR_NODE) 
  SELECT CUST_NO,  '0CUSTOMER', CUST_NO, CUST_NAME, HISTAGE.NODE_ID
  FROM CUSTOMERS, HISTAGE
  WHERE CUSTOMERS.REGION = HISTAGE.NODE_NAME;
Listing 3 Inserting the Customer Nodes by Joining the Staging Table with the Customer Table

     After this final insert statement is complete, the staging table will contain the records shown in Figure 12.

NODE_ID INFO_OBJ NODE_NAME NODE_DESC PAR_NODE
1 0HIER_NODE REGIONS REGIONS 0
2 0HIER_NODE EAST EAST 1
3 0HIER_NODE CENTRAL CENTRAL 1
4 0HIER_NODE WEST WEST 1
17723453 0CUSTOMER 17723453 Lake Auto 3
25200295 0CUSTOMER 25200295 Coast Brokers 2
33560401 0CUSTOMER 33560401 Happy Vacuum 3
48239123 0CUSTOMER 48239123 Mountain Software 4
54324062 0CUSTOMER 54324062 Frost Electronics 3
69439193 0CUSTOMER 69439193 Ocean Cola 2
71648329 0CUSTOMER 71648329 Valley Tracto 3
80153434 0CUSTOMER 80153434 Pier Applications 2
91823049 0CUSTOMER 91823049 Northern Airline 4
Figure 12 Final Staging Table

     Then use the query in Listing 4 to create a comma-delimited file, the format required by the SAP BW Hierarchy InfoSource. You can then spool the output of the query to a file, and specify it as the external file when loading the InfoPackage for the hierarchy.


SELECT NODE_ID, ',' a, INFO_OBJ, ',' b, NODE_NAME, ',' c,
  ' ' LINK, ',' d, PAR_NODE, ',' e, 'E' LANGU, ',' f,  
  NODE_DESC TXTSH, ',' g, NODE_DESC TXTMD, ',' h,
   NODE_DESC TXTLG
FROM HISTAGE
ORDER BY INFO_OBJ DESC, NODE_ID;
Listing 4 Query to Create the Comma-Delimited Format Required by SAP BW

Conclusion

We have seen that hierarchies within SAP BW can be used to graphically display and summarize data based on your organizational structure. Hierarchies can then be populated with the use of a staging table with a few simple SQL statements. These hierarchies allow you to use BW to its fullest to get the information you need.


John Asfar is currently an Advisory Analyst at Information Builders Incorporated in New York City. He has been designing and developing interfaces with SAP since 1995. He has been instrumental in developing and enhancing Information Builders' SAP-specific ETL solutions for both SAP R/3 and SAP BW. Most recently, with the Data Migrator for SAP BW product, he has played the lead role in certifying Data Migrator for the BW Staging Interface (BW-STA).


An email has been sent to:






More from SAPinsider



COMMENTS

Please log in to post a comment.

No comments have been submitted on this article. Be the first to comment!


SAPinsider
FAQ