How to Connect Excel and Google BigQuery

Despite the existence of a plethora of data analytics tools, Microsoft Excel is still the preferred choice for many analysts working with data. With the continued growth of data, as well as migration to the cloud, accessing the data necessary for analysis has become progressively more complex. Enterprises increasingly need to navigate hybrid cloud environments consisting of online and offline data platforms with speed to identify insights in time to respond effectively to key business questions.

The emergence of cloud-based data warehouses, such as Google BigQuery, aim to create an affordable solution for accessing and storing business-critical data, but alone fail to deliver the breadth, depth, and flexibility needed to utilize the full potential of Excel data analytics at a reasonable cost. GBQ does not natively support the MDX query language, nor does it support data virtualization, which are both essential for effective data mining and analysis in Excel.

Google BigQuery is a cutting edge, powerful cloud data warehouse that allow enterprises moving from an on-premise to save money and improve the ROI of their operational analytics. With that said, the lack of a live connection between Google BigQuery and Excel adds an additional layer of potential business disruption when adopting Google Cloud. Enterprises evaluating BigQuery that have large analyst constituencies using Excel must moving those analysts to a BI tool that speaks SQL. This type of replatforming can be costly and very interruptive, as analysts would need to be retrained and new processes would need to be instituted to accommodate the new BI tool.

Fortunately, enterprises don’t have to make this difficult decision. Companies with large excel user bases can realize the fantastic benefits of BigQuery, while keeping their analysts on Excel. The answer is AtScale’s Adaptive Analytics Fabric.

How AtScale Connects Excel and Google BigQuery

AtScale’s Adaptive Analytics Fabric allows enterprises to realize the cost efficiencies of GBQ while simultaneously utilizing one cohesive view of business-critical data in Excel. AtScale’s virtualization capability allows multiple data sources, both in the cloud and on-premise, to be presented as a single data view to Excel and other business intelligence tools, greatly enhancing users’ data mining power. This is especially impactful during the data offload from an on-premise warehouse to Google BigQuery.

AtScale is the only solution that provides real-time MDX querying capability to GBQ, allowing Excel users to leverage its powerful data capabilities with greater speed and reduced costs. This is done through AtScale’s Universal Semantic Layer, which translates MDX queries from Excel into GBQ-specific SQL instantly. The use of machine learning and artificial intelligence within the AtScale platform further empowers users by allowing rapid querying and multidimensional analysis in Excel on data stored in Google BigQuery. AtScale’s intelligent aggregates enable Excel users to produce queries that do not scan raw data in GBQ, leading to vastly accelerated time to insight and improved return on their organization’s investment in BigQuery.

Example of AtScale Connecting Excel and Google BigQuery at a Fortune 100 Retailer

A global home improvement retailer needed to modernize its 1990s Microsoft SSAS technology in use by 4,000 stores. The old SSAS application was limiting because it could only store a single month of data for a single store. Data updates took 4 days and users were unable to do important regional comparisons across multi-year time frames. Also, users were comfortable with Microsoft Excel, and IT did not want to introduce a new BI tool, so any new solution would need to work well with Excel.

Initially, the company utilized AtScale to move to Hadoop and provide users with the ability to query against a data lake. While the move was seamless, Hadoop proved more challenging to manage and expensive for their needs, so they opted to move to the cloud with GBQ. Again relying on AtScale for a seamless transition. At this point, the retailer also wanted the new environment to provide its extensive supplier network with SKU level data access to empower them to effectively manage inventory.

AtScale empowered the retailer by providing fast, easy access to 2.5 years of data, refreshed every 20 minutes through its platform in conjunction with Excel and GBQ. Store managers could now not only see their store, but could also benchmark against other stores within and across regions through AtScale’s virtual data warehouse. Users retained the ability to use Excel and avoided costly re-training efforts.

The migration from Hadoop to GBQ was completed over a weekend without any disruption to the business users. On Monday morning users were able to query the same reports without noticing any difference other than faster response times. Additionally, suppliers received access to a new application built on GBQ to manage inventory through an Excel interface. AtScale delivered data at the SKU level reaching petabytes in size with speed and ease. AtScale’s security policies assured that suppliers could only see their own SKUs, protecting sensitive data across accounts.

Conclusion: Make Excel Work on Google BigQuery

Despite the presence of newer and flashier BI tools, Microsoft Excel will remain a corporate standard for analytics teams for the foreseeable future. Organizations moving analytical workloads to the cloud would be remiss to not consider how Excel works with their cloud data platform of choice. For companies looking to take advantage of the many benefits of Google BigQuery, AtScale’s Adaptive Analytics Fabric is essential, as it is the only tool that provides BI users with a live view in Excel of data hosted on BigQuery.