- SAP Managed Tags:
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.
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
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
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.
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
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
21 Comments
