Data Staging

1. Abstract

The data staging plug-in is introduced with release 4.2.1.0 and supports MS SQL-Server data staging databases only!

Plug-in to import result files "tor" from an OMrun component or CSV flatfiles to a Microsoft SQL-Server staging database. To connect to the staging database, the credentials from the OMrun environment definition are used and transferred via “Data Link” drop down menu to the plug-in.

All test results within the selected component path are loaded into the staging database. During the load process the system generates automatically staging queries.

Staging Types

The plug-in supports two type of staging mechanism:

• append (Standard, appends test result to an existing or new data table in the staging database)

• delete (deletes data table content in the staging database and imports the new results into an existing or new data table) The test results are automatically loaded with the database schema dbo. In case there is a need for another database schema, it can be overwritten by a parameter.

Staging Definition Settings

In the data staging configuration file there are three base definition settings for further adjustments:

• SchemaName (Standard schema name definition)

• DropTable (Truncate / drop data table)

• IncludeStats (Adding statistical results)

After running the data staging process for every test data object within the addressed component there exists a data table on the staging database with the imported records from all selection results.

---

2. Input

Execution parameters to invoke a data staging:

Drop down settings

• Program Call: DataStaging

• Data Link: <DB variable>

Data staging append results

Field @param1 (Component path) [mandatory]

• Description: Component path location, no file name or extension

• Syntax: <full component path>

• Example: "C:\Data\TestScripts\Project\DataPrep"

Field @param2 (Query path) [optional]

• Description: Query path, if defined, which will contain all auto-generated query files for the loaded test results

• Syntax:

  • <empty> = no queries generated

  • <full path for query files>

• Example: "C:\Data\ TestQueries"

Field @param3 (Schema name) [optional]

• Description: Schema name, overwrites the default setting from config file (default = dbo)

• Syntax:

  • <empty> = uses dbo as the database schema name

  • <Schema name>

• Example: "stage"

Field @param4 (Load type) [optional]

• Description: Defines how new test results are loaded into database tables

• Syntax:

  • <empty> = appends result data

  • <append> = appends new results to existing data

• Example: "append"

Attention: ⚠️\ Append does not work if configuration file parameter has been set to "DropTable = true".

Data staging delete + load results

Field @param1 (Component path) [mandatory]

• Description: Component path location, no file name or extension

• Syntax: <full component path>

• Example: "C:\Data\TestScripts\Project\DataPrep"

Field @param2 (Query path) [optional]

• Description: Query path, if defined, which will contain all auto-generated query files for the loaded test results

• Syntax:

  • <empty> = no queries generated

  • <full path for query files>

• Example: "C:\Data\ TestQueries"

Field @param3 (Schema name) [optional]

• Description: Schema name, overwrites the default setting from config file (default = dbo)

• Syntax:

  • <empty> = uses dbo as the database schema name

  • <Schema name>

• Example: "stage"

Field @param4 (Load type) [optional]

• Description: Defines how new test results are loaded into database tables

• Syntax:

  • <delete> = deletes result data before loading new results

• Example: "delete"

---

3. Application Configuration File

The application configuration file contains three additional parameters to adjust the general behavior of the plug-in.

SchemaName (Standard schema name definition)

The settings contains the standard database schema name used in case there is no "@param3" defined.

DropTable (Truncate / drop data table)

DropTable = “false” (default)

• @param4 = delete Truncate the data table before loading new data create a new table if not exists or modify data table structure if necessary.

• @param4 = append Append records to the existing data table, create a new table if not exists or modify data table structure if necessary.

DropTable = “true”.

• @param4 = delete before the result data is loaded, the staging table is dropped and re-created.

• @param4 = append append data with DropTable = true is not allowed

IncludeStats (Adding statistical results)

In most cases the user is not interested in loading statistical data in addition to the loaded test result data. Nevertheless if statistical data is needed as stage tables, IncludeStats needs to be set to “true”. With this setting for every result table the plugin generates a statistical table with the post fix “_Stats”.

---

4. Output

Table Name

The staged result files are generally named as the test object name from OMrun:

• Table name “schema.TestObjectName” if parameter list “Extensive” was selected

• Table name “schema.TestObjectName_ParameterListName” if a different parameter list as “Extensive” was selected.

• Table name “schema.TestObjectName_Stats” or “schema.TestObjectName_ParameterListName_Stats” if application configuration setting was set to “IncludeStats” = true

Table Structure

During the load process the table structure is defined related to the loaded data and is automatically modified if the data format range is not sufficient enough or an additional data column is needed.

An existing data column is never automatically deleted (only exception is: DropTable in application configuration file = “true”).

Data Load Results

During the load process a log file is created that contains any issue detected during the load process:

C:\Users\<UserName>\AppData\Roaming\dbi\OMrun\Log\OMrunPlugIn_DataStaging.Log.txt

A successful data load is stored as a passed result in OMrun.