Four years ago, Chicago led in the innovation of open data portals. The city was first to appoint a Chief Data Officer and, subsequently, the number of datasets grew to include detailed information on crimes in Chicago, building violations, food inspections, and up-to-date information on 311 calls for service, plus a lot more. It has been a worthwhile investment that has sparked a vibrant civic tech community, start-ups using open data to drive their business, and has even been used by the city to improve efficiency and save money.

To build on this, we developed OpenGrid, which uses that same data but in a more user-friendly interface. You can navigate through more than a dozen datasets at once. Below the fold, we have some quick tips on how you can use OpenGrid.



We have revamped the presentation of data about lobbyists on our Open Data Portal. People lobbying the City of Chicago are required to register with the Board of Ethics and file periodic reports through its Electronic Lobbyist Filing System (ELF), which began collecting data in 2012. The data structures are complex and previous attempts to recombine data about lobbyists, their employers, their clients, and their lobbying activity on behalf of these entities into tabular datasets ended up being difficult to understand.

In our new approach, we are publishing the data in a way that more closely matches the structures in the source system. Most of the datasets contain only a single type of information, with sufficient common IDs between the datasets for users to link them, as needed.  The subjects of these datasets are:

There is one dataset that does combine multiple types of information. It links a lobbyist and his or her employer and clients. This dataset contains elements from the Lobbyist, Employers, and Clients datasets but has been combined to give a single view of information from all three in order to show the most central set of relationships in the data:

Connections Between the Datasets

The new datasets have the indicated ID columns in common to allow for linking between datasets.

An ERD diagram of relationships between the new Lobbyist Datasets

The relationships between the new Lobbyist Datasets

Older Data

The older datasets are still present on the Data Portal but marked as either “Historical” or “Deprecated” datasets.  The Historical datasets are based on the predecessor system to ELF and contain data prior to 2012.  The Deprecated datasets are the previous presentation of ELF data, beginning in 2012.  (There is discussion of our general approach to dataset deprecation in this post on our Data Portal Status Blog.)

As always, we welcome comments and questions on these datasets at or @ChicagoCDO.


Just one percent of Chicago’s buildings comprise 20 percent of the total energy consumed. For the first time, the City of Chicago is releasing detailed information on the energy consumption and efficiency of the largest municipal, commercial, and institutional buildings – those over 250,000 square feet – on the city’s open data portal. The release provides the data and transparency that is useful to find opportunities for find energy efficiency.


One of the important lessons we’ve learned with open data is to leverage technology to automatically update data. There are a number of benefits to this: leveraging technology means we can update data every day, every hour, or even every 10 minutes. Providing data that is reliably updated also means companies, like Chicago Cityscape and EveryBlock, and civic developers can have confidence that fresh data will be available in their applications. Every day, a little over 100 scripts run to keep the data portal up-to-date using an internal “framework” we’ve developed for this process.

Last year, we released the software we used to drive our Extract-Transform-Load (ETL) process that automatically updates our data portal. Today, we’ve released version 1.2.0 with some new enhancements.

Windows compatibility

While the software is compatibility with Windows, there have been a few utilities that were only compatible on the Linux and MacOS. With this release, we’ve brought full compatibility to Windows, so you can use the useful ETL Log utilities. As always, this utility is platform agnostic so you can develop ETLs on a Windows machine and deploy it on Red Hat, or vice versa, with no modifications.

For instance, suppose I am experiencing issues with uploading data on Beach Water Quality. It appears to take a long time, but need to discover if the performance is unusual. I can use the A_ETLRuntimes.bat utility to summarize the time it. We refer to each data set by it’s unique ID, in the case of the beach data, that is “qmqz-2xku” (see the URL):

C:\path\to\open-data-etl-utility-kit\Log>A_ETLRuntimes qmqz-2xku

The output from the command summarizes the run-time:

INFO 01-08 00:45:31,840 - Kitchen - Processing ended after 9 seconds.
INFO 01-08 01:45:30,199 - Kitchen - Processing ended after 10 seconds.
INFO 01-08 02:45:31,140 - Kitchen - Processing ended after 9 seconds.
INFO 01-08 03:45:28,912 - Kitchen - Processing ended after 9 seconds.
INFO 01-08 04:45:36,126 - Kitchen - Processing ended after 10 seconds.
INFO 01-08 05:45:34,713 - Kitchen - Processing ended after 10 seconds.
INFO 01-08 06:45:30,634 - Kitchen - Processing ended after 9 seconds.
INFO 01-08 07:45:30,623 - Kitchen - Processing ended after 10 seconds.
INFO 01-08 08:45:29,526 - Kitchen - Processing ended after 9 seconds.
INFO 01-08 09:45:31,162 - Kitchen - Processing ended after 10 seconds.
INFO 01-08 10:45:29,547 - Kitchen - Processing ended after 10 seconds.
INFO 01-08 11:45:30,572 - Kitchen - Processing ended after 11 seconds.
INFO 01-08 12:45:30,549 - Kitchen - Processing ended after 10 seconds.
INFO 01-08 13:45:30,839 - Kitchen - Processing ended after 11 seconds.
INFO 01-08 14:45:31,076 - Kitchen - Processing ended after 10 seconds.
INFO 01-08 15:45:30,413 - Kitchen - Processing ended after 10 seconds.
INFO 01-08 16:45:36,389 - Kitchen - Processing ended after 15 seconds.
INFO 01-08 17:45:30,481 - Kitchen - Processing ended after 10 seconds.
INFO 01-08 18:45:26,824 - Kitchen - Processing ended after 8 seconds.
INFO 01-08 19:45:35,574 - Kitchen - Processing ended after 11 seconds.
INFO 01-08 20:45:29,922 - Kitchen - Processing ended after 9 seconds.
INFO 01-08 21:45:27,716 - Kitchen - Processing ended after 9 seconds.
INFO 01-08 22:45:28,732 - Kitchen - Processing ended after 10 seconds.
INFO 01-08 23:45:30,488 - Kitchen - Processing ended after 10 seconds.
INFO 02-08 00:45:29,156 - Kitchen - Processing ended after 9 seconds.
INFO 02-08 01:47:32,107 - Kitchen - Processing ended after 2 minutes and 14 sec
 onds (134 seconds total).

It appears that the most recent ETL did take longer than normal–over 2 minutes compared to the typical 10 seconds.

There are a handful of these utilities now compatible on Windows machines (and Linux/Mac):

  • Summarize ETL run times
  • Show the files associated with a particular data set
  • Show ETL logs from today
  • Run a specific ETL based on its name (see below)

Quick update from the command line

While it’s best to schedule uploads ahead of time, sometimes it’s convenient to run a one-time, unscheduled upload. Now, it’s a little easier to do this from a Linux/MacOS shell or Windows command prompt. Using that same unique ID as before, we run the ETL with:

C:\path\to\open-data-etl-utility-kit\Log>A_RunETL.bat qmqz-2xku

For Linux or Mac:

$ cd \path\to\open-data-etl-utility-kit\Log
$ ./ qmqz-2xku

Soon enough, your data set will be updated on the portal.

With the opening of swimming season, the Chicago Park District water sensors at Lake Michigan beaches are live again and streaming to the data portal hourly.

That dataset has a partner dataset, beginning this year, Beach Weather Stations. The Park District places land-based sensors at some beaches to measure air temperature, humidity, rainfall, wind, barometric pressure, and sunlight. The data collected are also streamed to the data portal hourly.

Finally, in order to facilitate geographic analysis of the water and weather data, we have published the locations of the beaches where the sensors are in operation.  Note that not all sensors are active at all times so locations sometimes will be listed that are not currently providing data.

Image by Basheer Tome.

Our Open Data Portal now includes a new view type − Data Lens. These views do not present any new data. Rather, much like charts, they offer a way to summarize and explore existing datasets.

Data Lens pages are built around the concept of cards, with each card on the page showing a different way of summarizing the data in bar chart or map format or of filtering by a particular column. The real power of a Data Lens page is that the cards are linked so that filtering in one card filters all the others. For example, consider Food Inspections:

Food Inspections Data Lens

The cards above give a quick summary of food inspections, as a whole, but suppose one is most interested in inspections of Risk 1 establishments. Simply click that bar on the Risk card and all other cards update correspondingly.

Food Inspections Data Lens Limited to Risk 1 Establishments

But how about just the Risk 1 inspections with a Result of Pass? Click the Pass bar, as well.

Food Inspections Data Lens Limited to Risk 1 Establishments with a Result of Pass

So, how are those inspections distributed across Community Areas? One can sort of see but that card is pretty small. Click the double arrow in the corner of the Community Areas card …

Click the Double-Headed Arrow to Expand a Card

… and it expands to larger size.

Community Area Card Expanded to Larger Size

The best way to learn about Data Lens pages is to experiment with them, yourself. We have published some initial pages, including Crimes, Business Licenses, and Red Light Camera / Speed Camera Violations, and will continue to add more. As with other view types, Data Lens pages appear as a searchable category in the left pane of the Data Portal home page.

View Types Highlighting Data Lens

For up-to-date information on Data Lens pages, in general, please see Socrata’s Data Lens Support page. Socrata will add new features over time and we will implement those that seem useful for City of Chicago data. Both Socrata and the City of Chicago welcome feedback and suggestions. Each Data Lens page will have a feedback button in the lower right that sends comments to Socrata. As always, the best ways to reach us are @ChicagoCDO and

The Mayor has recently launched an online community forum called CHIdeas to engage Chicago’s residents and businesses in a discussion on how to improve services at City Hall, create programs and initiatives in our neighborhoods, and enhance quality of life across the city. CHIdeas provides a structured platform to seek ideas from the public and promote community dialogue around key issues such as raising the minimum wage, early learning programs, library services, and public art installations.


CHIdeas Logo


Visit to add your voice to the conversation

Online urban city guide Yelp has helped millions find and review businesses in their area, from discovering a local deli to cure a lunch break rut to trying out a new hair dresser with fingers crossed and eyes closed. And soon, Yelp will not only be in our back pockets, but in our backyard as well. Yelp Co-founder and CEO Jeremy Stoppelman and Mayor Rahm Emanuel have recently announced the expansion of Yelp to the Merchandise Mart, where it’s slated to open shop January 2015.

In addition to providing ratings and reviews, Yelp lets patrons “check-in” on the mobile app to their frequented restaurants to earn titles of Regular and Duke, even Baron of the Neighborhood – a lesser-known Yelp marketing technique. But beyond virtual royalty, Yelp is now offering Chicagoans something a little more substantial – jobs. They plan to hire 300 employees in the next 12 to 18 months in Chicago.

With offices in San Francisco, Scottsdale, New York, London, Hamburg, and Dublin, Yelp chose Chicago due to its pipeline of talented personnel from universities across the Midwest, along with its dedication to local business and a nationally-recognized technology community. The office in the Merchandise Mart attracted Yelp, as it sought a commuter-friendly location situated near Chicago’s thriving business district. Yelp is excited to play a part in Chicago’s rich history of innovation, which includes the Ferris Wheel, the skyscraper and brownies, to name a few.

“This vibrant metropolis is an ideal location to source talented new employees and connect with even more great local businesses. Chicago’s history as a leader in innovation and support of small business and tech industry growth fits seamlessly with Yelp’s initiatives, and we are excited to build a home here with Yelp’s seventh office,” said Jeremy Stoppelman, Co-founder and CEO of Yelp.

Since its 2004 launch in San Francisco, Yelp has connected millions of users to local businesses in 27 countries worldwide: US, Canada, UK, Ireland, France, Germany, Austria, The Netherlands, Spain, Italy, Switzerland, Belgium, Australia, Sweden, Denmark, Norway, Finland, Singapore, Poland, Turkey, New Zealand, the Czech Republic, Brazil, Portugal, Mexico, Japan and Argentina. And like that guy who always knows the newest, trendiest place in town, Yelp is popular. It boasted a monthly average of 68 million unique hits on mobile devices in the second quarter of 2014.

Yelp will be joining an already thriving tech community in the Merchandise Mart: technology hub 1871, Razorfish and MATTER, a start-up for next-generation health IT, medical device and biopharma companies. The City looks forward to welcoming Yelp to its skyline, in what is hoped to be a fruitful – and tasty – relationship.

From smart phones to digital cameras, from Facebook to Google, technology is a central part of our kids’ lives today. We’re happy to announce that Best Buy is now sponsoring Chicago City of Learning programs designed to build our students’ technology skills through fun, interactive workshops and activities.

“The Chicago City of Learning program continues to engage our kids, opening the doors to a variety of learning opportunities across the city,” said Mayor Rahm Emanuel. “The additional resources provided by Best Buy’s Geek Squad Academy will allow Chicago’s youth to explore a future in the science, technology, engineering, and math fields. This will help prepare our children with the skills and education they need for college and a successful career.”

anyone-can-code[1]The CCOL initiative offers thousands of after-school and summer programs, through partner organizations citywide, designed to challenge youth to engage in new ways of learning. Hands-on projects afford children and teens to work with everything from traditional craft materials to state-of-the-art technology. Through the sponsorship, participating CCOL outlets will afford Best Buy Geek Squad Academy to teach Chicago’s youth about the latest technology that will set them up for future career and college success. Geek Squad Academy will provide tech experts to engage kids with activities such as robotics, 3D printing and audio and video production.

More information on the Chicago City of Learning can be found at