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 learn more about how you can use data to develop public service, download our latest eBook on organisational growth.

Download the SQL eBook

See more examples of what you can do with Power BI

The post Bringing government data to life appeared first on Microsoft Industry Blogs - United Kingdom.

As government IT administrators evaluate Microsoft’s new database, here are some of the capabilities they won’t want to miss:

Advanced analytics

Industry analysts recognize the breadth and depth of Microsoft’s capabilities in data, intelligence, and the cloud. Microsoft is the only company recognized as a leader across data platforms and cloud by Gartner in both vision and execution, in database, business intelligence, advanced analytics, data warehouse, cloud infrastructure and cloud application platforms.

SQL Server 2016 builds on this reputation by bringing next-generation, advanced analytics to governments, helping IT administrators turn vast amounts of data into actionable insights. The database uses in-memory technology to improve the speed and performance of data queries. It includes the ability to run the R open-source programming language within the the database itself, opening up the opportunity to perform advanced analytics right from within SQL Server. SQL Server 2016 also includes PolyBase, a new technology that makes it possible to extract value from both structured data and unstructured data without the time and expense of moving it around or transforming it into a common format.

Used in combination with other Microsoft technologies, SQL Server 2016 puts data at the fingertips of government workers. For example, employees can use Power Query to pull data from numerous sources into data models and analytical reports. Moreover, they can view these insights as rich visualizations using their Windows, iOS, and Android mobile devices. Whether it’s predicting future outcomes from past trends or analyzing the results of public programs, SQL Server 2016 can deliver the advanced insights that governments need.

Enhanced security and encryption

SQL Server already has an outstanding security record. In fact, for the last six years, it has had the fewest security vulnerabilities of any major database on the market, according to the National Institute of Standards and Technology. SQL Server 2016 builds on our commitment to security by offering several additional features to ensure that government data is always protected.

Always Encrypted is an industry-first technology that helps protect data by encrypting it while at rest, in use, and when processing queries—with the decryption key provided only to authorized users. By using Dynamic Data Masking, IT administrators can create rules that hide sensitive data such as phone numbers, social security numbers, and bank information to prevent unauthorized access. Similarly, Row-Level Security enables IT administrators to restrict the information employees can see in database tables based on their role within the organization. And an enhanced AlwaysOn Availability Groups improves database recovery by allowing up to three synchronous replicas for auto-failover across domains.

Improved hybrid environments

SQL Server 2016 is designed to work in a hybrid environment, delivering a flexible and consistent experience both on-premises and in the cloud. New tools in SQL Server and Microsoft Azure make it possible to shift database content between a government agency’s on-premises infrastructure and the cloud, to back up on-premises databases to Azure, and to access resources no matter where they’re stored. For example, IT administrators can pin a Reporting Services paginated report item to a Power BI dashboard so they can view all their information in one place. They can also access on-premises data from the Power BI service without the need to move the data to the cloud. In addition, they can take advantage of Stretch Database to automatically move on-premises data to the cloud for archiving after a specified length of time, freeing up valuable on-premises storage space while maintaining easy access to their data in both environments.

All these features are built directly into the database without the need for expensive add-ons. To learn more about how SQL Server 2016 can benefit your government agency, please see Joseph Sirosh’s blog post, “SQL Server 2016: The database for mission-critical intelligence.” Also, watch the recent webcast in which Microsoft CEO Satya Nadella and other leaders discuss the power of Microsoft’s new database.

Download the Microsoft SQL Server eBook

The post Unlocking the power of big data appeared first on Microsoft Industry Blogs - United Kingdom.