Load Multiple Tables, One Job Design Question

[iq_etl]

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.

[chulett]

We'd really rather not have to write conversions for all of these

First thought? Don't. Just because you are moving to Parallel jobs doesn't mean every single Server job must be rewritten in Parallel. Use PX for new jobs where appropriate. Start the rewrite with those Server jobs that would most benefit from it.

As to your redesign, I'm firmly in the camp that designs of that nature are a Bad Idea. I would much rather have multiple loads that I can run in parallel via my scheduling mechanism and which allow us to handle issues / failures with individual loads on an individual basis. This rather than having to rerun the whole dang thing when one small load fails.

[iq_etl]

Very valid point. Being sort of new to parallel vs server, do you have a good guide as to use which when? One other thing, we'd rather have all of our jobs in our new 9.1 instead of having to toggle between new development in 9.1 an older jobs in 8.7. Would you suggest just copying over the 8.7 jobs to 9.1 if we determine them to stay as server jobs?

[chulett]

Would you suggest just copying over the 8.7 jobs to 9.1 if we determine them to stay as server jobs?

Exactly... export from 8.7, import into 9.1 and you're ready to go.