Dynamic Hierarchy

Question & Answer
,
1

Hi,

In order to create dashboards for my customer, I moved his data to AWS cloud and implemented Quicksight service.

I am having problems with a field called “Equipment location”, which organizes hierarchically, so when the user clicks one category > the subcategories open and so on until he reaches what he was looking for- a “Dad” field with categories underneath it, or a “son” category with no categories underneath it, or a “son” category that is also a “dad”.

My problem is that when I import the database into a table, all the values under the “Equipment location” field are imported unchanged, and therefore the hierarchy is not visible.

“Equipment location” database in SAP background looks like this:

DAD SON
Engine Motorhead
Motorhead Motorhead sensor
Motorhead Motorhead gasket
Engine Cylinder
Engine valves

In other words, there could be values that could be both “Dad” and “son”, like “Motorhead”.

In practice, the hierarchy is as follows:

image
image771×394 51.9 KB

Due to the fact that all the values go under one field “Equipment location” regardless of the hierarchy level, I cannot allow the user to filter the data according to different levels and view the relevant records.

My goal is that if the customer filters the data based on only records where “Equipment location” is Engine, they will also see records with “Equipment location” values that are also under Engine: cylinder, valves, motorhead, and under motohead: motohead sensor & motohead gasket.

At the moment, with one field calls “Equipment location”, the customer will only see records whose “Equipment location” equals “engine”.

The alternative solution is to create a few more columns as the number of the hierarchy levels and process the data as follows:

Equipment location 1 Equipment location 2 Equipment location 3
Engine Motorhead Motorhead sensor
Engine Motorhead Motorhead gasket
Engine Cylinder
Engine valves

This solution has a problem because the hierarchy in SAP is dynamic, so the user can add “dad” or “son” and the fields should change accordingly - Columns can be added based on the depth of the hierarchy.

I’m looking for the ideal way to solve the dynamic hiring problem in this case.

Thank you,

Noy

2

@Noys as I understand from your description above, hierarchical data is currently stored in recursive fashion where parent and child are stored in the same column in a relational table and to retrieve this hierarchical relationship you may have a special logic/script to build the hierarchy. For QuickSight, you need to flatten this hierarchical data to add each level of hierarchy in a separate columns and then ingest into QuickSight as dataset to build visuals and use hierarchies in visuals like you have highlighted in your note. This flatten table will be created with as max number of possible level columns supported in your data.
After this flatten table is created, you can use Adding drill-downs to visual data in Amazon QuickSight - Amazon QuickSight, Creating a pivot table - Amazon QuickSight and cascading filter controls that will support your use case.

1 Like
3

hI @DeepakS ,

Thank you for your reply!
You understood it right.
In SAP the relations express by the same column in a relational table of “son” and “dad”/“parent”,
comparing the QuickSight, which requesting a flat hierarchical model.
As I said above the problem with flattening this hierarchical data is that I don’t know how many columns I have to add in advance, because the depth of the hierarchy is not foretold and could always change as the SAP user’s need.

Please tell me if you have any other insight.
Regardless I’ll check the pivot table option since I’m not familiar with that.

Thank you

Noy

4

@Noys, my suggestion will be to reach out to SAP team members and ask them what is the max number of levels have been configured for that specific hierarchy in SAP module and use that information to create flattened hierarchy for that many levels. Most likely you will learn that there are multiple hierarchies in SAP and max number of levels may be different for each of those hierarchies, so you will need to create flattened hierarchy for each of the hierarchies if needed in QuickSight.

5

@DeepakS I understand, I’ll do it. Thank you so much!