Preparing Data for Visualization and BI

What it is

Dashboards need answers in seconds, from data that each viewer is allowed to see. Preparing data for visualization means connecting the right tool, precalculating expensive work, using BigQuery's BI acceleration features, fixing slow queries and applying security and masking before the first chart is drawn.

How it works

Connecting to tools

In Looker Studio, a data source uses either the owner's credentials (viewers see what the owner can see) or the viewer's credentials (each viewer's own BigQuery permissions apply). Row-level security only filters per viewer when the viewer's credentials are used.

Precalculating fields

Calculate once, read many times.

BigQuery features for BI

Materialized views store a query's result and refresh automatically when base tables change. BigQuery can rewrite queries against the base table to read the view instead, which is called smart tuning.

CREATE MATERIALIZED VIEW sales.daily_revenue_mv
OPTIONS (enable_refresh = TRUE, refresh_interval_minutes = 60) AS
SELECT DATE(order_ts) AS order_date, country,
       SUM(amount) AS revenue, COUNT(*) AS order_count
FROM sales.order_facts
GROUP BY DATE(order_ts), country;

BI Engine is an in-memory analysis service. You reserve memory capacity for a project in a location, and it accelerates eligible queries from Looker Studio, Looker and other tools, giving sub-second dashboard response.

Troubleshooting poor performing queries

Open the query's execution details to see stages, slot time, bytes shuffled and any skew. Then apply the fix that matches the symptom.

LIMIT does not reduce bytes scanned on a non-clustered table; it only shortens the output.

Security, masking, IAM and Cloud DLP

CREATE ROW ACCESS POLICY emea_only
ON sales.order_facts
GRANT TO ('group:emea-analysts@example.com')
FILTER USING (region = 'EMEA');

Masking rules include nullify, SHA-256 hashing and showing only the last four characters. Hashing returns the same value for the same input, so dashboards can still count and group by the masked column.

A worked example

A Looker Studio sales dashboard takes 40 seconds, scans 3 TB per refresh and shows customer emails to every viewer. The engineer creates a materialized view of daily revenue by country and points the dashboard at it.

A BI Engine reservation in the dataset's location gives sub-second charts. The email column gets a policy tag with a hash masking rule, and the data source switches to viewer's credentials so row policies apply per user.

How the exam asks about this

• "A dashboard repeats the same aggregation over a large, growing table." Create a materialized view and let smart tuning use it.
• "Analysts may group by email but must not see real addresses." A policy tag with a data masking rule such as SHA-256 hashing.
• "A query's execution details show one stage where a single worker takes far longer than the rest." Data skew on a join or grouping key; handle the hot keys separately or pre-aggregate.