Data Validation Server

The Data Validation Server is an extension to the HAKOM Batch PlugIn to the TSM. It allows for automated processing of a large number of time series. This is often used in validation and replacing missing data. Large exports profit from this as well, as do large processes combining multiple prognoses. Combining this tool with the capabilities of the HAKOM Time Series Manager including formula time series and prepared templates, opens up a near infinite number of possibilities on how to deal with large amounts of time series data.

The Data Validation Server allows to either write the results back to the data base or export it to a file and includes the ability to log processes to the database for traceability.

The Batch PlugIn has been extended to take certain configuration information from a template workbook, located under its "BatchParameter" sheet, and use these for the automated process. The parameters, so-called "Defined Names", are taken from the sheet and used in each step of the automation.

Configuration sheet

Within a workbook that is supposed to be processed by the Data Validation Server, a sheet named "BatchParameter", which contains the parameters to process the workbook, must be located.

The first row may be used for free-form text descriptions of the process.

The second row represents a header that holds all of the available "Defined Names" and should not be altered.

From the third row on, you may set all these "Defined Name" parameters for each step.

Example:

Available Parameters (Defined Names)

Defined Name	Description	Datatype	Example
ActivateBatch	(True or False), Controls whether this line should be processed or not	Boolean	True
ActivateRead	Whether the time series should be read from the data base or not when refreshing	Boolean	False
MissingEmpty	Whether missing values should be read as empty	Boolean	True
ActivateWrite	Whether writing to the data base should be activated	Boolean	True
ActivateSaveFile	Whether the file should be saved afterwards	Boolean	False
SavePath	Path of the file to be saved	String	C:\Output
ActivateSaveFileOnError	Whether the file should be saved to an alternate path only if an error occurred	Boolean	True
SavePathOnError	The alternate path erroneous files should be saved to	String	C:\Failed
SaveFileFormat	The format in which the file should be saved ("Excel" or "csv")	String	Excel
CSVSheets	When exporting to csv, controls which sheets to be saved.	String	Sheet-1
csvDecimalPoint	Which decimal delimiter to use in numbering (comma, point)	String	,
csvDelimiter	Which character to use to separate columns	String	;
saveFilename	Specifies the format of the name for the generated file.	String	<Filename>_<Date,ddMMyyyy_HHmmss>
saveTimestampAfter (obsolete)	Whether the time stamp should be appended or prepended to the file name. This parameter is only included for backward compatibility. It is only used if saveFilename has one of the following formats: ddMMyyyyHHmmss ddMMyyyy_HHmmss yyyyMMdd_HHmmss yyyyMMddHHmm	Boolean	True
Sheets	Name of the sheet within the workbook to use for this process	String	Sheet-1
LogErrorMessages	Determines which cells of the sheet to be processed contain an error message. Log entries of type "Error" are generated for existing entries in the corresponding cells.	MS Excel Range Object (https://learn.microsoft.com/en-us/office/vba/api/excel.range(object))	"Plausi!2:A3"
LogInformationMessages	Determines which cells of the sheet to be processed contain an information message. Log entries of type "Information" are generated for existing entries in the corresponding cells.	MS Excel Range Object (https://learn.microsoft.com/en-us/office/vba/api/excel.range(object))	"Plausi!B2:B3"
AreasToDelete	Defines the areas within the processed sheet that should be deleted afterwards (Comma separated list of Excel ranges).	String	"Validation-1!I10:I10","Validation-1!F10:G10"
SheetsToDelete	Defines the sheets within the workbook to delete afterwards, separated by a comma	String	Sheet-1,Sheet-3

Application

To run the Data Validation Server, call the executable as follows

CODE

C:\> HAKOM.TSM.Console.exe -p <templatePath>

or use

CODE

C:\> HAKOM.TSM.Console.exe --help

to display the help screen:

Running the application performs a single "Batch" operation for each row in the BatchParameter sheet, with the given values for the "Defined Names"

By specifying a value for the "LogErrorMessages" Defined Name, Errors will be written to that range and also to the protocol table in the database. Every message is treated as an error and its call-ID will be returned. For a call without errors "0" will be returned.

In addition, "from" and "to" values can be set in the command line to override the range in the sheets.

Using the parameter -d (or --definednames) Defined Names from "BatchParameter" will be overwritten. The value may be any valid Excel cell value (cell reference, formula, etc). Multiple Defined Names can be set seperated by semicolon. These Defined Names will be saved by Excel's Name Manager.

Test your workbook

A workbook, that has been created to be processed by the Data Validation Server can be tested by using the Batch PlugIn. Open the Batch PlugIn from the relevant workbook and hit "Update" to make sure the correct workbook will be processed. The click on "Start":

You will be asked to specify the line from the sheet "BatchParameter", that you want to test (This specification is relative to line 3, since line 3 is the first one with parameters. So if you want to run the test with the parameters from line 3, you have to write "1" into the confirmation window):

Examples

Plausibility check

Let's say you use the Data Validation Server to write time series' data to a database. On of these time series contains data to energy consumption of a machine/device, but you know that sometimes the data on this time series is not plausible and therefore want to have a simple plausibility check.

Here you have a very simple example for a plausibility check using the Data Validation Server:

DataValidationServer_Template_Plausi.xlsx

You have a time series that contains energy consumption for a certain machine/device, called "ActValues", and another one that contains maximum values of energy consumption for this machine/device, called "MaxValues".

If you look at the sheet "Values", you will see that column E contains a formula that checks, whether ActValues' values are higher than those of MaxValues. If so, the word "ERROR" is written in the corresponding cell. This is important, because in "BatchParameters" under "LogErrorMessages" we have stated, that column E should be checked for messages!

Now go to "Save these values_1" and click on the Save-Ribbon. In this sheet the values of ActValues are always lower than those of MaxValues.

Open the Batch PlugIn under "Automation" and click on the Update-button (Just in case another template was executed before). When you click on "Start" a small dialogue will appear. You can leave it empty and just hit "OK". The Data Validation Server will now process the sheet "Values" with the settings from "BatchParameter". After closing the Task progress window the generated excel file will automatically be opened. As you can see, there are no errors in the "Values" sheet.

Now go to "Save these values_2" and click on the "Save"-Ribbon. In this sheet the values of ActValues are in some cases higher than those of MaxValues.
Open the Batch PlugIn under "Automation" and click on "Start" and "OK". Now the task progress window reports an error. Close it and the resulting Excel workbook will again be opened automatically. In the "Values" sheet you will now find several "Error" messages in column E.

The two files were generated under "C:\Program Files (x86)\HAKOM" as set in "BatchParameter"

Combination forecast

DataValidationServer_Template_Combi.xlsx

To get started, you have to import the sheet "Import". Then save the sheet "Data". You will see, that there are six time series. Always two with the name "TimeSeriesIn", "TimeSeriesReturn" and "WeightingFactor". Switch to the sheet called "Combination". There are only three time series: "TimeSeriesIn", "TimeSeriesReturn" and "WeightingFactor". These time series names are only placeholders. They do not exist in the data base and will be replaced when the Batch is processed.

Switch to the sheet "BatchParameter" and towards the end of the columns, starting with column P, you will find these placeholders as Defined Names. Below them are the names of the time series, which you have imported before. This means, that at the first run of the Batch the name of the placeholder will be replaced with the name from row three and at the second run it will be replaced with the name from row four.

This example is rather simple. In the sheet "Combination" you will find, that the Data Validation Server will read the time series represented by TimeSeriesIn to column C and shift by one hour it in column E, when processing the Batches. In column D it will read the values of the time series represented by WeightingFactor and in column F the product of WeightingFactor and the shifted TimeSeriesIn will be calculated and written to TimeSeriesReturn.

Open the Batch plug-in and click on "Start" and then "OK". This will process both rows from BatchParameter.

The resulting Files will be opened automatically. There you will see, that the placeholders will have been replaced by the corresponding time series and read with the correct values. Also the results for TimeSeriesReturn will have been calculated and saved to the data base.

Important Notice

If during automated processing, problems occur which cannot be reproduced using TSM, it's suggested the template is run again using TSM+ since there are some minute differences in how formulas are treated between Excel and TSM+ (the Data Validation Server internally uses TSM+ for automation)

Limitations

KISS-A Transformation

It is not possible to perform a KISS-A transformation with the Data Validation Server. This is because there is currently no known reasonable use case for executing a KISS-A transformation with the Data Validation Server. Additionally there are incompatible requirements for the data contained in the workbook depending on whether a KISS-A transformation or a standard batch process is to be performed.