Skip navigation
All Places > PI Developers Club > Blog > 2017 > September
2017

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);

Introduction

 

The PI Web API 2017 release has come with lots of great new features. One of the them is the Swagger specification which is a JSON string that allows you to generate client-side libraries in many different programming languages including Java, C#, JavaScript AngularJS etc...

 

I have already published the PI Web API client library for AngularJS. Nevertheless, there are web developers who still prefer jQuery and that are not familiar with AngularJS. Since the code is very similar (after all everything is written in Typescript which is compiled into JavaScript), I've decided to release a jQuery version.

 

On this blog post, I won't show you how to generate this library because the process is very similar to AngularJS. In case you are interested in generating your own library, please refer to the AngularJS version of this post and also visit this GitHub repository which has the generator of this library.

 

If you want to download the library please visit this GitHub repository. A sample web application using this library is available here.

 

What is Swagger?

 

If you visit the Swagger web site, this is how they define their product: "The goal of Swagger™ is to define a standard, language-agnostic interface to REST APIs which allows both humans and computers to discover and understand the capabilities of the service without access to source code, documentation, or through network traffic inspection. When properly defined via Swagger, a consumer can understand and interact with the remote service with a minimal amount of implementation logic. Similar to what interfaces have done for lower-level programming, Swagger removes the guesswork in calling the service.".

 

Please visit this page to find more information about getting started with Swagger.

 

How to access the PI Web API Swagger definition?

 

Just make a GET request against the following url: https://servername/piwebapi/help/specification?pretty=true. You will receive a JSON response back with the Swagger definition.

Remember that you must be using at least PI Web API 2017 in order to have access to the Swagger definition. This url won't work with PI Web API 2016 R2 and older versions of the product. Nevertheless, if you download the library from our GitHub repository, it will be compatible with all PI Web API versions. Just expect to receive some exceptions in case you are using a method that is not implemented on the server side as result of not using the latest PI Web API version.

 

Requirements

 

  • PI Web API 2017 installed within your domain using Kerberos or Basic Authentication.
  • jQuery 1.7.1+

 

Installation

 

  • Download this source code
  • Copy the files from the dist folder to one of your web application folders.

 

Usage

 

  • Refer to the jquery-piwebapi.js or jquery-piwebapi.min.js (make sure it is after loading the jQuery library).

 

Source Code

 

The solution that generates the final library is available on the src folder. You might want to add or edit a method and rebuild the solution in order to generate custom assemblies. The links from both GitHub repositories are below:

 

 

Documentation

 

All classes and methods are described on the DOCUMENTATION.

 

Examples

 

Please check the sample_main.js from this repository. Below there are also code snippets written in JavaScript for you to get started using this library:

 

Set up the instance of the PI Web API top level object.

 

    var piwebapi = new PIWebApi("https://marc-web-sql.marc.net/piwebapi", true); 

If you want to use basic authentication instead of Kerberos, set useKerberos to false and set the username and password accordingly.

    var piwebapi = new PIWebApi("https://marc-web-sql.marc.net/piwebapi", false, "username", "password"); 

 

Get the PI Data Archive WebId

 

    piwebapi.dataServer.getByPath('\\\\MARC-PI2016').then(function (response) { dataServer = response.data; }, function (error) { console.log(error); });

 

Create a new PI Point

 

    var newPoint = new PIWebApiClient.PIPoint(null, null, "SINUSOID_TEST124121115", null, "Test PI Point for jQuery PI Web API Client", "classic", "float32", null, null, null, false); 
    piwebapi.dataServer.createPoint(dataServer.WebId, newPoint).then(function (response) { console.log(response.data); }, function (error) { console.log(error); });       

 

Get PI Point WebId

 

    piwebapi.point.getByPath("\\\\MARC-PI2016\\sinusoid").then(function (response) { 
       var webId = response.data.WebId; },
    function (error) {
       console.log(error);
   });

 

Get recorded values in bulk using the StreamSet/GetRecordedAdHoc

 

    var webIds = [] 
    webIds.push(point1webId);
    webIds.push(point2webId);
    webIds.push(point3webId);
    piwebapi.streamSet.getRecordedAdHoc(webIds, null, "*", null, true, 1000, null, "*-3d", null).then(function (response) {
      console.log(response.data);
    }, function (error) {
      console.log(error);
    });

 

Send values in bulk using the StreamSet/UpdateValuesAdHoc

 

    streamValuesItems = new PIWebApiClient.PIItemsStreamValues() 
    streamValue1 = new PIWebApiClient.PIStreamValues()
    streamValue2 = new PIWebApiClient.PIStreamValues()
    streamValue3 = new PIWebApiClient.PIStreamValues() 
    value1 = new PIWebApiClient.PITimedValue()
    value2 = new PIWebApiClient.PITimedValue()
    value3 = new PIWebApiClient.PITimedValue()
    value4 = new PIWebApiClient.PITimedValue()
    value5 = new PIWebApiClient.PITimedValue()
    value6 = new PIWebApiClient.PITimedValue() 
    value1.Value = 2
    value1.Timestamp = "*-1d"
    value2.Value = 3 value2.Timestamp = "*-2d"
    value3.Value = 4 value3.Timestamp = "*-1d"
    value4.Value = 5 value4.Timestamp = "*-2d"
    value5.Value = 6 value5.Timestamp = "*-1d"
    value6.Value = 7 value6.Timestamp = "*-2d" 
    streamValue1.WebId = point1webId
    streamValue2.WebId = point2webId
    streamValue3.WebId = point3webId 
    values1 = []; values1.push(value1)
    values1.push(value2)
    streamValue1.Items = values1 
    values2 = []; values2.push(value3)
    values2.push(value4)
    streamValue2.Items = values2
    values3 = [];
    values3.push(value5)
    values3.push(value6)
    streamValue3.Items = values3
    streamValues = []
    streamValues.push(streamValue1)
    streamValues.push(streamValue2)
    streamValues.push(streamValue3)
    piwebapi.streamSet.updateValuesAdHoc(streamValues, null, null).then(function (response) {
          console.log(response.data);
    }, function (error) { console.log(error); });

 

Developing a web application using jQuery and PI Web API client library for jQuery

 

In order to test this new client library, let's take the example from the blog post. First we need to edit the pi_data_result.html:

 

    <script src="Scripts/jquery-3.1.1.js"></script>
    <script src="Scripts/pi_data_result.js"></script>
    <script src="Scripts/jquery-piwebapi.js"></script>

 

You might need to install jQuery NuGet package using the command on the Package Manager: Install-Package jQuery

 

The original pi_data_result.js is:

 

function GetQueryStringParams(sParam) {
    var sPageURL = window.location.search.substring(1);
    var sURLVariables = sPageURL.split('&');
    for (var i = 0; i < sURLVariables.length; i++) {
        var sParameterName = sURLVariables[i].split('=');
        if (sParameterName[0] == sParam) {
            return sParameterName[1];
        }
    }
}


function AppendErrorToTable(DataObj, TableToAdd) {
    var ErrorMsgs = DataObj;
    $('<tr/>', {
        'text': ErrorMsgs
    }).appendTo(TableToAdd);
}


function StartRetrievalMethod(PerformRequest, RetrievalMethodName, TableToAdd, RetrievalMethodClass, RetrievalMethodData) {


    if (PerformRequest == "yes") {
        try {
            for (var i = 0; i < RetrievalMethodData["Items"].length; i++) {
                $('<tr/>', {
                    'id': RetrievalMethodName + 'Tr' + i,
                }).appendTo(TableToAdd);
                $('<td/>', {
                    'text': RetrievalMethodData["Items"][i].Value
                }).appendTo('#' + RetrievalMethodName + 'Tr' + i);
                $('<td/>', {
                    'text': RetrievalMethodData["Items"][i].Timestamp
                }).appendTo('#' + RetrievalMethodName + 'Tr' + i);
            }
        }
        catch (e) {
            if (RetrievalMethodData["Value"] != undefined) {
                $('<tr/>', {
                    'id': RetrievalMethodName + 'Tr',
                }).appendTo(TableToAdd);
                $('<td/>', {
                    'text': RetrievalMethodData["Value"]
                }).appendTo('#' + RetrievalMethodName + 'Tr');
                $('<td/>', {
                    'text': RetrievalMethodData["Timestamp"]
                }).appendTo('#' + RetrievalMethodName + 'Tr');
            }
            else {
                AppendErrorToTable(RetrievalMethodData, TableToAdd);
            }
        }


        $(RetrievalMethodClass).css("visibility", "visible")
    }
    else {
        $(RetrievalMethodClass).css("visibility", "hidden")
    }




}


var piServerName = "";
var piPointName = ""
var startTime = ""
var endTime = ""
var interval = ""


$(document).ready(function () {
    piServerName = GetQueryStringParams('piServerName');
    piPointName = GetQueryStringParams('piPointName');
    startTime = GetQueryStringParams('startTime');
    endTime = GetQueryStringParams('endTime');
    interval = GetQueryStringParams('interval');
    var getsnap = GetQueryStringParams('getsnap');
    var getrec = GetQueryStringParams('getrec');
    var getint = GetQueryStringParams('getint');
    $("#PIServerNameValue").text(piServerName);
    $("#PIPointNameValue").text(piPointName);
    $("#StartTimeValue").text(startTime);
    $("#EndTimeValue").text(endTime);
    $("#IntervalValue").text(interval);


    piwebapi.SetBaseServiceUrl("https://devdata.osisoft.com/piwebapi");
    piwebapi.ValidPIServerName(piServerName).then(function () {
        $("#PIServerExistValue").text("true");
    }, function (error) {
        $("#PIServerExistValue").text("false");
    });
    piwebapi.ValidPIPointName(piServerName, piPointName).then(function (piPointData) { 
        $("#PIPointExistValue").text("true");
        piwebapi.GetSnapshotValue(piPointData).then(function (data) { 
            StartRetrievalMethod(getsnap, 'Snapshot', 'table.snapshot', ".snapshot", data) 
        }, function (error) { });
        piwebapi.GetRecordedValues(piPointData, startTime, endTime).then(function (data) { 
            StartRetrievalMethod(getrec, 'Recorded', 'table.recorded', ".recorded", data)
        }, function (error) { });
        piwebapi.GetInterpolatedValues(piPointData, startTime, endTime, interval).then(function (data) { 
            StartRetrievalMethod(getint, 'Interpolated', 'table.interpolated', ".interpolated", data);
        }, function (error) { });
    }, function () {
        $("#PIPointExistValue").text("false");
    });

});

 

 

The new pi_data_result.js looks like:

 


$(document).ready(function () {
    piServerName = GetQueryStringParams('piServerName');
    piPointName = GetQueryStringParams('piPointName');
    startTime = GetQueryStringParams('startTime');
    endTime = GetQueryStringParams('endTime');
    interval = GetQueryStringParams('interval');
    var getsnap = GetQueryStringParams('getsnap');
    var getrec = GetQueryStringParams('getrec');
    var getint = GetQueryStringParams('getint');
    $("#PIServerNameValue").text(piServerName);
    $("#PIPointNameValue").text(piPointName);
    $("#StartTimeValue").text(startTime);
    $("#EndTimeValue").text(endTime);
    $("#IntervalValue").text(interval);
    var piwebapi = new PIWebApi("https://marc-web-sql.marc.net/piwebapi", true);




    piwebapi.dataServer.getByPath('\\\\' + piServerName).then(function (response) {
        $("#PIServerExistValue").text("true");
    }, function (error) {
        $("#PIServerExistValue").text("false");
    });




    piwebapi.point.getByPath('\\\\' + piServerName + '\\' + piPointName, null, null).then(function (response) {
        $("#PIPointExistValue").text("true");


        piwebapi.stream.getValue(response.data.WebId).then(function (response) {
            StartRetrievalMethod(getsnap, 'Snapshot', 'table.snapshot', ".snapshot", response.data) 
        }, function (error) { });
        piwebapi.stream.getRecorded(response.data.WebId, null, null, endTime, null, null, null, null, startTime).then(function (response) {
            StartRetrievalMethod(getrec, 'Recorded', 'table.recorded', ".recorded", response.data) 
        }, function (error) { });
        piwebapi.stream.getInterpolated(response.data.WebId, null, endTime, null, null, interval, null, null, null, null, null, startTime).then(function (response) {
            StartRetrievalMethod(getint, 'Interpolated', 'table.interpolated', ".interpolated", response.data) 
        }, function (error) { });
    }, function () {
        $("#PIPointExistValue").text("false");
    });

});

 

 

Using the library, you don't need to write the piwebapi_wrapper.js. Just use this client library instead and it will work!

 

Conclusion

 

I really think that having PI Web API client libraries available for many different platforms with make our lives much easier. Just download it from this GitHub repository and start developing web apps using jQuery and PI Web API.

Filter Blog

By date: By tag: