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.

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).

Fabric capacity (F2 … F2048)one pool of capacity units (CU)Power BIData FactorySpark / LakehouseWarehouseReal-TimeData ScienceOneLake: one lake per tenant, Delta Parquet filesshortcuts point at ADLS, S3 or GCS without copying; mirroring copies other databases in

One capacity, many workloads, one lake. Power BI is the reporting workload on top.

IdeaWhat it means
CapacityYou buy a capacity size (F2 up to F2048). Every Fabric job, including Power BI queries and refreshes, spends capacity units (CU) from it.
SmoothingShort 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.
ThrottlingIf 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.
LicencesBelow 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.
OneLakeOne 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.
WorkspacesFolders of items (semantic models, reports, lakehouses) with roles: Admin, Member, Contributor, Viewer. Only Viewers are held by RLS.

Internals

Inside a Power BI query

visualDAX queryFormula engineone thread: thelogic of the DAXStorage engineVertiPaq: many threads,scans compressed columnsDirectQuerySQL sent to theDatabricks warehousememory: columnsdictionary + run-length encodedDelta tables(Photon doesthe work)Slow query? Look at the split: lots of formula-engine time means the DAX is the problem; lots of storage-engine or SQL time means the data or the model is.

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

ImportPower BI visualrefresh copiesdata intoVertiPaq memoryDelta tablesfastest clicks;stale until refreshDirectQueryPower BI visualeach visual sendsSQL to Databricksevery timeDelta tablesalways fresh;every click costsDirect Lake (Fabric)Power BI visualreads Delta files inOneLake straightinto memoryDelta tablesfast and fresh;needs OneLakeCompositePower BI visualsmall tables imported,big facts DirectQuery,aggregations on topDelta tablesbalanced;most moving parts

Where the data sits decides speed, freshness, cost and whether Unity Catalog security applies.

ModeHow it worksGood atWatch out for
ImportRefresh 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.
DirectQueryEach 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.
DualA table that can act as Import or DirectQuery depending on the query.Small dimension tables in composite models.Must be refreshed like Import.
Composite + aggregationsImport 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 LakePower 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

RouteHowCatchVerdict
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 FabricFabric 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 pipelineA 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 ImportData stays in Databricks.DirectQuery cost and speed, or Import staleness.Pursue on AWS and GCP
Security note. Direct Lake and shortcuts read files, not tables through Unity Catalog. Row filters and column masks are not applied. If the data is sensitive, either secure it in Power BI (roles and OLS) and OneLake, or stay on DirectQuery with single sign-on.

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.

KindHowCatchVerdict
Static roleA 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 roleOne 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-onThe 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

SymptomTool to find the causeUsual fixes
A report page is slowPerformance 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 slowDAX 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 memoryVertiPaq 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 slowDatabricks 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 failsRefresh 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 auditTabular 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

TestHowWhat it proves
Model as codeSave 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 checkTabular Editor Best Practice Analyzer from the command line in CI.Catches bidirectional relationships, missing formats, unused columns before merge.
Same-number testRun 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 testexecuteQueries 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 testDeploy to a test workspace through deployment pipelines and refresh against test data.Catches broken sources and timeouts before production.
Performance testDAX Studio benchmark per key query; budget per page.Stops a slow change from shipping.

Limits

Limits to know (check the current numbers)

AreaLimit
Model size1 GB per model on Pro; on Fabric the memory per model grows with capacity size (about 25 GB on F64, check the current table).
Refresh8 scheduled refreshes a day on Pro, 48 on capacity; a refresh times out after about 2 hours on Pro and 5 hours on capacity.
DirectQueryAbout 1 million rows per intermediate result by default; a limited number of parallel connections to the source per model.
Direct Lake guardrailsRows 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).
RLSNot 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

ProblemFix
Refresh fails at 3 a.m. because the person whose login it used leftUse a service principal for data source credentials; alert on refresh failure.
Capacity throttled by one bad model; all reports slowCapacity 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 differentOne certified, endorsed thin model; others build reports on it (live connection), not new models.
A member of the workspace sees every rowReaders get the Viewer role or an app; members are only the build team.
Export to Excel takes all rows out of the governed worldLimit export in tenant settings for sensitive models; sensitivity labels.
DirectQuery bill on Databricks doublesWatch warehouse cost by query source; add aggregations; switch summary pages to Import.
Direct Lake falls back to DirectQuery silently and gets slowWatch the fallback in query traces; stay inside guardrails; run OPTIMIZE to cut file counts.
Private networking blocks the cloud service from DatabricksUse 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

The other pages

The decision · Databricks AI/BI, in depth