GRC
HR
SCM
CRM
BI
Expand +


Article

 

Analyzing Sensor Data in SAP HANA in Real Time

by Dominik Schrank, Senior Consultant, SAP Deutschland SE & Co KG

June 7, 2017

The following solution design for applications allows for the combination of real-time sensor data with data from other sources within SAP HANA. The architecture is based on the SAP Manufacturing Integration and Intelligence (SAP MII) and SAP Plant Connectivity technologies. This flexible architecture enables the consumption of data from any system that collects sensor data and supports the OPC Unified Architecture (OPC-UA) industrial communication standard.

Businesses such as pharmaceutical, oil and gas, and industrial organizations leverage the OPC Unified Architecture (OPC-UA) industrial communication standard to facilitate machine-to-machine communication between industrial equipment and systems that collect data.

OPC-UA is a cross-platform standard that is freely available. The concept is based on a service-oriented architecture. This standard is developed by the OPC Foundation. The SAP Plant Connectivity solution allows you to connect to systems that support the OPC-UA standard. The architecture described in this article is applicable for systems that support the OPC-UA communication standard. This makes the concept very flexible and sets the basis for the implementation of advanced analytics use cases. The connectivity to an OSIsoft PI system is highlighted to demonstrate one use case. More details on the OPC-UA standard can be found on the OPC Foundation’s website.

I examine the architectural concepts and characteristics for using sensor data in SAP HANA. As an example, I highlight the real-time connectivity to an OSIsoft PI system in SAP HANA without persisting this data. The required configuration steps in the SAP solutions are revealed, and options to build SAP HANA calculation views that consume sensor data as well as data that is already available in SAP HANA are discussed.

The OSIsoft PI system is leveraged by organizations across different industries to collect operational data (e.g., temperature, pressure, and flow) from sensor devices. This sensor data can be used for tasks such as viewing the operational performance of assets in real time, monitoring the quality of products, and identifying machine failures. In addition, sensor data from an OSIsoft PI system can even be consumed in SAP HANA through the smart data access functionality, thus enabling new insights when sensor data is combined with information from other sources.

The method explained in this article supports data retrieval from the OSIsoft PI Data Archive as well as from the OSIsoft PI Asset Framework (PI AF). The PI Data Archive is the component of an OSIsoft PI system that provides efficient storage and methods for archiving time-series data. The data is assigned to so-called PI tags. The PI AF is a repository that organizes data in hierarchies and groups elements per their relationships. Elements are asset-centric objects; they can expose several attributes, such as PI data points or calculations.

Solution Architecture

The architecture of this solution is based on the following software components:

  • SAP HANA and smart data access (the architecture in this article was tested with SAP HANA Support Package 12)
  • SAP Manufacturing Integration and Intelligence (SAP MII) 15.1
  • SAP Plant Connectivity 15.1
  • OSIsoft PI Software Development Kit (SDK) and AF SDK to access the OSIsoft PI system

The SAP Plant Connectivity solution offers connectivity methods to OSIsoft PI systems via the OSIsoft PI SDK and AF SDK. SAP Plant Connectivity is a software component that enables the data exchange between SAP systems and industry-specific standard data sources. To set up the technical connectivity between SAP Plant Connectivity and the OSIsoft PI system, you need to configure SAP Plant Connectivity Agents.

Next, you must send queries to the OSIsoft PI system to request time-series data (a specific time frame and PI tags). These types of queries can be configured via the SAP MII solution. SAP MII allows the configuring of so-called SAP Plant Connectivity queries (Tag Retrieve Queries or Tag Aggregate Queries) and thus enables the sending of requests to the SAP Plant Connectivity system. SAP Plant Connectivity can forward these requests to the OSIsoft PI system. In addition, 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. A virtual table in SAP HANA is a mapping to tables or views located in remote data sources. Virtual tables can be used in SAP HANA just like standard database tables.

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

Figure 1 highlights the solution architecture and components.


Figure 1
Solution architecture

Required System Configuration

Next, I discuss the following required configuration steps to retrieve sensor data from OSIsoft PI in SAP HANA:

  • The required configuration steps in the SAP Plant Connectivity solution
  • SAP MII configuration
  • The configuration steps required to connect SAP HANA to SAP MII and thus enable real-time data retrieval from OSIsoft PI 
SAP Plant Connectivity Configuration Steps

SAP Plant Connectivity is the component that provides technical connectivity to the OSIsoft PI system. The following configuration steps are required in SAP Plant Connectivity to connect to the OSIsoft PI system (the SAP Plant Connectivity Management Console is required to perform the configuration steps):

  • Create a source system to connect to OSIsoft PI
  • Create a target system to connect to SAP MII
  • Create an SAP Plant Connectivity Agent that can handle requests from SAP MII 

The SAP Plant Connectivity Management Console allows you to configure the connectivity to the source and destination systems. In the example in this article, OSIsoft PI is the source system and SAP MII is the destination system. Figure 2 shows the main screen of the application.


Figure 2
The SAP Plant Connectivity Management Console

First, you must establish a technical connection to the OSIsoft PI system. This can be configured by adding a new source system in the SAP Plant Connectivity Management Console. Click the add source system icon under the Source Systems menu, and the Add Source System dialog in Figure 3 appears.


Figure 3
Add a new source system in the SAP Plant Connectivity Management Console

Select a source system type. For the connectivity to the OSIsoft PI system, you can select the option Asset Framework Agent or PI Agent from the Source System Type drop-down menu. The option Asset Framework Agent is the SAP-recommended connectivity method for connecting SAP Plant Connectivity to OSIsoft PI. It is a new connectivity method provided by the OSIosft PI SDK that offers more features and better performance. Confirm the selection by clicking the OK button in the Add Source System dialog window. 

The screen in Figure 4 opens where you specify the server name of the OSIsoft PI server in the PI Server field. This option is available once the source system type is selected. SAP Plant Connectivity supports PI Trust or User Name/Password as authentication mechanisms to the OSIsoft PI system. Figure 4 shows the configuration of the OSIsoft PI server name. In Figure 4, the name of the PI server is PI_SERVER_NAME.


Figure 4
Configure the PI server name in the SAP Plant Connectivity Management Console

After specifying the OSIsoft PI server name, go to the Settings tab to enter the connection settings (Figure 5). In this article, I highlight the option to retrieve numeric values for PI tags of an OSIsoft PI system. This type of PI tag is called Analog PI tags. An OSIsoft PI system also supports Digital PI tags. This type of tag represents values as texts (e.g., the status of a PI tag could be Stopped or Running). If Digital PI tags need to be retrieved, select the Digital Settings option DigStr as String check box. 


Figure 5
Source system settings in the SAP Plant Connectivity Management Console

The Asynchronous Call Type option defines that SAP Plant Connectivity submits a tag request to the OSIsoft PI server that can be terminated by the OSIsoft PI server. The Use GetPoints2 check box needs to be selected for newer versions of the OSIsoft PI SDK. When you select this option, the OSIsoft PI SDK method GetPoints2 method is used to retrieve data. This method results in a better query-execution performance. 

The Number of Threads option defines the number of parallel threads (processes) to be used to connect to the OSIsoft PI system. Especially if the solution is expecting a high load, SAP recommends you increase the number of threads for an optimal performance. The number of threads depends on the expected load on the system and SAP recommends you test a different number of thread configuration settings to identify the optimal performance within your landscape. Eight is the maximum thread number that can be configured. Once you’ve made the configuration settings, save them by clicking the save icon on the top left. 

This configuration step concludes the connectivity to the OSIsoft PI source system in the SAP Plant Connectivity Management Console. Further details of other configuration settings can be found in the SAP Plant Connectivity documentation on this website

In the next step, you configure a destination system in the SAP Plant Connectivity solution. A destination system receives the data that is selected from the source system. In the method explained in this article, the destination system is of the type SAP MII. 

This can be configured by adding a new destination system in the SAP Plant Connectivity Management Console. First, click the add destination system icon under the Destination Systems menu in the SAP Plant Connectivity Management Console. This opens the screen in Figure 6 where you can specify the Server Name, Port, and Version of the SAP MII system as well as the User Name and Password of a technical SAP MII user. The technical SAP MII user is used to connect to the SAP MII system and push the data from OSIsoft PI. Figure 6 shows the required configuration of the SAP MII destination system.


Figure 6
Configuring an SAP MII destination system in the SAP Plant Connectivity Management Console

The final step in the required SAP Plant Connectivity setup is the configuration of an Agent Instance. This component is a process that can receive requests, connect to the source system, and send data to a destination system. 

This is also configured in the SAP Plant Connectivity Management Console. First, click the add agent instances icon under the Agent Instances menu in the SAP Plant Connectivity Management Console (Figure 7). Now you are able to specify the source system and destination system. The configuration of the new Agent Instance is completed when you click the save icon on the top left corner. The name of the new Agent Instance then appears in the list under the Agent Instances menu. A green icon indicates that the new Agent Instance is available. In this example, a new Agent Instance with the name PI_ANALOG_PCO_AGENT was created. 


Figure 7
Adding a new Agent Instance in the SAP Plant Connectivity Management Console

A required configuration step for the connectivity method is the definition of a Query Port. This option allows you to determine the settings for the data flow between the source and destination systems. Click the new Agent Instance PI_ANALOG_PCO_AGENT that was created in the previous step and a window opens where you can specify the configuration. Select the Query Ports tab to continue with the required configuration (Figure 8). Select SAP MII as the Port Type and then select a port number of the SAP Plant Connectivity Agent Instance. SAP MII can reach the SAP Plant Connectivity Agent Instance from the outside using the port specified here. Complete the configuration by clicking the save icon on the top left corner.


Figure 8
Maintain the SAP MII Query Port in the SAP Plant Connectivity Management Console

This configuration step completes the setup of SAP Plant Connectivity to retrieve sensor data from an OSIsoft PI system in SAP HANA. Next, you need to perform the required configuration in SAP MII. 

SAP MII Configuration Steps

SAP MII is the component that provides the technical connectivity to the SAP Plant Connectivity and SAP HANA systems. Furthermore, SAP MII allows you to define the data-retrieval methods from the OSIsoft PI system. The following configuration steps are required in SAP MII:

  • Create a data server that connects to the SAP Plant Connectivity Agent Instance
  • Create an SAP MII connection to SAP HANA
  • Define the data-retrieval method for OSIsoft PI by creating an SAP MII query

A data server in SAP MII defines the connectivity to an SAP Plant Connectivity Agent Instance. In this section I demonstrate how to connect to the SAP Plant Connectivity Agent Instance that was set up in the previous section. 

First, access the SAP MII web application to perform the required configuration steps. This can be accessed in a web browser via the following URL: http://<server>:<port>/XMII/. The <server> and <port> need to be specified depending on the installation in your environment. To configure a data server in the SAP MII solution, select the Data Servers option from the SAP MII web application Data Services menu tree (Figure 9). Specify the name of the SAP MII data server and select PCoConnector as the Connector Type. Click the Next button to get to the configuration step to specify the PCoConnector web services URL.


Figure 9
The data servers in the SAP MII web application

This opens the screen where you specify the connectivity to the SAP Plant Connectivity system (Figure 10). Select the Enter the URL to the PCo instance manually radio button and enter the URL in the box on the right. Click the Next button to get to the final configuration step.


Figure 10
Configure the SAP Plant Connectivity Instance URL in the SAP MII web application

Once the technical connectivity to SAP Plant Connectivity is established, the SAP Plant Connectivity Agent Instance created in the previous chapter is displayed (Figure 11). Select the SAP Plant Connectivity Agent Instance and click the Finish button to complete the configuration.


Figure 11
Add a new SAP Plant Connectivity Agent Instance in the SAP MII web application

The new SAP Plant Connectivity Agent Instance is added to the list of data servers within SAP MII (Figure 12). Now the data server needs to be enabled to be ready to be used. Select the newly added data server, tick the Enabled check box, and click the Save button in the menu bar.


Figure 12
Enable the SAP Plant Connectivity Agent Instance in the SAP MII web application

You can confirm the successful connectivity to the SAP Plant Connectivity Agent Instance by clicking the Status tab of the Data Server configuration (Figure 13).


Figure 13
Confirmation that the SAP Plant Connectivity Agent instance is available in the SAP MII web application

Next, to configure the technical connectivity to SAP HANA a new SAP HANA smart data access connection must be created in the SAP MII web application. Select the Connections function in the Data Services group in the SAP MII: Connections screen (Figure 14).


Figure 14
SAP HANA smart data access connection in the SAP MII web application

Click the Create button to create a new connection (Figure 15). Select HANA SDA as the Connection Type, define a unique name, and confirm the configuration by clicking the OK button.


Figure 15
Add a new SAP HANA connection in the SAP MII web application

Once the technical connection to SAP HANA and SAP Plant Connectivity is established, configure the SAP MII queries. SAP MII queries allow you to define data-retrieval methods from the OSIsoft PI system. In this article, I highlight the configuration of Tag Retrieval Query and Tag Aggregate Query methods. A Tag Retrieval Query allows you to retrieve, for example, interpolated or recorded values of PI tags from an OSIsoft PI system. A Tag Aggregate Query allows you to retrieve aggregated PI tag information such as the time-weighted average or min/max values over a certain period. 

SAP MII queries are configured within the SAP MII Workbench. Select the Workbench option from the Content Development group of the SAP MII web application to start the SAP MII Workbench (Figure 16).


Figure 16
Develop SAP MII queries with the SAP MII Workbench

The SAP MII Workbench organizes SAP MII queries in folders. The folder structure is visible in the top left corner of the application window. Create a new SAP MII query by clicking the new icon on the menu bar. I highlight the steps to create an SAP MII query that retrieves interpolated values for PI tags from the OSIsoft PI system. 

First, select the Data Source option under the Template Categories section when creating a new SAP MII query (Figure 17). Select the SAP Plant Connectivity Agent Instance that was configured as an SAP MII Data Server in the previous step. Since in this example I want to retrieve interpolated values for PI tags from the OSIsoft PI system, select the TagRetrieveQuery option in the Modes box.


Figure 17
Developing a TagRetrieveQuery with the SAP MII Workbench

You can define SAP MII query parameters in the General section of the Template Categories tab of the SAP MII Workbench. Click the General button in the Template Categories tab in Figure 17 to open this screen (Figure 18). Query parameters allow you to specify, for example, the number format or query-cache settings. In the example described in this article no changes need to be performed. A complete list of the available query parameters and explanation is available in the SAP MII documentation.


Figure 18
Configuration settings of a Tag Retrieve Query in the SAP MII Workbench

The view options and extended parameters need to be specified for the SAP MII query in this configuration step. Select the Tag Retrieve Query option from the Template Categories tab in Figure 18 and the screen in Figure 19 opens. In this example, the selected view option is PointPlot. This option allows the data retrieval of interpolated information from the OSIsoft PI system. Other options such as Current, Event, and Interpolated are also available.


Figure 19
Configure a Tag Retrieve Query in the SAP MII Workbench

Additionally, it might be interesting for end users to get the information on the status of certain values of a PI tag that the OSIsoft PI system returns. Per the recorded value of a PI tag, the OSIsoft PI system returns an IsGood parameter. This parameter can have two values: 0 or 1. The value of 0 indicates that an error occurred during the recording of a certain value. The value of 1 indicates that the recorded value is a valid one. To retrieve this information later in SAP HANA, the IsGood option needs to be selected under the Extended tab of the Tag Retrieve Query options (Figure 20). Click the save icon after selecting the IsGood option to save these configuration settings.


Figure 20
Available metadata for the TagRetrieveQuery in the SAP MII Workbench

This configuration step completes the required setup within SAP MII to retrieve values for OSIsoft PI tags. In the next section I show the final configuration steps to retrieve values from OSIsoft PI in SAP HANA.

SAP HANA Configuration Steps

The following configuration steps are required in SAP HANA for consuming data from Osisoft PI in SAP HANA views: 

  • Create a new remote source via SAP HANA smart data access from SAP HANA to SAP MII
  • Import SAP MII queries as a virtual table in SAP HANA
  • Add a virtual table to an SAP HANA calculation view 

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 feature of SAP HANA. (For an introduction to smart data access, click here.)

This configuration is performed in the SAP HANA studio application (Figure 21). Select the Provisioning option on the left in SAP HANA studio, right-click Remote Sources, and select New Remote Source from the context-menu options that open (not shown). This opens a new window on the right of Figure 21 where you can specify the connectivity to the SAP MII system.


Figure 21
Configure an SAP MII remote source in SAP HANA

Specify the server name of the SAP MII system and the port number of the SAP HANA connector on the SAP MII system. Select the value HANA (ODBC) for the Adapter Name and confirm the configuration settings by clicking the save icon. 

On an SAP HANA Support Package Stack 10 system or above, you need to enable the parameter enable_hana_odbc_sql_table in the SAP HANA system. This parameter can be enabled by opening a new SQL console via the Open SQL console button in the SAP HANA studio application and executing the following SQL statement:

ALTER SYSTEM ALTER CONFIGURATION (‘indexserver.ini’, ‘SYSTEM’) SET (‘smart_data_access’, ‘enable_hana_odbc_sql_table’) = ‘true’ WITH RECONFIGURE; 

After you set this parameter, you can import SAP MII queries as virtual tables in SAP HANA. 

An SAP MII query can be added as a virtual table by opening the SAP HANA studio application and clicking the Provisioning entry on the left side. Next, select the remote system that was configured in the previous step and click the <NULL> and <PISDK> entries. Right-click the SDA/PLOTVALUES entry and select Add as Virtual Table from the context menu options that open. A new window appears that allows you to complete the configuration. Figure 22 shows this step.


Figure 22
Add an SAP MII query as a virtual table in SAP HANA

In the next step, specify the database schema in SAP HANA studio (where the virtual table will be created) and the table name (Figure 23). Click the Create button to confirm the configuration.


Figure 23
Configuration steps to add an SAP MII query as a virtual table in SAP HANA studio

Once the connection from SAP HANA to SAP MII has been established, the SAP MII queries appear in the remote-source system configuration. Now SAP MII queries can be imported as virtual tables into a database schema in SAP HANA studio. The virtual table has the columns shown in Table 1. 

Column name

Definition

TagName

This is the name of the PI tag from the OSIsoft system

IntervalCount

A numeric value that allows you to define the number of intervals to be used for a specific time frame

Duration

This column defines the duration of an interval. This field is relevant in combination with the DurationUnit field.

DurationUnit

This field defines the type of a duration. Possible values are d for days, h for hours, m for minutes, and s for seconds.

DateTime

This field holds the time stamp of the recorded value of the PI tag from OSIsoft PI

Value

This field holds the actual value that was recorded in the OSIsoft PI system

IsGood

The value of this field defines if a measurement from the OSIsoft PI system was valid or if an error occurred. The value 1 means that the measurement was valid; 0 means that an error occurred.

Table 1
The virtual table columns 

Figure 24 shows the virtual table definition of an SAP MII query in SAP HANA studio.


Figure 24
Virtual table in the SAP HANA database schema

Once the virtual table is available in the SAP HANA database schema, you can run a SQL-select statement and retrieve values from the OSIsoft PI system. 

The WHERE-clause of the SQL statement needs to include the following conditions:

  • TagName – At least one or multiple names of OSIsoft PI tags need to be defined
  • DateTime – A time range needs to be selected
  • IntervalCount – The number of intervals of the selected time frame needs to be specified 

An example SQL SELECT statement is highlighted in Figure 25. Values for the PI tag with the name TAGNAME1 are selected for the time frame from 2016-02-27 00:00:00 to 2016-03-27 00:00:00. The selected time frame is split up into 100 evenly distributed time intervals.


Figure 25
Test the connectivity to OSIsoft PI via an SAP MII query as a virtual table in SAP HANA

This configuration step completes the connectivity from SAP HANA to the OSIsoft PI system via SAP MII and SAP Plant Connectivity. Now you can create an SAP HANA calculation view that leverages the virtual table and thus can consume sensor data from OSIsoft PI. 

Building SAP HANA Calculation Views That Leverage Sensor Data

SAP HANA sends a request to the SAP MII system to retrieve the sensor data. This request is translated into an SAP MII query. SAP MII Tag Retrieve Plant Connectivity queries require the definition of a TagName (the name of the PI tag for whom the data should be retrieved), DateTime (a time frame for which data should be requested), and IntervalCount (the number of intervals within the requested time frame). 

I recommend using the virtual table of the SAP MII query in a Projection node of an 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. Figure 26 shows an SAP HANA calculation view with a virtual table of an SAP MII query in a Projection node. The filter is set on the Projection node.


Figure 26
SAP HANA calculation view that connects to OSIsoft PI via virtual table SAP MII query

I have configured three input parameters in the SAP HANA calculation view: the IP_TAGNAME (OSIsoft PI tag name) and from and to dates (IP_FROM; IP_TO). These input parameters are used to set the filter for the SAP MII query. Figure 27 shows example input-parameter values of the calculation view. The highlighted icon is an optional value help for end users to select the date rather than typing it.


Figure 27
Input parameters for an SAP HANA calculation view

Once the input parameters are configured and the calculation view is executed, the SAP MII system runs the query and requests data from the OSIsoft PI system for the specified PI tag and time frame via SAP Plant Connectivity. The data is returned to SAP HANA and can be visualized in SAP HANA studio directly (or in any other front-end solution that can connect to SAP HANA calculation views). Figure 28 shows the visualization of time-series data of an OSIsoft PI tag in SAP HANA studio.


Figure 28
Real-time sensor data retrieved in SAP HANA studio

This example shows the data visualization of an analog PI tag. It is worthwhile to mention that the response time is quite fast. 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 312 milliseconds. 

Combining Sensor Data and Non-Sensor Data in an SAP HANA Calculation View

The previous section focused on retrieving measurements of real-time sensor data via an SAP HANA calculation view. In this section, I highlight a use case for the real-time data integration option in SAP HANA. I demonstrate how different types of sensor data can be combined and how non-sensor data can be combined with sensor data in an SAP HANA calculation view.

As an example, I demonstrate how to build an SAP HANA calculation view that displays the temperature values of a PI tag over a certain time. Furthermore, the SAP HANA calculation view is enhanced by the daily average temperature values. The example is completed by adding data to the SAP HANA calculation view that is persisted in the SAP HANA database—the high-/low-temperature-limit values for PI tags. Using this type of SAP HANA calculation view, an analyst can not only evaluate real-time temperature values, but also can immediately identify when certain measurements are above daily averages or a predefined high/low limit. 

First, I explain the configuration steps to create an SAP MII query and an SAP HANA calculation view that can retrieve different types of OSIsoft PI aggregation methods dynamically—for example, daily time-weighted average values for sensor data over a certain period. 

Second, I demonstrate how different types of sensor data (real-time measurements and dynamic aggregates) can be combined in one SAP HANA calculation view. 

Third, I highlight how sensor data can be combined with non-sensor data in an SAP HANA calculation view. 

Consuming Dynamic Aggregates PI Data in SAP HANA

To retrieve aggregated values from OSIsoft PI, you first must create a new SAP MII query via the SAP MII Workbench. In the previous section, I demonstrated how to create a TagRetrieveQuery. For this requirement, select the TagAggregateQuery option as the mode (Figure 29).


Figure 29
TagAggregateQuery in SAP MII Workbench

To review the support types of aggregates, select Tag Aggregate Query from the Template Categories menu. This opens the screen on the right of Figure 30 with the list of supported aggregates in the SAP MII Workbench. SAP MII supports the retrieval of Average, AverageTW (Time Weighted), Count, Integral, and Max (and Min) values for OSIsoft PI tags over a certain time frame. None of these methods need to be selected in the SAP MII query, but rather, the values can be passed dynamically from SAP HANA. This option allows you to specify what type of aggregates should be retrieved from OSIsoft PI at the moment you run the SQL SELECT statement from SAP HANA. Figure 30 shows some of the available aggregates; you need to scroll down to see them all.


Figure 30
Some of the available aggregation methods for the Tag Aggregate Query

After creating the SAP MII query with the mode TagAggregateQuery, complete the step to import this new SAP MII query into SAP HANA as a virtual table. The configuration steps are the same as for the previous virtual table described in this article. 

Once the new virtual table is available in SAP HANA, you can create a new SAP HANA calculation view and use the virtual table in a Projection node. A Projection node is used to retrieve values directly from a source table in an SAP HANA calculation view without aggregating the data. Figure 31 shows the SAP HANA calculation view and the virtual table.


Figure 31
SAP HANA calculation view that consumes the dynamic aggregates SAP MII query

After setting the virtual table in a Projection node in the SAP HANA calculation view, you can specify the filter options. Like the SAP HANA calculation view in the previous section, filters on the TagName, IntervalCount, Duration, and DurationUnit are required. Additionally, specify a filter on the AggregateType. The type of aggregated values that you want to retrieve from the OSIsoft PI system is specified in this field. Possible values are, for example, AverageTW, Min, or Max. Figure 32 highlights the filter criteria on the virtual table in the SAP HANA calculation view.


Figure 32
Filter options for the dynamic aggregates SAP MII query

Now the SAP HANA calculation view is defined in a very flexible way that supports, for example, the retrieval of the daily or hourly average values for a specific PI tag for a specific time frame. In Figure 33 I highlight the retrieval of the daily (IP_DURATION_UNIT = d) time-weighted average (IP_AGGREGATION_TYPE = AverageTW) values for the PI tag (IP_TAGNAME) TAGNAME_A for the last 15 days (IP_INTERVAL = 15, IP_DURATION = 15).


Figure 33
SAP HANA calculation view input parameters for the Tag Aggregate Query SAP MII query

The data preview option in SAP HANA studio allows you to retrieve the values from OSIsoft PI directly. Right-click the SAP HANA calculation view and select Open data preview from the context menu options that open (not shown). Figure 34 shows the time-weighted average values for a specific PI tag over the last 15 days.


Figure 34
Average temperature value of the PI tag over the last 15 days

Combining Real-Time Measurements with Dynamic Aggregated PI Data

I previously demonstrated how to create SAP HANA calculation views that retrieve interpolated and aggregated values from OSIsoft PI. Now I show how to combine different types of sensor data in one SAP HANA calculation view. I also demonstrate how to create an SAP HANA calculation view that displays interpolated values and daily average values. 

You create a new SAP HANA calculation view that includes the views for interpolated and aggregated data in Projection nodes. Both Projection nodes can be joined via a left outer join on the PI tag name and the day of the measurement. This left outer join allows you to combine the daily average values with interpolated PI tag data values. Figure 35 shows the left outer join between the two projections.


Figure 35
SAP HANA calculation view that joins the real-time PI tag values with the daily average values

Check the new SAP HANA calculation view in SAP HANA studio using the data preview context menu option, and then the interpolated and aggregated data points can be visualized.  Figure 36 shows the data visualization in SAP HANA studio.


Figure 36
Result of the SAP HANA calculation view that combines the temperature measurements with the daily average values

This step completes the configuration of an SAP HANA calculation view that combines different types of PI data from an OSIsoft system. 

Adding Non-PI Data to the SAP HANA Calculation View

The example in this section highlights the option to combine non OSIsoft PI data with interpolated and aggregated data from OSIsoft PI. Business users might be interested not only in seeing the interpolated and aggregated data from OSIsoft PI, but also in combining this data with information regarding the limits of certain values. This enables business users to immediately identify if certain sensor data is above or below predefined limits. 

To demonstrate this option, the SAP HANA calculation view from the previous section is extended by two Projection nodes that include an SAP HANA table that holds the high- and low-limit information for PI tags. This information can be joined in the existing SAP HANA calculation view. Figure 37 shows the two Projection nodes for the high and low limits in the SAP HANA calculation view. A left outer join is performed on the tag name to retrieve the information for each OSIsoft PI tag measurement.


Figure 37
Adding non-PI data to the SAP HANA calculation view

The interpolated and aggregated values of a PI tag can now be visualized together with the high and low limits for the tag when performing a data preview in SAP HANA studio. The high and low limits are a constant line in the graph. It is immediately obvious to business users when certain PI tag measurements are beyond the limits. These insights are very helpful to identify time frames to focus on for further investigation. Additional scenarios could be implemented as well in which alarms are triggered if PI tag measurements are beyond predefined limits. Figure 38 shows the visualization in SAP HANA studio.


Figure 38
Result of the SAP HANA calculation view that combines different types of PI and non-PI data

This step completes the configuration of a use case that SAP HANA enables with the integration of OSIsoft PI via SAP MII and SAP Plant Connectivity—combining sensor data with SAP HANA data in one SAP HANA calculation view. This integration allows for the creation of data exploration views that enable useful insights for analysts. 

Lessons Learned

The solution architecture described in this article enables the following scenarios:

  • Retrieve sensor data from systems that support the OPC-UA communication standard and combine this data with information from other sources within SAP HANA
  • Dynamically requesting data for different PI tags (e.g., send a query to OSIsoft PI and request interpolated data of tags for specific days/weeks/months). Required variables: PI Tagname (one or multiple), Timeframe, and Number of intervals.
  • Requesting data for multiple PI tags at the same time dynamically (e.g., up to 10 different PI tags at the same time). End users determine which PI tags they want to see. The name of the PI tag is required as the input parameter.
  • Request data for analog (numeric value) and digital (state of a PI tag) PI tags
  • Use different functions to request PI data (Interpolated, Plotvalues, and Dynamic Aggregations such as time-weighted average and min/max values)
  • Combining OSIsoft PI data with data persisted in SAP HANA tables 

Retrieving data from the OSIsoft PI system without having to store the data within SAP HANA is a very useful feature. Depending on the type of sensor device, multiple data points might be captured every millisecond. This results in a large amount of data that needs to be stored and processed. The OSIsoft PI system is optimized to store this type of sensor information on a very granular level. Furthermore, the OSIsoft PI system already provides functionalities for an optimal data retrieval. These functionalities can be leveraged by the method described in this article. This saves data storage on the SAP HANA system, as well as development effort to create calculation models where OSIsoft PI already provides standard calculations. 

The technical setup of the system integration is straightforward. Once the SAP MII and SAP Plant Connectivity systems are up and running, configuring the connectivity from SAP HANA to OSIsoft PI requires just a minor effort. Furthermore, working with the virtual tables that access data from OSIsoft PI is as simple as working with standard column tables in SAP HANA models. This makes the adoption for SAP HANA modelers quite an easy one. 

An interesting finding during the implementation was that there is only a minimal performance overhead when retrieving the data in real time in the SAP HANA system. Most of the end-to-end run time is the data processing happening on the OSIsoft PI system. In the examples used in this article, I just requested data for one PI tag. A feature that is worth highlighting is that if data is requested for more than one PI tag, the requests to the OSIsoft PI system are sent in parallel from SAP MII. This ensures an optimal performance even when you are requesting a larger set of PI tags at the same time. 

SAP MII supports different types of SAP Plant Connectivity queries (Tag Retrieve, Tag Aggregates); this enables retrieving data for analog or digital PI tags from an OSIsoft PI system with different functions (e.g., Plotvalues, Interpolation, Last Recorded, or Dynamic Aggregates). These different types of SAP MII queries can be used as virtual tables in SAP HANA and thus enable great use cases for analyzing data from OSIsoft PI in SAP HANA.

Companies that are using SAP MII, SAP Plant Connectivity, and SAP HANA can consider this architecture as an option with minimal effort to integrate sensor data from systems that support the OPC-UA communication standard in SAP HANA.  

An email has been sent to:





 

Dominik Schrank

Dominik Schrank is a senior consultant for Business Intelligence solutions at SAP Deutschland SE & Co. KG. He has comprehensive experience in designing and implementing large business analytics applications based on the SAP HANA-, SAP BusinessObjects BI-, and the SAP HANA Cloud Platform- solution portfolio for global customers.



COMMENTS

Please log in to post a comment.

No comments have been submitted on this article. Be the first to comment!


SAPinsider
FAQ