Batch Orchestration (Core Scripts)

How to add a Master-Batch?

A master-batch is the natural entry point for a data pipeline and all the batches and load process steps (LPS)…

A master-batch is the natural entry point for a data pipeline and all the batches and load process steps (LPS) it contains. Usually, only this batch is called via the stored procedure uspBatchExecuteParallelLoadProcessStep.

ExMeX Companion App

Starting with version 0.2.0 of the ExMeX Companion App, the data modeler can add a master-batch in the ExMeX Companion App.

ExMeX Framework Core Scripts

As always, the data modeler or DevOp has to edit the file \etc\config\ConfigLoadBatchLogistic.sql.

The following SQL example shows how to add a sample master-batch 'ExMeX Master'. The same SQL example can also be used to convert an existing batch into a master-batch.

To define a master-batch, the parent batch must be NULL!

/*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- Insert Batch without Parent Batch                                          --

-- Master Level
MERGE INTO MetadataZone.LoadBatch AS target
	USING (	SELECT 'ExMeX Master'
		   ) AS source (BatchLabel)
	ON (target.BatchLabel = source.BatchLabel)
WHEN NOT MATCHED THEN
	INSERT (BatchLabel)
	VALUES (source.BatchLabel)
WHEN MATCHED THEN
    UPDATE SET 
    target.ParentLoadBatchId = NULL            
;
/*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/

It does matter where in the file the SQL is inserted. It should be at the very beginning, of course. Before children-batches or dependencies are inserted.

After reinstalling the metadata, the master-batch is added.

You can double check your batch hierarchy with

SELECT *
FROM MetadataZone.BatchHierarchy
Posted 1 year agoby Dirk.Lerner

How to add a Children-Batch (Hierarchy)?

A child batch usually contains one or more Load Process Steps (LPS), grouped or organised according to the type of…

A child batch usually contains one or more Load Process Steps (LPS), grouped or organised according to the type of LPS or for logical and logistical reasons. All LPS in a batch are executed in parallel by the ExMeX Framework Core, if possible, depending on the maximum parallality setting.

ExMeX Companion App

Starting with version 0.2.0 of the ExMeX Companion App, the data modeler can add one or more Children-Batch(es) in the ExMeX Companion App.

ExMeX Framework Core Scripts

As always, the data modeler or DevOp has to edit the file \etc\config\ConfigLoadBatchLogistic.sql.

The following SQL example shows how to add a sample children batch ‘ExMeX MD‘ (Level 1 in the hierarchy) to the master-batch 'ExMeX Master'. The same SQL example can also be used to reorganize a children-batch to another master- or children-batch.

/*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- Level 1

MERGE INTO MetadataZone.LoadBatch AS target
	USING (	SELECT 'ExMeX MD'
				  ,LoadBatchId
			FROM MetadataZone.LoadBatch
			WHERE BatchLabel = 'ExMeX Master'
		   ) AS source (
				 BatchLabel
				,ParentLoadBatchId
				)
	ON (target.BatchLabel = source.BatchLabel)
WHEN MATCHED THEN
	UPDATE SET 
		target.ParentLoadBatchId = source.ParentLoadBatchId
WHEN NOT MATCHED THEN
	INSERT (
		 BatchLabel
		,ParentLoadBatchId
		)
	VALUES (
			 source.BatchLabel
			,source.ParentLoadBatchId
			)
;
/*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/

The following SQL example shows how to add several sample children batches (‘ExMeX MD - Hub‘, ‘ExMeX MD - Link‘, ‘ExMeX MD - Satellite‘) (Level 2  in the hierarchy) to the children-batch 'ExMeX MD'. The same SQL example can also be used to reorganize a children-batch to another master- or children-batch.

/*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- Level 2

MERGE INTO MetadataZone.LoadBatch AS target
	USING (	SELECT 'ExMeX MD - Hub', LoadBatchId 
                  FROM MetadataZone.LoadBatch WHERE BatchLabel = 'ExMeX MD'
            UNION ALL
            SELECT 'ExMeX MD - Link', LoadBatchId 
               FROM MetadataZone.LoadBatch WHERE BatchLabel = 'ExMeX MD'
            UNION ALL
            SELECT 'ExMeX MD - Satellite', LoadBatchId 
               FROM MetadataZone.LoadBatch WHERE BatchLabel = 'ExMeX MD'
		   ) AS source (
				 BatchLabel
				,ParentLoadBatchId
				)
	ON (target.BatchLabel = source.BatchLabel)
WHEN MATCHED THEN
	UPDATE SET 
		target.ParentLoadBatchId = source.ParentLoadBatchId
WHEN NOT MATCHED THEN
	INSERT (
		 BatchLabel
		,ParentLoadBatchId
		)
	VALUES (
			 source.BatchLabel
			,source.ParentLoadBatchId
			)
;
/*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/

It does matter where in the file the SQL is inserted. After adding the batches used in the hierarchy. For levels of batches in the hierarchy are possible.

After reinstalling the metadata, the children-batch(es) is/are added.

You can double check your batch hierarchy with

SELECT *
FROM MetadataZone.BatchHierarchy
Posted 1 year agoby Dirk.Lerner

How to add a Batch dependeny?

The ExMeX Framework Core is designed in such a way that batches can be executed in parallel on the same…

The ExMeX Framework Core is designed in such a way that batches can be executed in parallel on the same level. If this is not desired, a dependency can be created between batches. For example, all raw data must be processed before applying business rules to the raw data.

ExMeX Companion App

Starting with version 0.2.0 of the ExMeX Companion App, the data modeler can add a batch dependency in the ExMeX Companion App.

ExMeX Framework Core Scripts

As always, the data modeler or DevOp has to edit the file \etc\config\ConfigLoadBatchLogistic.sql.

The following SQL example shows how to add sample batch dependencies for the dependency between batches 'ExMeX MD - Hub' and 'ExMeX MD - Link' as well as the dependency between batches 'ExMeX MD - Link' and 'ExMeX MD - Satellite'.

The former are the predecessors and the latter are the successors.

This means that the batch 'ExMeX MD - Hub' must be finished before the batch 'ExMeX MD - Link' starts and the batch 'ExMeX MD - Link' must be finished before the batch 'ExMeX MD - Satellite' can start.

/*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- Insert Batch Dependency                                                    —

MERGE INTO MetadataZone.LoadBatchDependency AS target
	USING (	SELECT prelb.LoadBatchId
				  ,suclb.LoadBatchId
			FROM MetadataZone.LoadBatch prelb
			CROSS JOIN MetadataZone.LoadBatch suclb
			WHERE prelb.BatchLabel = 'ExMeX MD - Hub'
			AND   suclb.BatchLabel = 'ExMeX MD - Link'
            UNION ALL
            SELECT prelb.LoadBatchId
				  ,suclb.LoadBatchId
			FROM MetadataZone.LoadBatch prelb
			CROSS JOIN MetadataZone.LoadBatch suclb
			WHERE prelb.BatchLabel = 'ExMeX MD - Link'
			AND   suclb.BatchLabel = 'ExMeX MD - Satellite'
		   ) AS source (
				 PredecessorLoadBatchId
				,SuccessorLoadBatchId)
	ON (target.PredecessorLoadBatchId = source.PredecessorLoadBatchId
	AND target.SuccessorLoadBatchId = source.SuccessorLoadBatchId)
WHEN NOT MATCHED THEN
	INSERT 
	VALUES ( source.PredecessorLoadBatchId
			,source.SuccessorLoadBatchId)
;
/*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/

It does matter where in the file the SQL is inserted. After adding the batches used in the dependency.

After reinstalling the metadata, the dependency is added.

You can double check your batch dependency with

SELECT *
FROM MetadataZoneCore.LoadBatchDependencyWithLabel
Posted 1 year agoby Dirk.Lerner

How to delete a Batch dependeny?

The ExMeX Framework Core is designed in such a way that batches can be executed in parallel on the same…

The ExMeX Framework Core is designed in such a way that batches can be executed in parallel on the same level. If this is not desired, a dependency can be created between batches. For example, all raw data must be processed before applying business rules to the raw data.

If this dependency is not to exist any longer, it can be removed.

ExMeX Companion App

Starting with version 0.2.0 of the ExMeX Companion App, the data modeler can delete an existing batch dependency in the ExMeX Companion App.

ExMeX Framework Core Scripts

As always, the data modeler or DevOp has to edit the file \etc\config\ConfigLoadBatchLogistic.sql.

The following SQL example shows a sample batch dependency deletion for the dependency between batches 'Source Interface A 2 Stage' and 'Load Stage with SSIS'. The former is the predecessor and the latter the successor.

/*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
-- Delete Batch Dependency                                                    --

INSERT INTO @DeleteBatchDependency (PredecessorBatchLabel,SuccessorBatchLabel)
    VALUES ('Source Interface A 2 Stage','Load Stage with SSIS')
;
/*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/

In the table variable @DeleteBatchDependency, the data modeler or DevOp enters the dependency to be deleted between for each two batches via insert DML.

It does not matter where in the file the SQL is inserted.

After reinstalling the metadata, the dependency is deleted.

You can double check your batch dependency with

SELECT *
FROM MetadataZoneCore.LoadBatchDependencyWithLabel
Posted 1 year agoby Dirk.Lerner

How to activate/deactivate a Batch?

If there is a need to remove a batch from regular execution, a data modeller or DevOp can disable it.…

If there is a need to remove a batch from regular execution, a data modeller or DevOp can disable it. All child batches of the batch deactivated by the data modeller or DevOp are also automatically deactivated.

Later, of course, the data modeller or DevOp can activate the batch again, including all child batches.

ExMeX Companion App

Starting with version 0.2.0 of the ExMeX Companion App, the data modeler can activate/deactivate one or more batch(es) in the ExMeX Companion App.

ExMeX Framework Core Scripts

As of now this feature is not available as script.

Posted 1 year agoby Dirk.Lerner