Excel with Google BigQuery
I hear it all the time. Many enterprises hate Excel because it’s an analytics “free for all” but the business users love it and can’t live without it. No matter what BI tools an enterprise may have, ultimately, that last mile of analytics is probably coming from Excel. What most people don’t know is that Excel is by far the most popular BI tool in the world, dwarfing the market share of tools like Tableau and Power BI with over 750M users worldwide. In this post, we’ll explore why Excel and the spreadsheet metaphor is still so important to business users and how it can work in a modern analytics stack safely and at scale.
Modern OLAP on BigQuery
Google BigQuery is an awesome data platform for Big Data. It can troll through billions of rows of data very quickly. However, like any other cloud data warehouse, BigQuery is not optimized for OLAP style queries that deliver results in under one second. This “speed of thought” type of query is a key benefit of OLAP but difficult if not impossible to achieve with SQL. On top of that, OLAP delivers the benefits of a universal semantic layer that makes BigQuery data consumable by business users, not just SQL developers. Finally, the power of OLAP’s MDX language goes way beyond SQL’s ability to express business constructs like time intelligence and semi-additive metrics.
With AtScale, you get the benefits of OLAP with the modern architecture and scale of Google BigQuery:
- Fast, interactive queries under one second
- A universal semantic layer built for business users
- The OLAP based MDX language to express business logic
See how you can define OLAP models with AtScale Design Center and query them instantly with Tableau (11 minute video):
No More Data Copies. “Live” Excel on BigQuery
Corporate IT tends to frown upon Excel usage as a BI tool because often users will copy data from other BI tools to do additional analysis or formatting. That copy of data that sits on an end user’s machine is a gigantic security risk. Even worse, once data is copied into Excel it tends to drift from the carefully curated source of truth which may introduce errors and inconsistencies. Wouldn’t it be great to use Excel as a BI tool the same way you use Tableau or Power BI with a direct query against BigQuery? With AtScale, you can do just that. No more data copies or security risks because Excel becomes a visualization layer, not a database to rival BigQuery.
See how AtScale allows Excel to query live data in BigQuery and drill down to atomic data (5 minute video):
Spreadsheet Power, BigQuery Scale
Why does Excel remain such a stalwart in the corporate world? It’s all about the flexibility and power of a spreadsheet. With the ability to create cell based calculations, the options are limitless. For creating forecasting and financial models, a spreadsheet is essential since you can mix input cells with cells that point to actual data. With traditional BI tools that are more matrix or row based, this level of flexibility is just not there.
With AtScale, you can leverage the power of a spreadsheet with the power and scale of BigQuery. Using Excel Pivot Tables and the CUBEVALUE and CUBEMEMBER functions, you can mix data from BigQuery with your own data in Excel at the cell level. This unprecedented level of control is made possible by AtScale’s support of MDX and it’s API level compatibility with the XMLA protocol. This combination means you can mix cells of data from a table in BigQuery with your own calculations to create rich models and visualizations without the backflips required by traditional BI tools.
See how AtScale can deliver cell level data access to BigQuery using Excel’s CUBEVALUE and CUBEMEMBER functions (5 minute video):
As you can see, modernizing your analytics infrastructure doesn’t mean you have to lose the OLAP functionality that your users want and need. With AtScale, you can ditch the antiquated OLAP architecture but keep its power, ease of use and flexibility. Coupled with Google BigQuery, AtScale can transform Excel into the world’s most ubiquitous and powerful business intelligence tool with the scale of BigQuery and safety of a universal semantic layer.
See more about how AtScale can save you money and boost the performance of Google BigQuery by downloading our BigQuery Benchmark Report here.