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
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
||Sales by Customer
||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.
||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)
||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
- 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,
- 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
- 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.
||Maintenance Screen for Hierarchy InfoSource
|| Unique ID for the hierarchy node (local to each hierarchy)
||Name of the InfoObject that this hierarchy node refers to
||Name of the key value of this hierarchy node
|| Flag: X is a link node
||The node ID of the parent node
|| Language of the descriptions
||20 character description
||40 character description
||60 character description
||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 NAME
|| PARENT NODE ID
||PARENT NODE NAME
||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).
|| Coast Brokers
|| Happy Vacuum
|| Frost Electronics
|| Ocean Cola
|| Northern Airline
||The Customers Table
Then, the Regions table (Figure 9),
which contains the company's regions ordered sequentially.
||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
- 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).