Migrate Informatica PowerCenter ETL to Microsoft Fabric

A ‘Big Four’ global accounting organization required an efficient solution to migrate its on-premise legacy Enterprise Data Warehouse built on Informatica PowerCenter and SQL Server to Microsoft Fabric Pipeline, Fabric-PySpark Notebook and Azure SQL Managed Instance. The solution needed to leverage the advanced capabilities of the Microsoft Fabric ecosystem to enhance data integration and workflow orchestration.

Client Challenges and Requirements

• Detailed assessment of identified workflows due to lack of documentation on current system.
• Migrate Informatica PowerCenter to Fabric Pipelines / Fabric-PySpark Notebooks as part of program to modernize the Enterprise Data Warehouse.
• As-Is migration of frameworks which were tightly coupled with Informatica to make them Fabric compatible.
• Stringent timelines to complete the migration.
• Limited Fabric awareness with stakeholders.

Bitwise Solution

• Conduct automated assessment using Source ETL Analyzer tool for detailed analysis of legacy jobs.
• Bitwise used assessment reports throughout the project to reduce turnaround time and increase accuracy with analysis for source and target information used within ETLs, pattern findings, lineage and schedule information.
• Migrate workflow parameters from text file storage to a metadata-driven parameter table for enhanced scalability and management.
• Develop reusable notebooks to replicate Informatica UDF logic within Microsoft Fabric using PySpark.
• Configure Fabric Notebooks with environment-specific settings by integrating a package containing library functions and a database configuration file linked to Azure Key Vault for secure credential management and access control.
• Provide standardized documentation while creating knowledge base and improved end-to-end ecosystem support and observability.

Tools & Technologies We Used

Key Results

Reduced 40-45% assessment efforts using Bitwise Source ETL Analyzer for code analysis

Achieved up to 78% automation in converting Informatica mappings to Fabric Notebooks, resulting in a 30% reduction in the overall effort for the migration

Enhanced project efficiency and accuracy by leveraging automated ETL migration tools and conducting detailed assessments

Implemented robust data quality checks and validation processes, ensuring data integrity and compliance