Skip to main content
Skip table of contents

Master Data Import and Export

Time series master data can be read to or from Excel by pressing the buttons “Export” respectively “Import”. This eases editing of time series master data as well as creating time series.

You can open the Plug-in via the button from the "Administration" ribbon:

It is possible to select "PowerTSM Database" and/or "WebTSM Services" as the data source, depending on the configuration.

Time Series Master Data Export

Clicking the "Export" button will open the Time Series Search window where you can search the time series that are to be exported. For reading time series master data to Excel, select the desired time series in the spread sheet and press “OK” in the “Add selection to application” field.

Http connections can also be specified as the data source in order to be able to export time series from these. 

Time Series Master Data Import

Time series master data can be imported from currently active Excel sheets. Therefore it is required that the format in the Excel sheet to be imported corresponds to the format used for the export.

 

Time Series Master Data – Table Fields

The fields from this table are mandatory for a master data import. When it comes to attributes additional fields have to be used. These are shown and explained in the template, which can be opened by clicking on "Import template with data examples".


Field

Mandatory

Description

Possible ValuesExample

ObjectID

Only if the definition of a existing time series is edited

Unique ID of the specific time series master data set.



Name

yes

Unique name of a time series



Description


Description of the time series



Unit

yes

The unit of the time series



Type

yes

The type of the time data

  • A = time series with left notation
  • E = time series with right notation
  • S = spontaneous time series

Interval

yes

Time resolution

  • S = Second
  • N = Minute
  • H = Hour
  • D = Day
  • W = Week
  • M = Month
  • Q = Quarter
  • Y = Year

Interval length

yes

Length of an interval



Formula


Formula to calculate a time series' data


You can find examples of formulas under  Formula Time Series

Table


The data table for the time series



Archive table
The archive data table for the time series

TimeZone
Name of the time zone. Besides the time zones supported by Microsoft, there are several time zones defined by HAKOM.

For a list of time zones supported by Microsoft, see https://support.microsoft.com/en-us/help/973627/microsoft-time-zone-index-values.

A list of HAKOM specific time zones can be found under Reading Data into Excel

W. Europe Standard Time
StartOfYear
Value by which the interval is shifted (in months).1-12
StartOfDay
Value by which the interval is shifted (in hours from midnight on).0-23
PreserveDuplicatedValues
Defines whether identical consecutive values are stored (TRUE) or not (FALSE) in spontaneous time series when saving.
  • TRUE
  • FALSE

Aggregation

Determines which method is used to aggregate time series data.

  • Sum
    Addition of the values of the points in the specified time range (time periods are not taken into account).
  • Average
    Arithmetic mean of the values of the points in the specified time range (weighted by validity period).
    (Values and their time range with flag "No Value" are not taken into account).
  • Min
    The smallest value in the specified period.
    (No Value has no value and can only be the result of Min if the whole time segment contains only No Value values.)
  • Max
    The highest value in the specified period.
    (No Value has no value and can only be the result of Max if the whole time segment contains only No Value values.)
  • MostFrequently
    The first longest valid value per interval.
  • AtTheMoment
    The value at a time t using the underlying function f(t) of the time series (internal representation of discrete values over time).
  • AbsMin
    The minimum value (corresponds to the Min function) of the absolute values of the time series.
  • AbsMax
    The maximum value (corresponds to the Max function) of the absolute values of the time series.

QuotationDefaultBehavior

Defines the standard behavior when reading quotation time series if no quotation date has been set.

If a value is entered here, it overrules the one set in the HAKOM.Config.

  • 1
    (MaxExact)
    The latest quotation date for the time domain will be determined and only data for this date will be read.
  • 2
    (MaxNotExact )
    Data from the time domain will be read with the respective latest quotation date.
  • 3
    (NowNotExact)
    Data from the time domain wll be read with the respective latest quotation date but earlier than the current time.
  • Null
    If the cell is empty, the value from HAKOM.Config is used.


When time series master data is imported, the TSM differentiates between entries which have already existed in the data base and have just been edited (after an export) or if new entries shall be created in the data base.

Import of New Time Series Master Data

When importing new data, the ObjectID has to stay empty and the Name is not already existent in the data base.

In order to import time series into an http connection data source, the import template only differs in the data source area. In the case of an http connection, the ID of the data source and the repository must be specified in field B2. It is also important to successfully log in to the WebTSM service before importing. This can be done via the "Master Data Import/Export" plug-in ("Sign in" button with correctly selected data source).

Example:
In the HAKOM.Config there is an HttpConnection with the Id "WebTSMService". The WebTSM service configured here has a repository "TSM". To import time series here, the import file must contain "WebTSMService/TSM" in field B2 (next to "Data source").

Re-Import of Time Series Master Data After Editing

After a “Time Series Master Data Export” like described above, the time series categories which have been read to Excel can be edited.

 

Changes can be read from Excel and saved to the data base by clicking the button “Import”.

If data is re-imported from Excel (existing data in the data base is updated) the ObjectID has to stay the same and shall not be edited (For more information see: Time series Master Data).

Time Series Administration

Additional settings can be made for the import of time series master data:

  • Add categories
    If this checkbox is active, attributes of type "category" will be saved to the database, if they have not already existed.

    Note: This will work for attributes of type "category" only! All other types attributes that are to be assigned via master data import must already be saved on the database!

  • Ignore blank attribute entries
    This option is selected by default. It means, that time series, that do not have an entry in the column of an attribute will not be assigned this attribute.
    In the example above "Doc_TS_1" would be assigned "An_attribute", "Doc_TS_2" would not. Both of them would be assigned "A_category".
  • Import also blank attribute entries
    If this option is selected, time series, will also be assigned attributes, when the relevant column is empty.
    In the example above both time series would be assigned "An_attribute", but only "Doc_TS_1" would have a value for it.
  • Delete blank attribute entries
    If this option is selected, the time series will be unassigned their attributes, if the relevant column is empty.
    In the example above this would mean, that "Doc_TS_2" would loose "An_attribute". 

Export and Import of Attributes

Similar to the time series import and export, attributes can be imported to and exported from all data sources using a template via the "Master Data Import/Export" plug-in.

Export

The "Export" button exports all attributes from the data source and repository selected above to an spreadsheet.

Import

The "Import" button imports all attributes from the currently active sheet into the data source specified in the sheet.
An example of the import template can be generated via "Import template with data examples".

Data Source:

The data source into which the attributes are to be imported.

For database connections, only the repository needs to be specified here.

For http connections, the HttpConnection Id and the repository must be specified here in the following format: HttpConnectionId/Repository.

ID:

Optional field. If an attribute is to be renamed, the reference to the attribute to be renamed can be specified via the ID. This field must remain empty for new attributes.

Name:

This field can either be used to specify the name of the new attribute or, if an attribute with this name already exists, it will be updated.

Description:

A description can be assigned to the attribute via "Description".

Type:

The type of attribute is defined here. The following types are supported:

  • Boolean
  • Category
  • DateTime
  • List
  • MSCONSID
  • Node
  • Numeric
  • Text
  • MultilineText
  • TimeSeries
  • TimeSpan
JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.

Contact Support Close