Project Overview
In a cloud reporting modernisation project, Lighthouse Group’s ageing SQL/Excel-based reporting reached end of life. Guided by App Innovation expertise, Transparity and Differentis re-architected the stack using Azure Data Factory for transformation, Azure SQL Database as the warehouse, and Power BI for dynamic management reporting.
The team streamlined legacy logic, matched existing reports for continuity, and applied DevOps (ARM templates, automated tests) for rapid releases. Outcomes included drill-through insights on P&L and debtors, faster data loads (around four hours to ~40 minutes), scalable infrastructure, richer monitoring, and reduced maintenance—supported by Data Consultancy.
The Challenge
Lighthouse Group had invested in Intelliflo’s Intelligent Office platform for managing client portfolios but had a requirement for bespoke reporting above the standard offering. This original bespoke reporting system was based in SQL Server Integration Services, T-SQL and Excel, and had been in place for several years.
But the system and the platform that it was running on was now reaching end of life and the organisation wanted to leverage the best of breed cloud platforms to reduce the dependence on their data centres. Independent Management and IT Consulting firm, Differentis were engaged to review the existing processes and architecture. Which led to Transparity being referred for implementation of a replacement data transformation process, data warehouse and reporting layer.
The Solution
The recommendation from both Transparity and Differentis was to utilise the best of breed cloud services from Microsoft to provide a modern, future-proof process with less requirement for daily management of hardware.
- Azure Data Factory was picked as the data transformation engine, providing a serverless process for migrating and transforming the data.
- Azure SQL Database was used to implement the data warehouse, allowing for scalability and performance.
- Power BI was utilised to provide a dynamic dashboard for management reporting.
The biggest challenge encountered was the complexity of the existing solution, with many areas having evolved from the original documentation. Our engineers were able to work with the finance team to understand what was required from the reports and to isolate the relevant areas of the legacy code so that the new system was streamlined and focussed on the current needs.
The final reports needed to match the existing reports to ensure a smooth migration from one system to the other.
DevOps: The development involved DevOps through automated unit tests and integration tests developed using Specflow and Selenium, and fully automated systems deployment using Azure Resource Manager (ARM) templates and deployment pipelines. Once the system was live and operational DevOps continued as our DevOps engineers made fixes and enhancements to the live system using a very short test and release cycle.
The Outcome
The final solution delivered:
- Dynamic reports in Power BI enables senior management to drill through hierarchical data and extract insights on the current state of Profit & Loss and Debtors.
- The streamlined Azure Data Factory data load process reduces the overall load time from around four hours down to an average of forty minutes.
- Azure Data Factory also helps to deliver detailed monitoring for alerts and logging to understand volumes for each load.
- Azure SQL allows the infrastructure team to be able to scale up and scale down the database to achieve an optimum performance/cost ratio.
The reduction in time needed to maintain the new platform frees up both technology and finance resources to focus on higher-value tasks. And enables greater access to the data for more teams. Legacy servers can now start to be decommissioned, reducing the overall risk to the environment.
