Tree vs Flat Hierarchies in SAP BI

[Former Member]

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:

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

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.