Home › BI and analytics › Power BI vs Databricks AI/BI
BI and analytics · free guide · three pages
Power BI vs Databricks AI/BI, or both, on a lakehouse
Your data already sits in a lakehouse (Delta tables in Databricks). Which BI tool should people use, where should the business definitions live, where should row-level security live, how does each handle big data, and what does it cost? This page decides. The two in-depth pages explain how each tool works inside, how to troubleshoot, benchmark and test it, and what goes wrong in production.
- In plain words
- Where the semantic model lives
- Where row-level security lives
- How RLS works on both sides
- How each handles big data
- Cost comparison
- Which tool for which job
- The recommended architecture
- Questions people ask
In plain words
Three things a BI tool needs
Every BI setup needs three things. Data: the tables, here gold Delta tables in the lakehouse. Meaning: the semantic model, which says what 'revenue' or 'on-time delivery' means, how tables join, and which filters apply. Permission: row-level security (RLS), which decides that a sales manager in the North sees only North rows.
Power BI and Databricks AI/BI can both hold the meaning and the permission. The question is not which tool is better. The question is where meaning and permission should live so that every tool, and every AI agent, gives the same number to the same person.
The recommended picture. Meaning and permission live once in Unity Catalog. Tools that query as the person looking get the rules for free; Power BI copies get a thin model and a role that reads the same entitlements table.
Decision 1
Where should the semantic model live?
Three choices. Keep definitions in one place; a second hand-written copy always drifts.
| Option | What happens | Verdict |
|---|---|---|
| A. Databricks side: metric views hold every definition; Power BI keeps a thin model whose measures match them one to one | One truth for dashboards, Genie, agents and Power BI. Power BI still needs a model (it cannot run without one), but the model holds no business logic of its own. An automated test checks every Power BI measure against its metric view. | Pursue (default) |
| B. Both sides, each written by hand | Two teams, two languages (SQL/YAML and DAX), two release cycles. Within months 'revenue' differs by a filter. This is the most common real-world mess. | Drop |
| C. Power BI side only: Databricks stores tables, the Power BI model is the semantic layer | Fine when Power BI is the only consumer and nobody else needs the numbers. Breaks the day you add Genie, an agent, an API or a second BI tool: they cannot read DAX measures. | Only if Power BI is the sole consumer, forever |
| B, done right: definitions in one source (metric-view YAML), Power BI measures generated or checked from it | Both sides exist, but one is derived from the other and a test fails the build if they disagree. | Pursue when Power BI is large |
What a thin Power BI model looks like: tables or views from gold, relationships, and measures that are simple sums, counts or ratios with the same names and filters as the metric views. No calculated business rules that exist only in DAX. A nightly test runs each Power BI measure (through the XMLA endpoint or the executeQueries API) and the same metric view in Databricks, and fails if they differ.
Can Power BI read metric views directly? Support for reading Unity Catalog metric views from outside Databricks is still changing (check the current Databricks and Power BI connector notes). If your setup cannot, point Power BI at gold aggregate views generated from the same metric-view YAML, so the definition still lives in one file.
Decision 2
Where should row-level security live?
| Option | Works for | Fails for | Verdict |
|---|---|---|---|
| Databricks side only (Unity Catalog row filters and column masks) | Works for every consumer that queries as the person looking: AI/BI with viewer credentials, Genie, agents acting on behalf of the user, Power BI DirectQuery with single sign-on. | Power BI Import or Direct Lake: the data is copied out, so the filter is not there. Power BI DirectQuery with a shared service account: Databricks sees one user. | Pursue (default) |
| Power BI side only (roles in the model) | Works inside Power BI for Import, Direct Lake and DirectQuery. | Nothing else is protected: an analyst in Databricks, Genie or an agent sees every row. Workspace admins, members and contributors skip Power BI RLS entirely. | Drop as the only layer |
| Both sides, driven by one entitlements table | Needed whenever Power BI uses Import or Direct Lake. Both rules read the same gold table (who may see which region, plant or customer), so access is changed in one place. | Two enforcement points to test. The test suite runs the same user on both sides and expects the same row count. | Pursue when Power BI copies data |
| Both sides, each kept by hand (AD groups in Power BI roles, different groups in Unity Catalog) | Looks fine on day one. | Someone is removed in one place and not the other. Auditors find it. | Drop |
Decision 2, continued
How RLS works on both sides at once
One entitlements table, two enforcement points. With DirectQuery and single sign-on both apply, and the user sees the overlap.
The entitlements table lives in gold and is owned by the data team, fed from HR or your identity system: one row per person (or group) per thing they may see, such as user_email, region.
-- Unity Catalog: a row filter function, then attach it to the table
CREATE FUNCTION sec.region_filter(region STRING) RETURN
is_account_group_member('bi_all_regions')
OR EXISTS (SELECT 1 FROM gold.entitlements e
WHERE e.user_email = current_user() AND e.region = region);
ALTER TABLE gold.sales SET ROW FILTER sec.region_filter ON (region);
-- A column mask: only HR sees salaries
CREATE FUNCTION sec.mask_salary(v DECIMAL(12,2)) RETURN
CASE WHEN is_account_group_member('hr') THEN v ELSE NULL END;
ALTER TABLE gold.employees ALTER COLUMN salary SET MASK sec.mask_salary;Databricks adds the filter to every query on Databricks compute (SQL warehouses, serverless, standard clusters), including queries from metric views, dashboards, Genie and Power BI DirectQuery.
// Power BI: a role on the imported Entitlements table // Model: Entitlements[region] 1 ─► * Sales[region] (filter flows to Sales) [user_email] = USERPRINCIPALNAME()
Power BI: the role filters the small Entitlements table and the relationship carries the filter to the fact table. Members of the role are Entra ID groups, assigned in the service.
What the user sees in DirectQuery with single sign-on: Power BI's role filters first, then the SQL reaches Databricks as that user, so the Unity Catalog filter also applies. The user sees rows allowed by both. With one shared entitlements table the two always agree.
Why not just one side? Unity Catalog cannot see inside a Power BI Import or Direct Lake model: those read a copy or the files directly. Fabric cannot mirror Databricks tables that have row filters or column masks (check the current mirroring limits), which pushes you to Power BI roles for Fabric-side copies.
Decision 3
How each handles big data
| Mode | Where the data sits | What limits it | Good for |
|---|---|---|---|
| Power BI Import | Compressed copy in memory (often 5 to 10 times smaller than Parquet text, depends on cardinality) | Model memory limit per capacity size; 1 GB per model on Pro; refresh time windows; refresh doubles memory while it runs | Gold aggregates up to tens of millions of rows; fastest clicks |
| Power BI DirectQuery | Nothing copied; every visual is a SQL query | Each visual waits on the warehouse; a 1 million row cap on intermediate results; limited parallel connections | Billions of rows, if you add aggregation tables and a well-clustered gold layer |
| Power BI Direct Lake | Delta files in OneLake loaded into memory column by column when first asked | Guardrails per capacity size (rows per table, files and row groups per table, memory); beyond them it falls back to DirectQuery or fails | Large Fabric-native data; Databricks data only through shortcuts or mirroring |
| Databricks AI/BI | Nothing copied; every widget is a SQL query on a SQL warehouse | Warehouse size and number of clusters; result-size caps per widget; cost grows with clicks | Any size the warehouse can scan quickly; clustered gold tables and materialized views keep it fast |
The rule that matters most is the same for both tools: build gold aggregates for dashboards. A dashboard that scans 4 billion raw rows to draw 12 monthly bars is slow and costly in any tool. Pre-aggregate by day and the main dimensions, cluster on the filter columns, and keep the raw detail for drill-through only.
Decision 4
Cost comparison
Example: 25 report authors, 500 viewers, used 10 hours a day on 22 working days. US list prices as known in November 2026, rounded; your discounts, region and Microsoft 365 agreement change them a lot. Databricks serverless SQL at about $0.70 per DBU; a Small warehouse is 12 DBU an hour and a Medium is 24.
| Setup | BI licences per month | Databricks compute per month | Total per month | Comment |
|---|---|---|---|---|
| Power BI Pro for everyone, Import | 525 × $14 = about $7,350 | refresh only: about 22 hours of Small = about $185 | about $7,500 | Simple; cost grows with every viewer |
| Fabric F64 (viewers free) + 25 Pro authors, Import or Direct Lake | F64 about $5,000 reserved (about $8,400 pay-as-you-go) + $350 | about $185 for refresh | about $5,500 to $9,000 | Pays off above roughly 400 viewers; capacity also runs other Fabric work |
| Power BI Pro for everyone, DirectQuery | about $7,350 | every click: Medium for 220 hours = about $3,700 | about $11,000 | The worst of both bills; use only where freshness or Unity Catalog RLS demands it |
| Databricks AI/BI only | none (no per-viewer licence) | Small to Medium for 220 hours = about $1,850 to $3,700; auto-stop lowers it | about $2,000 to $4,000 | Cheapest for many occasional viewers; costs follow usage |
| Both: Power BI for 100 report users, AI/BI and Genie for the rest | 100 × $14 = $1,400 (often $0 if Microsoft 365 E5 already includes Pro) | about $2,000 to $3,000 | about $2,000 to $4,500 | Usually the best value in a Microsoft shop |
Rough, unverified list-price estimates. Run your own numbers in the Databricks cost calculator and with your Microsoft agreement. Two things move the answer most: whether Microsoft 365 E5 already includes Power BI Pro for everyone, and how many viewers open dashboards only a few times a month.
Decision 5
Which tool for which job
| Job | Use | Why | Verdict |
|---|---|---|---|
| Operations, platform and data-trust dashboards | AI/BI | Next to the data, no licences, Unity Catalog rules apply directly. | Pursue |
| Plain-language questions from business users | Genie over metric views | Answers use the same certified definitions as the dashboards. | Pursue |
| Agents that need numbers | Typed tools over metric views (or the Genie API) | Agents call tools; they never write free SQL against raw tables. | Pursue |
| Board packs, finance, paginated and printed reports, Excel, Teams | Power BI | Stronger formatting, paginated reports, Excel and Teams; Pro is often already paid for. | Pursue in a Microsoft 365 company |
| Heavy self-service modelling by analysts who know DAX | Power BI on a certified thin model | Let them build reports, not new definitions of revenue. | Pursue with guardrails |
| A company with no Microsoft estate | AI/BI only | Power BI would add licences, a second model and a second set of security rules for little gain. | Drop Power BI |
| Power BI Copilot as the agent layer | — | It works only over a Power BI model and needs Fabric capacity; it becomes a second source of truth for AI. | Drop |
Putting it together
The recommended architecture
- Gold: Delta tables, liquid-clustered on the columns dashboards filter by, plus pre-aggregated gold tables or materialized views for dashboards.
- Semantic layer: Unity Catalog metric views, written in YAML, kept in git, certified. The only place a business number is defined.
- Security: one entitlements table in gold; Unity Catalog row filters and column masks read it; Power BI roles read it only where Power BI copies data.
- AI/BI dashboards for operations and engineering, published with viewer credentials when data is sensitive.
- Genie over metric views only, with instructions, example questions and benchmarks. Agents call typed tools over metric views.
- Power BI: a thin, certified model. DirectQuery with single sign-on for sensitive, fresh data; Import for small, non-sensitive, heavily used aggregates; Direct Lake only if the company runs on Fabric and the data is in OneLake.
- Tests: the same-number test (Power BI measure equals metric view), the same-rows test (one user sees the same row count in both tools), and a performance budget per dashboard (see the in-depth pages).
Swapping tools later is then cheap: a new BI tool is one more reader of the same metric views and the same security rules.
FAQ
Questions people ask
Should the semantic model live in Databricks or in Power BI?
Keep the definitions in Databricks (Unity Catalog metric views) and keep the Power BI model thin, with measures that match the metric views one to one and a test that checks they agree. Power BI always needs a model to run, but it should not invent its own business logic.
Where should row-level security live?
In Unity Catalog, as row filters and column masks, driven by one entitlements table. Add a Power BI role that reads the same table only when Power BI copies data out (Import or Direct Lake), because then Unity Catalog cannot see the query.
Does Unity Catalog row-level security apply to Power BI?
Only in DirectQuery with single sign-on, where each Power BI user's own identity reaches Databricks. With Import, Direct Lake, or a shared service account, it does not, and Power BI must apply its own role.
Is Databricks AI/BI cheaper than Power BI?
For many occasional viewers, usually yes: AI/BI has no per-viewer licence and you pay only for warehouse time. For a few heavy users in a company that already pays for Microsoft 365 E5, Power BI can cost almost nothing extra. DirectQuery from Power BI pays both bills.
Can we use both?
Yes, and many companies should: AI/BI and Genie for operations, engineering and plain-language questions, Power BI for formal reporting in Microsoft 365. The rule is one set of definitions and one entitlements table under both.
Read on
The in-depth pages
Power BI and Fabric, in depth: how it works inside, Direct Lake, RLS mechanics, troubleshooting, benchmarking, testing, production problems. Databricks AI/BI, in depth: the same for AI/BI dashboards and Genie. Related: the semantic layer · Genie and metric views · know your consumers.