Data Staging

1. Abstract

The data staging plug-in was introduced with release 4.2.1.0 and supports MS SQL-Server databases (OLEDB) for data staging only. Plug-in is able to import data from 2 different data sources to a Microsoft SQL-Server staging database:

• Import of all latest result files (TOR) from an OMrun component: Each latest test result for every object within the selected component path are loaded into the staging database.

• Import of all CSV flatfiles from a directory: All csv flatfiles within the selected directory path are loaded into the 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. During the load process the system generates automatically staging queries (see input field @param2 below).

Staging Types

The plug-in supports two type of staging mechanism:

• append (Default, 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 (see input field @param3 below).

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 directory (with TOR or CSV file content) there exists a data table on the staging database with the imported records from all selected files.

---

2. Input

Execution parameters to invoke a data staging:

Drop down settings

• Program Call: DataStaging

• Data Link: <DB variable>

Parameter List settings

Field @param1 (Component path) [mandatory]

• Description: OMrun component path location (for TOR files) or directory path with CSV flatfiles, no file name or extension

• Syntax: <full 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 after data staging process.

• 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 (same as "append")

  • <append> = appends new results to existing data

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

• Example 1: append

• Example 2: delete

⚠️ Attention The methode "append" does not work if configuration file parameter has been set to "DropTable = true".

Example

OMrun Object: Data Staging

---

3. Configuration File

The application configuration file contains three additional parameters to adjust the general behavior of the plug-in. Path: C:\Program Files\dbi\OMrun\BuiltInTests\OMrunPlugIn_DataStaging.dll.config

Parameter SchemaName

This setting contains the standard database schema name definition used in case there is no "@param3" defined.

Parameter DropTable

Function to truncate data table or to drop data table during load process.

DropTable = “false” (default)

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

• @param4 = append Appends records to the existing data table, creates a new table if not exists or modifyies data table structure if necessary.

DropTable = “true”

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

• @param4 = append (not supported) ⚠️ Function "append" data with config parameter DropTable = "true" is not allowed.

Parameter 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 additional stage tables, IncludeStats needs to be set to “true”. With this setting the plugin generates a statistical table for every result 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 has been detected.

An existing data column is never automatically deleted (the only exception is: parameter DropTable in application configuration file is set to “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 process execution is stored as a 'passed' result in OMrun.