Engineering Resilient ETL: PowerCenter Performance Limits and Cloud Migration Pathways
Main Article Content
Abstract
The goal of this project is to modernize BP's OPSD2 Downstream Logistics ETL Solution by moving away from Mainframe DB2 database systems and using Oracle-based DBs and incorporating multiple source data sources through Informatica Mappings using an Agile Governance framework for ETL solutions. This study uses InformaticaPowerCenter's Repository-Integration service architecture to analyze the performance characteristics of InformaticaPowerCenter and find that Read Operations can scale efficiently under high levels of concurrency while Write Operations experience rapid degradations in performance due to the use of exclusive locks on certain tables. Therefore, there is a risk of failure if high- volume deployments are attempted using a Write Operation approach. Performance analysis performed using multiple tools have identified that the write latency is the primary cause of the performance constraint. To alleviate this issue, it is recommended that cache tuning, repository clustering and asynchronous bulk logging be used to meet the operational demands of fuel distribution. Looking forward, OPSD2 intends to evolve into BP's Real-time Net-Zero Analytics Platform by leveraging cloud-native solutions based on Informatica IDMC and Advanced Streaming capabilities.
Article Details
Section
How to Cite
References
1. “Challenges in Downstream Oil & Gas Distribution”, Lasse Jiborn, April 2022, https://www.amcsgroup.com/resources/blogs/challe nges-in-downstream-oil-gas-distribution/.
2. “Solutions in oil downstream logistics”, December 28, 2023, https://inspenet.com/en/articulo/logistics- downstream-oil/.
3. “Oil and Gas Major Retires Mainframe After Upstream Downstream Split”, Amit Garag, Jul 01, 2021, https://www.scribd.com/document/513778639/Oil- and-Gas-Major-Retires-Mainframe-After- Upstream-Downstream-Split.
4. “DB2 to Oracle On-premise Migration challenges”, 2020 Dec 16, https://community.sap.com/t5/technology-q-a/db2- to-oracle-on-premise-migration-challenges/qaq- p/12310862.
5. “How to Integrate Data in the Oil and Gas Industry”, September 25, 2023, https://dataforest.ai/blog/how- to-integrate-data-in-the-oil-and-gas-industry.
6. “Building Real-Time ETL Pipelines with Apache Kafka”, Stefan Sprenger, February 11, 2022, https://datacater.io/blog/2022-02-11/etl-pipeline- with-apache-kafka.html.
7. “Cloud–based Fault Detection and Classification for Oil &Gas Industry”, Athar Khodabakhsh, Ismail Ari, Mustafa Bakir, 11 May 2017, https://arxiv.org/pdf/1705.04583.
8. “Literature Review: A Comparative Study of Real Time Streaming Technologies and Apache Kafka”, Shubham Vyas, Dr Rajesh Kumar Tyagi, DrCharu Jain, Dr Shashank Sahu, 2021, https://cict21.bmiet.net/proceed/pdfs/CCICT2021- 5zzaqZBA554QZHi78VvEEj/239200a146/239200 a146.pdf.
9. “Streaming ETL with Apache Kafka in the Healthcare Industry”, Kai Waehner, April 2022, https://www.kai- waehner.de/blog/2022/04/01/streaming-etl-with- apache-kafka-healthcare-pharma-industry/.
10. “Migrating Batch ETL to Stream Processing: A Netflix Case Study with Kafka and Flink”, Daniel Bryant, Feb 08, 2018, https://www.infoq.com/articles/netflix-migrating- stream-processing/.
11. “Top 3 Kafka Streams Challenges”, Apr 11, 2024, https://www.voltactivedata.com/blog/2024/04/top- 3-kafka-streams-challenges/.
12. “Scaling Kafka for High-Throughput Data Pipelines: Techniques and Tools”, Ashnik Team, Feb 27, 2025, https://www.ashnik.com/scaling- kafka-high-throughput/.
13. “Best practices for scaling Apache Kafka”, Tony Mancill, Jan 24, 2024, https://newrelic.com/blog/best-practices/kafka-best-practices.
14. “Kafka performance metrics: How to monitor”, Evan Mouzakitis, David M. Lentz, Apr 6, 2016, https://www.datadoghq.com/blog/monitoring- kafka-performance-metrics/.