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:
Documentation regarding the data can be obtained from the following link:
http://www.whitehouse.gov/sites/default/files/omb/budget/fy2012/assets/db_guide.pdf
Modeling the Data
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.
Leave a Reply