Master Sequence Job Design
Posted: Thu Apr 23, 2015 1:03 pm
Hi All,
We do not have a Scheduler and hence we are planning to design a Master Sequence job by which we will control the end-end ETL cycle, right from polling for a Trigger File to start the Cycle and then load Staging Tables followed by Ref tables followed by Dim tables followed by Fact Tables and finally Post Processes. This one Master Seq job will be scheduled using Windows Task Scheduler via a Script
My question is, do we build individual Sequence for each of these class of objects, like StgSeq, RefSeq, DimSeq, FactSeq to group them togather and then call these Sequence jobs in the Master Seq (Concept of Master and Child Seq) so we segregate them and it becomes easy to add more Dims/Facts in next phase of the project, rather than directly adding the jobs in the Master Seq
(Individual Dim/Fact build themselves are Seq jobs)
What is a better approach from Restart-ability, future change ability, best practices perspective and a less complicated design?
Thanks,
NV
We do not have a Scheduler and hence we are planning to design a Master Sequence job by which we will control the end-end ETL cycle, right from polling for a Trigger File to start the Cycle and then load Staging Tables followed by Ref tables followed by Dim tables followed by Fact Tables and finally Post Processes. This one Master Seq job will be scheduled using Windows Task Scheduler via a Script
My question is, do we build individual Sequence for each of these class of objects, like StgSeq, RefSeq, DimSeq, FactSeq to group them togather and then call these Sequence jobs in the Master Seq (Concept of Master and Child Seq) so we segregate them and it becomes easy to add more Dims/Facts in next phase of the project, rather than directly adding the jobs in the Master Seq
(Individual Dim/Fact build themselves are Seq jobs)
What is a better approach from Restart-ability, future change ability, best practices perspective and a less complicated design?
Thanks,
NV