Including Hierarchies in an Analytical Query

For analytical query results that contain a lot of data, it is good practice to compact the results into a hierarchy so that the business users can decide how to expand each level of the hierarchy to reach the details.

Below is an example of a analytical query result that displays the results for year, quarter, month and date, that has been compacted into a hierarchy. Notice the small arrows that appear alongside each node of the hierarchy. Clicking on these arrows disaggregates the measures by drilling-down to the next level of detail in the hierarchy, or they can be used to collapse the detail to aggregate the measures.

The benefit of this approach is that when the analytical query result first appears, the business user is not overwhelmed with data. Instead, the analytical query opens with a simpler, summarized view. Measures are aggregated to produce a smaller result set that is easier to work with.

In our example, the results are compacted in the rows with the node-totals displayed at the bottom with the sub-nodes displayed above. This is the default layout. However, it is possible to display the node-totals at the top with the sub-nodes displayed below. To do this use the header annotation @Analytics.settings.rows.totalsLocation: #TOP.

As well as in the rows, you can also compact the dimension in the columns. You can even compact both axis simultaneously.

The hierarchy is formed from the dimension relationships defined in the underlying cube of the analytical query. No separate hierarchy definition is required.

This type of hierarchy is referred to as a universal display hierarchy.

A prerequisite to defining a universal display hierarchy is that totals must be enabled for all dimensions on the axis that is to be compacted.

Let's look at the code to implement a universal display hierarchy.

We first need a header annotation: @Analytics.settings.<rows / columns>.hierarchicalDisplay to enable the hierarchy by default when the analytical query launches. Here, you choose which axis the hierarchy applies to and also the initial expansion level of the hierarchy.

Then list the dimensions in the order of the hierarchy from top to bottom. Don't forget to specify that totals should appear for all dimensions in the rows.

Watch the video to learn how to implement a universal display hierarchy.

We learned that a universal display hierarchy compacts the dimensions in the rows or columns using a sequence of dimensions defined, either in the analytical query, or in the end user tool. A universal display hierarchy is a simple, visual feature to help the business user navigate large data sets.

As well as a universal display hierarchy, there is another method to define a hierarchy that provides even more features.

We now introduce display hierarchies. Display hierarchies are defined using hierarchy views. The underlying cube might provide hierarchy views. These hierarchy views can be used to define the drill-down of the query result.

SAP provides hierarchies for many standard dimensions such as time, cost center, and product. Customers can also create their own hierarchy views. For each dimension there can be multiple hierarchies that give the business user the opportunity to drill down in different ways.

Here is an example of a display hierarchy that organizes the Departure Airports by country and continent, so that we can observe the aggregated measures at those levels with the option to drill-down.

Let's review the code that defines a display hierarchy in an analytical query.

Follow these steps to define a display hierarchy in your analytical query:

1. Select a display hierarchy for the chosen dimension. In our case, we chose the hierarchy GEO.
2. Add the key of the dimension as a dummy field (create a dummy alias name to avoid conflicting with other fields) and hide this field by adding the annotation @Consumption.hidden: true. This is required only for correct CDS syntax and is not used in the query result.
3. You enable the hierarchy with the annotation @AnalyticsDetails.query.displayHierarchy: #ON and the annotation @AnalyticsDetails.query.hierarchyAssociation references the name of the display hierarchy.

In the example above, the hierarchy with the name GEO has been hard-coded into the analytical query. This means that the same hierarchy is always used. But often there are multiple hierarchies available and the business user should choose the hierarchy. So then we would need to implement a parameter that provides the user with a prompt where they can select a hierarchy from a list. You will learn how to do this in a later lesson.

In an analytical query, only one display hierarchy for a dimension can be selected.

Watch the video below to learn how to implement a display hierarchy in your analytical query.