Skip navigation
All Places > PI Developers Club > Blog > 2012 > April
2012

Hi everyone,

 

I have decided follow up on my previous post on test PI Web Services using a Powershell script but by using this time the proxy library generated using the metadata returned by the Web Service itself.

 

If you want to test PI Web Services using other bindings or with more complex parameters, you generally need to write a small application using Visual Studio. As many components are involved during the installation such as configuring the virtual application on the Web Server (IIS), ASP.NET, application pool, web.config, PI SDK, AF SDK, security, etc. or also, other elements can come into the game: questions related to Active Directory forest, domain(s) and workgroup, and the network routes and filters, many things can go wrong. Your testing application cannot be tailored for a "one size fit all" one. It means you will need to re-configure the class(es) needed for communicating with the Web Service and modify the app.config file, and then you will rebuild your application. These steps can be tedious on the long run. I want to propose you an alternative to this by writing your Powershell script which is quick to edit.

 

You will need Powershell version 2.0 and later to achieve this as we are communicating with Web Services implementing the Windows Communication Foundation (WCF) which was introduced with the .NET Framework 3.5 that is supported with version 2.0. You will also need the C# compiler (csc.exe) installed under your C:\Windows\Microsoft.NET\Framework\v3.5 or C:\Windows\Microsoft.NET\Framework64\v3.5 and the SVCUtil.exe tool which is installed with Visual Studio. If you don`t have Visual Studio installed on the machine you planned to work on, you can easily copy the files under any folder you want. You need to copy the SVCUtil.exe and SVCUtil.exe.config files. Don`t forget to change the path where these files are copied if there are not at their default location. If you use the SVCUtil.exe from the .NET Framework 4.0, you will need to create or modify the powershell.exe.config file under the C:\Windows\System32\WindowsPowerShell\v1.0 folder with the following settings:

 

 

By default Powershell is configured to only use .NET Framework 2.0 assemblies, so you need to enable the use of 4.0 ones.

 

Let`s examine the script in more details... This script contains three (3) functions to be used:

  • PIWS-Https-TestGetPIArchiveData
  • PIWS-NetTCP-TestGetPIArchiveData
  • PIWS-RawXMLSOAP-TestGetPIArchiveData

The first two: PIWS-Https-TestGetPIArchiveData and PIWS-NetTCP-TestGetPIArchiveData make use of a proxy DLL which is dynamically compiled and added to the script to send request to PI Web Services, the last one is the function I introduced in my last blog entry regarding manually creating a SOAP/XML message to invoke PI Web Services methods.

 

Let start by explaining the logic of this script for functions: PIWS-Https-TestGetPIArchiveData and PIWS-NetTCP-TestGetPIArchiveData. First, you need to generate a proxy DLL representing the classes used by the Web Service, followed by loading it into Powershell namespace using the principle of reflection. You will need to create an instance of a class representing one of the services offered by PI Web Services (either PITimeSeries or PISearch); thereafter this instance is called your proxy object. It is important to configure the impersonation level used by the Powershell script.

 
#Allow delegation to occur.
$_Proxy.ClientCredentials.Windows.AllowedImpersonationLevel = [System.Security.Principal.TokenImpersonationLevel]::Delegation

In many cases, you will use a delegation level to enable your credentials to be passed between two machines. Finally, you will manipulate your proxy object and some classes to launch request against the different Web methods

 

Generating a Proxy
This step consists of calling the SVCUtil.exe command with the endpoint to generate two (2) files which will contain class definitions and the default app.config. Both files will be copied in the same folder of your Powershell script. Secondly, the CSC.exe command is called with the classes definition file to compile into an assembly file (*.dll). The AnyCPU platform is selected for compilation. If you are interested to know what command lines are invoked, I suggest you take a look in the script file attached at the end of this blog entry.

 

 

 

Loading the Generated Assembly
This step consists of loading the generated assembly file (*.dll), and two (2) other ones in the Powershell session namespace by the use of Reflection.Assembly.LoadFrom method. The System.ServiceModel and System.Security.Principal namespaces are loaded for their usage in the script. If you want to make use of the .config file generated, you have to invoke the CurrentDomain.SetData method with the path of the file before calling the Reflection.AssemblyLoadFrom method, otherwise it won`t be taken into account.

 
#Add the configuration file into the domain before loading the assembly if using default...
if ($Configuration -eq "default") { [System.AppDomain]::CurrentDomain.SetData("APP_CONFIG_FILE", $WSLibPath + ".config") }
#Add a reference to the PISDK assembly.
[reflection.assembly]::LoadFrom($WSLibPath) | Out-Null
#Add a reference to the servicemodel.
[Reflection.Assembly]::LoadWithPartialName("System.ServiceModel") | Out-Null
#Add a reference to ...
[Reflection.Assembly]::LoadWithPartialName("System.Security.Principal") | Out-Null

Instantiating
This step consists of creating a proxy client object representing your connection with the Web Service. You can invoke one of its constructors that takes the name of the endpoint as argument. The other allows defining a custom configuration that you can control programmatically.

 

 

 

Build your Request
This step looks like what you would do if you were using Visual Studio. You create your requests and send them to the GetPIArchiveData method which will return an array of TimeSeries object that you can manipulate.

 

 

 

To utilize your script, you will need to open a command prompt and call powershell or call powershell directly from the Run command. As a very important step, you will need to unlock the policy for using this type of script by invoking this command: Set-ExecutionPolicy RemoteSigned at the Powershell prompt. Afterward, you can invoke your script wherever you saved it by simply typing its full path and name. If you receive errors directly inside the Powershell session, it means something is not configured properly somewhere. Otherwise in case of success you will receive a screen that looks like the following.

 

If you are using PI Web Services 2010 R2 or R3 installed on IIS and using the Net TCP binding, the default port used is 808. You will need to specify the port in the URI of the Web Service.

 

 

If you are interested you can also test other bindings such as the name pipes. Although, it works only within the boundary of a unique machine. Try different paths involving PI Data Archive, PI AF or PI Performance Equation.

 

Please find a copy of the script here. Share some thoughts or comments with me by replying to my blog.

 

 

 

 

jlakumb

PI Server 2012 at UC 2012

Posted by jlakumb Apr 20, 2012

This year is very exciting for the PI Server team as we are getting ready to unveil the latest PI Server. During this year’s Users Conference 2012 in San Francisco we will be announcing PI Server 2012.

 

 

 

PI Server 2012 offers significant enhancements in performance, scalability, reliability and manageability while utilizing system resources better than ever before and this year’s Users Conference is the place for you to learn more about all these enhancements. Here’s where:

 

 

 

·         The Partner Expo, on Monday April 23rd, is where you can find some of our early adopters like SISCO and Rockwell. These Technology Adoption Program (TAP) participants have already the power of PI Server 2012 and they are ready to share it with you.

 

·         During the General Session keynote, you’ll discover how the PI Server is the foundation of the PI System’s evolution. The OSIsoft Engineering Leadership Team will walk us through cutting edge demos to showcase the performance and scalability of PI Server 2012.

 

·         During the product talks on Day 1 and 2 you’ll find different sessions showing what’s new with PI Server 2012. You’ll get a chance see it live in both the PI Server 2012 breakout session, as well as the PI Server 2012 kiosk during the Product Expo.

 

·         We’ll continue our discussion on configuration, visualization, asset centricity and the powerful analytic tools in other breakout sessions. Don’t miss your chance to learn how PI System Calculations can help you operate, maintain and improve the performance of your processes. Also, if you need to know when certain events occur—even when you are not at your desk—see how the PI System can provide this information using PI Notifications.

 

·         During these same sessions, we’ll also introduce new capabilities like organizing and visualizing your data by business events with PI Event Frames through the PI Data Access products.

 

·         And last, but not least, the Product Education Day will help you prepare your existing environment for the upgrade to PI Server 2012, so be certain not to miss this one!

 

 

 

We hope to see you at all the PI Server 2012 related talks and don’t be shy, we want to hear your questions and impressions! So register to the OSIsoft’s UC 2012, attend the presentations, and raise your hand or send us an email at piserver2012@osisoft.com.

 

 

 

PI Server 2012 Team

The title and timing of this post are inspired by Ahmad's excellent series of posts on PI Data and R. Ahmad shows an implementation of R hosted in Excel (RExcel) and he uses PI DataLink to pull in data. The simplicity is brilliant. What, though, about automating calculations like that? 

 

The first place I turn for automating PI stuff is PI ACE, since the scheduling/triggering, buffering, graceful degradation, failover, management, and contextualization are all taken care of out-of-the-box. The downside for casual coders is that writing ACE code requires a full copy of Visual Studio, but since we're here on vCampus, I'll assume that won't be a problem for this audience! Not too long ago, I worked up a scenario exploring the use of an external math library within ACE. The library I chose was Alglib, but the beta of Math.NET also caught my eye. There's even an R wrapper that looks interesting though there's a caveat about multiple instances of that particular implementation. Anyway - choices abound - but I've already got some code and screenshots showing Alglib so I'll stick with that here. Also, because ACE is a creature of the PISDK (many of its methods return PISDK data types, such as PIValues and PITime) we kill two birds with one stone if we look at some of the tricks involved with using the PI SDK + ACE + external library.

 

Imagine the case of pump performance: the control system has a calculation for the desired flow through the pumps, and a flow meter tells us the actual pump flow. So - how closely does actual flow correspond to desired flow? One indicator might be the correlation coefficient of the data - also known as the r-value. 

 

Low correlation:

 

1055.lowCorrelation.png

 

...versus higher correlation:

 

6646.highCorrelation.png

 

 

Example: Setting up a complex statistics calculation in PI ACE

We will look at a similar use case, calculating the correlation between temperature and concentration in a bioreactor. Creating an ACE calculation with these inputs/outputs will look something like:

 

0714.aceWizard.png

 

 

 

As mentioned before, I hunted around for a VB-wrapped library which could calculate Pearson correlation coefficient and came up with Alglib. I believe Alglib is targeted to .NET Framework 2, but that's always something to check - it's easy to forget and ACE can give some cryptic errors when you're referencing an assembly targeting a higher .NET framework version. In this blog post, I use .NET 3.5 because there are some features we'll be using, such as LINQ and Lambda expressions. 

 

5277.aceTargetFrameworkDropdown.png

 

 

 

After adding references to Alglib, OSIsoft.PISDK, OSIsoft.PISDKCommon, and OSIsoft.PITimeServer (which we'll need by the time this adventure is done), we're ready to dig in. Alglib's correlation method has a rather straightforward signature (copied from the C# documentation)...

 

 

 
double pearsoncorr2(double[] x, double[] y)

 

 

The inputs are vectors of corresponding value pairs: for us, expectedFlowValues[] and the corresponding actualFlowValues[].

 

 

Retrieving PI archive values the simple way from PI ACE 

We'll start by getting values for concentration using built-in functionality from PI ACE:

 
        'Get values for Concentration
        Dim concPIValuesCOM As PIValues = BA_Conc_1.Values("*-1h", "*", BoundaryTypeConstants.btInside)

 

 

Notice the PIACEPoint.Values method returns a PISDK datatype (full of more PISDK datatypes like PISDK.PIValue and PITimeServer.PITime), so we're already seeing direct involvement with the PISDK. Not a bad thing, but the PIValues collection is not truly IEnumerable, so the first thing we'll do is a copy operation to get ourselves into the world of enumerables. For those of you not native to .NET, the IEnumerable collection is the foundation of a wealth of extensions and time-saving functionality. Other interfaces inherit from IEnumerable - Lists, for example, are heirs of IEnumerable but which are ordered and indexed.

 
        'Native .NET data structures to hold PI value query results
        Dim concPIValues As List(Of PIValue) = New List(Of PIValue)

        'We would like to enumerate/filter the PIValues, but
        'PISDK.PIValues only likes enumerating in ForEach loops.
        'Other attempts at using this as an IEnumerable are futile.
        'So here, we manually add the contents to a .NET List(Of PIValue)
        For Each v As PIValue In concPIValuesCOM
            concPIValues.Add(v)
        Next

 

 

Recall the correlation inputs needed - concentration values and corresponding temperature values. So, we will interpolate the temperature values for each concentration value's timestamp. Our first use of that List(of PIValue) will be to grab those Concentration timestamps by themselves. Instead of writing a for-each loop just to extract an array of timestamps from the value objects, the LINQ Select method does this in one line:

 
'Get timestamps from the Concentration values, at which we wish to interpolate Temperature values
Dim concTimes = From v In concPIValues Select (v.TimeStamp)

 

Retrieving interpolated values using the PI SDK directly

The built-in PI ACE function for getting raw archive values from concentration was nicely simple, but now we need to do the temperature interpolation. Because the PIACEPoint object is a simpler, friendlier subset of the raw PISDK.PIPoint, we'll need to switch over to the latter for getting interpolated values. The same PIValues enumerable caveat holds, so we'll copy these interpolated values to an enumerable List too.

 

Module/class-level properties to hold the PISDK object and the PIPoint object:

 
    Private PISDKroot As PISDK.PISDK
    Private BA_Temp_1_PIPoint As PIPoint

 

 

During initialization, find the raw PISDK PIPoint underneath the PIACEPoint wrapper:

 
        'Get PIPoint object corresponding to Temperature PIACEPoint 
        PISDKroot = New PISDK.PISDK
        BA_Temp_1_PIPoint = PISDKroot.Servers(BA_Temp_1.Server).PIPoints(BA_Temp_1.Tag)

 

 

During evaluation, make a PISDK data call to the raw PIPoint:

 
        'Get interpolated temperature at each concentration value timestamp
        Dim tempPIValuesCOM As PIValues = BA_Temp_1_PIPoint.Data.TimedValues(concTimes.ToArray())

        'Native .NET data structures to hold PI value query results
        Dim tempPIValues As List(Of PIValue) = New List(Of PIValue)

        'Add to list
        For Each v As PIValue In tempPIValuesCOM
            tempPIValues.Add(v)
        Next

 

 

By default, PI ACE is courteous and adjusts for clock drift between server and client. In contrast, the PISDK by default does not adjust for clock drift. Because in this case we end up using the timestamps only for matching temperature and concentration values, I don't care if the values end up as server time or client time - just as long as they're uniform. The quickest path around this is to disable clock offset adjustment for concentration in PI ACE:

 

 

 
'Disable clock offset adjustment for PI ACE point wrapper
BA_Conc_1.AdjustClockOffset = False

 

Filtering PI Values with LINQ

Now that we have values from PI for temperature and concentration, at matching timestamps, we're ready to go, right? No! It turns out Alglib isn't very happy if any of the data is bad, if we even get past the process of casting the values as double (the desired datatype). So we need to filter the data down to the pairs when values where temperature and pressure are both good.

 

Lambda expressions (someone's name for inline anonymous functions) allow us to define ad-hoc functions with minimal extra code. When doing something simple like filtering a set based on some conditions, this is invaluable. The VB incarnation of Lambdas is a bit restricted versus the full C# implementation, and not as widely documented, but there's a good introductory blog post and Microsoft's bank of LINQ examples is a good implicit introduction. Below, we're using some friendly enough syntax to filter the temperature/concentration values collections to only the items where both temperature and concentration have a valid value, i.e. when IsGood(). 

 

 

 
        'Filter out bad values
        Dim goodTempPIValues = tempPIValues.Where(Function(temp, i) temp.IsGood() And concPIValues(i).IsGood())
        Dim goodConcPIValues = concPIValues.Where(Function(conc, i) conc.IsGood() And tempPIValues(i).IsGood()) 

 

Calling the statistical correlation function

Okay, NOW we're ready - we have collections of synchronized, good PI values and can call Alglib's Pearson correlation function.

 

 

 
'Generate value arrays of ONLY the actual data values (float32) stripped out of their PIValue containers
Dim goodTempValues = goodTempPIValues.Select(Function(v) CDbl(v.Value)).ToArray()
Dim goodConcValues = goodConcPIValues.Select(Function(v) CDbl(v.Value)).ToArray()

'Call Alglib Pearson correlation function
Dim r As Double = alglib.pearsoncorr2(goodTempValues, goodConcValues)

'Write output value to PI
BA_Correlation_1.Value = r

 

 

There you have it - an external math library included in a scheduled PI ACE calculation. This example was contrived to demonstrate functionality, so isn't the soundest of engineering feats. We aren't doing any sort of weighting to the values, so the calculation is event weighted. We could have done our data retrievals as interpolations  for the same range and interval and thus we return to the realm of time weighted calculations. In calculations you create, don't underestimate the magnitude of effects which time vs. event weighting can have. And there are other ways to work the LINQ magic which might end up more efficient - perhaps zipping the temperature/concentration values side-by-side and then doing a single filtering pass. If anyone plays with this and has an improvement, please share in the comments!

 

 

Full code

 

 
Imports OSIsoft.PI.ACE
Imports PISDK
Imports PISDKCommon
Imports PITimeServer
Imports System.Linq
Imports System.Collections.Generic

'Project references:
'OSIsoft.PISDK.dll
'OSIsoft.PISDKCommon.dll
'OSIsoft.PITimeServer.dll
'alglibnet2.dll

Public Class Correlation
    Inherits PIACENetClassModule
    Private BA_Temp_1 As PIACEPoint
    Private BA_Correlation_1 As PIACEPoint
    Private BA_Conc_1 As PIACEPoint
    '
    '      Tag Name/VB Variable Name Correspondence Table
    ' Tag Name                                VB Variable Name
    ' ------------------------------------------------------------
    ' BA:Conc.1                               BA_Conc_1
    ' BA:Correlation.1                        BA_Correlation_1
    ' BA:Temp.1                               BA_Temp_1
    '

    Private PISDKroot As PISDK.PISDK
    Private BA_Temp_1_PIPoint As PIPoint

    Public Overrides Sub ACECalculations()

        'Native .NET data structures to hold PI value query results
        Dim concPIValues As List(Of PIValue) = New List(Of PIValue)
        Dim tempPIValues As List(Of PIValue) = New List(Of PIValue)

        'Get values for Concentration using friendly PI ACE functionality
        Dim concPIValuesCOM As IPIValues2 = BA_Conc_1.Values("*-1h", "*", BoundaryTypeConstants.btInside)

        'We would like to enumerate/filter the PIValues, but
        'PISDK.PIValues only likes enumerating in ForEach loops.
        'Other attempts at using this as an IEnumerable are futile.
        'So here, we manually add the contents to a .NET List(Of PIValue)
        For Each v As PIValue In concPIValuesCOM
            concPIValues.Add(v)
        Next

        'Use PI SDK directly to get interpolated temperature value at each concentration value timestamp
        Dim concTimes = From v In concPIValues Select (v.TimeStamp)
        Dim tempPIValuesCOM As IPIValues2 = BA_Temp_1_PIPoint.Data.TimedValues(concTimes.ToArray())

        'Add to list
        For Each v As PIValue In tempPIValuesCOM
            tempPIValues.Add(v)
        Next

        'Filter out bad values
        Dim goodTempPIValues = tempPIValues.Where(Function(temp, i) temp.IsGood() And concPIValues(i).IsGood())
        Dim goodConcPIValues = concPIValues.Where(Function(conc, i) conc.IsGood() And tempPIValues(i).IsGood())

        'Generate value arrays of ONLY the actual data values (float32) stripped out of their PIValue containers
        Dim goodTempValues = goodTempPIValues.Select(Function(v) CDbl(v.Value)).ToArray()
        Dim goodConcValues = goodConcPIValues.Select(Function(v) CDbl(v.Value)).ToArray()

        'Call Alglib Pearson correlation function
        Dim r As Double = alglib.pearsoncorr2(goodTempValues, goodConcValues)

        'Write output value to PI
        BA_Correlation_1.Value = r

    End Sub

    Protected Overrides Sub InitializePIACEPoints()
        BA_Conc_1 = GetPIACEPoint("BA_Conc_1")
        BA_Correlation_1 = GetPIACEPoint("BA_Correlation_1")
        BA_Temp_1 = GetPIACEPoint("BA_Temp_1")
    End Sub

    '
    ' User-written module dependent initialization code
    '
    Protected Overrides Sub ModuleDependentInitialization()

     'Disable clock offset adjustment for PI ACE point wrapper
        BA_Conc_1.AdjustClockOffset = False

        'Get PIPoint object corresponding to Temperature PIACEPoint
        PISDKroot = New PISDK.PISDK
        BA_Temp_1_PIPoint = PISDKroot.Servers(BA_Temp_1.Server).PIPoints(BA_Temp_1.Tag)

    End Sub

    '
    ' User-written module dependent termination code
    '
    Protected Overrides Sub ModuleDependentTermination()

        'Dispose of COM objects
        BA_Temp_1_PIPoint = Nothing
        PISDKroot = Nothing

    End Sub
End Class

 

Greetings, fellow geeks!  I hope to occasionally find time to write blog posts that are hopefully meaningful to others.  Like many other vCampus customers, I consider myself to be working deep in the trenches where we oftentimes must supplement the OSIsoft offerings with some of our own patchwork code to provide a more comprehensive solution, or even just a simple workaround to our own customers.  It’s in the spirit of finding such solutions that I would dedicate my own blogs.

 

 

 

My inaugural blog will cover the topic of time.  Or more so, the resolution of time as it may be on a client front-end and how that may differ from what might be coming from a backend PI server.  This topic may be particularly relevant to anyone needing to work with sub-second data.

 

 

 

A couple years back I had a gnarly 3-day bug hunt trying to find out why the AFTime value coming back from a PI Point didn’t exactly match the start time from a GetValues call.  Some info about my environment: I was testing on my 64-bit Windows 7 desktop against an AFAttribute using the PI Point data reference.  The PI Point was interpolated, i.e. not stepped.  I would set the end of the time range to AFTime.Now, and set the start to 8 hours before the end.  And for reasons that shall remain undisclosed, I was checking that the passed start time (now minus 8 hours) was exactly equal to the first returned timestamp for the PI Point over that time range.

 

 

 

I was quite surprised to find out that they weren’t exactly the same – when I thought they should be.  In fact, it took me over 2.5 days to find that much out.  The fact that they weren’t equal was slightly obscured as I initially dumped out the UtcSeconds for the timestamps.  What I first discovered was something like:

 

 

 

AFTime.Now.UtcSeconds      1334259544.15274

 

Returned.UtcSeconds        1334259544.15274

 

 

 

Hey, those look equal to me.  How about you?  But something still wasn’t right, so I had to dig deeper.  I decided to chop off the whole number portion of UtcSeconds and focus on the decimal portion.

 

 

 

AFTime.Now.UtcSeconds      1334259544.15274

 

              Whole        1334259544

 

              Decimal      0.152743101119995

 

 

 

Returned.UtcSeconds        1334259544.15274

 

              Whole        1334259544

 

              Decimal      0.152740240097046

 

 

 

Ahhhh, now my interest got peaked.  This times are indeed different staring in the 6th decimal place.  In this example, I show a difference of:

 

 

 

Delta  2.86102294921875E-06 of one second

 

 

 

I began to ponder just how could that be?  At first I wondered if that’s attributable to the difference between AFTime versus PITime.  But, no, it’s wasn’t  that.  It soon dawned on me that the problem was my development environment had a high resolution timer for when AFTime.Now is invoked.  And while a sub-second PI server can serve up sub-second data, that sub-second resolution will be in intervals of 65536 sub-second slices per second.  That was my problem: AFTime.Now had a finer sub-second resolution and wasn’t guaranteed to be on a ‘whole’ PI sub-second slice (if I have explained that adequately) and therefore differed from the returned PI server time’s UtcSeconds starting in the 6th decimal place.  I did mention gnarly, right?

 

 

 

For those of us who can live with whole second data, OSIsoft has 2 methods worth mentioning.  Ever since AF 2.3, the AFSDK offers both the AFTime.NowInWholeSeconds property, compliments of yours truly.  Not that I wrote it, but I did request it.  And there is also the AFTime.TruncateToWholeSeconds method.  These hopefully should satisfy many of you, except perhaps those in Electrical or Power industries where sub-second timings are critical.  For those, I offer a bit of code to compliment the TruncateToWholeSeconds method.

 

 

 

I call the extension method TruncateToPISubseconds where again I define a “PI server sub-second” as 1/65536th of a second.

 

 

 

C# Extension Method

 

 

 

using OSIsoft.AF.Time;

 

 

 

public static class SomeTimeExtensions

 

{

 

    public static AFTime TruncateToPISubseconds(this AFTime inputTime)

 

    {

 

        AFTime outputTime = new AFTime(inputTime);

 

        double Seconds = outputTime.UtcSeconds;

 

        double WholeSeconds = System.Math.Truncate(Seconds);

 

        if (Seconds != WholeSeconds)

 

        {

 

            const int SlicesPerSecond = 65536;

 

            double Slices = (Seconds - WholeSeconds) * SlicesPerSecond;

 

            double WholeSlices = System.Math.Truncate(Slices);

 

            if (Slices != WholeSlices)

 

            {

 

                double Subseconds = WholeSlices / SlicesPerSecond;

 

                outputTime = new AFTime(WholeSeconds + Subseconds);

 

            }

 

        }

 

        return outputTime;

 

    }

 

}

 

 

 

VB.NET Extension Method

 

 

 

Imports OSIsoft.AF.Time

 

 

 

Public Module SomeTimeExtensions

 

 

 

    <System.Runtime.CompilerServices.Extension()> _

 

    Public Function TruncateToPISubseconds(inputTime As AFTime) As AFTime

 

        Dim outputTime As New AFTime(inputTime)

 

        Dim Seconds As Double = outputTime.UtcSeconds

 

        Dim WholeSeconds As Double = System.Math.Truncate(Seconds)

 

        If Seconds <> WholeSeconds Then

 

            Const SlicesPerSecond As Integer = 65536

 

            Dim Slices As Double = (Seconds - WholeSeconds) * SlicesPerSecond

 

            Dim WholeSlices As Double = System.Math.Truncate(Slices)

 

            If Slices <> WholeSlices Then

 

                Dim Subseconds As Double = WholeSlices / SlicesPerSecond

 

                outputTime = New AFTime(WholeSeconds + Subseconds)

 

            End If

 

        End If

 

        Return outputTime

 

    End Function

 

 

 

End Module

 

 

 

In summary, there are 2 bits of advice that I give to every developer new to time series data:

 

 

 

First, is to never just use AFTime.Now as is, and especially not in a loop.  Since Now changes with every invocation, you run the risk that data returned is not at the same event horizon, even if they are mere milliseconds apart.  Rather you should freeze AFTime.Now into a variable, and use that variable’s value for the duration of your procedure.

 

 

 

And second, you should consider truncating AFTime.Now to an acceptable resolution as required by your application.  If your PI data is saved to whole seconds, then use one of the AFSDK methods to truncate Now to whole seconds.  If you are using sub-second PI, that is 1/65536th of a second data, you may find the above extension methods useful.  If you work with whole minutes, there’s nothing stopping you from writing your own extension method!

Following my previous posts (here and here) on integrating PI System with R, this post takes the story one step further by analyzing the data to find correlations between them. By doing this we make two major points; first, R is extremely powerful and efficient in analyzing large amounts of data and manipulating it statistically. You see from the code snippets that we get all we do here with about 10 lines of code! Second, we see the power of good graphics to interpret data. In fact, the same data set can reveal many different things if it is shown in different ways. Appropriate graphics make data talk.

 

We work with some real-world data here. In our San Leandro office, we collect outside temperature as well as the instantaneous power consumption level of the building in a PI Server. I exported the data (sampled at around 4pm once a day) over the past 1 year to a CSV file (using PI DataLink or the piconfig utility). Therefore, we have a CSV file with 3 columns: Timestamp, Power, Temperature. This file is attached for your reference. Note that there may be some data cleaning necessary before feeding it into the graphs.

 

0825.CSV-file-screenshot.jpg

 

The next step is to read the data into a "dataframe" object in R. You can think of a dataframe as a super table which R can do lots of things with it.

 
#Read the data from the CSV file
PowerTemp.df <- read.csv(file='C:\\Users\\afattahi\\Documents\\R\\Examples\\SL - Power - Temp - 1year - Cleaned.csv', header=TRUE)

 The "\" character happens to be the escape character. First step is to convert the power and temperature to numeric values (they are read as "factors" in R):

 
#Converting the Power and Temperature to numerical vectors
power.numeric <- as.double(as.vector(PowerTemp.df$Power))
temperature.numeric <- as.double(as.vector(PowerTemp.df$Temperature))

 Now let's plot power vs. time:

 
#Plot the power and temperature vs time as is
plot(PowerTemp.df$Time, power.numeric, xlab="Date", ylab="Power") 

4442.Raw-Power-Graph.jpeg

 

As we see the date objects are just imported as strings and there is no real date order and the x-axis looks really bad. To fix this, we convert the date into "Date" objects in R and redo the plot for temperature and power:

 
#Plot the power and temperature vs time as Date objects
plot(as.Date(PowerTemp.df$Time, format="%d-%b-%Y"), power.numeric, xlab="Date", ylab="Power")
plot(as.Date(PowerTemp.df$Time, format="%d-%b-%Y"), temperature.numeric, xlab="Date", ylab="Temperature")

6014.Raw-Temperature-Graph-_2D00_-Dates-enhanced.jpeg0842.Raw-Power-Graph-_2D00_-Dates-enhanced.jpeg 

 

As is intuitively expected as well, there seems to be some sort of correlation between the temperature and power consumption at the building. The higher the temperature the higher the power consumption due to AC at the building. To see this better lets plot power vs. temperature:

 
#Plot the correlation between temperature and power using plot and smoothScatter
plot(temperature.numeric, power.numeric, xlab="Outside temperature", ylab="Power consumption")

1362.Temperature-_2D00_-Power-_2D00_-Correlation-_2D00_-Plot.jpeg 

 

Now it is way more obvious that there is some strong correlation between the temperature and power demand. The only problem with this graph is that it can fail showing the density of points in crowded areas as dark points tend to overlap each other; two overlapping dark points look the same as 10 overlapping points. To fix this issue let's use smoothScatter:

 
smoothScatter(temperature.numeric, power.numeric, xlab="Outside temperature", ylab="Power consumption")

5141.Temperature-_2D00_-Power-_2D00_-Correlation-_2D00_-smoothScatter.jpeg 

 

Now it looks beautiful! Not only the correlatoin is obvious, but also it shows some bifurcation in behavior; there are two obviously separate branches. In other words, it shows that there should be other parameter(s) that the relationship is conditioned on. The intuition of the underlying problem tells us that the weekends should be much lighter on the power because the AC set points are adjusted to save power. To test this let's add the "day of the week" to our dataframe and plot power vs. day of the week:

 

 

 
#Get the week day out and plot power grouped by week day
PowerTemp.df <- transform(PowerTemp.df, Weekday=weekdays(as.Date(Time,format="%d-%b-%y"), abbreviate=TRUE))
qplot(PowerTemp.df$Weekday, power.numeric,  position=position_jitter(w=0, h=1), xlab="Day of the week", ylab="Power")

3426.Power-grouped-by-day-of-the-week.jpeg 

 

Now it clearly shows that Saturdays and Sundays behave differently than the workdays. These two days correspond to the lower branch in the correlation graphs above.

 

As we saw above, R provides mighty tools to analyze large amounts of data and produce actionable graphics out of them. Together with PI System they make a power house in turning data into action. There is a reason we spend so much resources to collect many many pieces of data.

 

 

 

 

My previous blog post was very nuts-and-bolts. What follows is a bit more... experimental.

 

 

 

 

 

What time did you last leave the office and commute home? Think about that timestamp for a minute. For me, the timestamp was 06-Apr-2012 14:48 local time. That’s this past Friday – Good Friday –at a scandalously early hour in the day to be escaping work. It was a beautiful Spring afternoon, with the sun out and roads empty.

 

 

 

But you knew most of that just by looking at the timestamp. Friday the sixth of April in the early afternoon. Got it. Though my PI Server has a different and much more succinct take on the situation:

 

1333741680, the same timestamp in Unix seconds. Try parsing that, human.

 

 

 

The descriptive facets of my escape time would be very useful when making direct comparisons of data. How does shift one compare to two and three? How does Q1 this year compare to Q1 of years past? Is efficiency any different during the middle of the week versus the days that touch the weekend? Powerful comparisons – which are already second-nature if you’re familiar with multidimensional Business intelligence.

 

 

 

For data cubes, the question becomes how to add this time intelligence to your data. Almost universally, this calls for a time dimension table. The old-guard Microsoft SQL Analysis Services will make you a time table, but at a maximum resolution of one day. This is usually not good enough for PI data, which we might be importing at hourly resolution. In the user-oriented Microsoft PowerPivot (integrated with Excel), we could get started by using Excel magic to make a column of timestamps. But what about a more universal solution – one which we coders could prepare for users? A solution which might be accessible directly via PI OLEDB Enterprise?

 

 

Generating a timestamp column in PI OLEDB Enterprise

I have a guerrilla approach to getting a column of timestamps from PI OLEDB Enterprise: create a dummy AF Attribute with a static integer value of 0, and interpolate it over a range. I hid my dummy Element in the Configuration database.

 

 

8203.af_5F00_interp_5F00_element.png 7802.af_5F00_interp_5F00_attribute.png

 

 
CREATE VIEW [NuGreen].[DataT].[TimeMonth1h]
AS
SELECT ir.[Time]
FROM Configuration.Asset.ElementHierarchy eh
INNER JOIN Configuration.Asset.ElementAttribute ea
ON ea.ElementID = eh.ElementID
CROSS APPLY Configuration.Data.InterpolateRange
     (
       ea.ID
     , 't-1mo'
     , 't+1d'
     , '1h'
     ) ir
WHERE eh.Name = 'NuGreen'
AND eh.Path = '\'
AND ea.Name = 'Zero'     

 

 

1854.oledbe_5F00_time_5F00_interpolation_5F00_results.png

 

 

 

Sure, this is redneck engineering, but I’d say it’s a cheap and effective method to generate timestamps dynamically – with the bonus that we can use the same PI time syntax as our data queries might use. We can now pull this view into PowerPivot or any other BI utility which gives us the ability to add and define calculated columns, such as Day of Month and Weekday:

 

 

 

 

 

2055.powerpivot_5F00_adding_5F00_time_5F00_dax.png

 

 

 

Yet this still seems inelegant. There’s no way to save the time-parsing syntax back into PI OLEDB Enterprise, so all of our work will be confined to – in this case – this one PowerPivot cube.

 

 

 

I’m a firm believer in the power of the custom AF Data Reference. One must bear in mind that AF is not a calculation engine, so data references shouldn’t be abused for expensive operations. But a cheap operation like parsing timestamps into time parts (month, weekday, etc.) is precisely what custom data references are good for. I present to you: the Time Parts Data Reference.

 

 

 


A step further: the Time Parts data reference

Continuing the example of my Good Friday escape from work, the Time Parts Data Reference would take the timestamp –  06-Apr-2012 14:48 – and return a specified “time part” such as the date, day name, day-of-month, and so on as its AF Value:

 

 

 

3056.af_5F00_timeparts_5F00_atts_5F00_detail.png

 

 

 

 

PI OLEDB Enterprise Data Transpose Functions and the Time Parts DR

The TimeParts element becomes useful if we make a PI OLEDB Enterprise data transpose function for it. Data transpose functions are created against AF element templates, so we’ll first convert the element to a template…

 

8203.af_5F00_timeparts_5F00_templateicon.png

 

… and be good to go. The desired transpose function is TransposeInterpolateRange:

 

 

 

5086.oledbe_5F00_createTransposeFunction.png

 

 

 

And now we can modify our existing view to query our transpose function, giving us a dynamically-generated time dimension table showing every single 'time part' for each timestamp:

 

 

 
ALTER VIEW [NuGreen].[DataT].[TimeMonth1h]
AS
SELECT tir.* FROM 
Configuration.Asset.ElementHierarchy eh
CROSS APPLY Configuration.DataT.TransposeInterpolateRange_TimePartsTemplate
     (
       eh.ElementID
     , 't-1mo'
     , 't+1d'
     , '1h'
     ) tir
WHERE eh.Name = 'TimeParts'
AND eh.Path = '\'

 

 

4532.oledbe_5F00_transpose_5F00_results.png

 

 

 

Of course, harkening back to my previous blog post, we could always split these views in the name of abstracting the time range and resolution from the element and transpose function providing the time parts.

 

 

 
CREATE VIEW [NuGreen].[DataT].[TimePartsFlex]
AS
SELECT tir.* FROM 
Configuration.Asset.ElementHierarchy eh
INNER JOIN Configuration.DataT.ft_TransposeInterpolateRange_TimePartsTemplate tir
ON eh.ElementID = tir.ElementID
WHERE eh.Name = 'TimeParts'
AND eh.Path = '\'

 

 

 

 
CREATE VIEW [NuGreen].[DataT].[TimeParts1mo1h]
AS
SELECT *
FROM [NuGreen].[DataT].[TimePartsFlex]
WHERE StartTime = 't-1mo'
AND EndTime = 't+1d'
AND TimeStep = '1h'

 

 

8372.oledbe_5F00_views_5F00_created.png

 

 

 

Now, in PowerPivot or any other BI package, end users have a predefined, dynamic time dimension table view waiting to be consumed. I like the simplicity: no other OLE source is required (such as a SQL Server where such a table could also be generated) nor are the end users required to write any queries themselves. A ready-to-use time intelligence table view falls right out of PI OLEDB Enterprise:

 

 

 

7750.powerpivot_5F00_transpose_5F00_import.png

 

 

 

Demonstration code for the Time Parts data reference and the XML export of the TimeParts AF Element Template are attached. I welcome anyone who enhances it to post a comment about what you did.

March Madness is a wrap, did your picks do well? You can consider the Pwn2Own competition at CanSecWest as a cyber security version of March Madness.

 

In continuation of a global trend, this year signaled a change in the 'sport of hacking'.  Move over undergrads. Pwn2Own has become a professional contest. It was Vupen's dedicated exploit team versus Google's Chrome security team (both declared victory but Vupen's story won better news coverage).

 

So yes, cyber security is a team sport.  It is complete with talented athletes, coaches, and trainers. Let's not forget the fans, institutions, regulators, media and the rest of the eco system.   Do you have PI System security superstars on your team?

 

I'm very pleased to call out a strong cyber security line up for User Conference 2012:

 

Day Zero

 

1:45 PM - ISA 99 Workshop sponsored by WBF.  Learn about the ISA 99 standard approach for cyber security.

 

Graham Speake (Yokogawa), Joel Langill (SCADAhacker)

 

Day 1

 

12:45 PM - Product Expo PI System Security Booth

 

"Open topics like: Architecture, Firewalls, Compliance, Windows Server Core, Services"

 

Bryan Owen, David Casazza, Gary Seifert, Jim Davidson, John Stawiarski, Martin Bryant

 

3:55 PM - "Have you done enough with Cyber Security?" (vCampus Live! 2011 encore presentation)

 

Bryan Owen (OSIsoft), Joel Langill (SCADAhacker)

 

Day 2

 

9:40 AM - "Secure, Manageable Application Integration at Detroit Water and Sewerage Department"

 

Biren Saparia (Detroit Water) and Andrew Ginter (Waterfall Security Solutions)

 

5:00 PM - Keynote closing panel  "Data-Driven Decision Making"

 

Panelist Marty Edwards, DHS Control System Cyber Security Program Director

 

Product Evaluation Day

 

8:30 AM - PI System Security Workshop

 

Jed Haile (Idaho National Lab) with Anthony Tang, Dario Amiri, and Omar Shafie (OSIsoft)

 

Panel from the field: What works, what's challenging, and what can be done to save time and effort.

 

Panelists (TBA)

 

In summary, OSIsoft User Conference 2012 is the place to be if you are charged with cyber security for the PI System.  We will make a best effort to share these materials with those who can't attend but contributing in person is the way to get the most benefit from these highly professional resources.

 

If you are a vCampus member but aren't the 'security guru' - please let them know the place to be and people to meet for PI System security are at the UC.

 

Your teamwork makes a difference with Cyber security!

SQL saved views like this make me sad:

 
SELECT 
  e.Name as AssetName
, e.ID as AssetID
, et.ID as TemplateID
, tir.*
FROM NuGreen.Asset.ElementTemplate et
INNER JOIN NuGreen.Asset.Element e
ON et.ID = e.ElementTemplateID
INNER JOIN NuGreen.Asset.ElementCategory ec
ON ec.ElementID = e.ID
INNER JOIN NuGreen.Asset.Category c
ON ec.CategoryID = c.ID
CROSS APPLY NuGreen.DataT.TransposeInterpolateRange_Compressor
     (
        e.ID
      , 't-1w'
      , '*'
      , '1h' 
     ) tir
WHERE
c.Name = 'Equipment Assets'
AND (et.Name = 'Compressor' OR et.InheritancePath LIKE '%Compressor%')
OPTION(FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)

 

 

It’s a PI OLEDB Enterprise query to run a (data query) on a (subset of assets) for a (time range). That cries out for being broken into components.

 

 

Breaking views into pieces

To continue this example, we can start by finding a list of assets. Incidentally, a query like this would be the ideal centerpiece (fact table) for a snowflake schema in a BI cube.

 

 

 
CREATE VIEW [NuGreen].[Asset].[CompressorsOfInterest]
AS
SELECT 
  e.Name as AssetName
, e.ID as AssetID
, et.ID as TemplateID
FROM NuGreen.Asset.ElementTemplate et
INNER JOIN NuGreen.Asset.Element e
ON et.ID = e.ElementTemplateID
INNER JOIN NuGreen.Asset.ElementCategory ec
ON ec.ElementID = e.ID
INNER JOIN NuGreen.Asset.Category c
ON ec.CategoryID = c.ID
WHERE
c.Name = 'Equipment Assets'
AND (et.Name = 'Compressor' OR et.InheritancePath LIKE '%Compressor%')

 

 

If we write a second view which performs the data call for these assets, we’re on the right track – we’ve broken the query into (subset of assets) and (data query for time range). But we could do better and separate (data query) from its (time range).

 

 

 

How do we break it apart? We’ve been calling a function with fixed parameters…

 

 

 
CROSS APPLY NuGreen.DataT.TransposeInterpolateRange_Compressor
     (
        e.ID
      , 't-1w'
      , '*'
      , '1h' 
     ) tir

 

 

The magic becomes possible when we switch over to the transpose function’s equivalent Function Table.

 

 

6470.sqloe_5F00_tvf_5F00_params.png
2134.sqloe_5F00_ft_5F00_columns.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Either this is a little-known trick, or it’s so obvious that nobody’s written about it here on vCampus, but PI OLEDB Enterprise saved views are completely stackable. WHERE restrictions in one view will cascade directly into nested views. Because the function table exposes StartTime, EndTime, and TimeStep as columns, those are ideal targets for this trick.

 

 

 

So we can make a data query view which, by itself, won’t execute because it is incomplete, lacking time range restrictions:

 

 

 
CREATE VIEW [NuGreen].[DataT].[CompressorDataInterpolateRange_Flex]
AS
SELECT compressors.AssetName, tir.*
FROM NuGreen.Asset.CompressorsOfInterest compressors
INNER JOIN NuGreen.DataT.ft_TransposeInterpolateRange_Compressor tir
ON compressors.AssetID = tir.ElementID
--no StartTime restriction
--no EndTime restriction
--no TimeStep restriction
OPTION(FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)

 

 

And call it from a query (or saved view) with the time restrictions specified in the WHERE clause:

 

 

 
SELECT *
FROM [NuGreen].[DataT].[CompressorDataInterpolateRange_Flex]
WHERE
StartTime = 't-1w'
AND EndTime = '*'
AND TimeStep = '1h'

 

 

My testing indicates no quantifiable performance hit in taking this layered approach, and we end up with the advantages of flexible, reusable queries. Users now have this query in their toolbox.

 

 

Views with parameters from SQL Server

Recently, I was working with a customer who was trying to pull PI assets and data into a 3rd-party OLAP package, but the data access options were limited. There was only limited JDBC connectivity to a handful of specific targets – including Microsoft SQL Server, but not PI JDBC. So we chose SQL Server as our gateway to PI OLEDB Enterprise (as a Linked Server). If you’re unfamiliar with SQL Linked Servers, this MSDN article is a fair introduction. The product manual for PI OLEDB Enterprise holds specific configuration instructions.

 

 

 

 

 

In an ideal world, we could take a query drafted in PI SQL Commander (such as that very first one in this post), adapt it very slightly, and execute it from SQL Server to our PI OLEDB Enterprise Linked Server. The Execution Plan will be 100% remote:

 

 

 

5305.ssms_5F00_queryErrorGoodPlan.png

 

 

 

But the Linked Server approach has an Achilles’ Heel: the SQL Server query optimizer cannot be disabled. A correctly-written PI OLEDB Enterprise query may fail to execute from SQL Server after the SQL Server decides to “help”:

 

 

 

5228.ssls_5F00_queryErrorPlan.png

 

0827.ssms_5F00_queryError.png

 

 

 

Classically, there are two workarounds for this situation: VIEWs saved in the target provider (i.e. PI OLEDB Enterprise), or the OPENQUERY command. But views don’t traditionally accept parameters (such as time ranges). OPENQUERY is SQL function to pass a query string directly to a remote provider, but it too is limited to static queries (so no stringbuilding in SQL unless the workaround is employed).

 

 

 
--IN SQL Management Studio
SELECT * FROM OPENQUERY(AF_Loth,'
SELECT compressors.AssetName, tir.*
FROM NuGreen.Asset.CompressorsOfInterest compressors
INNER JOIN NuGreen.DataT.ft_TransposeInterpolateRange_Compressor tir
ON compressors.AssetID = tir.ElementID
AND tir.StartTime = ''t-1mo''
AND tir.EndTime = ''*''
AND tir.TimeStep = ''1h''
')

 

 

But naturally, the customer’s use case called for the ability to pass custom time ranges into their data queries. So what did we do? We made some incomplete/non-time-restricted views in PI OLEDB Enterprise and called them from SQL Server with WHERE restrictions on time. The restrictions get passed straight through into PI OLEDB Enterprise. Extending the Compressors example from earlier:

 

 

 
--IN SQL Management Studio
SELECT *
FROM AF_Loth.[NuGreen].[DataT].[CompressorDataInterpolateRange_Flex]
WHERE
StartTime = '01-Mar-2012 00:00:00'
AND EndTime = '01-Apr-2012 14:25:31'
AND TimeStep = CAST('01:00:00' AS TIME(0))

 

 

4188.ssms_5F00_compressorDataFlex.png

 

 

 

Voilà, no execution errors, and we keep the ability to specify time range and resolution from a query in SQL Server. And of course, this trick works for parameters other than just time ranges - hierarchy paths, template names... whatever floats your boat.

Filter Blog

By date: By tag: