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.
Here By this Example you will load the parent-child table of employees of Steel Wheels (Get it in pentaho Sample DB).
The hierarchy
of roles in Steel Wheels is as follows:
1) A sales representative reports to a sales manager
2)A sales manager reports to a vice-president
3) A vice-presidents reports to the president
4) The president is the highest level in the hierarchy. There is a single employee
with this role.
Now Start Doin...
Go to your Database And create "employee" table
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 he 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.*.
Working with Databases
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,
For more detail Ref:- Pentaho Data integration cook book
or wrire to me spectrumon@gmail.com
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.
Here By this Example you will load the parent-child table of employees of Steel Wheels (Get it in pentaho Sample DB).
The hierarchy
of roles in Steel Wheels is as follows:
1) A sales representative reports to a sales manager
2)A sales manager reports to a vice-president
3) A vice-presidents reports to the president
4) The president is the highest level in the hierarchy. There is a single employee
with this role.
Now Start Doin...
Go to your Database And create "employee" table
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 he 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.*.
Working with Databases
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,
For more detail Ref:- Pentaho Data integration cook book
or wrire to me spectrumon@gmail.com
No comments:
Post a Comment