Home › BI and analytics › Power BI vs Databricks AI/BI › AI/BI in depth
BI and analytics · free guide · page 3 of 3
Databricks AI/BI, in depth
How AI/BI dashboards and Genie run on your lakehouse, how row-level security and volume work, how to troubleshoot, benchmark and test them, their limits, and what goes wrong in production.
- How it works
- How volume is handled
- How RLS works
- Troubleshooting
- Testing and benchmarking
- Limits
- Production problems
Internals
How an AI/BI dashboard works
No copy and no separate engine: the dashboard is a set of SQL queries the warehouse runs against Unity Catalog tables.
| Part | What it does |
|---|---|
| Dashboard | A file (.lvdash.json) holding datasets, pages and widgets. Drafts are edited; a published copy is what viewers open. |
| Dataset | A SQL query or a metric view. Widgets aggregate on top of it, and Databricks pushes that aggregation into SQL, so only small results travel to the browser. |
| SQL warehouse | The compute that runs every widget query. Serverless starts in seconds, uses Photon, adds clusters when queries queue, and keeps a result cache. |
| Unity Catalog | Checks the person's rights on each table and adds row filters and column masks, every query. |
| Sharing | Share with users and groups in the Databricks account, even ones without workspace access; schedules email snapshots; dashboards can be embedded in other apps. |
| Genie space | A set of tables or metric views, plus instructions, example SQL and trusted functions. An LLM turns a question into SQL, runs it as the user, and shows the result. Benchmarks measure its accuracy; an API lets apps and agents ask it. |
Volume
How volume is handled
AI/BI has no in-memory copy. When a viewer opens a page, each widget's aggregation is turned into SQL and sent to the SQL warehouse. Photon scans only the columns and files needed (file pruning uses the clustering and statistics on the Delta tables) and returns a small result to the browser. So data size is limited by how fast the warehouse can scan it, not by memory in the BI tool.
Three things keep it fast at large volume: gold aggregates or materialized views sized for the dashboard, liquid clustering on the columns people filter by (date, region, plant), and enough clusters on the warehouse for the busiest hour. The result cache answers a repeated query without scanning again; it works best when viewers share credentials.
Security
How row-level security works in AI/BI
| Choice | How | Catch | Verdict |
|---|---|---|---|
| Viewer credentials (no embedded credentials) | Each widget query runs as the viewer, so Unity Catalog filters apply per person. | Results are not shared between viewers, so more warehouse work. | Pursue for sensitive data |
| Embedded (publisher's) credentials | Every query runs as the publisher; viewers see what the publisher sees. Results are shared, so it is cheaper. | Leaks data if the publisher can see more than the viewers. | Pursue only for data everyone may see |
| Row filters and column masks | SQL functions attached to tables, using current_user() and is_account_group_member(), often with an entitlements table. | Keep the function simple (a lookup), or every query slows down. | Pursue |
| Tag-based policies (attribute-based access control) | One policy applies to every table or column carrying a tag such as pii. | Newer; check its status in your region. | Pursue when available, to avoid one filter per table |
| Dynamic views with is_account_group_member() | The older way: a view that filters, readers get only the view. | One view per table to maintain; easy to bypass via the table. | Drop for new work |
Metric views inherit the row filters of their tables, and Genie runs as the asking user, so one set of rules covers dashboards, Genie and agents. The full code is on the decision page.
When it is slow
Troubleshooting AI/BI performance
| Symptom | Where to look | Usual fixes |
|---|---|---|
| A widget is slow | Query history filtered by the dashboard; the query profile for that query: bytes scanned, files pruned, spill, time spent queued. | Cluster gold on the filter columns; build a pre-aggregated table or materialized view; avoid SELECT * datasets. |
| Slow only in the morning | Warehouse monitoring: queued queries, number of clusters running. | Raise max clusters; pre-warm with a schedule; spread refresh schedules. |
| First open is slow | Cold start of the warehouse. | Serverless (seconds), or keep a warehouse warm during working hours. |
| Cost is climbing | system.billing.usage and system.query.history by dashboard and user. | Publish non-sensitive dashboards with shared credentials; remove auto-refresh; size the warehouse right. |
| Genie gives wrong answers | Genie benchmarks and the SQL it generated. | Point it at metric views; add instructions, synonyms and example SQL; fewer, cleaner tables. |
Testing
Testing and benchmarking AI/BI
| Test | How | What it proves |
|---|---|---|
| Dashboards as code | Keep .lvdash.json files in Git folders and deploy with Databricks Asset Bundles from dev to test to prod. | Every change is reviewed and repeatable. |
| Same-number test | Query each dataset and the metric view with the same filters; compare. | Dashboards and Genie agree with the certified numbers. |
| RLS test | Run the same query as test users or service principals in different groups; assert row counts and masked columns. | A North user sees only North; matches the Power BI test for the same user. |
| Genie accuracy | Genie benchmarks: questions with known-correct SQL, run on every change to the space. | Accuracy does not slip when someone edits instructions. |
| Performance test | Replay the dashboard's SQL from query history with many threads (Python with the Databricks SQL connector, JMeter or Locust); measure p50 and p95, queue time and DBUs; repeat with the result cache off (SET use_cached_result = false). | The dashboard stays inside its time and cost budget. |
Set the same kind of budget as for Power BI: page under 3 seconds at the 95th percentile at expected peak users, and a monthly DBU ceiling per dashboard.
Limits
Limitations (check the current numbers)
| Area | Limit |
|---|---|
| Formatting | Fewer chart types and less control than Power BI; no custom visuals; no pixel-perfect paginated reports (snapshots by email instead). |
| Modelling | No in-tool model language like DAX; logic goes in SQL, metric views or dashboard-level calculations. Good for one truth, less freedom for analysts. |
| Result size | Each widget's result has a size cap (tens of thousands of rows; check the current number). Fine for charts, not for exporting raw data. |
| Always live | No import mode: every open runs queries, so cost follows usage. The result cache and shared credentials soften it. |
| Office tools | Excel and Teams integration is weaker than Power BI's (check the current Excel connector and embedding options). |
| Genie | A limit on tables per space (check); answers are generated, so they need curation, benchmarks and a human check for important decisions. |
Production
Production-grade problems and their fixes
| Problem | Fix |
|---|---|
| A sensitive dashboard published with embedded credentials | Review every published dashboard's credential setting; make viewer credentials the default for gold tables with row filters. |
| Publisher leaves; dashboard stops refreshing | Owners are groups or service principals, set in the bundle. |
| Warehouse cost spike from auto-refresh and many viewers | Budgets and alerts on system.billing.usage; turn off frequent auto-refresh; shared credentials where allowed. |
| Dashboards built on silver, slow and inconsistent | Datasets read only gold and metric views; a review check blocks silver. |
| Genie answer used in a decision without checking | Show the SQL; certify questions in benchmarks; keep Genie to metric views. |
| Monday 9 a.m. queue | Max clusters sized for the peak; Intelligent Workload Management on serverless; schedules staggered. |
| Dashboard sprawl, nobody knows which is official | Certified folder and naming; owners; delete unused dashboards found through query history. |
FAQ
Questions people ask
Does AI/BI respect Unity Catalog row-level security?
Yes, when the dashboard runs with viewer credentials: each widget query runs as the viewer and Unity Catalog adds the row filter. With embedded (publisher's) credentials, everyone sees what the publisher sees.
How does AI/BI handle large data?
It never copies data. Each widget sends SQL to a SQL warehouse, which scans Delta tables with Photon and returns only the small aggregated result. Speed depends on the warehouse size and on how well gold is clustered and pre-aggregated.
What are the limitations of Databricks AI/BI dashboards?
Fewer visual types and formatting options than Power BI, no paginated reports, no DAX-style modelling in the tool, caps on result size per widget, weaker Excel and Teams integration, and every view costs warehouse time.
How do you test AI/BI dashboards?
Keep them as code in Databricks Asset Bundles, test datasets against metric views, test RLS by running queries as different test users, run Genie benchmarks, and replay dashboard SQL under load for performance.
Read on
The other pages
The decision · Power BI and Fabric, in depth · Genie and metric views