Skip navigation
All Places > PI Developers Club > Blog > 2019 > February
2019

Dear Fellow PI Geeks,

 

It is with a heavy heart that I must announce that we have cancelled this year's PI World Innovation Hackathon.  It was not an easy decision.  We were given a cutoff date 8 weeks before PI World to make a decision to go forward or cancel.  It's hard to make firm predictions so far in advance.  While we were confident optimistic we could have a similar number of participants as last year, the tipping point in the decision was the bitter reality that the hackathon has been shrinking in attendance for many years.  When we started out on this brave new venture 7 years ago, we obviously were filled with hopes that it would grow rather than decline.

 

I have received emails asking if we will offer hackathons in the future.  YES, we will.  However, I do not see us offering hackathons during PI World.  Many of our partners and EA customers form the core of the developer community, but PI World has so many demands pulling those partners and customers in so many different directions.  Thus, we are currently considering a special hackathon-only event.

 

I will be hanging out at the Parc 55 hotel on Day 2 and Day 3, where the agenda has lots of developer related offerings from 90 minute Tech Talks, some cool PI Geek talks, and the traditional 45 minute Developer Talks.  Not to mention the Day 3 hands on labs!  So I invite all developers to come over to the Parc 55 to attend some in-depth talks. And if you happen to bump into me, I would love to talk to you about what you want to see in future hackathons.

 

There will be a Developer Reception from 04:30-06:30 PM at the Parc 55 with drinks and appetizers.  Come meet fellow developers or supporting members of the PI Developers Club.

 

If you have any concerns or comments, please email me and/or the entire team below.

 

Rick Davin    

rdavin@osisoft.com OR TechnologyEnablementTeam@osisoft.com

Last week we met with Viten's Data Science team and they showed us this amazing Custom Data Reference that they've built for Dynamic Water Demand Forecasts.

They're using it for leak detection, but I'm sure the same could be applied to other use cases and other industries as well.

What's even more interesting is that they've been kind enough to upload the material in GitHub!

There is also a youtube video showing the installation, configuration and running DBM and DBM data reference.

 

DBM.jpg

 

Many thanks to Johan Fitié and the rest of Vitens' Data Science team for making this available and here are also a few comments from them:

 

Water company Vitens has created a demonstration site called the Vitens Innovation Playground (VIP), in which new technologies and methodologies are developed, tested, and demonstrated. The projects conducted in the demonstration site can be categorized into one of four themes: energy optimization, real-time leak detection, online water quality monitoring, and customer interaction. In the real-time leak detection theme, a method for leak detection based on statistical demand forecasting was developed.

 

Using historical demand patterns and statistical methods - such as median absolute deviation, linear regression, sample variance, and exponential moving averages - real-time values can be compared to a forecast demand pattern and checked to be within calculated bandwidths. The method was implemented in Vitens' realtime data historian, continuously comparing measured demand values to be within operational bounds.

 

One of the advantages of this method is that it doesn't require manual configuration or training sets. Next to leak detection, unmeasured supply between areas and unscheduled plant shutdowns were also detected. The method was found to be such a success within the company, that it was implemented in an operational dashboard and is now used in day-to-day operations.

 

The software is available as free software under the GPLv3 license;

 

This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.

 

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

 

You should have received a copy of the GNU General Public License along with this program. If not, see http://www.gnu.org/licenses/

 

For more information, please take a look at https://github.com/Vitens/DBM

In this post I will be leveraging OSISoft's PI Web API to extract PI System Data to a flat file.

To keep things simple and easy to reproduce, this post will focus how to extract data with this technology.

 

Prerequisites:

 

Remote PI System:

PI Data Archive 2018

PI AF Server 2018

 

Client:

Python 3.7.2

 

For simplicity 7 days of data of solely the PIPoint "Sinusoid" will be queried and written to a .txt file.

 

In order to retrieve data for a certain PI Point we need the WebID as reference. It can be retrieved by the built-in search of PI Web API.

In this case the WebID can be found here:

 

 

 

Given the WebID of the PI Point "Sinusoid", the following code will request historical data for the previous 7 days. It will parse the response JSON package, and write "Timestamp, Value, isGood" to the datafile specified.

 

Python Code:

import requests
import json
url = "https://<piwebapi_endpoint>/piwebapi/streams/<WebID_of_Sinusoid>/recorded?startTime=*-7d&endTime=*&boundaryType=Inside&maxCount=150000" #maxCount will set upper limit of values to be returned
filepath = "<filepath>"
response = requests.get(str(url), auth=('<user>', '<password>'), verify=False) #verify=False will disable the certificate verification check
json_data = response.json()
timestamp = []
value = []
isGood = []
#Parsing Json response
for j_object in json_data["Items"]:
 timestamp.append(j_object["Timestamp"])
 value.append(j_object["Value"])
 isGood.append(j_object["Good"])

event_array = zip(timestamp, value, isGood)
#Writing to file
with open(str(filepath), "w") as f:
 for item in event_array:

 try:
 writestring = "Timestamp: " + str(item[0]) + " , Value: " + str(item[1]) + " , isGood: " + str(item[2]) + " \n"

 except:

 try:
 writestring = "" + str(item[0]) + " \n"
 except:
 writestring = "" + " \n"

 f.write(writestring)
 f.close()

(*intendation not correctly displayed)

 

Result:

 

Timestamp, value and the quality for this time range were successfully written to the file.

In this post I will be leveraging OSISoft's AFSDK to extract PI System Data to a flat file.

To keep things simple and easy to reproduce, this post will focus how to extract data with this technology.

 

Prerequisites:

PI Data Archive 2018

PI AF Server 2018

PI AF Client 2018 SP1

Microsoft Visual Studio 2017

 

For simplicity 7 days of data of solely the PIPoint "Sinusoid" will be queried and written to a .txt file.

 

Following code will establish a AFSDK connection to the default PI Data Archive Server specified in the local Known Servers Table. A query for PI Points is launched to find the PIPoint "Sinusoid".

The method PIPoint.Recordedvalues is used to retrieve a list of AFValues. Their properties "Timestamp, Value, IsGood" are then written to a flat file.

 

C# Code:

namespace Data_Access_AFSDK
{
class Program
{
static void Main(string[] args)
{
PIServer myPIserver = null;
string tagMask = "";
string startTime = "";
string endTime = "";
string fltrExp = "";
bool filtered = true;

//connection to PI server
if (myPIserver == null)
myPIserver = new PIServers().DefaultPIServer;

//Query for PI Point
tagMask = "Sinusoid";
List<PIPointQuery> ptQuery = new List<PIPointQuery>();
ptQuery.Add(new PIPointQuery("tag", AFSearchOperator.Equal, tagMask));
PIPointList myPointList = new PIPointList(PIPoint.FindPIPoints(myPIserver, ptQuery));

startTime = "*-7d";
endTime = "*";

//Retrieve events using PIPointList.RecordedValues into list 'myAFvalues'
List<AFValues> myAFvalues = myPointList.RecordedValues(new AFTimeRange(startTime, endTime), AFBoundaryType.Inside, fltrExp, filtered, new PIPagingConfiguration(PIPageType.EventCount, 10000)).ToList();

//Convert to PIValues to string[]
string[] query_result_string_timestamp_value = new string[myAFvalues[0].Count];
string value_to_write;
string quality_value;
int i = 0;

foreach (AFValue query_event in myAFvalues[0])
{
value_to_write = query_event.Value.ToString();
quality_value = query_event.IsGood.ToString();
query_result_string_timestamp_value[i] = "Timestamp: " + query_event.Timestamp.LocalTime + ", " + "Value: " + value_to_write + ", " + "IsGood: " + quality_value;
i += 1;
}
//Writing data into file
System.IO.File.WriteAllLines(@"<FilePath>", query_result_string_timestamp_value);
}
}
}

 

 

Result:

Timestamp, value and the quality for this timerange were successfully written to the file.

In this post I will be leveraging OSISoft's PI SQL Client OLEDB to extract PI System Data via the PI SQL Data Access Server (RTQP).

To keep things simple and easy to reproduce, this post will focus how to extract data with this technology.

 

Prerequisites:

PI SQL Client OLEDB 2018

PI Data Archive 2018

PI AF Server 2018

PI SQL Data Access Server (RTQP Engine) 2018

Microsoft Visual Studio 2017

 

For simplicity a test AF database "testDB" with a single element "Element1" which has a single attribute "Attribute1" was created.

This attribute references the PIPoint "Sinusoid".

 

 

SQL Query used to extract 7 days of events from \\<AFServer>\testDB\Element1|Attribute1

 

SELECT av.Value, av.TimeStamp, av.IsValueGood
FROM [Master].[Element].[Archive] av
INNER JOIN [Master].[Element].[Attribute] ea ON av.AttributeID = ea.ID
WHERE ea.Element = 'Element1' AND ea.Name = 'Attribute1'
AND av.TimeStamp BETWEEN N't-7d' AND N't'

 

Example C# Code:

 

using System;
using System.Data;
using System.Data.OleDb;
using System.IO;


namespace Data_Access_PI_SQL_Client_OLEDB
{
class Program
{
static void Main(string[] args)
{
DataTable dataTable = new DataTable();
using (var connection = new OleDbConnection())
using (var command = connection.CreateCommand())
{
connection.ConnectionString = "Provider=PISQLClient; Data Source=<AFServer>\\<AF_DB>; Integrated Security=SSPI;";
connection.Open();


string SQL_query = "SELECT av.Value, av.TimeStamp, av.IsValueGood ";
SQL_query += "FROM [Master].[Element].[Archive] av ";
SQL_query += "INNER JOIN [Master].[Element].[Attribute] ea ON av.AttributeID = ea.ID ";
SQL_query += "WHERE ea.Element = 'Element1' AND ea.Name = 'Attribute1' ";
SQL_query += "AND av.TimeStamp BETWEEN N't-7d' AND N't' ";


command.CommandText = SQL_query;
var reader = command.ExecuteReader();
using (StreamWriter writer = new StreamWriter("<outputfilepath>"))
{
while (reader.Read())
{
writer.WriteLine("Timestamp: {0}, Value : {1}, isGood : {2}",
reader["Timestamp"], reader["Value"], reader["IsValueGood"]);
}
}
}

Console.WriteLine("Completed Successfully!");
Console.ReadKey();
}
}
}

 

 

Result:

Events were successfully written to flat file:

 

Great list, though the author admits the list is more exhausting than it is exhaustive.  A must-read for VB.NET developers.

 

An Exhausting List of Differences Between VB.NET & C# | Anthony's blog

Filter Blog

By date: By tag: