Harmonize oil and gas datasets and create business intelligence software with Amazon QuickSight

This is a guest post co-written by Tata Consultancy Services.

With origins in India and a legacy of more than 50 years, Tata Consultancy Services (TCS) is a global consultancy specializing in a range of technical operations. We operate in more than 50 countries with a highly localized workforce of more than 600,000 employees, across cloud and cybersecurity to analytics and network capabilities. In this post, TCS shares how we built a modern business intelligence (BI) software for an oil and gas client with Amazon QuickSight.

One of our key offerings is helping oil and gas companies tackle complex data problems.

We were approached by an Australian oil and gas giant about developing a modern, unified BI infrastructure and the services on top of it. They wanted the software to be modular, scalable, and with strong capabilities to present data as visualizations. But complicating matters was the project’s requirements to use a serverless architecture that could retain, host, curate, catalog, and query important data from legacy applications.

The system needed to be able to deliver against three core areas: assess historical trends, establish data-backed targets, and allow the business to work toward predictive insight. These all fed into a better level of monitoring for senior executives, who used these insights to measure and improve efficiencies.

Using QuickSight not only reduced our cost of operation, but also increased productivity by freeing up valuable resources and the time spent managing and querying the old applications.

A siloed environment and a ticking timer

The oil and gas business had fueled much of its growth through a history of acquisitions. But each time this process occurred, another narrow-scope application and set of rationalization rules were incorporated from the backend of the acquired party as they came aboard. Due to the number of different datasets from different parties in play, the business was currently using spreadsheets to normalize and combine data for insight. This resulted in a huge amount of time, effort, and resources to manage it.

Challenges in their current BI system were also caused by important data applications facing decommissioning and no longer being supported by modern operating systems. In the current system, there was a risk of information that was vital for regulatory compliance and government reporting could be lost, which would spell disaster. Certain programs in use were already no longer supported and the result was high maintenance and inflated operational costs for continuing to use them as safely as possible.

Because of these legacy systems and the importance of the data they held, we needed to develop a system as fast as possible to retain this legacy information safely and securely. And although this system was needed for occasional queries and reporting, but not day-to-day operations, it needed to be held in a way that didn’t result in expensive overprovisioning.

Slotting into the AWS ecosystem

The oil and gas business already had several AWS services within its technology stack, so it made sense for the client to use AWS for its BI services for continuity and a straightforward level of data management. The client still assessed several different solutions, but QuickSight came out on top for query and response times, helped by existing data being held in Amazon Simple Storage Service (Amazon S3), lowering data loading times and enhancing end-user experience. The project was scoped in three phases timelined for 6 months.

The following diagram illustrates the solution architecture to pull data into QuickSight.

We leveraged the AWS Transfer Family to automate the migration of our application data into an initial Amazon S3 staging bucket. AWS Step Functions was then used to orchestrate the transfer of objects into our raw data lake in a separate Amazon S3 bucket.

AWS Glue‘s powerful ETL capabilities were employed to transform the raw CSV data residing in the lake into a more optimized parquet format prior to loading. Glue crawlers continuously monitored the data lake, updating our AWS Glue Data Catalog with accurate schema definitions and metadata for partitioned Parquet files.

With our transformed data now cataloged, Amazon Athena allowed us to instantly run SQL queries against exabytes of data using standard SQL. The results were then seamlessly imported into Amazon QuickSight for advanced data visualization and dashboard publishing.

By surfacing insight-rich dashboards in Amazon QuickSight, our users gained interactive and easy-to-understand business analytics to help drive more informed decisions. The serverless architecture promoted agility while AWS services orchestrated the end-to-end data pipeline with little ongoing management overhead. Advanced report customization and interactivity features were leveraged in Amazon QuickSight for a data analysis project.

Parameters provided flexible filtering and controls. Over ten parameters per analysis allowed filters an on-screen control. In total across four analyses, 30+ parameters offered numerous ways to tailor visualizations.

Intuitive on-screen controls were built from the parameters. More than ten controls per analysis filtered visuals dynamically based on dropdown selections, responding instantly to user inputs.

Quick navigation was enabled through Amazon QuickSight’s URL actions. Visuals and action labels guided users with single clicks to specific web destinations containing related information. The tool integrated analysis and external resources for simplified exploration.

Striking it rich with significant optimization

The results of the business’s move to the QuickSight system we built were significant. They were soon operating a far more scalable and flexible system at much greater speed, allowing a higher level of BI insight to end-users regardless of varying request loads. That under-threat legacy information was securely archived in Amazon S3 and available through QuickSight in an efficient, pay-for-usage model. Additionally, legacy applications had well-established workflows, features, and documentation, which we were able to use Amazon S3 or SharePoint for and then link through to the QuickSight reports requested.

The following screenshot shows an example QuickSight dashboard, showing insights into oil and gas production.

From an employee standpoint, the serverless architecture removed a lot of infrastructure management, meaning the in-house development team could focus on using the newly available insights and develop new, innovative processes for the business.

The headline numbers made a compelling case for QuickSight’s impact:

• 200% improvement in reporting performance
• 300% improvement in job refresh and query performance
• 65% lower total cost of ownership through utilizing a serverless architecture
• Pay-as-you-use QuickSight model resulted in 10% costs compared to on premises

Looking to the future

With the project making a significant impact on the client’s operations, there’s still more that we want to use QuickSight for as we move forward.

Currently, the future aim is to implement generative AI and generative BI into the QuickSight dashboards. This should allow more freeform and specific queries to be handled, through a system that learns as it operates, and is available through the existing feature Amazon Q in QuickSight.

Conclusion

In this post, we shared how TCS built a modern BI software for an oil and gas client with QuickSight. To learn more about how QuickSight can help your business create exciting new applications, save on in-house development time, and bring data insights to customers, visit Amazon QuickSight.

About the Authors

Sumitha Madoor works with the Data & Analytics practice team of the AI.Cloud unit in TCS. Providing streaming, analytics, and database solutions in AWS are her key focus areas. With over 15 years of experience in the industry, Sumitha has handled strategic initiatives, led teams, and driven successful outcomes in AWS migration and reporting solutions.

Lokesh Seramalanna leads the Data & Analytics – Reporting CoE stream for the AI.Cloud unit in TCS. He contributes towards customer projects and CoE service offerings in Data Analytics & Insights, creating value for customers and key stakeholders. With over 21 years of overall experience in various roles, Lokesh has helped customers across industry verticals (healthcare, automobile, workforce, telecom, energy, and manufacturing) in delivering value-added solutions.

Ramesh Srinivasan heads the Data & Analytics for AI.Cloud unit in TCS. He has been nurturing multiple product COEs with a focus on data engineering and analytics, He collaborates with customers and other stakeholders and build relationships with stakeholders to appropriately stretch and impact the system delivery results, creating value for customers and key stakeholders. With over 26 years of overall experience in various roles, Ramesh has helped customers across BFSI as a trusted advisor.

Saunak Chandra is technology thought leader and principal architect driving organizations’ responsible use of data and AI on AWS. Saunak leads high-performing global teams building and delivering industry solutions, enabling customers’ organizational transformation. He is a thought leader in platform technologies including business intelligence, data warehouses, and data lakes.