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

Version 1

    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'