Aggregating Event Frame Data Part 3 of 9 - Setting up the App

Blog Post created by rdavin Employee on May 10, 2017

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


Event Frame Template

I'm going to skip over creating a nice warm-hearted, make-believe story.  This is an Advanced AF SDK series.  My audience is accustomed to reading Help files in all their minimalist glory.  This is a step up from that.  Besides this part is going to be one of the longest in the series, so let's jump right into things:


Here's what my event frame template looks like:

Template: Low Efficiency inherited from Condition Alert

EF Template.png

The report in Part 2 uses the Duration property and summarizes by the Manufacturer and Model attributes.  We will also filter on an attribute: we are only interested in when the Wind Velocity at Start is greater than or equal to 11 miles per hour.  IMPORTANT: to be able to efficiently filter on an attribute, we need to issued CaptureValues() on all the event frames of interest.  Otherwise any performance benefit will be lost.


CRITICAL: in order to filter on a numeric attribute, the attribute's data type must be a floating point.  It cannot be integral (yet) but may change in future versions.  My Wind Velocity at Start is defined as a Single, and there is a Wind Velocity that is also a Single back on the primary referenced element.  It's perfectly okay that back in the data archive the underlying PI point is an Int32.  What matters to AFEventFrameSearch is the filtered attribute must be floating point.  Another benefit: if you ever plan to allow any UOM conversion in the future, floating point values convert with better precision that integers.


Analysis Template to Generate Event Frames


Note the analysis has 3 possible Start triggers and each trigger has a different Severity level.  What happens if a Warning event occurs but later degrades to be Major before the End trigger is satisfied?  This "one" low efficiency event would create at least 3 event frames:

  1. A root level frame for the entire duration of the event with the worst Severity
  2. A child event frame for the initial Warning
  3. A child event frame for the subsequent Major breech


Sample of Compound Event

Not every event triggered will generate multiple event frames.  Some will.  Some won't.  Keep things interesting.  Here's a sample of what that might look like:


Root Level.png


You will discover later that the query I use with AFEventFrameSearch will only look at root level event frames.  I want to count the total occurrences and duration of the "one" low efficiency event, so I must not double-dip and count the child frames.


The Query

I'm going to show 3 different ways of generating a query.  There are 2 main overloads to AFEventFrameSearch I could use.  One accepts a list of AFSearchTokens.  The other takes a string.  For the one that takes a string, I will build that string 2 ways, one as a long, wide string, and the other using StringBuilder.  For all these varied techniques, I will use the same base filter variables:


bool allDescendants = false;
string templateName = "Low Efficiency";
DateTime startTime = new DateTime(2017, 1, 21, 0, 0, 0, DateTimeKind.Local);
DateTime endTime = new DateTime(2017, 1, 22, 0, 0, 0, DateTimeKind.Local);
//CRITICAL the attrName includes a prefixed "|".
string attrPath = "|Wind Velocity at Start";
int attrValue = 11;


Let's assume that I also previously have declared and set an AFDatabase object named "database".


A few sections back I said a filtered numeric attribute must have a floating point data type.  But my attrValue variable is declared as an int (Int32).  This is also okay.  Ultimately I must serialize everything to a string, so be it an int (11) or a Single (11.0F), it will eventually be converted to string ("11").


As discussed in previous section, I only want to look at root level event frames.  Another way of saying that is I am not interested in all descendants.  The template name should be understood.  I care about only one specific day, which is to say it starts at midnight of one date and ends at midnight on the following date.  One key distinction of strings passed is whether it refers to an attribute or a property.  The important way to make that distinction is to prefix any attribute name with "|".  Attribute names that contain blanks should be wrapped in single or double quotes.  You should treat the "|" as part of the name (it's a path actually) so the "|" would also be inside the single or double quotes.



Using the above variables, here's what I would do to create a search object using tokens:


var tokens = new List<AFSearchToken>();
tokens.Add(new AFSearchToken(AFSearchFilter.AllDescendants, AFSearchOperator.Equal, allDescendants.ToString()));
tokens.Add(new AFSearchToken(AFSearchFilter.Template, AFSearchOperator.Equal, templateName));
tokens.Add(new AFSearchToken(AFSearchFilter.Start, AFSearchOperator.GreaterThanOrEqual, startTime.ToString("O")));
tokens.Add(new AFSearchToken(AFSearchFilter.End, AFSearchOperator.LessThanOrEqual, endTime.ToString("O")));
//Attribute values are special case:
tokens.Add(new AFSearchToken(AFSearchFilter.Value, AFSearchOperator.GreaterThanOrEqual, attrValue.ToString(), attrPath));

using (var search = new AFEventFrameSearch(database, "tokens example", tokens))
    //Get ready to rumble


For lines 04 and 05, I use the DateTime Round Trip Specifier of "O" to generate an ISO 8601 compliant time string that is (a) culturally neutral and (b) unambiguous as to its instance in time.  Of side note, the VM has its time zone set to UTC.


Everywhere else anything that should be a string is given an appropriate ToString().  A quick sanity check by dumping the query to my console produces:

Base Query Tokens:


    Template:'Low Efficiency'



    '|Wind Velocity at Start':>=11


You should not that the AFSearchToken automatically inserted single quotes where there should be embedded blanks.  See 'Low Efficiency' and '|Wind Velocity at Start'.  Another advantage of the "O" specifier for the Round Trip time string is that the time string does not contain blanks.


As mentioned in Part 1, AFSearch now implements IDisposable so in line 09 a using block is used.


Wide String

The same query could have just as easily have been one wide string, but it is now my responsibility as developer to wrap values in single quotes wherever there is a possibility of an embedded blank.  The burden is on you.


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

using (var search = new AFEventFrameSearch(database, "string example", query))
    //Get ready to rumble


Let's consider any value that I absolutely know would never have an embedded blank when ToString() is applied:

  • a bool, so I don't worry about allDescendants
  • an int, so I don't worry about attrValue
  • any DateTime output with Round Trip specifier "O"


In general, any String variable that could be a name or path should be wrapped in single quotes for safety.  Out of sheer caution, consider anything else to be a BIG MAYBE.  Any such MAYBE's should be wrapped in single quotes.  Here again see '{templateName}' and '{attrPath}'.



Once again the same query could have also been constructed using StringBuilder, particularly if the string would be very wide.  Here again the burden is on you as the developer to wrap values in single quotes when such values have the possibility to contain an embedded blank.


var builder = new StringBuilder();
//Be sure to include leading blank for each line below below.
builder.Append($" Template:'{templateName}'");
builder.Append($" Start:>={startTime.ToString("O")}");
builder.Append($" End:<={endTime.ToString("O")}");
builder.Append($" '{attrPath}':>={attrValue}");

using (var search = new AFEventFrameSearch(database, "string example", builder.ToString()))
    //Get ready to rumble


There you have it.  Three different ways you may build a query to be passed to AFEventFrameSearch.  Which way is the best way?  That's up to you.  It's purely a matter of personal preference, as all 3 ways produce equivalent filters.


Adding Conformity to 5 Very Different Methods

I mentioned that how we interact with each of our 5 methods will be different per app.  This entails what we do in order to make each call, and later what we do with the different objects returned from each call.  In order to have some uniformity among the apps, especially since I don't want to make 5 different methods to produce the pretty report shown in Part 2, I will have the apps conform to what will be output.  What I settled upon was I would have a dictionary inside a dictionary.  The outer key will be the Manufacturer name.  The inner key will be the Model name.  The value of the inner key will be this custom structure:


public struct DurationStats
    public TimeSpan TotalDuration { get; set; }

    //Eventually some customers may have well over 2 billion event frames!
    //When that day comes, a Int64 (long) should be used for Count.
    public int Count { get; set; }

    public TimeSpan AverageDuration => Count > 0 ? TimeSpan.FromTicks(TotalDuration.Ticks / Count) : new TimeSpan();


Ok, so I want the average duration.  But I also want the count of events.  I chose to track the total duration and along with the count I will calculate the average duration.  This makes it easier on my first 2 methods (FindEventFrames and FindObjectFields) which will return detail rows of each event frame.  I don't necessarily need to track to total duration for the last 3 methods (Summary, GroupedSummary, AFSummaryRequest) and could just have easily gone directly with average duration.  But again, for conformity across the apps, I will be tracking total duration and calculate the average myself.


In order to organize each stats by Manufacturer and Model, I use the dictionary inside a dictionary as shown below.  I offer a few overloads to updating the stats, and again you will note that I apply a rigorous approach to truly segment each Model within each Manufacturer.  Included in this class is how the pretty report is generated.


// Dictionary of (1) Manufacturer with innder Dictionary (2) Model, and (3) Stats
public class StatsTracker : Dictionary<string, Dictionary<string, DurationStats>>
    public void AddToSummary(string mfr, string model, DurationStats stats)
        AddToSummary(mfr, model, stats.TotalDuration, stats.Count);

    public void AddToSummary(string mfr, string model, AFTimeSpan duration, int countIncrement)
        AddToSummary(mfr, model, duration.ToTimeSpan(), countIncrement);

    public void AddToSummary(string mfr, string model, TimeSpan duration, int countIncrement)
        //Add to appropriate summary, first by Manufacturer ...
        Dictionary<string, Support.DurationStats> inner;
        if (!TryGetValue(mfr, out inner))
            inner = new Dictionary<string, Support.DurationStats>();

        //... and secondly by Model
        Support.DurationStats stats;
        if (!inner.TryGetValue(model, out stats))
            stats = new Support.DurationStats();

        //Update the stats
        stats.Count += countIncrement;
        stats.TotalDuration = stats.TotalDuration.AddDuration(duration);
        inner[model] = stats;
        this[mfr] = inner;

    public override string ToString() => DisplaySummary(indent: 0);
    public string ToString(byte indent) => DisplaySummary(indent);

    public string DisplaySummary(byte indent = 0)
        var totalMfrs = 0;
        var totalModels = 0;
        var totalFrames = 0;
        var pad = new string(' ', indent);
        var builder = new StringBuilder();
        builder.AppendLine($"{pad}{"Manufacturer",-13} {"Model",-12} {"Count",9} Avg Duration");
        builder.AppendLine($"{pad}{new string('-', 13)} {new string('-', 12)} {new string('-', 9)} {new string('-', 16)}");
        foreach (var outer in this)
            foreach (var inner in outer.Value)
                builder.AppendLine($"{pad}{outer.Key,-13} {inner.Key,-12} {inner.Value.Count,9:N0} {inner.Value.AverageDuration}");
                totalFrames += inner.Value.Count;
        builder.AppendLine($"{pad}{new string('-', 13)} {new string('-', 12)} {new string('-', 9)} {new string('-', 16)}");

        // The very last thing we write should use simple Append and not AppendLine so as not to include
        // a trailing Environment.NewLine sequence.  Leave it to hte calling Console.WriteLine(x) to
        // issue the last NewLine.
        builder.Append($"{pad}{totalMfrs,13:N0} {totalModels,12:N0} {totalFrames,9:N0}");

        return builder.ToString();


Elsewhere (meaning in another static class) I also defined an extension method to allow me to add a TimeSpan to a Timespan:


public static TimeSpan AddDuration(this TimeSpan duration1, TimeSpan duration2)
    return TimeSpan.FromTicks(duration1.Ticks + duration2.Ticks);


There you have it.  I will be able to funnel the different outputs to StatsTracker to have uniformity of producing the report.


Initializing StatsTracker

I didn't drive home the point that the report shown in Part 2 was sorted first by Manufacturer and secondly by Model.  But it was.  And you may note in the StatsTracker class I am not using a SortedDictionary.  I get around this by initializing an instance of StatsTracker that is already sorted.


Is this necessary?  That depends upon which method were are using.  Certainly it is a nicety.  You should not assume the results returned by any of our methods will be sorted.  A general rule of thumb is the results are produced in the order they are discovered.  If you want a nice sorting, you can sort the dictionaries after you have built them, or as I have chosen to do, you may initialize an instance to already be sorted.


Again I ask, is this necessary?  Not for all methods we call.  And it's not necessary that its sorted.  But 2 of the methods, Summary and GroupedSummary, require some a priori knowledge in order to even issue the call in the first place.  For Summary, where we summarize per Model, we must know the models we want to summarize before calling Summary.  For GroupedSummary, we must know the manufacturers to summarize since we want to aggregate it by Manufacturer.  Whether this a priori information is sorted or not is not absolutely necessary, but it must be accumulated.  So why not sort it as well?


The burning question for you should be: How do I determine this knowledge beforehand?  Will you have a hard-coded list?  How will this be maintained?  For my database, I use an AFTable.  It doesn't have to be a table of just manufacturers and models, but it does have to contain all information you may be interested in.  For my database, I have a table for wind turbine power coefficients.  It has multiple entries per manufacturer and model:


2017-03-03 09_23_04-3323vlecs1.cloudapp.net_60001 - Remote Desktop Connection.png


That has all the information I desire, and then some.  All I need to do is organize it and populate my StatsTracker with it.


public static StatsTracker InitializeFromTable(AFDatabase database, string tableName, string mfrColName, string modelColName)
    var table = database.Tables[tableName].Table;

    // https://weblogs.asp.net/wenching/linq-datatable-query-group-aggregation
    var query = from row in table.AsEnumerable()
                group row by new { Manufacturer = row.Field<string>(mfrColName),
                    Model = row.Field<string>(modelColName) } into grp
                orderby grp.Key.Manufacturer, grp.Key.Model
                select new { grp.Key.Manufacturer, grp.Key.Model };

    var dict = new StatsTracker();
    foreach (var row in query)
        Dictionary<string, DurationStats> inner;
        if (!dict.TryGetValue(row.Manufacturer, out inner))
            inner = new Dictionary<string, DurationStats>();
        inner[row.Model] = new DurationStats();
        dict[row.Manufacturer] = inner;
    return dict;


I would call the above method passing my database object, tableName would be "OSIDemo_Wind Turbine Power Coefficient", mfrColName is "Manufacturer", and modelColName is "Model".  I set the method up to take arguments in case I ever had another source table that used slightly different names, e.g. a column named "Mfr".  This will produce a StatsTracker instance with:

  • The outer dictionary has 2 entries.
  • Outer keys are "Cervantes" and "Sailr" in that order.
  • The inner dictionary for Manufacturer "Cervantes" has 1 item for Model "DQ-M0L".
  • The inner dictionary for Manufacturer "Sailr" has 2 items: Models "Nimbus 2000" and "SWTG-3.6" in that order.


If you don't need to initialize StatsTracker, which is a choice if you are not calling Summary or GroupedSummary, and you want to sort it after the fact, then you may use this extension method:


public static StatsTracker SortByKeys(this StatsTracker input)
    var output = new StatsTracker();
    var mfrKeys = input.Keys.ToList();
    foreach (var mfr in mfrKeys)
        var modelKeys = input[mfr].Keys.ToList();
        foreach (var model in modelKeys)
            var stats = input[mfr][model];
            output.AddToSummary(mfr, model, stats.TotalDuration, stats.Count);
    return output;


Mental Exercise: I have shown you how I setup MY application.  Your database is probably organized very differently.  There are many ways to achieve the same thing.  If you need to know certain information before making a call, you must ask yourself how you would acquire that information for your application.  What's right for my app could be wrong for yours.  Think through your problem and come up with a solution that works for you.


Next Up: Getting a Metrics Baseline

I warned you these were going to get longer!  We have established many pieces parts used by the database and subsequent applications.  In Part 4, we will establish a baseline for metrics by doing it the old way.  The old way is our trusty FindEventFrames that has been available way back in AF 2.8.