What is BigQuery BI Engine?
BigQuery BI Engine is a fast, in-memory analysis service. By using BI Engine you can analyze data stored in BigQuery with sub-second query response time while providing compute cost savings.
BigQuery BI Engine improves your dashboards in two ways:
BI Engine SQL interface is a distributed, in-memory engine, that accelerates any BigQuery query, regardless of the API being used. Any tool that works with BigQuery interfaces like BigQuery SQL, API and client libraries, or JDBC drivers will work with BI Engine SQL interface as well. BI Engine SQL interface provides additional statistics and monitoring by scaling to large data sizes using distributed execution and partial acceleration.
Google Data Studio acceleration with which BI Engine delivers interactive performance.
With BI Engine, you can build rich, interactive dashboards and reports without compromising performance, scale, security, or data freshness.
Advantages of BI Engine
BI Engine has the following advantages:
- Fast: Match performance to the speed of business by reducing time to insights
- Today, it's difficult to run reports fast enough to steer your business in a data-driven way by using operational, prescriptive business intelligence. Teams also struggle with sluggish dashboards and stale data. BI Engine provides sub-second query response time with minimal load times and intelligent caching for data stored in BigQuery. By integrating BI Engine with BigQuery streaming, you can perform real-time data analysis over streaming data without sacrificing write speeds or data freshness.
- Simplified architecture: Get started quickly without managing complex data pipeline or servers
- Traditional BI systems require users to move data from data warehousing platforms to data marts or BI platforms to support fast, interactive analysis. This typically requires complex ETL pipelines for data movement. The time required by these ETL jobs can delay your reporting and compromise the freshness of data for critical decision support systems. BI Engine performs in-place analysis within BigQuery. This eliminates the need to move data or to create complex data transformation pipelines.
- Ease of use: Seamless experience in BigQuery
- BI Engine uses the same interface as BigQuery. So regardless of the BI tool you are using, you can leverage the benefits of BI Engine SQL interface without any changes to the way they interact with BigQuery. This means that if a query is not eligible for acceleration with BI Engine, it doesn't fail and runs as a regular query.
- Smart tuning: Very few configuration settings
- BI Engine's self-tuning design automatically tunes queries by moving data between BI Engine's in-memory storage, the BigQuery query cache, and BigQuery storage to ensure optimal performance and load times for dashboards. Your BigQuery administrator can easily add and remove BI Engine memory capacity by using the Cloud console.
Best practices for BI Engine
When implementing BI Engine, consider the following best practices:
- Isolation - To ensure a particular set of queries always gets accelerated, create a separate billing project with BI reservation and ensure that the reservation capacity is large enough for the project.
- Query complexity - Performance is best for pre-aggregated data with filtering and computation on top. BI Engine also works well with a small number of joins (inner, left outer). This is particularly true when one side of the join is large and the others are much smaller such as when you query a large fact table joined with a small dimension table.
- Query optimization - Use partitioning and clustering to optimize the performance of large tables. For example, if your dashboard shows only the last quarter's data, then consider partitining by time so only the latest partitions are loaded into memory.
- Materialized views - Materialized views in BigQuery perform precomputation, thereby reducing query time. You should create materialized views to improve performance and to reduce processed data by using aggregations, filters, inner joins, and unnests.
- Performance comparison - You can view BI Engine logs and diagnostics to determine whether a query was accelerated fully or partially using BI Engine. Run the same queries on a project with BI Engine and another without BI Engine. To get more stable results, run the queries multiple times.
Quotas and limitations
See BigQuery quotas and limits for quotas and limits that apply to BI Engine.
For a list of limitations to BI Engine query acceleration, including BigQuery API and other BI tools, see BigQuery BI Engine SQL interface overview.
For a full list of optimized functions and operators in custom queries and views, see Optimized SQL functions and operators.
BI Engine is supported in the same regions as BigQuery. See the Locations page for a complete list of supported regions and multi-regions.
For information on BI Engine pricing, see the BI Engine Pricing page.
- Get started with BigQuery sandbox, risk-free and at no cost.
Learn how to use BI Engine with the following:
Learn about using BI Engine with other business intelligence tools.
Learn how to reserve capacity for BI Engine.