Jerome Lefebvre

Get public JSON data into PI using the PI Connector for UFL -- Update

Blog Post created by Jerome Lefebvre on Sep 3, 2016

An update on my blog post of sending currency data to PI using the PI Connector for UFL.


I have been looking to add in more examples of public data to the PI Connector for UFL sample repository:  that I manage, but I have received many great contributions from members across OSIsoft.


The first step in adding in doing so, is making sure that the current examples are doing things correctly and can do handle added functionality like back-filling in some data.


The API allow you to retrieve historical information by using URLs of the form: . So, to implement this backfill operation, all that I would need to do is iterate over all dates between some chosen day and today. Easy enough to do in Python, here is all the code that will runs the back-fill feature.


def daterange(startDate, endDate):
     for n in range(int((endDate - startDate).days)):
          yield startDate + timedelta(n)

def url(day):
      return "" + day.strftime("%Y-%m-%d")

# The oldest data is date(1999, 1, 4)
startDate = date(1999, 1, 4) if args.backfill else date.fromordinal( - 1)
endDate = date.fromordinal(

for day in daterange(startDate, endDate):
     data = getData(url(day), params={"base": args.currency})


Running this new script, I could now see that I was indeed getting historical data all the way from 1999 to the present (1999 being the oldest year we can retrieve data from fixer). The problem, is that my VM started to slow down to a crawl as memory and cpu available vanished. There was not much of either to start, so maybe this was not a problem, the next day was no better and my machine and in particular, my sql server were tremendously busy.


Historical data was not an issue, the data archive had stored 15 years worth of currency data without a hiccup. Looking at the PI Connector for UFL asset buffer queue  painted a different picture. The queue still had hundreds of thousands elements  hours after the last event had been written to the data archive. So what was the issue?


The currency data updates daily and I was going over around 15 years worth of data, thus the script ran 365*15 = 5475 times.

Each day the set of indexed currency can be slightly different as new currencies are tracked and old ones removed. Here is the list of currency that have been tracked over the years, 42 in total:



Currently there is about 30, and somewhat less historically. So, let’s say on average there is around 25 currency rates that can be fetched each day. This means, by back-filling we are sending around: 365*15*25 = 136 875 event. A large number of events, sure, but nothing to worry about. But, as was it was noted, the issue wasn't with the data sent to PI Data Archive, but to the AF Server.


In the ini file, we can see that we are calling StoreEvent as often as we do StoreElement:


Currency.FILTER = C1=="        *"
CurrencyName = ["*\"(*)\"*"]
CurrencyValue = ["*: (*),"]
StoreEvent(Base + "_to_" + CurrencyName, CurrencyName, Timestamp, CurrencyValue)
DynAttrCol = Add(Base + "_to_" + CurrencyName)


But, each StoreElement call is different, as the list in DynAttrCol is grows over each iteration. First round the list may contain only CAD, but the next round it will contain CAD, JPY and the time after that CAN, JPY, USD. And each time, we request an element to be created, we do so with a growing list of attributes. So to go through a single round of currency data, the ini file is asking to create an element with 1 attribute, then an element with 2 attribute, then with 3, 4, etc.


So, I am not just sending 365*15*25 requests to AF, I was sending 365*15*(1 + 2 + 3 + … + 24 + 25) = 365*15*25*(25+1)/2 = 1 779 375 attribute creation request in a very short time, on a very memory constrain system. No wonder my system was crying uncle.


The fix is very easy all that is needed to do is move the StoreElement at the very bottom, so that is called only once. Thus, decreasing the number of attribute creations by two magnitudes.

EndOfFile.FILTER = C1=="}"
StoreElement(Base, "Currency", DynAttrCol)
DynAttrCol = Clear()

Sorry for anybody who made use of the old version of my code!


The full ini file and the Python script with the new back-fill feature is found here:

PI-Connector-for-UFL-Samples/COMPLETE_SOLUTIONS/Currency at master · osisoft/PI-Connector-for-UFL-Samples · GitHub

Stay tune as I look into adding more examples, such as those contained in: Public Weather and Environmental Internet Data Sources Integration into the PI System.pdf


One last issue that is remaining with this and it is a current limitation of the Connector for UFL.


If you look at the AF element that is created, you will see that several elements are excluded.

The reason being that the currency not included in the very latest report are not available when the "StoreElement" call is made. The historical data is perfectly well stored and if you remove the excluded flag on the attribute, you will have access to tag that stores that data. But, the next time the data is parsed, the excluded flag will be once more appended.

Thus, with version 2016 of the connector, if the list of attributes that you are currently working with varies with each call, a different strategy would need to be implemented.

I am unsure what is that strategy is yet though.. please comment me if you have an idea!