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

Stored Procedures

What does the stored procedure return code mean?

Purpose This table gives an overview to all used or reserved return codes any stored procedure returns within the TEDAMOH…
Purpose

This table gives an overview to all used or reserved return codes any stored procedure returns within the TEDAMOH ExMeX Framework.

Motivation

Having a consistent error messaging within the TEDAMOH ExMeX Framework.

Applicability

The overview is a reference book.

Return Codes

Return code value Meaning
0 Successful execution.
>1 Any error occured generally spoken
 
16 Not able to create execution object of SSIS package
17 Source Table or View does not exist. 
18 Target Table or View does not exist.
19 Initial steps failed! Master batch is not opened!
20 Initial steps failed! Check MetadataZone.SatProcessStepDeltaExport for Orphans.
21 See table sys.messages SQL Server.
22 Initial steps failed! Please check MetadataZoneStage.ParallelAsyncLPSChunkLog for Orphans.
40 Violation of one of the submitted Filter Conditions. See more information in provided error-message (uspEntityClassAttributeFilter).
41 Violation of Filter Conditions. Source attribute code does not exist (uspEntityClassAttributeFilter).
42 Violation of one of the submitted global variables values. See more information in provided error-message (uspConfigExMeXFramework).
43 Detector does NOT exist (Alert & Notification Framework)
44 Violation of one of the submitted values in uspSetBatchSetting. See more information in provided error-message (uspSetBatchSetting).
45 Violation of one of the submitted values in uspGetBatchDml. See more information in provided error-message.
46 Violation of one of the submitted values in uspSetCompilerModuleTemplate. See more information in provided error-message.
47 Violation of one of the submitted values in uspCompiler. See more information in provided error-message.
84 ERROR: Load ConfigLoadBatchDependency from JSON to Metadata Zone Core failed: Something wrong with length of dependency chain!
85 ERROR: Load ConfigLoadBatchDependency from JSON to Metadata Zone Core failed: Batch dependency circle exist!
86 ERROR: Load ConfigLoadBatch from CoreScript to Metadata Zone Core failed!
87 uspParallelLPSWaitForChunk catched an error: Job execution within a SQL Server Agent failed (msdb.dbo.sysjobhistory.run_status = 0)
88 uspParallelLPSWaitForChunk catched an error but error status number is not numeric.
89 uspParallelLPSWaitForChunk catched an error but no error status number is available. Try to restart several times (max. 20 times) the SQL Agent Job after this error occurs.
90-99 Reserved for SSIS specific loging, generated within an SSIS package, in data logistic stattistics. Out of control of the Framework itself.
100 Restart of a batch is not possible. Batch is still running or not properly ended!
Please check for more information in table MetadataZone.BatchRunVapourTrail.
>100 See table sys.messages SQL Server.
Posted 2 years agoby Dirk.Lerner

usp - Batch Execute Load Process Step

TEDAMOH ExMeX Framework version: 2.8.0 or greater. Purpose This procedure executes the batches and LoadProcessSteps (LPS) configured in the Data…

TEDAMOH ExMeX Framework version: 2.8.0 or greater.

Purpose

This procedure executes the batches and LoadProcessSteps (LPS) configured in the Data Logistic metadata according to the orchestration.

Motivation

This procedure was developed with the purpose of simplifying the execution of hundreds of LPS and batches. Based on the parameter values passed and the metadata installed in the metadatabase, the procedure orchestrates the data pipeline independently and completely.

Applicability

Usually this procedure is used within the regular Data Pipeline job control.
During operation or testing of the data warehouse solution, the procedure can also be used to restart incorrect or non-executed LPS and to start individual batches or LPS.

Content

The master batch is usually passed to the procedure as a value. The master batch serves as an anchor to execute all batches and LPS located under the master batch.

The master batch itself must be opened before the procedure is called and then it is closed, since this is not done by the procedure for the batch passed to it. The reason for this is that the calling job control executes other processes in addition to the procedure, which are also assigned to the master batch. An example of the described workflow is shown in the TEDAMOH ExMeX Framework in the file ./Manual Batch.sql.

In parallel execution mode: Please make sure SQL Agent Service is running.

Parameter Value Type M* Meaning
@BatchLabel BatchLabel (aka Name) of a batch. VARCHAR(100) X

As defined in the Metadata. See also table MetadataZone.LoadBatch. Usually the master batch is passed as value.

@BatchExec

T = Trace/Verbose

E = Execute

CHAR(1) X Triggers mode in which the procedure will be executed.
@BatchRestartRunVapourTrailId BatchRunVapourTrailId of batch to restart. BIGINT   BatchRunVapourTrailId is logged in the data logistic vapour trail tables. See also table MetadataZone.BatchRunVapourTrail or MetadataZone.BatchExecuteLoadProcessStep. All executed batches are logged in the first table. The latter shows the state of the currently or last executed batches.
@SingleProcessStepId ProcessStepId BIGINT   Unique Id to identify a defined LoadProcessStep in table MetadataZone.LoadProcessStep.
@MultipleBatchLabel One or many BatchLabel (aka Name) of a batch. MultipleBatchLabel   The value passed to this paramter is a user defined table type. Values are inserted with DML-statements. One or many BatchLabels are possible to pass.
@ParallelExecution

0 = Off (default)

1 = Parallel execution enabled

BIT   Enable or disable parallel execution. If parameter is not provided, default is 0.
@ExitOnError

0 = Off (default)

1 = Exit On Error in Batch

BIT   Enable or disable exit on error in batch. If enabled the stored procedure exits after finishing a batch if a LPS raises an error within an batch.
@SSISLinkedServer SSIS Linked Server NVARCHAR(256)   Override SSIS environment. Please note the global settings of the ExMeX variables in the table MetadataZone.TEDAMOHConfigExMeXFramework !
@SSISProjectName SSIS Project Name NVARCHAR(256)  
@SSISFolderName SSIS Folder Name NVARCHAR(256)  
@SSISProxyAccountName SSIS Proxy Account Name NVARCHAR(256)  

M* = Mandatory parameter

Return Codes

Return code value Meaning
0 Successful execution.
>1 Any error occured generally spoken
 
16 Not able to create execution object of SSIS package
17 Source Table or View does not exist. 
18 Target Table or View does not exist.
19 Initial steps failed! Master batch is not opened!
20 Initial steps failed! Check MetadataZone.SatProcessStepDeltaExport for Orphans.
21 See table sys.messages SQL Server.
22 Initial steps failed! Please check MetadataZoneStage.ParallelAsyncLPSChunkLog for Orphans.
90-99 Reserved for SSIS specific loging, generated within an SSIS package, in data logistic stattistics. Out of control of the Framework itself.
100 Restart of a batch is not possible. Batch is still running or not properly ended!
Please check for more information in table MetadataZone.BatchRunVapourTrail.
>100 See table sys.messages SQL Server.
Considerations and consequences

In case of a restart, the procedure reopens and closes the master batch. The reason for this is that in most cases this is a task within the operation of the data warehouse solution and other processes, which are also part of the master batch, are not affected by a restart.

It is recommended to execute the procedure first in trace mode to double check if every paramter value is configured in the correct way and the result is the expected one.

Sample

In this example the master-batch BigOne is used. The following code-snippets shows how the procedure can be executed.

Trace mode examples

Mode Example

Execution Mode:
Trace

Execution:
All

EXEC MetadataZone.uspBatchExecuteLoadProcessStep
@BatchLabel = 'BigOne'
,@BatchExec = 'T';

Execution Mode:
Trace

Execution:
Restart Batch

EXEC MetadataZone.uspBatchExecuteLoadProcessStep 
@BatchLabel = 'BigOne'
,@BatchExec = 'T'
,@BatchRestartRunVapourTrailId = 10456;

Execution Mode:
Trace

Execution:
Single LPS

EXEC MetadataZone.uspBatchExecuteLoadProcessStep
 @BatchLabel = 'BigOne'
,@BatchExec = 'T'
,@SingleProcessStepId = 31;

Execution Mode:
Trace

Execution:
Restart Batch but only a single LPS

EXEC MetadataZone.uspBatchExecuteLoadProcessStep 
@BatchLabel = 'BigOne'
,@BatchExec = 'T'
,@BatchRestartRunVapourTrailId = 10456
,@SingleProcessStepId = 70;

Execution Mode:
Trace

Execution:
Multiple Batches (one or many)

DECLARE @MultipleBatchLabel MetadataZone.MultipleBatchLabel;
INSERT INTO @MultipleBatchLabel
VALUES ('Default - Hub');
INSERT INTO @MultipleBatchLabel
VALUES ('Default - Link');

EXEC MetadataZone.uspBatchExecuteLoadProcessStep
@BatchLabel = 'BigOne'
,@BatchExec = 'T'
,@MultipleBatchLabel = @MultipleBatchLabel;

Execution Mode:
Trace

Execution:
Multiple Batches (one or many)
but only a single LPS

DECLARE @MultipleBatchLabel MetadataZone.MultipleBatchLabel;
INSERT INTO @MultipleBatchLabel
VALUES ('Default - Hub');
INSERT INTO @MultipleBatchLabel
VALUES ('Default - Link');
EXEC MetadataZone.uspBatchExecuteLoadProcessStep
@BatchLabel = 'BigOne'
,@BatchExec = 'T'
,@SingleProcessStepId = 59
,@MultipleBatchLabel = @MultipleBatchLabel
;

 

Execute mode examples

Mode Example

Execution Mode:
Execution

Execution:
All

EXEC MetadataZone.uspBatchExecuteLoadProcessStep
@BatchLabel = 'BigOne'
,@BatchExec = 'E';

Execution Mode:
Parallel Execution

Execution:
All

EXEC MetadataZone.uspBatchExecuteLoadProcessStep
@BatchLabel = 'BigOne'
,@BatchExec = 'E'
,@ParallelExecution = 1;

Execution Mode:
Execution

Execution:
Restart Batch

EXEC MetadataZone.uspBatchExecuteLoadProcessStep 
@BatchLabel = 'BigOne'
,@BatchExec = 'E'
,@BatchRestartRunVapourTrailId = 10456;

Execution Mode:
Execution

Execution:
Single LPS

EXEC MetadataZone.uspBatchExecuteLoadProcessStep
 @BatchLabel = 'BigOne'
,@BatchExec = 'E'
,@SingleProcessStepId = 31;

Execution Mode:
Execution

Execution:
Restart Batch but only a single LPS

EXEC MetadataZone.uspBatchExecuteLoadProcessStep 
@BatchLabel = 'BigOne'
,@BatchExec = 'E'
,@BatchRestartRunVapourTrailId = 10456
,@SingleProcessStepId = 70;

Execution Mode:
Execution

Execution:
Multiple Batches (one or many)

DECLARE @MultipleBatchLabel MetadataZone.MultipleBatchLabel;
INSERT INTO @MultipleBatchLabel
VALUES ('Default - Hub');
INSERT INTO @MultipleBatchLabel
VALUES ('Default - Link');

EXEC MetadataZone.uspBatchExecuteLoadProcessStep
@BatchLabel = 'BigOne'
,@BatchExec = 'E'
,@MultipleBatchLabel = @MultipleBatchLabel;

Execution Mode:
Execution

Execution:
Multiple Batches (one or many)
but only a single LPS

DECLARE @MultipleBatchLabel MetadataZone.MultipleBatchLabel;
INSERT INTO @MultipleBatchLabel
VALUES ('Default - Hub');
INSERT INTO @MultipleBatchLabel
VALUES ('Default - Link');
EXEC MetadataZone.uspBatchExecuteLoadProcessStep
@BatchLabel = 'BigOne'
,@BatchExec = 'E'
,@SingleProcessStepId = 59
,@MultipleBatchLabel = @MultipleBatchLabel
;
Posted 2 years agoby Dirk.Lerner

usp - Config ExMeX Framework

Valid as of release 2.12.0 Purpose The purpose of this stored procedure is to change global variables which configures and…

Valid as of release 2.12.0

Purpose

The purpose of this stored procedure is to change global variables which configures and controls different parts of the ExMeX Framework Core.

Motivation

The existing configuration in scripts should be made more variable and be usable by all parts of the ExMeX Framework Core at runtime. This is only possible if more and more settings are made directly in the database in the future. Therefore a stored procedure is needed for a better maintenance and access by companion app.

Applicability

Usually this procedure is used for the first configuration or within the DevOps maintenance operations to change global variables for a different behaviour of the ExMeX Framework Core.

See also section Getting Started -> Configuration.

Execution

See also examples below.

EXEC MetadataZoneCore.uspConfigExMeXFramework
     @VariableName         = <Variable Name to change>    
    ,@VariableValue        = <Value to set of Variable Name>    
    ,@VariableDatatype     = <Datatype of Variable Name - Do not change> 
    ,@VariableDescription  = <Description of Variable Name>

Which parameter provides the usp?

The following table lists all parameter which are available in the stored procedure. If no default value is specified the parameter is mandatory.

Parameter Value Datatype M* Meaning
@VariableName One of the given global variables. See section Getting Started -> Configuration -> How do you set global variables and what is their purpose? for more details. VARCHAR(256) X Variable Name to change
@VariableValue Value to set for the global variable. See section Getting Started -> Configuration -> How do you set global variables and what is their purpose? for more details. VARCHAR(256) X Value to set of Variable Name
@VariableDatatype Given datatype of the global variable. See section Getting Started -> Configuration -> How do you set global variables and what is their purpose? for more details. VARCHAR(256) X Datatype of Variable Name. It is not recommended to change it.
@VariableDescription   Detailed description of the global variable. See section Getting Started -> Configuration -> How do you set global variables and what is their purpose? for more details. VARCHAR(1024)   Description of Variable Name. It is not recommended to change it.

M* = Mandatory parameter

 

Return Codes

This stored procedure returns the values listed in the following table.

Return code value Meaning
0 Successful execution.
>1 Any error occured generally spoken
 
42 Violation of one of the submitted value for a global variable. See more information in provided error-message.

 

Considerations and consequences

The stored procedure updates the following tables in the Metadata Zone Core:

  • MetadataZoneCore.HubConfigExMeXFramework and
  • MetadataZoneCore.SatConfigExMeXFramework

These tables should not be changed manually. A reading access takes place for all parts of the ExMeX Framework Core in the Metadata Zone via the view MetadataZone.ConfigExMeXFramework.

Since a change of the global variables usually has far-reaching consequences for all parts of the ExMeX Framework Core, they should be chosen carefully.

Example

The following code snippets show how the values can be changed using the global variable UseTargetEntityDB as an example.

Activate UseTargetEntityDB

EXEC MetadataZoneCore.uspConfigExMeXFramework
     @VariableName         = 'UseTargetEntityDB'     
    ,@VariableValue        = '1'    
    ,@VariableDatatype     = 'BIT'
    ,@VariableDescription  = 'Switch functionality to enable or  usage of SQL-Statement USE [<TargetDatabaseName>] within all LPS. 0 = Off, 1 = On'

Disable UseTargetEntityDB

    EXEC MetadataZoneCore.uspConfigExMeXFramework
     @VariableName         = 'UseTargetEntityDB'     
    ,@VariableValue        = '0'    
    ,@VariableDatatype     = 'BIT'
    ,@VariableDescription  = 'Switch functionality to enable or  usage of SQL-Statement USE [<TargetDatabaseName>] within all LPS. 0 = Off, 1 = On'    

 

Posted 1 year agoby Dirk.Lerner

usp - Entity Class Attribute Filter

TEDAMOH ExMeX Framework version: 2.10.0 or greater. Purpose This procedure maintains filter on source objects for LoadProcessSteps (LPS) configured in…

TEDAMOH ExMeX Framework version: 2.10.0 or greater.

Purpose

This procedure maintains filter on source objects for LoadProcessSteps (LPS) configured in the Data Logistic metadata.

Motivation

This procedure was developed with the purpose of simplifying the maintenance of filter conditions on source objects attributes for LPS. Based on the parameter values passed and the metadata installed in the metadatabase, the procedure stores filter conditions for one to many attribute on each source objects.

The filter conditions can also be used for housekeeping/archiving data.

Applicability

Usually this procedure is used within the regular Data Pipeline job control.
During operation or testing of the data warehouse solution, the procedure can also be used to reduce the amount of data which is populated by each individual batch or LPS.

Content

By setting filters on one or more possible columns on a source object for each LPS it is possible to reduce the amount of data to be processed. This feature is mainly introduced to filter from incoming interface to stage layer (volatile zone), but implemented to use it in any LPS.

Which filter conditions are available?

Condition Meaning / Example
= (simple equality)

Finding a row or more by using a simple equality

Example

LastName = 'Mueller'

LIKE (contain a value as part of a string)

Finding rows that contain a value as part of a string

Example

LastName LIKE '%Muel%'

IN (list of values)

Finding rows that are in a list of values

Example

LastName IN ('Mueller','Smith','Jones')

comparison operator
→ < (less than) or =< ( aka <= )
→ > (greater than) or >= ( aka => )

Finding rows by using a comparison operator

Example

→ TotalNetSalesAmount < 500.00
→ TotalNetSalesRevenue =< 150.00

→ TotalNetSalesAmount > 400.00
→ TotalNetSalesRevenue >= 50.00

LAST

Finding a row or more by using a moving/dynamic filter

Last (condition) 3 (value) years (Unit)

Example

Filter: Last 3 years

On 2020-05-05: Date > '2017-05-05'
On 2020-05-11: Date > '2017-05-11'

 

Which parameter provides the usp?

Parameter Value Type M* Meaning
@ModelCode ModelCode (aka Name) of a data model where the table exists/was designed. VARCHAR(100) X

As defined in the data model and/or Metadata. See also table MetadataZone.Model.

Hint: Use ModelCode of Source for LPS!

Example

'STAGE_LAYER'

@EntityClassCode EntityClassCode (aka Name) of the table to which the filter should applied to. VARCHAR(100) X

As defined in the data model and/or Metadata. See also table MetadataZone.Model.

Hint: Use EntityClassCode of Source for LPS!

Example

'VehicleLicensePlate'

@AttributeCode AttributeCode (aka Name) of the column to which the filter should applied to. VARCHAR(100) X

As defined in the data model and/or Metadata. See also table MetadataZone.Model.

Hint: Use AttributeCode of EntityClassCode of Source for LPS!

Example

'VehicleLicensePlate'

@EntityClassAttribute
   FilterConditionPos
1 (default) or greater TINYINT   If more than one filter condition on a column is applied. To order filter conditions within the column filter.
@EntityClassAttribute
   FilterCondition
'='
'LIKE'
'IN'
'<'
'<='
'>'
'>='
'LAST'
VARCHAR(10)  

Condition how to filter on the given column.

Where 'LAST' has a special function: The last 3 (value) years (unit) are always calculated in the DAPI from the current UTC-Datetimestamp. Format defines how UTC-Datetimestamp is converted to a string or integer. 'LAST' itself will be converted into '>='.

@EntityClassAttribute
 FilterInnerClusterType
'AND'
'OR'
NULL (default)
VARCHAR(10)   Needed, if more than one filter conditions are applied on the given column.
@EntityClassAttribute
 FilterOuterClusterType
'AND' (default)
'OR'
VARCHAR(10)   Not used yet.
To combine different filter cluster. Mostly each filter on a column equals one cluster.
@EntityClassAttribute
   FilterClusterPos
1 (default) or greater TINYINT   Not used yet.
Needed, if more than one filter conditions are applied in cluster.
@EntityClassAttribute
   FilterClusterLevel
NULL (default), 1 or greater TINYINT   Not used yet.
Needed if filter condition clusters are nested within each other.
@EntityClassAttribute
   FilterUnit

'DAY'
'WEEK'
'MONTH'
'QUARTER'
'YEAR'

Additional in combination with @IsHousekeeping:

'ProcessStepRun'

VARCHAR(32)  

Submit filter unit only, if filter condition = 'LAST'

 

Only applicable with condition

'LAST'.

For 'ProcessStepRun' only @EntityClassAttributeFilterDatatype = 'INT' is valid.

@EntityClassAttribute
   FilterValue
  VARCHAR(1024)  

Submit at least one value or a complete list separarted with commas. If the values are of datatype character ' to enclose values are added. For filter condition IN and LIKE the complete string which is locatet between ( and ) must be submitted.
Hint: For ' don't forget to escape with '! E.g. 'test' -> ''''test'''

Example

Single integer: 200
Integer list (IN-filter condition): 100,200,400
Singel character: Mueller
Character list (IN-filter condition): 'Mueller','Smith','Jones'
Part of a string (LIKE-filter condition): '%Muel%'

@EntityClassAttribute
   FilterDatatype
'INT' (Default)
'STRING'
'ORACLE_DATE'
VARCHAR(128)  

If 'INT', the filter condtion will be compared to an integer value.
   Example: ColumYXZ >= 202005
If 'STRING', the filter condtion will be compared to a string value.
   Example: ColumnABC >= '20200519130229'
If 'ORACLE_DATE', the filter condtion will be compared to a string value converted to a date by the ORACLE function to_DATE() using the defined filter format.
   Example: >= TO_DATE('2000','YYYY')

Only applicable with conditions

'LAST','=','<','<=','>','>='

@EntityClassAttribute
   FilterFormat
  VARCHAR(32)  

Submit filter format only, if filter data type = 'LAST'

For applicable filter format elements see table below.

Only applicable with condition

'LAST'

@IsHousekeeping 0 = No (Default)
1 = Yes
BIT  

If the filter is used for housekeeping, archiving data. 

Only applicable with condition

'LAST'.

HINT: Only one Filter is applicable on @EntityClassAttributeFilterConditionPos = 1
HINT: Only @EntityClassAttributeFilterDatatype = 'INT' is valid

@IsNOT

0 = Off (Default)
1 = On

BIT  

Condition to include logical operator NOT:
0 = Off
1 = On (NOT or !)

Only applicable with conditions

'='
'LIKE'
'IN'

@Action

'X' = Insert or Update filter (Default)
'D' = Delete filter on column position
'd' = Delete all filter on column
'R' = Read filter on column

CHAR(1)   'D' = To delete a filter column on a defined position if not longer needed. To delete correct mandatory parameters and @EntityClassAttributeFilterConditionPos has to be submitted.
'd' = To delete all filter on column if not longer needed.
'R' = Read / Show all filter on attribute. All mandatory paramter has to be submitted.
@StateTimeFrom   DATETIME2   Bitemporal Part. For future use. Yet not implemented.
@StateTimeBefore   DATETIME2   Bitemporal Part. For future use. Yet not implemented.
@EntityClass
   FilterCondition
  VARCHAR(2048)   Returns compiled filter condition on column.

M* = Mandatory parameter

 

Overview which parameters are mandatory depending on the coosen filter condition

  @EntityClassAttribute
   FilterValue
@EntityClassAttribute
   FilterDatatype 
@EntityClassAttribute
   FilterFormat 
@IsHousekeeping  @IsNOT 
'=' X  X  (X only with ORACLE_DATE)   (X)
'<'
'<='
'>'
'>='
X  X  (X only with ORACLE_DATE)     
'LIKE' X        (X)
'IN' X        (X)
'LAST' X  X  X  (X)  

X   = Mandatory parameter
(X) = Optional parameter

 

Applicable filter format elements for parameter @EntityClassAttributeFilterFormat

Element  Description 
-
/
,
.
;
:
@
AnyText
 Punctuation and text, which is not part of the elements, is reproduced in the result.
AMPM
ampm
 Meridian indicator, upper or lower case
DD  2 digits day of month (01-31).
D  1 digit day of month (1-9) and 2 digits day of month (10-31).
MM  2 digits month of year (01-12).
M  1 digit month of Year (1-9) and 2 digits month of year (10-12).
MON  Abbreviated name of month.
Month  Name of month.
YYYY  4-digit year.
YY  Last 2 digits of year.
hh  2 digits hour of day (01-12).
h  1 digit hour of day (1-9) and 2 digits hour of day (10-12).
mm  2 digits minute of hour (01-60).
m  1 digit minute of hour (1-9) and 2 digits minute of hour (10-60).
ss  2 digits second of minute (01-60).
s  1 digit second of minute (1-9) and 2 digits second of minute (10-60).

 

Filter format examples

Datetimestamp Input Filter format Output
 '1/31/09 23:15:45:222'  'Month MON MM/DD/YYYY @ hh:mm:ss'  January Jan 01/31/2009 @ 23:15:45
 '2020-01-02 23:15:45:222'  'YY-M-D hhmmss'  20-1-2 231545
 SYSUTCDATETIME()
 (2020-05-20 09:31:35.7174969)
 'YYYYMMDDhhmmss'  20200520093135
 '2020-01-02 03:01:05'  'Month | MON | YY-M-D h m s'  January | Jan | 20-1-2 3 1 5
 '2020-11-12 13:11:15'  'Month | MON | YY-M-D h m s'  November | Nov | 20-11-12 13 11 15

 

Return Codes

Return code value Meaning
0 Successful execution.
>1 Any error occured generally spoken
 
40

Violation of one of the submitted Filter Conditions. See more information in provided error-message:

→ Violation of Entity Class Attribute Filter Condition:
    -> Must be in ('=','LIKE','IN','<','<=','=<','>','>=','=>','LAST').

→ Violation of Entity Class Attribute Filter Datatype:
    -> Must be in ('INT','STRING','DATETIME','ORACLE_DATE').

→ Violation of Housekeeping conditions:
    -> EntityClassAttributeFilterDatatype must be in ('LAST').

→ Violation of Entity Class Attribute Filter Unit:
    -> Must be in ('ProcessStepRun','DAY','WEEK','MONTH','QUARTER','YEAR').

→ Violation of Entity Class Attribute Filter Condition Pos:
    -> Must be >= 1.

→ Violation of Action Delete ('D')!
    -> If Action = 'D' Entity Class Attribute Filter Condition Pos must be submitted and >= 1

→ Violation of Action Delete All ('d')!
    -> If Action = 'd' Entity Class Attribute Filter Condition Pos must NOT be submitted

41 Source attribute code does not exist.
Considerations and consequences
Sample

In this section different examples are shown. To keep examples simple first are some variables are declared. Last the usp will be executed. These two parts are needed all the time. Copy the one middle section (examples) as needed.

First -> Middle (choose one) -> Last

How to get the filter out of the DAPI please go to the last example.

The following code-snippets shows how the procedure can be executed.

First: Declare variables for examples

DECLARE @ReturnCode                                     INT             
       ,@ModelCode			                VARCHAR(100)    
       ,@EntityClassCode                                VARCHAR(100)    
       ,@AttributeCode                                  VARCHAR(100)    
       ,@EntityClassAttributeFilterConditionPos         TINYINT         
       ,@EntityClassAttributeFilterCondition            VARCHAR(10)     
       ,@EntityClassAttributeFilterInnerClusterType     VARCHAR(10)     
       ,@EntityClassAttributeFilterOuterClusterType     VARCHAR(10)     
       ,@EntityClassAttributeFilterClusterPos           TINYINT        
       ,@EntityClassAttributeFilterClusterLevel         TINYINT         
       ,@EntityClassAttributeFilterUnit                 VARCHAR(32)     
       ,@EntityClassAttributeFilterValue                VARCHAR(1024)   
       ,@EntityClassAttributeFilterDatatype             VARCHAR(128)    
       ,@EntityClassAttributeFilterFormat               VARCHAR(128)    
       ,@IsHousekeeping                                 BIT
       ,@IsNOT                                          BIT
       ,@Action                                         CHAR(1)
       ,@StateTimeFrom                                  DATETIME2       
       ,@StateTimeBefore                                DATETIME2       
       ,@EntityClassFilterCondition                     VARCHAR(2048)   
;

Middle section: Set filter LIKE on column.

    SET @ModelCode		                    = 'STAGE_LAYER'
    SET @EntityClassCode                            = 'VehicleLicensePlate'
    SET @AttributeCode                              = 'LicensePlate'
    SET @EntityClassAttributeFilterConditionPos     = 1
    SET @EntityClassAttributeFilterCondition        = 'LIKE'
    SET @EntityClassAttributeFilterValue            = '''KS%'''    

Middle section: Set second filter LIKE on same column.

    SET @ModelCode		                    = 'STAGE_LAYER'
    SET @EntityClassCode                            = 'VehicleLicensePlate'
    SET @AttributeCode                              = 'LicensePlate'
    SET @EntityClassAttributeFilterConditionPos     = 2
    SET @EntityClassAttributeFilterCondition        = 'LIKE'
    SET @EntityClassAttributeFilterValue            = '''XY%'''        

Middle section: Delete all filter on same column.

    SET @ModelCode	                            = 'STAGE_LAYER'
    SET @EntityClassCode                            = 'VehicleLicensePlate'
    SET @AttributeCode                              = 'LicensePlate'
    SET @Action                                     = 'd'    

Middle section: Insert filter LAST column with Oracle date conversion and date format.

    SET @ModelCode			            = 'StageLayerHiZ'
    SET @EntityClassCode                            = 'DataWithFancyFullHiZ'
    SET @AttributeCode                              = 'AuditTrailId'
    SET @EntityClassAttributeFilterCondition        = 'LAST'
    SET @EntityClassAttributeFilterUnit             = 'WEEK'
    SET @EntityClassAttributeFilterValue            = '20'
    SET @EntityClassAttributeFilterDatatype         = 'ORACLE_DATE'
    SET @EntityClassAttributeFilterFormat           = 'YYYYMMDD'

Middle section: Insert (second, accidentally) filter LAST column with Oracle date conversion and date format.

    SET @ModelCode		                    = 'StageLayerHiZ'
    SET @EntityClassCode                            = 'DataWithFancyFullHiZ'
    SET @AttributeCode                              = 'AuditTrailId'
    SET @EntityClassAttributeFilterConditionPos     = 2
    SET @EntityClassAttributeFilterCondition        = 'LAST'
    SET @EntityClassAttributeFilterUnit             = 'YEAR'
    SET @EntityClassAttributeFilterValue            = '2'
    SET @EntityClassAttributeFilterDatatype         = 'ORACLE_DATE'
    SET @EntityClassAttributeFilterFormat           = 'YYYY'

Middle section: Delete single filter (accidentally inserted before) on specific column position.

    SET @ModelCode		                    = 'StageLayerHiZ'
    SET @EntityClassCode                            = 'DataWithFancyFullHiZ'
    SET @AttributeCode                              = 'AuditTrailId'
    SET @EntityClassAttributeFilterConditionPos     = 2
    SET @Action                                     = 'D'

Last: Execute usp to set filter in metadata database

EXEC @ReturnCode = MetadataZone.uspEntityClassAttributeFilter
                 @ModelCode		                         = @ModelCode				             
                ,@EntityClassCode                                = @EntityClassCode                       
                ,@AttributeCode                                  = @AttributeCode                     
                ,@EntityClassAttributeFilterConditionPos         = @EntityClassAttributeFilterConditionPos
                ,@EntityClassAttributeFilterCondition            = @EntityClassAttributeFilterCondition   
                ,@EntityClassAttributeFilterInnerClusterType     = @EntityClassAttributeFilterInnerClusterType 
                ,@EntityClassAttributeFilterOuterClusterType     = @EntityClassAttributeFilterOuterClusterType 
                ,@EntityClassAttributeFilterClusterPos           = @EntityClassAttributeFilterClusterPos   
                ,@EntityClassAttributeFilterClusterLevel         = @EntityClassAttributeFilterClusterLevel
                ,@EntityClassAttributeFilterUnit                 = @EntityClassAttributeFilterUnit        
                ,@EntityClassAttributeFilterValue                = @EntityClassAttributeFilterValue       
                ,@EntityClassAttributeFilterDatatype             = @EntityClassAttributeFilterDatatype  
                ,@EntityClassAttributeFilterFormat               = @EntityClassAttributeFilterFormat
                ,@IsHousekeeping                                 = @IsHousekeeping
                ,@IsNOT                                          = @IsNOT
                ,@Action                                         = @Action
                ,@StateTimeFrom                                  = @StateTimeFrom                         
                ,@StateTimeBefore                                = @StateTimeBefore                       
                ,@EntityClassFilterCondition                     = @EntityClassFilterCondition OUTPUT           
;
SELECT @ReturnCode, @EntityClassFilterCondition
;

DAPI: Select all filters on DAPI for an Entity

Each filter condition on a column must be part in the overall filter when data is extracted. In SQL this will be the WHERE-CLAUSE. All filter conditions are connected the the logical operator which is provided in column SourceFilterOuterCluster ('AND' or 'OR').

SELECT  SourceEntityClassCode
       ,SourceAttributeCode  
       ,SourceAttributeFilterCondition
       ,SourceAttributeFilterOuterClusterType
       ,IsTargetHistory
       ,TargetEntityClassCode
       ,TargetAttributeCode  
       ,HousekeepingAmount
       ,HousekeepingUnit
FROM MetadataZone.MetadataForGenerateSource2StageLayer
WHERE (SourceModelCode       = @ModelCode				          
   AND SourceEntityClassCode = @EntityClassCode)
  OR  (TargetEntityClassCode = @EntityClassCode)
;
Posted 2 years agoby Dirk.Lerner

usp - Process Step Vapour Trail

Purpose The purpose of this stored procedure is to log data logistic statistics of Load Process Steps (LPS) executed within…
Purpose

The purpose of this stored procedure is to log data logistic statistics of Load Process Steps (LPS) executed within the ExMeX Framework Core or from outside trough 3rd party products, e.g. SSIS or Python scripts, to the ExMeX Logging Framework.

Motivation

The motivation for this stored procedure is a consistent logging of LPS’s in the corresponding Load Process Step Vapour Trail table. But also the reduction of errors while logging, e.g. wrong insert or updates, during development of LPS templates as well as the simplified use of logging purposes by third parties.

Applicability

This stored procedure is the link to the ExMeX logging framework. It is used by the ExMeX Framework Core, and by third party protducts to log the execution (start, end, insert, updates, etc.) of LPS’s.

A precondition for executing this stored procedure is that a batch —to which the LPS is associated— has already been started/opened in the ExMeX Logging Framework. See also the documentation of the stored procedure uspBatchVapourTrail in this section.

Content

This stored procedure is used by any LPS to ensure consistent logging into the ExMeX logging framework.

The stored procedure can be executed in different modes. Depending on this modes, the stored procedure logs the passed parameters differently in the Load Process Step Vapour Trail table:

(S)tart
Starts logging of the LPS:

  • Inserts a row into the Load Process Step Vapour Trail table with a new Audit Trail Id (AuditTrailId) and a UTC-timestamp when the LPS started (ProcessStartDateTime)
  • Checks if a ProcessStepDeltaExportOverride timesteps exists and applies it
  • Applies ProcessStepDeltaExport for performance tuning in business rule data objects
  • Sets the ProcessStatusNumber = 1
  • Returns the new Audit Trail Id

(U)pdate
Updates in the table Load Process Step Vapour Trail the row for the supplied Audit Trail Id:

  • The number of rows updated in the target by an LPS update subprocess (RowsUpdated)
  • The UTC-timestamp when the LPS update subprocess was finished (RowsUpdatedDateTime)

(D)elete (logical deletes)
Updates in the table Load Process Step Vapour Trail the row for the supplied Audit Trail Id:

  • The number of rows logical deleted in the target by an LPS logical delete subprocess (RowsDeletedLogical). Logically deleted rows are identified by the CDC flag 'D'.
  • The UTC-timestamp when the LPS logical delete subprocess was finished (RowsDeletedLogicalDateTime)
  • Logically deleted rows must not be counted as updated rows (RowsUpdated) and will be subtracted from updated rows (Updated rows = Updated rows - Deleted rows)

(C)lear (physical delete)
Updates in the table Load Process Step Vapour Trail the row for the supplied Audit Trail Id:

  • The number of rows physical deleted in the target by an LPS physical delete subprocess (RowsDeletedPhysical).
  • The UTC-timestamp when the LPS physical delete subprocess was finished (RowsDeletedPhysicalDateTime)

(I)nsert
Updates in the table Load Process Step Vapour Trail the row for the supplied Audit Trail Id:

  • The total number of rows read from source by an LPS (RowsRead)
  • The number of rows inserted in the target by an LPS insert subprocess (RowsInserted)
  • The UTC-timestamp when the LPS insert subprocess was finished (RowsInsertedDateTime)
  • Updated rows (RowsUpdated) and logically deleted rows (RowsDeletedLogical) must not be counted as inserted rows (RowsInserted) and will be subtracted from inserted rows (Updated rows = Updated rows - Deleted rows) 

(E)nd
Ends logging of the LPS in the table Load Process Step Vapour Trail for the supplied Audit Trail Id:

  • Deletes ProcessStepDeltaExportOverride if LPS finished successful
  • Finishes ProcessStepDeltaExport
  • Sets the ProcessStatusNumber = 0, if LPS finished successful else sets submitted error status number (> 1)
  • The UTC-timestamp when the LPS was finished (ProcessEndDateTime)

All timestamps in the Load Process Step Vapour Trail table are stored in Coordinated Universal Time (UTC).

Which ProcessStatusNumber are available?

The following table lists all states of ProcessStatusNumber:

ProcessStatusNumber  Description 
 0  LPS successful finished
 1  LPS successful started
 >1  Any error occurred generally spoken
 >100  See table sys.messages SQL Server
Execution 

See also code snippets in the example section below.

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;
Which parameter provides the usp?

The following table lists all parameter which are available for the stored procedure. If no default value is specified the parameter is mandatory:

Parameter
Value Type M* Meaning
@LoadBatchId LoadBatchId BIGINT X

The Batch to which the LoadProcessStep is assigned

@ProcessStepId ProcessStepId BIGINT X The LoadProcessStep from which the stored procedure is called
@ProcessStepExec (S)tart
(I)nsert
(U)pdate
(D)elete
(C)lear
(E)nd
CHAR(1)  X

 

CHAR(1)
Mode in which the stored procedure is executed

@AuditTrailIdIn   BIGINT  (X) AuditTrailId which was generated during (S)tart. Mandatory for all modes except (S)tart
@AuditTrailIdOut   BIGINT OUTPUT  X Return value for AuditTrailId in all modes
@SourceEntityClassCode    NVARCHAR(100)  X  The source table or view where data is get from
@RowsRead    INT    Rows read from source table or view
@RowsInserted    INT    Rows inserted by a LPS
@RowsUpdated    INT    Rows updated by a LPS
@RowsDeletedPhysical    INT    Rows deleted (physical) by a LPS
@RowsDeletedLogical    INT    Rows deleted (logical) by a LPS
@ProcessStatusNumber    INT    Value of ERROR_NUMBER() or user defined error number
@ProcessStatusMessage    NVARCHAR(4000)    Value of ERROR_MESSAGE() or user defined error message
@ErrorSeverity    INT    Value of ERROR_SEVERITY() or user defined error severity
@ErrorState    INT    Value of ERROR_STATE() or user defined error state
@ErrorProcedure    NVARCHAR(128    Value of ERROR_PROCEDURE() or user defined error procedure
@ErrorLine    INT    Value of ERROR_LINE() or user defined error line

M* = Mandatory parameter

Return Codes

This stored procedure returns the values listed in the following table:

Return code value Meaning
0 Successful execution.
>1 Any error occured generally spoken
 
>100 See table sys.messages SQL Server.
Considerations and consequences

The passing of parameters should be done very carefully, as the quality of logging depends on it. For example, the calculation of the number of lines must be taken into account for the Insert and Delete modes. Or submitted status numbers or messages may have effects.

The stored procedure inserts into, updates or deletes from the following tables:

  • MetadataZone.ProcessStepVapourTrail
  • MetadataZone.SatProcessStepDeltaExportOverride
  • MetadataZone.SatProcessStepDeltaExport
  • MetadataZoneStage.ProcessStepDeltaExportOverride
Example

The following code snippets show how the values can be changed.

(S)tart

-- Provided by your script or by generated LPS
SET @EntityId	           = <YourEntityId>
SET @LoadBatchId	   = <YourLoadBatchId>
SET @ProcessStepId         = <YourProcessStepId>
SET @SourceEntityClassCode = <YourSourceEntityClassCode (aka table code)>

-- Set execution mode for stored procedure
SET @ProcessStepExec       = 'S' 

-- Execute stored procedure
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;
						

(U)pdate

-- Provided by your script or by generated LPS
SET @EntityId	           = <YourEntityId>
SET @LoadBatchId           = <YourLoadBatchId>
SET @ProcessStepId         = <YourProcessStepId>
SET @SourceEntityClassCode = <YourSourceEntityClassCode (aka table code)>

-- DML <Your Update DML> -- Get return values of DML SELECT @ProcessStatusNumber = @@ERROR ,@RowsUpdated = @@ROWCOUNT -- Set execution mode for stored procedure SET @ProcessStepExec = 'U' -- Execute stored procedure 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;

(I)nsert

-- Provided by your script or by generated LPS
SET @EntityId              = <YourEntityId>
SET @LoadBatchId           = <YourLoadBatchId>
SET @ProcessStepId         = <YourProcessStepId>
SET @SourceEntityClassCode = <YourSourceEntityClassCode (aka table code)>

-- DML <Your Insert DML> -- Get return values of DML SELECT @ProcessStatusNumber = @@ERROR ,@RowsInserted = @@ROWCOUNT
-- Set execution mode for stored procedure SET @ProcessStepExec = 'I' -- Execute stored procedure 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;

(E)nd

-- Provided by your script or by generated LPS
SET @EntityId	           = <YourEntityId>
SET @LoadBatchId           = <YourLoadBatchId>
SET @ProcessStepId         = <YourProcessStepId>
SET @SourceEntityClassCode = <YourSourceEntityClassCode (aka table code)>

-- DML <Your Insert DML>

/*---------------------------------------------------------------------*/ /*-- Get error information of DML after fail. */ /*---------------------------------------------------------------------*/ SELECT @ProcessStatusNumber = ERROR_NUMBER() ,@ProcessStatusMessage = ERROR_MESSAGE() ,@ErrorSeverity = ERROR_SEVERITY() ,@ErrorState = ERROR_STATE() ,@ErrorProcedure = ERROR_PROCEDURE() ,@ErrorLine = ERROR_LINE() ; -- Set execution mode for stored procedure SET @ProcessStepExec = 'E' -- Execute stored procedure 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 6 months agoby Dirk.Lerner

usp - Set Batch Setting

Valid as of release 2.13.0 Purpose The purpose of this stored procedure is to change batch specific settings which configures…

Valid as of release 2.13.0

Purpose

The purpose of this stored procedure is to change batch specific settings which configures and controls execution of batches within the ExMeX Framework Core.

Motivation

The batch-specific settings were previously only possible via the ExMeX Companion App. And then only as part of the main configuration of a batch. This led to some disadvantages in the general configuration and in deployment.
The stored procedure decouples the specific settings from the general batch configuration. Thus, a separate deployment as well as an environment-specific configuration of the specific batch settings is possible.

Applicability

The stored procedure is mainly used by the ExMeX Companion App (valid as of App version 0.4.0). However, it can also be used independently, in an additional tool.
Since the batch-specific settings depend on the respective environment, the stored procedure is not part of the ExMeX Framework Core metadata installation.
In principle, it would be possible to integrate specific scripts created for each environment via the usr area.

Execution

The recommended way to configure the batch specific settings is with the ExMeX Companion App. However, it can be useful to execute the stored procedure directly, e.g. in a script:

EXEC MetadataZoneCore.uspSetBatchSetting
     @BatchLabel             = <Batch Label to change>
    ,@IsActive               = <0 = Off, 1 = Active>
    ,@ExitOnError            = <0 = Off, 1 = Exit>
    ,@MaxSqlCommandInChunk   = <Set max number of LPS>	

(See also examples below.)

There is also the 'Set Batch Setting' tool that supports and simplifies the development of a script.

Go to Documentation

Which parameter provides the usp?

The following table lists all parameter which are available in the stored procedure. If no default value is specified the parameter is mandatory.

Parameter Value Datatype M* Meaning
@BatchLabel <Batch Label> NVARCHAR(256) X Existing batch label to be changed. If no batch label exists, an error occurs.
@IsActive 0,1 BIT   0 = Batch is not active
1 = Batch is active
@ExitOnError 0,1 BIT   Exit if Error occurs.
See section Data Logistics -> Batch & LPS Orchestration -> Global & Batch Variable configuration for more details.
@MaxSqlCommandInChunk >=1 INT   Set max number of LPS executed in parallel.
See section Data Logistics -> Batch & LPS Orchestration -> Global & Batch Variable configuration for more details.

M* = Mandatory parameter

 

Return Codes

This stored procedure returns the values listed in the following table.

Return code value Meaning
0 Successful execution.
>1 Any error occured generally spoken
 
44 Violation of one of the submitted values. See more information in provided error-message.

 

Considerations and consequences

The stored procedure updates the following table in the Metadata Zone Core:

  • MetadataZoneCore.SatLoadBatchSetting

These tables should not be changed manually. A reading access takes place for all parts of the ExMeX Framework Core in the Metadata Zone via the view MetadataZone.LoadBatchNowNow.

Since a change of the global variables usually has far-reaching consequences for all parts of the ExMeX Framework Core, they should be chosen carefully.

Example

The following code snippets show how the values can be changed using the global variable UseTargetEntityDB as an example.

Disable Batch, no other batch-specific settings

EXEC MetadataZoneCore.uspSetBatchSetting
     @BatchLabel             = N'BigOne'
    ,@IsActive               = 0
    ,@ExitOnError            = NULL
    ,@MaxSqlCommandInChunk   = NULL

Set MaxSqlCommandInChunk to 20, no other batch-specific settings

EXEC MetadataZoneCore.uspSetBatchSetting
     @BatchLabel             = N'BigOne'
    ,@IsActive               = NULL
    ,@ExitOnError            = NULL
    ,@MaxSqlCommandInChunk   = 20

 

Posted 1 year agoby Dirk.Lerner

usp - Set Delta Export Time Period

Purpose The purpose of this stored procedure is to override the value in column DeltaExportAssertionTimeFrom in table MetadataForExecuteDataLogisticLoadProcessStep for a…
Purpose

The purpose of this stored procedure is to override the value in column DeltaExportAssertionTimeFrom in table MetadataForExecuteDataLogisticLoadProcessStep for a Load Process Step (LPS) at execution time in data logistics.

Motivation

The motivation for this stored procedure has different use cases but not limited to these:

  • Set a timestamp to perform a LPS initial load from a source to one or many target object(s).
  • Set a timestamp to perform a LPS reload of a defined time period from a source to one or many target object(s) in the case of errors or other issues.
  • Set a different timestamp for a LPS to ensure an extract of a slightly different time period from a source which is not reliable.
Applicability

This stored procedure is applied every time the timestamp value in table MetadataForExecuteDataLogisticLoadProcessStep, column DeltaExportAssertionTimeFrom, needs to be replaced with a different timestamp, for a Load Process Step (LPS), that is provided by the ExMeX Framework Core.

Content

This stored procedure is used to override the value in column DeltaExportAssertionTimeFrom in table MetadataForExecuteDataLogisticLoadProcessStep provided by the ExMeX framework core.

A batch, which contains all LPS to override the DeltaExportAssertionTimeFrom timestamp can be submitted to the procedure as a value.

A single LPS within a batch, to override the DeltaExportAssertionTimeFrom timestamp can be submitted to the procedure as values.

The LPS’s, which received a new timestamp by this stored procedure, can use the newly set value in column DeltaExportAssertionTimeFrom during execution. This information is provided by the DAPI MetadataForExecuteDataLogisticLoadProcessStep.

Execution

See also code snippets in the example section below.

EXEC MetadataZone.uspSetDeltaExportTimePeriod 
  @BatchLabel                   = <Your Batch Label>
 ,@LoadProcessStepId            = <Your (Load Process) Step Id>
 ,@DeltaExportAssertionTimeFrom = <Your timestamp>
 ;
 
Which parameter provides the usp?

The following table lists all parameter which are available for the stored procedure. If no default value is specified the parameter is mandatory:

Parameter Value Type M* Meaning

@BatchLabel

Label (name) of a given batch

NVARCHAR(256)

 X

Batch label as defined in the Metadata - see table MetadataZone.LoadBatch

@LoadProcessStepId

ProcessStepId

BIGINT

 

Unique Id to identify a LoadProcessStep - see table MetadataZone.LoadProcessStep

@DeltaExportAssertionTimeFrom

One or many BatchLabel (aka Name) of a batch.

DATETIME2

 X

New timestamp for column DeltaExportAssertionTimeFrom

M* = Mandatory parameter

Return Codes

This stored procedure returns the values listed in the following table:

Return code value Meaning
0 Successful execution.
Considerations and consequences

The stored procedure inserts data into the table ProcessStepDeltaExportOverride. This data is used within the ExMeX Framework Core by the stored procedure uspProcessStepVapourTrail.

Override a timestamp of a batch, in column DeltaExportAssertionTimeFrom in table MetadataForExecuteDataLogisticBatch is not possible.

Example

The following code snippets show how the values can be changed.

Override timestamp for a single LPS

-- Execute stored procedure
EXEC MetadataZone.uspSetDeltaExportTimePeriod 
  @BatchLabel                   = 'Example Hub (Core)'
 ,@LoadProcessStepId            = 1256
 ,@DeltaExportAssertionTimeFrom = '2020-01-01 10:04:46.5187419'
 ;

Override timestamp for all LPS’s in a batch

-- Execute stored procedure
EXEC MetadataZone.uspSetDeltaExportTimePeriod 
  @BatchLabel                   = 'Example Hub (Core)'
 ,@LoadProcessStepId            = NULL
 ,@DeltaExportAssertionTimeFrom = '2020-01-01 10:04:46.5187419'
 ;
Posted 6 months agoby Dirk.Lerner

usp - Set Compiler Module Template

Valid as of release 3.0.0 Purpose The purpose of this stored procedure is to allow any ExMeX Framework Core administrator…

Valid as of release 3.0.0

Purpose

The purpose of this stored procedure is to allow any ExMeX Framework Core administrator to add or insert a section into a module-template (Load Process Step (LPS)) or to overwrite a given section of any provided module by the ExMeX Framework Core. This allows a user-specific creation of templates for modules.

Furthermore, this stored procedure creates the possibility to provide additional templates for modules in other code languages or for other target platforms.

Motivation

The motivation for this stored procedure is driven by different use cases but not limited to these:

  • Adding a table partition switch to switch the current partition —in the target object— within the module before loading from a source object to a target object — applies at Load Pattern Id level
  • Add custom SQL code to design a module for PIT or Bridge target objects according to your own requirements — applies at Mapping Object level
    Overwrite an ExMeX Framework Core Template, e.g. the Hub DML (insert statement) in the batch 'Initital Hub Load', with a special version for an initial loading of the target object — applies at Load Pattern Id level
  • Add new template languages, e.g. for Oracle or Azure
 
Applicability

The stored procedure is applied each time a template code snipped (section) for a module (LPS) template is provided to the ExMeX Framework Core. The section needs to be created, added, updated or deleted by an administrator. This usually happens during the metadata installation. But not limited to.
If a section is changed directly at the database we recommend to download/extract the installation script for the code snippet and store it in your code versioning.

Content

This stored procedure is used to modify or customize code snippets within the module template tables provided by the ExMeX Framework Core.

To the stored procedure can be passed a code snippet on different valid levels:

  • General, applies to all the following
  • For a specific Load Pattern Id
  • For a batch
  • For a mapping object
  • Or any combination of load pattern id, or mapping object

 

The new code snippets are applied immediately during subsequent compilation of the LPS.

Execution

See also code snippets in the example section below.

EXEC MetadataZoneCore.uspSetCompilerSqlExtendLps 
     @TemplateLanguageCode      <>
    ,@TemplateClassification    <>
    ,@TemplateComment           <>
    ,@TemplateVersion           <>
    ,@TemplateDate              <>
    ,@TemplateCodeSnippet       <>

 -- Action and return code 
    ,@ProcessStepType           < (optional)>
    ,@Action                    < 'R', 'I', 'U', 'D', 'S' or 'DL'>

-- Type of load pattern (e.g. Hub, Sat, PIT)
    ,@LoadPatternId             < (optional)>

-- Mapping/LPS (optional)
    ,@SourceModelCode           < (optional)>
    ,@SourceEntityClassCode     < (optional)>
    ,@TargetModelCode           < (optional)>
    ,@TargetEntityClassCode     < (optional)>
    ,@MappingRole               < (optional)>
;

 

Which parameter provides the usp?

The following blocks list all parameters available for the stored procedure:

@TemplateLanguageCode NVARCHAR(256)

Mandatory

By the ExMeX Framework Core supported language code. This may be extended in future. See also section Framework Core in Depth -> LPS Template -> What LPS templates are available. E.g.

N'TSQL'

@TemplateClassification NVARCHAR(256)

Mandatory

Name of a template section. There are different sections available within a template skeleton.

To get information about whicht section for a given language code are available see parameter @action. E.g.

N'<#=preBodySection#>'

@TemplateComment NVARCHAR(1024)

Mandatory for @Action = 'I'

Add a good and precises description for the given code snippet. E.g.

N'ExMeX User Template - 
Override/Extension for 
CDCodlb template in preBodySection'

@TemplateVersion NVARCHAR(20)

Mandatory for @Action = 'I'

Version of the the given code snippet. E.g.

N'1.0.0'

@TemplateDate DATE

Mandatory for @Action = 'I'

Date of the the given code snippet. E.g.

N'20220324'

@TemplateCodeSnippet NVARCHAR(MAX)

Mandatory for @Action = 'I'

The code snippet itself. E.g.

N'
[…]
/* Partition Switch */
DECLARE @CreatePartition NVARCHAR(MAX);
[…]
'

 

All quotation marks have to be escaped!

Example

Original code before submit to usp:

SELECT
[…]
WHERE textCol IN ('I', 'U', 'D')

 

Adapted code for usp:

SELECT
[…]
WHERE textCol IN (''I'', ''U'', ''D'')

@LoadPatternId BIGINT

Mandatory

The Load Pattern Id to which the given code snippet should be applied to. E.g. 1 for Hub

@SourceModelCode NVARCHAR(100)
@TargetModelCode NVARCHAR(100)
@TargetEntityClassCode NVARCHAR(100)
@MappingRole NVARCHAR(256)
@ProcessStepType NVARCHAR(50)

Optional

If a code snippet should be applied to a specific mapping, then set mapping with source and and target data objects , mapping role:

SELECT SourceModelCode
,SourceEntityClassCode
,TargetModelCode
,TargetEntityClassCode
,MappingRole
FROM MetadataZone.Mapping

ProcessStepType is ‘DEF’, ‘CDC’ or ‘SSIS’

Example:

EXEC [...]
    ,@SourceModelCode        = N'STAGE_LAYER'
    ,@TargetModelCode        = N'CoreLayerwithHashKey'
    ,@ProcessStepType        = N'DEF'
    ,@SourceEntityClassCode  = N'ArtworkInMuseum'
    ,@TargetEntityClassCode  = N'HubMuseum'
    ,@MappingRole            = N'ArtworkInMuseum2HubMuseum'
,@ProcessStepType = 'DEF' [...]

@Action CHAR(2)

Mandatory

'I' - insert code snippet
'U' - update code snippet
‘D’ - delete code snippet
'R' - get current template code snippet
'S' - get all possible sections (template classifications)
‘DL’ - get a script to download ansd save as file

 

Return Codes

This stored procedure returns the values listed below:

0

Successful execution.

>1

Any error occurred generally spoken

46

Violation of one of the submitted values. See more information in provided error-message.

Considerations and consequences

With this stored procedure it is possible to extend the templates of the ExMeX Framework Core or to overwrite parts of the supplied templates.

Templates changed or extended in this way are used by the ExMeX compiler (usp Compiler) during the next installation of the metadata to compile the modules (LPS).

A change of the templates, independently of whether a template was extended or overwritten, has far-reaching consequences. This should always be thought of. A thoughtless change can mean severe consequences.

Example

The following code snippets show how the values can be changed. The examples, if used as shown, will break the hub modules. They are not longer executable. If you follow the examples you should at least execute the last example (delete) to avoid severe consequences!

Which sections in this template skeleton are available?

DECLARE  @TemplateLanguageCode      NVARCHAR(256)   = NULL
        ,@TemplateClassification    NVARCHAR(256)   = NULL
        ,@TemplateComment           NVARCHAR(1024)  = NULL
        ,@TemplateVersion           NVARCHAR(20)    = NULL
        ,@TemplateDate              DATE            = NULL
        ,@TemplateCodeSnippet       NVARCHAR(MAX)   = NULL
        -- Type of load pattern (e.g. Hub, Sat, PIT)
        ,@LoadPatternId             BIGINT          = NULL
	 -- Action and return code 
        ,@Action                    CHAR(2)         = NULL
        ,@ReturnCode                INT             = NULL
        -- Mapping/LPS (optional)
        ,@SourceModelCode           NVARCHAR(100)   = NULL
        ,@SourceEntityClassCode     NVARCHAR(100)   = NULL
        ,@TargetModelCode           NVARCHAR(100)   = NULL
        ,@TargetEntityClassCode     NVARCHAR(100)   = NULL
        ,@MappingRole               NVARCHAR(256)   = NULL
        ,@ProcessStepType           NVARCHAR(50)    = NULL
;


SET @TemplateLanguageCode   = N'TSQL'

-- Use 'S' to get all available template sections
--
-- Set type of action 'I', 'U', 'R', 'S' or 'D'
SET @Action                 = 'S'


EXEC @ReturnCode = MetadataZoneCore.uspSetCompilerModuleTemplate
     @TemplateLanguageCode   = @TemplateLanguageCode  
    ,@TemplateClassification = @TemplateClassification
    ,@TemplateComment        = @TemplateComment       
    ,@TemplateVersion        = @TemplateVersion       
    ,@TemplateDate           = @TemplateDate          
    ,@TemplateCodeSnippet    = @TemplateCodeSnippet   
    ,@LoadPatternId          = @LoadPatternId         
    ,@SourceModelCode        = @SourceModelCode       
    ,@SourceEntityClassCode  = @SourceEntityClassCode 
    ,@TargetModelCode        = @TargetModelCode       
    ,@TargetEntityClassCode  = @TargetEntityClassCode 
    ,@MappingRole            = @MappingRole           
    ,@ProcessStepType        = @ProcessStepType       
    ,@Action                 = @Action                
;

SELECT 'ReturnCode: ',@ReturnCode;

Add user specific code snippet: Overwrite main section for Hub modules

DECLARE  @TemplateLanguageCode      NVARCHAR(256)   = NULL
        ,@TemplateClassification    NVARCHAR(256)   = NULL
        ,@TemplateComment           NVARCHAR(1024)  = NULL
        ,@TemplateVersion           NVARCHAR(20)    = NULL
        ,@TemplateDate              DATE            = NULL
        ,@TemplateCodeSnippet       NVARCHAR(MAX)   = NULL
        -- Type of load pattern (e.g. Hub, Sat, PIT)
        ,@LoadPatternId             BIGINT          = NULL
	 -- Action and return code 
        ,@Action                    CHAR(2)         = NULL
        ,@ReturnCode                INT             = NULL
        -- Mapping/LPS (optional)
        ,@SourceModelCode           NVARCHAR(100)   = NULL
        ,@SourceEntityClassCode     NVARCHAR(100)   = NULL
        ,@TargetModelCode           NVARCHAR(100)   = NULL
        ,@TargetEntityClassCode     NVARCHAR(100)   = NULL
        ,@MappingRole               NVARCHAR(256)   = NULL
        ,@ProcessStepType           NVARCHAR(50)    = NULL
;


SET @TemplateLanguageCode   = N'TSQL'

-- Set main body section 
SET @TemplateClassification = N'<#=mainBodySection#>'
SET @TemplateComment        = N'ExMeX User Template - Example in documentation'
SET @TemplateVersion        = N'1.0.0'
SET @TemplateDate           = N'20220301'

-- Use 'I' to insert/override code snippet
--
-- Set type of action 'I', 'U', 'R', 'S' or 'D'
SET @Action                 = 'I'

-- Set load pattern to 1 (Hub)
--
-- Type of load pattern (e.g. Hub, Sat, PIT)
SET @LoadPatternId          = 1

-- Code snippet for template
SET @TemplateCodeSnippet    = N'
-- ExMeX User Template - Example in documentation
INSERT INTO ['' + TargetDatabaseName + ''].['' + TargetSchemaName + ''].['' + TargetEntityClassCode + '']
 SELECT 
    ''''all with my much more better DML statement''''
 FROM IKnowItBetterTable
 ;
'
;

EXEC @ReturnCode = MetadataZoneCore.uspSetCompilerModuleTemplate
     @TemplateLanguageCode   = @TemplateLanguageCode  
    ,@TemplateClassification = @TemplateClassification
    ,@TemplateComment        = @TemplateComment       
    ,@TemplateVersion        = @TemplateVersion       
    ,@TemplateDate           = @TemplateDate          
    ,@TemplateCodeSnippet    = @TemplateCodeSnippet   
    ,@LoadPatternId          = @LoadPatternId         
    ,@SourceModelCode        = @SourceModelCode       
    ,@SourceEntityClassCode  = @SourceEntityClassCode 
    ,@TargetModelCode        = @TargetModelCode       
    ,@TargetEntityClassCode  = @TargetEntityClassCode 
    ,@MappingRole            = @MappingRole           
    ,@ProcessStepType        = @ProcessStepType       
    ,@Action                 = @Action                
;

SELECT 'ReturnCode: ',@ReturnCode;

Read user specific code snippet for main section for Hub modules

DECLARE  @TemplateLanguageCode      NVARCHAR(256)   = NULL
        ,@TemplateClassification    NVARCHAR(256)   = NULL
        ,@TemplateComment           NVARCHAR(1024)  = NULL
        ,@TemplateVersion           NVARCHAR(20)    = NULL
        ,@TemplateDate              DATE            = NULL
        ,@TemplateCodeSnippet       NVARCHAR(MAX)   = NULL
        -- Type of load pattern (e.g. Hub, Sat, PIT)
        ,@LoadPatternId             BIGINT          = NULL
	 -- Action and return code 
        ,@Action                    CHAR(2)         = NULL
        ,@ReturnCode                INT             = NULL
        -- Mapping/LPS (optional)
        ,@SourceModelCode           NVARCHAR(100)   = NULL
        ,@SourceEntityClassCode     NVARCHAR(100)   = NULL
        ,@TargetModelCode           NVARCHAR(100)   = NULL
        ,@TargetEntityClassCode     NVARCHAR(100)   = NULL
        ,@MappingRole               NVARCHAR(256)   = NULL
        ,@ProcessStepType           NVARCHAR(50)    = NULL
;


SET @TemplateLanguageCode   = N'TSQL'

-- Set main body section 
SET @TemplateClassification = N'<#=mainBodySection#>'

-- Use 'R' to read code snippet
--
-- Set type of action 'I', 'U', 'R', 'S' or 'D'
SET @Action                 = 'R'

-- Set load pattern to 1 (Hub)
--
-- Type of load pattern (e.g. Hub, Sat, PIT)
SET @LoadPatternId          = 1


EXEC @ReturnCode = MetadataZoneCore.uspSetCompilerModuleTemplate
     @TemplateLanguageCode   = @TemplateLanguageCode  
    ,@TemplateClassification = @TemplateClassification
    ,@TemplateComment        = @TemplateComment       
    ,@TemplateVersion        = @TemplateVersion       
    ,@TemplateDate           = @TemplateDate          
    ,@TemplateCodeSnippet    = @TemplateCodeSnippet   
    ,@LoadPatternId          = @LoadPatternId         
    ,@SourceModelCode        = @SourceModelCode       
    ,@SourceEntityClassCode  = @SourceEntityClassCode 
    ,@TargetModelCode        = @TargetModelCode       
    ,@TargetEntityClassCode  = @TargetEntityClassCode 
    ,@MappingRole            = @MappingRole           
    ,@ProcessStepType        = @ProcessStepType       
    ,@Action                 = @Action                
;

SELECT 'ReturnCode: ',@ReturnCode;

Delete user specific code snippet: Delete user specific settings for main section for Hub modules

DECLARE  @TemplateLanguageCode      NVARCHAR(256)   = NULL
        ,@TemplateClassification    NVARCHAR(256)   = NULL
        ,@TemplateComment           NVARCHAR(1024)  = NULL
        ,@TemplateVersion           NVARCHAR(20)    = NULL
        ,@TemplateDate              DATE            = NULL
        ,@TemplateCodeSnippet       NVARCHAR(MAX)   = NULL
        -- Type of load pattern (e.g. Hub, Sat, PIT)
        ,@LoadPatternId             BIGINT          = NULL
	 -- Action and return code 
        ,@Action                    CHAR(2)         = NULL
        ,@ReturnCode                INT             = NULL
        -- Mapping/LPS (optional)
        ,@SourceModelCode           NVARCHAR(100)   = NULL
        ,@SourceEntityClassCode     NVARCHAR(100)   = NULL
        ,@TargetModelCode           NVARCHAR(100)   = NULL
        ,@TargetEntityClassCode     NVARCHAR(100)   = NULL
        ,@MappingRole               NVARCHAR(256)   = NULL
        ,@ProcessStepType           NVARCHAR(50)    = NULL
;


SET @TemplateLanguageCode   = N'TSQL'

-- Override main body section 
SET @TemplateClassification = N'<#=mainBodySection#>'

-- Use 'D' to delete code snippet
--
-- Set type of action 'I', 'U', 'R', 'S' or 'D'
SET @Action                 = 'D'

-- Set load pattern to 1 (Hub)
--
-- Type of load pattern (e.g. Hub, Sat, PIT)
SET @LoadPatternId          = 1


EXEC @ReturnCode = MetadataZoneCore.uspSetCompilerModuleTemplate
     @TemplateLanguageCode   = @TemplateLanguageCode  
    ,@TemplateClassification = @TemplateClassification
    ,@TemplateComment        = @TemplateComment       
    ,@TemplateVersion        = @TemplateVersion       
    ,@TemplateDate           = @TemplateDate          
    ,@TemplateCodeSnippet    = @TemplateCodeSnippet   
    ,@LoadPatternId          = @LoadPatternId         
    ,@SourceModelCode        = @SourceModelCode       
    ,@SourceEntityClassCode  = @SourceEntityClassCode 
    ,@TargetModelCode        = @TargetModelCode       
    ,@TargetEntityClassCode  = @TargetEntityClassCode 
    ,@MappingRole            = @MappingRole           
    ,@ProcessStepType        = @ProcessStepType       
    ,@Action                 = @Action                
;

SELECT 'ReturnCode: ',@ReturnCode;
Posted 3 weeks agoby Dirk.Lerner

usp Compiler

Valid as of release 3.0.0 Purpose The purpose of this stored procedure is to compile module-templates and apply design metadata…

Valid as of release 3.0.0

Purpose

The purpose of this stored procedure is to compile module-templates and apply design metadata to it — during metadata installation. Sections for a module-template are provided by the ExMeX Framework Core itself or may be added by an ExMeX framework Core administrator with the stored procedure USP - uspSetCompilerModuleTemplate.

Furthermore, this stored procedure creates the possibility for the ExMeX Framework admin to test and execute modules, e.g. after changing a code snippet for a template section — without running a batch for it.

Motivation

The motivation for this stored procedure is driven by different use cases but not limited to these:

  • Enable the ExMeX Framework Core to generate modules for different code languages
  • Enable ExMeX framework Core administrators to test and execute additional code snippets for a load pattern or a single module
 
Applicability

The stored procedure is applied each time new design metadata is provided to the ExMeX Framework Core. This usually happens during the metadata installation. But not limited to.

Content

This stored procedure is used to compile module templates, apply design metadata and execute selected modules for testing purposes.

Execution

See also code snippets in the example section below.

EXEC @ReturnCode = MetadataZoneCore.uspCompiler
        -- Load Pattern Id which will be compiled
         @LoadPatternId         = @LoadPatternId 
        -- Language code -> switch for pattern
        -- Optional, global variable is applied. Can be overriden by this
        ,@LanguageCode          = @LanguageCode
        
        -- Compiler options
        -- 'TPL'  - build final template (tpl) - default
        -- 'XDM'  - build template and apply design metadata - compile (eXecute with Design Metadata - XDM)
        -- 'MOD'  - build example modul (LPS) (design metadata applied, executable returned, not yet persisted)
        -- 'XMOD' - execute module
        ,@CompilerExec          = @CompilerExec

        -- Compile results:
        -- * (TPL) Finally compiled template bevore design metadata is applied
        ,@CompilerTemplate      = @CompilerTemplate OUTPUT
        -- * (MOD) Finally compiled module (LPS) with design metadata applied
        ,@CompilerModule        = @CompilerModule OUTPUT

        -- Mapping/LPS (optional)
        ,@SourceModelCode       = @SourceModelCode
        ,@SourceEntityClassCode = @SourceEntityClassCode
        ,@TargetModelCode       = @TargetModelCode
        ,@TargetEntityClassCode = @TargetEntityClassCode
        ,@MappingRole           = @MappingRole
        ,@ProcessStepType       = @ProcessStepType
;

 

Which parameter provides the usp?

The following blocks list all parameters available for the stored procedure:

@LoadPatternId BIGINT

Mandatory

The Load Pattern Id to which the given code snippet should be applied to. E.g. 1 for Hub

@TemplateLanguageCode NVARCHAR(256)

Mandatory

By the ExMeX Framework Core supported language code. This may be extended in future. See also section Framework Core in Depth -> LPS Template -> What LPS templates are available. E.g.

N'TSQL'

@CompilerExec NVARCHAR(10)

Mandatory

Compiler options:

-- 'TPL'  - build final template (tpl) - default
-- 'XDM'  - build template and apply design metadata - compile (eXecute with Design Metadata - XDM)
-- 'MOD'  - build example modul (LPS) (design metadata applied, executable returned, not yet persisted)
-- 'XMOD' - execute module

@SourceModelCode NVARCHAR(100)
@TargetModelCode NVARCHAR(100)
@TargetEntityClassCode NVARCHAR(100)
@MappingRole NVARCHAR(256)
@ProcessStepType NVARCHAR(50)

Optional

If a code snippet should be applied to a specific mapping, then set mapping with source and and target data objects , mapping role:

SELECT SourceModelCode
,SourceEntityClassCode
,TargetModelCode
,TargetEntityClassCode
,MappingRole
FROM MetadataZone.Mapping

ProcessStepType is ‘DEF’, ‘CDC’ or ‘SSIS’

Example:

EXEC [...]
    ,@SourceModelCode        = N'STAGE_LAYER'
    ,@TargetModelCode        = N'CoreLayerwithHashKey'
    ,@ProcessStepType        = N'DEF'
    ,@SourceEntityClassCode  = N'ArtworkInMuseum'
    ,@TargetEntityClassCode  = N'HubMuseum'
    ,@MappingRole            = N'ArtworkInMuseum2HubMuseum'
,@ProcessStepType = 'DEF' [...]
Return Codes

This stored procedure returns the values listed below:

0

Successful execution.

>1

Any error occurred generally spoken

47

Violation of one of the submitted values. See more information in provided error-message.

Considerations and consequences

With this stored procedure it is possible to compile templates of the ExMeX Framework Core and apply design metadata to it.

Example

The following code snippets show how a design pattern template can be compiled.

The examples, if used as shown, will execute at least one random hub module. Keep in mind that some data may be populated in the target data object, which may have severe consequences!

Template
Compile Hub template

 

@LoadPatternId = 1 and @CompilerExec = N'TPL'

@CompilerTemplate will receive the compiled template. Which is possible to run in SQL Server Management Studio or in Azur Data Studio for testing.

-- Decalare and set parameter for usp
DECLARE  @LoadPatternId             BIGINT          = 1
        ,@LanguageCode              NVARCHAR(256)   = NULL
        ,@CompilerTemplate          NVARCHAR(MAX)   = N'nope' --OUTPUT
        ,@CompilerModule            NVARCHAR(MAX)   = N'nope' --OUTPUT
        ,@CompilerExec              NVARCHAR(10)    = N'TPL' 
        ,@ReturnCode                INT             = NULL
;        
        
EXEC @ReturnCode = MetadataZoneCore.uspCompiler
        -- Load Pattern Id which will be compiled
         @LoadPatternId         = @LoadPatternId 
        -- Language code -> switch for pattern
        -- Optional, global variable is applied. Can be overwritten by this
        ,@LanguageCode          = @LanguageCode
        
        -- Compiler options
        ,@CompilerExec          = @CompilerExec

        -- Compile results:
        ,@CompilerTemplate      = @CompilerTemplate OUTPUT
        ,@CompilerModule        = @CompilerModule OUTPUT
;

SELECT  @ReturnCode         AS ReturnCode
       ,@CompilerTemplate   AS CompilerTemplate
       ,@CompilerModule     AS CompilerModule
;
Compile All Modules
Compile Hub template and apply design metadata to modules and persisted as precompiled executable modules

 

@LoadPatternId = 1 and @CompilerExec = N'XDM'

@CompilerTemplate will receive the compiled template. Which is possible to run in SQL Server Management Studio or in Azur Data Studio for testing.

New and updated modules will be written into MetadataZoneCore.BatchExecuteLoadProcessStep.

-- Decalare and set parameter for usp
DECLARE  @LoadPatternId             BIGINT          = 1
        ,@LanguageCode              NVARCHAR(256)   = NULL
        ,@CompilerTemplate          NVARCHAR(MAX)   = N'nope' --OUTPUT
        ,@CompilerModule            NVARCHAR(MAX)   = N'nope' --OUTPUT
        ,@CompilerExec              NVARCHAR(10)    = N'XDM' 
        ,@ReturnCode                INT             = NULL
;        
        
EXEC @ReturnCode = MetadataZoneCore.uspCompiler
        -- Load Pattern Id which will be compiled
         @LoadPatternId         = @LoadPatternId 
        -- Language code -> switch for pattern
        -- Optional, global variable is applied. Can be overwritten by this
        ,@LanguageCode          = @LanguageCode
        
        -- Compiler options
        ,@CompilerExec          = @CompilerExec

        -- Compile results:
        ,@CompilerTemplate      = @CompilerTemplate OUTPUT
        ,@CompilerModule        = @CompilerModule OUTPUT
;

SELECT  @ReturnCode         AS ReturnCode
       ,@CompilerTemplate   AS CompilerTemplate
       ,@CompilerModule     AS CompilerModule
;
Sample Module
Compile Hub template and apply design metadata to a sample module (not persisted)

 

@LoadPatternId = 1 and @CompilerExec = N'MOD'

@CompilerTemplate will receive the compiled template. Which is possible to run in SQL Server Management Studio or in Azur Data Studio for testing.

@CompilerExec will receive the compiled sample module. Which is possible to run in SQL Server Management Studio or in Azur Data Studio for testing.

-- Decalare and set parameter for usp
DECLARE  @LoadPatternId             BIGINT          = 1
        ,@LanguageCode              NVARCHAR(256)   = NULL
        ,@CompilerTemplate          NVARCHAR(MAX)   = N'nope' --OUTPUT
        ,@CompilerModule            NVARCHAR(MAX)   = N'nope' --OUTPUT
        ,@CompilerExec              NVARCHAR(10)    = N'MOD' 
        ,@ReturnCode                INT             = NULL
;        
        
EXEC @ReturnCode = MetadataZoneCore.uspCompiler
        -- Load Pattern Id which will be compiled
         @LoadPatternId         = @LoadPatternId 
        -- Language code -> switch for pattern
        -- Optional, global variable is applied. Can be overwritten by this
        ,@LanguageCode          = @LanguageCode
        
        -- Compiler options
        ,@CompilerExec          = @CompilerExec

        -- Compile results:
        ,@CompilerTemplate      = @CompilerTemplate OUTPUT
        ,@CompilerModule        = @CompilerModule OUTPUT
;

SELECT  @ReturnCode         AS ReturnCode
       ,@CompilerTemplate   AS CompilerTemplate
       ,@CompilerModule     AS CompilerModule
;
Execute Sample Module
Compile Hub template and apply design metadata to a (not persisted) sample module and execute it

 

@LoadPatternId = 1 and @CompilerExec = N'XMOD'

@CompilerTemplate will receive the compiled template. Which is possible to run in SQL Server Management Studio or in Azur Data Studio for testing.

The stored procedure will return the loging statistics of the executed module.

-- Decalare and set parameter for usp
DECLARE  @LoadPatternId             BIGINT          = 1
        ,@LanguageCode              NVARCHAR(256)   = NULL
        ,@CompilerTemplate          NVARCHAR(MAX)   = N'nope' --OUTPUT
        ,@CompilerModule            NVARCHAR(MAX)   = N'nope' --OUTPUT
        ,@CompilerExec              NVARCHAR(10)    = N'XMOD' 
        ,@ReturnCode                INT             = NULL
;        
        
EXEC @ReturnCode = MetadataZoneCore.uspCompiler
        -- Load Pattern Id which will be compiled
         @LoadPatternId         = @LoadPatternId 
        -- Language code -> switch for pattern
        -- Optional, global variable is applied. Can be overwritten by this
        ,@LanguageCode          = @LanguageCode
        
        -- Compiler options
        ,@CompilerExec          = @CompilerExec

        -- Compile results:
        ,@CompilerTemplate      = @CompilerTemplate OUTPUT
        ,@CompilerModule        = @CompilerModule OUTPUT
;

SELECT  @ReturnCode         AS ReturnCode
       ,@CompilerTemplate   AS CompilerTemplate
       ,@CompilerModule     AS CompilerModule
;
Execute Selected Module
Compile Hub template and apply design metadata to a (not persisted) selected module and execute it

 

@LoadPatternId = 1 and @CompilerExec = N'XMOD'

@CompilerTemplate will receive the compiled template. Which is possible to run in SQL Server Management Studio or in Azur Data Studio for testing.

The stored procedure will return the loging statistics of the executed module.

-- Decalare and set parameter for usp
DECLARE  @LoadPatternId             BIGINT          = 1
        ,@LanguageCode              NVARCHAR(256)   = NULL
        ,@CompilerTemplate          NVARCHAR(MAX)   = N'nope' --OUTPUT
        ,@CompilerModule            NVARCHAR(MAX)   = N'nope' --OUTPUT
        ,@CompilerExec              NVARCHAR(10)    = N'XMOD' 
        ,@ReturnCode                INT             = NULL

        -- Mapping/LPS (optional)
        ,@SourceModelCode           NVARCHAR(100)   = NULL
        ,@SourceEntityClassCode     NVARCHAR(100)   = NULL
        ,@TargetModelCode           NVARCHAR(100)   = NULL
        ,@TargetEntityClassCode     NVARCHAR(100)   = NULL
        ,@MappingRole               NVARCHAR(256)   = NULL
        ,@ProcessStepType           NVARCHAR(50)    = NULL
; SET @SourceModelCode = N'STAGE_LAYER' SET @TargetModelCode = N'CoreLayerwithHashKey' SET @ProcessStepType = N'DEF' SET @SourceEntityClassCode = N'ArtworkInMuseum' SET @TargetEntityClassCode = N'HubMuseum' SET @MappingRole = N'ArtworkInMuseum2HubMuseum' EXEC @ReturnCode = MetadataZoneCore.uspCompiler -- Load Pattern Id which will be compiled @LoadPatternId = @LoadPatternId -- Language code -> switch for pattern -- Optional, global variable is applied. Can be overwritten by this ,@LanguageCode = @LanguageCode -- Compiler options ,@CompilerExec = @CompilerExec -- Compile results: ,@CompilerTemplate = @CompilerTemplate OUTPUT ,@CompilerModule = @CompilerModule OUTPUT -- Mapping/LPS (optional) ,@SourceModelCode = @SourceModelCode ,@SourceEntityClassCode = @SourceEntityClassCode ,@TargetModelCode = @TargetModelCode ,@TargetEntityClassCode = @TargetEntityClassCode ,@MappingRole = @MappingRole ,@ProcessStepType = @ProcessStepType ; SELECT @ReturnCode AS ReturnCode ,@CompilerTemplate AS CompilerTemplate ,@CompilerModule AS CompilerModule ;
Posted 3 weeks agoby Dirk.Lerner

Extensions By User

First, we will explain how to include a user's SQL script extension as well as how to include additional user-specific global variables.

How to add a user-specific variables and SQL scripts?

For and during the installation of the metadata and thus also for the deployment to different environments, such as development,…

For and during the installation of the metadata and thus also for the deployment to different environments, such as development, test and production, it is possible for the data modeler or DevOps to include their own user-specific SQL scripts and variables in the installation without much effort.

Purpose

The idea behind this is that it should be possible for data modelers or DevOps to include additional, non-framework specific variables and SQL scripts or artifacts of the ExMeX tools in the installation or deployment of the metadata.

Motivation

Environments that do not allow direct access by the ExMeX Companion App, respectively for the DevOps or data modelers, it is necessary to include SQL scripts or variables in the deployment and installation.
Or to reduce the manual steps in a continuous deployment process.

Applicability

The SQL scripts or variables created, and tested by DevOps or data modelers are automatically executed at the end of the metadata installation.

Content

The examples below show on the one hand how to include the artifacts of the ExMeX tool Set Batch Setting.
Another example shows how to create your own user-specific variables and how to install them in the ExMeX Framework Core.

Considerations and consequences

Since an execution of a user-specific SQL scripts and variables may have far-reaching consequences for all parts of the ExMeX Framework Core, they should be chosen carefully.

For example, a user-specific script for setting the batch settings can cause the values set with the ExMeX Companion App to be overwritten after each installation. This is also one of the main reasons why the ExMeX Tools are not a standard part of the metadata installation.

Example

The following, sample step-by-step instructions show how to install an artifact of the ExMeX tool Set Batch Setting (aka user-specific SQL script) as well as user-specific variables.

Add artifact of ExMeX tool Set Load Batch

Please follow all the steps below to add an artifact of the ExMeX tool Set Batch Setting to the Metadata Installation. For more information on the ExMeX tool Set Batch Setting see section Toolbox -> Set Batch Setting -> How to configure Batch settings without the Companion App?

  • Copy “./library/tool/Set Batch Setting/SetBatchSetting.sql” file to “./usr/query/SetBatchSetting.sql”.
  • Add the execution command for the script "./usr/query/SetBatchSetting.sql" at the end (or the desired order) of the file "./usr/sqlcmdquery.sql":
    :r $(InitPath)"\usr\query\SetBatchSetting.sql”
  • Done


Add user-specific global variables

Please follow all the steps below to add user-specific global variables to the Metadata Installation. For more information on global variables see section Getting Started -> Configuration -> How do you set global variables and what is their purpose?

  • Create “./usr/query/LoadConfigUserSpecificVariables.sql” file.
  • Add the following code-snippet to “./usr/query/LoadConfigUserSpecificVariables.sql” file and adapt it to your needs:
    /*----------------------------------------------------------------------------*/
    /*-- Declare variable                                                         */
    /*----------------------------------------------------------------------------*/
    DECLARE @UserSpecificReturnNumber      INT;
    
    /*----------------------------------------------------------------------------*/
    /*-- Set variable initial                                                     */
    /*----------------------------------------------------------------------------*/
    SET @UserSpecificReturnNumber = 0;
    
    /*----------------------------------------------------------------------------*/
    /*-- Set global variable                                                      */
    /*----------------------------------------------------------------------------*/
    EXEC @UserSpecificReturnNumber = MetadataZoneCore.uspConfigExMeXFramework
        @VariableName         = 'UserSpecificName' --@VariableName   
        -- Varaibale value can be set in  ./etc/env/SetEnv*.sql   
    	 -- if it needs to be environment specific 
       ,@VariableValue        = @YourSpecificSetEnvValue --@VariableValue      
       ,@VariableDatatype     = 'NVARCHAR(256)' --@VariableDatatype   
       ,@VariableDescription  = 'Special user specific variable' --@VariableDescription
    
    SELECT @UserSpecificReturnNumber;
  • Add the execution command for the script "./usr/query/LoadConfigUserSpecificVariables.sql" at the end (or the desired order) of the file "./usr/sqlcmdquery.sql":
    :r $(InitPath)"\usr\query\LoadConfigUserSpecificVariables.sql”
  • Done

 

Posted 10 months agoby Dirk.Lerner