Community Development 1 The State of Connecticut has recently launched an open data portal providing public access to open data.  The site is powered by Socrata, which in my experience provides a very solid platform for open data.  One of the data sets available at the newly launched site is a Community Development Portfolio from the Connecticut Department of Economic and Community Development.

Open Data Bits has added a page that provides a step-by-step process for integrating the Connecticut Community Development Portfolio into a business intelligence solution to our library.  Click here to visit that page.

The Community Development Portfolio data set contains grant and loan data that has been awarded from different sources.  The sources range from the Urban Act, to the General Fund, to Capitol Improvement.

For the data visualizations below, the methodology from the Open Data Bits documentation page was used to pull the data into Excel 2013 using Power Query.  Power Pivot was used to build the data model, and Power View is used for the visualizations.

At a high level there are slicers for Fiscal Year, Municipality, and Funding Source.  The top bar chart shows Loan Amount and Grant Amount totals over time.  The horizontal bar chart shows the maximum individual loan and grant allotments for each funding source.  The map shows Total Assistance by Zip Code, and the bottom grid chart shows the Applicant, Project Name, Total Assistance, and Total Assistance % (percentage of Total Project Cost) sorted to have the largest Total Assistance value at the top:

Community Development 1

Selecting the year 2009 from the slicer then filters all of the web parts for that year.  Notice that in 2009, the Urban Act and the Special Act contributed the two largest grants:

Community Development 2

Narrowing down the Funding Source filter to just the Urban Act and Special Act, a zip code in the area of Hartford has the largest Total Assistance on the map:

Community Development 3

Selecting Hartford from the Municipality slicer, and then drilling into the large bubble on the map yields the two addresses that received a total of $22.5M in funding:

Community Development 4

From a high level visualization containing all of the data within the Community Development portfolio, the business intelligence model can be used to drill down to individual loans and grants.

Once again, a step-by-step methodology for building this data model yourself can be found at this page.

Community Development Featured