This post is based on a summer internship project, undertaken at the OSIsoft Montreal office. For more details, check out the PI Square blog Monitoring Smart City Assets with the PI System.
For this post, we shall take a look at a concrete example.
Below we have data from bike trips in a CSV file, published on Montreal’s bike sharing system’s website.
Historical Bike Data (2015) - Montreal's BIXI
As we can see, we have information regarding the following: When was the bike picked up? From where? When was the bike returned? To where? Additional information such as the account type and the total trip duration are also given.
Eventually, we’ve decided to store these trips, not as PI points, but rather as event frames. This is due to the fact that the data is static and holds a start and an end time.
We have also considered the possibility of using them as transfers, but they are not supported by very many client tools.
We will need to assign the Event Frames to a parent element in PI AF, thus allowing us to consume this trip data in client tools like PI Coresight.
Given the large number of Event Frames created (more than 5,000,000!), another great way of consuming the data will be to create Event Views using the PI Integrator for BA, then build dashboards in Power BI.
We did not simply transform those csv trips into event frames just to view them in Power BI, because we could have simply loaded the CSV directly from Power BI, right?
However, where it becomes more interesting, is when we combine the event frame’s static data (static attributes) with other relevant PI points that existed in the same time frame. This is where the PI System comes in place.
For this example, since biking is an outdoor activity, the weather data would seem able to justify why on some days there are more trips than others, would you agree? This is exactly where the PI System comes in hand - the ability to integrate multiple datasets and PI points in a single scene!
Fortunately, we were collecting live weather data using the connector. Therefore, we simply used to connector to store the following weather parameters to the same PI points as the live weather is writing to, and backfill.
Let’s take a look at the historical weather data we’ve brought into our PI server:
Historical Weather Data (2015)
Indications regarding the weather status, temperature, humidity and wind speed will hopefully permit to justify the observations made for the 2015 bike trips.
Using PI Connector for UFL to create an AF structure, event frames with attributes, and referencing the event frames to their corresponding element:
One of the many reasons in which we’ve decided to use the PI Connector for UFL, rather than the PI Interface for UFL, is due to its ability to store event frames using the built-in StoreEventFrame() function.
First, let us see how the data is structured in the comma separated values (csv) file:
In this case, we know that the first content before the first comma represents the “start date”, the second content after the first comma represents the “start station number”, so on and so forth. Therefore, we can delete the first csv line (that represents the headlines of the table’s columns) to avoid storing it.
Since we’re more interested in looking at the demand at certain stations, we’re going to be referencing to the “start station name” to analyze how many trips were made from a station rather than to a station.
Let us take a closer look on how to parse the 2015 trips that are in a csv file into event frames.
We must first define our variables in our INI configuration file:
The Att_Col variable will be used to allow us to store multiple attributes, under one single variable!
Finally, we run this configuration file in our connector’s admin page with the data file (csv) we’re trying to parse!
This is the AF structure we’ve created with the connector for UFL in order to reference the event frames (bike trips) to their corresponding starting station name:
As we can see, simply with one INI configuration file, the PI connector for UFL was able to:
- Create an AF structure
- Store the trips as Event frames (with a start and end time)
- Store four attributes for the event frame template
We have used the PI Connector for UFL to store the weather parameters to existing PI points and backfilled.
Next, we simply import these weather PI points in our original event frame template to have them in a single scene.
Now, we need to bring this data into our business intelligence tool. Fortunately, this is where the PI integrator for BA shines.
In our case, we published “Event Views” as we were dealing with event frames. If our AF elements had attributes, we could have published “Asset Views”.
Below, we can see how the final published “Event View” looks like:
All of our Event Views were stored in our SQL Server, therefore we can now go to Power BI and load them from our SQL server:
Once that’s loaded, we can use the various features in Power BI to create multiple advanced analysis, all thanks to the Integrator for BA.
It is worth mentioning that once in we loaded our “Event View” in Power BI, we have added another column counter and gave it the value of 1. That way, whenever we slice through parameters, we can have a value to determine how many of the parameters are being sliced, or in other terms, being active by the filter (or slicer).
Attached, you will find the INI file and a sample data set for you to try!
Please click here to view the analysis done on Power BI!
P.S: If you happen to download a csv file from BIXI's open data website for other months for example, make sure you open the csv in MS Excel and then save it (still in CSV), simply to have beautiful rows so that the connector can easily read.