cancel
Showing results for 
Search instead for 
Did you mean: 
DustinB
Employee
Employee

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.

DustinB_0-1646254641269.png

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:

  1. Add a new Aggregated Table insight to your dashboard and open for edit
  2. Drag a Dimension into the Grouping Dimension list (Employee Name in our example)
  3. Drag a Measure into the Measure list (Employee Salary in our example)
  4. Edit Insight settings and ensure Merge Columns is enabled
  5. Click on the Grouping Dimension blue pillbox to open the configuration flyout menu
  6. 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)
  7. Save and review
DustinB_1-1646254641255.png

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:

DustinB_2-1646254641305.pngAdditional 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

Related Material

Best Practices Index
Best Practices

Just here to browse knowledge? This might help!

Contributors
Version history
Last update:
‎03-08-2022 01:54 PM
Updated by: