SCD stage: loading dimension and fact table in the same job

ecote · Post by **ecote** » Wed Jan 23, 2013 9:25 am

Hi everyone,

I just a simple question about the SCD stage.

Question: Does the SCD stage guarantees that the dimension table record will be inserted prior to inserting the fact table record with the dimension's SK? Referential integrity is implemented between the 2 tables so that's why I'm concerned.

I'm guessing there is no guaranty and I have to load the 2 tables in 2 jobs with the proper order because of RI.

vmcburney · Post by **vmcburney** » Wed Jan 23, 2013 6:34 pm

IBM have designed the SCD stage so that dimensions and facts are written out in the same job. I don't know if the stage guarantees that the writes happen in the right order - and I think you could get duplicate rows to the dimension tables when the same new dimension data appears on more than one incoming row. I always felt it was better to handle them in separate jobs.

If you keep it in the same job you may be able to write multiple output links to the same Oracle connector stage and see if it can write data across the multiple tables as a single transaction.