Client: Financial Services Company

Technology: Azure Data Factory, Azure SQL, Power BI

Service: Team Augmentation

Delivery: Migration of Legacy Data Transformation and Data Warehouse 

The client is a collective of financial advisory firms that provide financial solutions tailored to meet their customer’s needs, for both the individual and corporate. It uses market-leading business technology and their own bespoke product solutions to deliver consistent customer outcomes.

Situation

The client 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 Ballard Chalmers being referred for implementation of a replacement data transformation process, data warehouse and reporting layer.

Solution

The recommendation from both Ballard Chalmers 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 financial 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.

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.

“Using SQL Azure, Azure Data Factory and  Power BI, we were able to develop a fast, accurate and reliable management information platform.The Datawarehouse platform provided the client with a set of critical MI reports and the tools and technology required for effective MI reporting.The platform has enabled the client to be self-sufficient to build and run their own reports and grow there MI capabilities” – Senior Software Engineer, Ballard Chalmers