Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
I067599
Employee
Employee

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. [7] 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. [6] 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. [3]

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. [2]

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

Image: 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 configuration of SAP MII queries and the connection to SAP PCo is described in this document: [2].

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 (an introduction to SDA can be found here [10]). 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 (see [2] on how to configuration and start the SAP MII HANA connector). The image bellow shows the configuration of the remote source in SAP HANA Studio.

Image: 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. [11]

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.

Image: 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.

Image: 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.

Image: 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.

Image: 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.

Image: Time series data of a digital PI Tag, visualized in SAP HANA Studio

Conclusion

The approach described in this blog highlights a method to retrieve data from an Osisoft PI system in SAP HANA in real time without having to persist the data in SAP HANA. The technical setup of the system integration was straightforward (see the SAP HANA SDA Integration with SAP MII article [2]). Once the SAP MII and SAP PCo systems are up and running, configuring the connectivity from SAP HANA to Osisoft PI is of 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 negligible performance overhead when retrieving the data in real time in the SAP HANA system. The majority of the end to end runtime is the data processing happening on the Osisoft PI system. In the example used in this blog, I just requested data for one PI Tag. A great feature that is worth highlighting is that if data are requested for more than one PI tag, the requests to the Osisoft PI system will be sent in parallel from SAP MII. This ensures an optimal performance even when requesting a larger set of PI Tags at the same time.

SAP MII supports different types of SAP PCo 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, 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.

References

SAP MII Doku [1] https://help.sap.com/mii

SAP HANA SDA Integration with SAP MII [2] https://scn.sap.com/docs/DOC-69597

SAP PCo [3] https://help.sap.com/pco

SAP MII and Lumira [4] http://scn.sap.com/docs/DOC-58260

SAP Manufacturing Implementation Architecture  [5]

http://scn.sap.com/docs/DOC-1845

Osisoft PI Asset Framework [6] https://techsupport.osisoft.com/Products/PI-Server/PI-AF/Overview/

Osisoft PI [7] https://techsupport.osisoft.com/Products/PI-Server/PI-Data-Archive/Overview/

Osisoft PI Basics – Video [8] https://pisquare.osisoft.com/videos/1454

SAP MII Query Types [9] https://help.sap.com/saphelp_mii122sp01/helpdata/en/4a/a98326ff1537b1e10000000a421138/content.htm?or...

Smart Data Access Introduction [10]

http://scn.sap.com/community/developer-center/hana/blog/2014/06/02/sap-hana-smart-data-access1-a-bri...

SAP MII Connections [11]

http://help.sap.com/saphelp_mii151sp02/helpdata/en/4c/72e07ce631469ee10000000a15822d/content.htm

4 Comments