Warning "Not accelerated by BigQuery BI Engine"

Question

I'm using GCP BigQuery with Google Data Studio. I did setup the BI engine reservation in the same region as the dataset (EU, multiple locations). I still get in Data Studio this warning: "Not accelerated by BigQuery BI Engine"

A [Minimal Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) is required (a specific SQL query that reproduces the issue and optionally a sample Google Data Studio Report with the query in the Data Source). Without specifics this question may likely lead to more generic / broad answers as it is not clear exactly which functions to avoid and what are the alternatives for the respective functions... — Nimantha, Feb 09 '22 at 09:08

score 2 · Answer 1 · answered Feb 05 '22 at 19:15

BI Engine accelerates only some types of queries. See some limitations documented here.

Go to your BigQuery console, locate the project history, and the Jobs history, filter to Querys only.

Examine each query that is coming from DataStudio, and if you open the pane, it will have details why it's not accelerating it.

You will see samples like this:

If you are more developer friendly you can use the BQ cli command tool

To get the most recent jobs:

bq  ls -j -a --max_results=15

To fetch the statistics associated with BI Engine accelerated queries, run the following bq command-line tool command:

bq show --format=prettyjson -j job_id

and it will have a section such as:

"statistics": {
    "creationTime": "1602175128902",
    "endTime": "1602175130700",
    "query": {
      "biEngineStatistics": {
        "biEngineMode": "DISABLED",
        "biEngineReasons": [
          {
            "code": "UNSUPPORTED_SQL_TEXT",
            "message": "Detected unsupported join type"
          }
        ]
      },

Note that currently queries that are coming from DataStudio may not generate BigQuery job when handled by BI Engine. When that happens, BI Engine serves query completely from RAM, and BigQuery APIs are not used. We are going to improve that later this year, so that every query that is generate by BI Engine produces statistics to analyze. Also, BI Engine statistics is now visible in [information schema](https://cloud.google.com/bigquery/docs/bi-engine-sql-interface-overview#acceleration_statistics_in_information_schema) — Alexey Klishin, Mar 18 '22 at 20:18

Warning "Not accelerated by BigQuery BI Engine"

1 Answers1