Configuration

How do you set global variables and what is their purpose (until release 2.12.x) ?

Valid as of release 2.12.0 - Valid until release 2.12.0 In the ExMeX Framework Core, there are global variables for…

Valid as of release 2.12.0 - Valid until release 2.12.0

In the ExMeX Framework Core, there are global variables for controlling various processes and for configuration. These include general settings, data logistics in batches and (Load Process Steps (LPS) or also the configuration of data for historisation.

Purpose

A dynamic access to global variables from all parts of the ExMeX Framework Core at runtime.

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.

Applicability

All global variables can be set by the data modeller or the DevOp via the ExMeX Companion App or via the stored procedure MetadataZoneCore.uspConfigExMeXFramework. See also the examples below or for more detailed information the section Framework in Depth -> Stored Procedures.

Content

The following table shows all available global variables in the ExMeX Framework Core.

Variable Name  Value Type  Effectiveness Meaning Documentation
 MaxSqlCommandInChunk 1 to 231-1 INT immediately  Defines the max number of LPS which are executed in parallel. How to use variable MaxSqlCommandInChunk?
 ExitOnError 0 = Off, 1 = Exit BIT immediately  If an error occurs in the batch, the batch is terminated or the error is ignored and the batch continues; How to use variable ExitOnError?
 FKConstraintOnSatelliteLPS 0 = Off, 1 = On BIT After generating the LPS  Switch functionality to enable or disable FK-Constraint checks on Satellite (Child) to Hub/Link (Parent). How to use variable FKConstraintOnSatelliteLPS and FKConstraintOnLinkLPS?
 FKConstraintOnLinkLPS 0 = Off, 1 = On BIT After generating the LPS  Switch functionality to enable or disable FK-Constraint checks on Link (Child) to Hub (Parent). How to use variable FKConstraintOnSatelliteLPS and FKConstraintOnLinkLPS?
 DayUsedForAvgFkCheck 1 to 255 TINYINT immediately   Defines the number x to receive average rows written for a given LPS within the last x days, using statistic data of the ExMeX Logging Framework. How to use variable FKConstraintOnSatelliteLPS and FKConstraintOnLinkLPS?
 RowsMaxInsertedFkCheck 1 to 231-1 INT immediately  Threshold value on average rows written within the last x days (defined by global variable DayUsedForAvgFkCheck) to decide whether a FK should be deactivated or not. How to use variable FKConstraintOnSatelliteLPS and FKConstraintOnLinkLPS?
 UseTargetEntityDB 0 = Off, 1 = On BIT After generating the LPS  Switch functionality to enable or disable usage of SQL-Statement USE [<TargetDatabaseName>] within all LPS. How to use variable UseTargetEntityDB?
 HousekeepingHiZAmount 1 to 231-1 INT immediately  Amount of units which will be kept. All others can be archived. Housekeeping
 HousekeepingHiZUnit ProcessStepRun,
DAY,
WEEK,
MONTH,
QUARTER,
YEAR
NVARCHAR(256) immediately  Units of amount to be left in table. Housekeeping
 HousekeepingHiZAttributeCode User defined NVARCHAR(256) immediately  Column which is used to filter for housekeeping. Housekeeping
 HighEndTimestamp 2999-12-31 00:00:00.0000000 (Default) DATETIME2 After generating the LPS  Overall valid High End Timestamp for LPS and Satellites.  
 LowEndTimestamp 1970-01-01 00:00:00.0000000 (Default) DATETIME2 After generating the LPS  Overall valid Low End Timestamp for LPS and Satellites.  
 SSISLinkedServer User defined NVARCHAR(256) immediately  Value of variable inserted from env-variables of file etc\env\SetEnv*.sql  
 SSISProjectName User defined NVARCHAR(256) immediately  Value of variable inserted from env-variables of file etc\env\SetEnv*.sql  
 SSISFolderName User defined NVARCHAR(256) immediately  Value of variable inserted from env-variables of file etc\env\SetEnv*.sql  
 SSISProxyAccountName User defined NVARCHAR(256) immediately  Value of variable inserted from env-variables of file etc\env\SetEnv*.sql  
Considerations and consequences

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 2 years agoby Dirk.Lerner

How do you set global variables and what is their purpose (as of 2.13.0) ?

Valid as of release 2.13.0 In the ExMeX Framework Core, there are global variables for controlling various processes and for…

Valid as of release 2.13.0

In the ExMeX Framework Core, there are global variables for controlling various processes and for configuration. These include general settings, data logistics in batches and (Load Process Steps (LPS) or also the configuration of data for historisation.

Purpose

A dynamic access to global variables from all parts of the ExMeX Framework Core at runtime.

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.

Applicability

All global variables can be set by the data modeller or the DevOp via the ExMeX Companion App or via the stored procedure MetadataZoneCore.uspConfigExMeXFramework. See also the examples below or for more detailed information the section Framework in Depth -> Stored Procedures.

The following picture shows the configuration of global variables with the ExMeX Companion App:

ExMeX Companion App - Core Configuration Window - Setting variable language code

Content

The following table shows all available global variables in the ExMeX Framework Core.

Variable Name  Value Datatype Variable Type  Effectiveness Meaning Documentation
MaxSqlCommandInChunk 1 to 231-1 INT Batch immediately Defines the max number of LPS which are executed in parallel. How to use variable MaxSqlCommandInChunk?
ExitOnError 0 = Off, 1 = Exit BIT Batch immediately If an error occurs in the batch, the batch is terminated or the error is ignored and the batch continues; How to use variable ExitOnError?
FKConstraintOnSatelliteLPS 0 = Off, 1 = On BIT LPS When compiling a module (LPS) Switch functionality to enable or disable FK-Constraint checks on Satellite (Child) to Hub/Link (Parent). How to use variable FKConstraintOnSatelliteLPS and FKConstraintOnLinkLPS?
FKConstraintOnLinkLPS 0 = Off, 1 = On BIT LPS When compiling a module (LPS) Switch functionality to enable or disable FK-Constraint checks on Link (Child) to Hub (Parent). How to use variable FKConstraintOnSatelliteLPS and FKConstraintOnLinkLPS?
DayUsedForAvgFkCheck 1 to 255 TINYINT LPS immediately  Defines the number x to receive average rows written for a given LPS within the last x days, using statistic data of the ExMeX Logging Framework. How to use variable FKConstraintOnSatelliteLPS and FKConstraintOnLinkLPS?
RowsMaxInsertedFkCheck 1 to 231-1 INT LPS immediately Threshold value on average rows written within the last x days (defined by global variable DayUsedForAvgFkCheck) to decide whether a FK should be deactivated or not. How to use variable FKConstraintOnSatelliteLPS and FKConstraintOnLinkLPS?
UseTargetEntityDB 0 = Off, 1 = On BIT LPS When compiling a module (LPS)  Switch functionality to enable or disable usage of SQL-Statement USE [<TargetDatabaseName>] within all LPS. How to use variable UseTargetEntityDB?
HashKeyDelimiter

For example

\@|

NVARCHAR(3) Hash When compiling a module (LPS) Configuring Hash-Key and Hash-Diff - Part 1:
Define delimiter between to columns concatenated for hashing.
 
HashAlgorithm MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512 NVARCHAR(8) Hash When compiling a module (LPS) Configuring Hash-Key and Hash-Diff - Part 2:
Choose Hash Algorithm
 
HashConvertCharLength CONVERT(CHAR(40),Possible Values: MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512According CharLength: 32 | 32 | 32 | 40 | 40 | 64 | 128 NVARCHAR(25) Hash When compiling a module (LPS) If hash value as CHAR is preferred:
Update according CharLength to HashAlgorithm
(Not recommended)
 
HashConvertCharLengthEnd ,2) NVARCHAR(3) Hash When compiling a module (LPS) If hash value as CHAR is preferred.
(Not recommended)
 
HousekeepingHiZAmount 1 to 231-1 INT   immediately Amount of units which will be kept. All others can be archived. Housekeeping
HousekeepingHiZUnit ProcessStepRun,
DAY,
WEEK,
MONTH,
QUARTER,
YEAR
NVARCHAR(256)   immediately Units of amount to be left in table. Housekeeping
HousekeepingHiZAttributeCode User defined NVARCHAR(256)   immediately Column which is used to filter for housekeeping. Housekeeping
HighEndTimestamp 2999-12-31 00:00:00.0000000 (Default) DATETIME2 SYSTEM When compiling a module (LPS)

Overall valid High End Timestamp for LPS and Satellites.

DO NOT CHANGE!

 
LowEndTimestamp 1970-01-01 00:00:00.0000000 (Default) DATETIME2 SYSTEM When compiling a module (LPS)

Overall valid Low End Timestamp for LPS and Satellites.

DO NOT CHANGE!

 
SSISLinkedServer User defined NVARCHAR(256) USER immediately Value of variable inserted from env-variables of file etc\env\SetEnv*.sql  
SSISProjectName User defined NVARCHAR(256) USER immediately Value of variable inserted from env-variables of file etc\env\SetEnv*.sql  
SSISFolderName User defined NVARCHAR(256) USER immediately Value of variable inserted from env-variables of file etc\env\SetEnv*.sql  
SSISProxyAccountName User defined NVARCHAR(256) USER immediately Value of variable inserted from env-variables of file etc\env\SetEnv*.sql  
SequenceAsDataVaultSK

0 = Off (aka Hash),

1 = On (aka Sequence)

BIT SYSTEM When compiling a module (LPS) Switch functionality to enable or disable usage of Sequences as Surrogate Key within all Data Vault LPS (Hubs, Links and Satellites).  
ColumnStatusCDC

For example:

'StatusCDC'

NVARCHAR(256) Column When compiling a module (LPS) Set code for column which will contain StatusCDC flag (I,U,D,X).  
ColumnAuditTrailId

For example:

'AuditTrailId' or NULL

NVARCHAR(256) Column When compiling a module (LPS) Set code for column which will contain ''Audit Trail Id'' from ExMeX Logging Framework.  
ColumnEntityId

For example:

'EntityId' or NULL

NVARCHAR(256) Column When compiling a module (LPS) Set code for column which will contain ''Entity Id'' from ExMeX Logging Framework. If no value is set (empty or NULL), the column will not be created at all.  
ColumnLoadTimestamp

For example:

'LoadTimestamp' or NULL

NVARCHAR(256) Column When compiling a module (LPS) - Hubs & Links Only Set code for column which will contain a timestamp for Load Date (LDTS, Load Date Timestamp) in Data Vault concept Hub or Link. If no value is set (empty or NULL), the column will not be created att all.  
SatColumnLoadTimestamp

For example:

'LoadTimestamp'

NVARCHAR(256) Column When compiling a module (LPS) - Satellites Only Set code for column which will contain a timestamp for Load Date (LDTS, Load Date Timestamp) in Data Vault concept Satellite. From timestamp of a satellite timeperiod [Closed;Open[ of assertion timeline. This code may be the same or different to the one in Hub or Link.  
SatColumnLoadEndTimestamp

For example:

'LoadEndTimestamp'

NVARCHAR(256) Column When compiling a module (LPS) Set code for column which will contain a timestamp for Load Date (LEDTS, Load End Date Timestamp) in Data Vault concept Satellite. This code may be the same or different to the one in Hub or Link.  
ColumnRecordSource

For example:

'RecordSource' OR NULL

NVARCHAR(256) Column When compiling a module (LPS) Set code for column which will contain ''Record Source'' in Data Vault concept Hub, Link or Satellite. If no value is set (empty or NULL), the column will not be created at all.  
ColumnHashDiff

For example:

'HashDiff'

NVARCHAR(256) Column When compiling a module (LPS) Set code for column which will contain ''HashDiff'' in Data Vault concept Satellite with HashDiff.  

ColumnHashDiffCDC NVARCHAR(256)

Type: COLUMN

Set code for column which will contain 'HashDiffCDC' in CDC Data Pipeline or One To One Data Copy.

For example: N'HashDiffCDC'

Effectiveness: When compiling a module (LPS)

See also:

LanguageCode NVARCHAR(256)

Type: SYSTEM

Set language code for ExMeX compiler. Define in which language (e.g. TSQL or ORCL) modules should be compiled.

For example: N'TSQL'

Valid as of release 3.0.0

Effectiveness: When compiling a module (LPS)

See also:

Considerations and consequences

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.

LPS

Not all global variables already work with all templates for compiling the LPS. For more information about the LPS templates and the state of integration of the global variables see also section Framework in Depth -> LPS Template -> What LPS templates are available?

Deployment

In case the settings for global variables should be deployed to other environments, such as development, test or production, please refer to the section Framework Core in Depth -> Extensions By User -> How to add a user-specific variables and SQL scripts?

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 2 years agoby Dirk.Lerner