Part 1 ended with a demo on Lakehouse Federation that we built within days. Once people started using it, the first thing we changed was how the dashboards query the data. The setup below is an intermediate step in moving the application's reporting onto Databricks, and later parts of the series change it again.
We built the first boards the fast way. The client sent Power BI examples, we rebuilt them with AI help, and each group of widgets got its own pre-aggregated SQL. That put real data in front of the client and got the approach approved. Then we looked at the query history.
It showed the same base query running several times per board. An AI/BI dashboard runs each dataset as its own query, and a filter change re-runs every dataset bound to it. With five datasets per board, each open and each filter click sent the same base query to the application's database five times.
We replaced that with a rule: one row-level dataset per dashboard, with aggregation in the widgets. The dataset returns rows plus a few derived columns. Counters, bars, lines and pies compute their own SUM and AVG through the widget encoding, and the filters bind to that one dataset. The base query runs once per load and once per filter change.
What the rule costs:
- a pivot table cannot filter a shared dataset to one subset of metric rows, so each pivot keeps its own pre-aggregated query
- a counter accepts only a simple aggregate of a column, so a percentage has to be computed as a derived column in the dataset
- widgets inline the dataset SQL onto one line, so a "--" comment turns the rest of the query into a comment; we use /* */ or no comments
We made one exception. Over federation a widget cannot push its aggregation down, so the KPI counters got their own pre-aggregated dataset. Once the data moved to Delta, we folded them back into the row-level one, and the counters started cross-filtering with the charts.
At production scale we saw that the rule depends on the source. Over federation, an aggregated dataset pushes its GROUP BY down to Azure SQL and returns in about 5 seconds. A raw-row dataset does not push the aggregation down and takes about 50. One wide dataset per board works well only with a fast source behind it, which is one of the reasons we later moved the data to Delta.
Today the combined board has 18 datasets, 15 of them bound to the filters, because pivots and per-dimension top-ten tables cannot share rows. When they run together, a dataset query takes 1.4 times longer at the median than the same query alone, and up to 3 times longer. Reducing the number of datasets further is still on our list. Like the rest of the setup, this board is one step toward the target solution on Databricks.
Next: five reports that read the same data, and what it cost to embed each one as its own dashboard.