SAP BW (parent/child) hierarchies in Power BI

SAP BW (parent/child) hierarchies in Power BI

Hierarchies are a very powerful concept in the world of data processing and visualization. For anyone working with hierarchies, it’s important to understand the different formats that can exist on DB level, and how they can be handled in the reporting tools. In this blog we’ll specifically look at the SAP BW (external) hierarchies, and how they can be handled in Power BI.

Before we start, let’s first look at what our environment looks like:

We start with some BW objects, among which InfoObjects with Hierarchies defined on them. Those objects generate views in the HANA db. On top of those views we build our own modeling & reporting views. And then we extract the data from the reporting views to Power BI. We do some ETL in PBI if necessary, and finally build the model & the report on top of it.

OK, so now that we’re clear on the basic setup, let’s look at those hierarchies.

SAP BW can, as many other DB/DWH solutions, store hierarchies either in ‘level’ format or ‘parent/child’ format. The chosen format typically depends on the type of hierarchy that needs to be modeled.

balanced hierarchy level format	unbalanced / ragged hierarchy parent/child format
All paths have the same amount of nodes. Therefore, one can define levels.	Some paths go deeper than others. It’s not possible to define distinct levels.
Example	Example
This hierarchy type is easily modeled in a DB by using different columns for the different levels	This hierarchy type is typically modeled by a parent/child table

The parent/child format (also called ‘external hierarchies’ in SAP BW) is the one we want to focus on in this blog:

• This format has some excellent properties from a technical/modeling point of view (performance, simplicity, completeness), and is specifically interesting for modeling those unbalanced / ragged hierarchies with ease
• However, this format needs some specific processing to construct a typical hierarchical visualization, like this one:

The SAP reporting tools translate the parent/child table structure to the visual hierarchy structure automatically behind the scenes, via a recursive self-join.

Power BI on the other hand, does not support visualizing parent/child hierarchies out-of-the-box, so we need some additional modeling for, first flattening the hierarchy to levels, and then creating a hierarchy structure from those levels.

Let’s start our discussion with the second part, creating the hierarchy structure. When you have already flattened the parent/child structure into levels, this step is actually fairly easy. It is, off course, done in the reporting tool, and it involves simply creating a PBI “hierarchy” from one of our level columns, and then adding the other level columns to it.

Result:

Unfortunately, we can’t make this ‘dynamic’ in any way. Let’s say that we have 4 levels today, and tomorrow the data has changed, and we have 5 levels. Then we’ll need to manually add the new 5^th level into the created hierarchy. :-/

Now, for the first part, flattening the parent/child hierarchy into levels, you have several options:

• Where?
  • You could do it in SAP BW
  • You could do it in HANA
  • You could do it in Power BI, either with Power Query or DAX
• How?
  • You could do it statically (define the levels manually, beforehand)
  • You could do it dynamically (discover the levels automatically during data load)

Let’s first discuss the how. It would of course be great if we can get the levels dynamically, so that it will always work, no matter what changes happen in the data (new levels / removed levels). And it is in fact possible to do it dynamically. (see, for example, the excellent blog  https://www.thebiccountant.com/2017/02/14/dynamically-flatten-parent-child-hierarchies-in-dax-and-powerbi/)

But please remember that the second part (constructing the hierarchy) could not be done dynamically! So, to me, there is no great advantage in doing this part dynamically, after which you will still need to do manual actions on report level. The better option might be to just foresee some extra ‘contingency’ levels in a static way.

Now, with respect to the where:

• BW is, although possible, probably the least interesting option. There is no specific code that you can summon to do the flattening. And since we’re still in BW, it might be interesting to keep the hierarchy in its native SAP form. That way reporting on it via SAP reporting tools stays possible.
• HANA has some nice “Hierarchy Functions”, but please be aware that they have nothing to do with flattening the hierarchy. They are meant for working with Hierarchies in the native SAP way (aka multidimensionally). Still, it should be fairly easy to flatten the hierarchy through some custom SQL code and using these functions could help.
• Power BI is probably the easiest place to do the flattening. You can do it during ETL in Power Query, or during reporting in DAX. In Power Query there are options to do it dynamically, but as mentioned before, it doesn’t brings that much added value. Moreover, the static technique has the advantage of being easily readable / understandable for others afterwards.
• So, we chose to do it statically within Power BI DAX. And, to be future proof, we added some extra ‘not-yet-used’ levels.

Let’s dive in, and see what the different steps are in this static PBI DAX approach:

1. Create a ‘PATH’ column

This is the ‘magical’ step that makes our efforts feasible. The standard DAX function “PATH” travels through our parent/child dataset, and discovers the entire path for each child.

2. Create ‘Level’ columns

As said, we choose to do this statically, which means choosing beforehand the maximum number of levels that we can have. To make things a bit more future-proof we do add more level columns than our data currently needs.

3. Add ‘Level’ columns to a PBI Hierarchy

This step uses the standard PBI functionality of creating a “hierarchy” from multiple columns. Basically, each child line is split into several lines, with each subsequent level value in a line below the previous level value. As we have an unbalanced/ragged hierarchy, not all levels will be filled for all child lines. And thus we can get empty rows.

4. Show level values only when they are filled (get rid of empty rows)
Find the depth of the path for each child

As we can see, this simple formula acts as expected in a basic table (left image) with only the path. But when we use the PBI Hierarchy (right image) the system starts to sum up the values along the hierarchy, which we don’t want in this case.

Create a measure that uses aggregation MAX instead of SUM

Now, instead of summing up the values, we get for each child the maximum value of all its sub-paths.

Create a measure that checks whether a certain level is used (in scope).

The first line for CEO contains a row-depth of 4, because it takes the maximum of all underlying sub-paths. But the second line for CEO (one level further) doesn’t have any sub-paths, and thus only has a row-depth of 1 (= its own row-depth). So, to remove unwanted lines, we can say that browse-depth should always be less or equal to row-depth.

Create a measure that only shows when browse-depth <= row-depth

One could also use an actual measure (for example ‘nbr of employees’) instead of a simple ‘X’. In that case the actual measure will also be correctly summated over the hierarchy nodes. All measures that you want to show in hierarchy form, should have the IF(browsedepth <= rowdepth) part.

The end result