A child’s attendance at school impacts the outcomes they have in later life. Monitoring attendance is always important, but during the Covid 19 pandemic it became critical. When the COVID-19 crisis struck, and schools closed to all except vulnerable children and those of key workers, the existing reporting needed to be enhanced to make sure that children were kept safe.
Data has proven to be a key tool in the battle against Covid-19. As such, Devon County Council responded to these obstacles by working in a collaborative way with Peak Indicators to access and leverage their data in the best way. One of the key challenges that surfaced was how council workers accessed school attendance data – specifically for vulnerable children.
What was the problem?
In the existing system (Capita one) – it was very time consuming for council workers to access data for vulnerable children, to check whether they were attending school or not. The COVID-19 crisis was a developing situation and schools were being closed, which increased the necessity of this solution.
There was an existing system in place to distribute the data on a daily basis to the council employees that needed access to it. However, the overall process of accessing this data took approximately 2 hours per day.
As Devon County Council are committed to delivering the best service to its constituents – creating easier and secure access to this data whilst saving time was imperative. By solving this challenge, council workers would be able to dedicate more time to supporting vulnerable children, in a situation where time is a vital resource.
What did we do?
Working with Peak Indicators to solve this challenge, we worked in a partnership to develop a creative solution to this urgent problem.
Working as a partnership, teams from Devon County Council and Peak Indicators built upon the existing system using a variety of Microsoft data analytics tools including Azure Data Factory, and Power Bi.
The solution developed required strong collaboration, with Devon County Council providing domain expertise, and Peak Indicators providing the technical expertise.
Peak Indicators utilised their extensive Microsoft data skills to create a solution that made the school attendance data both legible and reportable. The data was then reported in Power Bi dashboards that can be accessed by council employees at the click of a button.
Further to this, there were security parameters set in place so only authorised council employees could access this sensitive data.
Results
The result of this cross-organisational collaborative effort was an end-to-end data extraction and reporting solution. The key requirement, of refreshing data daily without occupying a lot of council worker time, was met.
This was achieved using an automated process which ensured that the attendance data for vulnerable children could be refreshed several times a day allowing rapid access to insights whilst cutting labour intensive administrative tasks.
Innovative features of this solution include:
- Azure Data Factory Pipelines scheduled to refresh data 7 times per day – ensuring the data is up to date at all times.
- Monitoring is enabled and will send an email to developers if any loads fail – creating peace of mind.
- Slowly changing dimensions established so a history can be built into the Azure solution that does not exist in the source.
- By utilising the tagging feature of Azure, we can understand the cost of running this implementation.
- Control tables are utilised – meaning changes are controlled in 1 place.
By creating this solution, Peak Indicators and DCC have saved council employees 2 hours of their time, per day – in a time when councils need as much time as possible to serve their communities.
What did the client say?
“When the solution came on line it sorted everything out for everybody – it’s something that’s there sitting that can be used”.
Cathy Williams