Transparity delivered a financial planning business intelligence solution for Attivo, building Azure-based staging and data warehouse layers to integrate Intelliflo and HMRC data. The team used automated daily loads, an incremental star-schema model, and a Power BI Embedded app to provide board-ready reporting and performance insights.
Work spanned discovery and technical design through deployment and knowledge transfer, with assets hosted in the client’s Azure for GDPR control. Explore related services: App Innovation, Azure Consulting, Data Consultancy, and Power BI Consultancy.
Attivo was looking for a partner to develop a business intelligence (BI) system in conjunction with their in-house team. Transparity was chosen because we have experience working with Intelliflo Data and have built a similar reporting system for a previous client.
The project is the creation of staging and data warehouse databases using data loaded from Intelliflo and HMRC. The Intelliflo System provides financial software for Financial Advisors to allow them to manage their clients and client products. However, the Intelliflo system does not offer the expansive reporting capabilities required by the client.
The first part of the project was a period of scoping and design. This analysed the requirements and details of the data load and reports, and came up with a design, detailed in a technical specification.
The technical solution for this BI system consisted of the latest Azure technology:
The design of the database warehouse schema is as follows:
The design contains Dimension tables that are referenced by the Fact tables and contain all the filtering criteria used by the reports.
Next Transparity set up the development environment for the BC and Attivo BI developers to use consisting of:
All of these assets were created in Attivo’s Azure so that all data and all source code are maintained in Attivo-controlled infrastructure for GDPR reasons.
Next, we prepared scripts and deployment documentation to cover the creation of environments in Azure for the Test, UAT and Live environments. The following stage was the creation of scripts to create all the tables for the staging database. This database is divided into multiple schemas.
For the Intelliflo data load, we created an Azure Data Factory Pipeline to carry out an automated daily load of the files delivered by Intelliflo over sFTP into the Staging database tables. The pipeline also updates the audit and ODS tables.
For loading the HMRC data load, an Azure Function and Azure Data Factory pipelines were used to load the HMRC data into the staging database.
At this point, scripts were created to make all the tables for the data warehouse database. Then for the ETL process T-SQL code, and Azure Data Factory pipelines were developed, which transition the Staging operational data store into the star schema of the data warehouse database. This data load was designed to be incremental, meaning it only loads the most recent changes into the data warehouse and not all of the data.
The final stage was the creation of the first Power BI report and the base Power BI Data Model that is available for use in all subsequent Power BI Reports. And the development of a Power BI Embedded application for the viewing of reports
We then provided a knowledge transfer on how to use the Power BI Data Model and the data warehouse database to create additional Power BI reports.
Now the system is live, Attivo has taken over all day-to-day support of the system including managing Azure, creating or modifying Power BI reports and modifying the data warehouse schema or ETL code should it be required.
Transparity is providing support to Attivo for these activities. This support is called on when needed.
The system is now a business-critical system used by the Attivo board and financial planning managers to measure how the business and their associated advisors are performing.
Founded in 2005, Attivo specialises in Lifestyle Financial Planning. Their highly qualified Planners advise on pensions, investments, retirement, tax and estate planning, mortgages and protection, and other aspects of financial planning advice to individuals and businesses.
Attivo has been named one of the Best Financial Advisers to Work For at the Professional Adviser Awards for 3 years running. This award was mostly determined by the results of an employee survey produced by Best Companies Group on the benefits of working at Attivo and our employees’ satisfaction.
