Financial Planning Business Intelligence Solution for Attivo

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 Solution

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 Azure technologies:

• Azure Data Factory for loading and processing data
• SQL Azure for the Staging and Data Warehouse databases
• Power BI Pro for reporting
• Azure DevOps for managing source code, development sprints and deployments
• Power BI Embedded MVC Web Application to view reports in a custom website

The design of the database warehouse schema is as follows:

• A Star schema
• Fact tables similar to the following (Note the Fact tables contain the Measures used in report aggregations):
  • Transaction
    • Monthly Snapshot
  • Payment
    • Rolling 12 Months
  • Payment Adjustment

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:

• The necessary assets such as Azure Data Factory and SQL Azure Database in the Development environment
• Azure DevOps with Repositories for storing source code and projects for managing backlog items and sprints
• Azure DevOps build and deployment pipelines for the deployment of code and Azure resources to have Development, Test and Production environments
• Developer Virtual Machines

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.

The Outcome

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.