Tuesday, 16 August 2016

Connecting SAP HANA Views to Sensor Data from Osisoft PI

Organizations across different industries leverage Osisoft PI systems in order to collect operational data (e.g. temperature, pressure, flow…) from sensors. These sensor data can be used to get a real-time view of the operational performance of assets, monitor the quality of products, or identify machine failures; just to highlight a few examples. However, sensor data from an Osisoft PI system can even be consumed in SAP HANA Views and thus enable new insights for business users.
In this blog, I give an overview on how these sensor data stored in an Osisoft PI system can be accessed from SAP HANA Views in real time via the SAP Manufacturing Integration and SAP Plant Connectivity solutions, without having to persist the data in the SAP HANA database. I focus on accessing the PI Data Archive and PI Asset Framework (PI AF) of an Osisoft PI system.

Background


The PI Data Archive is the component of an Osisoft PI system that provides an efficient storage and methods for archiving time series data. The data is assigned to so-called PI tags. Two types of PI tags can be differentiated: Analog- and Digital- PI tags. An Analog-PI tag represents numeric values of time series data, e.g. the temperature values of a sensor. Whereas, a Digital-PI tag represents values as texts; e.g. the status of a component could be ‘Stopped’, ‘Running’… Both types of PI tags can be retrieved in SAP HANA. Despite this, different functions are supported in order to retrieve data of PI tags. Examples of the supported data retrieval functions include Plotvalues, Interpolation, and Aggregates. I will not go into further details with respect to these different functions and their purpose, since this is a topic for another blog.
The PI Asset Framework (PI AF) is a repository that organizes data in hierarchies and groups elements according to their relationships. Elements are asset-centric objects; they can expose a number of attributes like PI data points, or calculations, just to name some examples. These PI AF attributes can be retrieved as well in SAP HANA via the method explained in this blog.

Solution architecture


The architecture of the solution described in this blog is based on the following software components:
• SAP HANA and Smart Data Access (I’m using SAP HANA SP10, Rev 102.06 as a reference in this blog)
• SAP Manufacturing Integration (MII) 15.1
• SAP Plant Connectivity (PCo) 15.1
• Osisoft PI SDK and AF SDK in order to access the Osisoft PI system


The SAP Plant Connectivity (SAP PCo) solution offers connectivity methods to Osisoft PI systems via the Osisoft PI SDK and AF SDK. SAP PCo is a software component that enables the data exchange between SAP systems and industry-specific standard data sources. In order to setup the technical connectivity between SAP PCo and the Osisoft PI system so-called SAP PCo Agents need to be configured.

Next, it is required to send queries to the Osisoft PI system to request time series data, e.g. a specific timeframe and PI tags. These types of queries can be configured via the SAP MII solution. SAP MII allows configuring of so-called SAP PCo queries (Tag Retrieve- or Tag Aggregate- Queries) and thus enables sending requests to the SAP PCo system. SAP PCo can forward these requests to the Osisoft PI system. Beside, SAP MII provides a connector for SAP HANA. This connector allows SAP HANA to expose SAP MII queries as Virtual Tables in the SAP HANA system.

When running a SQL select-statement on the SAP HANA system against one of these SAP MII virtual tables, the request is transformed into a SAP MII query. A Virtual Table can be used in a SAP HANA View as well. This integration enables the real time data retrieval from data in an Osisoft PI system in SAP HANA.

The solution architecture and components are highlighted in the diagram bellow.

Connecting SAP HANA Views to Sensor Data from Osisoft PI
Solution architecture of the real time integration of Osisoft PI in SAP HANA

Enhancing SAP HANA Views with Sensor Data from Osisoft PI


In this part of my blog, I focus on the integration of Virtual SAP MII Tables in SAP HANA Views. 
The following configuration steps are required in SAP HANA for consuming data from Osisoft PI in SAP HANA Views:
• Creating a new remote source from SAP HANA to SAP MII
• Importing SAP MII queries as Virtual Table in SAP HANA
• Adding a Virtual Table to a SAP HANA Calculation View

Creating a new remote source from SAP HANA to SAP MII


SAP HANA needs to establish a connection to the SAP MII system. Therefore, a new remote source needs to be configured via the Smart Data Access (SDA) feature of SAP HANA. The server name of the SAP MII system and the port number of the SAP HANA connector on the SAP MII system need to be specified . The image bellow shows the configuration of the remote source in SAP HANA Studio.

Connecting SAP HANA Views to Sensor Data from Osisoft PI
Remote SAP MII source in SAP HANA Studio

Note: If you are running a SAP HANA SPS10 system or above, you will need to set the 'enable_hana_odbc_sql_table' parameter in your SAP HANA system. This parameter enables you to import SAP MII queries as virtual tables in SAP HANA.

The following statement needs to be executed in SAP HANA in order to set the parameter:
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM') SET ('smart_data_access', 'enable_hana_odbc_sql_table') = 'true' WITH RECONFIGURE;

Importing SAP MII queries as Virtual Table in SAP HANA


Once the connection from SAP HANA to SAP MII has been established, the SAP MII queries appear in the remote source system configuration. Now, the SAP MII queries can be imported as Virtual Tables into a database schema in SAP HANA. The image bellow shows the Virtual Table definition of a SAP MII query in SAP HANA Studio.

Connecting SAP HANA Views to Sensor Data from Osisoft PI
SAP MII query as Virtual Table in SAP HANA Studio

Adding Virtual Tables to SAP HANA Views


When the Virtual Table for SAP MII is available in the SAP HANA system, it can be used within SAP HANA Views; equal to regular tables.

One important thing to keep in mind is that SAP HANA sends a request to the SAP MII system. This request is translated into a SAP MII query. SAP MII Tag Retrieve PCo queries require the definition of a TagName (the name of the PI tag for whom the data should be retrieved), DateTime (a timeframe for which data should be requested), and IntervalCount (number of intervals within the requested timeframe).

I recommend using the Virtual Table of the SAP MII query in a Projection Node of a SAP HANA Calculation View and setting a filter on the TagName, DateTime and IntervalCount on this Projection Node. This ensures that the filters are pushed down to the SAP MII system accordingly. The screenshot bellow shows a SAP HANA Calculation View with a Virtual Table of a SAP MII query in a Projection Node. The filter is set on the Projection Node.

Connecting SAP HANA Views to Sensor Data from Osisoft PI
Using Virtual Tables connected to Osisoft PI data in a SAP HANA Calculation View
In the example Calculation View above, I have configured three Input Parameters: IP_TAGNAME (Osisoft PI Tagname) and a from- and to- date (IP_FROM; IP_TO). These Input Parameters are used to set the filter for the SAP MII query. The screenshot bellow shows example values that are used for the Input Parameters of the Calculation View.

Connecting SAP HANA Views to Sensor Data from Osisoft PI
Input parameters of a HANA View that retrieves real time Osisoft PI data
Once the Input Parameters have been configured and the Calculation View is exeuted, the SAP MII system runs the query and requests data from the Osisoft PI system for the specified PI Tag and Timeframe via SAP PCo. The data are returned to SAP HANA and can be visualized in SAP HANA Studio directly (or any other front-end solution that can connect to SAP HANA Calculation Views of course). The screenshot bellow shows the visualization of time series data of an Osisoft PI Tag in SAP HANA Studio.

Connecting SAP HANA Views to Sensor Data from Osisoft PI
Time series data of an analog PI Tag, visualized in SAP HANA Studio
The example above shows the data visualization of an analog PI Tag. It is worthwhile to mention that you get a response to your request quite fast. In the example above, I have requested time series data for one PI Tag for more than a year and got the result from Osisoft PI in SAP HANA in just 312ms.
It is also possible to retrieve data of digital PI Tags in SAP HANA. Digital PI Tags represent the status of a PI Tag as a text-value. The screenshot bellow shows the result of a SAP MII query in SAP HANA Studio when requesting time series data of a digital PI Tag.

Connecting SAP HANA Views to Sensor Data from Osisoft PI
Time series data of a digital PI Tag, visualized in SAP HANA Studio
Source: scn.sap.com