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
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
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
if we can have a share about SAC use Hana hierarchy will be great.
Thanks Effie,
I am using HANA and SAC, I will see what can be shared.
I have now updated the above blog, showing the parent child hierarchy visualised in SAP Analytics Cloud.
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.
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.
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
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?
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
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.
Can you please share some targeted information for ragged hierarchies?
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.
Do you know how to reverse sign from some nodes?
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?
Hi Kiran,
This type of question is best posted on the answers page.
Please see https://answers.sap.com/index.html
Thanks, Ian.