10 of 11Image
Google describes BigQuery as a column store database that works well for OLAP (OnLine Analytical Processing) queries. Microsoft PowerPivot, a free add-in for Excel 2010, precisely fits that description as well and can import from CSV files just fine. Here’s what the data looks like once it’s in PowerPivot. Throwing large datasets in PowerPivot will give you far better performance than storing the same data directly in a spreadsheet.
One very powerful aspect of PowerPivot is its use of data compression. The CSV file containing the six states’ baby data was over 20MB. The Excel workbook containing the PowerPivot model with that same data was less than 1MB.
Once your data is in PowerPivot, you can easily visualize it back in Excel using PivotTables, charts or both. Here we see a chart bound to the PowerPivot model with our baby name data, featuring several “slicers” above and to the left of the chart.
Slicers let you filter your data and see the chart update automatically. Here we have filtered the query to show data only for the names Abigail and Allison, in the states of AZ, CA, CO and NY, between 1995 and 2005. The slicer at the top contains only girls’ names because we selected “F” (female) in the "Gender" slicer toward the upper-left.
Here’s the same result in BigQuery. There’s no visualization of the data and rather than just clicking slicers, the WHERE clause in the query had to be written carefully to get the same result. Query times were comparable with PowerPivot's.
Going the PowerPivot route offers a lot of advantages and will likely make more sense for many users. But BigQuery is cloud-based and can be accessed from any Internet-connected computer or device.