What was the problem?
The Public Health Intelligent Team (PHIT) have experience working with data across databases (SQL Server Management Studio, Azure Data Studio), its analysis, and the utilisation of database querying languages such as SQL. The data is presently housed in the Devon County Council (DCC) on-premises servers. With cloud-based storage beginning to supersede the use of in-house storage – for efficiency, cost, and functionality – DCC aims to begin migrating datasets into the Devon Insight Hub (Azure Data Lake).
Smarter Devon will support PHIT in migrating datasets into Azure. The first datasets to be migrated are those pertaining to the National Childhood Measurement Programme (NCMP), which measures the height and weight of reception and year 6 students to assess their BMI. Smarter Devon will support PHIT to migrate this dataset and provide the training and allows for an opportunity to capture a process that standard operating procedures and a data warehouse list can be developed, allowing PHIT to become self-sufficient in this process.
What did we do?
The raw NCMP datasets have been migrated to the Devon Insight Hub (Azure Data Lake).
Training and support have been provided by Smarter Devon to encourage self-sufficiency in this process. A standard operating procedure for this process has been produced to facilitate skill share not just in the PHIT team but organisational also.
Data journey mapping was carried out and highlighted data overlaps and stopped different tools and data sets being introduced which were not needed.
Results
A NCMP dashboard launched at the beginning of August 2022, it has been designed to aid decisions, interventions and advocating to improve childhood obesity.
The following objectives have been achieved
- Understanding the trend of prevalence of overweight (including obesity) in Reception aged children for Devon, SW, England
- Understanding the trend of prevalence of overweight (including obesity) in Year 6 aged children for Devon, SW, England
- Understanding the differing levels of IDACI by LSOA across Devon
- Understanding the differing levels of overall IMD by LSOA across Devon
- Understand BMI categories by LSOA for Reception aged children over a five-year average (2015/16 to 2020/21)
- Understand BMI categories by LSOA for Year 6 aged children over a five-year average (2015/16 to 2020/21)
- Have clear visualisations to indicate if there is an association between Reception BMI overweight (including obesity) and IDACI
- Have clear visualisations to help understand if there is an association between Year 6 BMI overweight (including obesity) and IDACI
- Have clear visualisations to help understand if there is an association between Reception BMI overweight (including obesity) and IMD
- Have clear visualisations to help understand if there is an association between Year 6 BMI overweight (including obesity) and IMD
- Have clear visualisations to help identify where the children centres are across Devon in respect to prevalence of overweight (including obesity) for reception aged children
What did the client say?
“I am so pleased that we collaborated with Smarter Devon on this project! Historically this data was saved on the local server and sometimes in different places. Analysis was static and generally undertaken by request. Now there are clear processes and procedures around flowing the data into Azure with a fantastic dashboard which will be updated automatically. The skills and capability that were acquired throughout this work have transformed our way of working. It has enabled us to become incredibly strategic in our approach, focused and efficient delivering high quality outputs to support with health promotion and decision making to improve population health and wellbeing. I would recommend any team to prioritise a little time and resource to go on this journey because the time saved, the experience gathered, and the value of the output is certainly worth it! Our team has not only acquired new skills and best practice in Power Bi and Azure, but this project has also inspired us to develop best practice within Public Health Intelligence around project management, engagement, communications, information governance, data ethics and so much more!” –
Maria Moloney-Lucey – Public Health Specialist
Top tip
“When working with Power BI, always use measures to populate your visuals – it reduces processing time and makes your dashboard faster!”
Miranda Montano – Public Health Information Analyst (Report designer)