Module (LPS) Template

In this chapter the Module (LPS) Templates of the ExMeX Framework Core are described in more detail.

First which templates exist at all, second which templating expressions are available and later there will be further descriptions, e.g. how they work in more detail.

What LPS templates are available?

Valid as of release 2.13.0 The following table provides an overview of which Load Process Step (LPS) templates exist in…

Valid as of release 2.13.0

The following table provides an overview of which Load Process Step (LPS) templates exist in ExMeX Framework Core. Additionally the assigned Load Pattern Id is listed as well as the current LPS template version. Furthermore it is shown which of the global variables are already implemented and compatible in which template (for an overview of global variables see also section Getting started -> Configuration -> How do you set global variables and what is their purpose?).
As a rule, this is the case from LPS template version 4.0.0.

⊗ - global Variable is used by LPS Template
Grey cell - global Variable is not applicable
Empty cell - global Variable is yet not integrated in LPS Template

For the sake of clarity in the table, letters replaced the variable names:

A = SequenceAsDataVaultSK
B = ColumnAuditTrailId
C = ColumnEntityId
D = ColumnRecordSource
E = ColumnLoadTimestamp
F = SatColumnLoadTimestamp
G = SatColumnLoadEndTimestamp
H = ColumnHashDiff
I = ColumnHashDiffCDC
J = ColumnStatusCDC

LPS
Template
Load
Pattern
Id 

LPS
Template
Version

A B C D E F G H I J
 Hub  1  4.0.0 ⊗           
 Link  2  4.0.0          
 Hub-Satellite-HashDiff  3  4.0.0    
 Hub-Satellite-InsertOnly  4  3.0.0   ⊗             
 Link-Satellite-HashDiff  5  3.0.0    ⊗             
 Link-EndDatingSatellite  6  3.0.0    ⊗  ⊗             
 Hub-Satellite  7  4.0.0 ⊗       
 Link-Satellite  8  3.0.0    ⊗             
 Hub-Satellite Bitemporal  9  3.0.0    ⊗             
 Hub-Satellite Bitemporal Aligns 10  3.0.0    ⊗             
 Link-Satellite Bitemporal 11  3.0.0               
 Link-Satellite Bitemporal Aligns 12  3.0.1              
  13                      
  14                      
 SSIS-Stage 15  2.4.2                    
 CDC-ODLB-Stage 16  3.0.0                    
 CDC-HiZ-Stage 17  3.0.0                    
 Reference-Stage 18  4.0.0            
 One To One Data Copy 19  4.0.0    ⊗    ⊗ ⊗   ⊗  
Posted 11 months agoby Dirk.Lerner

Which templating expressions are provided by the ExMeX framework?

Section is work in progress! Templating expressions or ExMeX expressions are the basics of a template. You can use them…

Section is work in progress!

Templating expressions or ExMeX expressions are the basics of a template. You can use them individually within the plus sign +plus sign+ or combine them and build complex templates in combination with TSQL.

Basic Usage

ExMeX expressions are content enclosed by plus signs + +. In the template below, targetDataObject is a variable enclosed by plus signs, which is called an expression.

'TRUNCATE TABLE [' + targetDataObject + '];'

When the following design metadata is applied to the template,

TargetDataObject = 'ProductMain'

the expressions are interpreted as by the ExMeX compiler as follows:

TRUNCATE TABLE [ProductMain];

 

Escaping

For the ExMeX compiler the template is a string that starts and ends with an ' (apostrophe). To combine one or more ExMeX expressions e.g. with TSQL to a template the string must be terminated before each expression with a ' (apostrophe) and started again afterwards.

If you want the ExMeX compiler to output a ' (apostrophe), use the “double apostrophe”, '':

'SELECT ''My current Audit Trail Id: '', + @AuditTrailId 
;'

The “template” is interpreted by the ExMeX compiler as follows:

 DECLARE @AuditTrailId BIGINT;
SELECT 'My current Audit Trail Id: ', + @AuditTrailId
;

More information about @AuditTrailId in the next section.

Logging

The ExMeX variable declaration for @AuditTrailId is added automatically by the ExMeX compiler as part of the template. The integer value for @AuditTrailId is set by the ExMeX Logging Framework. Start and end of logging for the module is ensured by the ExMeX compiler.

ExMeX variables

@ColumnAuditTrailId

Column name in the data structures for the Audit Trail Id. Ist is set in the Global Variables.

@AuditTrailId

This variable will provide an integer value during execution of the module. The compiler can not interpret it. Therefore it should be part of the template string.

ExMeX expression

TargetDatabaseName

wip

TargetSchemaName

wip

TargetEntityClassCode

wip

TargetSurrogateKey

wip

TargetCompositeAlternateKey

wip

SourceDatabaseName

wip

SourceSchemaName

wip

SourceEntityClassCode

wip

SourceSurrogateHashKey

wip

SourceCompositeBusinessKey

wip

CorrelatedSubquery

wip

SourceSurrogateIs
NotNullCheck

wip

ExMeX expressions for the ExMeX Logging Framework

Logging other than start and end of execution (automatically built in by the compiler) is not essential, but helpful for later analysis of statistics about the execution of the module.

It is recommended to save the return values of any DML in local variables before they are cleared by the database:

    SELECT
         @ProcessStatusNumber   = @@ERROR
        ,@RowsInserted          = @@ROWCOUNT
    /*  ,@RowsUpdated           = @@ROWCOUNT */
    /*  ,@RowsDeletedPhysical   = @@ROWCOUNT */
    /*  ,@RowsDeletedLogical    = @@ROWCOUNT */
    ;

 

By putting the following lines into your template string, the execution mode of the logging stored procedure will be set and the stored procedure itself will be inserted y the compiler. For more details see Framework Core in Depth -> Stored Procedures:

SET @ProcessStepExec = ''I'' /* (S)tart, (U)pdate, (D)elete, (C)lear, (E)nd */;

By adding the ExMeX expression @SqlUspProcessStepVapourTrail to the template 

    '
    /*---------------------------------------------------------------------*/
    /*-- LOGGING: Update Insert information and statistics of VapourTrail  */
    /*---------------------------------------------------------------------*/

    SET @ProcessStepExec = ''I'' /* (S)tart, (U)pdate, (D)elete, (C)lear, (E)nd */;

    ' + @SqlUspProcessStepVapourTrail + '

 

The ExMeX compiler will interpred it as follows:

    /*---------------------------------------------------------------------*/
    /*-- LOGGING: Update Insert information and statistics of VapourTrail  */
    /*---------------------------------------------------------------------*/

    SET @ProcessStepExec      = 'I' /* (S)tart, (U)pdate, (D)elete, (C)lear, (E)nd */;

    EXEC MetadataZone.uspProcessStepVapourTrail
                     @LoadBatchId           = @LoadBatchId
                    ,@ProcessStepId         = @ProcessStepId
                    ,@ProcessStepExec       = @ProcessStepExec
                    ,@AuditTrailIdIn        = @AuditTrailId
                    ,@AuditTrailIdOut       = @AuditTrailId           OUTPUT
                    ,@SourceEntityClassCode = @SourceEntityClassCode
                    ,@RowsRead              = @RowsRead
                    ,@RowsInserted          = @RowsInserted
                    ,@RowsUpdated           = @RowsUpdated
                    ,@RowsDeletedPhysical   = @RowsDeletedPhysical
                    ,@RowsDeletedLogical    = @RowsDeletedLogical
                    ,@ProcessStatusNumber   = @ProcessStatusNumber
                    ,@ProcessStatusMessage  = @ProcessStatusMessage
                    ,@ErrorSeverity         = @ErrorSeverity
                    ,@ErrorState            = @ErrorState
                    ,@ErrorProcedure        = @ErrorProcedure
                    ,@ErrorLine             = @ErrorLine;
Posted 2 weeks agoby Dirk.Lerner