Learn the Basics of PI Programming - Serving Data with PI SQL Data Access Technologies

Document created by aduhig Employee on Feb 19, 2015
Version 1Show Document
  • View in full screen mode

Exercise Objectives:

  • Become more familiar with the different tables available in PI SQL Data Access Technologies
  • Learn how the tables interact with each other
  • Join PI SQL tables together

 

Approach:

We are tasked with writing a query to grab real time fuel gas flows for all assets in an enterprise for use on a reporting system. Each reading will have to have contextual information to go along with it, including the location of the asset, name of asset, and description of asset. We also need to know the current value of the reading, as well as when it was last updated. We’ll go through gradually, adding more and more complexity to our query as we go.

 

This exercise was originally written with PI OLEDB Enterprise, but can also be done with PI JDBC Driver or PI OLEDB Driver. The SQL queries in the solution will be the same. Before completing the exercise, you will need to import the database yourself, however if you are in an organised lab, this will be done for you. For details on importing the database, see the attachments to this document.

 

Part 1: A Table for One, Please

Write a query (or edit one of the above selected pre-defined queries) to return all the names and descriptions of all elements in the “OSIsoft Enterprises” database – not just those at the region level. You’ll have to look through the table columns of each table, but you should be able to do this without any table joins. Execute the query to ensure you have the correct solution.

 

Expected result:

Name

Description

Wichita

The Wichita Plant

 

Solution:

We need to use the [OSIsoft Enterprises].[Element] table for this query, it has both the element Name and Description columns (below in red).

2015-02-20 12_35_02-MasterPI on ADUHIG-E4310 - Virtual Machine Connection.png

Our select statement will be:

SELECT e.Name, e.Description

FROM [OSIsoft Enterprises].[Asset].[Element] e

 

Part 2: A Table for Two, Please

Add to your previous query, making the results show the names, descriptions, and paths to the parents of all elements in the database. You will need to join two of the above mentioned tables on corresponding GUIDs, using some of the ID related columns. Generally speaking, if you want to do anything using PI OLEDB Enterprise – you’re going to have to join at least two tables together.

 

Expected result:

Path

Name

Description

\

Houston

The Houston Plant

 

Solution:

We need the Name and Description columns from the Element table just like before, but we also need the "Path" column from the ElementHierarchy table. (highlighted below in red). Therefore we need to join the [OSIsoft Enterprises].[Element] table with the [OSIsoft Enterprises].[ElementHierarchy] tables. We find two compatible GUID columns, in this case the Element.ID and ElementHiererchy.ElementID columns (Join shown below in Grey).

2015-02-20 11_01_37-MasterPI on ADUHIG-E4310 - Virtual Machine Connection.png

Our select statement will be:

SELECT eh.Path, e.Name, e.Description

FROM [OSIsoft Enterprises].[Asset].[Element] e

INNER JOIN [OSIsoft Enterprises].[Asset].[ElementHierarchy] eh ON e.ID = eh.ElementID

 

Part 3: A Table for Three, Please

Write a query to return the “Fuel Gas Flow” attributes of all elements in the hierarchy, including the Attribute names, owning element, parent of owning element, and the element’s description.

 

Expected result:

Element Parent

Element Name

Element Description

Attribute Name

\Tucson\Cracking Process

Boiler-309

Cracking Boiler B-309

Fuel Gas Flow

 

Solution:

In addition to the above "Part 2" solution, we also require the name of all element attributes that are "Fuel Gas Flow" attributes. Therefore, we need a table that tells us the names of element attributes. This is the ElementAttribute table (below in red). We need to join this table to the element table on the corresponding GUIDs. The columns that match are ElementAttribute.ElementID, and Element.ID. We also need to limit the result set to only give us 'Fuel Gas Flow' attributes, and rename our columns to be more user friendly.

2015-02-20 11_01_37-MasterPI on ADUHIG-E4310 - Virtual Machine Connection.png

Our select statement will be:

SELECT eh.path as [Element Parent], e.name as [Element Name], e.description as [Element Description], ea.name as [Attribute Name]

FROM [OSIsoft Enterprises].[Asset].[Element] e

INNER JOIN [OSIsoft Enterprises].[Asset].[ElementHierarchy] eh ON eh.ElementID = e.ID

INNER JOIN [OSIsoft Enterprises].[Asset].[Elementattribute] ea ON ea.ElementID = e.ID

WHERE ea.name = 'Fuel Gas Flow'

 

Part 4: A Table for Four, Please

Write a query to get the data according to our original requirements:

 

"We are tasked with writing a query to grab real time fuel gas flows for all assets in the enterprise for use on a reporting system. Each reading will have to have contextual information to go along with it, including the location of the asset, name of asset, and description of asset. We also need to know the current value of the reading, as well as when it was last updated."

 

Expected result:

Element Parent

Element Name

Element Description

Attribute Name

Time

Value

\Tucson\Cracking Process

Boiler-309

Cracking Boiler B-309

Fuel Gas Flow

2015-04…

66.4…

 

Solution:

In addition to the "Part 3" solution, we need to join the Data.Snapshot table for its Time and Value columns (below in red). The Snapshot table will be joined on the GUIDs its contents share with the ElementAttribute table. We'll join on the Snapshot.ElementAttributeID and ElementAttibute.ID columns (below in grey).

2015-02-20 11_01_37-MasterPI on ADUHIG-E4310 - Virtual Machine Connection.png

Our select statement will be:

SELECT eh.path as [Element Parent], e.name as [Element Name], e.description as [Element Description], ea.name as [Attribute Name], s.Time, s.Value

FROM [OSIsoft Enterprises].[Asset].[Element] e

INNER JOIN [OSIsoft Enterprises].[Asset].[ElementHierarchy] eh ON eh.ElementID = e.ID

INNER JOIN [OSIsoft Enterprises].[Asset].[Elementattribute] ea ON ea.ElementID = e.ID

INNER JOIN [OSIsoft Enterprises].[Data].[Snapshot] s ON ea.id = s.ElementAttributeID

WHERE ea.name = 'Fuel Gas Flow'

Attachments

Outcomes