From legacy data warehouse to powerful Azure data services.
Expert Thinking successfully migrated one of the UK’s leading lenders to a new Azure data services solution. Working with the customer we designed a new solution architecture, data model and led the development of the new data warehouse solution, optimising reporting and migrating terabyte volumes of data.
The outcome was a step change in data quality, system performance, stability and capability enabling the business to reduce risks, enhance customer engagement and lower support costs.
The customer had built an on-premise solution using MySQL and a number of reporting platforms (creating over 650 separate reports) that was directly impacting the effectiveness and efficiency of the business. Challenges included:
- Significant issues with data quality and lack of error handling caused frequent service outages, leading to,
- Significant delays and lack of confidence when presenting data to internal and external consumers – fixed reporting dates regularly missed
- Complexity of the system – significant cost and effort required to maintain due to frequent outages
- Near impossibility of introducing new data sources to support the business due to complex design and technical debt
The customer recognised the need for a new ‘data warehouse’ to meet their strategic business requirements, resolve the core issues and align to their strategic Azure cloud strategy, quickly and at a competitive price.
Expert Thinking was asked to manage and deliver the end-to-end solution, working closely with key IT and business stakeholders from solution architecture through to data migration.
“Expert Thinking quickly gained a clear understanding of our aims and desires; then provided clear, practical and targeted guidance.”
The solution was built on Azure data services (ADF with SSIS integrated run-time, SQL database, Azure Data Lake, Azure Monitoring, Key Vault and ADFS, and Splunk) and used Azure DevOps best practice, integrating with the customer’s CI pipeline.
The key activities to deliver the solution included:
- Developing a pilot instance on Azure to test the solution architecture and agree technical direction with the key IT stakeholders.
- Analysing (using Kimball methods) their old data warehouse to optimise data structures based on key business stakeholder needs. Their legacy data warehouse comprised 5+TBs of structured and unstructured data (multiple formats), 650+ tables and seven key source data systems.
- Leading a blended team (Expert Thinking and customer engineers) to develop the solution comprising ETLs/ELTs, data lake, SQL database, security integration and reporting, operational monitoring tools.
- Working with the customer to review and optimise their reporting engines, removing redundant reporting, analysing report structure to ensure warehouse and reports were aligned, tested and delivered.
- Pipeline integration, development of orchestration assets and integration with Azure DevOps (JSON, PowerShell and ARM templates).
- Defining a test framework as part of the build process to ensure code quality, system configuration and security compliance as part of code development and deployment.
- Migrating 5+TBs across from the old data warehouse, working with the customer SMEs to ensure data quality errors were eliminated or “handled” within the new data warehouse.
We successfully delivered the target solution and, in doing so, uplifted the internal capability to be able to maintain and extend the solution, ensuring the necessary skills, environment and culture within the customer organisation to succeed.
When compared with the legacy data warehouse the delivered solution:
- Reduced the number of reports generated by 85%
- Enabled two reporting tools to be decommissioned – reduced complexity and costs
- Reduced cost of service by ~25% (optimised licensing, migration to Azure, and lower cost of support)
- Improved data quality, meeting internal and external compliance standards
- Optimised the data model and reduced complexity – new data sources can be easily integrated
- Incorporated new business reporting and analytics integration – flexible, fast and easy to deliver on-going change
“Working with Expert Thinking has proved to be of major value and has helped drive our cloud adoption at pace.”