Bi Engine with BigQuery Materialized View not working

[finderco-engr]

We have a BigQuery Materialized View with:

• allow_non_incremental_definition set to TRUE
• 2 CTEs
• a window function
• a few left joins
• the MV and all base tables are added a preferred tables in a BI Engine reservation

BI Engine docs clearly state materialized views are supported and we're in line with the limitations.  While the docs are a little inconsistent around join support (MV docs say left joins aren't supported, but this Google blog says they are) this MV is allowed to be created with non-incremental option. I've waited at least an hour for any BI Engine config changes to settle.

However, when adding the MV as a Looker Studio data source and then exploring or using in a Looker Report, the BI Engine is not working for this materialized view.

Not Accelerated by BigQuery BI Engine

This data source connects to a BigQuery view. Looker Studio BI engine doesn’t currently support materialized views.

What do we need to do to get more information or get this working?

[nceniza]

Hi, It appears that Looker Studio BI engine do not support MV at the moment as the error mentions, Apologies there might be a confusion for the documentation provided. I would recommend checking the release notes page of Looker studio for this, also you file a feature request for Looker studio to accept MV here[1]. Alternatively you can contact Google support for further investigation of your project's resources that can possibly help you for your case.[2]

[1]https://support.google.com/looker-studio/answer/11521624?hl=en#may-18-2023 

[2]https://cloud.google.com/contact

[Roderick]

Agreed @nceniza,

@finderco-engr, Looker Studio BI Engine does not currently support Materialized Views. However, there are a few things that you can do to get around this limitation.

• Try using the BigQuery Query Editor to run your queries against the Materialized View. This will bypass Looker Studio BI Engine and use BigQuery's native query engine to run the query.
• Try using the Looker API to run your queries against the Materialized View. This will also bypass Looker Studio BI Engine and use BigQuery's native query engine to run the query.

This is an interesting use case that the team may be able to help with. Try out the above options and if you continue to run into issues, please contact Looker support and ask them if there is a workaround for this limitation.

I hope this helps! Let me know if you have any other questions.

[finderco-engr]

@Roderick @nceniza Do you know for a fact from other source that BI Engine does not support Materialized Views, or are you basing that off the error message?

The BI Engine docs explicitly state the use of Materialized views, recommending them as a best practice:

You can also combine the benefits of materialized views and BI Engine. This works particularly well when the materialized views are used to join and flatten data to optimize their structure for BI Engine.

You can combine BI Engine with materialized views that which perform joins to produce a single large, flat table. In this way, the same joins don't have to be performed on every query.

@Roderick  I appreciate your recommendations, but they don't help my situation. My queries work fine from Looker Studio, but are not accelerated by BI Engine, which is what I'm trying to achieve. 

If BI Engine truly doesn't support Materialized Views, the only option I see is a scheduled query that writes to a physical table, then basing my Looker Studio queries on that table, which presumably BI Engine would support.

I'm surprised to see what the BI Engine docs explicitly recommend described as an "interesting use case". These forums appear to be my only support option, even though I'm paying for BI Engine reserved capacity. I would certainly appreciate someone from the BI Engine team responding to this issue, as this is pretty surprising and disappointing.

[sarsasid]

Same issue here. BIEngine does not support Materialized Views even run through BigQuery console. First of all for BIEngine for MV to work, all the source tables on MV needs to be added to BIEngine. Secondly even if we add and if our MV contains some rank function, it wont work. Why can't BIEngine consider non-incremental MVs as tables and does not really care about the source of MVs?

I believe this is a  bug with Non-Incremental MVs with BIEngine, as it should be considered as pure tables and does not look into source, as the tables does not really matter

[dluijk]

Same issue here, I would really like to use Material Views for Looker Studio, but it doesn't enable BI Engine which makes it very very slow.