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 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 a vice-president

• A vice-presidents 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. These are 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 the 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. 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.

6. Add a Database lookup 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.

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

   

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

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

10. Double-click 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.*.

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

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

13. Save and run the job. The table should have all employees loaded, as you can see below:

    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, loading all records at once 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 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 by 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. If you are not confident with inserting data into a table see this recipe.