Data Warehouse Modernization
EDW Modernization with Legacy EDW Support for Enhanced Data Insights
A Fortune 500 insurance company needed to modernize its ETL to create a unified data repository and support the legacy system during migration to achieve faster, more accurate analysis and support ongoing business needs throughout the transition.
Client Challenges and Requirements
- Analyze the legacy system for claims and revenue to understand current business of the organization’s 53 OUs.
- Manage the diverse OUs coupled with growing data volume.
- Need for consolidated data warehouse to provide a holistic view of policy, claim and billing data of all the OUs.
- Daily support of the legacy data warehouse.
Bitwise Solution
- Installed and Configured environment setup of required software.
- Analyzed details and designed document for all OUs.
- Consolidated View via generic SSIS packages for all the OUs (Generic Mapping for data across OUs).
- Developed ETLs as per business requirement and performed end-to-end testing.
- Modernized 5 OUs by building ADF pipelines using DevOps framework to migrate Guidewire legacy data to the EDW.
- Provided Maintenance and Support of the legacy system during the modernization process.
- Integrated data mart serving as a base for more advanced BI analytics and reports.
Tools & Technologies We Used
SQL Server
T-SQL
SSIS
SQL Server Agent
Azure Data Factory (ADF)
Key Results
Unified Data Repository (EDW) for all Operational Units (OUs)
Re-designed 70 SSRS reports and created interactive Role Based reports in Power BI
Automated legacy support saved 30% effort, significantly reducing manual intervention
35% cost reduction of month-end closure activities by leveraging consolidated data views
