Pull US Census Data Via The Bureau’s API (Part 3: Success!)

Craig Colangelo, April 10, 2023

This is the final part of a 3 part series on how to Pull U.S. Census Data Using Cognos Analytics via different methods. Check out parts 1 and 2 here:

• Part 1: Pull U.S. Census Data Using Cognos Analytics via the Bureau’s API
• Part 2: Manual Pull Boogaloo

Table of Contents

Alrighty…let’s finish this blog series off with a nice win! The Progress DataDirect Autonomous REST connector included with Cognos Analytics 11.2.2 has been improved by Progress and we can now effectively use it to do some straightforward querying of the U.S. Census Bureau’s API. The fixed Progress REST connector went in somewhere between CA 11.2 base and CA 11.2.2 (which represents six dot and interim fix releases over that span).

In our original test case, we attempted to use the U.S. Census ACS (American Community Survey) data to get at estimates around the percentage of people 24 years old and older who hold 4-year engineering or science degrees, at the state level. We wanted to use the easy button here, a.k.a. the ‘sample’ method, and allow the autonomous REST connector to do its thing autonomously, inferring and determining schema from the API itself, all on its own. Results will generally vary using the sample method depending on lots of factors, including the complexity of each API/schema/number of endpoints/data set returned/etc. So, with the easy button, we created a simple data source connection (using Cognos Analytics 11.2.2) utilizing the Progress DataDirect Autonomous REST connector and the sample method.

Every API is different, but for this one in particular, if we were querying more than 50x a day, we’d need to include an API key and token values we received from the U.S. Census Bureau when we signed up for the API. You can do this using the sample method by utilizing the ‘key=’ and ‘token=’ prefixes. See the CA documentation for the Progress Data Direct Autonomous REST connection for more info. The AUTOREST schema is really all you need to load in order to get at the data from your API call.

Because we didn’t do anything more elaborate (like the config method), we get the following back. Using a simple data module, notice the first row (Position = 0)? Notice the metadata (table name, column names, etc)? Could use a little cleaning up if we’re going to take this easy approach while still making the results available to our data consumers. Also worth noting, is if you wanted to use multiple endpoints (multiple census data API calls to different surveys/data sets), you’d need to use the config parameter approach previously mentioned.

To clean up the data module a bit, I’d simply rename columns and table names, hide the Get and For columns from users, change usage a bit for some of the data items, and maybe add a table filter to exclude that first row (Ordinal Position = 0) which contains the unfortunate column headers (it’s how the sample method, the way we employed it, worked for this somewhat unconventional API…with other API’s, you’d get different, likely better results, right out of the box).

Using this simple API call, with this simple data module, and a simple dashboard, we can quickly visualize our ask of the Census Bureau API.

At PMsquare, we use the Progress DataDirect Autonomous REST connector, available in Cognos Analytics version 11.1 and greater, to reach lots of different data within our internal apps via API. We grab from internal systems like our project management applications, time tracking application, expense management application, and more. I’m betting you have plenty of internal use cases as well. Not to mention, external use cases, where this method also shines (think weather, demographics, news, travel/transportation, etc). 

Considering how easy it is, why not give it a shot? Skip the whole ETL portion of an analytics ask (if possible) and get right to the data. You can even do some data cleansing in the data module, if necessary. This API connector is yet another tool IBM makes available to Cognos to enable your team to get to data quicker and with less hassle. 

PMsquare has helped customers successfully utilize data from many different sources using various methods. For help pulling API data into your Cognos Analytics implementation, contact one of our solutions architects by filling out our form!