Home › BI and analytics › Power BI vs Databricks AI/BI › Power BI in depth
BI and analytics · free guide · page 2 of 3
Power BI and Fabric, in depth
How the new Fabric-based Power BI works, what happens inside a query, how it handles volume and row-level security when the data is in a Databricks lakehouse, and how to troubleshoot, benchmark and test it before production.
- How Fabric works
- Inside a Power BI query
- Storage modes and volume
- Direct Lake on Databricks data
- How RLS works in Power BI
- Troubleshooting slow reports
- Benchmarking
- Testing
- Limits
- Production problems
The new Power BI
How Fabric works
Power BI is now one part of Microsoft Fabric. Fabric is a software-as-a-service data platform: you do not run servers, you buy a capacity and every Fabric workload spends from it. Power BI Premium capacities (P sizes) have been replaced by Fabric capacities (F sizes).
One capacity, many workloads, one lake. Power BI is the reporting workload on top.
| Idea | What it means |
|---|---|
| Capacity | You buy a capacity size (F2 up to F2048). Every Fabric job, including Power BI queries and refreshes, spends capacity units (CU) from it. |
| Smoothing | Short spikes are spread out: interactive work (report clicks) over at least 5 minutes, background work (refreshes) over 24 hours. You pay for the average, not the peak. |
| Throttling | If you keep borrowing from the future: after about 10 minutes of overuse, report clicks are delayed; after about 60 minutes, they are rejected; after 24 hours, refreshes are rejected too. Watch it in the Fabric Capacity Metrics app. |
| Licences | Below F64, every viewer needs a Pro (or Premium Per User) licence. From F64 up, viewers with a free licence can read content; authors still need Pro. |
| OneLake | One data lake per company, storing tables as Delta Parquet. Shortcuts point to ADLS, S3 or GCS without copying. Mirroring brings other databases in, including Azure Databricks Unity Catalog catalogs. |
| Workspaces | Folders of items (semantic models, reports, lakehouses) with roles: Admin, Member, Contributor, Viewer. Only Viewers are held by RLS. |
Internals
Inside a Power BI query
Every visual sends a DAX query. Two engines answer it.
VertiPaq, the in-memory engine, stores each column separately. It replaces each distinct value with a small number (dictionary encoding) and stores runs of the same number once (run-length encoding). So a column with few distinct values (country, status) shrinks enormously, and a column with millions of distinct values (timestamps to the second, GUIDs, free text) barely shrinks. Cardinality decides model size and speed.
The formula engine works out what the DAX means and runs on one thread. The storage engine scans compressed columns on many threads, or, in DirectQuery, turns the request into SQL for Databricks. Both cache results, which is why the second run of a query is faster (test both cold and warm).
Volume
Storage modes and how volume is handled
Where the data sits decides speed, freshness, cost and whether Unity Catalog security applies.
| Mode | How it works | Good at | Watch out for |
|---|---|---|---|
| Import | Refresh reads the source and stores a compressed, column-by-column copy in VertiPaq memory. | Fastest interaction; full DAX; works offline from the source. | Stale between refreshes; refresh time and memory; Unity Catalog RLS is lost. |
| DirectQuery | Each visual becomes SQL sent to the Databricks SQL warehouse at click time. | Always fresh; no copy; Unity Catalog RLS applies with single sign-on. | Every click costs warehouse time; slower; some DAX gets heavy; limits on rows and connections. |
| Dual | A table that can act as Import or DirectQuery depending on the query. | Small dimension tables in composite models. | Must be refreshed like Import. |
| Composite + aggregations | Import a small aggregate table, keep the big fact in DirectQuery; Power BI answers from the aggregate when it can. | Big data with fast summary pages. | The most moving parts; aggregate must match the detail. |
| Direct Lake | Power BI reads the Delta Parquet files in OneLake straight into VertiPaq memory, column by column, when first needed. 'Refresh' only points the model at the newest Delta version (called framing). | Import-like speed without copying; fresh after framing. | Data must be in OneLake; guardrails per capacity size; Databricks-written files lack V-Order and read slower; Unity Catalog rules do not apply. |
Rules of thumb on a lakehouse: Import for gold aggregates up to tens of millions of rows that refresh a few times a day; incremental refresh with partitions for bigger Import tables; DirectQuery with aggregation tables for billions of rows or minute-level freshness; Direct Lake only when the data is in OneLake already.
Direct Lake
Direct Lake when the data is in Databricks
| Route | How | Catch | Verdict |
|---|---|---|---|
| Shortcut from OneLake to the Databricks storage (ADLS on Azure, S3 on AWS) | No copy; Direct Lake reads the files. On AWS the reads cross clouds and S3 charges data leaving AWS. | Unity Catalog permissions are not checked on file reads: whoever can read the shortcut reads every row. Protect with Power BI roles or OneLake security. | Pursue only on Azure, for non-sensitive or Power-BI-secured data |
| Mirrored Azure Databricks catalog in Fabric | Fabric reads Unity Catalog metadata and makes shortcuts for you. | Tables with row filters or column masks are not mirrored (check the current limits). | Pursue for non-sensitive gold on Azure |
| Copy gold into a Fabric lakehouse with a pipeline | A second copy, V-Ordered for Fabric. | Two copies to keep in sync; drift risk. | Drop unless the company is moving to Fabric |
| Skip Direct Lake; use DirectQuery with single sign-on or Import | Data stays in Databricks. | DirectQuery cost and speed, or Import staleness. | Pursue on AWS and GCP |
Security
How row-level security works in Power BI
A role is a DAX filter on a table, written in the model. When a user opens a report, Power BI adds the filter for each role the user belongs to, and the filter flows along relationships from the 'one' side to the 'many' side. Several roles add up (a user in North and South roles sees both). Members are assigned in the service, ideally as Entra ID groups.
| Kind | How | Catch | Verdict |
|---|---|---|---|
| Static role | A DAX filter with a fixed value, e.g. [region] = "North"; one role per region. | Many roles to maintain; grows badly. | Drop beyond a handful |
| Dynamic role | One role; the filter compares a column with USERPRINCIPALNAME() through an entitlements table. | Needs the entitlements table and a single-direction relationship from it. | Pursue |
| Object-level security (OLS) | Hides whole tables or columns from a role (e.g. salary). | Reports that use the hidden column break for that role. | Pursue for sensitive columns |
| Embedded, 'app owns data' | Your app passes an effective identity (user name and roles) in the embed token. | Your app must pass the right identity every time. | Pursue for customer-facing embedding |
| DirectQuery with single sign-on | The user's Entra ID identity is passed to Databricks, so Unity Catalog filters apply as well. Simple on Azure Databricks; on AWS and GCP check current OAuth support. | Both rule sets apply; the user sees the overlap. | Pursue for sensitive, fresh data |
For the full both-sides design with Unity Catalog, see How RLS works on both sides.
When it is slow
Troubleshooting Power BI performance
| Symptom | Tool to find the cause | Usual fixes |
|---|---|---|
| A report page is slow | Performance Analyzer in Power BI Desktop: time per visual split into DAX query, visual display and other. | Copy the slowest visual's DAX into DAX Studio. |
| One DAX query is slow | DAX Studio with Server Timings and the query plan: formula engine vs storage engine time, number of storage engine queries. | High formula-engine time: rewrite the DAX (iterators over big tables, nested CALCULATE). Many storage-engine queries: simplify the model or measure. |
| The model is big or refresh runs out of memory | VertiPaq Analyzer (in DAX Studio or Tabular Editor): size per table and column, cardinality. | Drop unused columns; split date and time; remove high-cardinality IDs and GUIDs; turn off auto date/time; prefer measures to calculated columns. |
| DirectQuery is slow | Databricks query history and query profile for the SQL Power BI sent: bytes scanned, files pruned, Photon, queueing. | Cluster gold on filter columns; add aggregation tables; reduce visuals per page; set 'assume referential integrity' on relationships to get inner joins. |
| Everyone is slow at 9 a.m. | Fabric Capacity Metrics app: CU per item and operation, throttling, overage. | Move refreshes off peak; fix the heaviest model; size up the capacity or split workloads across capacities. |
| Refresh is slow or fails | Refresh history; Power Query diagnostics; Databricks query history for the refresh queries. | Incremental refresh with partitions; fold transformations into SQL (or do them in gold); use a service principal, not a personal OAuth token that expires. |
| Good-practice audit | Tabular Editor Best Practice Analyzer; Fabric workspace monitoring (logs into an Eventhouse) or Log Analytics for query logs. | Run the analyzer in CI and fail on serious rules. |
Benchmarking
How to benchmark Power BI
- Set a budget first: for example, every page renders in under 3 seconds at the 95th percentile, with 50 people using it at once, and costs under a set number of capacity units.
- Single query: DAX Studio's benchmark runs a query several times with a cold cache and a warm cache and reports both. Record the storage-engine and formula-engine split.
- Whole page: Performance Analyzer, exported as JSON, gives the time per visual. Keep the file per release to compare.
- Many users: Microsoft's Realistic Load Test Tool replays report sessions in browsers; watch the Capacity Metrics app (CU and throttling) and, for DirectQuery, the Databricks warehouse (queueing, clusters, p95 latency).
- Same data, every run: benchmark on a fixed copy of production-sized data, otherwise you compare data growth, not changes.
Testing
How Power BI testing works
| Test | How | What it proves |
|---|---|---|
| Model as code | Save as Power BI Project (PBIP) with the model in TMDL text files; keep it in git through Fabric Git integration. | Every change is reviewed as a diff. |
| Rules check | Tabular Editor Best Practice Analyzer from the command line in CI. | Catches bidirectional relationships, missing formats, unused columns before merge. |
| Same-number test | Run each measure through the executeQueries REST API, the XMLA endpoint, or semantic link (sempy) in a Fabric notebook; run the matching metric view in Databricks; compare. | Fails the build if Power BI and Databricks disagree. |
| RLS test | executeQueries with impersonatedUserName (or 'View as role' by hand) for test users per region; assert row counts. | Proves a North user sees only North, and matches the Unity Catalog row count for the same user. |
| Refresh test | Deploy to a test workspace through deployment pipelines and refresh against test data. | Catches broken sources and timeouts before production. |
| Performance test | DAX Studio benchmark per key query; budget per page. | Stops a slow change from shipping. |
Limits
Limits to know (check the current numbers)
| Area | Limit |
|---|---|
| Model size | 1 GB per model on Pro; on Fabric the memory per model grows with capacity size (about 25 GB on F64, check the current table). |
| Refresh | 8 scheduled refreshes a day on Pro, 48 on capacity; a refresh times out after about 2 hours on Pro and 5 hours on capacity. |
| DirectQuery | About 1 million rows per intermediate result by default; a limited number of parallel connections to the source per model. |
| Direct Lake guardrails | Rows per table, Parquet files and row groups per table, and memory per model, each scaling with capacity size (around 1.5 billion rows per table on F64, check). |
| RLS | Not applied to workspace Admins, Members and Contributors; only DAX filters on rows, no masking by value (use OLS to hide columns). |
Production
Production-grade problems and their fixes
| Problem | Fix |
|---|---|
| Refresh fails at 3 a.m. because the person whose login it used left | Use a service principal for data source credentials; alert on refresh failure. |
| Capacity throttled by one bad model; all reports slow | Capacity Metrics alerts; separate capacities for critical and self-service work; fix the heavy model. |
| Ten copies of 'Sales model' made by analysts, each a little different | One certified, endorsed thin model; others build reports on it (live connection), not new models. |
| A member of the workspace sees every row | Readers get the Viewer role or an app; members are only the build team. |
| Export to Excel takes all rows out of the governed world | Limit export in tenant settings for sensitive models; sensitivity labels. |
| DirectQuery bill on Databricks doubles | Watch warehouse cost by query source; add aggregations; switch summary pages to Import. |
| Direct Lake falls back to DirectQuery silently and gets slow | Watch the fallback in query traces; stay inside guardrails; run OPTIMIZE to cut file counts. |
| Private networking blocks the cloud service from Databricks | Use the virtual network data gateway or a private-link setup, and plan it early. |
FAQ
Questions people ask
How does Direct Lake work with data in Databricks?
Direct Lake reads Delta Parquet files in OneLake into memory. Databricks data reaches OneLake through a shortcut to its storage or through a mirrored Azure Databricks catalog. Unity Catalog row filters and masks do not apply to those file reads, so secure the data in Power BI or OneLake, and expect slower loads because Databricks does not write V-Order.
Why is my Power BI report slow?
Find the slow visual with Performance Analyzer, then the slow part with DAX Studio Server Timings. Formula-engine time means the DAX needs rewriting; storage-engine or SQL time means the model, the data size or the source is the problem.
How do you test Power BI?
Keep the model as code (PBIP and TMDL in git), run Best Practice Analyzer in CI, test each measure against the source (same-number test), test RLS with impersonated users, refresh in a test workspace, and benchmark key queries before release.
Does Power BI RLS apply to workspace members?
No. Admins, Members and Contributors of the workspace see all rows. Only users with the Viewer role, or app audiences, are filtered.
Read on