Getting Started

There are no big problems, there are just a lot of little problems.Henry Ford
 

This document is a documentation of TEDAMOH ExMeX Framework. Feel free to report all findings to us!

Install Framework

This chapter describes how to install and update the ExMeX Framework Core.
First the initial installation is explained, then the configuration as well as the update of the ExMeX Framework Core.

How to install a new ExMeX Framework Core (as of release 2.13.0) ?

Valid as of release 2.13.0 To install ExMeX Framework Core in a new database, please download first the current version…

Valid as of release 2.13.0

To install ExMeX Framework Core in a new database, please download first the current version of the ExMeX Framework Core. An existing installed SQL Server 2017 (14.x) and later is required.

How to update to a new ExMeX Framework Core release, see the following section.

Now that you have downloaded the latest release, please follow all the steps below:

  1. Unpack the downloaded 7z file and copy the files to the new root directory, e.g. c:/GitRepos/<YourGitClone>/Tutorial ExMeX Framework/.
  2. Switch to your new root directory.
  3. Create a database (SQL Server 2017 (14.x) and later), e.g. a database named Tutorial-ExMeX-Metadata with script CreateTutorialDatabase.sql. Available for download here (You can put the file in the ./opt/tutorial folder):
  4. Switch to folder ./etc/config in root directory.
  5. Copy all files and remove "orig.", e.g.: ConfigExMeXFramework.orig.sql to ConfigExMeXFramework.sql
  6. Open the ConfigExMeXFramework.sql file.
  7. Change variable MetadataDatabase to the database where your ExMeX Framwork Core is installed. Example:
    :setvar MetadataDatabase <YourTEDAMOHExMeXDatabase>
    to e.g.
    :setvar MetadataDatabase Tutorial-ExMeX-Metadata
  8. Change variable MetadataPath to your metadata directory ./var/TEDAMOH-ExMeX. Keep the folder TEDAMOH-ExMeX at the end of the path if you will use a directory outside of your root directory! Example:
    :setvar MetadataPath "<YourTEDAMOHExMeXMetadataDirectory>\TEDAMOH-ExMeX"
    to e.g.
    :setvar MetadataPath "C:\…\Tutorial ExMeX Framework Core\var\TEDAMOH-ExMeX"
  9. Open the ‘ExMeX Set Config Variable.orig.sql’ file and configure the global variables as needed. All available global variables are described in detail in the following section Getting Started -> Configuration -> How do you set global variables and what is their purpose?.
    If you don't know what exactly to do here, skip the step for now and the default setting will be applied.
  10. Save and close file.
  11. Switch to folder ./usr/config in root directory.
  12. Copy all files and remove "orig.", e.g.: UsrConfigExMeXFramework.orig.sql to UsrConfigExMeXFramework.sql
  13. Switch back to your root directory.
  14. Copy all files and remove "orig.", e.g.: “Install TEDAMOH ExMeX Database Framework CMD.orig.sql” to “Install TEDAMOH ExMeX Database Framework CMD.sql”
  15. Open the "Install TEDAMOH ExMeX Database Framework CMD.sql" file.
  16. Set variable InitPath to your new root directory. Example:
    :setvar InitPath "<YourRootDirectory>"
    to e.g.
    :setvar InitPath "C:\…\Tutorial ExMeX Framework Core"
  17. Save and close file.
  18. Finally, to install a complete current version of the ExMeX Framework Core database, run the script Install TEDAMOH ExMeX Database Framework CMD.sql in SSMS with SQLCMD Mode on.
  19. Done.

It is recommended to ignore all modified files located in ./usr/config within your code versioning tool, e.g.: .gitignore

Posted 2 years agoby Dirk.Lerner

How to install a new ExMeX Framework Core (until release 2.12.x) ?

Valid until release 2.12.0 To install ExMeX Framework Core in a new database, please download first the current version of…

Valid until release 2.12.0

To install ExMeX Framework Core in a new database, please download first the current version of the ExMeX Framework Core. An existing installed SQL Server 2017 (14.x) and later is required.

How to update to a new ExMeX Framework Core release, see the following section.

Now that you have downloaded the latest release, please follow all the steps below:

  1. Unpack the downloaded 7z file and copy the files to the new root directory, e.g. c:/GitRepos/<YourGitClone>/Tutorial ExMeX Framework/.
  2. Switch to your new root directory.
  3. Create a database (SQL Server 2017 (14.x) and later), e.g. a database named Tutorial-ExMeX-Metadata with script CreateTutorialDatabase.sql. Available for download here (You can put the file in the ./opt/tutorial folder):
  4. Switch to folder ./etc/config in root directory.
  5. Copy all files and remove "orig.", e.g.: ConfigExMeXFramework.orig.sql to ConfigExMeXFramework.sql
  6. Open the ConfigExMeXFramework.sql file.
  7. Change variable MetadataDatabase to the database where your ExMeX Framwork Core is installed. Example:
    :setvar MetadataDatabase <YourTEDAMOHExMeXDatabase>
    to e.g.
    :setvar MetadataDatabase Tutorial-ExMeX-Metadata
  8. Change variable MetadataPath to your metadata directory ./var/TEDAMOH-ExMeX. Keep the folder TEDAMOH-ExMeX at the end of the path if you will use a directory outside of your root directory! Example:
    :setvar MetadataPath "<YourTEDAMOHExMeXMetadataDirectory>\TEDAMOH-ExMeX"
    to e.g.
    :setvar MetadataPath "C:\…\Tutorial ExMeX Framework Core\var\TEDAMOH-ExMeX"
  9. Save and close file.
  10. Switch to folder ./usr/config in root directory.
  11. Copy all files and remove "orig.", e.g.: UsrConfigExMeXFramework.orig.sql to UsrConfigExMeXFramework.sql
  12. Switch back to your root directory.
  13. Copy all files and remove "orig.", e.g.: “Install TEDAMOH ExMeX Database Framework CMD.orig.sql” to “Install TEDAMOH ExMeX Database Framework CMD.sql”
  14. Open the "Install TEDAMOH ExMeX Database Framework CMD.sql" file.
  15. Set variable InitPath to your new root directory. Example:
    :setvar InitPath "<YourRootDirectory>"
    to e.g.
    :setvar InitPath "C:\…\Tutorial ExMeX Framework Core"
  16. Save and close file.
  17. Finally, to install a complete current version of the ExMeX Framework Core database, run the script Install TEDAMOH ExMeX Database Framework CMD.sql in SSMS with SQLCMD Mode on.
  18. Done.

It is recommended to ignore all modified files located in ./usr/config within your code versioning tool, e.g.: .gitignore

Posted 2 years agoby Dirk.Lerner

How do you set global variables for initial installation?

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

Instead of configuring the global variables in the database after each installation of the ExMeX Framework Core on different environments, such as development, test and production, this can be done once in the initialization script of the installation. This way a rollout via GIT is possible at any time and all environments have the identical configuration inital.

Applicability

For installation of the ExMeX Framework Core on different environments, such as development, test and production, all global variables can be set by the data modeller or the DevOp in the script ‘./etc/config/ExMeX Set Config Variable.sql’.

Check if the settings made in the script ‘./etc/config/ExMeX Set Config Variable.sql’ match the values set in the ExMeX Framework Core database after installation:

SELECT *
FROM MetadataZone.ConfigExMeXFramework
Content

All available global variables are described in detail in the following section Getting Started -> Configuration -> How do you set global variables and what is their purpose?.

Considerations and consequences

Since a change in global variables usually has far-reaching consequences for all parts of the ExMeX Framework Core, they should be chosen carefully.
At this point the global variables are configured exclusively for the installation or update (new global variables) of the framework. Afterwards the configuration set here has no more effects.

Later necessary changes, e.g. for the target database in which an LPS is to be executed (UseTargetEntityDB) can be carried out by a data modeler or DevOp as described in section Getting Started -> Configuration -> How do you set global variables and what is their purpose?.

Posted 11 months agoby Dirk.Lerner

How to update ExMeX Framework Core?

To update ExMeX Framework Core to the latest release we always recommend to perform the following three steps: Backup, Update…

To update ExMeX Framework Core to the latest release we always recommend to perform the following three steps: Backup, Update and Housekeeping.

Backup ExMeX Framework Core

Before updating an exiting ExMeX Framework Core to the newest release, it is recommend to backup your

  • ExMeX Framework Core Database,
  • ExMeX Framework Core root folder and
  • User specific add ons

If you do not backup, well it’s up to you.

Update ExMeX Framework Core

To update the ExMeX Framework Core to the newest release, please follow the install notes in the latest release notes!

Housekeeping ExMeX Framework Core Database

When the installation of the release is done, there are some final tasks to do. If everything is installed fine, there will temporary tables remaining in you ExMeX Framework Core database.

These tables are backups of tables which structure was changed during the update process. They can be identifies by their prefix: tmp_<TableName>.

You can clean up your ExMeX Framework Core database by deleting all temporary backup tables.

Posted 10 months agoby Dirk.Lerner

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