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.
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
Alternatively, if Analysis for Office is not available then a 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.
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 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?
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?