Gareth Stretch

Technical briefing · BI architecture

A technical briefing on BI architecture, written for a mixed executive and engineering audience.

Executive summary

Complex, KPI-dense Power BI reports don’t fail because of Power BI. They fail because the DAX engine is being asked to do the job of a distributed data platform. Databricks Apps can genuinely take that job over, but it isn’t a drop-in replacement: it swaps a mature, declarative reporting layer for an application-development platform with better ceilings and a much steeper first build. The right call is workload-specific, and the fix that pays off either way is moving KPI logic out of measures and into the lakehouse.

---

1. The brief

This is a familiar shape for a lot of large enterprises: the reporting standard is Power BI (it’s the Microsoft 365 relationship, the existing analyst skillset, the governed workspace model), but the actual data platform underneath is Databricks. That’s fine for most dashboards. It stops being fine once a report accumulates enough KPIs, enough nested DAX, and enough row-level security tiers that every storage mode you try trades one problem for another.

We’ve seen teams cycle through all three canonical fixes on the same report: Import mode, because it’s fast, until the model gets too big to refresh in the window available. DirectQuery, because the model’s too big, until the DAX doesn’t fold cleanly and every filter change is a multi-second wait. Hybrid/composite, to get the best of both, until the aggregation-table maintenance becomes its own project. None of these are wrong choices; they’re the correct escalation path inside Power BI’s architecture. But at some point the honest question is whether the constraint is the tool, or the fact that the tool’s calculation engine was never built to do what you’re now asking of it.

Why this matters beyond IT: For the business, this shows up as slow dashboards, stale numbers, or a backlog of “can we add one more KPI” requests that engineering keeps declining. For finance, it shows up as Premium capacity invoices that keep climbing because bigger models need bigger SKUs. Both are symptoms of the same root cause.

2. Anatomy of the three modes

Import mode

Power BI copies your data into VertiPaq, its in-memory columnstore, compresses it, and evaluates every DAX measure locally against that copy. This is why Import is fast even for complex DAX: the formula engine never leaves memory. The cost shows up upstream: refresh time, dataset size limits tied to your Premium capacity SKU, and a hard ceiling on how “live” your numbers can be. Incremental refresh buys you time, not architecture; it still copies the whole model logically, just in smaller nightly slices.

DirectQuery

No local copy. Every visual interaction gets compiled into SQL and sent to the source, in your case a Databricks SQL Warehouse. This is where complex DAX starts to hurt: measures with nested CALCULATE, many-to-many relationships, or row-level iterators either “fold” cleanly into a single efficient SQL statement, or they don’t. When they don’t, Power BI falls back to pulling more data locally and computing what it can, which is slow and sometimes silently wrong on edge cases. Every open dashboard is also concurrent load on your SQL Warehouse, so warehouse sizing becomes a BI cost line, not just an ETL one.

Hybrid / composite models

Composite models let you mix storage modes table-by-table, and add aggregation tables that sit in Import mode in front of a DirectQuery detail table. The engine tries the fast aggregated path first and only falls through to live DirectQuery on a cache miss. Done well, this is genuinely the best available answer inside Power BI for large, complex, semi-live models. Done without discipline, it becomes a second data model you have to keep in sync with the first, and every new KPI is a design decision about which storage mode it belongs in.

3. Where DAX actually breaks

Every DAX query splits its work between the formula engine (FE), which is single-threaded and handles row context and iteration logic, and the storage engine (SE), which is multi-threaded and scans and aggregates the columnstore. Simple measures spend almost all their time in SE, which is fast and scales. Complex measures (nested SUMX over FILTER, iterators calling other iterators, anything that forces a row-by-row CALCULATE context transition) push work into FE, and FE doesn’t parallelise. That’s the mechanism behind the thing every DAX author eventually learns the hard way: a report with 40 well-written measures can be fast, and the 41st measure, if it’s the wrong shape, can double your query time on its own.

Multi-tier RLS compounds this. Every role adds a filter that has to be evaluated for every context transition, and layered RLS (business unit, then region, then user) multiplies rather than adds to query cost. This is a real, well-documented ceiling, not a Power BI implementation mistake, and it’s the specific pain that “very complex reports” tends to actually mean.

4. The query’s journey: same KPI, four architectures

The bottleneck moves between these four approaches. It never disappears. Import and DirectQuery move it between refresh and live query, Hybrid buys the best of both at the cost of a second design surface, and a Databricks App moves the calculation itself onto a different engine entirely, which only pays off if that engine (Photon/Spark) is genuinely better suited to the calculation than VertiPaq is.

Approach	Engine	What happens on a filter click	Where it bottlenecks
Import mode	VertiPaq (in-memory)	DAX evaluated entirely in-memory against the last refresh	Refresh window + model size
DirectQuery	Query folding → SQL Warehouse	DAX compiled to SQL per visual, executed live	SQL folding limits + warehouse concurrency
Hybrid / Composite	VertiPaq + folded SQL, per table	Aggregation table intercepts if possible, else falls to DirectQuery	Aggregation-table design & drift
Databricks App	Photon / Spark SQL + app compute	App calls a pre-built SQL/metric view over Delta	App/compute cold-start + dev effort per interaction

5. Databricks Apps as a reporting layer

A Databricks App is a containerised web app, commonly Python (Streamlit, Dash, FastAPI + React), deployed and governed inside the Databricks workspace, with direct, low-latency access to Unity Catalog and SQL Warehouses/serverless compute. The reporting logic that lived in DAX measures moves to SQL views, materialised gold tables, or a metrics layer (Unity Catalog metric views, or Genie’s semantic layer), computed either at pipeline time or at query resolution in Spark SQL / Photon.

Practically, that trade shows up in three places:

• Calculation ceiling goes up. A join across billions of rows, a window function over years of transactional history, an ML-scored segmentation: these are exactly what Spark/Photon is built for, and exactly where VertiPaq and SQL-folded DirectQuery start to strain.
• Governance gets genuinely stronger. Unity Catalog’s row filters and column masks are policy objects attached to the table, enforced everywhere that table is queried, not a per-report DAX filter that has to be re-implemented in every model. For multi-tier RLS this is a materially better foundation.
• Reusability resets to zero. Power BI’s DAX measure library is reusable the moment it’s written; any report in the workspace can consume it. A Databricks App has no equivalent out of the box; every interactive control, drill-through, or bookmark-style state is application code you write and maintain, until you’ve built your own internal component library.

The part that’s easy to underestimate: Databricks Apps require authenticated workspace identities. Any report with external or partner viewers needs one of three patterns: Entra guest accounts via SCIM, an external auth gateway, or an embedded/pre-computed delivery model. Each adds real infrastructure and operational overhead that a native Power BI share link doesn’t.

6. Nuts-and-bolts matrix

Dimension	Import	DirectQuery	Hybrid	Databricks App
Calculation engine	VertiPaq (FE + SE)	SQL folding → Databricks SQL	VertiPaq + folded SQL	Spark SQL / Photon
Where complex logic lives	Reusable DAX measures	Reusable DAX, folded per query	Split across storage modes	SQL views, metric views, or app code
Row-level security	Static roles + DAX filters	Same, pushed to source where possible	Same, per-table nuance	Unity Catalog dynamic views / column masks
Refresh model	Scheduled / incremental	None, always live	Mixed per table	Pipeline-driven (batch, streaming, or on-demand)
Concurrency ceiling	High (in-memory)	Bound by SQL Warehouse sizing	High for aggregated paths	Bound by serverless/app compute sizing
Author skillset	DAX + Power Query	DAX + SQL awareness	DAX + data-modelling judgement	SQL/PySpark + Python + React/TypeScript
Self-service by analysts	Yes, native	Yes, with caveats	Yes, with caveats	Limited, engineering-mediated
Native visual maturity	10 years deep	10 years deep	10 years deep	Build-your-own (D3/Plotly/Chart.js)
Licence/compute cost driver	Premium capacity (P-SKU)	Premium + warehouse compute	Premium + partial warehouse	SQL Warehouse + app compute, no BI licence

7. The feasibility verdict

Feasible, and often the right call, for a specific slice of the portfolio, not the whole thing.

8. The fix, regardless of tool

Whichever way the Databricks Apps decision goes, the same architectural correction pays off: stop computing complex KPI logic inside DAX measures against a raw fact table, and compute it once, in the lakehouse, as governed gold-layer tables or Unity Catalog metric views. Power BI (in Import or DirectQuery mode) then reads something already aggregated and simple, instead of re-deriving the KPI, with all its context transitions, on every filter click.

This single change is what actually shrinks refresh windows, collapses DirectQuery fold failures, and cuts the aggregation-table sprawl in composite models. It’s also a prerequisite for a credible Databricks Apps migration anyway, since the app needs that same semantic layer to query against. It’s the one piece of this decision that isn’t really optional.

9. What we’d actually do

1. Inventory and score. Score every complex report on three axes: calculation scale (rows/joins), RLS complexity, and how much of it is really an interactive tool versus a dashboard. This is a day of work and it tells you which reports are candidates before anyone writes code.
2. Build the gold-layer metric first. For the highest-scoring candidate, move its KPI logic into a Unity Catalog metric view or gold table. This benefits it in Power BI immediately, independent of any Apps decision.
3. Pilot one Databricks App on the worst offender. Pick the single report causing the most pain, usually the one that’s already failed in Import, DirectQuery, and Hybrid, and rebuild it as a Databricks App. Treat the components (RLS pattern, drill-through, a chart library) as reusable, not bespoke.
4. Re-score the portfolio against the proven components. Once you have working, reusable pieces, the 10–21 day estimates for a fresh build drop substantially for the next report. Re-run the inventory and decide the next wave with real numbers instead of estimates.

Bottom line: This was never a “which tool wins” question. Power BI stays the right home for business-owned, self-service dashboards. Databricks Apps becomes the right home for the small number of reports where the calculation, the governance, or the interactivity has outgrown what a semantic layer built for spreadsheets-in-memory was designed to do. The fastest way to find out which is which is to build the gold layer first and let the pilot tell you.