Have you ever browsed open data sets, and realized that open data is loaded with GIS data? When searching the web for new open data visualizations, I stumbled across Mapbox. According to their website, “Mapbox is an open source mapping platform for developers and designers at enterprise scale.”
The visualization below is from a Mapbox blog post by one of their data artists and software developers, Eric Fischer. According to the post, “In 29 hours 68 contributors mapped an entire city (20,105 buildings).” Also, it seems that the effort was to assist doctors fighting the recent Ebola outbreak in Africa. Using open data for a good cause provides the greatest value of all. Visit the blog post for more details by clicking here.
The Centers for Medicare and Medicaid Services (CMS) has recently released Medicare Provider Utilization and Payment Data at the detail level of Physicians and Other Suppliers. This release of open data has generated significant publicity and has become a hot topic of debate. A few articles about this CMS data release are linked at the bottom of this post. Would you like to have a look at this data for yourself with the ability to interactively filter it and build reports? Open Data Bits has now added documentation to our library that can help you do it from a personal computer.
First, you should note that this CMS data set contains CPT codes that are the intellectual property of the AMA. There is a licensing document available via CMS from this link which details the terms of the license that you need to be aware of when using this data.
This CMS data set contains detailed information about claims and reimbursement data at the provider level. Once you have integrated this data into a business intelligence solution you will be able to filter and analyze the data by attributes such as state, city, address, provider gender, provider type, HCPCS code, and more.
Articles related to CMS Medicare Provider Utilization and Payment Data
Once the City of Chicago crime data has been built into a business intelligence solution using the Open Data Bits methodology, a user can begin exploring a data set with over 5 million unique entries using a data visualization tool. The examples below are using Power View for Excel 2013 to display City of Chicago crime data from a Power Pivot solution.
At a high level, slicers on the right hand side of the Power View report can be used to filter crime locations on the map, a vertical bar chart of crimes by hour of the day, and a horizontal bar chart of crimes by year (segmented by arrest or no arrest). There are over 5 million crimes in the data set used for this example, so the map can only show a sample of those results:
Selecting “Homicide” from the [Primary Type] slicer filters all of the web parts for the data that falls within that grouping:
Selecting only the [Description] of “First Degree Murder” filters the web parts down even further:
Selecting only the year 2014, and drilling down to the month level on the horizontal bar chart, yields a much smaller set of data:
Seelcting the slicer value of “false” for [Arrest], along with clicking on the 4 am value for the vertical bar chart “Crime Count by Hour,” yields a single geographic point for which two incidents were recorded:
With a few clicks, the data visualization tool was able to take us from a report of over 5 million unique incidents to only two.
This article was originally published to a blog which has been decommissioned.
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.
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:
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:
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:
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:
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.
This article was originally published to a blog which has been decommissioned.
The Consumer Financial Protection Bureau of the United States educates consumers about financial options, enforces federal consumer financial laws, and conducts studies to “better understand consumers, financial services providers, and consumer financial markets.” Consumers can submit complaints to the Consumer Financial Protection Bureau about financial services and institutions, and the data from the complaint process is made available to the public as open data.
Following are a few examples of how the data within the Consumer Complaints Database can be visualized. At a high level, filtered for Year 2013, the count of complaints can be listed by Issue, graphed by Company and whether the customer disputed the Company response, and by a count of complaints over time:
Filtering for the Product “Credit reporting,” the Issue “Incorrect information on credit report,” and the State “NJ” yields the Sub-issues and graphs that are specific for those filter selections:
The same data model can be used to build a report in Power View for Excel 2013, which has a mapping tool to display the complaint data by State and Zip Code:
Filtering the chart for the State of Nevada and drilling into the bar chart gives a more detailed perspective on the “Consumer Loan” complaints in that State:
This article was originally published to a blog which has been decommissioned.
The United States Office of Management and Budget (OMB) has provided budgetary open data for public use that is accessible through WhiteHouse.gov and Data.gov. The data set covers 40 years of history going back to 1976. One of the great benefits of modern software is that anyone with a laptop and a basic business intelligence tool such as Excel can now access, model, and create interactive dashboard reports with this data. This article will feature the creation of a data model and dashboard report using open data from the OMB’s Public Budget Database.
Accessing the Data
The OMB’s Public Budget Database can be obtained as either an Excel file or .csv file at the following link:
The data is provided in the form of a normalized .csv flat file or .xls spreadsheet. For the examples in this article, the data was pulled into Excel 2013 using Power Query. Power Query was also used to “unpivot” columns for different years in order to transform the data into a format that is more suited to dimensional modeling and interactive reporting.
Date Dimension
The years specified for the data are US fiscal years, which differ from calendar years. Beginning in 1977, fiscal years are October 1 through September 30 of the next calendar year. So fiscal year 1980 would be 10/1/1979 through 9/30/1980. A derived column was added to the data model so that there is a key for the first day of each fiscal year.
Metrics
The primary metric for the data is named [Amt Total] in the reports built for this article. Another dynamic metric for year-over-year growth percentage ([YOY Growth %]) was added to the model, too.
Visualizing the Data
The OMB’s Public Budget Database open data was accessed, modeled, and visualized using Excel 2013 with Power Query and Power Pivot. The data can be displayed on an interactive dashboard using slicers, pivot tables, and pivot charts. The [US Fiscal Year] slicer is selected for member “2013” which filters all report parts except the historical trend chart:
Notice the “expand” buttons next to the [Agency] members on the pivot table? They can be opened to drill down within an Agency to a lower level of detail. The example below expands down to show values by [Bureau Name] and [Subfunction Title] within the [Agency] “005 – Department of Agriculture”:
Selecting only the [Agency Name] slicer for member “Social Security Administration” filters all of the parts of the report for tables and charts specific to data from that category:
Using the pivot table expand feature enables a user to determine the specific values for Account “800760 FDI, Federal Payments to the FDI Trust Fund,” which had the largest YOY Change %:
Finally, the Account “800760 FDI, Federal Payments to the FDI Trust Fund” can be selected in the slicer to give charts specific to data for Fiscal Year 2013, mapped to the Social Security Administration, and unique to that Account:
Once the data model exists in a tool such as Power Pivot, new interactive business intelligence reports such as these can be built in a matter of hours.
This article was originally published to a blog which has been decommissioned.
The open data sets previously featured at Open Data Bits were both from the city of San Francisco, and they both contained latitude and longitude coordinates.
Mashing Up the Data Sets
Both the San Francisco Graffiti Reports and SFPD Reported Incidents data are at the granularity of a single reported incident per row of data. Although the two data sets are different types of events, displaying a count of each event on a map can yield an interesting geographical data visualization. For the data mashup, Open Data Bits performed the equivalent of a SQL “Union All” statement using Power Query for Excel 2013. Various business intelligence tools will have different methods to union tables together, but the following columns from both tables can be used to reproduce the visualizations for this feature:
Date and Time dimensions can be used with the mashed-up data table in order to provide hierarchical rollups in the reports.
Visualizing the Data
There are numerous business intelligence tools that can be used to visualize the data mashup. For the example below, the solution was built in Power Pivot and is displayed using Power View for Excel 2013.
The map below contains slicers for [Week Year], [Date], [Category], and [Description]. The [Week Year] is selected for the fourth week of 2013. Any of these slicers can be used to dynamically change the data that is displayed on the map. The map contains blue circles representing a count of graffiti reports, and red circles for SFPD incidents. Notice the warning at the top of the map stating that there are too many data points to visualize on the map simultaneously:
Selecting the [Category] slicer members “DRUG/NARCOTIC” (from SFPD) and “Graffiti Private Property” (from Graffiti Reports) narrows down the events that are mapped for week 4 of 2013:
Selecting a few members of the [Descript] slicer (SFPD data) related to cocaine and hard drugs then filters the map to show what appears to be a tendency for both graffiti and SFPD incidents to happen in close proximity to one another:
Zooming in on the cluster in the northeast part of the city reveals more details about the geographic locations of both the SFPD and San Francisco graffiti events:
The city of San Francisco has numerous data sets available to the public, including data that details reports about graffiti in the city. This article will model, integrate, and visualize that data.
Accessing the Data
San Francisco’s graffiti data can be accessed at the following web page:
The page is in a user-friendly Socrata format containing discussions, links to other views, and different formats for accessing the data.
Modeling the Data
The San Francisco Graffiti data is sourced as a normalized table containing several attributes. The column [Opened] contains date and time information that can be derived into a date and time column to reference Date and Time dimensions.
Metrics
The only metric added to the Open Data Bits example solution is for [Graffiti Count], which represents a count of rows in the data. Theoretically, this should represent a count of graffiti reports.
Visualizing the Data
Once the San Francisco Graffiti Reports open data has been modeled, business intelligence tools can be used to visualize the data. For the example below, the solution was built in Power Pivot and is displayed using Power View for Excel 2013. Here’s a screenshot of the solution in Power Pivot:
Starting out filtered for 2013 and the [Neighborhood] values of “Mission,” “Western Addition,” and “Haight Ashbury” the San Francisco Graffiti open data can be visualized as shown below. Slicers for [Year], [Month Year], [Neighborhood] and [Supervisor District] filter the interactive charts. The upper right map shows reports by [Neighborhood], the pie chart shows reports by [Supervisor District], and the bar chart at the bottom visualizes reports by [Neighborhood] and month:
Selecting “May 2013” in the slicer and drilling into the bar chart shows the reports in May 2013 by day:
Selecting only the [Neighborhood] slicer for “Mission” reveals that most of the reports there map to [Supervisor District] member “9”:
Selecting only [Supervisor District] member “9” in the slicer, and drilling into the pie chart to show [Category] “Graffiti Private Property” shows that most of the reports fell along two streets on the map, including 24th Street:
The city of San Francisco has made a wealth of data available for public use via an open data website. One of the many robust and useful data sets is sourced from the San Francisco Police Department and contains details about reported incidents spanning back to 2003. This article will discuss modeling, integrating, and visualizing San Francisco Police Department Reported Incidents open data.
Accessing the Data
The SFPD Reported Incidents open data can be accessed from the following web page:
The page contains general information about the data, along with a link to download a zip file containing a separate .csv zip file for each year of data going back to 2003. For the screenshots in this article the zip file was downloaded manually, extracted, and integrated into Power Pivot for Excel 2013.
Modeling the Data
Each year of data is available in a normalized .csv table. The columns are the same for each year, so all of the tables could be united with a SQL “Union All” statement or the equivalent in another tool. There are a few useful attributes such as date, time, police district, and latitude/longitude.
Date and Time Dimensions
The SFPD Reported Incidents data contains columns for both [Date] and [Time]. The [Date] column can be used directly as a foreign key to reference the Date Dimension. The SFPD Reported Incidents [Time] column does not contain “seconds,” but with a slight transformation can be used as a foreign key to reference the Time Dimension.
Hierarchies
The only hierarchy that was added to the solution was for [Category] > [Description]. It appears that [Description] is a child member of [Category]:
Metrics
The only metric that was added to the solution was Incident Count, which is intended to represent a count of incidents based upon counting the rows in the source data.
Visualizing the Data
Power Pivot for Excel 2013 was used to create a data model for the SFPD Reported Incidents data. The visualizations were then created using Power View.
From a high level, data can be visualized for the specific [PdDistricts] Central, Mission, Northern, Southern, and Tenderloin. These members have been selected in the slicer in the upper left of the page. There are also interactive slicers for Week of the Year (week 3 of 2013 is selected), Date, and Hour of the Day. The top line graph shows incidents by hour of the day, by [PdDistrict]. The bar chart shows incidents by [Category], and the map plots a count of incidents at each [Location] as mapped using [Latitude] and [Longitude]:
Next, hovering over a line spike shows that there were 12 incidents that week in the [PdDistrict] “Southern” during the noon hour:
Selecting only “Southern” in the [PdDistrict] slicer reveals the days upon which these incidents occurred. Eight of them happened on 1/17/2013:
Selecting 1/17/2013 in the slicer, the locations and types of [Category] can be viewed for 1/17/2013:
Drilling into the bar chart for “Larceny/Theft,” and then clicking on “Grand Theft From Locked Auto” filters the map for the [Location] and count of incidents:
Selecting all members of the [PdDistrict] slicer then shows the [Description] “Grand Theft From Locked Auto” for all members of [PdDistrict]:
The 2014 Canadian Open Data Summit is taking place in Vancouver today, February 21. In the spirit of the convention, an open data set from the Canadian government will be featured in this article. Data.gc.ca has survey results available as open data for a 2012 HIV/AIDS Attitudinal Tracking Survey. When viewed on an interactive dashboard, this data set is very interesting to browse.
Accessing the Data
Canada’s 2012 HIV/AIDS Attitudinal Tracking Survey open data can be found at the following link:
The page contains information about the data set along with documentation about the data which is available in a .doc format at this link. A connection was set up to the .csv html link using the Excel 2013 add-in Power Query, and then added to a data model in Power Pivot.
Modeling the Data
Canada’s 2012 HIV/AIDS Attitudinal Tracking Survey open data is very simple to model. The granularity appears to be a survey response, and there is not a date or time field. Eventually Open Data Bits will add a conformed dimension for Canadian provinces, but for this example the provinces will be left as normalized columns on the survey results table.
Metrics
The following metrics were added to the featured solution by Open Data Bits:
Survey Count – A count of rows in the table represents a count of survey responses.
Diabetes Very or Somewhat Serious % – A percentage of respondents who considered diabetes to be a”Very Serious” or “Somewhat Serious” disease.
HIV-AIDS Very or Somewhat Serious % – A percentage of respondents who considered HIV/AIDS to be a”Very Serious” or “Somewhat Serious” disease.
Visualizing the Data
The data visualization examples below were built in Power View with a Power Pivot source from Excel 2013.
Starting at a high level of rolled up data the slicers for primary language, province, and age are on the left hand side of the report. Responses for the “Diabetes” severity question (q3cx) are on the top bar chart, and responses for the “HIV/AIDS” severity question (q3fx) are on the bottom bar chart. A map displaying respondent count by age group and province is on the top right. The bottom right scatter chart displays the percentage of respondents who consider diabetes a “very serious” or “somewhat serious” condition are on the y axis, and for HIV/AIDS on the x axis. Each bubble represents a province, and the size of the bubble represents the count of respondents:
Clicking the slicer for “25-34 years” filters all of the charts on the report. Notice that in that age group, of the 22 respondents from Nunavut 86% consider diabetes to be a “Very Serious” or “Somewhat Serious” disease while only 64% responded that HIV/AIDS was a “Very Serious” or “Somewhat Serious” disease:
Selecting the province slicer value for “Nunavut” yields the bar chart values for diabetes (top) and HIV/AIDS:
Finally, clearing the age filter and drilling into the bubble chart yields the differences in survey results among different age groups:
