csawyer

Piping PI Data to Open Applications using PI SQL, RTQP and Microsoft SQL Server

Blog Post created by csawyer Employee on Aug 15, 2018

One of the coolest things about Microsoft SQL Server in the last couple of years is how it has expanded from the confines of Windows Server and can now run on all three major desktop OSes, as well as sit in the cloud.

None of that expansion would have mattered much if downstream clients for SQL Server didn’t also expand their horizons to touch more platforms. And with Microsoft client tools for Linux and Mac, this is no longer an issue.

You can sneak PI Data through this mechanism

We can take advantage of Microsoft SQL Server and OSIsoft PI SQL by adding a linked server to SQL Server that forwards queries to PI Server. From there we can build SQL views which opens a portal into both the PI Data Archive and PI AF directly. You can also combine your own data stored in SQL with your real-time data. Downstream applications will see normal every day recordsets.

Here’s a screenshot where I’ve used this technique to pull data directly into Microsoft Excel for Mac. Not only is this data fresh, but I can refresh the query in my worksheet just like I would do in Excel for Windows. The connection from the worksheet is going straight to SQL Server.

ExcelForMac.png

Setup Steps

Setup PI SQL Data Access Server (RTQP Engine)

Make sure you’ve installed the PI SQL Data Access Server (RTQP) Engine which is in your PI Server 2018 (and later) install kit:

RTQP Install.png

Grab PI SQL Client

Next you need to get the PI SQL Client kit and install this on the instance where your SQL Server is. You can grab it from the OSIsoft Technical Support Downloads page.

Configure the PI SQL Client provider in Microsoft SQL Server Enterprise Manager

Hop over to SQL-EM and modify the linked server provider to ensure these options are switched on:

pisqlsetup.png

Create a Linked Server connection

By right-clicking on the Linked Servers folder in SQL-EM you can set up any number of linked server connections. Typically, you will want to set up one linked server connection per AF database. Here I’ve setup a connection to NuGreen:

linkedserversetup.png

Now the fun part: queries!

First let’s go with a basic type of query that finds all the pumps in the NuGreen database

Query

SELECT [ID]
   ,[Name]
   ,[Description]
   ,[Comment]
   ,[Revision]
   ,[HasChildren]
   ,[PrimaryPath]
   ,[Template]
   ,[Created]
   ,[CreatedBy]
   ,[Modified]
   ,[ModifiedBy]
   ,[TemplateID]
  FROM [PISERVER_TEST].[Master].[Element].[Element]
WHERE Name LIKE 'P%'
GO

Simple enough. This yields the following:

pumpssql.png

We can use a SQL Database to expose this as a view by wrapping this query with CREATE VIEW.

USE TEST
GO

CREATE VIEW REPORTING_PUMPS 
AS

SELECT [ID]
   ,[Name]
   ,[Description]
   ,[Comment]
   ,[Revision]
   ,[HasChildren]
   ,[PrimaryPath]
   ,[Template]
   ,[Created]
   ,[CreatedBy]
   ,[Modified]
   ,[ModifiedBy]
   ,[TemplateID]
  FROM [PISERVER_TEST].[Master].[Element].[Element]
WHERE Name LIKE 'P%'
GO

Now, when we select everything in the view we get:

reportingpumpsview.png

Perfect. Now that we have PI Server data we can pull this across to any application that can communicate to Microsoft SQL Server.

Importing PI Server data into Excel for Mac

Now that we have PI Server data exposed to Microsoft SQL Server it is fairly painless to connect this to downstream applications that can read recordsets from there. Let’s use this to connect the view we set up.

SQLServerODBCMac.png

Microsoft Excel can import remote a SQL datasets in the Data tab. From there you can select New Database Query and SQL Server ODBC.

odbcconnect.png

Insert your SQL Server credentials and authentication method and click Connect. A “Microsoft Query” window will then appear where you can enter a SQL statement to produce a recordset. It will follow the same syntax that you would use in SQL-EM.

From here I’ll select the contents of my view. Press Run to execute it on the server and inspect what comes back.

querywindow.png

Now you can press Return Data to deposit the results into your Excel worksheet. The connection to SQL Server is preserved in your worksheet when you save the Excel workbook. You can edit it and re-run the query by visiting the connections button that’s also on the Data ribbon.

connections.png

Data now refreshes on your terms in your Excel worksheet and your connection details are preserved between document openings.

Caveats

Read-only

Presently the restrictions that existed with PI OLDEDB Enterprise also apply to the latest PI SQL and Data Access Server. You cannot post data into your Asset Database via this connection type.

If you attempt to write, expect this error:

Msg 7390, Level 16, State 2, Line 35 The requested operation could not be performed because OLE DB provider “PISQLClient” for linked server “PISERVER_TEST” does not support the required transaction interface.

Outcomes