Hierarchies play an important role in SAP BI reports for data aggregation. Hierarchy enables the user to summarize the data at a desired level in the BI report. Data can be rolled up or down dynamically by the user.

Scenario / Requirement:  There are situations when the developers may need to read the hierarchy to perform a business logic while transforming the data in the data model. Reading the tree structure provided by standard SAP via a code can be cumbersome and may impact data load performance.

Background information:

Standard SAP BI hierarchy (tree structure) of a geographical hierarchy with three levels may look like:

hierarchi pic.png

The H table of this hierarchy in SAP BI will be:

Note: the data below is shown only for illustration, the H table in BW system may have more fields and deals with node ID’s, not node names, this has been done for simplification.

Hierarchy ID

Node ID

Level

Parent

Child

Next

19823547648

ASIA

1

JAPAN

JAPAN

19823547648

JAPAN

2

ASIA

JP1

CHINA

19823547648

CHINA

2

ASIA

CH1

INDIA

19823547648

INDIA

2

ASIA

IN1

JP1

19823547648

JP1

3

JAPAN

JP2

19823547648

JP2

3

JAPAN

CH1

19823547648

CH1

3

CHINA

CH2

19823547648

CH2

3

CHINA

IN1

19823547648

IN1

3

INDIA

IN2

19823547648

IN2

3

INDIA

Problem Statement:

Many business logics expect the data model to transform data based on the values in the hierarchy. For example, the requirement may be to find the plants in JAPAN and use that data as an input for further transform the data based on a complex business logic.

In this case, BW developers often opt to write a code to read the H table of the hierarchy which is often cumbersome as one has to loop through the entire table and all levels to determine the plants in JAPAN.

Similar logic often has to be applied for multiple look ups which often hits the data load performance.

Proposed Approach:

Flatten the tree structure of the hierarchy and store it in a transparent table (example DSO or as a master data).

hierarchy pic 2.png

Here is a comparison of a standard SAP BI hierarchy vs a flattened structure of the same data:

Standard SAP BW hierarchy (tree structure)

Flattened Hierarchy in a Data store object or as a master data table

Data Summarization in reports

Good, can summarize the data with a good look and feel to the user.

Yes, can summarize the data but may not be appealing to user.

Data Filtration in reports

Yes, but not much intuitive, user has to drill down to each level to apply filters.

Yes, user can apply the filters easily as a standard report column.

Data Redundancy

No redundancy while storing the data in base tables.

Generates redundant data.

Reporting performance

Comparatively lower than a flat structure.

Good

Lookups for transforming data

Cumbersome and impacts the performance of the data loads.

Easy and helps in better data load performance.

Conclusion / Recommendations:

Use Standard BW hierarchy when the look and feel of the report is important and main purpose of the hierarchy is to summarize the data, i.e. no business logic requires to transform the data based on the contents of the hierarchy.

A Flat structure may be preferred when the user is highly analytical in nature and would like to filter the data frequently, based on the hierarchy. In case the business logic requires to read the hierarchy and transform the data during ETL, a flat structure may be preferred for lookups.

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

Leave a Reply