Sergei Semenkov

Posted on Nov 5 Edited on Nov 5 AI-assisted

Power BI on Databricks Metric Views: a live connection, with the measures kept in Databricks

#powerbi #databricks #semanticlayer #dataengineering

As of November 2026: eMondrian Community 2026.11.05.1 (server 10.0.74, modules 0.0.0.89). You need Power BI Desktop, a Databricks SQL warehouse with a metric view and, for the Power BI Service, a Windows machine for the on-premises data gateway.

Databricks Metric Views let you define a metric once, in Unity Catalog, and query it from SQL, notebooks, dashboards and Genie. Power BI is the tool most teams still report in, and right now it can't use those definitions properly. I connected the two through a semantic layer, live, and checked the totals that usually go wrong.

Disclosure: I'm the author of eMondrian, the open-source semantic layer used here.

In short: eMondrian connects Power BI to Databricks metric views live. Power BI Desktop and the Power BI Service connect to eMondrian as to Analysis Services and send DAX. eMondrian turns each query into flat MEASURE() SQL that the Databricks SQL warehouse runs against the metric view. The measures stay defined once, in the metric view: no Import, no hand-written Native query and no copy of the formulas in DAX. Distinct counts and ratios show correct totals at every level, because Databricks computes each total from the view's own definition.

Why keep the metrics in Databricks

A metric view is a YAML definition next to the data: the source table, joins, dimensions and measures such as COUNT(DISTINCT customer_id) or SUM(revenue) / SUM(orders).

Keeping metrics there has practical advantages:

One definition for every consumer. SQL users, notebooks, ML pipelines, AI/BI dashboards and Genie all read the same measure. Nobody re-types it in DAX.
Changes in one place. Rename a column or change a formula, and every consumer gets it. There are no copies in individual .pbix files to fix.
The grain is handled by the engine. MEASURE(unique_customers) grouped by month is computed per month, and grouped by year it's computed per year. A distinct count or a ratio is never added up from smaller pieces.
Unity Catalog is where the data team works already: lineage, permissions and reviews of the definitions are in the same place as the tables.

What goes wrong with Power BI today

Querying a metric view needs special SQL: each measure wrapped in MEASURE(), an explicit GROUP BY, no SELECT *, no joins in the query. Power BI doesn't generate that.

BI compatibility mode is gone. It used to be an option in the Databricks connector: Databricks rewrote plain SUM into the measure definitions, and even then COUNT DISTINCT and MEDIAN failed or gave wrong results. Databricks now notes that the option "is no longer available in the Power BI connector".
What's left is Native query or a wrapper view. With Native query you hand-write MEASURE() SQL for each table in DirectQuery. That doesn't scale beyond a few visuals. A standard view that wraps the metric view fixes the grain in advance, so Power BI adds its rows up like an import.
Import mode re-aggregates. Load a metric view at month grain and let Power BI sum it, and the yearly total of a distinct count is wrong. People then rebuild the ratio in DAX, which is the duplication metric views were meant to remove.
Converting is copying. Tools such as Tabular Editor's Semantic Bridge translate a metric view into a Tabular model. That works for many measures, but the definitions now live in two places again.

The Fabric idea "Enable native Power BI integration with Databricks Metric View" has more than 800 votes, and its comments are mostly about these points.

How it works: a live model in between

eMondrian holds a model whose cube points at the metric view. To Power BI it looks like Analysis Services: Power BI connects live and sends DAX. eMondrian turns each query into flat SQL for the SQL warehouse:

SELECT `order_year`, MEASURE(`mv`.`unique_customers`)
FROM `catalog`.`schema`.`orders_mv` AS `mv`
WHERE `order_status` IN ('Open', 'Processing')
GROUP BY `order_year`

No nesting, no joins, no subqueries, no GROUPING SETS: the rules of metric views are kept. The measure is computed by Databricks, from its own definition. eMondrian doesn't hold a copy of the formula.

Excel PivotTables connect to the same model over the same address (MDX), and AI agents over MCP.

Measure types that fit metric views

In an eMondrian schema each measure says how it may be rolled up. Two settings matter for metric views.

Additive measures (sums, counts of rows). Declared with aggregator="sum". eMondrian may cache a result and add months into a year itself, which saves trips to the warehouse.

Non-additive measures (distinct counts, ratios, averages, medians). Declared as non-additive (aggregator="distinct-count") and computed by the database (aggregatorImplementation="MEASURE"). eMondrian never adds them up. Every level a visual shows, every subtotal and the grand total is asked from Databricks with its own GROUP BY.

<Measure name="Unique Customers" column="unique_customers"
         aggregator="distinct-count" aggregatorImplementation="MEASURE"/>
<Measure name="Order Count" column="order_count"
         aggregator="sum" aggregatorImplementation="MEASURE"/>

Which one to use depends only on the measure's expression in the metric view:

Expression in the metric view	aggregator
a single `SUM(x)`, `COUNT(x)` or `COUNT_IF(cond)`, with or without `FILTER (WHERE …)`	`sum`
a single `MIN(x)` / `MAX(x)`	`min` / `max`
`COUNT(DISTINCT x)`, a ratio such as `SUM(a) / SUM(b)`, `SUM(a) * 2`, `AVG`, `MEDIAN`, `PERCENTILE`, `APPROX_*`, window measures, references to other measures	`distinct-count` (never rolled up)

When in doubt, choose non-additive. A wrong sum gives wrong totals without any error. A wrong non-additive setting only costs an extra query.

The schema also needs the size of each level (how many members it has). Without it the engine would send COUNT(DISTINCT …) to the view to find out. With sizes in place every statement stays a plain SELECT … MEASURE() … GROUP BY.

Filters work the same way: with two order statuses selected, the total is the metric view's own figure for those two statuses, not the sum of two rows. An Excel PivotTable gets the same totals, including when you filter the pivot and Excel sends a subselect.

The trade-off: a visual with several non-additive measures may need several SQL statements, and each one runs on the warehouse.

Time intelligence lives in the model

On a live connection to a multidimensional model, Power BI doesn't let you write report measures, so TOTALYTD or SAMEPERIODLASTYEAR aren't an option. Year-to-date, same period last year, previous year, year-over-year and a rolling three months are calculated measures in the eMondrian schema instead. The MCP tool add_time_measures writes them for the measures you name; the cube authoring guide explains the templates.

They work for non-additive measures too. Year-to-date unique customers for December is one query over months 1–12, and the result is the metric view's own figure for the year, not the sum of twelve months:

SELECT MEASURE(`mv`.`unique_customers`)
FROM `catalog`.`schema`.`orders_mv` AS `mv`
WHERE `order_year` = 1996 AND `order_month` IN (1, 2, …, 12)

On a test setup that applies the metric view query rules, these measures matched hand-written MEASURE() SQL in both the Excel and the Power BI query shapes, including a year picked in a Power BI slicer and a rolling three months across the new year. Every query was a single flat MEASURE() statement. In Power BI Desktop, checked on eMondrian's FoodMart sample, a year chosen in a slicer keeps all twelve months, and the total row shows the year's figures.

Let an agent write the model

You don't write this schema by hand. eMondrian's MCP server includes a cube authoring guide with a section on metric views: the rules above, the time hierarchy, the metric view SQL rules, and a complete example.

The agent needs two things from Databricks:

the view's definition, DESCRIBE TABLE EXTENDED catalog.schema.orders_mv AS JSON, which marks each field as a measure or a dimension and includes the YAML;
the number of distinct values of each dimension field, a GROUP BY without measures.

From those it writes the schema, checks it and saves it.

This is deliberate: there is no fixed converter from metric view to cube. A converter would make the same choices for every view, while a real model needs decisions that depend on the data and on how people report: which fields form a hierarchy, what to name things, which measures to hide, which roles to add. The agent makes those choices with you, following the guide, and you review the result like any other change.

In my test a new agent session had only the guide, the JSON and the counts. It produced a schema whose figures matched the hand-written one cell for cell: all measures by year and by month, the IN filter case, and status by priority. It chose sum for the two counts and the non-additive setting for the distinct count and the ratio.

The agent runs these two queries with its own Databricks access, for example the Databricks MCP server, and under its own permissions. You can also run them in the SQL editor and paste the results. eMondrian's MCP tools only work with the model (MDX and DAX); reading the source is left to Databricks' own tools.

Try it yourself

You need Claude Code, Docker, Power BI Desktop on Windows, and a Databricks workspace with a metric view, a SQL warehouse and a personal access token.

1. Start eMondrian Community.

git clone https://github.com/eMondrian/emondrian-community
cd emondrian-community
cp .env.example .env
./setup.sh

To change the port, edit EMONDRIAN_PORT in the copied .env; setup.sh lists any settings your .env leaves out.

2. Add a licence key. The Power BI (DAX) and MCP modules need one. Request the free 30-day key at emondrian.com/license and put the .lic file into modules/. No restart is needed.

3. Connect eMondrian to Databricks. The Databricks JDBC driver isn't in the image: download it from Databricks and mount it into the server's lib folder. The connection settings go into an env file, so the token never appears in a schema or in datasources.xml:

# docker-compose.override.yml
services:
  eMondrian:
    env_file: databricks.env
    volumes:
      - ./drivers/databricks-jdbc.jar:/usr/local/tomcat/lib/databricks-jdbc.jar:ro

Use the name of the jar you downloaded in place of databricks-jdbc.jar.

# databricks.env (keep it out of Git)
EMONDRIAN_DATABRICKS_HOST=<workspace host>
EMONDRIAN_DATABRICKS_HTTP_PATH=<SQL warehouse HTTP path>
EMONDRIAN_DATABRICKS_CATALOG=<catalog>
EMONDRIAN_DATABRICKS_TOKEN=<token>

Then docker compose up -d.

4. Give Claude Code both servers.

claude mcp add --transport http emondrian http://localhost/emondrian/mcp
claude mcp add --transport http databricks https://<workspace host>/api/2.0/mcp/sql --header "Authorization: Bearer $DATABRICKS_TOKEN"

The Databricks SQL server has a tool that runs any statement (execute_sql) and a read-only one (execute_sql_read_only). Allow only the read-only tool and poll_sql_result; the agent needs nothing else. DESCRIBE, SHOW CREATE TABLE and SELECT … MEASURE() … GROUP BY all pass through it.

5. Ask for the cube.

Two MCP servers are connected: "databricks" and "emondrian". In Databricks there is a metric view <catalog>.<schema>.orders_mv. Build an eMondrian cube over it: follow the eMondrian cube authoring guide (it has a section on metric views), read what you need about the view through the databricks tools, write the schema, validate it, and register it in eMondrian as a new catalog named "Orders". The server already has the Databricks connection as environment variables; never write the host, path or token into anything.

The guide is long; Claude Code saves it to a file and reads the metric view section from there.

6. Ask it to check its own work.

Compare at least three figures from the "Orders" cube with MEASURE() SQL on the metric view, including the distinct-count measure for one whole year against the sum of that year's months: they must differ, and the cube must match the year. Then add time measures with add_time_measures (preview first, then save) for one additive and one distinct-count measure, and check one year-to-date value against SQL. Show the comparisons as a table.

7. Ask for the Power BI project.

Create a Power BI Desktop project for the "Orders" cube with the eMondrian report tools: one page with a table of months showing the main measures and their year-to-date measures, and a slicer on the year. Connect it to http://<eMondrian machine>/xmla. Write the files into ./orders-report and pack the folder into orders-report.zip.

Use the address by which Power BI Desktop's machine reaches eMondrian: the server's name or IP, or localhost if Desktop runs on the same machine. If you leave it out, the agent asks for it. Unzip the archive and open orders-report.pbip in Power BI Desktop. It connects live, with no data import.

In my run each of the three prompts took one to three minutes. The cube figures, the year-to-date measures and every value in the Desktop table matched MEASURE() SQL on the view, and the year total of the distinct count was the view's own figure for the year, not the sum of its months.

Deployment: two VMs, one for the gateway and one for eMondrian

For the Power BI Service you need the on-premises data gateway in standard mode. It uses its built-in Analysis Services connection to eMondrian, so nothing Databricks-specific is installed on it: no ODBC driver, no custom connector.

I recommend two separate machines:

	Gateway VM	eMondrian VM
OS	Windows (the gateway runs only on Windows)	Linux with Docker
Talks to	Power BI Service (outbound) and eMondrian	the gateway and the SQL warehouse
Updates	monthly gateway releases, Windows restarts	eMondrian releases

Why not one machine:

Different platforms. The gateway is a Windows service; eMondrian runs as Linux containers. On one box you end up with Docker on Windows, which is the less common setup to run in production.
Restarts don't take each other down. Gateway updates and Windows patches restart the gateway VM. Excel users and AI agents connected straight to eMondrian keep working.
Resources. Both do real work per report view: the gateway relays every query, and eMondrian builds SQL and holds the cache. Separate VMs mean neither starves the other, and each can be sized and scaled on its own (a gateway cluster, a larger eMondrian VM).
Credentials stay apart. The warehouse token lives only on the eMondrian VM. The gateway only knows the XMLA address.
Placement. Put both in the same virtual network and region as the SQL warehouse. The XMLA endpoint then doesn't need to be reachable from the internet: only the gateway, Excel users on the network and agents use it.

Setting up the Service is the same as for any eMondrian source: a gateway connection of type Analysis Services, server https://<emondrian-address>/xmla, database = the catalog name, Windows authentication; then Publish from Desktop. In the test the report and pinned dashboard tiles refreshed through the gateway, and every figure equalled the metric view's own query.

Plan the warehouse for live use: every click in the report runs on the SQL warehouse, so choose its size and set auto-stop with that in mind.

FAQ

Can Power BI connect live to a Databricks metric view?
Yes, through eMondrian. Power BI connects live to eMondrian as to Analysis Services, and eMondrian queries the metric view with MEASURE() SQL. Power BI's own Databricks connector no longer offers this: BI compatibility mode is gone, and what remains is hand-written Native query SQL.

Does it work in the Power BI Service, not only in Desktop?
Yes. The on-premises data gateway uses its built-in Analysis Services connection to eMondrian, with no Databricks driver on the gateway. Reports and pinned dashboard tiles refresh through it.

Are totals of distinct counts and ratios correct?
Yes. eMondrian never adds up non-additive measures. Every subtotal and the grand total is a separate MEASURE() query, so Databricks computes it from the view's definition.

Do I have to rewrite the metric view's measures in DAX?
No. The measure formulas stay only in Databricks. The eMondrian schema names the measures and says whether they may be rolled up, and an AI agent can write that schema from the metric view.

Does Excel work too?
Yes. Excel PivotTables connect to the same eMondrian model over XMLA and get the same figures.

What does it cost?
eMondrian Community is open source. The Power BI (DAX) and MCP modules need a licence key; a free 30-day key is available. Every report query runs on your SQL warehouse.

Would this help you?

I'm looking for teams that report in Power BI on Databricks and have hit the problems above. If you've rebuilt metric-view measures in DAX, or your totals stopped matching, I'd like to hear how you handle it now. Leave a comment.

eMondrian: Demo · GitHub · Docs

Sources:

DEV Community