DEV Community: Sergei Semenkov

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

Sergei Semenkov — Thu, 05 Nov 2026 10:52:07 +0000

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:

Power BI Service on ClickHouse, without an ODBC driver on your gateway

Sergei Semenkov — Tue, 03 Nov 2026 13:09:25 +0000

As of November 2026. You need Power BI Desktop, a Power BI account and a Windows machine for the on-premises data gateway.

Last week Claude built a Power BI report on 182 million flights in ClickHouse. It ran in Power BI Desktop. The next question is the one every BI team asks: can the rest of the team open it in the Power BI Service?

Here's how: publish the same live report through the standard gateway, with nothing ClickHouse-specific installed on it.

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

How it works: a semantic model in between

eMondrian sits between Power BI and ClickHouse. It holds the model, with cubes, dimensions and measures such as On-Time Rate defined once. To Power BI it looks like Analysis Services. Power BI connects to it live and sends DAX. eMondrian turns that into SQL for ClickHouse.

For the Service, that means the gateway uses a connection type it already has built in: Analysis Services. There is no ClickHouse driver or custom connector on the gateway. The gateway only needs to reach eMondrian's HTTPS address.

	Official ClickHouse connector	Through eMondrian
On the gateway machine	ClickHouse ODBC driver	Nothing extra: built-in Analysis Services
How Power BI queries	DirectQuery: Power BI writes SQL against the tables	Live connection to a semantic model
Where measures live	In each .pbix	Once, in the model; the same in Power BI, Excel and AI agents

What you need

A report with a live connection to eMondrian. Use the one from the previous tutorial. Or connect Power BI Desktop yourself: Get data → Analysis Services, server https://demo.emondrian.com/xmla, database OnTime, Connect live.
The on-premises data gateway in standard mode (not personal mode), on a Windows machine that can reach the eMondrian address.
A Power BI account. On a Free licence you can view your own report in My workspace. To share it with your team, you need Pro or Premium Per User.

I used the public eMondrian demo, so there was no server to install. Its data is read-only: 182 million US flights in ClickHouse.

Step 1. Add the connection to the gateway

Install the gateway and sign in, if you haven't already. Then, in the Power BI Service: Settings → Manage connections and gateways → New → On-premises.

Gateway: your gateway
Connection type: Analysis Services
Server: https://demo.emondrian.com/xmla (or your eMondrian address)
Database: OnTime
Authentication: Windows, with an account the gateway can use
Privacy level: Organizational

Save. The gateway needs a Windows account to make the connection; the demo doesn't check it.

Step 2. Publish from Power BI Desktop

Open the report in Desktop and click Publish, then choose a workspace. The Service matches the report to the gateway connection by server and database. If it doesn't, check Semantic model settings → Gateway and cloud connections.

Step 3. Open it in the browser

Every visual queries the model live through the gateway. Nothing is imported into Power BI, and there's no scheduled data refresh to set up for the report itself.

I clicked through it and compared each number with a direct MDX query on the server. They matched:

2025: 7,001,619 flights, 76.34% on time, the same as in Desktop and in the Claude dashboard on the same model;
carrier DL (Delta) selected: 1,026,332 flights, 79.12%;
the tooltip for Delta in September: 87.36%;
clicking American (AA) on the carriers bar cross-filters the page: 973,653 flights, 72.70%;
cancellation rate by day of week for Delta matches the previous article: Friday 1.73% is the worst day, Wednesday 0.49% the best.

Speed: the eight-visual page opened in 3.6–4.6 seconds in the Service through the gateway. In Desktop, connected directly, its slowest visual took 1.7–3.6 seconds. Those were warm runs on a shared public demo, so treat them as a rough idea, not a benchmark.

Step 4. Pin it to a dashboard

Pin a few visuals to a dashboard as usual. The tiles show live data and refresh automatically through the same gateway connection. This requires eMondrian 10.0.62 or later.

What about Excel?

Analyze in Excel from the Service needs a Pro or PPU licence. I didn't test it.

You don't need the Service for Excel, though. Excel connects to eMondrian directly: Data → Get Data → From Database → From Analysis Services, with the same address. A PivotTable there uses the same model and the same measures as the report.

Doing this on your own data

Run eMondrian Community next to your database: git clone https://github.com/eMondrian/emondrian-community and ./setup.sh. It works with ClickHouse, PostgreSQL, MySQL or any JDBC source.
Describe your cubes and measures, or ask Claude to draft the schema over MCP.
Point the gateway's Analysis Services connection at your eMondrian address and publish.

The Power BI (DAX) module needs a key; a free 30-day evaluation key is at emondrian.com/license.

eMondrian: Demo · GitHub · Docs

Sources:

Have you put ClickHouse behind the Power BI Service? What broke? Leave a comment.

Claude builds a Power BI report on a live model in ClickHouse

Sergei Semenkov — Tue, 27 Oct 2026 13:58:19 +0000

As of October 2026. You need Windows with Power BI Desktop and Claude Code. Nothing else to install.

Last week I showed Claude building a live dashboard on 182 million flights. The most common question afterwards: can it do that in Power BI? That's where most BI teams actually live.

It can. In this tutorial an AI agent builds a real Power BI project, a page with slicers, KPI cards and charts, with a live connection to a semantic model on ClickHouse. Then you look at it in Power BI Desktop, ask for a change, and look again. Nothing is imported into Power BI, and there's no copy of the data.

It runs against the public eMondrian demo, so you don't install a server. The agent took about three and a half minutes for the first version and two and a half for the change.

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

What you'll build

Year and carrier slicers
KPI cards: flights and on-time rate for the selection
On-time rate by year, and by month for the selected year
The largest carriers by flights
After the change: cancellation rate by day of week

The agent wrote every page and visual. I typed two prompts and didn't touch the files.

How it works, in one paragraph

Power BI Desktop can save a report as a project (PBIP): a folder of JSON files, one per page and per visual. An agent can write those files. The model lives in eMondrian, an open semantic layer on ClickHouse, where measures like On-Time Rate are defined once. Power BI connects to it live through its Analysis Services connector and sends DAX, the same way it talks to Analysis Services. The agent never sees raw tables or rows: it reads the model, generates the report files, and validates them before it hands them over.

Step 1. Connect Claude Code to the demo (1 minute)

In an empty folder:

claude mcp add --scope project --transport http emondrian https://demo.emondrian.com/emondrian/mcp

This writes a .mcp.json into the folder; Claude Code asks you once to allow the server when it starts. The demo is public and its data is read-only: 182 million US flights in ClickHouse, behind a model called OnTime.

Step 2. Ask for the first version (3½ minutes)

Start claude in that folder and paste:

Using the eMondrian MCP server, build a Power BI report on the OnTime catalog: a page "On-time performance" with year and carrier slicers, KPI cards for flights and on-time rate, on-time rate by year (line), by month for the selected year, and the largest carriers (bar). Live connection to the model, no import. Validate the report before you finish. Power BI should connect live to https://demo.emondrian.com/xmla.

I ran it in a brand-new Claude Code session; the folder held nothing but the MCP configuration. The agent:

read the model: catalogs, the cube, and the field names Power BI will use. That's how it found On-Time Rate already defined instead of inventing its own formula;
generated the project: page, seven visuals, layout, connection;
validated it against Power BI's schemas and the model before writing anything to disk;
wrote the files and checked the 2025 numbers with one query against the server.

3 minutes 28 seconds, 30 tool calls. One hiccup: on Windows, its first attempt to write all files in one long shell command failed. It noticed, checked that nothing had been written, and wrote the 18 files one by one.

The result is an ordinary Power BI project folder: a .pbip file and a definition folder with one JSON file per page and visual. Every file is exactly what the generator produced; the agent didn't hand-edit anything.

Step 3. Open it in Power BI Desktop

Double-click the .pbip file. No preview settings to switch on, no sign-in: Power BI connects live to the demo, and every visual sends a query when it renders.

A few things the generator decided, the way a person would:

The year slicer opens on 2025, the latest complete year. 2026 is in the list, but it only has seven months.
The cards show the exact number, 7,001,619, not "7M".
The year line ignores the year slicer, so it keeps all years instead of collapsing to a single point. The carriers bar likewise ignores the carrier slicer, so you still see the ranking.

Now pick DL (Delta) in the carrier slicer:

The numbers are the same as in last week's Claude dashboard on the same model: 7,001,619 flights in 2025, on-time rate 76.34%; Delta 1,026,332 flights, 79.12%. That's the point of a semantic layer: Excel, Power BI and the AI agent get the same measure, defined once.

Step 4. Ask for a change (2½ minutes)

Leave Power BI Desktop open, and in the same Claude Code session:

Add a chart with cancellation rate by day of week for the selected year and carrier.

The agent noticed that Power BI Desktop was running. Before writing, it asked me to save any edits there, because Desktop's next save would overwrite its files. Then it added the chart, laid the page out again in two rows so the year line keeps its width, and validated the report. 2 minutes 29 seconds including my answer, 11 tool calls, no errors. It changed the positions of three charts and added one file; nothing else.

Desktop notices the change by itself:

Step 5. Look at the change

Click Apply external changes. The page reloads with the new chart, without reopening the report:

In 2025, across all carriers, Sunday had the highest cancellation rate, 1.73%, and Tuesday the lowest, 1.18%. Delta was different: Friday was its worst day, 1.73%, and Wednesday the best, 0.49%.

Doing this on your own data

Run eMondrian Community next to your database: git clone https://github.com/eMondrian/emondrian-community and ./setup.sh. It works with ClickHouse, PostgreSQL, MySQL or any JDBC source.
Describe your cubes and measures. You can ask Claude to draft the schema over MCP; it validates and saves it.
Ask for a report, exactly as above, with your server's address instead of the demo's. The agent works from the model, so it never needs your raw tables.

The Power BI and AI modules need a key; a free 30-day evaluation key is at emondrian.com/license. Community's MCP server has no sign-in, so keep it on your own machine or a trusted network.

eMondrian: Demo · GitHub · Docs

Sources: Power BI Desktop projects (PBIP) · PBIR report format

Questions, or a report you built this way? Leave a comment.

Claude Dashboards on ClickHouse, through a semantic layer: a 10-minute tutorial

Sergei Semenkov — Fri, 23 Oct 2026 12:00:00 +0000

As of October 2026. You need a paid Claude plan (Pro, Max, Team or Enterprise); on Enterprise an owner has to turn Dashboards on.

On Tuesday I wrote about where the data goes when you use Claude Dashboards. Several people asked for the practical part: how do you actually get a live dashboard on a big ClickHouse table, and what does Claude do along the way?

Here it is, on the public eMondrian demo: 182 million US flights in ClickHouse, behind a semantic layer that defines measures like On-Time Rate once. Nothing to install. It took me about ten minutes, most of it reading what Claude was doing.

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

Why not connect Claude to ClickHouse directly? You can: Claude Dashboards has its own ClickHouse connector. Then Claude writes SQL against your tables, and every table the connection can read is in play. Here the connection goes through a semantic layer instead. Claude sees cubes and measures that are already defined, such as On-Time Rate, writes MDX against them, and the server generates the SQL. You get the same numbers as in Excel and Power BI, and Claude reaches less of your database. More on that trade-off in Is your data safe with Claude Dashboards?

What you'll build

Year and carrier slicers
KPI cards: on-time rate and flights for the selection
On-time rate by year (1987–2026) and by month for the selected year
The largest carriers, with on-time rate on each bar
A panel with the exact MDX query behind each chart

Claude designs the page on every run, so yours will look a little different. The numbers won't.

Step 1. Add the connector (1 minute)

In claude.ai open Customize → Connectors, click + Add → Add custom connector and fill in:

Name: eMondrian demo
MCP server URL: https://demo.emondrian.com/emondrian/mcp

Click Continue. Claude checks the server; the demo needs no sign-in, so the connector is added right away. The demo's data is read-only and public, so that's fine here. For your own data it isn't — more on that at the end.

Open the connector to see its tools. For each one you choose whether Claude may use it always, only with your approval, or never:

Step 2. Start a dashboard (30 seconds)

Open Artifacts and pick Dashboard (Beta). You get an empty dashboard and a chat next to it.

Step 3. Ask for it (1 minute)

Paste this prompt:

Using the eMondrian demo connector, build a dashboard for the OnTime cube: on-time rate by year, by month for 2025, and the largest carriers, with year and carrier slicers. Add a panel that shows the MDX each chart sends.

Be specific about measures and the cube. "Build me a flights dashboard" works too, but you'll spend the saved minute on corrections.

Step 4. Watch and approve (5 minutes)

Claude first reads the model, then tests each query before it writes the page. Every tool call asks for your approval, and the approval shows what will run:

List catalogs — which models and cubes the server has.
Get schema — dimensions, measures and their formulas. This is how Claude learns that On-Time Rate already exists and how it's defined, instead of inventing its own.
Execute query — a few MDX test queries, one per chart shape.

Use Allow once if you want to read each query. Claude tests about ten of them, and the finished page asks again for each chart when it first loads, so once you've seen enough, click Always allow for Execute query. Without waiting on me, the build takes well under ten minutes.

A detail I liked: Claude checked how the slicers would substitute values into the queries, tested them with fixed values, and told me which part it couldn't test from the chat.

When the page opens for the first time, it shows a consent dialog, This artifact uses connectors, listing the eMondrian demo connector with a switch. Every viewer sees it before the page can query anything. The label says "Can read and make changes"; in practice the page calls one tool, Execute query, which only reads data.

Step 5. Use it

The dashboard is live: each chart is a stored query, not a picture of numbers. Pick another year or a carrier (or click a carrier's bar) and the charts re-query the server. The screenshot at the top shows Delta selected.

The numbers come from the semantic layer, so they match what Excel and Power BI show for the same model: 7,001,619 flights in 2025 with an on-time rate of 76.3%; for Delta, 1,026,332 flights and 79.1%.

Step 6. Look at the MDX

Scroll to the MDX panel. Every chart shows the query it sends, with the current slicer values filled in:

SELECT
  {[Measures].[Flights], [Measures].[On-Time Rate]} ON COLUMNS,
  NON EMPTY [Time].[Year].Members ON ROWS
FROM [OnTime (attributes)]
WHERE (StrToMember("[Airline].[" || 'DL' || "]"))

The answer is 30 rows: one per year Delta flew. The month chart is 12 rows.

This is the part I'd show a colleague who asks "where do these numbers come from". It's also what makes the security story simple: the page stores queries, and each one asks the server for a handful of aggregated rows.

Step 7. Change it by asking

Ask in the chat, for example:

Add a chart with cancellation rate by day of week for the selected year and carrier.

Claude tested the new query, found the Cancellation Rate measure already defined in the model instead of computing its own, and added the chart in under a minute.

Step 8. Share it — with a catch

Dashboards start private. When you share one, each viewer runs the queries through their own connector, so they need the eMondrian demo connector too. A dashboard that uses connectors can't be opened with "Anyone with the link".

What actually happened (30-second version)

Claude saw the model — cubes, measures, formulas. No raw rows.
The page stores queries. Every time someone opens it, the queries go from the browser through Anthropic's connector service to the eMondrian server, and only aggregates come back (a year by month is 12 rows).
So the numbers pass through Anthropic's cloud. On the public demo that doesn't matter. For your own data, read Is your data safe with Claude Dashboards? first.

Doing this on your own data

Run eMondrian Community next to your database: git clone https://github.com/eMondrian/emondrian-community and ./setup.sh. It ships with ClickHouse and an OnTime sample.
Define your cubes and measures. You can ask Claude Code to draft the schema over MCP; it validates and saves it for you.
Connect Claude to your server. Two honest caveats:
- A remote connector (as above) calls your server from Anthropic's cloud, so the server has to be reachable from the internet. Community's MCP has no sign-in — don't expose it with real data.
- For local testing, use Claude Desktop or Claude Code with a local MCP server instead.

The Power BI and AI modules need a key; a free 30-day evaluation key is at emondrian.com/license.

eMondrian: Demo · GitHub · Docs

Sources: Get started with Claude Dashboards · Artifacts admin guide

Questions, or a dashboard you built on the demo? Leave a comment.

Is your data safe with Claude Dashboards?

Sergei Semenkov — Tue, 20 Oct 2026 07:30:49 +0000

What Claude sees, where the data goes, and how a semantic layer on ClickHouse limits it. As of October 2026.

With Claude Dashboards (beta since October 8, 2026), Claude builds a live dashboard for you: you ask a question, it writes the page, and the page keeps querying your data every time someone opens it. I built several of these on an eMondrian semantic layer over 182 million US flight records in ClickHouse, and the first question everyone asked me was the obvious one: where does the data go?

Short answer: Claude sees your model (table names, measures, formulas), not your rows. Every time the dashboard opens, its queries go through Anthropic's cloud to your semantic layer server, and the aggregated results come back the same way. Whether that's acceptable depends on two things you control: which Claude plan you're on, and where your server sits.

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

1. What actually moves

There are two different flows, and it helps to keep them apart.

Metadata: once, when Claude builds the dashboard

To write a dashboard, Claude needs to know what it can ask for. It calls two tools on the eMondrian MCP server:

list_catalogs returns the models and their cubes:

  OnTime: OnTime (attributes)
  FoodMart: Sales, Warehouse, Store, HR, Sales Ragged, Warehouse and Sales

get_schema returns the model definition: dimensions (Time, Airline, Day of Week, Distance Group), measures, and their formulas:

  <CalculatedMember name="On-Time Rate" dimension="Measures">
    <Formula>1 - ([Measures].[Late Arrivals] + [Measures].[Cancelled]
                + [Measures].[Diverted]) / [Measures].[Flights]</Formula>
  </CalculatedMember>

That's metadata. No data rows are involved. One thing to know, though: the schema includes the SQL behind the cube, so table and column names in your database become visible to Claude.

Claude then writes MDX queries into the page, for example "On-Time Rate and Cancellation Rate for each month of 2025, for Delta". The page stores queries, not numbers.

Data: every time someone opens the dashboard

When someone opens the page, it runs its stored queries through that person's connector. The request goes from the browser to Anthropic's connector service, then to your eMondrian server. eMondrian turns MDX into SQL, ClickHouse does the aggregation, and the result goes back the same way.

What comes back is small. The monthly chart for one year is 12 rows × 3 measures, not the 7 million flights behind them. Raw rows never leave ClickHouse, because the semantic layer only answers questions about the measures it defines.

A Claude Dashboard built on the eMondrian demo with the prompt from "Try it" below. Every chart is a stored MDX query; the month chart comes back as 12 rows.

Refreshing the dashboard runs the stored queries again. It doesn't ask Claude to write anything new.

So, is it safe?

The question isn't whether Claude sees your rows. It's where your numbers travel:

The data path goes through Anthropic's cloud. This is the main point. Every time anyone opens the dashboard, its queries and their results pass through Anthropic's connector service. The results are small and aggregated, but they are your data, so what happens to them is governed by your agreement with Anthropic (section 2).
Your server has to be reachable from Anthropic's cloud. That follows from the first point, and it brings its own requirement: a server reachable from the internet needs authentication, or anyone who finds the URL can query it (section 3).

2. What Anthropic commits to

Everything below comes from Anthropic's own documentation as of October 2026. Terms change, so check the linked pages before you rely on them.

Connections come from Anthropic's cloud, not from your laptop. The Help Center states that Claude connects to a remote MCP server "from Anthropic's cloud infrastructure, rather than from your local device", and that the server "must be reachable over the public internet from Anthropic's IP ranges". This applies in the browser and in Claude Desktop alike.

You choose how people sign in to your connector. There are three modes: sign in up front, sign in when the server asks, or no sign-in. The documentation is explicit about the last one: with no sign-in, "anyone with access to the server URL can use the connector". Connector credentials are stored encrypted.

Custom connectors are unverified. Anthropic warns that custom connectors connect Claude to services "that have not been verified by Anthropic" and that a malicious server could try prompt injection. That's a reason to run your own server rather than someone else's.

No training on your content on commercial plans. Anthropic's Commercial Terms, which cover the API, Claude Team and Claude Enterprise, say: "Anthropic may not train models on Customer Content from Services." Personal plans (Free, Pro, Max) are under the consumer terms instead, where it depends on your privacy settings. If your company's data is involved, use a Team or Enterprise plan.

Retention is set by your agreement, not by the terms page. The Commercial Terms bring in a Data Processing Addendum and treat your content as your confidential information. Retention periods aren't stated on the terms page itself. If retention matters to you, ask Anthropic for the numbers for your plan in writing.

Regulated data. Anthropic's admin guide says organizations set up for HIPAA (the US health privacy law) can turn Dashboards on, but Dashboards aren't covered by the BAA (Business Associate Agreement, the contract under which Anthropic may handle patient data). So keep PHI (protected health information, i.e. patient data) out of them.

Private networks: MCP tunnels (Claude Enterprise, research preview). If your server must not be reachable from the internet, Claude Enterprise can reach it through an MCP tunnel. You run a small stack inside your network, and it opens an outbound-only connection, so you need no inbound firewall rules. Cloudflare carries the traffic encrypted and can't read it. Anthropic's backend does terminate it, so Anthropic still processes the content. The tunnel requires MCP servers that use Streamable HTTP and require OAuth.

Dashboards themselves. Claude Dashboards is in beta on Pro, Max, Team and Enterprise plans; on Enterprise it stays off until an owner turns it on. While Claude builds a dashboard, claude.ai asks you to approve each tool call, and the approval shows the exact MDX query it's about to run. The finished dashboard stores its queries and runs them through each viewer's own connector whenever it's opened, refreshed or filtered, so a viewer needs access to that connector. Dashboards start private to you. Sharing outside your organization is an admin setting, and a dashboard that uses connected apps can't be shared with "Anyone with the link". Admins can turn connectors in artifacts on or off, but they can't restrict which connectors a dashboard calls. So the real boundary is the connector itself: what your server lets it see.

3. What you control

This is where the real decisions are, and where a semantic layer earns its place.

What the agent can see at all. Claude Dashboards can also connect straight to a warehouse, ClickHouse included. Then Claude writes SQL against your tables, and every table the connection can read is in play. Through a semantic layer it's different: Claude doesn't get a SQL console to your warehouse. It gets the cubes, measures and dimensions you defined, and it writes MDX against them. A table that isn't in a cube isn't reachable through MCP, and an agent can't invent its own "revenue" formula when one is already defined. One caveat: if the server allows the agent to save schemas, the agent could define a new cube over another table. Read-only mode closes that door.

What it can do. On the public demo, data and schemas are read-only. The tools that change schemas or register catalogs are switched off.

Who can see which numbers. Mondrian roles can restrict access to cubes, dimension members and measures. To tie those roles to the person who signed in, the MCP server needs OAuth.

The honest part about eMondrian today:

OAuth for MCP is not part of eMondrian Community. Without it, the MCP server has no authentication.
eMondrian Community includes tools that can change schemas. Keep its MCP endpoint on localhost or a trusted network. Don't expose it to the internet with real data: with no sign-in, anyone with the URL could query your cubes.
There is no audit log of agent queries in Community either.
Planning a production setup with real data? Contact us at contact@bisolutions.dev.

Where your server sits. This is the choice that matters most:

Option	Server reachable from	Fits when	What you need
Remote connector	Anthropic's cloud (internet or allowlisted Anthropic IPs)	Your company uses Claude Team or Enterprise and allows aggregated data to go through it	OAuth on the MCP server (not in eMondrian Community)
MCP tunnel	Only your network; outbound connection to Anthropic	Same, but security policy forbids public endpoints	Claude Enterprise with tunnels enabled, Streamable HTTP and OAuth on the server
Local	Only your machine or network	Data must not go to a cloud AI at all	Claude Desktop with a local MCP server, or any MCP client with a self-hosted model. eMondrian's MCP is a standard server and isn't tied to Claude

Try it on the public demo

You can see all of this on the public demo. The data is public and read-only.

In claude.ai, open Customize → Connectors and add a custom connector with the URL https://demo.emondrian.com/emondrian/mcp, then click Continue.
Open Artifacts → Dashboard (Beta) and ask: "Using the eMondrian demo connector, build a dashboard for the OnTime cube: on-time rate by year, by month for 2025, and the largest carriers, with year and carrier slicers. Add a panel that shows the MDX each chart sends."
Approve the tool calls as Claude works; each approval shows the query. It took about five minutes for me.
On the finished dashboard, the MDX panel shows exactly what each chart sends to the server.

Step by step, with screenshots: Claude Dashboards on ClickHouse, through a semantic layer

Sources (official):
Get started with Claude Dashboards ·
Artifacts admin guide ·
Custom connectors using remote MCP ·
Add a connector that isn't in the directory ·
MCP tunnels ·
Anthropic Commercial Terms

eMondrian: Demo · GitHub · Pricing

Questions about your own setup? Leave a comment, or write to contact@bisolutions.dev.

The new eMondrian: Power BI and AI agents on your own semantic model

Sergei Semenkov — Tue, 13 Oct 2026 07:34:31 +0000

In 2021 I wrote here how to load data from Mondrian into Power BI Desktop. The short version of that article was: a live connection isn't possible, so you have to import data with an MDX query.

That's no longer true. eMondrian now connects to Power BI live — and to AI agents too.

TL;DR

Power BI connects live to eMondrian through the DAX module — no data import, no SSAS. Your data stays in ClickHouse or your SQL database.
AI agents connect through MCP — they list catalogs, read the semantic model, run queries and can even draft new cubes for you.
One semantic model, defined once, serves Excel, Power BI and AI agents with the same measures and the same numbers.

You can try all three on the public demo in two minutes, or install Community and evaluate DAX and MCP for 30 days with a free key.

Why this matters

eMondrian is built on Mondrian, the open-source OLAP engine. You describe your business model once — cubes, dimensions, hierarchies, measures like Revenue or On-Time Rate — and eMondrian turns every question into SQL against your database.

Excel has always worked well with this: PivotTables speak MDX over XMLA, the same protocol Microsoft Analysis Services uses.

Power BI was the missing piece. When Power BI connects live to an Analysis Services–style server, it doesn't send MDX — it sends DAX. Classic Mondrian doesn't understand DAX, so Power BI couldn't use Mondrian cubes live. Teams imported data into Power BI and re-created every measure there — exactly the duplication a semantic layer is supposed to remove.

At the same time a new kind of client appeared: AI agents. Pointed at raw tables, an agent has to guess joins and formulas, and two agents (or an agent and a report) easily disagree on what "revenue" means. Pointed at a semantic model, the agent uses the definitions your business already agreed on.

The new eMondrian addresses both.

Power BI: live connection through the DAX module

What you get

In Power BI Desktop: Get data → Analysis Services, server http://<your-server>/xmla, Connect live.
Your cubes appear as a Power BI semantic model: measures, dimensions and hierarchies, ready to drag into visuals.
Every visual sends a DAX query to eMondrian; eMondrian evaluates it against the model and sends SQL to your database. Nothing is imported into Power BI.

The DAX module evaluates the queries Power BI sends on the same engine that serves MDX — so a measure defined once in the schema gives the same result in Excel and in Power BI.

You can see exactly what Power BI sends: in Power BI Desktop open Performance analyzer, refresh a visual and copy its DAX query. A simple example on the FoodMart sample:

EVALUATE ROW("Org_Salary", 'HR'[Org Salary])

Why a live connection instead of import or DirectQuery

No duplicated business logic. Measures live in one place — the eMondrian schema — not in every Power BI file.
No data copies. Data stays in your database; there's no import refresh to schedule.
Same numbers in Excel and Power BI. Both clients query the same model.

Note on Microsoft support. Power BI connects to eMondrian through its Analysis Services connector. Microsoft officially supports only a few third-party semantic models, and eMondrian is not one of them. Microsoft support won't help with this setup — eMondrian Enterprise does.

AI agents through MCP

MCP — the Model Context Protocol — is the standard way AI agents such as Claude connect to tools and data. eMondrian now includes an MCP server.

Tool	What the agent can do
`list_catalogs`	See which catalogs (semantic models) exist
`get_schema`	Read a model: cubes, dimensions, measures and their definitions
`execute_query`	Run MDX or DAX against a catalog
`get_cube_authoring_guide`	Learn how to write eMondrian schemas
`validate_schema`	Check a schema for problems before using it
`save_schema`	Save a new or updated schema
`save_catalog`	Register a new catalog and write its schema file
`read_logs`, `get_properties`	Inspect server logs and settings when something goes wrong

Ask questions in business terms. Connect Claude Code in one line:

claude mcp add --transport sse emondrian http://localhost/emondrian/mcp/sse

Then ask:

Compare the cancellation rate of American Airlines and Delta in January 2022 and explain the difference.

The agent reads the OnTime model, finds the Cancellation Rate measure — already defined as Cancelled / Flights — writes the MDX, runs it on ClickHouse through eMondrian and explains the answer. It doesn't invent its own formula, so its numbers match your Excel and Power BI reports.

Let the agent build your first cube

This is the part I'm most excited about. Describing the first cube has always been the hardest step of an eMondrian evaluation. With MCP, an agent can do most of it:

In catalog MySales, create a cube Sales on the ClickHouse table sales (sale_date, region, product, quantity, amount). Add a Time dimension from sale_date, Region and Product dimensions, measures Amount and Quantity, and Avg Price = Amount / Quantity. Follow the cube authoring guide, validate the schema, then save it.

One model, three clients

The real point isn't Power BI or MCP on their own. It's that one semantic model now serves all three kinds of clients.

	Excel	Power BI	AI agents
Protocol	XMLA / MDX	XMLA / DAX	MCP
Typical use	PivotTables, ad-hoc analysis	Dashboards, reports	Questions in plain language, schema authoring
Measures come from	the eMondrian schema	the eMondrian schema	the eMondrian schema

Define Revenue once. Finance sees it in Excel, management in Power BI, and the AI assistant uses the same definition when someone asks "how did revenue develop last quarter?".

Try it

1. Public demo — two minutes, nothing to install.
Connect Excel or Power BI to https://demo.emondrian.com/xmla and explore the FoodMart and OnTime (ClickHouse) samples. Demo guide →

AI agents can use the demo too, in read-only mode:

claude mcp add --transport sse emondrian-demo https://demo.emondrian.com/emondrian/mcp/sse

2. Community on your machine — about 15 minutes.

git clone https://github.com/eMondrian/emondrian-community.git
cd emondrian-community
./setup.sh          # Windows: powershell -ExecutionPolicy Bypass -File .\windows-setup.ps1

Excel works right away. For Power BI and AI agents, request a free 30-day evaluation key — one key unlocks both DAX and MCP. Quickstart →

3. Your own data.
Connect your ClickHouse (or other JDBC) database, describe a cube — by hand or with an agent — and point your real Power BI reports at it. Need more than 30 days? Ask for an extension.

Licensing at a glance

	Community	Commercial License	Enterprise
Engine, XMLA, MDX, Excel	✓ free, EPL-1.0	✓	✓
DAX module (Power BI)	30-day evaluation	✓	✓
MCP module (AI agents)	30-day evaluation	✓	✓
Support	GitHub issues	Email	Priority, help with your DAX reports
Price	Free	€4,900 per server / year	On request

The engine is open source under EPL-1.0. The DAX and MCP modules ship with Community and are unlocked by a license key. Pricing →

What to expect — honestly

We're a small team, so I'd rather be clear about the current limits:

DAX support for Power BI is in beta. Test your own reports during the evaluation and send us the queries that fail.
Not officially supported by Microsoft. See the note above.
MCP in Community is for local use. The MCP endpoint has no authentication and includes tools that can change schemas and register catalogs. Keep it on localhost or a trusted network. Authentication, per-user access and an audit log of agent queries are planned for Enterprise.
Support hours. Business days, UTC+4. No 24/7.

Planning in Excel with Writeback

Sergei Semenkov — Mon, 05 Sep 2022 18:26:30 +0000

Update 2026: eMondrian has a new home — emondrian.com, docs at docs.emondrian.com, code at github.com/eMondrian/emondrian-community. What's new: The new eMondrian.

Check our newest video on how eMondrian can support you to decentralize your planning and forecasting activities for your organization.

Our example from today is a beverages company which operates in many different regions and has plenty of stores. We are going to quickly show you how you could outsource the initial planning for the sales per product to the store managers.

This video was created by Leandro Aeschlimann. His company Consenso specialises in implementing data analytics tools and data infrastructure.

eMondrian is a free and open source alternative to Analysis Services from Microsoft which you can install on Linux and Windows.
It allows you to build cubes and consolidate the tables of you underlying data sources to a data model and connect to it from various client tools to visualize & analyse your data in a more user friendly way.

Welcome to the free open-source OLAP server project

Sergei Semenkov — Mon, 15 Nov 2021 13:59:06 +0000

Update 2026: eMondrian has a new home — emondrian.com, docs at docs.emondrian.com, code at github.com/eMondrian/emondrian-community. What's new: The new eMondrian.

Welcome to the site of the eMondrian project.

eMondrian is a free open-source OLAP server. It is based on the Mondrian project. OLAP server allows you to represent your database as a multidimensional space with dimensions and measures. It hides the complexity of underlying tables and their relations and allows you to interactively analyze data from multiple perspectives. eMondrian server can run on Windows and Linux operating systems.

eMondrian supports XML for Analysis (XMLA) standard and OLE DB for OLAP at the level that makes it possible to connect to it from client tools such as Microsoft Excel, Power BI, Tableau and many others. The logic of the original Mondrian was changed in order to improve performance of queries specific to these clients and to support clients’ features, for example Excel’s sessions, calculated members and sets.

Any database that has a JDBC driver can be the source for eMondrian. eMondrian server is a Relational OLAP (ROLAP) server that means it always shows real time data from a source. This server runs queries written in the MDX language, reads data from a source database and presents results in a multidimensional format.

The most efficient way is to use column store databases as data sources for eMondrian. For example, ClickHouse could run as a powerful and fast query engine while eMondrian works as a proxy representing data as cubes and executing MDX queries.

A simple way to import data to Power BI from Mondrian

Sergei Semenkov — Sun, 01 Aug 2021 16:22:05 +0000

Update 2026: besides importing with MDX, Power BI can now connect to eMondrian live. See The new eMondrian: Power BI and AI agents on your own semantic model.

In the previous article I described how to import data to Power BI using MDX query. A new version of eMondrain allows you to load data from Mondrian without any query, using only Power BI designer. eMondrain is a version of Mondrian that is modified to support Excel and some other applications as clients.

As an example, we will load data from the Mondrian sample database Foodmart.

In the Power BI Desktop application we create a new report and select the menu Get data - Analysis Services.

In SQL Server Analysis Services database dialog we fill in the Server field as a connection string to the Mondrian server. Pick the Import radio button and push the Ok button.

To try this example you can use an online eMondrian server with a sample database. The server address is https://ssemenkoff.dev/emondrian/xmla.

Navigator window will appear. There you have to pick up columns that you want to see in your report. While you are choosing columns you will see a data preview on the right side of the window. As you finish push the Load button.

Data will be loaded to our report and available columns will appear on the right side of the application.

This way of loading data from Mondrian OLAP server to Power BI is very simple and doesn’t need any knowledge of a query language.

If you have any questions or suggestions, feel free to contact me at siarhei.semiankou@gmail.com.

How to load data from Mondrian to Power BI Desktop

Sergei Semenkov — Fri, 05 Mar 2021 10:22:52 +0000

Update 2026: Power BI can now connect to eMondrian live — no MDX import needed. eMondrian answers the DAX queries Power BI sends, and AI agents can use the same model over MCP. See The new eMondrian: Power BI and AI agents on your own semantic model.

However it’s impossible to explore Mondrian cubes in Power BI Desktop through live connection, there is a way how you can load data from Mondrian by queries.

Let’s load data from the Mondrian sample database Foodmart and create a simple interactive report. This example is using the eMondrain version of Mondrian that is modified to support Excel and some other applications as clients.

First we have to design a MDX query. Following query gets sales amount data with dates and store information.

SELECT
[Measures].[Store Sales] on COLUMNS,
CrossJoin( [Time].[Month].Members, [Store].[Store Name].Members ) on ROWS
FROM [Sales]

Mondrian server can return data in two formats. First format is CellSet data format which is convenient in case of using client controls like Pivot Table.

The second format is tabular format. It’s a simple table and is convenient when you are designing a report with tables and charts. This format is used by Power Bi when it loads data from Mondrian.

In the Power BI Desktop application we create a new report and select the menu Get data - Analysis Services.

In SQL Server Analysis Services database dialog we fill in the Server field as a connection string to the Mondrian server. Fill in database name in field Database. Database is a required field and is case sensitive (in our case it's FoodMart). Pick the Import radio button. In the MDX or DAX query (optional) field copy our MDX query and push the Ok button.

To try this example you can use an online eMondrian server with a sample database. The server address is https://ssemenkoff.dev/emondrian/xmla.

Preview window will appear where you can see part of the query result data in tabular format. Click the Load button.

Go to the Data tab, rename columns and change Sales column data type from Text to Decimal number.

Let’s return to the Report tab and add some visualizations. In this example we use Slicer, Pie chart and Table controls.

To refresh your report and load newly added data you can always use the Refresh button.

As you see, you can add to your Power BI Desktop reports new ones which use data from the Mondrian OLAP server.

If you have any questions or suggestions, feel free to contact me at siarhei.semiankou@gmail.com.

How to explore ClickHouse data via Excel PivotTable using Mondrian

Sergei Semenkov — Wed, 20 Jan 2021 12:39:26 +0000

Update 2026: eMondrian now runs with Docker in one command and also works with Power BI and AI agents. Current setup: Quickstart · What's new: The new eMondrian.

ClickHouse is a very powerful OLAP engine. Microsoft Excel is one of the world's most popular and powerful business programs. There are several ways to bring ClickHouse data to Excel spreadsheets.

One way to do this is using ClickHouse ODBC driver. In order to do this you have to install the ODBC driver and create ClickHouse data source in Excel. Then you can explore data as tables or you can run a query on ClickHouse and browse results.

The other way to explore ClickHouse data in Excel is using Mondrian. This approach allows you to describe ClickHouse data as a multidimensional model and give access to this model through XMLA standart. It also allows client applications to run MDX queries on ClickHouse data. You represent ClickHouse data as cubes and dimensions and then browse those cubes in Excel PivotTable.

This article describes how to install Mondrian, create a Mondrian datasource for ClickHouse database and connect Excel PivotTable to this datasource.

We will use the eMondrian fork of the Mondrian ROLAP engine. The main feature of this version is support of ADOMD.NET and Excel clients. eMondrian already includes ClickHouse driver.

Mondrian can be hosted on different java servers (Apache Tomcat, Jetty, Tiny Java Web Server). This example describes the case with Ubuntu operating system and Apache Tomcat java server.

In this example Tomcat is installed in /opt/tomcat/latest/ directory.

You have to change the following command to your Tomcat path.

export TOMCATDIR="/opt/tomcat/latest"

Next commands download the Mondrian archive, copy it to Tomcat directory and restart Tomcat java server.

sudo wget https://github.com/SergeiSemenkov/eMondrian/releases/latest/download/emondrian.war
sudo cp emondrian.war $TOMCATDIR/webapps
sudo systemctl restart tomcat

Now, let's connect Excel to the Mondrian demo Foodmart database.

In order to get address to your Mondrian XMLA service you have to insert your Ubuntu server ip in following template
http://<your_server_address>:8080/emondrian/xmla
To get your server ip, run command

hostname -I

In this example Mondrian server address is
http://192.168.100.4:8080/emondrian/xmla

Open Excel, select the Data tab, then Get Data -> From Database -> From Analysis Services.

Paste address of Mondrian XMLA service as Server name and click Next.

Then choose a cube and click Finish. The PivotTable with cube data will appear.

Next, we will create a Mondrian schema for ClickHouse example dataset OnTime. The schema defines two cubes. Cube OnTime is based on data from ontime table. Cube ViewOnTime is an example of how to create a cube based on a query instead of a table.

Following command will create new schema file OnTime.xml.

sudo nano $TOMCATDIR/webapps/emondrian/WEB-INF/schema/OnTime.xml

Copy xml to this file and press Ctrl-S (Save) and Ctrl-X(Exit).

<?xml version="1.0"?>
<Schema name="OnTime">
<Cube name="OnTime">
  <Table name="ontime"/>
  <Dimension name="Date">
    <Hierarchy hasAll="true" allMemberName="All Dates">
      <Level name="Year" column="Year" uniqueMembers="true" type="Numeric"/>
      <Level name="Quarter" column="Quarter" uniqueMembers="false" type="Numeric">
        <CaptionExpression>
          <SQL dialect="generic">concat(toString(Quarter), ' ', toString(Year))</SQL>
        </CaptionExpression>
      </Level>
      <Level name="Month" column="Month" uniqueMembers="false" type="Numeric">
        <CaptionExpression>
          <SQL dialect="generic">
            concat(          
            CASE toString(Month) WHEN '1' THEN 'January'
            WHEN '2' THEN 'February'
            WHEN '3' THEN 'March'
            WHEN '4' THEN 'April'
            WHEN '5' THEN 'May'
            WHEN '6' THEN 'June'
            WHEN '7' THEN 'July'
            WHEN '8' THEN 'August'
            WHEN '9' THEN 'September'
            WHEN '10' THEN 'October'
            WHEN '11' THEN 'November'
            WHEN '12' THEN 'December'
            ELSE 'Unknown' END,
            ' ',
            toString(Year))
          </SQL>
        </CaptionExpression> 
      </Level>
      <Level name="Day" column="FlightDate" uniqueMembers="true" type="Date">
        <CaptionExpression>
          <SQL dialect="generic">concat(toString(Year), '-', toString(Month), '-', toString(DayofMonth))</SQL>
        </CaptionExpression>
      </Level>
    </Hierarchy>
  </Dimension>
  <Dimension name="Unique Carrier">
    <Hierarchy hasAll="true" allMemberName="All Unique Carriers">
      <Level name="Unique Carrier" uniqueMembers="true">
        <KeyExpression>
          <SQL dialect="generic">toStringCutToZero(UniqueCarrier)</SQL>
        </KeyExpression>
      </Level>
    </Hierarchy>
  </Dimension>
  <Dimension name="Destination">
    <Hierarchy hasAll="true" allMemberName="All Destinations">
      <Level name="Destination State Name" column="DestStateName" uniqueMembers="true"/>
      <Level name="Destination City" column="DestCityName" uniqueMembers="true"/>
    </Hierarchy>
  </Dimension>
  <Dimension name="Origin">
    <Hierarchy hasAll="true" allMemberName="All Origins">
      <Level name="Origin State Name" column="OriginStateName" uniqueMembers="true"/>
      <Level name="Origin City" column="OriginCityName" uniqueMembers="true"/>
    </Hierarchy>
  </Dimension>
  <Measure name="Flights" column="Flights" aggregator="sum" formatString="#,###"/>
  <Measure name="Air Time" column="AirTime" aggregator="sum" formatString="#,###"/>
  <Measure name="Distance" column="Distance" aggregator="sum" formatString="#,###"/>
  <Measure name="Dep Delay" column="DepDelay" aggregator="sum" formatString="#,###"/>
  <Measure name="Carrier Delay" column="CarrierDelay" aggregator="sum" formatString="#,###"/>
  <Measure name="Weather Delay" column="WeatherDelay" aggregator="sum" formatString="#,###"/>
</Cube>
<Cube name="ViewOnTime">
<View alias="ViewOnTime">
<SQL dialect="generic">
<![CDATA[select * from ontime]]>
</SQL>
</View>
  <Dimension name="Destination City Name" foreignKey="DestCityName">
    <Hierarchy hasAll="true" allMemberName="All Destinations">
      <Level name="Destination City Name" column="DestCityName" uniqueMembers="true"/>
    </Hierarchy>
  </Dimension>
  <Dimension name="Origin City Name" foreignKey="OriginCityName">
    <Hierarchy hasAll="true" allMemberName="All Origin Cities">
      <Level name="Origin City Name" column="OriginCityName" uniqueMembers="true"/>
    </Hierarchy>
  </Dimension>
  <Measure name="Dep Delay" column="DepDelay" aggregator="sum" formatString="#,###"/>
</Cube>
</Schema>

You have to give rights to this file to the user under which Tomcat server is running. In the following command change the user tomcat:tomcat name to the name under which your Tomcat server works and run it.

sudo chown -RH tomcat:tomcat $TOMCATDIR/webapps/emondrian/WEB-INF/schema/OnTime.xml

Now, we will edit the Mondrian datasource configuration file. Run following command.

sudo nano $TOMCATDIR/webapps/emondrian/WEB-INF/datasources.xml

Replace the DataSources tag with following xml.

<DataSources>
  <DataSource>
    <DataSourceName>OnTime</DataSourceName>
    <DataSourceDescription>ClickHouse Sample Data</DataSourceDescription>
    <URL>http://localhost:8080/emondrian/xmla</URL>
    <DataSourceInfo>Provider=mondrian;Jdbc=jdbc:clickhouse://192.168.100.8:8123/datasets;JdbcDrivers=ru.yandex.clickhouse.ClickHouseDriver</DataSourceInfo>
    <ProviderName>Mondrian</ProviderName>
    <ProviderType>MDP</ProviderType>
    <AuthenticationMode>Unauthenticated</AuthenticationMode>
    <Catalogs>
        <Catalog name="OnTime">
            <Definition>/WEB-INF/schema/OnTime.xml</Definition>
        </Catalog>
    </Catalogs>
  </DataSource>
</DataSources>

Edit connection string to your ClickHouse dataset in DataSourceInfo tag using following syntax:
jdbc:clickhouse://<host>:<port>[/<database>][?user=<my_user>&password=<my_password>]

Save (Ctrl-S) and close (Ctrl-X) datasources file.
Restart tomcat server.

sudo systemctl restart tomcat

Create a new connection to Mondrian server in Excel to see cubes from your new schema. Select one to browse its data in PivotTable.

Try and create your one Mondrian schema for ClickHouse datasets (documentation).

If you have any questions or suggestions, feel free to contact me at siarhei.semiankou@gmail.com.