Hierarchy is an ordered sequence of dimensions which is very helpful for multi-dimensional analysis of data.
SAP BUSINESSOBJECTS ANALYSIS, EDITION FOR OLAP is the premium alternative for your SAP BEx Web Analyzer and is targeting your business analyst and power users. The product is there to provide your users an environment for multi-dimensional analysis, which also includes hierarchical reporting. Especially when it comes to leveraging hierarchies from your SAP NETWEAVER BW system, SAP BusinessObjects Analysis, edition for OLAP, and SAP BusinessObjects Analysis, edition for Microsoft Office are your main BI clients.
In the next steps, we will use hierarchies as part of our BEx queries and learn the different options how SAP BusinessObjects Analysis, edition for OLAP is able to leverage the hierarchies.
For the next steps, we will use a Bex Query based on the MultiProvider 0D_NW_M01 of the SAP NetWeaver Demo model. The BEx Query contains the following characteristics in the Rows:
A country with an active hierarchy
The BEx Query contains the following characteristics in the Free Characteristics:
Calendar Year / MonthIn addition, the BEx query is using the following Key Figures:
For characteristic Country, we are using an active hierarchy as part of our BEx Query (see Figure 5.36).
Figure 5.36 SAP BW Hierarchy
Let’s now use the BEx query in combination with SAP BusinessObjects Analysis, edition for OLAP.
Figure 5.37 Workspace with Hierarchy
The hierarchy is shown in the rows (see Figure 5.37) and you can use the “+” and “-“ symbols shown next to the hierarchy nodes to open and close the levels for the hierarchy. The hierarchy will also leverage the setting for the placement of the subtotals (before or after the nodes) as configured in the BEx query.
8. Navigate to the tab Display.
9. Select the menu Totals & Parents > Total Settings (see Figure 5.38).
Figure 5.38 Totals and Parents
Here you can – in addition to the configuration option in the BEx query – also configure if the subtotals for the hierarchy nodes are being displayed before or after the nodes.
10. Activate the option Before (see Figure 5.39).
Figure 5.39 Workspace with Hierarchy
11. Now navigate to the Data panel on the left hand side.
12. Open the details for characteristic Country (see Figure 5.40).
Figure 5.40 Characteristic Country
As you can see (see Figure 5.40), we have an entry for the characteristic Country itself (the first entry), which represents the flat presentation of the data and we have an entry for the Country Hierarchy 1, which is the hierarchy we are using for the characteristic. Below the hierarchy – shown as subordinates – we can see the list of levels for the hierarchy and one entry called Leaf Members. The levels for the hierarchy and the entry Leaf Members can be used to filter the information shown in the cross tab, which we will do in the next few steps.
13. Select the entry Leaf Members in the Data panel.
14. Simply drag and drop the entry now towards the cross tab on top of the hierarchy for characteristic Country (see Figure 5.41).
Figure 5.41 Using Leaf Members
The information shown in the cross tab is now being filtered and only the leaf members of the hierarchy are shown (see Figure 5.42).
Figure 5.42 Hierarchy with leaf members only
You can use those entries in the data panel also in a combined way by using multiple entries in a single step.
15. Select the entries Level 1, Level 2, Level 3 from the Data panel. You can select one entry after the other without having to press any specific keys on the keyboard.
16. Now drag and drop the multiple selected items to the hierarchy in the cross tab (see Figure 5.43).
Figure 5.43 Hierarchy
The data is then updated according to the selection we made.
17. Use a right-click on the entry for the hierarchy in the Rows of the Layout panel.
18. Select the menu Show Levels (see Figure 5.44).
Figure 5.44 Show Levels
The menu Show Levels also allow you to filter the data shown by using the levels of the hierarchy.
The menu option Show Levels and the Level entries in the data panel allows you to quickly filter the data in the cross tab based on the levels of a hierarchy. This not only allows you to filter the data, but it also allows you to skip levels of a hierarchy.
19. Select the column header for key figure Net Value.
20. Navigate to the tab Analyze.
21. Select the menu Calculations > Dynamic Calculation (see Figure 5.45).
Figure 5.45 Dynamic Calculation
22. Select the option Olympic Rank (see Figure 5.46).
Figure 5.46 Ranking with Hierarchies
You are receiving an additional column with the Olympic Rank along the hierarchy based on the Net Value (see Figure 5.46). It is important to recognize that ranking information along a hierarchy is very different to ranking information along a standard characteristic. As shown in the image above, you can notice that the ranking of data is done according to the different levels in the hierarchy, for example Europe and North America are ranked against each other, so are the three countries in Europe – United Kingdom, France, and Germany, but these countries from Europe are not ranked against the countries in North America.
23. Remove the Country Hierarchy 1 element from the Rows.
24. Add characteristic Country to the Rows.
25. Add characteristic Product to the Rows.
26. Navigate to the tab Display.
27. Select the menu Totals & Parents and activate the option Display subtotals.
28. Navigate to the tab Display.
29. Select the menu Hierarchy > Compact Display in Rows (see Figure 5.47).
Figure 5.47 Compact Display
By using the option Compact Display in Rows, we are basically using two or more characteristics and simulate hierarchical navigation.
Figure 5.48 Compact Display in cross tab
As shown in Figure 5.48, you can now use the two characteristics like a hierarchical display, which means you can open and close each Country and see the list of Products per Country. This is also known as “Display as hierarchy” functionality in the BEx query designer.
In addition to the hierarchy for characteristic Country, our InfoProvider also has hierarchies available for the characteristic Product (see Figure 5.49). Even though none of the available hierarchies have been configured as an active hierarchy in the BEx query, SAP BusinessObjects Analysis, edition for OLAP is making those hierarchies available in the Data panel. SAP BusinessObjects Analysis, edition for OLAP is listing all hierarchies that are available for each characteristic – independent if those hierarchies have been activated as part of the BEx query.
Figure 5.49 Multiple hierarchies
30. Remove all elements from the Rows.
31. Add the first hierarchy for characteristic Product to the Rows (see Figure 5.50).
Figure 5.50 Product Hierarchy
32. Now select the second hierarchy for characteristic Product in the Data panel.
33. Use a simple drag and drop navigation and drag the second hierarchy on top of the first hierarchy. You can drag and drop the hierarchy to the Rows in the Layout panel or you can drag and drop the hierarchy to the cross tab directly (see Figure 5.51).
Figure 5.51 Exchange of Hierarchy
SAP BusinessObjects Analysis, edition for OLAP not only is able to navigate along hierarchies, but is also able to switch between different hierarchies on the fly – something that only the Analysis suite of products is able to do.
In this section, we reviewed the hierarchical reporting capabilities in SAP BusinessObjects Analysis, edition for OLAP. In the next section, we will learn how to create conditional highlighting as part of our workspace.
Ravindra Savaram is a Content Lead at Mindmajix.com. His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.