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. It was enhanced with the release 5.6.x.x by using the schema.ini definision. 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:
appendExample 2:
delete
⚠️ Attention The methode "append" does not work if configuration file parameter has been set to "DropTable = true".
Example
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 IncludeScenarioResults
Function to load test scenario results in addition to there test object results.
IncludeScenarioResults = "false" (Default)
Parameter LatestResult
Function to define if all results within a directory (scenario, environment) are loaded or the latest result only.
LatestResult = "true" (Default)
Parameter StandardLimitCommit
Function to define iafter how many imported data rows a commit to the database is executed. This parameter enables the user to optimize import performance for his/her database. The standard setting commits staged data after every 100000 data rows. The maximal amount of rows possible to import before a commit to the database are 2147483647 rows.
StandardLimitCommit = 100000 (Default)
Parameter StandardCsvDelimiter
Function to define the delimiter used as default value if it is not defined otherwise within schema.ini file.
LatestReStandardCsvDelimitersult = ";" (Default)
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:
A successful data load process execution is stored as a 'passed' result in OMrun.
Last updated