Skip navigation
All Places > PI Developers Club > Blog > Authors Rick Davin
1 2 3 Previous Next

PI Developers Club

32 Posts authored by: Rick Davin Employee

A customer recently asked about filtering on multiple values of an attribute in AFSearch.  This is easily addressed using the IN search operator in a query string, or the equivalent AFSearchToken constructor expecting an array of values.  There is one major caveat: the IN operator is not allowed for floating point types such as Single or Double, since binary floating point values are considered approximations instead of exact numbers.

 

There are a few tips to get developers pointed in the right direction.  First of all, values within the IN operator are delimited by semi-colons.  If you (like me) accidentally use a comma-delimited list, then you will receive an odd error about missing a closing parenthesis.  Then if you carefully inspect the query string, then you (like me) will start pulling your hair out because you don’t see a missing closing parenthesis!  Hopefully you may benefit from my pain by quickly switching your delimiter to be a semi-colon.

 

Another tip is that while blanks are allowed within the IN operator - or more specifically around the delimiters - any values that contain embedded blanks must be wrapped in quotes (single or double quotes).  Consider this list of cities:

 

  • London
  • Paris
  • San Francisco

 

Obviously, San Francisco needs to be wrapped in quotes due to the blank inside the name.  If the attribute path is “|City”, then you could have this C# filter in your query string:

 

string uglyQuery = "|City:IN(London; Paris; \"San Francisco\")";

 

Though ... that is a wee bit ugly.  Besides being ugly, it also might be confusing to someone using a different language, for instance, VB.NET.  A couple of weeks ago, I taught a class where a VB coder thought the unsightly \" were delimiters for AFSearch.   I explained to him that it's how C# escapes double quotes.  This can look much easier on the eyes if you just use single quotes:

 

string prettyQuery = "|City:IN(London; Paris; 'San Francisco')";

 

And it avoids any needless confusion that \" is an AFSearch delimiter.

 

This is all fine-and-dandy, but how often do you hard-code values?  It does make the example simple and straight-forward, but what if the list of items you wish to filter upon are in a list or array?  Let’s look at some code that helps us out.  After all, this is a PI Developers Club, which demands for code.  Strictly speaking, all we need for the collection of values is that they be in an enumerable collection.  Your source collection doesn’t have to be strings, but keep in mind that eventually we will need to pass a string to the AFSearch query, or if we use the AFSearchToken, then we must actually pass an array of strings.

 

For our cities example, it makes sense that the city names are strings.  Let’s not take the easy way out and make it an array of strings.  Instead we will use a list so that we can see what we must do differently to make it all work together:

 

List<string> cities = new List<string>() { "London", "Paris", "San Francisco" };

 

A tiny bit of  code is needed to put that inside a query string:

 

// A little LINQ to wrap single quotes around each city, and separate them with "; "
string delimitedCities = string.Join("; ", cities.Select(x => $"'{x}'"));

// An Interpolated String to substitute the delimitedCities.
string query = $"|City:IN({delimitedCities})";

 

This resulting value in query would be:

 

"|City:IN('London'; 'Paris'; 'San Francisco')"

 

Or if you prefer working with search tokens, this would be the equivalent:

 

// Tip: here you must pass a string array, so we must ToArray() on the cities list.
AFSearchToken token = new AFSearchToken(AFSearchFilter.Value, AFSearchOperator.In, cities.ToArray(), "|City");

 

If we had defined cities to be a string array, we would not need the ToArray(), but then this example would be boring and less educational.

 

What if our enumerable collection isn’t a bunch of strings?  Let’s say we have a bunch of GUID ’s.  (Exactly how you got these GUID's is an interesting question not addressed here ; suffice to say this example takes a collection of things that aren't strings and converts them to one that is.)  There would now be an extra step needed where we must convert to string.  Once we have a collection of strings we can then implement code similar to the previous examples.  Let’s imagine we have something like this:

 

IEnumerable<Guid> ids = GetListOfGuids();  // magically get a list of GUID

 

Or it could maybe have been:

 

IList<Guid> ids = GetListOfGuids();

 

Let's say we want to filter on an attribute path of “|ReferenceID”.  First let’s tackle the problem of converting a GUID into a string that is compatible with AFSearch.  This is easy enough thanks to LINQ:

 

// Nicest way to convert a GUID a string compatible with AFSearch is to use GUID.ToString("B").
IEnumerable<string> idStrings = ids.Select(x => x.ToString("B"));

 

Okay, so now we have an enumerable collection of strings.  Using what we learned in previous examples, we can knock this out:

 

// A little LINQ to wrap single quotes around each string item, and separate them with "; "
string delimitedIds = string.Join("; ", idStrings.Select(x => $"'{x}'"));

// An Interpolated String to substitute the items.
string query = $"|ReferenceID:IN({delimitedIds})";

 

Fantastic.  If you prefer AFSearchTokens, that’s easy enough as well, but we do require the idStrings to generate a string array.

 

// Tip: here you must pass a string array, so we ToArray() on the idStrings collection.
AFSearchToken token = new AFSearchToken(AFSearchFilter.Value, AFSearchOperator.In, idStrings.ToArray(), "|ReferenceID");

 

Granted our example would have been simplified if we defined idStrings to be an array in the first place, but what fun would there be in that?

 

 

VB.NET Examples

 

Some of us supporting the PI Developers Club think there should be more VB.NET examples.  Towards that end, here are code snippets for the VB coders out there:

 

    Dim uglyQuery As String = "|City:IN(London; Paris; ""San Francisco"")"

    Dim prettyQuery As String = "|City:IN(London; Paris; 'San Francisco')"

    Dim cities As List(Of String) = New List(Of String) From {"London", "Paris", "San Francisco"}

 

Cities Query Example:

 

Query String

        ' A little LINQ to wrap single quotes around each city, and separate them with "; "
        Dim delimitedCities As String = String.Join("; ", cities.Select(Function(x) $"'{x}'"))

        ' An Interpolated String to substitute the delimitedCities.
        Dim query As String = $"|City:IN({delimitedCities})"

 

AFSearchToken

' Tip: here you must pass a string array, so we ToArray() on the cities list.
Dim token As AFSearchToken = New AFSearchToken(AFSearchFilter.Value, AFSearchOperator.In, cities.ToArray(), "|City")

 

Guids Example (or something that is not a collection of strings)

 

Query String

        Dim ids As IEnumerable(Of Guid) = GetListOfGuids()   ' magically get a list of GUID

        ' Nicest way to convert a GUID a string compatible with AFSearch is to use GUID.ToString("B").
        Dim idStrings As IEnumerable(Of String) = ids.Select(Function(x) x.ToString("B"))

        ' A little LINQ to wrap single quotes around each string item, and separate them with "; "
        Dim delimitedIds As String = String.Join("; ", idStrings.Select(Function(x) $"'{x}'"))

        ' An Interpolated String to substitute the items.
        Dim query As String = $"|ReferenceID:IN({delimitedIds})"

 

AFSearchToken

        ' Tip: here you must pass a string array, so we ToArray() on the idStrings collection.
        Dim token As AFSearchToken = New AFSearchToken(AFSearchFilter.Value, AFSearchOperator.In, idStrings.ToArray(), "|ReferenceID")

 

Summary

 

If you want to filter on a variety of values for an attribute, this requires the IN search operator.

  • You may use IN in a query string or an AFSearchToken.
  • Values must be exact.  Therefore, IN does not work on Single or Double floating point data types.
  • In query strings
    • The semi-colon is the delimiter between IN values.  Example: "|City:IN(London; Paris; 'San Francisco')"
    • Values containing blanks must be enclosed in single or double quotes.  Example: "|City:IN(London; Paris; 'San Francisco')"
  • AFSearchToken
    • Values must be passed as strings in an array

I have been asked on many occasions “How can I find the first recorded event for a tag?”  The direct answer to this may be as brief as the question.  However, usually there is a lurking question-behind-the-question about what they really want to do with that first event, and if you dig even slightly deeper you will uncover the overall task they are trying to accomplish.  What you may ultimately discover is there is no need to find the first event.

 

Let’s start off with the direct, simple answer in the form of a C# extension method:

 

public static AFValue BeginningOfStream(this PIPoint tag) => tag.RecordedValue(AFTime.MinValue, AFRetrievalMode.After);

 

This works because the PI Point data reference implements the Rich Data Access (RDA) method of RecordedValue.  The earliest timestamp to query is AFTime.MinValue, that is midnight January 1, 1970 UTC.  Thanks to the AFRetrievalMode, you ask for the first value after January 1, 1970.  If it’s the earliest recorded timestamp you are only concerned with, you can use this extension method:

 

public static AFTime BeginningTimestamp(this PIPoint tag) => BeginningOfStream(tag).Timestamp;

 

For PI points, this would give you the BeginningOfStream method to go along with the built-in EndOfStream.  Before the advent of future data, the EndOfStream was simply the Snapshot.  But there are oddities related to future data, which required different handling of data compared to the traditional historical data.  Hence, Snapshot was replaced by CurrentValue, and EndOfStream was added.

 

An AFAttribute could have a BeginningOfStream method, but it doesn’t have the same nice guarantees of PIPoint.  It all depends upon the data reference being used and whether it supports the RDA Data.RecordedValue method, which is why you should properly check that it is supported before attempting to call it:

 

public static AFValue BeginningOfStream(this AFAttribute attribute)
{
    if (attribute.DataReferencePlugIn == null) // static attribute
    {
        return attribute.GetValue();
    }
    if (attribute.SupportedDataMethods.HasFlag(AFDataMethods.RecordedValue))
    {
        // Depends on how well the data reference PlugIn handles AFRetrievalMode.After.
        return attribute.Data.RecordedValue(AFTime.MinValue, AFRetrievalMode.After, desiredUOM: null);
    }
    // Fabricated answer.  Not exact that one is hoping for.  
    return AFValue.CreateSystemStateValue(AFSystemStateCode.NoData, AFTime.MinValue);
}

 

Since the value being returned may be fabricated, I would be hesitant to include a BeginningTimestamp method as it would mask the inaccuracies.  To compensate, I would think further inspection of the returned value is needed, i.e. check for “No Data”.  Such are the difficulties of trying to create a BeginningOfStream method within your code libraries.  This is why we begin to probe more and ask about your use-case, or simply “What are you really wanting to do?

 

Virtually 100% of the people asking me how to find the first value in a stream want to find it for historical PI points only.  This greatly simplifies part of the problem because there is no need to be concerned with attributes or tags with future data.  Which brings us right back to the direct answer at the top, where you may be inclined to stop.  But if you take time to dig just a little deeper into what they are really doing, the true mission is revealed: they want to copy all historical data from an old tag to a new tag.  And trust me, there are several legitimate use-cases for doing this.

 

When I hear anything about copying of historical data, the first thought I have is “How much data is in the old tag?”  There are two particular answers that require the same special handling: (a) I don’t know, or (b) a lot.

 

The real problem they need to solve isn’t finding the earliest recorded timestamp.  Rather, they may have so much data they will bump into the ArcMaxCollect limitation (typically 1.5 million data values in a single data request).  There are programming ways around ArcMaxCollect (more below) and they rely upon the PIPoint.RecordedValues method specifying a maxCount > 0 (for instance, 100K works well).  The perceived issue of knowing the earliest timestamp becomes a moot point.  The more important date is knowing the end time, that is the switch-over date from the old tag to the new tag.  Depending upon how the old tag was populated, this may very well be the EndOfStream.  But if there is a chance that the old tag could still be receiving “InterfaceShut” or “IOTimeout” messages, you will need to explicitly specify the end time.  Worrying about the earliest recorded date has been a distraction to solving the real problem.

 

What of your start time?  I would think an in-house developer should know of the earliest start of their company's archive files.  A contracted developer could use AFTime.MinValue or go with a later, but still much safer date, such as “1/1/1980”.  Which brings us back to what they really want to do: copy large or unknown amounts of data.    This has been blogged about many times before:

 

Extracting large event counts from the PI Data Archive

 

PI DataPipe Events Subscription and Data Access Utility using AF SDK - PIEventsNovo

 

GetLargeRecordedValues - working around ArcMaxCollect

The recording for this Live Coding presentation may be found at: Getting the most out of AFSearch

 

Associated code may be found on this GitHub repository:

GitHub - Rick-at-OSIsoft/AF-SDK-TechCon-2018-AFSearch-LiveCoding: Presentation also titled "Getting the most out of AFSe…

 

GitHub also includes a PDF file of the slide deck.  Topics include:

 

Demo 1: What's new in AF SDK 2.9.5 and 2.10

Demo 2: Breaking it down line-by-line to see what's happening on the client versus the server

Demo 3: Caching versus Not Caching

Demo 4: Accurate counts especially when client-side filtering is required

Demo 5: The perils of LINQ

 

Along the way we touch upon AFAttributeSearch, using the skinny, lightweight FindObjectFields with AFEventFrames, why caching is so important to performance, and why you need to properly dispose of the AFSearch objects.

On behalf of Engineering's Technology Enablement team, and in conjunction with Technical Support's Developer Technologies team, it is my privilege to announce this year's Community All-Stars.  We give special recognition to these individuals for their prolific and relentless contributions to the PI Community at large.  We thank them for sharing their experiences and knowledge for all things PI, and in particular for any posts that are developer related.  Let me add that each recipient holds near and dear to each of them the concept of "community", and even if we did not hand out such awards, or if we did not hand out any prize other than simple recognition, these individuals would still post and contribute to our PI Square Community with the same dedication for the sheer sake of expanding the knowledge base of the community.

 

I ask my fellow PI Geeks to help congratulate our deserving 2018 All-Stars!  It's interesting to note that are 3 Community All-Star and one Rising Star are from 4 different continents.

 

PI All Stars 2018.jpg

Rick Davin (far left), Paurav Joshi, John Messinger, and Dan Fishman.  Not shown: Roger Palmen.

 

 

PI Developers Community All-Stars 2018

  • Dan Fishman, Exele Information Systems.  Dan is a first time winner as Community All-Star but won twice before as OSIsoft All-Star.
  • John Messinger, Optimate Pty Ltd.  John wins for the 2nd year in a row.
  • Roger Palmen, CGI.  I don't remember a year when Roger wasn't a winner.

 

Last year as an experiment, I began following a lot of people.  Anyone who has ever been an All-Star, honorable mention, or just nominated.  I can tell you that my Inbox was slammed all year by answers from Dan, John, and Roger.  Thanks, guys.  I don't mean that sarcastically.  Each "spam" in my Inbox from you is you helping another community member, so absolutely, sincerely THANKS!

 

PI Developers Club Community Rising Star 2018

We introduced this category last year to recognize contributions and efforts on well deserving contributors that steadily add to our content here but just fall short of the elite upper strata of All-Star.  A big difference between Rising Star and All-Star is a Rising Star may only be awarded once, whereas All-Star may be won year after year (I'm looking at you, Roger).  Anyone who has ever been an All-Star or Rising Star in the past is ineligible for further consideration as a Rising Star.  Last year we handed out 3 of these as a means of playing catch-up to some folks who have been solid contributors over the year.  This year we have only one winner:

 

 

Paurav along with Dan, John, and Roger win:

  1. Recognition in the community
  2. One year free subscription or renewal to PI Developers Club
  3. One-time free registration to a 2019 PI World event (SF or EMEA)
  4. A legal waiver for them to sign
  5. An Amazon gift card worth $400 USD granted after submitting the signed legal waiver back to us

 

And let's not forget there are a lot of fine contributors within OSIsoft.  While it is our job to help customers, we do want to recognize a select few for their efforts the past year.

 

PI Developers Club OSIsoft All-Stars 2018

 

Our OSIsoft recipients win:

  1. Recognition in the community
  2. Amazon gift card worth $200 USD

 

The clock has been reset and everyone's slate is clean

It's never too early to start thinking about 2019 All-Stars.  We monitor the quantity and quality of posts from one PI World SF to the next in order to pick our winners.  The new countdown has begun.  One thing I can tell you that all of our 2018 winners have in common are the following:

  • A high volume of posts
  • Posts of high quality (lots flagged as helpful or marked as answers)
  • A strong desire to help others.

 

Absolutely, positively candidates must contribute to threads other than their own.  In short, you must show strong participation in the community as a whole.  Without a doubt this year's crop of winners have done just that.

 

CONGRATULATIONS TO ALL WINNERS!

PI World NA is next week and I am quite excited.  Join us the afternoon of Day 3 (Thursday April 26) from 4:30 - 6:00 PM PDT for the first ever Developers Meet-Up Reception, where we will also be handing out a gaggle of awards for this year's crop of Community All-Stars, winners of the Innovation Hackathon, as well as the winners of the recent Visualization Virtual Hackathon!  If you consider yourself a developer, or just wish you were a developer in-training, or are a member of PI Developers Club, or at the very least are attending any DevCon presentations or labs at Parc 55, then by all means stop in for a beer or refreshing drink, have a snack, and mingle with fellow developers.  At 5:00 PM we will begin announcing awards, where you may congratulate the winners or commiserate with the losers other not-so-fortunate participants.  Don't overdo it because at 7:00 PM the party shifts to the Hilton for the always fun PI Geek Night, with more food and drinks, games, and other hijinks.

 

What:     Developer Meet-Up Reception & Awards

Where:   Parc 55, Cyril Magnin I & Foyer, Level 4

When:    4:30 PM - 6:00 PM (awards start at 5:00 PM)

 

You may see more at the bottom of this link:  PI World for Developers: PI World - OSIsoft Users Conference 2018 (SF)

 

Since it is being held in the late afternoon and to a limited segment of PI World attendees, this is not listed as an Evening Event.  However, it does appear on the Day 3 Agenda for Parc 55 hotel under the Sessions Agenda

 

We hope to see you there!

PI AF 2017 R2 (AF SDK 2.9.5) was released shortly before 2018.  There are some exciting new features with AFSearch that should interest developers.

 

First off, I would hope any developer would always go to the Live Library What's New page for any major PI AF release.  That page gives a summary of what's new with that particular release, not just for AFSearch but for all namespaces.  Specific to AFSearch namespace, you would see the following:

 

OSIsoft.AF.Search Namespace

Two new query based search classes have been added in this release: AFAttributeSearch and AFNotificationContactTemplateSearch. The AFSearchToken.AFSearchToken(AFSearchFilter, AFSearchOperator, String, IList<AFSearchToken> ) constructor and the AFSearchToken.Tokens property have also been added to support enhanced nested query filters for searches.

The following new search tokens have been added: EventFrame, IsInternal, Parent, PlugIn, and PlugInName.

 

As a fellow developer and PI geek, when I first read that my reaction was "Cool! Nested queries!"  If you think about it, an AFAttributeSearch means that attributes are being searched upon some element(s) or event frame(s).  This implies there will first be a search for elements or event frames, followed by a search upon those results for the attributes.  This does not require you to create 2 search objects on the client.  Rather you will have 1 search object, namely an AFAttributeSearch, and it will have a nested query to filter on the desired elements or event frames.  But the nested queries are not limited to AFAttributeSearch.   For example, you may have an AFEventFrameSearch that uses a nested query for its elements.

 

Each release of AF SDK since 2.8.0 has introduced new features and capabilities to put more efficient searches at your fingertips.  For example, if you were searching on an attribute category to return many attributes per element, you could additionally filter on PlugIn or PlugInName to restrict the returned attributes to be PI points.

 

Contacts Search Example

 

Here's a quick example where I search for any contact with a name beginning with "Davi*".

 

using (AFNotificationContactTemplateSearch search = new AFNotificationContactTemplateSearch(assetServer, "contact demo", " Name:'Davi*' "))
{
    foreach (AFNotificationContactTemplate contactTemplate in search.FindNotificationContactTemplates(fullLoad: true))
    {
        Console.WriteLine($"   {contactTemplate.Name,-24} {contactTemplate.Contact}");
    }
}

 

The output:

 

   Template                 Contact

   David Burns_Email        David Burns

   David Doll_OCS           David Doll

   David Moler_Email        David Moler

 

 

Nested Queries

 

Let's take a look at 3 different examples that all do the exact same thing.  We want to perform an attribute search for any attributes named "Feed Rate" that belong to any elements whose name starts with "Boiler".  We will perform the same search 3 times, but each time how we setup the search object will be different.  The 3 techniques we will briefly cover are:

 

  • Using Nested Search Tokens
  • Using Nested Query String
  • Using Interpolated Nested Query String

 

They example code is kept simple.  We will perform an AFAttributeSearch searching for attributes found within a nested element query with the following filters:

  1. Attribute Category of "Process Monitoring"  (note the blank)
  2. Element Category of "ProcessMonitoring" (does not have a blank)
  3. Element Template of "FCC Pump Process"

 

If you've ever worked with AFSearch before, then your previous experience should have been that you could not specify Category twice on a query prior to nested queries in 2.9.5

 

Using Nested Search Tokens

 

string templateName = "FCC Pump Process";
string elemCatName = "ProcessMonitoring";
string attrCatName = "Process Monitoring";

List<AFSearchToken> nestedTokens = new List<AFSearchToken>();
nestedTokens.Add(new AFSearchToken(AFSearchFilter.Template, templateName));
nestedTokens.Add(new AFSearchToken(AFSearchFilter.Category, elemCatName));

List<AFSearchToken> tokens = new List<AFSearchToken>();
// The Element uses the nested token(s)
tokens.Add(new AFSearchToken(AFSearchFilter.Element, AFSearchOperator.Equal, null, nestedTokens));
// The Attribute uses the non-nested tokens, in this case just Category.
tokens.Add(new AFSearchToken(AFSearchFilter.Category, attrCatName));

using (AFAttributeSearch search = new AFAttributeSearch(root.Database, "nested tokens example", tokens))
{
    search.CacheTimeout = TimeSpan.FromMinutes(10);
    foreach (AFAttribute item in search.FindAttributes())
    {
        // Do something
    }
}

 

 

Using Nested Query String

 

The trick with a nested query string is that it will be enclosed in {braces}.

 

// Notice how element has nested { }. 
// The Category depends on nesting level. 'Process Monitoring' with a blank is outside the nesting,
// so it will be an Attribute Category, whereas 'ProcessMonitoring' inside the nesting is an
// Element Category.
string query = "Element:{Template:'FCC Pump Process' Category:'ProcessMonitoring'} Category:'Process Monitoring'";

using (AFAttributeSearch search = new AFAttributeSearch(root.Database, "nested query string", query))
{
    search.CacheTimeout = TimeSpan.FromMinutes(10);
    foreach (AFAttribute item in search.FindAttributes())
    {
        // Do Something
    }
}

 

 

Using Interpolated Nested Query Strings

 

With Interpolated Strings, you may easily substitute a variable's value (technically, it substitute's the string returned from the variable's ToString() method).  If you are familiar with this in either C# or VB.NET, you know that {braces} are used.  This raises an interesting question of how the Interpolated String knows which brace is for the nested query, and which is for the value substitution.  You would denote that using an escape sequence of the braces themselves.

 

string templateName = "FCC Pump Process";
string elemCatName = "ProcessMonitoring";
string attrCatName = "Process Monitoring";

// This gives a compile error with an Interpolated String
// string query = $"Element:{Template:'{templateName}' Category:'{elemCatName}'} Category:'{attrCatName}'";

// Escape the { and } around literal braces with {{ and }}.
// Fun Fact: {{something}} is called the Mustache Template!
// See https://en.wikipedia.org/wiki/Mustache_(template_system)
string query = $"Element:{{Template:'{templateName}' Category:'{elemCatName}'}} Category:'{attrCatName}'";

using (AFAttributeSearch search = new AFAttributeSearch(root.Database, "interpolated nested query string", query))
{
    search.CacheTimeout = TimeSpan.FromMinutes(10);
    foreach (AFAttribute item in search.FindAttributes())
    {
        // Do Something
    }
}

 

An interesting bit of trivia: the {{something}} format of double braces is called the Mustache Template!

 

 

AFAttributeSearch

 

The nested query examples used AFAttributeSearch for searching upon elements.  The AFAttributeSearch may search on event frames instead.

 

One thing to note is the fullLoad parameter is missing from the FindAttributes method because it must always do a full load since the attributes cannot exist without the owning element or event frame.  However, the AFAttributeSearch.FindObjectFields is smart enough to know when it must make a full load to evaluate data references.  To state that a different way, it is smart enough to know when to skip a full load because of captured event frames. You don't have to do anything special like consult Oracles to figure this out for any given situation.  You would code it the same way regardless of the situation and let AFAttributeSearch.FindObjectFields make the right decision!

 

Why you should use the new search classes

 

Maintainability - some Find methods in AFAttribute or AFNotificationContactTemplate have already been marked as Obsolete, and it is reasonable to suspect that others may be marked so with future releases of AF SDK.  Make your applications more resilient by switching to these new methods sooner rather than later.

 

Performance - if you require fetching more than one page of results from the server and you opt-in to server-side caching, any of the AFSearch classes perform much faster than the older methods.  This means that AFAttributeSearch will be much faster than the AFAttribute.FindElementAttributes overloads.

 

Ease of Use - the AFSearch classes take care of paging issues seamlessly for you.  The new nested queries makes searching for attributes on either elements or event frames very easy with fewer lines of code.  There is previously mentioned smartness built into AFAttributeSearch.FindObjectFields. All of this without extra coding on your part.

 

Reliability - when searching on attribute values that might be evaluated client-side (due to needing to evaluate the data reference), it is impossible to do paging properly using the older search methods because you don’t reliably know what to use for the next page index.  By opting-in to server-side caching with the newer AFSearch methods, paging is more reliable.

The webinar scheduled for today (Wednesday March 21) will begin at 4:00 PM GMT or UTC.  Due to an unfortunate glitch with North America observing DST and Europe not, the previously published start times are incorrect.  Europe does not transition to Summer Time until March 25.

 

Recording and Slides are available at this link.

 

Topic: Asset Analytics native integration with MATLAB

 

North America 4:00 PM GMT is:

     Noon - 1 PM US Eastern

     9-10 AM US Pacific

 

Europe 4:00 PM GMT is:

     5:00 PM CET

 

UPDATE: A customer reported that the link in his confirmation mail 'click here to join' does not work, because it is missing the ':' after https. We don't know how widespread this is or whether it is a one-off for this particular customer.  However, he did report that the the URL in the Outlook invitation/appoint is OK.

Table of Contents

 

 

Many of the .NET based code samples on PI Square and GitHub are in C#.  It’s easy for a VB.NET developer to feel left out.  If you’ve been following me within the past 5 years you may think that I am a heavy duty C# developer.  Well, I am actually, and C# is definitely my first choice for writing applications.  However, I do have a fondness for VB as I started out with Visual Basic 3 in the mid-1990’s and was able to eek out a nice living writing for VB, VBA, and eventually VB.NET.  VB helped feed and clothe my family for close to 2 decades.

 

Coming from VB 6, VB.NET was a natural introduction to .NET programming.  But early on, I started to wean myself away from VB-centric calls and more towards .NET-centric ones.  For example, I replaced VB’s MsgBox calls with .NET’s MessageBox.  This transitioned me to the point where I stopped calling myself a VB developer and instead started calling myself a .NET developer because it was the .NET Framework, not the language used, that really was at the heart of my applications.  After a while of doing that, I found it an easier transition to switch from VB.NET to C# than when I had switched from VB 6 to VB.NET.

 

There is no need to put your "VB Forever" shields up.  I am not here to convince you to switch to C#.  You are more than welcome to stay with VB.NET.  What I am here to do is to help explain a few language specific things about C# so that it makes it easier for VB.NET coders like yourself to follow the C# examples more easily and without being stumped by some C# idiosyncrasies.  To a large degree, many of our examples are fairly easy to follow.  Putting aside the unsightly semi-colons and multitude of braces versus the wordiness of Visual Basic, both languages obviously have an overlapped feature set.  It’s easy to understand that C#'s foreach and VB's For Each do the same thing.  Likewise, both languages use similar features like Interpolated Strings, which look quite similar in either language.

 

This cheat sheet is to help address those few C# things that aren’t immediately translatable to VB.  That way you can begin to put your focus back on PI Developer Technologies and not worry so much about a language that is foreign to you.

 

 

Logical Operators

Earlier versions of VB did not have short-circuiting but VB.NET has had it for quite a long while.

C#
VB.NET
===
&And
&&AndAlso
|Or
||OrElse

 

 

var keyword for implicitly typed variables

Many C# snippets may occasionally employ the var keyword to declare variables.  For someone unfamiliar with it, they may mistakenly believe that the variable has a type of Object and may take any value of any data type (like Variant in the olden days).  This is not true. With var, a variable is assigned the type from the right-hand expression, and once that type is assigned it cannot be changed.  Microsoft's own recommendation is to use var when (1) the data type on the right-hand side is easily understood, or (2) if you really don't care about the type for variable with a very short scope.

 

var thing1 = "Hello, World!";   // always a string
var thing2 = 1;                   // always an Int32
var thing3 = 1.0;                // always a Double
var thing4 = 1.0F;                 // always a Single

 

The equivalent code in VB.NET would be:

 

Dim thing1 = “Hello, World!”;  ' always a string
Dim thing2 = 1;  ' always an Int32
Dim thing3 = 1.0; ' always a Double
Dim thing4 = 1.0F; ' always a Single

 

 

Conditional Operator, or ? : operator

You may frequently see statements such as this peppered in C# code:

 

var lastIndex = (list != null) ? list.Count – 1 : -1;

 

That is to say condition ? truePart : falsePart;  This is called the conditional operator.  The equivalent in VB.NET would use the If operator:

 

Dim lastIndex = If(list != null, list.Count – 1, -1)

 

One should emphatically NOT use the IIf function, as it has the adverse side-effect of evaluating both the TruePart and FalsePart regardless of the condition.  For example:

 

IIf(condition, SomeFunction1(), SomeFunction2())

 

Is the same as this code:

 

Dim truePart = SomeFunction1()      ' always runs regardless of condition
Dim falsePart = SomeFunction2()     ' always runs regardless of condition 
If condition Then
    truePart
Else
    falsePart
End If

 

Whereas the If operator is equivalent to this bit of code:

 

If condition Then
    ' will only run if condition is True
    SomeFunction1()
Else
    ' will only run if condition is False
    SomeFunction2()
End If

 

 

Null-Coalescing Operator, or ??

I personally am not a big fan of the null-coalescing operator or simply ??.  It returns the left-hand operand if that operand is not null; otherwise it returns the right hand operand.  The VB equivalent is also the If operator but with only 2 arguments, and the first argument must be a nullable reference type.

 

There is a decent explanation of how it works with VB.NET in this StackOverflow link.  See the answer by Code Maverick on Dec 19, 2013.

 

 

Read-only Auto-properties (or get-only property)

C# version 6.0 introduced read-only auto-properties, which might seem odd to ponder a get-only property:

 

Public property AFDatabase Database { get; } 

 

The above is equivalent to this in earlier versions of C#:

 

Private readonly AFDatabase _database;
Public AFDatabase Database { get { return _database; } }

 

In either case, the readonly property may only be assigned a value at class initialization or else within a class constructor.

 

The equivalent in VB.NET would be:

 

    Private ReadOnly _database As AFDatabase
    Public ReadOnly Property Database() As AFDatabase
        Get
            Return _database
        End Get
    End Property

 

Note that in VB both the property - and the private backing field - must be decorated as ReadOnly.

 

 

Expression-bodied members (property => expression)

A C# expression-bodied member may work for methods as well as properties, and are best reserved for a quick one-line piece of code.  Borrowing from a snippet above, an example would be:

 

Public AFDatabase Database => _database; 

 

Which is the equivalent of:

 

Public AFDatabase Database { get { return _database; } }

 

However, VB.NET does not support expression-bodied members so you would have to use the more verbose VB code referenced in the previous VB example.

 

 

Remainder or Modulus operator

In VB, the Mod operator is used to find the remainder:

 

Dim quartile As Integer = value Mod 4

 

In C#, the % operator does the same thing:

 

int quartile = value % 4;

 

 

Integer versus Floating Point Division

In VB, the / operator will always perform floating point division, even if both operands are integers.  To perform integer division with VB, one would use the \ operator.

 

Dim a = 5 / 2      ' a is a Double = 2.5
Dim b = 5 \ 2      ' b is an Int32 = 2

 

In C#, division on 2 integers always results in integer division.  There is no direct equivalent to VB's \ operator.  In order for floating point division to occur in C#, at least one of the operands should be a floating point value.

 

var a = 5 / 2;     // a is an Int32 = 2 since 5 and 2 are Int32
var b = 5 / 2.0;     // b is a double = 2.5 since 2.0 is a double
var c = (double)5 / 2;     // c is a double = 2.5 since integer 5 is cast to double before division

 

 

Related Links

For additional reading, you may want to check out this comparison of language features between C# 6.0 and VB 14.

 

There you go.  We've given a little love to our VB.NET community, which is probably larger than we think it is (you are certainly a quiet bunch).  You may let me know if you liked this in comments below.  Or you may comment if there are any C# translations you would like to see covered.  I will be glad to add it to this page.  As stated in the introduction, my intent is to empower you to read C# examples so that you are better equipped to translate to VB.

Asset Analytics Native Integration with MATLAB

 

March 21, 2018

9:00 AM (PT)/12:00 PM (ET)/6:00 PM (ES) See European Time Below

 

Link To Recording

 

In this PI Developers Club webinar, we offer a sneak preview of the AF 2018 release with the highly anticipated feature of native integration between Asset Analytics and MATLAB!  Panelists include Stephen Kwan, AF Product Manager, along with technical representatives from MathWorks, Tim Choo and Sachin Nikumbh, both on the MATLAB Production Server team.  Data scientists, developers, system integrators, or anyone interested in analytics is encouraged to attend and ask questions.

 

UPDATE: If you clicked the above link before Feb 14 at 2 PM US Pacific time, your registration may not have gone through.  If you have not received a confirmation email, please register again.

 

DAYLIGHT SAVING TIME WARNING:

North America transitioned to DST on March 11, and Europe will not transition until March 25.  This produces an offset error with our scheduling system.  The correct webinar times for Europe will be:

 

     5:00 PM CET or 4:00 PM GMT (or UTC)

 

For locales that do not observe DST, your scheduled times may also be incorrect.  Please refer to the GMT/UTC time above and adjust your local time accordingly.

 

Speakers:

  • Stephen Kwan, AF Product Manager
  • Tim Choo, MathWorks, Product Manager for MATLAB Production Server
  • Sachin Nikumbh, MathWorks, Engineering Manager for MATLAB Production Server
  • Hosted by Rick Davin, Sr. Technology Enablement Engineer

 

We'd love to hear your comments and questions here in the comments or in the PI Developers Club forum!

Some customers have the occasional but pronounced need to retrieve millions upon millions of recorded values for a given PIPoint.  There is no method directly with the PI AF SDK to address this need.  Virtually every data method within the SDK has 2 known limitations:

 

  1. An operations timeout should a single data call take too long to fulfill, and
  2. No more than ArcMaxCollect values may be returned in a single data call, where ArcMaxCollect is a tuning parameter on your PI Data Archive.

 

One should not modify ArcMaxCollect on a whim.  There's a reason why the defaults are what they are (PI Server 2012+ the default is 1.5 million, earlier versions are 150K).  You would not be increasing it just for you.  The change applies to all users.  How confident are you that your own users won't be trying to fetch 200 million data values at once?  There is a workaround that you may prudently use in your code in lieu of increasing ArcMaxCollect.

 

GitHub - Rick-at-OSIsoft/pipoint-getlargerecordedvalues: A workaround that removes the limitation of ArcMaxCollect, and …

 

The GitHub repository has code versions for C# and VB.NET.

 

In a nutshell, how does this workaround get past the 2 known limitations?  By retrieving the AFValues in pages of 100K values at a time.  This is well below the default value for ArcMaxCollect, and retrieving 100K values is easily satisfied within the operations timeout.  Remember: you would want to use the GetLargeRecordedValues method when you know you have PIPoint(s) with millions and millions of recorded values.

 

Usage

Given you have a PIPoint instance in an object named tag and a defined AFTimeRange in timeRange:

 

C# Example

var values = tag.GetLargeRecordedValues(timeRange);
foreach (var value in values)
 {
    // do something with value
}

 

 

VB.NET Example

Dim values = tag.GetLargeRecordedValues(timeRange)
For Each value As AFValue in values
    ' do something with value
Next 

 

 

Cautions and What to Avoid

The biggest caution to acknowledge is that you have massive amounts of data.  The traditional best practices such as using bulk PIPointList will no longer apply.  You are in a different world if you want to loop over a tag that has 200 million data values.  There are considerations well outside the scope of AF SDK.  Two notable instances are timing and memory.  Retrieving 200 million values will be done as quickly as possible, but you have to accept that it still takes some time to consume that much data.  So the concept of "fast" goes out the window.  And if you attempt to retrieve those values into a list or array, .NET will most likely give you an out-of-memory exception long before all the values are retrieved.

 

Therefore it's best to avoid memory-hogging calls such as LINQ's ToList() or ToArray().  The best performance is to consume the values as they are being streamed without any attempt to persist to an indexed collection.  Another performance killer is using Count().  I have seen a lot of traditional applications that attempt to show a value count before looping over the values.  Since its a streamed enumerable set that isn't persisted in memory, issuing a Count() has a very negative performance consequence of retrieving and counting all the AFValues.  Subsequent passes through a loop will then require the time-consuming process of retrieving the data a second time!  If you want to display a count in your logs because it's something nice to do, you should maintain your independent count while you are looping the first time, and then display that nice count at the end rather than the beginning.

 

C# Example

var values = tag.GetLargeRecordedValues(timeRange);
int count = 0;
foreach (var value in values)
{
     ++count;
    // do something with value
}
Console.WriteLine("{0} count = {1:N0}", tag.Name, count);

This question comes up quite a bit at PI Square.  I know of at least 5 different ways to find out my AF Client version.  In a nutshell,

 

  1. From the registry settings
  2. From Programs and Features
  3. From the file properties in File Explorer
  4. From PI System Explorer (if also loaded on the client machine)
  5. From custom code

 

Let's go over each of these.

 

Registry Settings

 

Run regedit.exe

Navigate to:    Computer\HKEY_LOCAL_MACHINE\SOFTWARE\PISystem\AF Client

Alternative:     Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\PISystem\AF Client

 

2017-07-19 09_07_09-Registry Editor.png

2017-07-19 09_06_00-Registry Editor.png

 

2017-07-19 09_07_29-Registry Editor.png

 

Programs and Features

Trying to run this may take you to Apps and features.  You could filter the list to AF and see something like:

 

2017-07-19 09_11_51-Settings.png

 

If you really want the Version  number, look to the upper right of the window for Programs and Features:

2017-07-19 09_13_40-Settings.png

 

You should then have a list where you may scroll to:

 

2017-07-19 09_12_46-Programs and Features.png

 

 

File Properties in File Explorer

 

Using File Explorer, navigate to:     %PIHOME%\AF\PublicAssemblies\4.0

 

In my example below, %PIHOME% is "C:\Program Files (x86)\PIPC" but this could be a different drive on your machine.

 

2017-07-19 09_15_33-4.0.png

 

Right-click on the file:     OSIsoft.AFSDK.dll

 

Click on the Details tab along the tab bar at the top.

 

     2017-07-19 09_16_27-OSIsoft.AFSDK.dll Properties.png

 

 

From PI System Explorer

 

Open PSE

From the menu bar at the top, click on Help

Click on About PI System Explorer ...

 

2017-07-19 09_17_46-About PI System Explorer.png

 

 

Custom AFSDK Code

 

From a C# application that has a reference to OSIsoft.AFSDK.dll, you can use:

 

Console.WriteLine((new OSIsoft.AF.PISystems()).Version);

 

Or if you already have a  using OSIsoft.AF;  statement, this shorter version will do:

 

Console.WriteLine((new PISystems()).Version);

 

Or if you despise one-liners, you may try:

 

var clientSDK = new PISystems();

Console.WriteLine( clientSDK.Version );

 

For VB.NET, you would use an  Imports OSIsoft.AF  statement, and this line of code:

 

Console.WriteLine((New PISystems()).Version)

 

And finally, if you're not a developer or don't have Visual Studio, all is not lost.  You may still try using Powershell.

 

[Reflection.Assembly]::LoadWithPartialName("OSIsoft.AFSDK")

$clientSDK = New-Object OSIsoft.AF.PISystems

Write-Host "AF Client Version:" $clientSDK.Version

 

To produce output such as:

 

GAC    Version        Location

---    -------        --------                                                                                                                        

True   v4.0.30319     C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\OSIsoft.AFSDK\v4.0_4.0.0.0__6238be57836698e6\OSIsoft.AFSDK.dll                             AF Client Version: 2.9.1.8106

 

 

There you go.  That's at least 5 different ways to get the AF Client Version number.  Are there any others that I may have missed?

The Advanced AF SDK lab at UC SF 2017 was on this very topic.  The material in this 9-part series follows much of that lab which showcases AFEventFrameSearch methods new to PI AF SDK 2.9.

 

Blog Series: Aggregating Event Frame Data

Part 1 - Introduction

Part 2 - Let's Start at the End

Part 3 - Setting up the App

Part 4 - Classical FindEventFrames

Part 5 - Lightweight FindObjectFields

Part 6 - Summary per Model

Part 7 - GroupedSummary Per Manufacturer

Part 8 - Compound AFSummaryRequest

Part 9 - Conclusion

 

Conclusion

We covered a lot of ground in this 9-part series because a lot of what was being covered was new ground.  You were introduced to 4 brand new methods to PI AF SDK 2.9.  Three of those were aggregation methods, which has got to be a much welcomed feature in AFEventFrameSearch.  And FindObjectFields might be the first one any developer checks out for its sheer speed and versatility not just for aggregation but for lightweight detail reporting.  To rehash what was covered:

 

  • Part 4 We showed the old way of doing things with the classical FindEventFrames.  This provided a baseline in performance for us to benchmark against the other new methods.
  • Part 5 We saw the new lightweight FindObjectFields method to return a skinny set of columns.  We looked at all 3 overloads of this method, each of which is concerned about casting first from generic object to the specific underlying type, followed perhaps by additional casting or converting to the type you desire.
  • Part 6 We saw the Summary method and discovered there is an event weighted overload as well as a general weighting overload to produce custom weightings beyond just time weighted.
  • Part 7 We saw how to use the GroupedSummary method to summarize with groupings, which allowed us to make fewer calls.
  • Part 8 We finished off with showing how to use a compound AFSummaryRequest to produce a 2-level grouping.  It was a tad bit complicated but did have great performance.

 

 

Tips to Remember

 

General:

  • Use CaptureValues() to see the performance benefits from server-side filtering.
  • Classes inherited from AFSearch, such as AFEventFrameSearch, now implement IDisposable starting with AF SDK 2.9.  You should consider wrapping your calls inside a using block, or else issue an explicit Close() when you are finished with your search activities.
  • When composing a query string, any values containing embedded blanks should be wrapped inside single or double quotes.
  • Your time string for output queries should be output using the "O" Round-trip specifier.
  • For best performance, you probably want to choose the method that makes the fewest calls to the server.

 

FindObjectFields:

  • If you are working with detail records, you should strongly consider including ID as one of the input fields.  That way if you ever have the need to perform further drilling into a specific event frame, you have the unique ID which can help you quickly locate the full event frame in question.
  • There is no weighted overload for FindObjectFields.  You would be expected to include your own weighting field (e.g. Duration or custom) in the returned set of values.
  • The underlying type of any attribute's value will be AFValue.
  • You may use fields or properties for your DTO class.
  • For the auto-mapped overload, you will have to use the ObjectField decorator to map the source attribute name that happens to begin with a "|" to your desired DTO field name.
  • For event frame properties and the auto-mapped overload, the default is to use the same property name for the mapping.  However, you may override this default.

 

AFSummaryRequest:

  • Is limited to no more than 2 levels of groupings.
  • For 1 grouping level, you should just use Summary or GroupedSummary depending upon your needs since these are less complicated and have a simpler packaging of the results.
  • Based on previous bullets, you probably would only use AFSummaryRequest precisely when you need 2-level groupings.

 

Async

Other than showing the method names in Part 1, we did not mention any of the async methods or show their usage.  But they are there and easily discernible by seeing a CancellationToken among the parameters.  Once LiveLibrary is active for PI AF Client 2017, you are encouraged to review the online help for:

 

  • BinnedSummaryAsync
  • FrequencyDistributionAsync
  • GroupedSummaryAsync
  • HistogramAsync
  • SummaryAsync (both event and general weighting overloads)

 

If you are curious as to why FindObjectFields does not have an async counterpart, keep in mind that FindObjectFields makes paged calls.  You are always capable of break your processing, which will stop requests for more pages of data.

 

Weighting

While the more natural weighting with the data archive is probably time weighted, event frames are not stored in the data archive but rather in SQL Server.  It should be no surprise that event weighting is the more natural or default weighting when dealing with event frames.  Out of the new AFEventFrameSearch aggregation methods, only Summary and SummaryAsync offer some other weighting overload other than event weighted.  You aren't limited to just time weighted as the lone alternative.  The new overloads are flexible to allow custom weightings.

 

FindObjectFields doesn't allow for weightings because it's not an aggregation method.  You may still use FindObjectFields but you should include the weighting field as part of the set of skinny columns to be returned.

 

Binning

I did not show any examples of binning.  That might be a future topic.  But you should be aware that these methods exist.

 

  • For discrete values such as integers or strings, FrequencyDistribution and FrequencyDistributionAync generates a <gasp> frequency distribution.
  • For floating point values, you would want to bin by ranges.  See Histogram or HistogramAsync for that.  Note that your requested ranges do not have to be in evenly-spaced intervals.
  • Why not have summaries by bins?  For this there is the BinnedSummary and BinnedSummaryAsync methods.

 

This is the End?

Or is it?  Don't be surprise if I do a future series about binning.

 

Thanks for reading the series.  I hope you enjoyed it.  Please remember to use this knowledge for good and not evil.

The Advanced AF SDK lab at UC SF 2017 was on this very topic.  The material in this 9-part series follows much of that lab which showcases AFEventFrameSearch methods new to PI AF SDK 2.9.

 

Blog Series: Aggregating Event Frame Data

Part 1 - Introduction

Part 2 - Let's Start at the End

Part 3 - Setting up the App

Part 4 - Classical FindEventFrames

Part 5 - Lightweight FindObjectFields

Part 6 - Summary per Model

Part 7 - GroupedSummary Per Manufacturer

Part 8 - Compound AFSummaryRequest

       Part 9 - Conclusion

 

Let's Make Only ONE Call

I'm going to assume that you haven't jumped blindly into this topic for the first time.  It should be a safe bet that you've read Parts 6 and 7 regarding Summary and GroupedSummary respectively.  It has boiled down to this: I don't want to make repeated calls, because we know each call to the server takes a performance hit.  Summary needed to be called 3 times for this use case, and GroupedSummary twice.  I want to issue one and only one call.  Plus I don't want to have know to know all Manufacturers or Models before I query for them.  Maybe I absolutely don't know that info ahead of time and that's why I'm doing this search in the first place.

 

Which brings us to AFSummaryRequest, which will perfectly fit the bill.  Technically it is not a method to the AFEventFrameSearch or AFSearch classes.  It's a concrete method in the OSIsoft.AF.Data namespace that implements the abstract method AFAggregateResult.  Other aggregation methods, like Summary and GroupedSummary, call AFSummaryRequest themselves for the most common method signatures that need a 1-level summary.  As we want a 2-level grouping, AFSummaryResult is the best choice for our use case.  With that in mind, we should be forgiving that the code to use it is a bit more complicated.  Note that AFSummaryRequest limits you to no more than 2 groupings.

 

public void GetSummaryByMfrAndModel(StatsTracker summary, AFDatabase database, IList<AFSearchToken> tokens)
{
    using (var search = new AFEventFrameSearch(database, "Compound Request", tokens))
    {
        //Opt-in to server side caching
        search.CacheTimeout = TimeSpan.FromMinutes(5);

        //While we eventually want an average, it will be calculated from Total and Count.
        var desiredSummaryTypes = AFSummaryTypes.Count | AFSummaryTypes.Total;

        //Here we make only 1 call to the server but we must build a compound AFSummaryRequest.
        //The GroupBy order is opposite than what you would intutively think: Model and then Manufacturer.
        //First we bundle the AFSummaryRequest.
        var compoundRequest = new AFSummaryRequest("Duration", desiredSummaryTypes)
                                    .GroupBy<string>("|Model")
                                    .GroupBy<string>("|Manufacturer");

        //We send the request as a member of IEnumberable<AFAggregateRequest>.
        //Since we pass a collection of one member, we get a collection of one member back.
        //So we grab that one member and cast it appropriately.
        var aggResult = search.Aggregate(new[] { compoundRequest })[0] as AFCompoundPartitionedResult<string, string>;

        //Unwrap the results.
        foreach (var kvp in aggResult.PartitionedResults)
        {
            var mfr = kvp.Key.PrimaryPartition;
            var model = kvp.Key.SecondaryPartition;

            var summaries = kvp.Value;

            var totalVal = summaries[AFSummaryTypes.Total];
            var countVal = summaries[AFSummaryTypes.Count];
            var stats = new DurationStats();

            if (countVal.IsGood)
            {
                stats.Count = countVal.ValueAsInt32();
                if (totalVal.IsGood)
                {
                    stats.TotalDuration = ((AFTimeSpan)totalVal.Value).ToTimeSpan();
                }
                summary.AddToSummary(mfr, model, stats.TotalDuration, stats.Count);
            }
        }
    }
}

 

 

There you have it.  Not exactly pretty.  But you can't argue with the results since this was the fastest method for my use case.

 

I want to reiterate that you may have no more than 2 levels of grouping for AFSummaryRequest.  And review lines 15-16 to see that the grouping is inside to outside.  That is if we want to group by Manufacturer first and Model second then when we compose the AFSummaryRequest the first GroupBy is by Model and the second GroupdBy is Manufacturer.

 

Metrics Comparison (from Part 2)

The numbers below are from a 2-core VM using Release x64 Mode.  The smaller values are better.  Caution that we sometimes have a difference in UOM between MB and KB, but I will bold KB when needed.

 

Resource Usage:

Values displayed are in MB unless noted otherwise

Method

Total GC Memory (MB)

Working Set Memory (MB)Network Bytes Sent
Network Bytes Received
FindEventFrames145.48257.089.13 MB190.08 MB
FindObjectFields1.2865.555.00 KB3.68 MB
Summary2.5455.358.58 KB261.81 KB
GroupedSummary9.8664.286.24 KB1.98 MB
AFSummaryRequest7.2965.365.00 KB3.68 MB

 

Performance:

MethodClient RPC CallsClient Duration (ms)Server RPC CallsServer Duration (ms)Elapsed Time
FindEventFrames12063337.011039118.102:27.8
FindObjectFields105360.8114547.600:06.0
Summary159484.6169310.900:10.1
GroupedSummary125527.2134938.500:06.2
AFSummaryRequest102992.2102222.200:03.7

 

We are at the same spot where we ponder if AFSummaryRequest is the fastest of the methods.  It appears to be so for my particular use case of reporting by Manufacturer and Model.  If we were to ignore my use case, and compare AFSummaryRequest to Part 6's Bonus Summary and Part 7's Bonus GroupedSummary, both of which issued one call on the same data set, here's how those metrics line up:

 

Metric
SummaryGroupedSummaryCompound AFSummaryRequest
Total GC Memory (MB)4.4812.247.29
Working Set Memory (MB)52.4861.8465.36
Network Bytes Sent4.77 KB4.85 KB5.00 KB
Network Bytes Received260.02 KB1.98 KB3.68 MB
Client RPC Calls101010
Client Duration (ms)534.01913.72992.2
Server RPC Calls101010
Server Duration (ms)353.81472.82222.2
Elapsed Time00:01.100:02.600:03.7

 

I know I sound like a broken record but the same advice applies: think through your application and pick the right tool for the right job.  Which one gives the correct results with making the fewest calls to the server?

 

Up Next: End of the Series

We conclude this 9-part series naturally enough with post that I call Part 9!

The Advanced AF SDK lab at UC SF 2017 was on this very topic.  The material in this 9-part series follows much of that lab which showcases AFEventFrameSearch methods new to PI AF SDK 2.9.

 

Blog Series: Aggregating Event Frame Data

Part 1 - Introduction

Part 2 - Let's Start at the End

Part 3 - Setting up the App

Part 4 - Classical FindEventFrames

Part 5 - Lightweight FindObjectFields

Part 6 - Summary per Model

Part 7 - GroupedSummary per Manufacturer

Part 8 - Compound AFSummaryRequest

Part 9 - Conclusion

 

Query One Level Up

GroupedSummary and Summary have something in common.  They both require a priori knowledge of what you will be summarizing before you can actual summarize it.  For Summary, this required summarizing per the inner loop of Model, which required 3 calls (one for each of our 3 models).  For GroupedSummary, we can reduce the number of calls to the server by making a call on the the outer loop per Manufacturer.  While we do need to know the manufacturers to filter upon for GroupedSummary, we don't need to know the models.

 

We will do something similar as we did the Summary in Part 6:

  • Get a priori list of Manufacturers
  • Build a new token for the given Manufacturer
  • Issue the GroupedSummary call
  • Peel back the results to feed to my DurationStats and StatsTracker

 

public void GetSummaryByMfrAndModel(StatsTracker summary, AFDatabase database, IList<AFSearchToken> baseTokens)
{
    //Absolutely critical to have a priori list of Manufacturers
    var mfrList = summary.Keys.ToList();

    foreach (var mfr in mfrList)
    {
        var tokens = baseTokens.ToList();
        tokens.Add(new AFSearchToken(AFSearchFilter.Value, mfr, "|Manufacturer"));

        using (var search = new AFEventFrameSearch(database, "GroupedSummary Example", tokens))
        {
            //Opt-in to server side caching
            search.CacheTimeout = TimeSpan.FromMinutes(5);

            //While we eventually want an average, it will be calculated from Total and Count.
            var desiredSummaryTypes = AFSummaryTypes.Count | AFSummaryTypes.Total;
            var groupedField = "|Model";
            var summaryField = "Duration";

            var perMfr = search.GroupedSummary(groupedField, summaryField, desiredSummaryTypes);

            foreach (var grouping in perMfr.GroupedResults)
            {
                var model = grouping.Key.ToString();
                var totalVal = grouping.Value[AFSummaryTypes.Total];
                var countVal = grouping.Value[AFSummaryTypes.Count];

                var stats = new DurationStats();

                if (countVal.IsGood)
                {
                    stats.Count = countVal.ValueAsInt32();
                    if (totalVal.IsGood)
                    {
                        stats.TotalDuration = ((AFTimeSpan)totalVal.Value).ToTimeSpan();
                    }
                    summary.AddToSummary(mfr, model, stats.TotalDuration, stats.Count);
                }
            }
        }
    }
}

 

While we did have some similarities, where we invoked the server call is very different.  Here with GroupedSummary, we make the call in our outer loop so we will have less trips to the server.  For Summary in Part 6, we made the call inside the inner loop.  Also the returned results are quite different, though the concept of what we do with them is the same: peel back the returned dictionary accordingly and have them conform to my output objects.

 

The metrics shown in Part 2 would make you think GroupedSummary is faster than Summary.  In general, this is really not true.  For my particular use case it is true, but that's because there are more server calls that my app is making to Summary than for GroupedSummary.  Do not walk away thinking you would want to avoid Summary.  Instead, you should not hesitate to use it for a better use case.

 

Metrics Comparison (from Part 2)

The numbers below are from a 2-core VM using Release x64 Mode.  The smaller values are better.  Caution that we sometimes have a difference in UOM between MB and KB, but I will bold KB when needed.

 

Resource Usage:

Values displayed are in MB unless noted otherwise

Method

Total GC Memory (MB)

Working Set Memory (MB)Network Bytes Sent
Network Bytes Received
FindEventFrames145.48257.089.13 MB190.08 MB
FindObjectFields1.2865.555.00 KB3.68 MB
Summary2.5455.358.58 KB261.81 KB
GroupedSummary9.8664.286.24 KB1.98 MB
AFSummaryRequest7.2965.365.00 KB3.68 MB

 

Performance:

MethodClient RPC CallsClient Duration (ms)Server RPC CallsServer Duration (ms)Elapsed Time
FindEventFrames12063337.011039118.102:27.8
FindObjectFields105360.8114547.600:06.0
Summary159484.6169310.900:10.1
GroupedSummary125527.2134938.500:06.2
AFSummaryRequest102992.2102222.200:03.7

 

 

BONUS: GroupedSummary Using ONE Call

Let's come up with a better use case where we only need to issue one call.  Allow me once again to temporarily change my requirements on the end report, purely for illustration purposes.  Let's imagine I no longer am interested in the average and counts per manufacturer and model.  Instead I want to summarize the same data set as a whole but I only care about models.  In this new scenario I have absolutely no concern about manufacturers.  The new report would look like:

 

Manufacturer  Model            Count Avg Duration

------------- ------------ --------- ----------------

<Any>         DQ-M0L           8,136 03:53:21.4859882

<Any>         Nimbus 2000      1,499 03:44:28.8192128

<Any>         SWTG-3.6        13,678 03:53:35.3165667

------------- ------------ --------- ----------------

            1            3    23,313

 

For the code to do that, I don't need to initialize my summary object to populate itself from an AFTable.

 

//I still use StatsTracker for conformity but we don't need to initialize this from our AFTable  
var summary = new StatsTracker();  

 

That is the summary instance I will pass to my new method, which now eliminates 1 level of looping.  However, I will need to later sort the results so I am going to pass summary by ref.

 

public void GetSummaryByMfrAndModel(ref StatsTracker summary, AFDatabase database, IList<AFSearchToken> tokens)
{
    summary = new StatsTracker();

    //In this bonus test, we want to only issue one GroupedSummary call.
    //Rather than rigorously issue separate calls per Manufacturer, I instead isssue one call on grouped on Model for all Manufacturers.
    //The downside is I lose the individual Manufacturer names.

    using (var search = new AFEventFrameSearch(database, "GroupedSummary Example", tokens))
    {
        //Opt-in to server side caching
        search.CacheTimeout = TimeSpan.FromMinutes(5);

        //While we eventually want an average, it will be calculated from Total and Count.
        var desiredSummaryTypes = AFSummaryTypes.Count | AFSummaryTypes.Total;
        var groupedField = "|Model";
        var summaryField = "Duration";

        var groupedSummary = search.GroupedSummary(groupedField, summaryField, desiredSummaryTypes);

        foreach (var grouping in groupedSummary.GroupedResults)
        {
            var model = grouping.Key.ToString();
            var totalVal = grouping.Value[AFSummaryTypes.Total];
            var countVal = grouping.Value[AFSummaryTypes.Count];

            var stats = new DurationStats();

            if (countVal.IsGood)
            {
                stats.Count = countVal.ValueAsInt32();
                if (totalVal.IsGood)
                {
                    stats.TotalDuration = ((AFTimeSpan)totalVal.Value).ToTimeSpan();
                }
                summary.AddToSummary("<Any>", model, stats.TotalDuration, stats.Count);
            }
        }
    }
    //Sort the results.  They have the same Manufacturer "<Any>" but the Models will be alphabetical.
    summary = summary.SortByKeys();
}

 

I am expecting to get back 3 rows, so I sort the results before returning from my method.  Let's review the metrics with making that one bonus GroupedSummary call and let's compare that to making one bonus Summary call.

 

Metric
SummaryGroupedSummary
Total GC Memory (MB)4.4812.24
Working Set Memory (MB)52.4861.84
Network Bytes Sent4.77 KB4.85 KB
Network Bytes Received260.02 KB1.98 KB
Client RPC Calls1010
Client Duration (ms)534.01913.7
Server RPC Calls1010
Server Duration (ms)353.81472.8
Elapsed Time00:01.100:02.6

 

For the right use cases, both of these methods are extremely fast, and should be welcome in your tool bag.  Don't shy away from using Summary or GroupedSummary because one table shows sluggish performance.  Use your noggin and pick the right tool for the right job.  The emphasis should be on producing the desired results with the fewest trips to the server.

 

 

Up Next: Name That Tune In One Call

Putting aside the bonus section, let's return to the original report by Manufacturer and Model.  To repeat the pattern you should have witnessed in the progression of each method in parts 4 - 7.

Part 4: Heavy detail records

Part 5: Light detail records

Part 6: Aggregation per inner loop

Part 7: Aggregation per outer loop

 

For each successive example we were making fewer calls or receiving fewer records.  The good news is that Summary and GroupedSummary are downright miserly on resources consumed.  The bad news is this whole a priori knowledge requirement as well as making multiple calls which degrades performance.  Wouldn't it be great to be able to make only ONE call and to do so without knowing what the heck we want to summarize in the first place?  That will be covered in Part 8.

The Advanced AF SDK lab at UC SF 2017 was on this very topic.  The material in this 9-part series follows much of that lab which showcases AFEventFrameSearch methods new to PI AF SDK 2.9.

 

Blog Series: Aggregating Event Frame Data

Part 1 - Introduction

Part 2 - Let's Start at the End

Part 3 - Setting up the App

Part 4 - Classical FindEventFrames

Part 5 - Lightweight FindObjectFields

Part 6 - Summary per Model

Part 7 - GroupedSummary per Manufacturer

Part 8 - Compound AFSummaryRequest

Part 9 - Conclusion

 

A Bona Find Aggregation Method

We've covered 2 different ways to produce our summaries but neither of those approaches used a true aggregation method.  Instead they both returned detailed rows where we had to apply our own custom aggregation.  In the case of FindEventFrames, the detail rows were heavyweight event frames.  In the case of FindObjectFields, the detail rows were data container records.  For this brand-new AFEventFrameSearch.Summary, we will getting back an aggregation and you will note what is sent from across the network to us (as recorded in Network Bytes Received) is only a teeny tiny bit memory.

 

Summary requires a priori knowledge of what you want to be summarizing.  In our case, we want to summarize by Manufacturer and Model so we must know all the Manufacturers and Models we wish to summarize before we can actually summarize them.  This was discussed in Part 3.  I chose to read an AFTable and populate a model-keyed dictionary inside a manufacturer-keyed dictionary.  You are in no way restricted to do the same.  You are encouraged to find the solution that best fits your own database and needs, and I welcome you sharing your creative solutions back on PISquare one day.

 

You may also remember that in Part 2 the Summary method seemed to be the slowest of the new methods.  It really isn't.  The problem is I am trying to have all these new methods produce the exact same output, so making multiple calls on Models within Manufacturers is really not the best use case of Summary.  On the other hand, it is a very good example of syntax on how to issue a Summary call, as well as what to do with the results that come back from that call.  Let's focus on that as the main lesson to be learned in the code below.

 

The Highlights

My a priori requirement is taken care of by my dictionary in a dictionary.  However, I will need to get an independent list of the keys to the dictionaries.

 

I will also need to issue a Summary per Model.  This means I must use the same base tokens or query that I used for our previous examples, and modify them for each Summary call.  Again, I could take the lazy or sloppy approach and only worry about Model since my current data set had 3 unique models.  But that code could break in the future if I were ever to add a Model with the same name to a different Manufacturer.  Instead, I will take a rigorous approach and truly query by Manufacturer and then Model.

 

All of this is to say that I will be looping first over Manufacturers, and then secondly over the Models.  Then I will modify the tokens or query string for inside the inner loop.  Because I will modify the input tokens/query repeatedly, I have renamed the input argument from "tokens" to be "baseTokens".

 

The final steps will be to receive the results from Summary, and unwrap them to conform to my DurationStats and StatsTracker objects discussed in Part 3.

 

public void GetSummaryByMfrAndModel(StatsTracker summary, AFDatabase database, IList<AFSearchToken> baseTokens)
{
    //Absolutely critical to have a priori list of Manufacturers and Models
   //Get independent list of Manufacturers
    var mfrList = summary.Keys.ToList();

    foreach (var mfr in mfrList)
    {
        //Get independent list of Models for given Manufacturer
        var modelSubList = summary[mfr].Keys.ToList();

        foreach (var model in modelSubList)
        {
            //Safest Technique: via tokens.  
            //Get independent copy to modify inside loop
            var tokens = baseTokens.ToList();
            tokens.Add(new AFSearchToken(AFSearchFilter.Value, mfr, "|Manufacturer"));
            tokens.Add(new AFSearchToken(AFSearchFilter.Value, model, "|Model"));

            //Starting with AF 2.9, AFSearch implements IDisposable
            using (var search = new AFEventFrameSearch(database, "Summary Example", tokens))
            {
                //Opt-in to server side caching
                search.CacheTimeout = TimeSpan.FromMinutes(5);

                var desiredSummaryTypes = AFSummaryTypes.Count | AFSummaryTypes.Total;

                var perModel = search.Summary("Duration", desiredSummaryTypes);

                var totalVal = perModel.SummaryResults[AFSummaryTypes.Total];
                var countVal = perModel.SummaryResults[AFSummaryTypes.Count];

                var stats = new DurationStats();

                //Unwrap the returned results as needed
                if (countVal.IsGood)
                {
                    stats.Count = countVal.ValueAsInt32();
                    if (totalVal.IsGood)
                    {
                        stats.TotalDuration = ((AFTimeSpan)totalVal.Value).ToTimeSpan();
                    }
                    summary.AddToSummary(mfr, model, stats.TotalDuration, stats.Count);
                }
            }
        }
    }
}

 

 

The above example uses query tokens.  I mentioned in Part 3 you could have used a query string.  If you wanted a string instead of tokens, I would have an input string argument named "baseQuery" containing:

 

$"AllDescendants:{allDescendants} Template:'{templateName}' Start:>={startTime.ToString("O")} End:<={endTime.ToString("O")} '{attrPath}':>={attrValue}"

 

Then inside the inner loop of Model, lines 16-18 would become:

 

var query = $"{baseQuery} |Manufacturer:'{mfr}' |Model:'{model}'";

 

Note the use of single quotes around {mfr} and {model}.  For model this is an absolute must have with our data, because we do have one model ("Nimbus 2000") that contains an embedded blank in its name.  For mfr, we did this for future proofing in case we ever add a Manufacturer with a blank in its name.  You may recall in Part 3 I cautioned that if it's a name or a path that the safest route is to wrap it in single quotes.  This helps make your code less fragile.

 

Event versus Time Weighting

For the Summary overload we used in the code above, the result is event weighted.  Normally with data coming from a process historian, I tend to first think in terms of time weighted values.  But we're working with event frames here, so my inclination is that the values are event weighted, that is there is a value associated with the entire event frame.  But that's me.  But you may be interested in getting back a time weighted number, so you might ask "Is there a time weighted overload?"

 

The trick answer is No.  While it's true there is not a Summary overload that allows you to pass an AFCalculationBasis.TimeWeighted enumeration, there is an overload that accepts a general weighting field as the 3rd argument.  This means you aren't restricted to either event weightings or time weightings, but you may pass a custom weighting!  The restriction here is that you pass the name of the weighting field, and that field must belong to the event frame.  For a time weighted weighting, the name of the weighting field could be "Duration" or perhaps you have another time span attribute defined on your event frame.

 

 

Metrics Comparison (from Part 2)

The numbers below are from a 2-core VM using Release x64 Mode.  The smaller values are better.  Caution that we sometimes have a difference in UOM between MB and KB, but I will bold KB when needed.

 

Resource Usage:

Values displayed are in MB unless noted otherwise

Method

Total GC Memory (MB)

Working Set Memory (MB)Network Bytes Sent
Network Bytes Received
FindEventFrames145.48257.089.13 MB190.08 MB
FindObjectFields1.2865.555.00 KB3.68 MB
Summary2.5455.358.58 KB261.81 KB
GroupedSummary9.8664.286.24 KB1.98 MB
AFSummaryRequest7.2965.365.00 KB3.68 MB

 

Performance:

MethodClient RPC CallsClient Duration (ms)Server RPC CallsServer Duration (ms)Elapsed Time
FindEventFrames12063337.011039118.102:27.8
FindObjectFields105360.8114547.600:06.0
Summary159484.6169310.900:10.1
GroupedSummary125527.2134938.500:06.2
AFSummaryRequest102992.2102222.200:03.7

 

 

Caution again about CaptureValues()

The performance is realized because all of my event frames have captured values.  This means filtering by wind velocity, manufacturer, and model - all of which are attributes - is performed on the server.  That greatly reduces the network load.

 

I don't know you consider it a good thing or a bad thing that the code above also works if you have not captured values.  Yes it will work.  But it may be as slow or slower than FindEventFrames.

 

Use the Right Tool for the Right Job

The above example shows correct syntax and how to peel back the results as you need.  Admittedly, a 2-level summary is not a good use case for Summary.  I would absolutely reject using this method if I had to query model 5 times or more (that is make 5 or more invocations of Summary).  I may possibly consider it if I knew I had less than 5 models but would likely reject it as the method of choice unless I only had to make 1 or 2 calls.  With 1 call, it's a no-brainer: Summary is the right choice.  Would you like proof?

 

BONUS: Summary Using ONE Call

Let's come up with a better use case where we only need to issue one call.  Allow me to temporarily (just for illustration purposes) change my requirements on the end report.  I no longer am interested in the average and counts per manufacturer and model.  Instead I want to summarize over the exact same data set as a whole.  The new report would look like:

 

Manufacturer  Model            Count Avg Duration

------------- ------------ --------- ----------------

<All>         <All>           23,313 03:52:55.3506627

------------- ------------ --------- ----------------

            1            1    23,313

 

I get the exact same record count as the original report in Part 2, which shouldn't be surprising since I use the exact same filter.  For the code to produce the above report, I don't need to initialize my summary object to populate itself from an AFTable.

 

//I still use StatsTracker for conformity but we don't need to initialize this from our AFTable
var summary = new StatsTracker();

 

That is the summary instance I will pass to my new method, which now eliminates 2 levels of looping.

 

public void GetSummaryByMfrAndModel(StatsTracker summary, AFDatabase database, IList<AFSearchToken> tokens)
{
    //Starting with AF 2.9, AFSearch implements IDisposable
    using (var search = new AFEventFrameSearch(database, "Better Summary Use Case", tokens))
    {
        //Opt-in to server side caching
        search.CacheTimeout = TimeSpan.FromMinutes(5);

        var desiredSummaryTypes = AFSummaryTypes.Count | AFSummaryTypes.Total;

        var oneCallSummary = search.Summary("Duration", desiredSummaryTypes);

        var totalVal = oneCallSummary.SummaryResults[AFSummaryTypes.Total];
        var countVal = oneCallSummary.SummaryResults[AFSummaryTypes.Count];

        var stats = new DurationStats();

        if (countVal.IsGood)
        {
            stats.Count = countVal.ValueAsInt32();
            if (totalVal.IsGood)
            {
                stats.TotalDuration = ((AFTimeSpan)totalVal.Value).ToTimeSpan();
            }
            summary.AddToSummary("<All>", "<All>", stats.TotalDuration, stats.Count);
        }
    }
}

 

Since I get back only 1 row, there is no need to sort the results.  Let's review the metrics with making that one call:

Metric
Summary
Total GC Memory (MB)4.48
Working Set Memory (MB)52.48
Network Bytes Sent4.77 KB
Network Bytes Received260.02 KB
Client RPC Calls10
Client Duration (ms)534.0
Server RPC Calls10
Server Duration (ms)353.8
Elapsed Time00:01.1

 

Wow, that IS FAST!!!

 

 

Up Next: Reduce the Calls to the Outer Loop

Putting aside the bonus section, let's return to the original report by Manufacturer and Model.  We had to drill down into 2 loops to build our Summary call per Model.  In Part 7 we reduce the number of calls by making a call in the outer loop per Manufacturer.  We will do this with the GroupedSummary method.  See you in Part 7.

Filter Blog

By date: By tag: