Analyzing Seattle 911 Data using PowerBI: Guest Post

March 12, 2014 12:53 pm PST | Data as a Service

By The ShiSh List


Editor’s note: The following blog was originally published on The ShiSh List and has been cross-posted here with permission.

I just discovered that any Socrata-Powered Website supports OData feeds. OData is a standardized protocol for creating and consuming data APIs. OData consumers are applications that consume data exposed using the OData protocol: In [Microsoft] 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.

To experiment with this, I went over to the open data site for the City of Seattle, 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.

shish 1

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:


I created the visualization of the data using Power View for Excel. Click here to view and interact with the full visualization. 

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

Previous ArticleHow Open Data Policies Unlock Innovation
Effective Governing
How Open Data Policies Unlock Innovation

March 13, 2014

Next Article
Data as a Service
Open Sourcing GovFinder: Windows 8 and Windows Phone Apps

March 10, 2014