How to deal with 1 to many SQL (Table inputs) in Pentaho Kettle

Question

I have a situation where in i have the following tables.

Employee - emp_id, emp_name, emp_address

Employee_assets - emp_id(FK), asset_id, asset_name (1-many for employee)

Employee_family_members - emp_id(FK), fm_name, fm_relationship (1-many for employee)

Now, I have to run a scheduled kettle job which reads in the data from these tables in say batches of 1000 employees and create a XML output for those 1000 records based on the relationship in DB with family members and assets. It will be a nested XML record for every employee.

Please note that the performance of this kettle job is very crucial in my scenario.

I have two questions here -

1. What is the best way to pull in records from the database for a 1-many relationship in schema?
2. What is the best way to generate the XML output structure given that XML join steps are a performance hit?

Answer 1

To pull data in you can use multiple db lookup fields or a Database Join step. Performance wise I would think that the join would likely be faster but that's all dependent on the complexity of the query you use and how it's written etc.

Answer 2

Here is how I have achieved this.

So, there is one Table Input step to read the base table and subsequently create the XML chunk for it. Subsequently, in the flow, I am using the 1-many relationship (child table) as another Database join step passing the relationship key to it. Once the data is pulled out, the XMLs are generated for the child rows. This is then passed on to the Modified Java Script Value step(merge rows) which then merges the content using trans_Status = SKIP_TRANSFORMATION for similar rows. Once similar rows are merged/concatenated, the putRow(row) is used to dump it out as an output to the next step. Please note, that this required the SQL to have order by/sorted based on the relationship keys. This is performing alright, so I can proceed with it.