Home Server Server Primers Data Formats and Data Types

Data Formats and Data Types

This article is about the types of data used in P2 Server, the associated terminology, and valid combinations. For details on how to use data in P2 Explorer, see: Adding Data to a Page, Creating a Trend, Using the Page Controls.

Data comes in many formats and types and P2 Server will treat data differently depending on this. 

Data Formats

P2 Server fetches data from external systems and, depending on the adaptor being used, the data is returned in one of two formats: time series or tabular.

Time series

Time series data is a sequence of named data points which typically contains consecutive measurements over a period of time. Therefore, every data point in time series data has an associated timestamp, which records the date and time of the measurement.

In P2 Server, each of these named data points are referred to as tags.

Time series data is used in pages and calculations, but is also often represented in a trend, which makes it easier to find and identify patterns over time for analysis and forecasting.

Time series data is obtained from sources such as data historians, and requires a tag datasource.

Related: Creating a Tag Datasource, Time Series Tag Fetch

Tabular

Tabular data contains data points that can be represented using rows and columns. This data is often represented in charts such as bar charts, or as a pivot table on a page.

Here is an example of some data that could be returned in tabular form:

Name Age Height Weight Colour
Jerry 23 165 61 Green
Alex 36 172 69 Blue
Sam 42 184 75 Grey

Tabular data is obtained from Oracle or SQL Server, and requires a dataset datasource.

Important Note: If your tabular data has timestamp information, you have another option. We can convert it into time series data by using the Relational Adaptor. This allows you to convert the data that you have in an Oracle or SQL Server database into a time series data stream, as you would get if you were connected to a PI or PHD historian.

Why are data formats important?

When you are defining an attribute, one of the things you need to define is the format of the data returned by an attribute. These can be one of these two types, either time series or tabular.

P2 Server uses this information in a number of different areas, to work out what operations it can do on the data being returned.

Related: Attributes, Creating a Dataset Datasource

 

Data Types

In P2 Server, the items used to configure the system and the data returned from the system, all have a specific data type that is used. We use a specific data type, rather than just treating everything as a string, so that we can be smarter with the data.

Datasource Parameters

The first place where you will need to be conscious of data types is when you are configuring a datasource. When configuring a datasource you will need to give it a number of parameters in order to function. Each adaptor will determine what parameters need to be configured and what data types are needed.

Related: Datasources and Adaptors

When configuring these items in P2 Server Management, the system will detect what type is being used and will automatically use the correct type of control, making the selection simple. When using the Import/Export spreadsheet, you will need to manually specify them.

These are some of the different data types that you will encounter:

  • Boolean – has two values denoting true or false.
  • DateTime – a structure that represents the date and time in UTC.
  • Decimal – a number written in decimal notation.
  • Duration – a period of time.
  • Integer – a whole number that can be positive, negative, or zero.
  • String – a sequence of characters.
  • EncryptedString – a sequence of characters that has been encrypted.

Dataset Keywords

When creating dataset queries, P2 Server provides keywords that allow you to pass parameters to a dataset query:

  • ENTITYNAME - used to add a column to the result set which contains the names of returned entities.
  • FIRSTROW - used to pass the index of the first row to return when paging is applied to a query.
  • LASTROW - used to pass the index of the last row to return when paging is applied to a query.
  • SORTBY - used to pass an order-by clause into a query.
  • PARAM - used to pass a single parameter into a query.
  • PARAMS - used to pass a collection of parameters into a query.
  • USERPARAM - used to pass a single user parameter into a query.
  • USERPARAMS - used to pass a collection of user parameters into a query.
  • USERPARAMSORTBY - used to pass an order-by clause into a query from a user parameter.

The keyword

  • PARAMSTABLE - is used to make each value supplied a row in a table and takes the arguments

    PARAMSTABLE( parameterName, parameterType, tableName, columnName )

    The first two arguments work in exactly the same way as they do in PARAM[S], while the last two define how the rest of the query will reference the table-valued data (e.g. in an INNER JOIN).

Keywords containing PARAM are used in conjunction with their data type, so that P2 Server can replace this with the relevant database syntax (e.g. SQL Server syntax). The data types that are available for use in queries are:

  • Boolean – has two values denoting true or false.
  • DateTime – a structure that represents the date and time in UTC.
  • Decimal – a number written in decimal notation.
  • Double - a number written in decimal notation.
  • Duration – a period of time.
  • Integer – a whole number that can be positive, negative, or zero.
  • String – a sequence of characters.
  • ZonedDateTime - a structure that represents the date and time in UTC.
  • Entity - a string that corresponds to an entity name mapped in the source system. If no match is found, the value is used as-is.
  • MaskedString – a sequence of characters that has been encrypted.

Note: The definitions above may be different, depending on the source system that the data is coming from. Refer to the source system’s documentation if you are unsure.

Within the query, parameters are used as variables within the ‘Where’ clause, thus affecting the query results.

Here is a screenshot from P2 Server Management, showing the PARAM keyword being used in a dataset query. Default values for the parameters are specified below the query.

Query with default dataset parameters in P2 Server Management.

 

Attribute Values

The second place that you will need to consider these data types is when defining attribute values. 

Related: Creating Templates and Attributes, Configuring Entities

Attribute values can store one of three types of information.

  1. Fixed Value – You have set the attribute value to be a specific value, for a period of time.
  2. Tag – You have set the attribute value to reference a specific time series tag.
  3. Dataset – You have set the attribute value to reference a specific dataset.

Items 1 and 2 in this list are in time series format, whereas item 3 is in tabular format.

This is important as the data type used must be compatible with the data format of the attribute definition.

For example, a dataset cannot be assigned to an attribute marked as tag; and fixed values and tags cannot be assigned to an attribute marked as dataset.

If the attribute has been defined with a Data Format of time series, the attribute value can be:

  • Boolean – a fixed value denoting true or false.
  • DateTime – a fixed value that represents the date and time in UTC.
  • Decimal – a fixed value consisting of a number written in decimal notation.
  • Duration – a fixed value denoting a period of time.
  • Integer – a fixed value consisting of a whole number.
  • String – a fixed value consisting of a sequence of characters.
  • Tag – a value from a time series datasource.

If the attribute has been defined with a data format of tabular, the attribute value MUST be dataset.

Because attributes are time-aware, they can have different types of values at different periods of time, as long as you are not mixing time series and tabular.

For example, a time series attribute designed to show the status of a pump can have an integer fixed value of 1 for a period of time, and then the value type can change to tag when a calculation is created to more accurately determine the status.  In this case, both of the attribute values belong to the time series data format, so this is allowed.

attributevalues

Attribute Values in P2 Server Management

Calculations

You will also encounter various data types when you are using calculations. Each calculation function returns data of a specific type, and accepts certain data types as inputs. 

Related: Calculation Overview and Syntax

These are some of the different data types that you will encounter:

  • Boolean – has two values denoting true or false.
  • DateTime – a structure that represents the date and time in UTC.
  • Decimal – a number written in decimal notation.
  • Duration – a period of time.
  • Error – passed to the function by the data itself.
  • Integer – a whole number that can be positive, negative, or zero.
  • Null – no value.
  • String – a sequence of characters.

Comments are closed