Oil and Gas Company Saves Time and Resources by Increasing Data Processing and Performance

Client: Independent Oil and Gas Company

Key Product Solution: SQL Server, SSIS, SSAS
Industry: Oil and Gas

Oil and Gas Company saves significant man hours and resources by increasing data processing and performance with Pragmatic Works.

THE CHALLENGE

An independent oil and gas company focused on the acquisition, exploitation, development and production of oil and gas properties was struggling with platform architecture for their growing data volumes. Business data is used to optimize oil production costs, which translates into greater margins and increased profitability.

ETL processes had slowed considerably, which hindered the ability to fully utilize data. With the current data volume, a number of issues were causing concern, including SQL Server Analysis Services processing running longer than expected; SQL Server Integration Services executing longer than expected; and exception handling and logging not consistently applied to SSIS packages.

Expected data growth and acquisitions would soon be causing bottlenecks with both manpower and computing capabilities.

THE SOLUTION

Pragmatic Works engaged in an assessment and re-architecture of the client’s environment for best practices including: SQL Server instance, database, SSIS, and SSAS configuration and design. Pragmatic Works partnered with the client’s internal team to identify potential scaling and performance bottlenecks while providing recommendations for improvement.

The customer also had many parent-child and many-to-many dimensions that caused issues with their query performance. By improving the SSAS dimension design pattern, the client now sees a significant improvement in that performance.

Pragmatic Works also improved the SSIS fact processing pattern. The fact packages were originally built off of multiple views that were several orders of magnitude deep. The consultant working onsite took the views and transformed them into tables, allowing for very fast querying of the source data.

The source data was also parameterized by date. A control table was added to allow date based processing. The SSIS fact table packages went from 1.5 hours to 5 minutes to process dates for the last 90 days.

Under Line