See KB484010 for the latest (as of Strategy 2020) whitepaper on the technical considerations of Strategy and Google BigQuery.
Goals of this document
The goal of the present document is to provide guidance on how to best use Strategy and BigQuery. It is the result of a thorough evaluation of both technologies, best practices and customer use cases. The intent is to be as comprehensive as possible. If you ever face a situation or use case that should be covered in this document, please let us know and we will enhance it.
What is Google BigQuery?
BigQuery is Google's fully managed, petabyte scale, low cost enterprise data warehouse for analytics. It is broadly used in Web Analytics, Social Media analytics and Marketing Analytics.
It is a cloud-based serverless MPP Datawarehouse service that can store and compute large amounts of data, hosted on Google Cloud Platform (GCP).
As we are seeing an increasing adoption of BQ in the market. This document aims to demystify BQ principles and identify strengths, best practices, limitations and workarounds to get the best of Strategy and BigQuery.
Who is Strategy?
Strategy provide powerful software solutions and expert services that empower every individual with actionable intelligence, helping enterprises unleash the full potential of their people and investments. Our analytics and mobility platform deliver high-performance business applications that meet the needs of both business and IT.
Our platform enhances the power of BigQuery by allowing users to build Enterprise reports and dashboards as well as combining information from multiple data sources. Strategy supports all of Google BigQuery’s data types, as well as user-defined functions (UDFs) which allow users to extend the SQL language by writing their own custom functions, partitions which optimizes performance and cost, as well as nested structures which optimize query performance and storage. Strategy also improves query performance by pushing down a variety of analytical functions to the database level and reducing the number of SQL passes required for a given query.
Mindset – Working with BQ
BigQuery is the result of years of technology innovations in the world of Big Data: Map Reduce, Dremel, Colossus, etc. It brings de facto new capabilities that were not possible or hard to implement with other Warehousing systems. The mindset when working with BQ is: forget what was not possible with other technologies and see if it is now with BQ
When working with MPP technologies, customers tend to try to join Petabyte tables together. This is not the best way to go. BQ provides another way of achieving similar results by storing table structures inside database columns, called Nested and Repeated Fields thus eliminating the need for joins. Consider Nested fields a way to store dimensions related information into the fact table, and Repeated fields a way to store a fact table into another fact table. Working with this mindset will be addressed in this document.
If you are concerned about BQ billing, you are all covered with this document. BQ charges by the amount of data read to process each query. Fortunately, many best practices are detailed in BQ documentation to optimize this. Features such as Partition / Clustered Tables, Nesting and Quota management provide means to ensures queries are computed with minimal cost. We will cover specific BQ quota features and Strategy features / capabilities to cope with these best practices. Note that you can use BQ for free up to 1TB of data processed per month.
KB442358
