To ensure data quality it is recommended to use a domain for the Status CDC column. Set up a domain with the following characteristics.

See section How to design a domain Status CDC? in Chapter Change Data Capture Pipeline.

DAPI

The DAPI MetadataZoneDapi.MetadataForGenerateStageReference is the basis for generating the LPS with the LoadPatternId 18.

Generator

The script for generating the LPS with LoadPatternId 18 is Reference-Stage.sql

Logging

Because of how the MERGE SQL statement logs data changed in the target, the ExMeX Logging Framework can display only the sum of inserted, updated, and deleted rows in the RowsInserted column of the FctProcessStepVapourTrail in the MetadataZone-Mart.

Quote from the Microsoft SQL Docs:

When used after MERGE, @@ROWCOUNT (Transact-SQL) returns the total number of rows inserted, updated, and deleted to the client.

Resources

• Microsoft Docs: MERGE (Transact-SQL)
• MSSQLTips: Using MERGE in SQL Server

With Release 2.13.0 this issue is solved. Inserted, Updated (Changed) and physical deleted rows are logged correctly!

Mapping table of type SL - Volatile Zone to SL - Reference Zone

The mapping of the columns from the table of type SL - Volatile Zone to type SL - Reference Zone is kept simple. Connect the columns to be loaded by drag & drop, give the mapping a suitable name and that's it.

The two additional metadata columns must not be mapped. The Load Process Step takes care of the loading of the two columns:

• Audit Trail Id (Name and Code as configured)
• Status CDC

The following two pictures show exemplary mappings for a primary key with one column as well as with several columns (composite primary key).

Reference Data Stage Mapping to Ref Single Column Key

Reference Data Stage Mapping to Ref Composite Key

ExMeX Companion App

Starting with version 0.2.0 of the ExMeX Companion App, the data modeler can create a new batch for the Reference Data Stage Pipeline in the ExMeX Companion App.

ExMeX Framework Core Scripts

As always, the data modeler must add a new batch to the \etc\config\ConfigLoadBatchLogistic.sql file. The following SQL example shows an sample batch for the Reference Data Stage Pipeline.

-- Example Batch for Reference Data in Stage
MERGE INTO MetadataZone.LoadBatch AS target
	USING (	SELECT 'Reference Data Stage'
				  ,LoadBatchId
			FROM MetadataZone.LoadBatch
			WHERE BatchLabel = 'BigOne'
		   ) 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
			)
;

Next, the data modeler must place the new batch in the dependencies to other batches (file \etc\config\ConfigLoadBatchLogistic.sql). In the following example SQLs the batch is arranged after the Stage Layer is loaded (Batch 'Load Stage with SSIS') and before the Batch 'Default - Hub'.

-- Stage to Reference Stage
MERGE INTO MetadataZone.LoadBatchDependency AS target
	USING (	SELECT prelb.LoadBatchId
				  ,suclb.LoadBatchId
			FROM MetadataZone.LoadBatch prelb
			CROSS JOIN MetadataZone.LoadBatch suclb
			WHERE prelb.BatchLabel = 'Load Stage with SSIS'
			AND   suclb.BatchLabel = 'Reference Data Stage'
		   ) AS source (
				 PredecessorLoadBatchId
				,SuccessorLoadBatchId)
	ON (target.PredecessorLoadBatchId = source.PredecessorLoadBatchId
	AND target.SuccessorLoadBatchId = source.SuccessorLoadBatchId)
WHEN NOT MATCHED THEN
	INSERT 
	VALUES ( source.PredecessorLoadBatchId
			,source.SuccessorLoadBatchId)
;

-- Stage to Core
MERGE INTO MetadataZone.LoadBatchDependency AS target
	USING (	SELECT prelb.LoadBatchId
				  ,suclb.LoadBatchId
			FROM MetadataZone.LoadBatch prelb
			CROSS JOIN MetadataZone.LoadBatch suclb
			WHERE prelb.BatchLabel = 'Reference Data Stage'
			AND   suclb.BatchLabel = 'Default - Hub'
		   ) AS source (
				 PredecessorLoadBatchId
				,SuccessorLoadBatchId)
	ON (target.PredecessorLoadBatchId = source.PredecessorLoadBatchId
	AND target.SuccessorLoadBatchId = source.SuccessorLoadBatchId)
WHEN NOT MATCHED THEN
	INSERT 
	VALUES ( source.PredecessorLoadBatchId
			,source.SuccessorLoadBatchId)
;

In the next step, the data modeler must add the Load Process Steps respectively the tables to the batch. The assignment is done in the file \etc\config\ConfigLoadBatchProcessStepLogistic.sql. The following two SQL statements show as an example the assignment of all tables of type SL - Reference Zone of the data model STAGE_LAYER to the new batch 'Reference Data Stage'.

-- Delete Load Process Steps from Batch
DELETE FROM MetadataZone.LoadBatchProcessStep
	WHERE LoadBatchId = (SELECT LoadBatchId
						 FROM MetadataZone.LoadBatch
						 WHERE BatchLabel = 'Reference Data Stage'
						)
;

-- DML to assign all Reference Load Process Steps to a batch
INSERT INTO MetadataZone.LoadBatchProcessStep (LoadBatchId, ProcessStepId)
		SELECT LoadBatchId, ProcessStepId
		FROM (SELECT LoadBatchId 
                FROM MetadataZone.LoadBatch 
                WHERE BatchLabel = 'Reference Data Stage') lb
	    CROSS JOIN (SELECT ProcessStepId
					FROM MetadataZone.EntityClass iec
					INNER JOIN MetadataZone.Mapping im
						ON  iec.EntityClassCode    = im.TargetEntityClassCode
						AND iec.ModelCode          = im.TargetModelCode
                    INNER JOIN MetadataZone.LoadProcessStep ilps
                        ON  ilps.MappingGUID       = im.MappingGUID
                    INNER JOIN (
                            SELECT ProcessStepTypeId
                            FROM MetadataZone.ProcessStepType
                            WHERE ProcessStepType = 'DEF'
                            ) pst
                        ON ilps.ProcessStepTypeId = pst.ProcessStepTypeId
                    -- Assign Data Models to batch
 					WHERE iec.ModelCode IN (
                                             'STAGE_LAYER'
                                            --,'StageLayerHiZ'
                                            )
                    -- Assign Stereotypes to batch
					  AND iec.Stereotype IN (
										    -- 'Volatile Zone'
                                            --,'History Zone'
                                            'Reference Zone'
                                            --,'CDC Complete'
                                            --,'CDC One Data Load Before'
                                            --,'CDC Partially'
					  					    )
                    -- Assign tables (according to naming conventions) to batch
                    -- e.g. %edSat% for EdDating-Satellites
                      AND iec.EntityClassCode     LIKE '%%'
                    -- Include or exclude mappings according to naming conventions
                    -- NoRun% -> e.g. not used integrity runs
                    -- TestRun% -> mappings not ready to run on quality or production
                    --             but available in test-batches
                      AND im.MappingName NOT LIKE 'NoRun%'
                      AND im.MappingName NOT LIKE 'TestRun%'
                      AND im.MappingName NOT LIKE 'BusinessRun%'
					) lps
;