Skip to main content

Parent-Child Dimension

Traditional hierarchies have a set of strict levels and members that follow these levels. For example, in the Product hierarchy, any member of the Product Name level has a parent node in the Brand Name level, and this parent node has a parent node in the Product Subcategory level, and so on. This structure can sometimes be too rigid to model real-world data (such as financial accounts, product line classifications, etc.).

A parent-child hierarchy has only one level (excluding the special "All" level), but any member can have a parent at the same level. A classic example is the supervisor reporting structure of employees. Below, we demonstrate creating an Employees dimension using the employee table in the Demo - FoodMart Model data model.

Creating an Employee Parent-Child Dimensionโ€‹

Create an employee dimension using the employee data table, enter the dimension name as Employees, label as Employee, and click apply to successfully create and navigate to the employee dimension interface.

  1. Click the more button on the Employee tab in the navigation bar, select (edit) Hierarchy, which will open the hierarchy property editing interface, and set the Primary Key to employee_id;
  2. Open the employee table structure, find the employee_id column and drag it to the Level area, where you can see the corresponding data preview;
  3. Select the employee_id level, open the level property editing interface, and enter the following level information:
    1. Name: Employee Id
    2. Caption: Employee
    3. Column: employee_id
    4. Unique Members: true
    5. Name Column: full_name
    6. Parent Column: supervisor_id
    7. Null Parent Value: '0'
  4. Save

Adding to Multidimensional Modelโ€‹

Create a Salary model using the salary data table, add the measure salary_paid, then add the employee dimension to the salary model, and change its shared dimension foreign key property to employee_id.

After saving, you can use the employee dimension's hierarchical relationships to perform hierarchical statistics on salary measures.

Adding Attributesโ€‹

You can also add attributes to the employee hierarchy, open the level property editing interface, click add in the attribute area, and add the following attributes:

  • // Format Name: Field
  • Marital Status: marital_status
  • Position Title: position_title
  • Gender: gender
  • Salary: salary
  • Education Level: education_level
  • Management Role: management_role

The Parent-Child hierarchy cannot set the Caption column, there is a bug that has not been resolved.

Closure Tableโ€‹

Edit the Employee Id level properties, enable the closure table, select the table as employee_closure, then choose the parent field as supervisor_id, and the child field as employee_id.

Creating an Account Parent-Child Hierarchyโ€‹

Create a parent-child hierarchy using the Account dimension as an example.

  1. Use the account data table to create the Account dimension
  2. Enter the information:
    1. Name: Account
    2. Caption: Account
    3. Table: account
  3. Click apply, navigate to the dimension page
  4. Open the hierarchy property editing page, enter the information:
    1. Primary Key: account_id
  5. Save

Creating an Expense Modelโ€‹

Create an expense multidimensional model using the expense_fact data table.

  1. Create the model, enter the following information:
    1. Type: Cube
    2. Name: Expense
    3. Caption: Expense
  2. Click apply
  3. Add the measure amount
  4. Add the Account dimension to this model
  5. Save

Verify the data.