Generating BI User Group Hierarchy Based on Company Department Levels
I. Requirement Background
Background: A client company has been established for a long time with complex department hierarchies and a large number of employees.
Requirement 1:
When BI goes live, it's necessary to reference the company's department hierarchy to plan and process the corresponding BI user group hierarchy, with employees from corresponding departments assigned to appropriate user groups.
e.g., All people from Retail District 1 and District 2 are assigned to the Retail Group 1 user group, while all people from Retail District 3 and District 4 are assigned to the Retail Group 2 user group.
Figure 1: Example of Company Department Hierarchy and BI User Group Hierarchy
Requirement 2:
When personnel changes occur in the company (onboarding, offboarding, transfers, etc.), the BI account's user group assignment should also change accordingly to reduce the cost of manual user group maintenance.
II. Basic Data Sources and Target Datasets
1. Basic Data Sources
First, obtain the company's department hierarchy through the Account Dataset function, which provides two tables: employee table and company department hierarchy table, as shown in Table 1 and Table 2 respectively. The employee table records each employee's employee number, name, email, department, and other information. The department hierarchy table records the company's department hierarchy with fields such as deptid, name, parent_id, etc.
| name(Name) | userid(Employee ID) | email(Email) | deptid(Department ID) |
| Zhang San | A001 | zhangsan@guandata.com | retail101 |
| Li Si | A002 | lisi@guandata.com | retail101 |
| Wang Wu | A003 | wangwu@guandata.com | retail101 |
| Zhao Qi | A004 | zhaoqi@guandata.com | retail201 |
| Qian Ba | A005 | qianba@guandata.com | retail201 |
Table 1: Company Employee Table
| deptid(Department ID) | name(Department Name) | parent_id(Parent Department) |
| retail101 | Retail District 1 | retail1 |
| retail102 | Retail District 2 | retail1 |
| retail103 | Retail District 3 | retail2 |
| retail104 | Retail District 4 | retail2 |
| retail1 | Retail Department 1 | retail |
| retail2 | Retail Department 2 | retail |
| retail | Retail Department | all |
| all | Company-wide | null |
Table 2: Company Department Hierarchy Table
2. Target Datasets
Combining the requirement background, basic data sources, and account synchronization functionality, we can design two target datasets that need to be organized: BI User Group Hierarchy and Employee Information Summary Table, as shown in Table 3 and Table 4 respectively.
| BI User Group ID | BI User Group Name | BI Parent User Group ID |
| BI_001 | Master User Group | null |
| BI_002 | Retail Group | BI_001 |
| BI_003 | R&D Group | BI_001 |
| BI_004 | Retail Group 1 | BI_002 |
| BI_005 | Retail Group 2 | BI_002 |
Table 3: BI User Group Hierarchy
The BI User Group Hierarchy in Table 3:
-
Organized based on client requirements, showing the BI user group hierarchy needed by the client
-
Will be used in account synchronization to create various levels of user groups in BI
| name | userid | Department | parent*1 | parent*2 | parent*3 | BI User Group ID | BI User Group Name |
| Zhang San | A001 | Retail District 1 | Retail Department 1 | Retail Department | Company-wide | BI_004 | Retail Group 1 |
| Li Si | A002 | Retail District 2 | Retail Department 1 | Retail Department | Company-wide | BI_004 | Retail Group 1 |
| Wang Wu | A003 | Retail District 3 | Retail Department 2 | Retail Department | Company-wide | BI_005 | Retail Group 2 |
| Zhao Qi | A004 | Retail District 4 | Retail Department 2 | Retail Department | Company-wide | BI_005 | Retail Group 2 |
| Qian Ba | A005 | Retail Department 1 | Retail Department | Company-wide | null | BI_004 | Retail Group 1 |
| Sun Jiu | A006 | Retail Department 2 | Retail Department | Company-wide | null | BI_005 | Retail Group 2 |
| Li Shi | A007 | Retail Department | Company-wide | null | null | BI_002 | Retail Group |
| Zhou Er | A008 | Company-wide | null | null | null | BI_001 | Master User Group |
Table 4: Employee Information Summary Table
The Employee Information Summary Table in Table 4:
-
Employee information such as name, userID, department, etc. is directly obtained from the company employee table shown in Table 1
-
parent1, parent2, parent*3... represent the employee's superior department, superior's superior department, superior's superior's superior department... which can intuitively show all superior departments of an employee, facilitating employee information management for clients. The field generation process please refer to the "Company Department Hierarchy Information Organization" section below.
-
Explanation of parentN terminology: refers to the Nth level superior department of a certain department. Taking Table 4 as an example, Retail District 1's parent1 is Retail Department 1, its parent2 is Retail Department, and parent3 is Company-wide; Retail Department 1's parent1 is Retail Department, and parent2 is Company-wide.
-
parentN_id refers to the Nth level superior department ID of a certain department, parentN_name refers to the Nth level superior department name of a certain department
-
The BI user group ID and name that the employee belongs to will be used in the account synchronization function to assign employees to their respective BI user groups. The field generation process please refer to the "Assigning Employees to BI User Groups" section below.
III. Implementation of Requirement 1
1. Step1 - Company Department Hierarchy Information Organization
The company department hierarchy table in Table 2 (deptid, name, parent_id) is suitable for storing all department hierarchy relationships in the company, but it's not conducive for users to intuitively and quickly understand all superior department levels of a certain department. The presentation form in Table 4 (department, parent1, parent2, parent*3) is more aligned with user habits.
So how do we transform the department hierarchy table in Table 2 into the presentation form in Table 4?
Currently, there's no perfect method, but we recommend the following approach. If anyone has better methods, we welcome additions: Through the correspondence relationship between dept_id and parent_id, left join parent_id with dept_id to get parent2_id, and similarly get parent3_id, parent4_id, parent5_id... parent*N_id, as shown in the figure below.
Figure 2: Company Department Hierarchy Information Organization
However, the size of N cannot be determined yet. If N is too small, we cannot completely obtain all superior departments of the department. To determine the value of N, we can first understand the client's current organizational hierarchy levels. Do multiple left joins first, until the value of a certain parent*N_id is all null. At this point, the hierarchy level N is the client's current organizational hierarchy level (as shown in Figure 2, the organizational hierarchy level is 4, i.e., N=4).
Actual situation reference: A client with over 18,000 DingTalk users has a company department hierarchy level of 9, i.e., N=9
Usually, organizational hierarchies are relatively stable and won't have major changes. To be safe in case the client makes adjustments related to adding new organizational hierarchies, add a few more hierarchy levels based on the current hierarchy level N, such as N+5, which can basically ensure that the value of parent*N+5_id is all null, thus obtaining the complete superior department hierarchy for each dept_id. At this point, the formula for summarizing all superior department hierarchies can be written as: concat(department name, parent1_name, parent2_name... parentN_name... parentN+5_name)
2. Step2 - Assigning Employees to BI User Groups
There are two methods to achieve this.
Method 1: Using fuzzy matching.
If the user's department or its superior department hierarchy contains certain keywords, it belongs to a certain user group. For example, if a department's superior department hierarchy contains the keyword "Retail Group 1", then that department belongs to the "Retail Group 1" user group.
For example: Superior department hierarchy = concat(department name, parent1_name, parent2_name, parent*3_name......)
So the fuzzy matching formula is: case when superior department structure like "%Retail Group 1%" then "Retail Group 1" else ... end
This can directly get the correspondence relationship of userid-department-BI user group name.
-
The advantages of this method are:
- If a new department belonging to "Retail Group 1" appears in the future, through fuzzy judgment of the department's superior department hierarchy, it can automatically be assigned to the "Retail Group 1" user group, saving the cost of manual maintenance;
-
The disadvantages are:
-
Changes in department names will make the judgment conditions invalid. For example, when "Retail Group 1" is changed to "Sales Group 1", the fuzzy matching conditional statement must also be modified accordingly
-
The maintenance of fuzzy matching conditions will definitely be handed over to clients for self-maintenance after product launch, which requires clients to have basic SQL statement writing capabilities (mainly case when statements). Some clients don't have this capability, and their learning ability is also poor, making it difficult to master this skill
-
In small companies or companies with simple user group hierarchies, using fuzzy matching to maintain user groups is acceptable, but in large companies or companies with complex user hierarchies, there will be many user groups, meaning there will be many judgment conditions, and the case when statement will be very long, such as case when superior structure like "%Retail Group 1%" then "Retail Group 1" when superior structure like "%Retail Group 2%" then "Retail Group 2" ... else ... end. Overly long statements are inconvenient to maintain in ETL.
-
Method 2: Manually organize the correspondence relationship table between department ID and user group ID (as shown in the table below), and match this correspondence relationship with the company employee table (Table 1) to obtain the BI user group that employees belong to.
| deptid | Company Department Name | BI User Group ID | BI User Group Name |
| dept_8 | Company-wide | BI_001 | Master User Group |
| dept_7 | Retail Department | BI_002 | Retail Group |
| dept_9 | R&D Department | BI_003 | R&D Group |
| dept_5 | Retail Department 1 | BI_004 | Retail Group 1 |
| dept_1 | Retail District 1 | BI_004 | Retail Group 1 |
| dept_2 | Retail District 2 | BI_004 | Retail Group 1 |
| dept_6 | Retail Department 2 | BI_005 | Retail Group 2 |
| dept_3 | Retail District 3 | BI_005 | Retail Group 2 |
| dept_4 | Retail District 4 | BI_005 | Retail Group 2 |
Table 5: Department ID and User Group ID Correspondence Table
-
The advantages of this method are:
-
No need to write SQL statements
-
Use department ID instead of department name to maintain correspondence relationship with user groups, avoiding trouble caused by later department name changes
-
-
The disadvantages are:
-
When adding a new department, manual maintenance of the correspondence relationship between the new department ID and user group is required, and it cannot automatically plan to user groups based on its superior hierarchy
-
Maintaining correspondence relationships in the form of department IDs is not as intuitive as department names
-
In an actual project implementation, after careful consideration, it was believed that the company's organizational structure is generally mature, and the possibility of adjusting the organizational structure is relatively low, so the second method was ultimately chosen.
IV. Implementation of Requirement 2
After completing the two target datasets, account synchronization configuration can be performed in the BI backend. The system will automatically generate BI user groups and perform basic personnel attribute settings (Account synchronization help documentation: Account Synchronization). The figure below shows the account synchronization configuration page in a client environment for reference.
