Global & LPS Variable Configuration

How do global or LPS variables affect LPS execution?

The global and LPS configuration variables listed in the following sections directly or indirectly influence the execution of any LPS.…

The global and LPS configuration variables listed in the following sections directly or indirectly influence the execution of any LPS. In general, the global variables shown in this section affect all LPSs.

An overview of the global variables and their current configuration is provided by the view MetadataZone.ConfigExMeXFramework or in the Getting Started -> Configuration section.

Mode of action according to priority

1stGlobally configured variables are used during LPS generation

2ndGlobally configured variables are applied during LPS execution

Posted 1 year agoby admin.tedamoh

How to use variable UseTargetEntityDB?

Valid as of release 2.12.0 The database on which an LPS is executed can be set via the variable UseTargetEntityDB.…

Valid as of release 2.12.0

The database on which an LPS is executed can be set via the variable UseTargetEntityDB.

If UseTargetEntityDB = 0 (OFF), the LPS is executed on the database in which the ExMeX Framework Core is installed.

If UseTargetEntityDB = 1 (ON), the LPS is executed on the database in which the table of the LPS to be loaded is located. The LPS is preceded by

USE [<TargetDatabaseName>].

Mode of action according to priority

1stGlobally configured variable UseTargetEntityDB applied during generation of LPS. No ad-hoc changes are yet possible!

Applicable

The variable affects all Data Vault load processes (Hub, Link and Satellites) as well as LPSs like CDC-, One-To-On- or References-LPS generated by the ExMeX Framework Core.

The variable does not affect ETL/ELT packages that are outside the responsibility of the ExMeX Framework Core and are "only" called by the ExMeX Framework Core. For example, SSIS or SSAS packages/scripts.

ExMeX Companion App

From version 0.3.0 it is possible to configure the global variable with the ExMeX Companion App.

ExMeX Companion App Config configuration Window UseTargetEntityDB

ExMeX Framework Core Scripts

Not available.

ExMeX Framework Core Global Variable configuration

To configure this variable, the data modeler or DevOp has to execute the stored procedure MetadataZoneCore.uspConfigExMeXFramework within the ExMeX Framework Core to update the global config variable.

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

The current settings of all global variables can be checked with the following DML:

SELECT *
FROM MetadataZone.ConfigExMeXFramework

Posted 1 year agoby admin.tedamoh

How to use variable FKConstraintOnSatelliteLPS and FKConstraintOnLinkLPS?

Valid as of release 2.12.0 Whether an LPS deactivates existing RI constraints in the database for the target database object…

Valid as of release 2.12.0

Whether an LPS deactivates existing RI constraints in the database for the target database object (Link or Satellite) before loading and reactivates them after execution can be set via the variable FKConstraintOnSatelliteLPS or FKConstraintOnLinkLPS.

If FKConstraintOnSatelliteLPS = 0 (OFF), the LPS "ignores" all RI constraints on Satellites. This means that if RI constraints are installed, they remain active during loading.

If FKConstraintOnSatelliteLPS = 1 (ON), the LPS "deactivates" all RI constraints on the Satellite before loading if the other conditions of the global variables DayUsedForAvgFkCheck and RowsMaxInsertedFkCheck are fulfilled. This means that if RI constraints are installed, they are set to inactive during loading and only reactivated after loading.

If FKConstraintOnLinkLPS = 0 (OFF), the LPS "ignores" all RI constraints on Links. This means that if RI constraints are installed, they remain active during loading.

If FKConstraintOnLinkLPS = 1 (ON), the LPS "deactivates" all RI constraints on the Link before loading if the other conditions of the global variables DayUsedForAvgFkCheck and RowsMaxInsertedFkCheck are fulfilled. This means that if RI constraints are installed, they are set to inactive during loading and only reactivated after loading.

DayUsedForAvgFkCheck defines the number x (1-255) to receive average rows written for a given LPS within the last x days, using statistic data of the ExMeX Logging Framework.

RowsMaxInsertedFkCheck is the threshold value on average rows written within the last x days (defined by global variable DayUsedForAvgFkCheck) to decide whether a RI constraint should be deactivated or not. This threshold value is determined each time the LPS is executed!

Example: On average over the last 10 days (DayUsedForAvgFkCheck) more than 200,000 (RowsMaxInsertedFkCheck) rows were loaded for the currently executed link LPS (FKConstraintOnLinkLPS = 1 (ON)), then all RI constraint on the link are deactivated.
If, on the other hand, less than 200,000 (RowsMaxInsertedFkCheck) rows have been loaded on average over the last 10 days (DayUsedForAvgFkCheck) for the currently executed link LPS (FKConstraintOnLinkLPS = 1 (ON)), then all RI constraint remain activated on the link.

Mode of action according to priority

1stGlobally configured variables FKConstraintOnLinkLPS and FKConstraintOnSatelliteLPS are applied during generation of LPS. No ad-hoc changes are yet possible!

Applicable

The variables affects all Data Vault load processes (Link and Satellites, which may be affected by RI-Constraints) generated by the ExMeX Framework Core.

The variable does not affect ETL/ELT packages that are outside the responsibility of the ExMeX Framework Core and are "only" called by the ExMeX Framework Core. For example, SSIS or SSAS packages/scripts.

ExMeX Companion App

From version 0.3.0 it is possible to configure the global variable with the ExMeX Companion App.

ExMeX Companion App Config configuration Window FKConstraintOnSatelliteLPS

ExMeX Framework Core Scripts

Not available.

ExMeX Framework Core Global Variable configuration

To configure this variable, the data modeler or DevOp has to execute the stored procedure MetadataZoneCore.uspConfigExMeXFramework within the ExMeX Framework Core to update the global config variable.

EXEC MetadataZoneCore.uspConfigExMeXFramework
     @VariableName         = 'FKConstraintOnSatelliteLPS' 
    ,@VariableValue        = '0' 
    ,@VariableDatatype     = 'BIT'
    ,@VariableDescription  = 'Switch functionality to enable or  FK-Constraint checks on Satellite (Child) to Hub/Link (Parent). 0 = Off, 1 = On'

EXEC MetadataZoneCore.uspConfigExMeXFramework
     @VariableName         = 'FKConstraintOnLinkLPS'
    ,@VariableValue        = '0'    
    ,@VariableDatatype     = 'BIT'
    ,@VariableDescription  = 'Switch functionality to enable or  FK-Constraint checks on Link (Child) to Hub (Parent). 0 = Off, 1 = On'

EXEC MetadataZoneCore.uspConfigExMeXFramework
     @VariableName         = 'DayUsedForAvgFkCheck'   
    ,@VariableValue        = '10'
    ,@VariableDatatype     = 'TINYINT' 
    ,@VariableDescription  = 'Defines the number x (1-255) to receive average rows written for a given LPS within the last x days, using statistic data of the ExMeX Logging Framework.'

EXEC MetadataZoneCore.uspConfigExMeXFramework
     @VariableName         = 'RowsMaxInsertedFkCheck'     
    ,@VariableValue        = '200000'       
    ,@VariableDatatype     = 'INT' 
    ,@VariableDescription  = '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.'

The current settings of all global variables can be checked with the following DML:

SELECT *
FROM MetadataZone.ConfigExMeXFramework
Posted 1 year agoby admin.tedamoh