Microsoft shows a few of the ways that governments can turn data into insight
In today’s digital era, governments across the globe are amassing larger amounts of data than ever before. Some of this is structured data such as census records, phone numbers, addresses, and any information that can easily be entered into a database or spreadsheet. And some of it is unstructured data, or harder-to-analyze information such as emails, documents, web pages, photos, and videos.
With greater emphasis on data sharing, evidence-based policy and smarter, more citizen-centric services, the key is turning data into actionable insights that lead to higher-quality decisions. Yet analyzing the skyrocketing quantity of information can be an overwhelming task.
Tools such as Power BI can help public sector organisations overcome this challenge by making it simple to transform vast amounts of data into rich visuals that can be examined from multiple perspectives.
Fully compliant with the G-Cloud, Power BI enables officials to review data in visual form, making it easier to identify critical patterns that move polices and services forward.
We examine how Power BI can be used by governments around the world to turn publicly available data into critical insights.
Analyzing and responding to accidents
Here in the UK, Altius observed that large amounts of data recording fire and traffic accidents could be mined and analysed to better understand what causes accidents, and how services could be improved to better respond.
Fire accidents in London
Using Open Data published by the London Fire Brigade (LFB), Altius obtained the detail records for every incident that LFB responded to between 2009 and 2016.
Using the powerful data import and transformation capabilities of Power BI, Altius loaded the incident and attendance records provided as a series of Excel spreadsheets, and created a relationship to allow them to associate incidents with the incident attendance by LFB.
The results are presented in a series of reports allowing Altius to visualise the areas within London Boroughs to see which locations suffer from higher numbers of incidents.
A series of filters and slicers allow an exploration of fires, false alarms and other incidents over time within each Borough. Taking advantage of Power BI’s custom visualisation capabilities, Altius have been able to present the data as series of maps using different sized square blocks to represent the number of incidents.
As well as using a heat map to show the concentration of incidents in a particular area, by joining the incident data with the attendance data Altius have been able to map the average response time for each area within the borough.
This meant Altius were able to highlight key patterns and areas of improvement for the fire service. For example, plotting the incident and attendance information over time meant Altius have been able to highlight a close correlation between the number of incidents and the attendance time when viewed by day of the week and hour of the day.
See how Altius did this here.
Accidents on the M25
In the same way Altius were able to find patterns from the LFB’s data on fire accidents, so they were able to use Power BI to analyse data for car accidents on the M25.
Altius downloaded an Open Data package provided by the Department for Transport containing detailed road safety data. The accident, vehicle and casualty records were loaded into Power BI and a model created to join the many tables of data.
From this information Altius created a report for accidents occurring on the M25 motorway around London. As shown below, the accident data is broken down by time and shown by month, day of the week and hour of the day. The accidents are plotted on a map and can be shown by Local Authority area and drilled down all the way to the individual accidents, coloured by severity of the accident. A treemap visual can be used to filter the analysis by each of the police forces responsible for the different stretches of the motorway.
Further analysis is provided breaking down the data by age and gender, showing the in general most accidents are by men in the age range 26-35 in fair weather away from a junction. The various visualisations can be used to explore different data scenarios. For example, viewing accidents in fair weather, the A1 / M25 junction appears to be a particular hot spot, however in wet weather, this is overtaken by the M1 / M25 junction.
See how Altius did this here.
Car Accidents in Australia
In the same way Altius inputted UK data into Power BI, here at Microsoft we used Power BI to analyse data about car accidents across Australia.
Accessing data about Australian car accidents from 2010 to 2015, we obtained detailed breakouts of vehicle collisions across the continent, including the time, location, and severity of the accident; the participants involved; whether alcohol was a factor; and much more. We then visualized this data using Power BI, observing some of the conditions that cause large numbers of accidents.
For example, reviewing the number of accidents in the morning versus the evening for every day of the week in bar graph form, we were able to see a noticeable bump in the number of alcohol-related accidents between Friday and Saturday.
Likewise, examining accident patterns in different speed-limit zones, we were able to conclude that more collisions with a fixed object occur in the 100-km-per-hour speed zone than at lower speed zones. With information such as this at hand, governments can create more measures to deter drunk driving on weekends. And they can lower speed limits in areas where there is a high rate of accidents.
Responding to citizen and budget needs
In the US, Microsoft observed that data around complaints and government spending could be analysed visually to inform policy-making decisions and public services.
Citizen Complaints in New York
Analyzing New York City’s detailed 311 data for 2015, we obtained detailed information about the calls being made, including the time of day, location, incident type, and agency involved—leading to insights about where the city can ramp up its services.
Analyzing citizen complaints by month, for example, we were able to see that the highest number of complaints in February were related to street conditions, blocked driveways, and snow.
Similarly, analyzing certain types of complaints by time of day, we learned that most garbage-related complaints occur between 8 a.m. and 11 a.m., while most noise-related complaints occur between 11 p.m. and 3 a.m. With these insights, the city could secure snow removal equipment before it’s needed. It could also work with trash removal services to deter complaints, while enforcing noise ordinances during the hours when complaints are highest.
Government Budgets in San Francisco
Lastly, we examined San Francisco’s budget data from 2010 to 2016, gathering detailed information at the departmental level. Visualizing this information using Power BI, we could easily observe where budgets are growing. We could also see where most of the revenue is being generated.
By comparing revenue to spending by department, for example, we observed that Public Works, Transportation, and Commerce make up the vast majority of both spending and revenue for the city. Examining this data in greater detail, we were also able to see which departments are consistently over budget from year to year. With these insights, the city could either allocate larger budgets to those departments that consistently come in over budget, or focus on ways to increase revenue or cut back spending within those groups.
—
These five demos show just a few of the ways governments can turn data into critical insights. By putting increasing volumes of data to better use with Power BI, governments have the opportunity to improve efficiency while better serving their constituents.
To find out more about how Microsoft can help you make the most of data, download our guide to unlocking organisational growth with data