Authoritative Single Source of Truth - Data Lake and Warehouse Use case

Our client - a data first organisation powered by a co-ordinated data platform.

As a leading charity, our client has a substantial membership which adds up to a great deal of valuable data. As an organisation, the client needs to be able to understand membership behaviour including things like rates of renewal, churn and engagement. They might want to know which of thier locations receive the most visits, demographic information about those visitors, information about membership renewals along with many other useful data queries and reports.

As part of a broader technical strategy to migrate some legacy systems into the cloud, PTR set about helping them to develop a strategy for handling their membership data and designing a Business Intelligence solution to analyse and report on it. After considering all the options together they decided on a Microsoft Azure Synapse solution with Power BI reporting and visualisation, initially integrating with their new CRM platform but ultimately consolidating data from a whole range of business systems.

The solution made use of several components to create an authoritative single source of truth, with new data pushed into a data lake, from which Azure Synapse Serverless Pool is used to provision rapid access to this new data so it would be available to be queried via SQL. Data is then ingested and transformed into Azure Synapse Dedicated Pool to build an Enterprise Data Warehouse, from which it is consumed by a series of Power BI Semantic models that drive the reports themselves.

PTR carried out the initial infrastructure build with the client data team working alongside to share information on security and data governance. It was important that they established common codes and standards between them so that the work was consistent and repeatable even after PTR had handed over.

First they built out the data pipeline to bring the data into the data warehouse. Then they began modelling it and shaping it into the context that the client needed for their reports. Finally they developed templates and themes for the dashboards and reports which would visualise the data using Microsoft Power BI.

All in all it was a highly collaborative process, working together to get everything done in a speedy and accurate fashion, working hard to achieve consistency by documenting changes, coordinating communication and scheduling regular workshops to maintain momentum and progress.

While the key project driver had been migrating from an on-premise CRM system to the cloud, this project motivated the client data team to take a joined-up approach to their data, resulting in a fully co-ordinated data platform to bring an end to siloed data and fragmented reporting.

As a result the client have re-imagined their approach to data across the whole organisation and now have a unified consolidated data reporting platform in the cloud.