Industrial historian data retrieval using Power BI

neil_varnas_IP · ‎10-29-2020

Introduction

Recently, Intelligent Plant helped facilitate the OGTC Codeless Hackathon event. That’s the Oil & Gas Technology Centre – who’s mission statement is to unlock the full potential of the UK North Sea through innovation and transformation. It was really something else. Over a hundred people from all over the world signed up for a four-day “hackathon challenge”.

The objective was to demonstrate how anyone can become an Industrial Data Scientist if given the right tools. Microsoft provided tutorials and mentoring for their Power Platform. Organizations such as Ithaca Energy and ORE Catapult set challenges that were based on genuine issues they face in their industrial processes, and Intelligent Plant provided the connectivity that allowed participants to directly connect Power Platform tools to Industrial Data Stores such as OSIsoft PI Historian.

Problem

(You can find the event outline here)

We encountered that many energy industry data scientists think they need to get all available raw data to solve a problem or to investigate an issue. When interfacing industrial historians this can be an issue because the total amount of data can reach up a 10+ GB depending on time range and resolution requested. At that point, you have to go down the route of importing data into a different store and write custom queries to attempt to retrieve it just to find out that half the data is missing because you didn't handle some special case when a value flashes up as NULL or has bad bits recorded.

Solution

(You can find the dashboard described below here: Industrial Historian data retrieval using Power BI)

Using Industrial App Store (IAS) Power BI connector we can create a sample dashboard where we don't need have all the raw data handy because we can dynamically re-query for specific time periods that we are interested in and update the dashboard.

To start with the trend is displaying the last 300 days. IAS allows us to specify time constraints using relative time periods which I'm utilising here. The main advantage of this is so when I publish this dashboard onto Power BI service and set up a background refresh it will always be up to date.

To accurately visualise the trend we only require 2000 points. Because my dashboard is less than 2000 pixels wide and I can't show any more resolution anyway (i.e. if I request 5k points there just isn't enough real estate to display that and at least 3k points will be discarded). When I want to zoom in and examine an area of interest I can simply edit the parameters and set the time span I'm interested in.

The number of points is still 2000 but I'm seeing much more detail because my timespan is much shorter. I'm using the PLOT aggregation which is specifically designed to show the best shape of the trend by representing all the peaks and throughs.

Utilising the parameters in power BI I transfer only the data that I'm interested in thus reducing bandwidth, load on the source system as well as my own computer. I don't need to extract 10 years worth of raw data, which will take a long time even if industrials historians support that e.g. CSV file for 1 month with 575 Tags at 1s resolution is a 9GB file and if you want 10 years of data, well you can do the math...

Furthermore, we can utilise the above parameters with Alarm and Event (A&E) data to further investigate issues.

A good place to start is to look at the sequence of events report (SoE). To browse SoE data, which can be millions of rows/events I can further parameterise the query by adding page number and page size properties to my report. The report is still filtered by the dates but having paging parameters allows us to drill down to a specific event e.g. shutdown.
Again we are not downloading 10 years worth of events because some systems might contain a lot of noise that contribute to the overall size. For example, some assets might record a million events for 5 hours during a significant event (e.g. shutdown) so you can only imagine what 10 years would look like...

Conclusion
Industrial App Store enables Power Platform data retrieval that can show a great level of detail for data scientists to come up with a machine learning algorithms without having tons of raw data downloaded or a brief summary overview that engineers can use to spot an issue.