Tree vs Flat Hierarchies in SAP BI
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.
ISn't it same like maintaining navigational attributes in a characteristic at the lowest level?