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 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'
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 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:
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'