Load Multiple Tables, One Job Design Question
Posted: Mon Dec 16, 2013 8:35 am
We're in the process of converting 8.7 server to 9.1 parallel. We have hundreds of jobs, and considering perhaps consolidating some. For example, for a given user area we may have 5-10 jobs that are very basic loads of simple tables to be used as lookup tables. This jobs simply have a source sequential file, a transformer stage, and a load to an oracle connector stage.
We'd really rather not have to write conversions for all of these, but more importantly look to streamline our projects for future maintenance.
So what are the community's thoughts on the following.
1. Our non-DS source jobs will write the data used for multiple lookup tables to one large dataset/sequential file. The data of the different tables will be separated by the table name, or the tablename will be the first piece of data in the record.
2. The DS job will have this large block of data as the source.
3. There will be one transformer stage with the one input, but multiple outputs depending on what it finds as the table name in the data. The idea is that the transformer stage will 'decide' which oracle connector to send the data to.
This seems like it would work and fairly straightforward, but what would be some potential downfalls? Will we actually be making a maintenance nightmare? Is there a better combination of stages to use?
Any suggestions would be fantastic.
We'd really rather not have to write conversions for all of these, but more importantly look to streamline our projects for future maintenance.
So what are the community's thoughts on the following.
1. Our non-DS source jobs will write the data used for multiple lookup tables to one large dataset/sequential file. The data of the different tables will be separated by the table name, or the tablename will be the first piece of data in the record.
2. The DS job will have this large block of data as the source.
3. There will be one transformer stage with the one input, but multiple outputs depending on what it finds as the table name in the data. The idea is that the transformer stage will 'decide' which oracle connector to send the data to.
This seems like it would work and fairly straightforward, but what would be some potential downfalls? Will we actually be making a maintenance nightmare? Is there a better combination of stages to use?
Any suggestions would be fantastic.