Analytics, Dashboard & Optimization.

1. Measure before touching anything. Performance Recording splits query time, render time and interaction cost — the answer changes completely depending on which dominates.

2. Decide what genuinely needs live access. The source changes hourly and the dashboard refreshes every minute; that alone is 60 refreshes per meaningful change. Almost certainly only a current-day operational slice needs live.

3. Move the repeated metrics upstream. Six calculated fields performing the same distinct count is one governed aggregate waiting to be built.

4. Build an extract or aggregate for the historical views, keep the small live slice if the business truly needs it, and let the two coexist — hybrid rather than doctrine.

5. Reduce first-paint worksheets, scope the filters to the panels they actually mean something for, and remove the 48,000-row grid in favour of Top-N plus drill-through.

6. Align refresh cadence with source freshness, ideally by invalidating on pipeline completion rather than on a timer.

Then re-measure and state what you bought — and what it cost in freshness.

Start with the definition, not the performance. Fourteen tiles with slightly different active-user logic is a governance failure that happens to also be slow. One LookML definition, owned, is the first change — everything else gets easier afterwards.

Give the queries a shape worth caching. Fourteen near-identical explores over large windows fragment the cache because no two requests match. Converging on common query shapes and bounded default windows is what makes caching effective at all.

Add aggregate awareness so tiles resolve against a smaller table where the grain permits, with persistent derived tables or aggregate tables where the workload justifies maintaining them.

Cut first-paint tiles. Fourteen embedded Looks is fourteen concurrent requests every time the page opens; most pages have three or four that people actually read first.

Name the trade-off: aggregates constrain ad-hoc exploration, so the process for adding a dimension has to be fast or analysts will route around the model.

Profile first. Performance Analyzer attributes the eight seconds across DAX evaluation, query execution and visual rendering; query diagnostics show what is actually reaching the source. Which of the three dominates decides everything after.

Look at the model before the measures. High-cardinality columns drive model size and compression; calculated columns are materialised at refresh and inflate it further. Ask whether each one needs to exist, and whether it belongs upstream instead.

Question the storage mode. DirectQuery on every page means every interaction is a source round trip. Import, or Direct Lake where the platform and storage support it, changes the interaction profile entirely — with the usual freshness trade-off to state out loud.

Examine the DAX. Measures that force large filter-context evaluation, or that iterate row by row over big tables, are a common cause of slow slicers even on a healthy model.

Then the visuals. Reduce the count per page, turn off automatic cross-interaction where it is not wanted so one slicer does not refresh everything, and add aggregations for the summary paths.

Behaviour here depends on version, capacity and configuration — so present these as the things you would check, with the profiler deciding the order.

Start with evidence, not taste: per-visual usage telemetry plus a handful of user interviews asking what decision they come here to make. Then rebuild around one question per surface — if there are three audiences, that is three dashboards over one governed model, not 24 charts and 18 filters. Impose a hierarchy: three to five KPI tiles with comparisons on top, two or three explanatory charts below, detail behind drill-through. Cut the palette to one, give the filters opinionated defaults, and ship it as a migration with a deprecation window rather than deleting charts people quietly depend on. Measure success by time-to-first-insight and repeat usage, not by charts removed.

Record a Performance Recording and split the 12 seconds before touching anything — query execution, connection/queue, rendering, and layout compute are four different problems. A live connection re-querying every worksheet on each filter change points at extract-vs-live; many worksheets each firing their own query points at topology; a slow render with a fast query points at mark count or a giant crosstab. Check whether the filter is a quick filter forcing a full domain scan on a high-cardinality field, and whether context filters are being recomputed. Only after the split do you look at the generated SQL.

Every viewer's first paint scans the full history across every panel, so the most expensive query in the system is also the most frequently executed one — and partition pruning does nothing when the filter is "everything". It also grows without limit: the dashboard gets slower every quarter with no code change. Set a bounded default (28 days or the current quarter), make longer ranges an explicit choice, align partitioning to the date filter, and point the default view at a rollup. Keep the all-time number if the business wants it, but serve it from a pre-computed total rather than a live scan.

Assume different definitions before assuming a bug. Collect each number's provenance — query, source, period boundary, filters — and reproduce both. Then walk the standard axes: gross vs net of refunds and discounts; bookings vs billings vs recognised revenue; order date vs ship date vs invoice date; time zone; currency rate and rate date; test, internal and intercompany transactions; cancellations. Build a reconciliation bridge that adds each named difference until one figure becomes the other — that artefact ends the argument. Only then look for defects: fan-out joins, dedup differences, a stale watermark. Prevent it with one definition, one business owner, visible certification, and an automated reconciliation job.

Because the unit of load is not the query, it is queries × viewers, and they all arrive in the same three minutes. Twenty panels × 400 viewers is 8,000 executions against a warehouse with finite concurrency slots; everything past the slot limit queues, so the two-second query becomes a two-second query behind ninety seconds of waiting. The fix is topology, not tuning: a result cache so identical queries execute once, cache warming scheduled before 9am, an extract or aggregate so the queries are cheap enough to be absorbed, and workload isolation so ad-hoc analysis cannot starve the executive tier. If interactive concurrency is a permanent requirement, that is the argument for an OLAP serving tier.

No — and being able to say that well is the point of the question. Ask what decision changes inside the freshness window; if nobody acts within the minute, the requirement is an alerting requirement wearing a dashboard costume. Split the surface: a small real-time panel fed by a streaming aggregate for the genuinely operational metrics, and the historical bulk served from batch rollups, each labelled with its own freshness. That keeps one expensive path narrow instead of making every panel expensive. Then push the threshold conditions into alerts that fire whether or not anyone is looking at the screen.

Lead with the principle: nothing scans 5 PB interactively, so the answer must already exist when the question is asked. Events land in an open table format on object storage, partitioned by date and clustered on the most selective dimension — that is the system of record and the drill-through target, not what dashboards query. Above it, rollup cubes at the ten to twenty dimension combinations users actually slice, built incrementally, with additive measures and HLL sketches so distinct counts stay mergeable. Serve those from an OLAP engine (Druid, Pinot, ClickHouse) built for high-concurrency sub-second scans, fronted by a result cache with warming, behind a semantic layer that routes queries to the right rollup automatically. Isolate interactive compute from batch, cap per-query resources, and control cost with incremental builds and usage-driven cube retirement. Then name the trade-off: precomputation buys latency at the cost of flexibility, so the process for adding a dimension has to be fast or people will route around the platform.

Decompose the ratio first. Plot numerator and denominator separately: denominator fell with a flat numerator means top-of-funnel tracking broke or a bot filter changed; numerator rose alone is plausible improvement or double-counted events; both moved proportionally suggests a population or attribution change. Then check the release for renamed events, an SDK bump, a consent-banner change, or an event now firing twice. Plot hourly around the deploy — a vertical cliff at the deploy timestamp is instrumentation, a genuine product win almost always ramps. Segment by platform, app version and browser, since real breakage is usually scoped. Finally, reconcile against a system that did not change: payments, orders in the transactional database, server logs. Report with the decomposition and the independent check, not a hunch — then add volume anomaly detection and a tracking-plan test in CI so the next one announces itself.

Refuse the tool migration — that produces 4,000 dashboards in a new tool. Sequence it. Measure first: per-dashboard usage, cost attribution, lineage, duplicate-definition detection, published openly so the conversation runs on evidence. Govern next: pick the twenty to fifty metrics the business actually runs on, agree one definition and one named business owner each, implement them once in the semantic layer, and mark dashboards built on certified metrics visibly. Consolidate: retire what has no views in 90 days with notification and a restore window, merge near-duplicates, and make the certified path faster than the bespoke one — otherwise people rebuild around it. Then cost: kill scheduled refreshes for dashboards nobody opens, add aggregates for the heavy ones, right-size compute. Finally prevent regrowth with certification gates and lineage-based change review. Report certified-viewing share, not dashboards deleted.

The assistant must not author SQL against raw tables. Route every question through the semantic layer so "revenue" resolves to the one governed definition, and execute under the asking user's identity so row-level security applies to generated answers exactly as it does to clicked ones — an assistant that bypasses RLS is a data breach with a chat interface. Answer from pre-computed aggregates for latency, and attach freshness to every response: if yesterday's partition is 96% loaded, the answer says so before it says the number. Make it explainable by returning the metric definition, the filters applied and the generated query alongside the narrative, so a human can audit the path. Log every question, resolved query and returned answer for lineage and review. Constrain scope: contribution analysis over modelled dimensions is a solvable problem; open-ended causal claims are not, and the system should decline rather than speculate. Then evaluate it like a product — a fixed question set with known answers, run on every change to the semantic model.