Master Sequence Job Design

[nvalia]

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

[Mike]

You can build as many levels into your job sequences as you'd like. It's not uncommon to have child job sequences , grandchild job sequences, etc. If designed properly, the restart is just as simple as cramming everything into a single master sequence.

Mike

[ray.wurlod]

You do have a scheduler. It's called the Information Server scheduling service.

[nvalia]

Ray I believe you mean the Scheduler from the Director Client right.

Yes we have that but it is a pretty basic one, what I meant was something like an Enterprise Scheduler where I can build dependencies using it between jobs, scripts and other components involved in the End to End ETL Cycle

Using this Information Server Scheduling Service we still need to build all inter dependencies in a Master Sequence Job

[priyadarshikunal]

I prefer having multiple sequence levels for better maintenance after wards depending on number of jobs to be sequenced.

[qt_ky]

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

Yes, it's a reasonable approach and would be easy to maintain and be restartable. I would put all logic and dependencies into the sequence jobs and keep it in DataStage where I think it belongs. Just place the top level sequence job on a schedule. Any scheduler will do.