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.
We recommend that you be familiar with these Incorta concepts before exploring this topic further.
These concepts apply to all releases of Incorta both on-premise and cloud.
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.
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
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:
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:
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