Skip navigation
All Places > PI Developers Club > Blog > Authors bperry

PI Developers Club

9 Posts authored by: bperry Employee

Hi all,

 

The Esri Integrator team has been hard at work since our first release this past fall – and PI Integrator for Esri ArcGIS 2014 SP1 is here!

 

2014 SP1

This release brings some welcome enhancements to the product:

  • Compatibility with
    • The newly-released Esri ArcGIS 10.3 platform
    • Windows-based security in ArcGIS
    • Oracle-backed ArcGIS Server instances
    • Future versions of the PI AF SDK
    • AF Child Attributes
    • Direct support for Portal for ArcGIS data stores in the GeoEvent Extension when publishing layers. Previously, the Server data store was necessary; now we can go directly to Portal.

 

  • A slew of bug fixes, among which the interesting bits are fixes for
    • A few possible odd behaviors in the Configuration Wizard which would prevent publishing. These are cleaned up.
    • Inability to access the CSV definition for manually creating the target Feature Service, and subsequent inability to select a manually-created Feature Service in the Wizard. Manual publishing works as expected now.
    • UI glitches when defining a new layer, especially if that new layer is large. This service pack doesn’t bring many design changes to the UI, but it certainly polishes what is already there.

 

    The release notes go into further detail.

 

Two other general items of note:

 

  • Two of the Esri products we interact with have been renamed with ArcGIS 10.3. The GeoEvent Processor is now the GeoEvent Extension, and the Portal Extension for Server is now Portal for Server. Our docs reflect this, though if you’re still using ArcGIS 10.2.2, the renaming hasn’t struck you yet.
  • ArcGIS 10.3 introduces a new database option underlying the ArcGIS Server: the “ArcGIS datastore,” which is a free PostgreSQL incarnation that ostensibly is a great alternative to paid SQL Server and Oracle underpinnings. But it is not compatible with the Esri Integrator product. OSIsoft and Esri are working together to establish the cause and resolution. In the meantime, we didn’t want to hold the release of this service pack!

 

We, the members of the development team, are encouraged by the feedback you’ve given us thus far, and excited that this service pack will help the momentum continue.

 

ArcGIS Extensibility

Ok, enough about the Integrator product. Now a word on something arguably more interesting – the PI data itself, once it’s available in ArcGIS! (This being, of course, the whole point of the Esri Integrator...)

 

The PI ecosystem currently lacks direct Esri map plugins (but we’re working on that!) and the ArcGIS side is ripe for more tools and widgets that expose PI data. There’s a lot to be done in that area. Esri has wonderful partner support, their ArcGIS platform is highly extensible, and customers regularly adopt partner add-ins and solutions. We encourage all of you PI acolytes to explore that world of ArcGIS extensibility. Time to see what all that PI data can really do when delivered into such a rich and extensible GIS platform!

 

All the best,
Brandon
and the rest of the Esri Integrator development team

Picking up where the last post left off...

 

 

 

In our previous post, we announced the first beta release of PI Integrator for Esri ArcGIS. Now - fast forward a few months - and we're in Beta 3! It's available in the download center, and requires Windows 8/Server 2012 and an Esri ArcGIS system. Speaking candidly, as we like to do here on vCampus, Beta 3 is our Release Candidate, and we're hopeful that it will carry us to Release 1 very shortly. Barring any significant issues, the code is feature-complete and all that remains on the horizon are a few bugs to fix and edges to smooth. This means we're very interested in feedback! The beta mailbox is BetaIntegratorForEsri@osisoft.com

 

 

 

The biggest change that Beta 3 brings is a different transport between our Configuration Portal and Esri's Geo Event Processor. We previously used the WebSocket protocol to send data to Esri, but that approach ended up with some intractable reliability problems. So the new transport-of-choice to send data to Esri is our old friend, HTTP. Esri's Geo Event Processor now makes simple HTTP(S) requests to us at some configured frequency and the system is much more reliable. This actually has some other pretty great advantages (being stateless at the web tier, it scales to the cloud/farm very well; individual requests can fail without bringing down the whole chain; etc) and the overhead is trivial compared to the payload size. Our data back-end is also optimized for that, and now scales far higher than in previous beta versions.

 

 

 

We hope that if maps are your thing, and ArcGIS is your style, that you take the opportunity to try out our Integrator!

 

 

 


Cheers,
Michael van der Veeken
Brandon Perry

 



 

P.S. - On a very related topic, Esri has some great documentation about their REST API and JavaScript libraries. The Esri Integrator product exists to feed PI data into the Esri world, so there's an opportunity for some rather novel apps and widgets to interact with real-time data on maps. We're rather curious to see what the community comes up with!

 

P.P.S. - Speaking of our data back-end ("Data Relay"), it is AF SDK-based and we've learned some lessons that we intend to share with the community: mostly, in regards to making the AFDataPipe and AFDataCache objects robust. If anyone has specific questions or requests before that blog post gets written, please shoot us a message.

Anyone who was at vCampus Live this past year may remember a session on what was then known as “PI Geo Services.” We showed a proof-of-concept delivering PI data into Esri’s ArcGIS geospatial platform, just a few months after having begun product development in earnest. Since then, the product name has changed and our team has grown slightly – and we are proud to announce the beta availability of PI Integrator for Esri ArcGIS!

 

 

 

6758.osiandesri.png

 

 

 

 

 

The idea behind the product is – not surprisingly – to stick PI data on a map. For many of us (including both developers on the project), we’re probably muttering “yeah, I wrote something like that one afternoon, with the Google Maps API and some beer.” Unsurprisingly, one of the first things our team learned (as PI guys) was that true geospatial folks (e.g. Esri) take maps really seriously. To them, in the beginning before we got to know each other, we were just “a database” – and to us, they were “a map.” That’s all fixed now, of course. In the same way that OSIsoft has built a company around time-series data, Esri has built a company around maps and geo-analytics. That rabbit hole goes deep, and it’s pretty cool to imagine analytical situations where time and space collide. Herein lies much of the power of our new product – the folks who know PI and PI data can very easily deliver that data into the skilled hands of Esri ArcGIS gurus.

 

This product is really supposed to be a product, and not something requiring extensive custom work/rework every time you need new widgets on a map. “Point, click, done” is the goal. So the human interface with PI Integrator for Esri ArcGIS is a friendly Configuration Portal.

 

7737.streamlist_2D00_sm.png

 

 

 

 

 

Within the Configuration Portal, you configure map services which are containers for map layers. In our implementation, map layers are like an AF Element search for one specific AF Element Template. You could configure a map layer to point to { your production PI AF Server, the NuGreen database, elements of the Boiler template, within Houston } – and then the data for all of those boilers is ready to stream out and onto a map. That publishing process is handled by a graphical wizard. Once the PI data is live on an ArcGIS map, the circle can be completed by linking back into a PI Coresight display.

 

6064.dashboard_2D00_sm.png

 

 

 

 

 

Architecturally, we've built the product to share a codebase for on-premises deployment (onto an IIS Server) and an Azure cloud deployment. This open beta announcement is for the on-premises deployment, which we’re focusing on for now.

 

Architecturally, zoomed in a bit: the product consists of two main components – a Data Relay service (read: PI AF client) and a Configuration Portal (read: web application). The Data Relay service gathers PI data, executes AF data references, and such. The Configuration Portal, as the name suggests, is the front-end to the product. It is also the muscle behind packaging and pushing data from the Data Relay node over to the ArcGIS system.

 

Right now, all data passes through Esri’s GeoEvent Processor engine and then into an ArcGIS Feature Layer which feeds maps. In upcoming releases, we’ll be doing some things to expose historical data and event frames. Right now, we’re very happy to start with real-time data!

 

5850.data_2D00_flow.png

 

 

 

 

 

If you’re itching to jump in and get started, there’s a full-fledged user manual available on the Tech Support website and within the download bundle – and, just as importantly, release notes! Since this is a beta, the section of the release notes titled “Known Issues” is mandatory reading :) ... Other than reading the release notes and user guide, my best advice is to find a buddy who knows ArcGIS. They’ll know how to work magic with the PI data you put in their hands. And they will be an indispensable interpreter as you get the hang of the ArcGIS lexicon.

 

Our talk from the San Francisco Users Conference this year has some further detail on the product and its provenance, scope and direction: http://www.osisoft.com/Templates/item-abstract.aspx?id=10993 Note that much time has passed since our vCL!13 talk, but for the sake of posterity, it’s at http://www.osisoft.com/Templates/item-abstract.aspx?id=10819

 

In the next post, we’ll outline the process of getting started with the product, and perhaps show an example of the product in action.

 

A big thanks to the rest of our team, and a heartfelt cheers to all who take our product for a spin!

 

Michael van der Veeken
Brandon Perry

This is a two-part post. 

 

Part One, below, is an introduction into obtaining PI summary data (statistics etc.) within PI OLEDB Enterprise.
Part Two is where things will get interesting: a look at how to exploit the nuances of PI Time Syntax when creating reports containing summary data. In other words – how to get statistics for different types of time ranges, like month-to-date.

 

 

Raw data or statistics?

As many here know, PI OLEDB Enterprise (as of 2010/2012 versions) does not directly support statistical calls. If I have an AF attribute giving me raw production flow…

 

1638.1_5F00_1_5F00_attribute.png

 

…then within PI OLEDB Enterprise I’m limited to getting various renditions of those archived values.

 

 

 

 

 

3323.1_5F00_2_5F00_oletables.png

 

 

 

For production flow, though, what really matters to me is total production – e.g. the total for today, or the month-to-date total, or some such thing. Total is key; the flow at given points doesn’t matter for my use case. So how do we coax a total out of PI OLEDB Enterprise?

 

 

What not to do

One forbidden fruit is the included aggregate SQL functions: SUM, AVG, MAX, MIN, COUNT.

 

0523.1_5F00_3_5F00_olebadquery.png 

 

 

 

Don’t get me wrong – there are definitely uses for these functions – but more often than not, they don’t fit the use cases of PI OLEDB Enterprise. What if we tried taking the SUM() of today’s values for Production flow? First off, we’d get an event-based result – senseless here – though we could fudge things with some interpolation. Second, this would be wildly inefficient! Why bring back a mountain of data from the PI Data Archive, only to aggregate it locally – when the PI Data Archive can do aggregations itself?

 

 

Adding statistics to the AF model

Thus the question becomes: How do we coerce the PI Data Archive into doing our aggregations for us? Thankfully, the PI Point Data Reference has this capability. So back in AF, we’ll add some attributes below Production Flow which will eventually show the numbers as desired.

 

4657.1_5F00_4_5F00_childatts.png

 

 

 

If we begin by configuring Total 24h, note that we already have a perfectly good PI tag to go and totalize – whichever tag Production flow is pointing at! So I’ll set this data reference to use Production flow’s tag as its source.

 

4760.1_5F00_5_5F00_piptattribute.png

 

 

 

 

 

But the interesting part is below, where if time mode is set to be a time range, we get a wealth of “value retrieval” modes: among them, Total, just as we need in this example.

 

5756.1_5F00_6_5F00_piptmethod.png

 

 

 

Our totalization requires some parameters – source units and rate, and the relative time range to use. Since our attribute is specifically a “past 24 hour total,” the appropriate time range is “-24h”

 

0066.1_5F00_7_5F00_pipttotalizer.png

 

 

 

This attribute, which does totalizations relative to an end time, will be directly accessible in PI OLEDB Enterprise. Here, we see it in PI System Explorer:

 

2185.1_5F00_8_5F00_wellsinexplorer.png

 

 

Querying the statistical attributes

I threw together a query to query my wells for their 24-hour Production flow total, for two different time stamps:

 

between * (now) and 24 hours before now

 

between the beginning of today (t) and 24 hours before then

 

Remember, the “24 hours before then” part is what we set in the Total 24h attribute configuration. What we’re specifying in the query is what “then” means.

 

 

 
SELECT e.Name as Well
, REPLACE(ea.Path,'\','') as Metric
, ea.Name as Aggregate
, di.[Time] as [Time]
, di.ValueDbl as Value
, UOMAbbreviation(ea.DefaultUOMID) as UOM
FROM Asset.ElementHierarchy eh
INNER JOIN Asset.Element e
ON eh.ElementID = e.ID
INNER JOIN Asset.ElementTemplate et
ON et.ID = e.ElementTemplateID
INNER JOIN Asset.ElementAttribute ea
ON ea.ElementID = e.ID
INNER JOIN Data.ft_InterpolateDiscrete di
ON di.ElementAttributeID = ea.ID
WHERE eh.Path = '\Wells\' --restrict by Path
AND et.Name = 'Well Template' --Template
AND ea.Name = 'Total 24h' --Attribute
AND ea.Path = '\Production flow\' --Parent Attribute
AND di.Time IN ('*','t') --Two different timestamps to query
ORDER BY e.Name ASC, di.Time ASC

 

 

1072.1_5F00_9_5F00_queryresults.png

 

 

 

So there you have it – the ability to request all of the native PI Data Archive statistics from within PI OLEDB Enterprise (and any other PI AF client) relative to the time of your choosing.

 

 

First caveat

Flexibility and overhead. Quite obviously, this is not a flexible way of doing things. It requires the infrastructure (PI AF model) to contain pre-configured, aggregated attributes to be queried. This adds overhead and removes flexibility, but the good news is that the AF SDK contains (as of version 2.5) the underpinnings for PI OLEDB Enterprise to offer native PI statistics in the future. I dream of a future without need of this workaround… but until then, this can work well.

 

 

 

Can work well?”…. Ah! Thus, we arrive at the...

Second caveat

Cost. Remember that these statistical calls are done on-demand. The magic formula for cost is:

 

(Attribute count) * (Data rate) * (Time range) = (Cost)

 

So if you’re storing 60Hz data in your PI archive, please don’t call an on-demand daily average for 1000 attributes at once! Your data rate is probably much saner, but regardless: please don’t do (e.g.) year-to-date totalizations on-demand! That type of expensive call is why we have PI Totalizer tags. Or if Totalizers make you squeamish, then PI ACE. Either of those solutions can write year-to-date totalizations to a PI tag at the interval of your choosing, which you can expose in your PI AF elements and query (raw values) from PI OLEDB Enterprise. It is additional configuration overhead, but your PI Data Archive server will thank you. On-demand statistics follow the universal on-demand mantra:

 

if it’s cheap, do it on demand; if it’s expensive, schedule and store.
 

 

Summary

To get PI data statistics within PI OLEDB Enterprise, create AF attributes which expose a certain "value retrieval method" (average, total, etc) for a relative time range (e.g. -1h for the past hour). Then query those on-demand statistical attributes, interpolated, within PI OLEDB Enterprise. This works well for cheap/small statistical calls, but for expensive calls, use a calculate-and-store option like PI Totalizer tags or PI ACE.

 

 

Up next

Stay tuned for Part 2, a dive into exploiting the nuances of PI Time Syntax when we write reports – to get, for example, a week-to-day total in PI OLEDB Enterprise.

 

 

Unlike Shakespearean roses, an AF Attribute by any other name does not smell as sweet.

 

 

 

When naming PI tags, the naming convention often corresponds tightly with the control system topology – or, often, the PI tag names are the control system tag names. Or (as many of you are muttering right now) there is no naming convention, but the names are still cryptic. When the name is the primary identifier of an object, it makes sense to do cryptic, human-unfriendly maneuvers in an attempt to uniquely and positively identify the tags for those individuals who are in “the club” and understand the control system lingo.

 

 

 

Try going onsite and understanding a competitor’s tag names. Go on, I dare you. Or hire a contractor to do integration work at your site. Or hire a new employee – you get the idea. With cryptic tag names, your data is cloaked in a fog of mystery. One of the prime directives of PI AF is to make your assets understandable by any onlooker (...who has the right security permissions).

 

 

 

Instead of requiring you to parse a fully-qualified PI Tag name (e.g. CEL-LIS_TRK001_DLOAD.MEAS.PV), PI AF presents Truck 1 as an entity with meaningful properties of a truck: Speed, Oil temperature, Dynamic load, and so forth. Furthermore, those properties are each presented in a known (and selectable!) unit of measure. Standardization (every truck is a truck), legible naming (e.g. Oil temperature), and units of measure (Oil temperature is presented by default in °C, unless requested otherwise) is how PI AF shines the light of sanity upon complex worlds.

 

 

 

Thus we arrive at my sermon: When you’ve got your hard hat on and are playing PI AF builder, remember the prime directive: PI AF must be friendly. A crucial and repeated task is to give PI AF Attributes “good” names. Good names are names that immediately have meaning to mortal humans, foreign systems, and the humans establishing mappings between PI and foreign systems.

 

 

 

 

Talk like you're a human

Names must be written like you’re communicating with a  human. “ENG SPD” is a lousy and obscure representation of “Engine speed,” so don’t even think about naming your PI AF attributes in the former, cryptic way. Forget how your PI tags are named; it doesn’t matter one bit. “Engine speed” makes much more sense to everyone and that is what matters going forward. I’m a mechanical engineer, and even I get nauseated by coded names.

 

4426.attEngineSpeed.png

 

 

Don't mix names with units

Names should not include units-of-measure. It makes absolutely no sense to say “Generated watts” when consumers can request the value of “Generated watts” in watts, kilowatts,  BTUs, or horsepower. What you’re trying to describe is “Generated power,” so call it that.

 

1512.attGeneratedPower.png

 

 

Be relative

Child attributes should be named relative to their parents. Long lists of attribute soup can be avoided three ways: subdividing your assets, categorizing the attributes (to enable grouping/sorting), and by nesting them (some attributes as child attributes of others). The latter case of child attributes pertains to this article on naming. If “Feed A Flow” is the parent attribute holding  “Feed A Total” and “Feed A Rate of Change,” those children’s’ names should be reduced relative to their parent – to “Total” and “Rate of Change.” Otherwise, you’ll end up with a attributes fully-named as, e.g., “Feed A Flow|Feed A Total.” This likely makes more sense as “Feed A Flow|Total” – the total of Feed A’s flow. 

 

4555.shortChildNames.png

 

In the new integrated PI Search experience (cf. Coresight, DataLink), search results will appear associated with their immediate parent:     

 

3005.coresightSearch.png

 

 

 

 

 

These three rules – legible names, not including units of measure, and not over-qualified if nested – will help your PI AF implementation be successful as an enterprise-level object model and data foundation. Go forth and prosper.

 

 

 

And I'm hoping for some great comments from those of you who can add to this list of good practices! What's missing?

 

 

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

 

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.

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.

bperry

Old Number 0

Posted by bperry Employee Mar 31, 2012

 

 

On behalf of myself and Martin Bryant, Howdy from Houston as we inaugurate this new blog of ours.

 

 

 

We’re OSIsoft Field Service engineers. In the course of our ramblings, Martin and I see a good deal of action with PI AF, the PI Data Access suite, and Analytics. We do customer coaching, internal research (fancy way of saying we get harebrained ideas from time to time), proofs-of-concept with OSIsoft partners (usually involving integration of PI with other business systems), and have been known to write material for the public Application Development course. 

 

 

 

Thus we find ourselves with a stack of code and lessons learned which  we “should throw on vCampus,” and this blog will be its landing pad. Thanks to the vCampus team for getting us set up here.

 

 

 

Brandon Perry

 

 

Filter Blog

By date: By tag: