Article

Using Hierarchies to Unlock Information in SAP BW

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).

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.

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).

While the use of cloud-based solutions is on the rise, on-premise solutions remain a critical part of IT infrastructures. To help minimize the administrative burden of managing these hybrid environments,...