Data Warehousing, Dashboards and Management Pack
Synopsis:
Connection of Salesforce, and multiple additional sources of company data (via. FTP) to Azure SQL Database and PowerBI to create an automated board pack for bi-monthly board meetings.
Detailed Description:
The business, then consisting of 38 business units across 4 divisions, generating turnover > £180mn had begun to outgrow the data management capabilities of many departments. Further to this, the business did not have an ERP platform in place, so Accounting & Finance data had to be consolidated manually, as did many other aspects of critical board level management information such as Operations, Health & Safety and a growing M&A function.
Historically, board packs would be produced collaboratively, which required an estimated 30 days of effort combined across the various functions, however as the business grew, this task became a greater burden on stretched resource.
I was tasked with finding a solution to this problem, so carried out a short investigation of how this data was being produced, and decided that the creation of an AzureSQL database with PowerBI visualisation would provide a simple, scalable and rapid solution to the problem. Other solutions such as Salesforce Analytics and Tableau were considered, however due to increasing licensing costs and the time required to overcome the skills gap in new departments having to learn to use a new tool, the simpler option was preferable.
The solution allowed for a simple process whereby users could deposit data in a structured format into a local / online file storage system. This required some reformatting of existing sources of data, and the use of Dataloader.io (existing licensing in place) to carry out the FTP and mapping into the Azure SQL database.
Data validation issues were prevented by restricting the data at source, and creating hidden mapping tables in the upload templates that prevented human error from causing formatting errors or duplication.Aside from the external sources of data, which were largely unstructured, reporting snapshots of the Salesforce Sales and Service Cloud data had to be created and synchronised on a schedule, which was ran through the Azure SQL managed instance.
Data from Sales, Commercial and Service functions would be sent automatically, and stored in AzureSQL tables, which could then be easily connected to PowerBI for visualisation. We took care to include properly formatted date fields to allow for like for like comparison in PowerBI, which was something that hadn’t been available digitally prior to this project.
Data from HR, Health & Safety and Accounts & Finance was added manually, and parsed into the required data structure, before being committed to the database using a script developed by a member of the in-house technology team.
As a result of this project, we were able to cut the time required to produce this data in half; a saving of approx. 15 days every two months. Further to this we introduced an interactive, standardised, professionally branded and automated board pack, which was distributed to the board and shareholders automatically using PowerBI dashboards.
Project:
Data Warehousing & Automated Management Pack
Date:
July 2020 - October 2020
Technologies Used:
Salesforce Sales Cloud
Power BI
Azure SQL Database
Dataloader.io
My Role(s):
Business Analyst
Project Manager
Systems Administrator
Budget:
£40,000 project cost