Data Warehouse Modernization
Migrate Informatica PowerCenter ETL to Azure Synapse Analytics
A global insurance and annuities provider needed to migrate its on-premises legacy ETL platform from Informatica PowerCenter to the cloud to leverage the advanced capabilities of Microsoft’s Azure ecosystem, including Synapse Analytics, to enhance data integration, workflow orchestration, and overall efficiency.
Client Challenges and Requirements
- ETL Modernization project required migration of Informatica Power Center to Synapse Pipelines / Synapse-PySpark Notebooks / Synapse Dataflows
- Migration of Informatica components like Power Exchange, Data Exchange, UDT
- Migration of frameworks like ServiceNow incident creation and email notifications for failure of jobs
- Applications like Oracle, DB2, Mainframe, HDFS, Mongo DB, etc. used multiple sources and target systems
Bitwise Solution
Bitwise provided automated ETL Migration services using its proprietary ETL Converter suite to accelerate project execution within defined cost and timeline constraints.
- Conduct automated assessment using proprietary Source ETL Analyzer tool for detailed analysis of legacy jobs provided for four applications.
- Develop custom solutions on Azure using Java, UDT, and other connectors to handle diverse data sources, including Mainframe systems.
- Integrate a metadata-driven parameter table to manage file-based source data, enhancing scalability and simplifying management.
- Configure Synapse Notebooks with environment-specific settings by integrating package libraries and a database configuration file linked to Azure Key Vault for secure credential management.
- Provide comprehensive solution documentation and code walkthroughs for all four applications to ensure a smooth handover and future support.
Tools & Technologies We Used
Bitwise Source ETL Analyzer & ETL Converter
Informatica PowerCenter, Developer, Power Exchange, Data Exchange
Synapse Pipelines, Dataflows, PySpark Notebook
Azure Logic App, Azure SQL DB
Oracle, DB2
Key Results
45% reduction in assessment efforts using Bitwise Source ETL Analyzer for code analysis
50% automation in converting Informatica mappings to Synapse Dataflows/Notebooks
Highly efficient accurate migration using automated tools and detailed assessment
Data integrity and compliance with robust data quality checks and validation processes
