Skip to Content
Author's profile photo Ian Henry

Working with SAP HANA Parent Child Hierarchies

A parent child relationships can be used to model many types of hierarchy, including ragged hierarchies, balanced hierarchies, and unbalanced hierarchies.

SAP Analytics Cloud (SAC) originally required us to use parent child hierarchies. Often when connecting live to HANA, you could be modeling your hierarchies in this way.

Below, we can see an example organisational structure. This is an unbalanced hierarchy as the depth of the hierarchy varies depending which part of the organisation you look at.

 

For clarity, we have added the ID of each member.
This ID will be this also becomes the child member within the hierarchy.

As we can see below, the parent child hierarchy only requires a simple structure of two columns, the child entity (Job Title), and the parent or level above that. It is also common to include the text related to that organisation level.

create column table ORG_STRUCTURE (ORG_ID INT, PARENT_ID INT, JOB_TITLE VARCHAR(50));
insert into ORG_STRUCTURE values (1, NULL, 'CEO');
insert into ORG_STRUCTURE values (2, 1, 'EA');
insert into ORG_STRUCTURE values (3, 1, 'COO');
insert into ORG_STRUCTURE values (4, 1, 'CHRO');
insert into ORG_STRUCTURE values (5, 1, 'CFO');
insert into ORG_STRUCTURE values (6, 1, 'CMO');
insert into ORG_STRUCTURE values (7, 3, 'SVP Sales');
insert into ORG_STRUCTURE values (8, 5, 'SVP Finance');
insert into ORG_STRUCTURE values (9, 6, 'SVP Marketing');
insert into ORG_STRUCTURE values (10, 7, 'US Sales');
insert into ORG_STRUCTURE values (11, 7, 'EMEA Sales');
insert into ORG_STRUCTURE values (12, 7, 'APJ Sales');
insert into ORG_STRUCTURE values (13, 9, 'Global Marketing');
insert into ORG_STRUCTURE values (14, 9, 'Regional Marketing');
insert into ORG_STRUCTURE values (15, 11, 'UK Sales');
insert into ORG_STRUCTURE values (16, 11, 'France Sales');
insert into ORG_STRUCTURE values (17, 11, 'Germany Sales');
insert into ORG_STRUCTURE values (18, 12, 'China Sales');
insert into ORG_STRUCTURE values (19, 12, 'Australia Sales');
select * from ORG_STRUCTURE;

With just this single table we can create a calculation view to model this structure.

 

Add a parent child hierarchy, more details on this step can be found in the official documentation. SAP HANA Developer Guide – Parent Child Hierarchies

Adding the Label Column allows us to display the JOB_TITLE and not just the ORG_ID

To be able to report on this we need a measure.
The easiest and most sensible option here is to add a counter to count the ORG_IDs.

 

Analysis for Office

To test hierarchies, we should use a tool that properly understands the hierarchical structures.
Below we can see the hierarchy with SAP BusinessObjects Analysis for Microsoft Office

SAP Analytics Cloud

Exposing this view to SAP Analytics Cloud is straightforward, we need to create a Live Connection to SAP HANA, I have previously described this – SAP Analytics Cloud Live Data Connection to HANA on-premise using CORS.

With the connection in place we can create a model against the CV

We will see our Hierarchy come through as an additional dimension

Exploring this model from story show the hierarchy being correctly represented

Analytic Privileges

Alternatively, if Analysis for Office or SAP Analytics Cloud are not available then one workaround is to view the hierarchy within the Analytic Privileges.
To do this, we need to “Enable Hierarchies for SQL Access” in the Calc View properties.  This property is exposed if we have a Star Join within Calc View.

 

Within the Analytic Privileges dialogue, we can find our hierarchy after first selecting the child attribute, ORG_ID.

 

We can then test and browse our hierarchy, here it shows both the ID (Value) and the Label (Description)

 

So far so good, now joining this hierarchy dimension to a fact table should be straight forward, and it is, provided you use the correct join – an outer join.

create column table EXPENSES (ORG_ID int, EXPENSE_AMOUNT int);
insert into EXPENSES values (1,430);
insert into EXPENSES values (2,120);
insert into EXPENSES values (3,100);
insert into EXPENSES values (4,250);
insert into EXPENSES values (5,530);
insert into EXPENSES values (6,180);
insert into EXPENSES values (8,450);
insert into EXPENSES values (9,250);
insert into EXPENSES values (10,160);
insert into EXPENSES values (12,350);
insert into EXPENSES values (13,130);
insert into EXPENSES values (14,300);
insert into EXPENSES values (15,140);
insert into EXPENSES values (16,550);
insert into EXPENSES values (18,170);
insert into EXPENSES values (19,150);

 

A common scenario is that not all the organisation entities will appear in the fact table, but they are still part of the hierarchy.  We want to ensure that our reporting is accurate, and not lose and information.  To achieve this we should use a left outer join on the dimension table.

 

We now have a simple calculation view with a fact table, dimension and a parent child hierarchy.

 

Switching to Analysis Office, we can report against our parent-child hierarchy. Notice how all members are returned, including the parent and child members where there are no expense amounts.

The same is true for SAP Analytics Cloud

Assigned tags

      21 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Ian,

      Thanks for this informative post on parent child hierarchies in hana.

      We have been facing an issue with parent child hierarchies for Engineering BOMs in which the same child can appear multiple times under the same parent, and i believe the above logic ignores these duplicates and returns results.

      Also in scenarios where the number of records to process is more than a million then the hierarchy logic in calculation view starts to hang.

      Let me know your thoughts on these encountered issues.

      Deep

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      Thanks Deepesh,

      If you are experiencing undesired behavior then I would log that with support and get it investigated.
      I have not come across the scenario that you describe, but having the same child multiple times could result in a many to many situation, which we all know is bad.
      I am sure there would be a way to handle the scenario, the solution usually depends on the desired report/outcome. Potential options to consider

      1. Using the label column and having distinct labels / IDs.
      2. Reversing the logic and store the child occurrences within the fact to count the instance of a child within that BOM.
      3. Model the data in a relational way.
      4. Use a bridge table to avoid the many to many relationship between fact and dimension tables.
      Author's profile photo Effie Zheng
      Effie Zheng

       

      if we can have a share about SAC use Hana hierarchy will be great.

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      Thanks Effie,

      I am using HANA and SAC, I will see what can be shared.

       

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      I have now updated the above blog, showing the parent child hierarchy visualised in SAP Analytics Cloud.

      Author's profile photo L. BI
      L. BI

       

      I am working on a scenario to test my Analytic Privilege on Hierarchy works on AFO or SAC.

      When I try to access CV on AFO without SQL Analytic Privilege I get the data in proper hierarchy representation

      But when I apply Analytic privilege defined on Hierarchy node I get  error as below in AFO and SAC

      "Instantiation of calculation model failed;exception 306105: Invalid query. Failed to find '----Node' at node '__starJoinOutputNode__OLAP_OP__"

      Please let me know your thoughts.

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      Hi there,

      I haven't come across this error before, it could be related to the Analytic Privilege.

      It's probably best to log it with our support team and they can investigate with you.

       

      Author's profile photo Graham Johanson
      Graham Johanson

       

      Hi Ian,

      I'm trying to consume a parent child hierarchy on a calculation view in BOBJ analysis v2.3 but I don't get the hierarchy rollup offered ... only the child/parent columns separately (see image).  Do you have any ideas?

      Thanks,

      Graham

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      Hi Graham,

      I would check the version of Analysis for Office supports the HANA revision that you are using.

      One or both of them may need to be changed?

       

      Author's profile photo Quynh Doan Manh
      Quynh Doan Manh

      Hello Ian Henry

      May I ask where could i find that information? I got the same issue where hierarchy is not rollup. my hana is 1.0 SP 12 and Analysis Office 2.6

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      The Product Availability Matrix (PAM), shows the compatible (tested) products.

      https://support.sap.com/content/dam/launchpad/en_us/pam/pam-essentials/SAP_Analysis_MS_26.pdf

      The PAM states
      "All HANA 1.x SPS > = 10 are supported as long as they are in maintenance."

      I would enable the trace in Analysis Office and check for messages/errors.

      Author's profile photo Former Member
      Former Member

      Can you please share some  targeted information for ragged hierarchies?

      Author's profile photo Former Member
      Former Member

      Can you publish either an example of recursive CTE or working hierarchy function? I haven't had any luck with either. The idea is to replace Oracle "connect by" queries. Recursive CTE is a SQL standard.

      Author's profile photo William Andrade Batista
      William Andrade Batista

      Do you know how to reverse sign from some nodes?

      Author's profile photo Kiran K
      Kiran K

      Hello Ian Henry

      I am facing this issue with HANA Hierarchy node variable.

      Need your inputs on the HANA Variable which loads its list of Values in Hierarchical way as per business requirement.

      For that I have created a Hierarchy and When I call that Hierarchy in Inputparameter/ Variable for list of Values it is taking lot of time to load.

      what can be changed to get the Values loaded fast?

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      Hi Kiran,

      This type of question is best posted on the answers page.

      Please see https://answers.sap.com/index.html

      Thanks, Ian.

       

      Author's profile photo Eddie Ng
      Eddie Ng

      Great blog Ian! just two questions

      1. i can't seem to have the option of enabling the hierarchy for SQL access on the calculation view, we are on HANA 2.0 SP3
      2. from the blog, in the analytic privilege's, are we able to defined procedure to the hierarchy restriction? we are trying to retrieve hierarchy node restriction dynamically

      thanks Ian!

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      Hi Eddie,

      1. Yes, that option should be there. You should double check your CV has a star join, I think that's needed. Check your HANA Studio is fairly recent too.
      2. I haven't tried that but I expect that would be possible with either SQL or or a Dynamic restriction based upon a procedure

      Cheers, Ian.

      Author's profile photo Kyrill Köhn
      Kyrill Köhn

      Hi Ian,

       

      Thank you for the very comprehensive description of dealing with hierarchies. It was a big help for me to understand the doing of it.

      I have one question though. Do you know of any handling for hierarchy tables from ERP systems, like S4/HANA? So basically when putting hierarchy tables from S4, you get a table with several hierarchies inside. Sadly the Hana can not handle this type of tables, as far as I experienced it (It throws a 'multiple parents are not allowed' error). Is seperating the hierarchies from the table and creating several Calculation Views the only approach to that?

       

      Best regards,

      Kyrill

      Author's profile photo Ian Henry
      Ian Henry
      Blog Post Author

      Hi Kyrill,

      I haven't tried with an S4 hierarchy table, but we do have some options that may help.
      There is the Advanced option for "Multiple Parents", alternatively you could create surrogate keys.  Additionally the "Time Dependency" options could be used to reduce the valid hierarchies.

      Let me know how you get on.
      Cheers, Ian.

      Author's profile photo Kyrill Köhn
      Kyrill Köhn

      Hi Ian,

      Thanks for your ideas on that topic. We were able to find a solution with the help of input parameters, which we used to filter for the right hierarchy before showing them in AFO.

      I have one more question though. Do you have any idea how to add the "Attributes" section to a hierarchy from a calc view? This is something you can do when using BW hierarchies (see attached screenshot). I'm wondering if there is a way to get the same result with calculation views. Sadly the "Additional Attributes" option in the hierarchy setting does not show any effects.

      BW%20hierarchy%20in%20AFO

      BW hierarchy in AFO

       

      Best regards,

      Kyrill