In the blog "Parent-Child Hierarchies," we see that in certain data models it makes perfect sense to create a Parent-Child Hierarchy.
For example, when you want your data model to emulate organizational structures within a company, or when you want to highlight hierarchical dependencies between
So now, let's see how we can actually do that in SAP Data Warehouse Cloud.
In the following example, we would like to see the expenses of each employee and his/her respective manager.
We want the data story I'll create to show me something like this:
Here, I see the Managers on the first level of the hierarchy, and their employees on the second level of the hierarchy on the left, and on the right-hand side, we see the expenses in accordance to this hierarchy.
So, now let's see how we get there:
First, go to the Data Builder and click the Space you want to work in.
Select or upload the data you need. In this example, we're uploading .csv files into the Data Builder.
Make sure to define the data types properly.
We do this for all data sets we need for this view. So, we'll do this for the Employee_1 table, the Expense_1 table.
Before we select a New Graphical View and model my data, I have to determine the proper hierarchy.
For this, we'll click on the table I just uploaded into the Data Builder.
We'll now see the properties of this data.
In order to define a hierarchy, we have to set the Type, of course, to a Dimension, as we're defining Semantics in our data.
Next, click on the little "Hierarchy" icon on the top menu. The icon looks like a little set of stairs.
We can now add a hierarchy to this data set. As there is only one manager for several employees, we'll work with a Parent-Child Hierarchy.
As parent column, we'll set the ManagerID.
For the child column, we'll select the EmployeeID.
Remember to give your Hierarchy a name.
Click "Close" and then "Save" all of it by clicking on the Save icon in the top left corner.
Next, we want to also make sure that we'll not only see the ID numbers of the Managers and Employees!
To do so, we'll go into the Attributes and under "Semantic Type" for the EmployeeName, we'll select "Text".
And under "Labels" for the EmployeeID, we'll select now "Employee Name".
Again, we'll save everything.
Now, we can go ahead, deploy it, and build our data model in the Data Builder's Graphical View.
Click on "Data Builder" in the menu on the left-hand side.
Choose "New Graphical View".
And then proceed to build your Analytical Data Set.
First, pull the Employee table into the canvas.
Then go into the Association View.
and link the Expenses table to the Employee table.
Make sure that the tables are properly matched to each other. In our example here, we want the EmployeeID to be mapped.
Important: make sure to set the Type to "Analytical Dataset" and select at least a Measure. In our example, we'll set the measure to "Amount," meaning the employee's expenses.
Save the model, and give it a name.
And then deploy it. We're getting ready for our data story.
Click on the "Story Builder" icon on the left-hand side. This will take us to the SAP Analytics Cloud.
Click on "Create Story".
Select your Data.
Now choose the object for the data story,
From the right-hand menu, add the measure i.e. the amount, and the dimension, i.e. the employee ID, because that is what you'd like to visualize.
And voila, here it is. A table that illustrates the organizational hierarchy and the associated expenses to each employee.