Well Test - Reporting

Reporting is an often overlooked piece of the system when it comes to building a well testing solution. Historically, building customized and interactive reports has been quite difficult because of how data from flow computers is exported and stored.

 

With PLC Shift flow computers, we introduce a new paradigm that makes building interactive reports using modern tooling easy. The key is the data model. Each record that is generated by the flow computers, including hourly, daily and minute flow history, as well as event, alarm and configuration records, includes metadata fields. Specifically, these records contain a 'Batch User ID' column and an 'Asset User ID' column.

 

The 'Batch User ID' column can be used to link records together for a specific batch, while the 'Asset ID' column can be used to link records together for a specific well on test. Because the values in these columns are settable by the user before a test begins, these IDs can also be used to link batches and wells for multiple flow runs.  See the Well Test - Automation section and the PLC_PRG program to see an example of how these fields can be used.  There are also additional features in the generated data, like values in normalized and user units, that make it easy to build reusable report templates.

 

In this demo project, we use Microsoft Power BI desktop, but it's important to understand that the reporting is easy because of the data model, not the tool used.

 

For the sake of portability of this demo, we have built the report on data exported from PLC Shift Manager into MS Excel, but for a real system, exporting flow computer data directly to a database and then querying it in the reporting tool makes more sense. Then the report can always automatically show the latest data.

 

PLC Shift flow computer applications can export record data directly to numerous external systems. For example, data could be exported to Ignition SCADA directly using MQTT Sparkplug B DRECORD functionality, and then reporting tools could be connected to Ignition's underlying database.

 

The finished report in desktop format can be found online at this link. A simpler version that is suitable for mobile devices is here. See the Well Test -Downloads section for the source files.

 

We have generated some well test data by letting the demo system run in Auto mode for a few hours. This data was retrieved from the flow computers using PLC Shift Manager and then exported to Excel files manually.

 

There is one file for each flow computer, and that one Excel file contains all the different types of data. There is also one additional file called 'UwiByAssetId.xlsx' that allows us to easily map the AssetId field from the tables to a UWI.

 

All the sample data is available in the downloads section.

 

 

 

Power BI can easily export data from Excel. The different data types, like batch, alarm, configurations, etc, are loaded from their corresponding sheet. Once data is loaded, minimal transformations are applied manually. Specifically, the timestamp columns in all tables need to be converted into Date/Time/Offset columns. The tables are also renamed by prefixing the sheet names with the name of the flow run.

 

 

As data is imported into Power BI, depending on your specific configuration, Power BI will attempt to automatically create relationships between different tables. These relationships are the key to being able to easily join data from different tables together. However, Power BI generates far too many relationships automatically, as shown below.

 

 

 

We recommend deleting all of these automatically generated relationships. Use the 'Manage Relationships' button in the 'Modeling' ribbon in Power BI to view and manage multiple relationships quickly.

 

The only relationship that we need is to connect the 'BatchUserId' columns together. We first create this relationship between the batch records in 'GF01_Batch', 'LF01_Batch' and 'WT01_Batch'. Next we create the same relationship for all tables that belong to a specific app. Specifically, we map the 'BatchUserId' column from the 'GF01_Batch' table to the 'GF01_Day' table, 'GF01_Hour' table and so on for all of the GF01 tables and also for the LF01 and WT01 tables. Finally, we map the 'AssetId' column from the 'GF01_Batch' table to the 'AssetId' column in the 'UwiByAssetId' column.

 

 

The data model is now complete. Records from all the tables are linked together using the 'BatchUserId' column, which is generated from the flow computers as the well test program executes. Having the data linked together in this way makes reporting on a specific well (aka AssetId) and specific batch very easy, even across multiple flow runs.

 

Building the report itself is now as simple as dragging and columns from the tables onto the report. In this demo, we start with a Power BI slicer that lets us select the well that we want to see results from. Next, we can optionally select a row from the 'Batch Summary' section to see detailed information from that well.

 

The finished report in desktop format can be found online at this link. A simpler version that is suitable for mobile devices is here.