Seattle Real Time Fire 911 Calls API Data pulled into Excel for analysis using Microsoft BI tools Power Pivot, Power Query, & Power View
The above video walks through the process by which to use an API to pull data into Excel from a real time database of public information. Note that the database, which can be found at this link, is managed by the Seattle Fire Department. It is made available to the public via a Socrata open data portal, and claims to be updated every 5 minutes.
I’d like to note that “real time data” can mean different things. In this example, it refers to a database that is updated several times every day. The Excel report uses Power Query to pull in the most recent 300 incidents in the database. Excel can then refresh the data from the source whenever the user decides to import fresh data. Because only 300 rows are pulled through the API, the query and processing time required for a refresh is very short.
If you’d like to build your own Excel report using Seattle Real Time Fire 911 Calls data, the following links should be helpful:
- Seattle Real Time Fire 911 Calls Socrata data portal
- Getting Started with the SODA Consumer API
- Getting Started with Power Query
- Power Pivot for the Data Analyst
- Mashing up Data in Power Pivot for Excel 2013
- Pulling Data into Power Query using an API