Analyzing Seattle 911 Data using PowerBI

  • Article

I just discovered that any Socrata-Powered Website supports OData feeds(https://www.socrata.com/customer-spotlight/). OData (https://www.odata.org/) is a standardized protocol for creating and consuming data APIs. OData consumers are applications that consume data exposed using the OData protocol: In Excel, PowerPivot and Power Query can be used as OData consumers.

Socrata has a pretty interesting blog entry on how to consume data from any Socrata-powered website: LINK

To experiment with this, I went over to the Open Data site for the City of Seattle: https://data.seattle.gov, which is a Socrata-Powered Website. One of the datasets available is the Seattle Police Department 911 Incident Response dataset. I used Power Query for Excel to connect to the OData Feed for this data.

Socrata911Seattle

The data available was from 2010 – 2014 and close to a million records of 911 incidents in Seattle. I used the OData link shown in the image above in Power Query to download the data to Excel.  Because Excel maintains an active link to the data, all you need to do to refresh your workbook with the latest data is click the “Refresh All” button in the Excel ribbon. To limit my data for 2014, I used a filter in Power Query to filter the incidents to this year:

Socrata911Seattle1

I created the visualization of the data using Power View for Excel. You can interact with the data visualization below and change the filter on the right to select specific categories of 911 Incidents:

Here is a full screen view of the interactive visualization.

I also created a geospatial visualization of the data using Power Map. Here is just part of that visualization:

[View:https://www.youtube.com/watch?v=8Pej4MDHHUA]

[View:https://www.youtube.com/watch?v=NMgXH29jCcU]