I blog previously how to create a Materialized View in BigQuery, and Obviously I talk a lot about BI Engine, but do they work together ?
To test it, I built a simple report in PowerBI using live mode ( you can use your favorite BI tool), 1 fact table with 78 Millions records at the minute granularity and a small dimension Table
In every Selection PowerBI send a SQL Query which scan 1.79 GB
Then I created a Materialized View , notice I skipped the time dimension
SELECT xx.DUID, yy.StationName, yy.Region, yy.FuelSourceDescriptor, yy.Technology, yy.latitude, yy.longitude, yy.Tech, DAY, SUM(Mwh) AS Mwh FROM `xxxxx.UNITARCHIVE` xx INNER JOIN xxxxx.DUID_DIM yy ON xx.DUID=YY.DUID GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
The Same Query will scan only 10 MB, and take 700 ms
Now let’s add 1 GB of BI Engine Reservation, the duration was reduced from 700 ms to 400 ms
But the nice surprise is this !!! The Memory used is only 2.78 MB
BI Engine was smart enough to load only the Materialized View instead of the Base Table, That’s a better usage of resources and will scale better.
Obviously if you select another column not available in the Materialized View ( like time), BI Engine will load the column Required automatically from the base Table.
2 thoughts on “Optimize BigQuery BI Engine Memory usage by Using Materialized Views”