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
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