What is BI Engine?

BigQuery BI Engine is a fast, in-memory analysis service that accelerates manySQL queries in BigQuery by intelligently caching the data you use mostfrequently. BI Engine can accelerate SQL queries from any source,including those written by data visualization tools, and can manage cachedtables for ongoing optimization. This lets you improve query performancewithout manual tuning or data tiering. You can useclusteringandpartitioning to further optimize theperformance of large tables with BI Engine.

For example, if your dashboard only displays the last quarter's data, thenconsider partitioning your tables by time so only the latest partitions areloaded into memory. You can also combine the benefits ofmaterializedviews and BI Engine.This works particularly well when the materialized views are used to join andflatten data to optimize their structure for BI Engine.

BI Engine provides the following advantages:

1. BigQuery API: BI Engine directly integrates with theBigQuery API. Any BI solution or custom application that workswith the BigQuery API through standard mechanisms such asRESTorJDBC and ODBC driverscan use BI Engine without modification.
2. Vectorized runtime: With the BI Engine,BigQuery uses a modern technique calledvectorized processing. Using vectorized processing in an execution enginemakes more efficient use of modern CPU architecture, by operating on batchesof data at a time. BI Engine also uses advanced dataencodings, specifically, dictionary and run-length encoding, to furthercompress the data that's stored in the in-memory layer.
3. Seamless integration: BI Engine works withBigQuery features and metadata, including authorized views,column and row level security, and data masking.
4. Reservations: BI Engine reservations manage memoryallocation at the project location level. BI Enginecaches specific columns or partitions that are queried, prioritizing thosein tables marked as preferred.

BI Engine architecture

BI Engine integrate with any business intelligence (BI) tool,including such as Looker, Tableau, Power BI, and custom applications toaccelerate data exploration and analysis.

BI Engine use cases

BI Engine can significantly accelerate many SQL queries, includingthose used for BI dashboards. Acceleration is most effective if you identifythe tables that are essential to your queries and then mark them aspreferredtables. To useBI Engine, create a reservation that defines the storagecapacity dedicated to BI Engine. You can letBigQuery determine which tables to cache based on the project'susage patterns or you can mark specific tables to prevent other trafficfrom interfering with acceleration.

BI Engine is useful in the following use cases:

• You use BI tools to analyze your data: The BI Engine canaccelerate BigQuery queries regardless of whether they run inthe BigQuery console, client library, or through an API or anODBC or JDBC connector. This can significantly improve the performance ofdashboards connected to BigQuery through a built-in connection(API) or connectors.
• You have certain tables that are queried most frequently:BI Engine lets you designate specific preferred tables toaccelerate. This is helpful if you have a subset of tables that are queriedmore frequently or are used for high-visibility dashboards.

BI Engine might not fit your needs in the following cases:

• You use wildcards in your queries: Queries referencing wildcard tables arenot supported by BI Engine and don't benefit from acceleration.

• You rely heavily on unsupported BigQuery features: WhileBI Engine supports mostSQL functions andoperators when connecting businessintelligence (BI) tools to BigQuery, there areunsupportedfeatures,including external tables and non-SQL user-defined functions.

Considerations for BI Engine

Consider the following when deciding how to configure BI Engine:

Ensure acceleration for specific queries

You can ensure a particular set of queries always gets accelerated by creating aseparate project with a BI Engine reservation. To do so, youshould ensure that the BI Engine reservation in that project islarge enough to match the size of all tables used in those queries anddesignate those tables aspreferredtables for BI Engine.Only those queries that need to be accelerated should be run in that project.

Minimize your joins

BI Engine works best with pre-joined or pre-aggregated, and withdata in a small number of joins. This isparticularly true when one side of the join is large and the others are muchsmaller such as when you query a large fact table joined with a small dimensiontable. You can combine BI Engine withmaterializedviews that which perform joinsto produce a single large, flat table. In this way, the same joins don't haveto be performed on every query.

Understand the impact of BI Engine

You can better understand how your workloads benefit fromBI Engine byreviewing the usage statistics inCloud Monitoringor querying theINFORMATION_SCHEMAin BigQuery. Be sure to disable theUse cached resultsoption in BigQuery to get the most accurate comparison. For moreinformation, seeUse cached query results.

Limitations

Queries that contain theVECTOR_SEARCH functionaren't accelerated byBigQuery BI Engine.

Quotas and limits

SeeBigQuery quotas and limitsfor quotas and limits that apply to BI Engine.

Pricing

For information on BI Engine pricing, see theBigQuery Pricing page.

Query optimization and acceleration

Note: For BI Engine maximum reservation size,seequotas and limits.

BigQuery, and by extension BI Engine, breaks downthe query plan that's produced for a SQL query into subqueries. A subquerycontains a number of operations, such as scanning, filtering, or aggregatingdata, and is often the unit of execution on a shard.

While all of BigQuery's supported SQL queries are correctlyexecuted by the BI Engine, only certain subqueries are optimized. Inparticular, BI Engine is most optimized for leaf-level subqueriesthat scan the data from storage, and perform operations such as filter, compute,aggregation, order-by, and certain types of joins. Other subqueries that are notyet fully accelerated by BI Engine revert back toBigQuery for execution.

Because of this selective optimization, simpler business intelligence ordashboard-type queries benefit the most from BI Engine (resultingin fewer subqueries) because the majority of the execution time is spent onleaf-level subqueries that process raw data.

What's next

• Learn aboutBI Engine optimized functions.
• To learn how to create your BI Engine reservation, seeReserveBI Engine capacity.
• For information designating preferred tables, seeBI Enginepreferred tables.
• To understand your utilization of BI Engine, seeMonitor BI Engine with Cloud Monitoring.