MigrationCase study
Data Warehouse Restoration
Replacing an ageing Oracle source with Microsoft Dynamics CRM while preserving the semantic and reporting layers users relied on, with SSIS packages generated automatically using BIML.
Before
- An Oracle source that was hard to maintain
- Data integrity issues and slow retrieval
- Slow, error-prone hand-built SSIS packages
- Risk of disrupting critical reports
After
- Dynamics CRM as the new source
- SSIS packages generated with BIML
- Semantic and reporting layers preserved
- Time and cost saved on development
01 · The challenge
Where they started
A leading utilities provider needed to replace the Oracle source behind its data warehouse with Microsoft Dynamics CRM, without disrupting the reports its business depended on.
An outdated source
The Oracle source was increasingly hard to maintain, with data integrity issues and inefficient retrieval.
Manual package development
Building SSIS packages by hand was slow, error-prone and resource-intensive.
Business continuity
The existing semantic and reporting layers had to be preserved so users saw no disruption.
02 · The solution
What we built
Our consultants have combined careful data mapping with automation to restore and migrate the warehouse.
How it fits together
- Oracle sourceRetired
- Microsoft Dynamics CRMNew source
- BIML-generated SSISAutomated ETL
- Data warehouseRestored
- Existing reportsUnchanged for users
Oracle to Dynamics CRM
Mapped and transformed data to the new source, ensuring integrity and compatibility.
BIML automation
Generated the SSIS packages automatically, reducing manual effort and errors.
Layer preservation
Reverse-engineered the semantic and reporting layers and integrated them into the new environment.
03 · Delivery
How we delivered it
- 1
Map
Mapped and transformed every data element from Oracle to Dynamics CRM.
- 2
Automate
Used BIML to generate the SSIS extraction, transformation and loading packages.
- 3
Preserve
Reverse-engineered and reintegrated the semantic and reporting layers.
- 4
Transition
Moved users across with critical reports working as before.
04 · Results
What changed
Better data integrity
Consistent, accurate information across the organisation.
Efficient ETL
BIML automation reduced manual effort, cut errors and sped up development.
No disruption for users
Critical reports and insights kept working as before.
Time and cost savings
Automated package development saved substantial time and cost.
05 · Technology
Built with
- Microsoft Dynamics CRM
- Oracle
- SSIS
- BIML
- SQL Server
Related services
More case studies
Facing something similar?
Tell us what you're working on. A consultant will read it, do a little homework and come back with practical next steps.