Loading a parent-child table

A parent-child table is a table in which there is a self-referencing relationship. In other words, there is a hierarchical relationship among its rows. A typical example of this is a table with employees, in which one of the columns contains references to the employee that is above each employee in the hierarchy.

In this recipe you will load the parent-child table of the employees of Steel Wheels. The hierarchy of roles in Steel Wheels is as follows:

• A sales representative reports to a sales manager

• A sales manager reports to the vice-president

• A vice-president reports to the president

• The president is the highest level in the hierarchy. There is a single employee with this role

You will load all employees from a file. The following are the sample rows in that file:

EMPLOYEENUMBER|LASTNAME|FIRSTNAME|EXTENSION|EMAIL|OFFICECODE|JOBTITLE|REP_TO

1002|Murphy|Diane|x5800|[e-mail] |1|President|

1056|Patterson|Mary|x4611|[e-mail] |1|VP Sales|[e-mail]

1076|Firrelli|Jeff|x9273|[e-mail] |1|VP Marketing|[e-mail]

1088|Patterson|William|x4871|[e-mail] |6|Sales Manager (JAPAN, APAC)|[e-mail]

...

As you can see, among the fields you have the e-mail of the employee who is above in the hierarchy. For example, Gerar Bondur is a Sales Manager, and reports to the employee with e-mail [e-mail], that is, Mary Patterson.

Getting ready

In order to run this recipe, either truncate the employees table in Steel Wheels, or create the table employees in a different database.

How to do it...

1. Create a transformation that inserts the record for the president who is first in the hierarchy and doesn't report to anyone. The transformation should read the file, filter the record with JOBTITLE=President, and insert the data into the employees table.

2. Create another transformation to load the rest of the employees. Define a named parameter named LEVEL that will represent the role of the employees being loaded.

3. Use a Text file input step to read the file of employees.

4. Use a Get Variables step to add the variable LEVEL as a new field named level.

5. Use a Join rows step to merge the employee data with the level the transformation will be filtering on. Leave the condition field empty so that the level from the Get Variables step will be added to each record.

6. Add a Filter rows step to filter the employees to load based on their role. In order to do that, enter the following condition: JOBTITLE REGEXP level.

7. Add a Database lookup step to find out the employee number of the employee who is one above in the hierarchy. In the upper grid, add a row with the condition EMAIL = REP_TO. Use the lower grid to get the field EMPLOYEENUMBER and rename it to REPORTSTO.

8. Add a Dummy step to send employee records that do not have an employee record parent to. This step will act as an error handling step.

9. Add a Table Output step and use it to insert the records in the table employees. Your final transformation looks like the following:

   

10. Finally, create a job to put everything together. Drag a START entry and four Transformation job entries to the work area. Link all of them in a row.

11. Use the first Transformation entry to execute the transformation that loads the president.

12. Double-click on the second Transformation entry and configure it to run the transformation that loads the other employees. Under the Parameters tab, add a parameter named LEVEL with value VP.*.

13. Repeat step 12 for the third Transformation entry, but this time, type .*Manager.* as the value for the LEVEL parameter.

14. Repeat step 12 for the fourth Transformation entry, but this time, type Sales Rep.* as the value for the LEVEL parameter.

15. Save and run the job. The table should have all employees loaded, as you can see in the following query:

    SELECT
       EMPLOYEENUMBER N
     , LASTNAME
     , REPORTSTO
     , JOBTITLE
    FROM employees;
    +------+-----------+-----------+----------------------------+
    | N    | LASTNAME  | REPORTSTO | JOBTITLE                   |
    +------+-----------+-----------+----------------------------+
    | 1002 | Murphy    |      NULL | President                  |
    | 1056 | Patterson |      1002 | VP Sales                   |
    | 1076 | Firrelli  |      1002 | VP Marketing               |
    | 1088 | Patterson |      1056 | Sales Manager (JAPAN, APAC)|
    | 1102 | Bondur    |      1056 | Sale Manager (EMEA)        |
    | 1143 | Bow       |      1056 | Sales Manager (NA)         |
    | 1165 | Jennings  |      1143 | Sales Rep                  |
    | 1166 | Thompson  |      1143 | Sales Rep                  |
    | 1188 | Firrelli  |      1143 | Sales Rep                  | | ...  | ...       |      ...  | ...                        |
    +------+-----------+-----------+----------------------------+
    23 rows in set (0.00 sec)
    

How it works...

If you have to load a table with parent-child relationships, loading all at once is not always feasible. Look at the sampledata database. There is no physical foreign key from the REPORTSTO column to the EMPLOYEENUMBER column, but if the foreign key had existed, it would fail because of the foreign key constraint. Not only that; in this case loading all at once would be impossible because in the file you missed the ID of the parent employee loading all records needed for the REPORTSTO column.

So, in this recipe there was one possible solution for loading the table. We loaded all employees, one role at a time, beginning with the president and followed by the roles below in the hierarchy. The transformation that loaded the other roles simply read the file, kept only the employees with the role being loaded, looked for the ID of the parent employee in the hierarchy, and inserted the records. For the roles you could have used fixed values, but you used regular expressions instead. In doing so, you avoided calling the transformation once for each different role. For example, for loading the vice-presidents you called the transformation once with the regular expression VP.* which matched both VP Sales and VP Marketing.

See also

• Inserting or updating rows in a table