Control Framework Registration

Control Framework Registration

Purpose

Generate a deployment script that creates the registration of data logistics processes for the DIRECT Framework.

Motivation

Once the contents is created, the corresponding data logistics processes may require to be connected to a control framework for process control, logging, orchestration, and auditability.

Applicability

• All design metadata

Design Pattern

N/A

Schema Type

N/A

Output Type

• SQL

Implementation guidelines

This template covers all design metadata.

Considerations and consequences

N/A.

Extensions

N/A.

Code

/*
    Registration script for the DIRECT control framework.
*/

/* Maintenance and verification code block
DELETE FROM omd.EVENT_LOG
DELETE FROM omd.SOURCE_CONTROL
DELETE FROM omd.BATCH_MODULE
DELETE FROM omd.MODULE_INSTANCE WHERE MODULE_INSTANCE_ID <> 0;
DELETE FROM omd.BATCH_INSTANCE WHERE BATCH_INSTANCE_ID <> 0;
DELETE FROM omd.BATCH WHERE BATCH_ID <> 0;
DELETE FROM omd.MODULE WHERE MODULE_ID <> 0;

SELECT * FROM omd.MODULE
SELECT * FROM omd.MODULE_INSTANCE

SELECT * FROM omd.BATCH
SELECT * FROM omd.BATCH_INSTANCE

SELECT b.*,m.* FROM omd.BATCH_MODULE bm
JOIN omd.MODULE m ON bm.MODULE_ID = m.MODULE_ID
JOIN omd.BATCH b ON bm.BATCH_ID = b.BATCH_ID
*/

/*
    Batch registration
*/
{{#each dataObjectMappingLists}}
{{#hasClassification classifications "Persistent Staging"}}
EXEC [{{lookupExtension dataObjectMappings.0.extensions "controlFrameworkDataStore"}}].[{{lookupExtension dataObjectMappings.0.extensions "controlFrameworkLocation"}}].[RegisterBatch]
 @BatchCode = 'b_{{name}}'
,@BatchDescription = 'Data logistics workflow for [{{name}}]'

{{/hasClassification}}
{{/each~}}

/*
    Module registration
*/
-- Landing modules
{{#each dataObjectMappingLists}}
{{#each dataObjectMappings}}
{{#hasClassification ../classifications "Landing"}}
EXEC [{{lookupExtension ../extensions "controlFrameworkDataStore"}}].[{{lookupExtension ../extensions "controlFrameworkLocation"}}].[RegisterModule]
 @ModuleCode = 'm_{{targetDataObject.name}}'
,@ModuleAreaCode = '{{../../../conventions.landingAreaObjectPrefix}}'
,@Executable = 'EXEC [{{../../../conventions.landingAreaObjectDatastore}}].[{{../../../conventions.landingAreaObjectLocation}}].[SP_{{targetDataObject.name}}] @{{../../../conventions.auditTrailIdColumn}} = @ModuleInstanceId'
,@ModuleDescription = 'Staging Area process for [{{../sourceDataObjects.0.name}}]'
,@ModuleSourceDataObject = '[{{lookupExtension ../sourceDataObjects.0.dataConnection.extensions "datastore"}}].[{{lookupExtension ../sourceDataObjects.0.dataConnection.extensions "location"}}].[{{../sourceDataObjects.0.name}}]'
,@ModuleTargetDataObject = '[{{lookupExtension ../targetDataObject.dataConnection.extensions "datastore"}}].[{{lookupExtension ../targetDataObject.dataConnection.extensions "location"}}].[{{../targetDataObject.name}}]'

{{/hasClassification~}}{{/each~}}{{/each~}}

-- Persistent Staging modules
{{#each dataObjectMappingLists}}
{{#each dataObjectMappings}}
{{#hasClassification ../classifications "Persistent Staging"}}
EXEC [{{lookupExtension extensions "controlFrameworkDataStore"}}].[{{lookupExtension ../extensions "controlFrameworkLocation"}}].[RegisterModule]
 @ModuleCode = 'm_{{targetDataObject.name}}'
,@ModuleAreaCode = '{{../../../conventions.persistentStagingAreaObjectPrefix}}'
,@Executable = 'EXEC [{{../../../conventions.persistentStagingAreaObjectDatastore}}].[{{../../../conventions.persistentStagingAreaObjectLocation}}].[SP_{{targetDataObject.name}}] @{{../../../conventions.auditTrailIdColumn}} = @ModuleInstanceId'
,@ModuleDescription = 'Persistent Staging Area process for [{{../sourceDataObjects.0.name}}]'
,@ModuleSourceDataObject = '[{{lookupExtension ../sourceDataObjects.0.dataConnection.extensions "datastore"}}].[{{lookupExtension ../sourceDataObjects.0.dataConnection.extensions "location"}}].[{{../sourceDataObjects.0.name}}]'
,@ModuleTargetDataObject = '[{{lookupExtension ../targetDataObject.dataConnection.extensions "datastore"}}].[{{lookupExtension ../targetDataObject.dataConnection.extensions "location"}}].[{{../targetDataObject.name}}]'

{{/hasClassification~}}{{/each~}}{{/each~}}

/*
    Batch/Module registration
*/
{{#each dataObjectMappingLists}}
{{#each dataObjectMappings}}
{{#hasClassification ../classifications "Persistent Staging"}}
-- {{../name~}}
{{#hasClassification ../../classifications "User Managed"}}{{else}}
EXEC [{{lookupExtension extensions "controlFrameworkDataStore"}}].[{{lookupExtension ../extensions "controlFrameworkLocation"}}].[AddModuleToBatch] {{!-- Landing --}}
 @ModuleCode = 'm_{{sourceDataObjects.0.name}}'
,@BatchCode = 'b_{{../../../name}}'
{{/hasClassification}}

EXEC [{{lookupExtension extensions "controlFrameworkDataStore"}}].[{{lookupExtension ../extensions "controlFrameworkLocation"}}].[AddModuleToBatch] {{!-- Persistent Staging --}}
 @ModuleCode = 'm_{{targetDataObject.name}}'
,@BatchCode = 'b_{{../../name}}'

{{/hasClassification~}}{{/each~}}{{/each~}}

/*
    Manual run scripts for reference
*/
/*

{{#each dataObjectMappingLists}}
{{#each dataObjectMappings}}
{{#hasClassification ../classifications "Persistent Staging"}}
-- {{../name}}
EXEC [{{lookupExtension extensions "controlFrameworkDataStore"}}].[{{lookupExtension ../extensions "controlFrameworkLocation"}}].[RunBatch]
 @BatchCode = 'b_{{../../name}}'

{{/hasClassification}}{{/each}}{{~/each}}*/