"Production Software" - a somewhat vague term that can include a number of functions - helps engineers optimize well production, minimize decline, reduce lease operating expenses (LOE), and forecast impacts on well economics by aggregating well performance data and well characteristics. Engineers spend a ton of time in Production Software, because it’s integral to their jobs.
One oft-overlooked element of Production Software, however, is it’s source of data. Typically, oil & gas production doesn’t include the data acquisition components required to create and transport data into a database for production optimization. That work often gets accomplished by SCADA or IoT Remote Monitoring (we discuss the difference here).
Engineers rarely actually analyze their data in SCADA, but it’s still a critical component of the data journey, and so Production Engineers are aware of its benefits (and limitations).
In this blog, we answer an important question: How do you get SCADA data into Production Software? We’ll cover 5 common methods and provide additional resources to learn about each one. Whether you work for a large integrated oil and gas corporation with its own data servers or a small, lean E&P using cloud-based SCADA (or anything in between), you should know how to export data from a SCADA system and put it to work in Production Software - or any other data analysis tool.
One common way to connect two different databases is to use database connecters. Examples of database connectors include ODBC, JDBC, or CLI. These database connectors are essentially APIs (which we will discuss later) designed specifically to allow Production Software to ingest data from a SCADA data historian.
How you set up database connectors depends on the types of data servers you are trying to connect to and the drivers supported by your Production Software. The process becomes more challenging overall if your data historian uses a different format or database management system than your Production Software, or if you need to pull data from multiple SCADA systems or field data capture sources.
In many cases, you’ll need to perform an extract, transform, and load (ETL) process to ensure data is pulled successfully from your first database, formatted correctly for the second, and then uploaded efficiently.
A simple diagram demonstrating the ETL process. Source: Xplenty.
Database connectors like ODBC are generally the purview of IT teams, as they require a strong understanding of database networking and query languages. Companies with large data servers and hosts of IT resources generally have an easier time with database connecters because they are more commonly used by bigger organizations. Smaller companies with limited IT resources, on the other hand, may find that they need outside help to implement these kinds of database connections effectively.
Here are some more resources for understanding how database connectors work.
How to get data into Spotfire using ODBC
A second approach to integrate SCADA data into Production Software is to use a File Transfer Protocol (FTP). As the name suggests, an FTP is for moving files that contain production data between two servers.
FTP is essentially a form of ETL using files as a medium. You are extracting data from one server, transforming it (by putting it into a file with rows and columns that the second server can understand), and then setting up a system whereby the receiving server can interpret and load it into some kind of database.
FTP works for almost any file type out there. When integrating SCADA time-series data with Production Software, engineers frequently use formats like CSV, XLS, MDB, or ODS. Overall, FTP provides a relatively simple and intuitive way to get data from SCADA to Production Software, provided each side supports the file creation/consumption process (many do).
Still, FTP usually requires some IT support, as companies have to set up FTP servers to send and receive files and ensure everything is secure. Note there are two types of FTP security protocols (SFTP & FTPS) and, yes, there is a difference.
A simple FTP diagram showing the added security offered by SFTP. Source: WPEngine.
WellAware uses both SFTP and FTPS with clients to get data from our modern oilfield remote monitoring system into their Production Software. It’s a tried and true method that’s relatively easy to validate because stakeholders and data engineers can share template files to define the data transformation.
If you aren’t working with an external vendor, though, it can be time-consuming or overly complex for your busy internal team. FTP still requires some IT know-how, as it involves setting up an FTP server and then pointing your Production Accounting client to that server to retrieve files on a schedule.
Here are some links to better understand FTP and how you can implement a simple FTP server on your machine.
The difference between SFTP and FTPS
How to set up a simple FTP server
If your SCADA data and/or Production Software uses databases hosted on third-party cloud platforms, like AWS or Azure, you can use tools built for those clouds to make data transfers more simple.
Azure, for instance, offers AZ Copy, a command-line utility to transfer data into Azure databases, either from another Azure database or from a different source entirely. AWS offers tools like DataSync to transfer data between servers or accounts.
Most Production Engineers will need IT support in this area as well. But since these proprietary tools are designed to support specific cloud infrastructures, they work pretty well for that infrastructure, and offer some flexibility as to which data sources you can get data from. This approach can be a good option if you already use cloud storage for SCADA and/or Production Software.
Getting started with AZ Copy (Azure)
Some modern cloud SCADA solutions typically come with an Application Programming Interface (API). What is an API? At a high level, an API functions as a translator between applications. So, in the world of oil and gas production, APIs offer a way for users or client applications to interact with many SCADA system functions and services, including exporting time-series data into Production Software or analysis tools.
To retrieve data from a SCADA API, you make a “request” to the API. The API will interact with the SCADA application to get the data you requested, and then return that data in a particular format. REST APIs, for example, often transfer data as structured ata dictionaries in the form of JSON files.
Why do Production Engineers like APIs?
More and more production engineers are educated in some form of programming language, giving them additional tools for performing analysis on SCADA datasets. It’s becoming more common for Production Engineers to have experience with languages like Python, Matlab, and R.
Most of these languages offer pre-built libraries that allow engineers to easily make API requests to get exactly the data they need without needing much - or any - IT support.
APIs are also hyper-contextualized, as they are built specifically to support the SCADA application, meaning engineers don’t have to deal in vague database terms but rather in terms specific to oil and gas production data.
While they may not often be used to create permanent connections between SCADA and Production Software, cloud SCADA APIs give engineers the ability to build “quick and dirty” scripts in the language of their choosing to access particular kinds of data.
Good documentation is an important feature of any cloud SCADA API.
Additionally, APIs aren’t just for requesting data, they are also useful for pushing information back to SCADA, meaning Engineers can build scripts to automate critical functions. For example, I could write a script that pulls data from a remote PLC that monitors and controls wellhead valves and pressures. My script could routinely check for over-pressure events that indicate a production issue, and I could use the API to remotely. shut the well-in to avoid problems.
These types of automation scenarios used to only be possible if you knew how to program PLCs using ladder logic, but with the advent of APIs in oil and gas production, a new world of automation is opening up.
Here are some resources to better understand how to use APIs for data analysis.
Our fifth and final method is Message Queuing Telemetry Transport (MQTT). MQTT is buzzing these days, as IoT and SCADA teams are using the technology more frequently to transfer data between applications.
MQTT uses a “publish/subscribe” (pub-sub for short) methodology in which an MQTT client can both publish and subscribe to data on different topics. MQTT brokers can also send data between clients.
One advantage of MQTT is that it is super lightweight and perfect for the time-series data streams we use in SCADA and Production Software. MQTT is also incredibly scalable due to its pub-sub framework. Users don’t have to manage connections between every client in the network - that’s what brokers are for.
Think of MQTT brokers like mortgage brokers: the benefit of going through a mortgage broker is that you don’t have to interact with 20 different lenders to get a good loan. The broker “serves up” the loan that you need based on parameters that you stipulate. MQTT brokers are similar in that they do the job of connecting clients automatically. Users get the exact data that they need based on the topics they subscribe to.
MQTT lets devices and clients subscribe to only relevant information.
Like APIs, MQTT is starting to become popular in industrial settings. Modern SCADA teams are using MQTT effectively, but legacy SCADA systems and Production Software don’t always natively support it.
Still, there’s tremendous promise. Most scripting languages include MQTT libraries, making the technology super flexible for DIY data analysis.
Here are some links to help better understand how MQTT works and how to get started.
How do set up a free MQTT broker
How MQTT is advancing automation and control
Ultimately, how you choose to get data out of SCADA and into your Production Software is up to you. But make sure to follow an approach that aligns with your company’s resources, skills, and goals.
SCADA vendors can also help you, depending on your target outcomes. If you find yourself evaluating a new vendor in the future, learn all you can about how they plan to get data from their system into your platform. The ultimate goal is always the same: putting SCADA data to work through data analysis. With this post, you should have a strong foundation of knowledge you can use to evaluate their strategy!