PowerPivot is a versatile tool that can be used for many purposes. Mash-up data from different sources, rapidly create BI dashboards, or use PowerPivot as a data modeling tool for development of an Enterprise Data Warehouse. This post will focus on another use that can be useful for PowerPivot, a tool for optimizing the browsing of large data documentation lists. Such a use can be helpful for subject matter experts, project managers, technical writers, and anyone else who has long lists that need to be efficiently browsed.
For this example, I’ll use a Data.gov download for the CMS Medicare and Medicaid EHR Incentive Program electronic health record products used for attestation. You can download it at this link. According to the site the official description of the data is that it “enables systematic analysis of the distribution of certified EHR vendors and products among those providers that have attested to meaningful use within the CMS EHR Incentive Programs.” The challenge is that the Excel spreadsheet has over 77,000 rows. How do you effectively browse such a large set of data? One option is PowerPivot:
The raw data from Data.gov is well-organized and thorough, but can be difficult to sort through and make use of with the default format for over 77,000 rows in Excel:
The single sheet of data for “MU_Attestations” is first pulled into PowerPivot. For a detailed walk-through of how to pull data into PowerPivot, click here. For basic browsing of the data, a single simple calculation can be added to the calculations bar in PowerPivot using the DAX code Count:=COUNTROWS(‘MU Attestations’).
To facilitate expand/collapse of Vendor products, I created a hierarchy in the diagram view of PowerPivot named Vendor Product (parent > children) as “Vendor Product > EHR Product Name > EHR Product Version“:
Finally, you can deploy the PowerPivot solution to Excel as a PivotTable. Add slicers, filters, and pull in the hierarchy to view the CMS data in a format that can be easily browsed and navigated:
While the underlying model is still a BI solution, the use of it in this example is different from traditional OLAP solutions. Columns from a single denormalized table can be used as filters, slicers, columns or rows. Even a table of over 77,000 rows can be simple to browse and navigate with PowerPivot.