What was The Problem?
Absence from school has a negative link to a child’s attainment. Devon County Council’s (DCC) Education department is responsible for monitoring absence and truancy2 to ensure more pupils regularly attend school and leave with the qualifications they need to succeed.
Collecting attendance data is a complex undertaking. Interventions to support pupils with poor attendance need to be made as quickly as possible.
The key issue the team faced was due to the nature of the data being provided from the source system – it was extremely difficult to decipher the school attendance statistics daily. This was due to the data being presented in the form of large strings, which included non-standard characters (such as “\”,”:”,”;” etc.) to determine the daily attendance.
The team needed a solution that would refresh their data daily, and present accurate data in their front-end Power Bi reporting to support timely decision making by Education specialists and to ensure that children get the education they need.
What Did We Do?
The solution would require a system that can transform the output from the source system into an output that is useful. The transformed data would then need to be stored somewhere accessible by Power BI for reporting.
Utilising the power of Microsoft Azure’s data transformation tool, Azure Data Factory, we established pipelines. We connected a Data Factory pipeline to the source system (Oracle database) to perform the key transformations required to make this data accessible.
Once the data was in an accessible format, the Azure Data Factory pipeline would move the data Into an Azure SQL database. This Is an easily accessible, affordable, and structured means of storing data.
As Azure Data Factory pipelines are highly automatable, we have scheduled the school attendance data to be refreshed twice per day.
Results
This new orchestration means that the Education team can accurately report school attendance data to senior management on a twice-daily basis. This solution has saved time for the council employees as it has automated large chunks of previously manual data transformations.
Phase 2 of this project is to combine this new reporting system with the social care data – which is currently a very manual process. This will then be built into a structured reporting data model in Azure.
What Did The Client Say?