Case Study
Harnessing true power through data warehousing
Dealing with multiple legacy systems can be a challenge, especially when harvesting data is cumbersome. Enter, a custom data warehouse that makes intelligent decision-making and transparency a reality.
Client
Razor Energy is a publicly traded junior oil and gas development company based in Calgary, Alberta. The company focuses on acquiring and subsequently enhancing oil and gas production from properties primarily in Alberta. The organization is listed on the TSX Venture Exchange, and has field operations in the Swan Hills/Kaybob region and Southern Alberta.
The bird's eye view
Oil and gas development is a complex line of work around the world, to say the least. There is a certain degree of government oversight, personnel health and safety is a huge factor, there is considerable amount of capital expenditure involved, the industry depends on external factors and hence, operations need to be agile. To top it all, efficiency is key for long-term sustainability and profitability.
Multiple systems keep track of multiple equipment and sites. To ensure companies are on top of things, it requires extensive data extraction efforts.
Razor had to deal with almost 13 different systems on a monthly basis to extract and collate data before any sort of decision-making was done. Right from Alberta Energy Regulator's Digital Data Submission (AER DDS) system to accounting systems, drilling operations management tool, and land management and mapping software, Razor needed data from each of those and a few other systems to be able to make intelligent decisions.
Launchcode helped create a data warehousing solution that not only brought all these systems together on to a single platform, but also ensured quick and reliable data extraction for timely and informed data-driven decision-making.
If data is the new oil, like some say, did we just create a refinery of sorts?
Transparency & data management
The price of oil has seen some heavy volatility in recent years, with everything from international politics to the use of alternative energy making an impact.
Companies around the world have come to rely on or at least identify technology as a tool to drive efficiencies.
According to a 2015 GE and Accenture report, as many as 87 percent of oil and gas executives surveyed had Big Data analytics among their top three priorities1. An oil and gas production and exploration company, as part of the same report, claimed that a 1 percent performance improvement would increase oil production by half a million barrels a day.
While data has been branded as the new oil, unlike oil, data isn't finite either. According to a Forbes report in 2021, the world created, captured, copied and consumed 59 trillion gigabytes of data in 2020, a 5,000% increase over 20102.
As time passes, more data is created, but decentralized legacy data systems and software simply aren't capable, or at the very least, fast enough to provide access and insights for agile and data-driven decision-making.
Razor faced problems with data acquisition and management, especially due to the heterogeneous nature of data from various systems. Each system spoke a different language about a different operational aspect.
It took Razor up to three weeks to extract data from their multiple systems and then consolidate it in appropriate formats for their management. However, a monthly process that lasts three weeks means the organization only has a week to try and make the right decision.
It wasn't just about decision-making, but also about lack of transparency. With each department working on a different system, it was almost impossible and definitely time-consuming to figure out what the other department was up to.
Dream on!
Replacing 13 different systems across the organization wouldn't be an ideal solution, but being able to harvest data from all these systems and then standardize them would be a start.
The solution had to provide Razor with a centralized and normalized data repository, as well as a multiplatform application to provide users with access to reports and dashboards.
Using an existing Microsoft environment at Razor was yet another box Launchcode had to check.
The rules of the game
With Razor using a Microsoft environment, the warehouse itself had to be built using SQL.
Data flowing into the warehouse had to be through APIs that could extract, transform and load (ETL) information from corresponding systems.
PowerBI would have to be used for data visualization and dashboards at the user end.
In the quest of data-driven decision making
In line with Launchcode's principles, the first step was to understand the purpose of all the data acquisition and management. It involved extensive interviews with the client's subject matter experts and lots of independent research into the processes involved.
The process was about bringing together the expertise of the development team at Launchcode, and process experts at Razor Energy, to ensure a comprehensive homework that then gave momentum to project development.
Armed with the whys and the whats of the pr oject, Launchcode developers next focused on each underlying system. Understanding each system and purpose, the team's next step was to build ETL-focused APIs.
That presented a fresh set of challenges, considering the different functions each of these solutions offered.
The ETL process is known to be challenging at the start since one needs to define specific data points right at the beginning, ensuring databases and warehouses don't have any unstructured information.
Once the developers dealt with those challenges, creating a centralized and normalized database was the next step. This involved restructuring and organizing a relational database into tables, removing ambiguous and duplicate data, standardizing metrics and terms.
With each of the systems speaking a different language, the team created a data dictionary as well, which served as a correspondence between the names used on the underlying systems and the warehouse itself.
The team then focused on integrating PowerBI with the data warehouse to facilitate reporting, data visualization, predictive analysis and business intelligence among other things.
Given the multitude of systems involved, and the complexity of the data, testing involved creating specific dashboards and tables, and comparing that with manual processes and their output.
Data made easy
The Razor Intelligence Solution provided a real-time evaluation of the company's performance through timely and reliable data acquisition and management.
The solution ensured real-time flow of information, production consolidation and operation monitoring.
The Launchcode-built solution brought down the data extraction and management times from three weeks into real time. In certain instances, Razor Energy realized a reduction of 3,400 percent in time spent collecting and analyzing data.
With the ETL processes automating the data flow processes, users only had to refer to PowerBI and spreadsheet programs to facilitate data presentation.
The system also allowed report scheduling with dashboards and numbers reaching the mailboxes of the personnel concerned.
The management was a lot more proactive and agile with their decision-making, with reliable and timely data at their fingertips.
Clients love us!
Once the solution was implemented, it allowed the personnel to focus on analysis than data acquisition.
The solution also fostered transparency within the organization, making data a lot more accessible.
The company also decided to display various performance dashboards on screens across various offices and departments. The dashboard identified trends, outliers, correlations, measured efficiencies and inefficiencies, etc.
That was yet another step towards organization-wide transparency with respect to company performance.
It also allowed employees to witness firsthand the impact of their efforts and ended up becoming an empowerment tool.
During the development process, Razor Energy appreciated the flexibility of the Launchcode team, responding to multiple changes in goals and briefs, but at the same time staying focused on what's important.
The team's extensive research on various existing systems and efforts to standardize information and terminology was appreciated, and eventually crucial to the project.
Citations
1“Industrial Internet Insights Report for 2015”. General Electric & Accenture. Accessed 28 June 2022
2Press, Gil. “54 Predictions About The State Of Data In 2021”, 20 December 2020. Forbes. Accessed 27 June 2022.