What was the problem?
Children’s Services department have been working with Power Bi to do reporting. The main problem that this department was facing was the large amount of time it takes to report on key metrics. Due to the complexity of the reporting and data requirements, a lot of time is spent processing and manually running SQL scripts every day. This is quite an arduous process.
Many steps needed to be followed to access this data – they needed to connect to a VPN, to get through to a Virtual Desktop before they could extract the data.
This solution needed expediting, using Azure to save the MIS team huge amounts of time!
What did we do?
Working with Peak Indicators we needed to make the data available in the Azure SQL DB. This would mean that users could query the vast amounts of data directly, rather than needing to connect to various systems before extracting the data. This changed a multi-step process into a single step (setting the foundation to query directly from Power BI to Azure SQL DB in the future).
During the creation of this 1:1 relationship between the Postgres DB and the Azure SQL DB, we wanted to create an incremental loading process (so only updated data is brought to Azure). This process faced some challenges as we found that data quality wasn’t always very good. There were some issues around duplication and lack of keys and dates. As we realised that the solution would not be at its most efficient with low data quality, we started looking into addressing this problem as well. To solve the low data quality problem, the first step we are taking is utilising Azure ML functionality to add an additional check for Data Profiling . A set of OFSTED data will be used for the initial implementation.
Results
Creating the 1:1 relationship between the source data (Postgres DB) and the Azure SQL DB has made the process of Children’s Services accessing data much more streamlined.
The first phase also included an automation of the main BAU Reports currently produced. This reduced the workload for the current team, giving them with more time to focus on key objectives.
Utilising the Azure SQL DB has increased the computing power available to the queries in the BAU reports, which are refreshed via an agreed schedule on Azure Data Factory. This means that performance and speed per query is increased.
With the framework now in place to identify specific data profiles daily, we are committed to provide the tools to the Children’s Services Team to support the OFSTED requirements more accurately and with much higher quality. This will help the service in delivering the highest level of information to OFSTED.
What impact did this have?
“Data needs to be considered a valuable commodity and put on the same footing as money. It allows us to choose the right projects for the right support. We can do more for less when intelligence from data illuminates our decision making”.
Sofie Francis, Project Lead at Smarter Devon