Seattle Building Permits visualized in Power BI with Socrata OData and data.seattle.gov

 

Living in Seattle, it’s quite fun to keep track of what’s happening through data.  The Open Data initiative from the City of Seattle is a treasure trove of information that you can explore here: https://data.seattle.gov.  This is all power by Socrata, as are many other open data initiatives for Cities and organizations.  I wanted to explore that data using Power BI, specifically Power BI designer.  In a future post, I’ll show how to monitor insights using the new Power BI Dashboards feature, but that’s for later.

I wanted to look at Building Permit data – since there’s a lot of construction happening in and around town.  I was able to find the “Building Permits : Current” data set which lists “Building Permits issued in the past five years or currently in progress”.  You can see it here:

https://data.seattle.gov/Permitting/Building-Permits-Current/mags-97de

I pulled that data into Power BI Designer.  You can download the designer at the link below if you’d like to try it for yourself: 

https://www.powerbi.com/dashboards/downloads/designer/

You can follow the steps below or play with the final file, download it from here:

Seattle Building Permits OData.pbix 

Let’s walk through step by step:

The data.seattle.gov site for building permits that exposed through Socrata.
1. Click the Export option
2. Select OData
3. Copy the OData URL

 

Open Power BI Designer.
1. Under "New Source" select "OData Feed".

 

Fill in the URL and press OK.
1. Put the OData Feed URL here.

 

Start using Power BI Designer to explore the data.
1. There are the fields from the OData Feed.  We call this the "Fields List".
2. You can drag fields from the "Fields List" into this area we call the "Canvas".

 

If you drag "value" to the canvas, a default visual is created.
1. This is the field I dragged.  You can also check the check box.
2. Use this button to quickly change the visual. 
3. You can also change visuals from here. Just make sure to select the visual you want to change first.

 

Here's my first report after playing with the data for just a few minutes.
1. This is the total value of all permits returned by the OData feed.
2. This is the distinct count of permits by permit_type.
3. This tree map shows that Commercial is the largest category by total value.  Interestingly it shows all the applicant names and how those applicants contribute to the total value of each category. 
4. This trend shows the distinct count applications by date.
5. This data point is fascinating!  The data sets has 5 years of building permit data and any "current" permits.  So permits from before this point are really old.  Might be worth investigating more why they're still open.

 

The report is interactive by default.
1. I clicked on "COMMERCIAL" here and it  cross filtered all the other visuals for me.  So I can see the trend and totals just for what I selected.

 

When I switch to "Query" I can see how the data was loaded.
1. Clicking here switch to show the data that was loaded.   Clicking "Report" takes me back to the report.
2. I can have multiple queries in a report.  They all show up here.  To get more queries, select "New Source" in the ribbon.
3. I can work on the data to transform it, mash it up, clean it up, or what have you.  We call these actions "Steps".   The steps show up here.  You view and fix the steps.

 

Odata can contain nested data.  You can work with it without a problem.
1. This column has more data.  It will shows as "Record", "Table", or "List" in the UI.
2. Press this button to expand it.

 

This is my query after I expanded a few columns.  I selected the green column so they're easier to see.
1. Notice how I have multiple steps now.  They're sequential so I can click through them to see what the query is doing.
2. I can always edit a step by pressing here.

 

I created a page that shows where each application is planning to build.

 

Using filters and different visuals, you can quickly find insights.  Here quickly see which applicants have applied for permits.  I've captured a view that shows that Patterson has applied for many more permits than Belcher, for example.  But for both those applicants, the total of their permit values is larger than half a million dollars.

 

There it is.  A simple interactive view of Seattle building permits data.  The OData feed from Socarata works great but unfortunately doesn’t include zip code information.  That would have allowed me to create a density map of neighborhoods showing where investment is going. Maybe someone from the City of Seattle will read this, add the zip code and I’ll do a follow up. 

Next step will be publishing this to Power BI to monitor it.  I’ll do that in a future blog post.

Let me know what you think in the comments.  Would love to answer any questions you might have.

HTH,

Lukasz

Comments

• Anonymous
  March 02, 2015
  Excellent. Perhaps we will do this in one of our upcoming Madison Modern Excel User group meetups!