.png "Employee"){kind=icon}
Introduction
If you need to represent hierarchical data in Incorta, we provide several features that make this data modeling technique easier. Common use cases for hierarchy include HR org chart, chart of accounts, and even your Incorta dashboards' folder structure. This article will present an overview of hierarchy features as well as links to more detailed information.
What you should know before reading this article
We recommend that you be familiar with these Incorta concepts before exploring this topic further.
Applies to
These concepts apply to all releases of Incorta both on-premise and cloud.
Let's Go
Self-Join
To get started with hierarchy in Incorta, you must first model a self-join within a table defined in your physical schema. In data modeling, hierarchical relationships are also referred to as entity relationships or parent child relationships. To leverage a self-join, your Incorta table must include both the child unique identifier as well as the parent unique identifier. For a practical example, let's look at modeling a Human Resources organization chart. As it happens, Incorta includes a sample HR schema that we can use to demonstrate hierarchy. In this sample schema, there is an Employees table that contains a row for each employee. Each row contains the employee's unique identifier "Employee Id" as well as a column called "Manager Id". That Manager Id is the Employee Id for this person's direct manager. In the brief sample below, Steven is the top-level manager (note that his Manager Id is null) with Adam reporting to him. Alexis reports to Adam.
Employee Id | Employee Name | Manager Id |
100 | Steven King |
|
121 | Adam Fripp | 100 |
185 | Alexis Bull | 121 |
To implement hierarchy, we must create a join in the HR schema from EMPLOYEES.MANAGER_ID to EMPLOYEES.EMPLOYEE_ID. Note that it is important that the Manager Id be on the left of the Incorta join (the "child") and Employee Id be on the right (the "parent" join). This may seem counter-intuitive as the Manager is logically the parent and the employee is the child but the key point is Incorta joins are child-to-parent or many-to-one and the Manager Id will be repeated multiple times in the Employees table as more than one employee can have the same manager.
Refer to this documentation page for additional detail on how to implement a Self-Join in Incorta: Self-Join Relationships in Incorta
Insight Hierarchy Support
With the physical data model configured with the self-join we can now configure a table insight to display the data in a hierarchical format. The first technique is to let Incorta render the hierarchy automatically based on the self-join. The steps are simple:
- Add a new Aggregated Table insight to your dashboard and open for edit
- Drag a Dimension into the Grouping Dimension list (Employee Name in our example)
- Drag a Measure into the Measure list (Employee Salary in our example)
- Edit Insight settings and ensure Merge Columns is enabled
- Click on the Grouping Dimension blue pillbox to open the configuration flyout menu
- Set Hierarchy Depth to the level of display required, 4 is adequate in our example as there are no more than 4 levels from Steven King to the lowest level employee (Levels 0, 1, 2, and 3)
- Save and review
Flattened Hierarchy View
Quite often there will be a requirement to present the hierarchical data in a flattened view. Rather than present the data in a Tree structure, the hierarchy is flattened so multiple leaves can be included in a single row of transaction data. Commonly you might see this in financial reporting. When displaying summary data for an account you may also want to display the parent account on the same row. In our example that would look like the following:
Employee | Manager | Salary |
Alexis Bull | Adam Fripp | 10500 |
Ellen Abel | Eleni Zlotkey | 9750 |
One way to achieve this in Incorta is to use an Incorta Analyzer table (one of the Derived Table options when selecting NEW+) within the HR Schema. In the Analyzer view for the table editor, configure an Aggregated Table. In the Grouping Dimensions list, add the Employee Name and Employee Id for each level (or the desired levels) in the hierarchy. Enable the Hierarchy option on each dimension (both Employee Name and Employee Id). In our example, we are going to set levels 0 through 3 and name the columns appropriately. For our example, that would look like the following:
Additional Features and Considerations- A table is allowed only one self-join
- The top-most entity in the hierarchy should have NULL for parent identifier
- Incorta automatically adds two columns to every table with a self-join:
- Level: An integer representing an item's level of the hierarchy (0-based indexed)
- Is Leaf: A boolean indicating true if the item is at the bottom of a particular branch
- Incorta provides a built-in function for use within formula columns:
- descendantOf(): Used to check whether a given value is within the hierarchy. See details here.
