Toolbox

In this chapter tools for the ExMeX Framework Core are described. This includes but not only

  • Update HashDiff of Satellite
  • LPS Side Injection
  • And more are upcoming on the horizon

Tools for the ExMeX Framework Core are available for download in the download area.

All tools are ready for use, some are in a research, experimental state!

todd quackenbush unsplash

Photo by Todd Quackenbush on Unsplash

LPS Side Injection

The ExMeX Tool - LPS Side Injection is a SQL Script for injecting a Load Process Step (LPS) into the ExMeX Framework Core.

This injected LPS is used to allow external applications to use the ExMeX Logging Framework.

The injected LPS allows external applications to collect data logistic statistics not only for batches but also for the atomic LPSs contained in them.

Main features are

  • Inject a LPS
  • Add injected LPS to a batch

How to inject an LPS?

At least Version 2.11.0 of ExMeX Framework Core has to be installed. 1. Extract 7z-file to /library/tool in your root directory.…

At least Version 2.11.0 of ExMeX Framework Core has to be installed.

1. Extract 7z-file to /library/tool in your root directory.

2. A new folder will be added (if it not already exists): LPS Side Injection.

3. Copy or rename LPS Side Injection/LPSSideInjection.orig.sql  to LPS Side Injection/LPSSideInjection.sql  and open script LPS Side Injection/LPSSideInjection.sql in SSMS.

4. Change the following lines (73 and following) to add a LPS Side Injection

You can adapt the script as often as you like, as an LPS side injection only has to take place once. Reinstalling the metadata does not change the injected LPS and its assignment to a batch. Of course, a script can also be created for each LPS side injection.

Specify only the batch to which the LPS Side Injection is to be assigned. Not a related parent, master or child batch.

--#-----------------------------------------------------------------------------
--# Edit Insert below
--# Recommendation: DO NOT INSERT more than 1 (one) row
--#-----------------------------------------------------------------------------

INSERT INTO @LPSSideInjection
    -- Source
    VALUES (
            -- Model, add a fictitious model (ModelCode, ModelName)
            -- Do NOT change the fictitious model!
            'LPSSideInjection','LPS Side Injection'
            -- Add a fictitious Source Entity ('<YourFictitiousSourceEntityCode>','<YourFictitiousSourceEntityName>','<YourFictitiousSourceDatabase>','<YourFictitiousSourceSchema>')
            ,'<YourFictitiousSourceEntityCode>','<YourFictitiousSourceEntityName>','<YourFictitiousSourceDatabase>','<YourFictitiousSourceSchema>'
            -- Add a fictitious Target Entity ('<YourFictitiousTargetEntityCode>','<YourFictitiousTargetEntityName>','<YourFictitiousTargetDatabase>','<YourFictitiousTargetSchema>')
            ,'<YourFictitiousTargetEntityCode>','<YourFictitiousTargetEntityName>','<YourFictitiousTargetDatabase>','<YourFictitiousTargetSchema>'
            -- Batch to which LPS is associated, must exist in table MetadataZone.LoadBatch ('<YourBatchLabel>')
            ,'<YourBatchLabel>'
            )
;

--##############################################################################
--#-----------------------------------------------------------------------------
--# DO NOT EDIT BELOW
--#-----------------------------------------------------------------------------

5. Execute the script.

6. Done. A new LPS is injected and added to a batch.

7. Now the stored procedures uspProcessStepVapourTrail and uspBatchVapourTrail can be used to write to the ExMeX Logging Framework. See also section Framework In Depth -> Stored Procedure.

It is not necessary to install the metadata again. An LPS Side Injection is independent of the ExMeX Framework Core metadata. It is only necessary to use the ExMeX Logging Framework.

Posted 1 year agoby Dirk.Lerner

Set Batch Setting

The ExMeX Tool - Batch Setting is a Excel Sheet and a SQL Script for configure batch settings of a batch in the ExMeX Framework Core.

The batch setting feature is mainly used by the ExMeX Companion App (valid as of App version 0.4.0). However, it can also be used independently

Main features are setting the variables

  • IsActive
  • ExitOnError
  • MaxSqlCommandInChunk

How to configure Batch settings without the Companion App?

At least Version 2.13.0 of ExMeX Framework Core has to be installed. Please download first the current version of the…

At least Version 2.13.0 of ExMeX Framework Core has to be installed.

Please download first the current version of the Batch Setting Tool for the ExMeX Framework Core.

Backup ExMeX Framework Core

Before you update the batch settings in ExMeX Framework Core with the Set Batch Settings tool, you should create a backup of your ExMeX Framework Core database.

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

Installation

After downloading the latest version of the Set Batch Setting tool, please follow all the steps below to get the most out of the tool.

The installation of the Set Batch Settings tool has to be done only once!

  • Extract 7z-file to ./library/tool in your root directory.
  • A new folder will be added (if it not already exists): Set Batch Setting.
  • Copy or rename “./library/tool/Set Batch Setting/SetBatchSetting.orig.sql” file to “./library/tool/Set Batch Setting/SetBatchSetting.sql”.
  • Copy or rename “./library/tool/Set Batch Setting/Set Batch Setting.orig.xlsx” file to “./library/tool/Set Batch Setting/Set Batch Setting.xlsx”.

Configure batch settings

You can adapt and enhance the script as often as you like, as a Set Batch Setting only has to take place once. Reinstalling the metadata does not change the new configured and set values to a batch.

  • Open file “./library/tool/Set Batch Setting/Set Batch Setting.xlsx” to configure the batch settings as needed. Only add batch settings if you want to change anything. If not then skip the following steps.
  • Check the currently set values in the Metadata database:
    SELECT BatchLabel
          ,IsActive
          ,ExitOnError
          ,MaxSqlCommandInChunk
    FROM MetadataZoneCore.LoadBatchNowNow
    	
  • Add new rows to Excel “./library/tool/Set Batch Setting/Set Batch Setting.xlsx” with the batches where you want to change the installations.
  • Then copy the new content from the DML column into the script “./library/tool/Set Batch Setting/SetBatchSetting.sql” starting from line 50.
  • Open the “./library/tool/Set Batch Setting/SetBatchSetting.sql” file and run it in an SSMS. To be on the safe side, check the Messages tab for error messages!
  • Done. The new settings are applied to the batch(es). Check the new set values in the Metadata database:
    SELECT BatchLabel
          ,IsActive
          ,ExitOnError
          ,MaxSqlCommandInChunk
    FROM MetadataZoneCore.LoadBatchNowNow
    	

It is not necessary to install the metadata again. A Set Batch Setting is independent of the ExMeX Framework Core metadata.

Deployment

In case the settings for the "Set Batch Setting" should be deployed to other environments, such as development, test or production, please refer to the section Framework Core in Depth -> Extensions By User.

Posted 10 months agoby Dirk.Lerner

Metadata Install (Batch File)

The ExMeX Tool - Metadata Install is a batch-file to install the ExMeX Metadata into the ExMeX Framework Core database. It can be used as an alternative to the 'Metadata Delete And Insert CMD.sql' script, which is executable only in SQL Server Management Studio (SSMS), and run in the command line (cmd.exe).

Main features are

  • Section as argument - Choose section for installing ExMeX Metadata
  • ExMeX Framework Core database as argument - Choose to which ExMeX Framework Core database Metadata will be installed
  • ServerInstance as argument - Choose your Server and Instance Name
  • Port as argument - Choose your Port
  • Error detection and report
  • Exit on in case of an error

How to use ExMeX Tool Metadata Install (2.x.x)?

Valid as of ExMeX Framework Core release 2.13.0 Valid as of ExMeX Tool Metadata Install release 2.2.0 Please download first the…

Valid as of ExMeX Framework Core release 2.13.0

Valid as of ExMeX Tool Metadata Install release 2.2.0

Please download first the current version of the Metadata Install 2.x.x Tool for the ExMeX Framework Core.

The ExMeX Metadata Install tool is intended to make it easier for data modelers or DevOps to develop and test error-free metadata installation and compilation of LPS.

Backup ExMeX Framework Core

Before you install metadata into the ExMeX Framework Core database with the Metadata Install tool, you should create a backup of your ExMeX Framework Core database.

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

Configuration

After downloading the latest version of the ExMeX Metadata Install tool, please follow all the steps below to get the most out of the tool.

  • Extract 7z-file to ./library/tool in your root directory.
  • A new folder will be added (if it not already exists): Metadata Install.
  • Copy and rename ‘./library/tool/Metadata Install/Metadata Install.2.x.x.orig.bat’ file to ./Metadata Install.bat’ (to your root directory).
  • Open file ‘./Metadata Install.bat’ to configure the default settings as needed. All settings can also be set with arguments, except login and password as shown below.
  • Save and close file.
:: :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: Set variables with predefined values, override by arguements

 :: ExMeX framework settings
 :: get global ExMeX variables
 set ExMeXGlobalEnv=library\env\GlobalVariable.sql
 :: root directory, arguement --exmex-root-path
 set ExMeXRootPath=<YourRootDirectory> example: C:\GitRepos\ExMeX-Framework-Core
 :: metadata directory, arguement --exmex-metadata-path
 set ExMeXMetadataPath=<YourMetadataDirectory> example: C:\GitRepos\ExMeX-Framework-Core\var
 :: target environment file, arguement --target-env-file
 set ExMeXTargetEnv=<YourEnvFile> example: SetEnvDevelopment.sq
 :: config directory, arguement --exmex-config-path
set ExMeXConfigPath=<YourConfigDirectory>
:: config file, arguement --exmex-config-file
set ExMeXConfigFile=<YourConfigFile> :: database config / setting to connect to metadata database :: metadata database, arguement --metadata-database set Database=<YourMetadaDatabase> example: TEDAMOH-ExMeX-Release-Feature :: server and instance, arguement --server set ServerInstance=<YourServer\YourInstance> example: 10.8.0.1 :: port of server, arguement --port set ServerPort=<YourServerPort> :: credentials only needed, if NO "Windows Authentication" is used :: login name set DatabaseUser= :: login password set DatabasePassword=

Example with non-default server instance and port

:: :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: Set variables with predefined values, override by arguements

:: ExMeX framework settings
:: get global ExMeX variables
 set ExMeXGlobalEnv=library\env\GlobalVariable.sql
 :: root directory, arguement --exmex-root-path
 set ExMeXRootPath=C:\GitRepos\Tutorial ExMeX Framework Core
 :: metadata directory, arguement --exmex-metadata-path
 set ExMeXMetadataPath=C:\GitRepos\Tutorial ExMeX Framework Core\var
 :: target environment file, arguement --target-env-file
 set ExMeXTargetEnv=SetEnvDevelopment.sql
:: config directory, arguement --exmex-config-path set ExMeXConfigPath=C:\GitRepos\Tutorial ExMeX Framework Core\\etc\config :: config file, arguement --exmex-config-file set ExMeXConfigFile=MetadataDataModels.config :: database config / setting to connect to metadata databas :: metadata database, arguement --metadata-database set Database=Tutorial-ExMeX-Metadata :: server and instance, arguement --server set ServerInstance=10.8.0.1\TutorialInstance :: port of server, arguement --port set ServerPort=1431 :: credentials only needed, if NO "Windows Authentication" is used :: login name set DatabaseUser= :: login password set DatabasePassword=

Sections

If you have a large amount of data models to install, we recommend to split the data models in different sections for a better handling during installation.

  • Copy and rename ‘./library/tool/Metadata Install/MetadataDataModels.orig.config’ file to ./etc/config/MetadataDataModels.config’.
  • Open file ‘./etc/config/MetadataDataModels.config’ to configure the sections as needed.
  • Save and close file.

Each section name must be in square bracket, e.g. [All].

The metadata files (also called data models) must be in the correct order. First must be the metadata file that has no dependencies to other metadata files (e.g. Tutorial-SourceSystem-A). The next file must be the metadata file that has dependencies to the previous file due to Data Object/Item Mappings (Tutorial-DWH-StageLayer has mappings to Tutorial-SourceSystem-A). And so on.

Any amount of sections can be created!

#
# section, which installs all data models
# 
[All]
Tutorial-SourceSystem-A
Tutorial-DWH-StageLayer
Tutorial-DWH-CoreLayer

#
# Generated with ExMeX Plugin Metadata Export 
# -> Excel
#
[ExMeXPluginExcel]
TestdataStage
TestdataCore

The configuration of the ExMeX Tool Metadata Install is now complete. In the future, changes only need to be made as required and the previous steps repeated if necessary.

Now the ExMeX Tool Metadata Install can be used as described below:

  • Open a Command-line Window (cmd.exe)
  • Change to your root directoty ‘./’
  • Execute MetadatInstall.bat with

 

Deployment

The ExMeX Metadata Install tool in version 2.x.x is suitable for automatic deployment (CI/CD).

Syntax

You can use argument --help to get an overview of all available arguments:

Usage MetadataInstall: [--version] [--help [-w|--web]]
                       [--target-env-file=<name>]
                       [--exmex-root-path=<path>] [--exmex-metadata-path=<path>]
                       [--exmex-config-path=<path>] [--exmex-config-file=<name>]
                       [--metadata-database=<name>] [--server=<server[\instance]>]
                       [--port=<number>] [--data-model=<name>]
                       [--no-metadata] [--no-compile-lps] [--get-settings]
                       <section>

  Remember, if the path contains spaces, then enclose the path in quotes <"path">!

  See 'MetadataInstall.bat help <concept>'
  to read about a specific concept.

Arguments

[--version] Displays the current version of MetadataInstall.bat
[--help [-w|--web]] Displays usage and an overview of all arguments. Optionally, the argument -w or --web can be passed. Then the manual on tedamoh.com will be opened with detailed instructions.
[--target-env-file=<name>] If set, a different environment file with <name> will be applied
[--exmex-root-path=<path>] If set, ExMeX root path with <path> will be used. Remember, if the path contains spaces, then enclose the path in quotes "path"!
[--exmex-config-path=<path>] If set, ExMeX config path with <path> will be used. Remember, if the path contains spaces, then enclose the path in quotes "path"!
[--exmex-config-file=<name>] If set, a different config file with <name> will be applied
[--exmex-metadata-path=<path>] If set, metadata path with <path> will be used to install metadata from. Remember, if the path contains spaces, then enclose the path in quotes "path"!
[--metadata-database=<name>] If set, metadata database with <name> will be used to install metadata to
[--server=<server[\instance]>] If set, server and/or instance with <server[\instance]> will be used to connect to server
[--port=<number>] If set, port with <number> will be used to connect to server
[--data-model=<name>] If set, metadata data model with <name> will be deleted and installed. Be careful with dependencies like mappings or replica-mappings! The data model must be part of the <section>
[--no-metadata] If used, no metadata will be deleted or installed
[--no-compile-lps] If used, no LPS will be compiled
[--get-settings] If used, current settings in MetadataInstall.bat or applied arguments are displayed
<section> The section to be installed of the applied config-file

Example A: Only with section ‘All’ - default settings are used

MetadataInstall.bat All

Example B: Section ‘All’ with arguments for a different server\instance, port and database

MetadataInstall.bat --metadata-database=Tutorial-ExMeX-Metadata --server10.8.0.1\MSSQLSERVER --port=1401 All

Example C: Section ‘All’ with argument for no LPS compiled

In this example only metadata is installed but no LPSs are compiled.

MetadataInstall.bat --no-compile-lps All

Example D: Section ‘All’ with argument for no metadata installation

In this example no metadata is installed but LPSs are compiled.

MetadataInstall.bat --no-metadata All

Example E: Section ‘All’ with different target environment file

In this example a different file for the target environment is used.

MetadataInstall.bat --target-env-file=SetEnvTest.sql All

 

Posted 4 months agoby Dirk.Lerner

How to use ExMeX Tool Metadata Install (1.x.x)?

At least Version 2.13.0 of ExMeX Framework Core has to be installed. Please download first the current version of the…

At least Version 2.13.0 of ExMeX Framework Core has to be installed.

Please download first the current version of the Metadata Install Tool for the ExMeX Framework Core.

The ExMeX Metadata Install tool is intended to make it easier for data modelers or DevOps to develop and test error-free metadata installation and compilation of LPS.

Backup ExMeX Framework Core

Before you install metadata into the ExMeX Framework Core database with the Metadata Install tool, you should create a backup of your ExMeX Framework Core database.

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

Configuration

After downloading the latest version of the ExMeX Metadata Install tool, please follow all the steps below to get the most out of the tool.

  • Extract 7z-file to ./library/tool in your root directory.
  • A new folder will be added (if it not already exists): Metadata Install.
  • Copy and rename ‘./library/tool/Metadata Install/Metadata Install.orig.bat’ file to ./Metadata Install.bat’ (to your root directory).
  • Open file ‘./Metadata Install.bat’ to configure the default settings as needed:

    From
     rem root directory
     set ExMeXRootPath=<YourRootDirectory>\
     rem metadata directory
     set ExMeXMetadataPath=<YourMetadataDirectory>\
     […]
     rem Database config - Database setting, used when param %2 is empty
     set Database=<YourMetadaDatabase>
     rem Database config - Server and Instance settings
     set ServerInstance=<YourServer\YourInstance>
     rem Database config - Server Port setting
     set ServerPort=<YourServerPort>
     rem Credentials only needed, if NO "Windows Authentication" is used
     set DatabaseUser=
     set DatabasePassword=
    To (with NO Default Instance and Port)
     rem root directory
     set ExMeXRootPath=C:\GitRepos\Tutorial ExMeX Framework Core\
     rem metadata directory
     set ExMeXMetadataPath=C:\GitRepos\Tutorial ExMeX Framework Core\var\
     […]
     rem Database config - Database setting, used when param %2 is empty
     set Database=Tutorial-ExMeX-Metadata
     rem Database config - Server, Instance and Port settings
     set ServerInstance=10.8.0.1
     rem Database config - Server Port setting
     set ServerPort=                  
     rem Credentials only needed, if NO "Windows Authentication" is used
     set DatabaseUser=
     set DatabasePassword=
    To (with Default Instance and Port)
     rem root directory
     set ExMeXRootPath=C:\GitRepos\Tutorial ExMeX Framework Core\
     rem metadata directory
     set ExMeXMetadataPath=C:\GitRepos\Tutorial ExMeX Framework Core\var\
     […]
     rem Database config - Database setting, used when param %2 is empty
     set Database=Tutorial-ExMeX-Metadata
     rem Database config - Server, Instance and Port settings
     set ServerInstance=10.8.0.1\TutorialInstance
     rem Database config - Server Port setting
     set ServerPort=1431
     rem Credentials only needed, if NO "Windows Authentication" is used
     set DatabaseUser=
     set DatabasePassword=
  • Save and close file.
  • Copy and rename ‘./library/tool/Metadata Install/MetadataDataModels.orig.config’ file to ./etc/config/MetadataDataModels.config’.
  • Open file ‘./etc/config/MetadataDataModels.config’ to configure the sections as needed.

    Each section name must be in square bracket, e.g. [All].

    The metadata files (also called data models) must be in the correct order. First must be the metadata file that has no dependencies to other metadata files (e.g. Tutorial-SourceSystem-A). The next file must be the metadata file that has dependencies to the previous file due to Data Object/Item Mappings (Tutorial-DWH-StageLayer has mappings to Tutorial-SourceSystem-A). And so on.

    Any desired number of sections can be created!

    #
    # section, which installs all data models
    # 
    [All]
    Tutorial-SourceSystem-A
    Tutorial-DWH-StageLayer
    Tutorial-DWH-CoreLayer
    
    #
    # Generated with ExMeX Plugin Metadata Export 
    # -> Excel
    #
    [ExMeXPluginExcel]
    TestdataStage
    TestdataCore
    	
  • Save and close file.

The configuration of the ExMeX Tool Metadata Install is now complete. In the future, changes only need to be made as required and the previous steps repeated if necessary.

Now the ExMeX Tool Metadata Install can be used as described below:

  • Open a Command-line Window (cmd.exe).
  • Change to your root directoty ‘./’.
  • Execute MetadatInstall.bat with

    Syntax
    MetadataInstall.bat <section> [<YourMetadataDatabase> [<YourServer\YourInstance> [<YourServerPort>]]]
    Example A (Only with section ‘All’ on default Server and Database)
    MetadataInstall.bat All 
    Example B (Section ‘All’ on a different Database, Server\Instance and Port)
    MetadataInstall.bat All Tutorial-ExMeX-Metadata 10.8.0.1\MSSQLSERVER 1401
  • Done. The metadata is installed in the ExMeX Framework Core database.

ExMeX Tool Metadata InstallExMeX Tool Metadata Install - Installation of section [All] with some resulting Warnings

For more information you can check the log-file ‘./tmp/output.log’

Deployment

The ExMeX Metadata Install tool is not suitable for automatic deployment.

Posted 10 months agoby Dirk.Lerner