Tag: power-bi

All you need is Onelake

Yes, it’s clickbait. Of course you need more, but it’s hard to get anyone to read a blog these days 🙂

I built OneLake Studio, a free VS Code extension that treats your Fabric tenant as one catalog. Every table gets one unique name, lakehouse.workspace.item.schema.table (or warehouse.…), and every semantic model is semanticmodel.workspace.model. You explore all of it with SQL and can join across items and workspaces. It’s read only, and what you’ve opened stays cached on your machine. A semantic model (a Power BI .bim or an Apache Ossie .yaml) is a view over its tables. You can pivot it and publish it as a very basic Fabric app. The theory: if you like the app, your coding agent can turn it into a full-blown one.

Why VS Code? It handles sign-in for me (you use the account VS Code already has), and it’s where I spend all my time anyway. Authentication is still harder than it should be. I dream of a world where I never have to deal with it.

Here’s the tour: browse OneLake, look at a table, open a semantic model, explore it, see how its DAX becomes SQL, then publish it as a Fabric app your colleagues open in their browser. That last step is the point of the whole exercise. At the end is a short look under the hood.

A word of warning. This is a fun personal project, not an official Microsoft one. It computes a model’s numbers with its own engine, and matching Power BI 100% of the time is not a goal: by design it already differs, for example in case sensitivity and in how text is trimmed. The DAX compiler, dax-sql, was contributed by N.S. Devaraj.

1. Install and sign in

In VS Code, open Extensions (Ctrl+Shift+X), search for OneLake Studio and click Install. Click its icon in the activity bar, then Sign in to OneLake.

2. Browse OneLake

All your workspaces show up in one tree. Pin the ones you use most so they stay at the top. Each workspace lists its lakehouses, its Semantic models and its Apps. Mirrored databases show up too.

3. Look at a table

Click a table. Its icon tells you whether it was written as Delta or Iceberg, and it opens on three tabs.

Stats comes straight from the table’s metadata, before any data is read. For a Delta table, that’s its Delta log: version, rows, files, size, compression, last write, and features.

History lists what happened to the table, newest first: each commit, its operation, the rows and files it added, and which engine wrote it. Nothing is read until you open the tab.

Data shows the first rows. The query is right there to edit if you want more.

Every table, Delta or Iceberg, is read through Iceberg metadata. OneLake translates Delta to Iceberg v2 by default, as a background service, and you don’t need to do anything. When a table has no Iceberg metadata, OneLake Studio quotes Fabric’s conversion log so you can see why.

4. Open a semantic model

Under Semantic models, click one. In the background, OneLake Studio saves it into its lakehouse, and a check mark shows next to it in the tree. From then on it opens at once.

The Model page shows its tables and how they relate.

An Apache Ossie .yaml in a lakehouse’s Files opens the same way.

5. Explore it

Switch to Pivot. Drag fields onto Rows, Columns, Values and Filters, then pick a view: table, bar, line, area, heatmap and more.

Show SQL turns the pivot into a query you can edit and run. You ask the model with plain SQL. Pick your columns, wrap a measure in AGG(), and select FROM model. For example, on the NYC taxi model:

SELECT "dim_date"."year", "dim_zone"."Borough", AGG("Total Amount")
FROM model
GROUP BY ALL

AGG("Total Amount") is the measure exactly as it was defined in the model, in DAX. It’s computed in the context of each row (that year and that borough, plus any WHERE you add), just like a visual in Power BI. There’s no new syntax to change a measure on the fly, to add a filter or remove one, and I’m not interested in inventing one. If you need a different calculation, define it in the model.

Show SQL also shows where the time went.

Click Tree to see what actually ran. You never wrote a join, but the model knows how its tables relate, so the trips are joined to the zones on the location and to the calendar on the date. Only the columns the query needs are read.

6. DAX to SQL

Every measure goes through dax-sql, a compiler that turns DAX into plain SQL, which DuckDB then runs. Here it is on something less trivial than a SUM: each borough’s share of the year’s total.

DEFINE MEASURE fct_trips[Share of Borough] =
  DIVIDE([Total Amount], CALCULATE([Total Amount], ALL(dim_zone[Borough])))
EVALUATE SUMMARIZECOLUMNS(dim_date[year], dim_zone[Borough], "Share", [Share of Borough])

That compiles to this (only the line breaks are mine):

WITH f3 AS MATERIALIZED (
  SELECT j4."year" AS "g0", j5."Borough" AS "g1", SUM(t2."total_amount") AS "a0"
  FROM "mart"."fct_trips" AS t2
  LEFT JOIN "mart"."dim_date" AS j4 ON j4."date" = t2."pickup_date"
  LEFT JOIN "mart"."dim_zone" AS j5 ON j5."LocationID" = t2."PULocationID"
  GROUP BY j4."year", j5."Borough"),
f7 AS (SELECT f3.g0 AS g0, SUM(f3.a0) AS a0 FROM f3 GROUP BY f3.g0)
SELECT CAST(q10."year" AS BIGINT) AS "year", q10."Borough" AS "Borough",
       CAST(q10."Share" AS DOUBLE) AS "Share"
FROM (SELECT s9."year", s9."Borough", s9."Share"
  FROM (SELECT *
    FROM (SELECT k1."year" AS "year", k1."Borough" AS "Borough",
                 (f3.a0 / NULLIF(f7.a0, 0)) AS "Share"
      FROM (SELECT f3.g0 AS "year", f3.g1 AS "Borough" FROM f3) AS k1
      LEFT JOIN f3 ON f3.g0 IS NOT DISTINCT FROM k1."year"
                  AND f3.g1 IS NOT DISTINCT FROM k1."Borough"
      LEFT JOIN f7 ON f7.g0 IS NOT DISTINCT FROM k1."year") AS z8
    WHERE z8."Share" IS NOT NULL) AS s9) AS q10

A few things to notice:

  • ALL(dim_zone[Borough]) doesn’t scan the trips again. f3 is the total by year and borough, and f7 adds the boroughs up for each year. The fact table is read once.
  • DIVIDE divides by NULLIF(…, 0), so a zero total gives a blank, not an error.
  • Blank rows are dropped, as SUMMARIZECOLUMNS does.

7. Publish it as a Fabric app

Click Export to Fabric. Confirm where the local copy goes and click Export.

Once it’s published, your colleagues get the same pages in their browser, without VS Code.

The app lives in your workspace, protected like any other Fabric item, so only people with access can open it (it shows under Apps in the tree, with a lock). It reads the model where it lives, so nothing is copied and the data is current. And a copy of the app stays in your folder, so you can treat it as a starting point: change it, then export again.

How it works

The same pages (Model, Pivot, Logs) run in two places, on DuckDB both times.

  • In VS Code, the extension runs a native DuckDB 2.0 inside VS Code itself. It’s multithreaded and reads OneLake directly, with the token from your VS Code sign-in. The panel you see is only a view.
  • In the Fabric app, there’s no VS Code, so the engine is DuckDB-WASM 1.5.6, running in the viewer’s browser. The app is a static page plus one small function. The function runs in Fabric: it reads the model from the lakehouse and tells the page where each table is. The page downloads the Parquet files it needs, and DuckDB reads them from the browser’s local storage. SQL typed in the app only sees those Parquet files and can’t read anything else.

DuckDB in the browser: single-threaded

DuckDB-WASM runs single-threaded here, so one query always runs on one core. A big scan is noticeably slower than the same query in VS Code. To keep the page responsive, the app runs a pool of two engines, each in its own web worker, fed by one queue. When a page draws several visuals, two queries run side by side, and a slow one holds up only its own engine. (I tried three. It wasn’t faster.)

I also tried a multithreaded build. It turned out to be more complex than it looks, so for now it stays single-threaded, and the pool of two does the job.

The cache

Iceberg files never change once they’re written, so almost everything can be kept.

  • In VS Code, listings, table metadata and first rows are kept on disk. They’re shown at once and refreshed in the background. Table data goes into DuckDB’s disk cache (64 GB by default, per account), so a table you’ve read before comes from your machine, even after a restart. Clear Cached OneLake Data empties it.
  • In the browser, there are four layers:
    • The function’s answer (where the tables are), kept for about an hour. A viewer calls the function about once an hour, not on every load.
    • The Iceberg manifests, so a new snapshot reads only the manifests it added.
    • The Parquet files, in the browser’s private file system (OPFS), up to 64 GB, shared by both engines and every tab. This is why the app needs Chrome or Edge 121 or later.
    • Query results, stored as Arrow and keyed by the SQL and the tables’ current snapshot. A reload answers every query it has already seen without running the engine. New data means a new key, so you never see a stale result.

Each app has its own cache, in each browser. The first visit pays for the download. Later visits are fast.

Limitations, by design

Everything runs on the client: the engine on your machine (or in your colleague’s browser, for the Fabric app) reads the data straight from OneLake.

  • Egress fees can become a problem. Every query pulls data out of OneLake to wherever the client is, and with big tables or users far from the data, that can cost money.
  • Row- and column-level security (RLS/CLS) aren’t supported. OneLake now has a table read API (in preview) that returns Apache Arrow with RLS and CLS applied. I’ve tested it with Python and it works as expected, but I haven’t tried it with WebAssembly yet.
  • It’s for small data. Don’t use it to download 10 GB tables.
  • Correct SQL is not the same as a good plan. Even when the generated SQL is right in theory, getting a well-optimized plan out of it is not obvious at all. A measure becomes CTEs, re-aggregations and joins that nobody would write by hand, and the engine has to see through them. Some queries will be slower than they should be.
  • DAX to SQL comes with no guarantee. dax-sql, contributed by N.S. Devaraj, compiles DAX as faithfully as it can, but there’s no guarantee that a result is correct, or that a given function or piece of syntax is supported. Some DAX isn’t supported at all.

So it’s not meant for production or any serious setup. But it’s an interesting experiment in what a Fabric app can do on its own, IMHO.

Funny enough, I wrote about this back in 2021: BI tools need a SQL generator that composes measures the way Power BI does, including the ones that ignore your filters. I had the idea, but obviously I couldn’t build it. Five years later, with AI, I could. If anything, this project is a testament to how far AI has come.

Try it

Install OneLake Studio from the VS Code Marketplace. It runs on Windows, macOS and Linux.

just vorder don’t try to understand how vertipaq works

I know it is a niche topic, and in theory, we should not think about it. in import mode, Power BI ingests data and automatically optimizes the format without users knowing anything about it. With Direct Lake, it has become a little more nuanced. The ingestion part is done downstream by other processes that Power BI cannot control. The idea is that if your Parquet files are optimally written using Vorder, you will get the best performance. The reality is a bit more complicated. External writers know nothing about Vorder, and even some internal Fabric Engines do not write it by default.

To show the impact of read performance for Vordered Parquet vs sorted Parquet, I ran some tests. I spent a lot of time ensuring I was not hitting the hot cache, which would defeat the purpose. One thing I learned is to test only one aspect of the system carefully.

Cold Run: Data loaded from OneLake with on-the-fly building of dictionaries, relationships, etc

Warm Run: Data already in memory format.

Hot Run: The system has already scanned the same data before. You can disable this by calling the ClearCache command using XMLA (you cannot use a REST API call).

Prepare the Data

Same model, three dimensions, and one fact table, you can generate the data using this notebook

  • Deltars: Data prepared using Delta Rust sorted by date, id, then time.
  • Spark_default: Same sorted data but written by Spark.
  • Spark_vorder: Manually enabling Vorder (Vorder is off by default for new workspaces).

Note: you can not sort and vorder at the same time, at least in Spark, DWH seems to support it just fine

The data layout is as follows:

I could not make Delta Rust produce larger row groups. Notice that ZSTD gives better compression, although it seems Power BI has to uncompress the data in memory, so it seems it is not a big deal

I added a bar chart to show how the data is sorted per date. As expected, Vorder is all over the place, the algorithm determines that this is the best row reordering to achieve the best RLE encoding.

The Queries

The queries are very simple. Initially, I tried more complex queries, but they involved other parts of the system and introduced more variables. I was mainly interested in testing the scan performance of VertiPaq.

Basically, it is a simple filter and aggregation.

The Test

You can download the notebook here : 

def run_test(workspace, model_to_test):
    for i, dataset in enumerate(model_to_test):
        try:
            print(dataset)
            duckrun.connect(f"{ws}/{lh}.Lakehouse/{dataset}").deploy(bim_url)
            run_dax(workspace, dataset, 0, 1)
            time.sleep(300)
            run_dax(workspace, dataset, 1, 5)
            time.sleep(300)
        except Exception as e:
            print(f"Error: {e}")
    return 'done'

Deploy automatically generates a new semantic model. If it already exists, it will call clearvalue and perform a full refresh, ensuring no data is kept in memory. When running DAX, I make sure ClearCache is called.

The first run includes only one query; the second run includes five different queries. I added a 5-minute wait time to create a clearer chart of capacity usage.

Capacity Usage

To be clear, this is not a general statement, but at least in this particular case with this specific dataset, the biggest impact of Vorder seems to appear in the capacity consumption during the cold run. In other words:

Transcoding a vanilla Parquet file consumes more compute than a Vordered Parquet file.

Warm runs appear to be roughly similar.

Impact on Performance

Again, this is based on only a couple of queries, but overall, the sorted data seems slightly faster in warm runs (although more expensive). Still, 120 ms vs 340 ms will not make a big difference. I suspect the queries I ran aligned more closely with the column sorting—that was not intentional.

Takeaway

Just Vorder if you can. Make sure you enable it when starting a new project. ETL, data engineering have only one purpose, make the experience of the end users the best possible way, ETL job that take 30 seconds more is nothing compared to a slower PowerBI reports.

now if you can’t, maybe you are using a shortcut from an external engine, check your powerbi performance if it is not as good as you expect then make a copy, the only thing that matter is the end user experience.

Another lesson is that VertiPaq is not your typical OLAP engine; common database tricks do not apply here. It is a unique engine that operates entirely on compressed data. Better-RLE encoded Parquet will give you better results, yes you may have cases where the sorting align better with your queries pattern, but in the general case, Vorder is always the simplest option.

First Look at Incremental Framing in Power BI

TL;DR: Incremental framing is like CDC to RAM 🙂 It significantly improves cold-run performance of Direct Lake mode in some scenarios, there is an excellent documentation that explain everything in details

What Is Incremental Framing?

One of the most important improvements to Direct Lake mode in Power BI is incremental framing.

Power BI’s OLAP engine, VertiPaq (probably the most widely deployed OLAP engine, though many outside the Power BI world may not know it) relies heavily on dictionaries. This works well because it is a read-only database. another core trick is its ability to do calculation directly on encoded data. This makes it extremely efficient and embarrassingly fast  ( I just like this expression for some reason ).

Direct Lake Breakthrough

Direct Lake’s breakthrough is that dictionary building is fast enough to be done at runtime.

Typical workflow:

  1. A user opens a report.
  2. The report generates DAX queries.
  3. These queries trigger scans against the Delta table.
  4. VertiPaq scans only the required columns.
  5. It builds a global dictionary per column, loads the data from Parquet into memory, and executes queries.

The encoding step happens once at the start, and since BI data doesn’t usually change more that much, this model works well.

The Problem with Continuous Appends

In scenarios where data is appended frequently (e.g., every few minutes), the initial approach does not works very well. Each update requires rebuilding dictionaries and reloading all the data into RAM, effectively paying the cost of a cold run every time ( reading from remote storage will be always slower).

How Incremental Framing Fixes This

Incremental framing solves the problem by:

  • Incrementally loading new data into RAM.
  • Encoding only what’s necessary.
  • Removing obsolete Parquet data when not needed.

This substantially improves cold-run performance. Hot-run performance remains largely unchanged.

Benchmark: Australian Electricity Market

To test this feature, I used my go-to workload: the Australian electricity market, where data is appended every 5 minutes—an ideal test case.

  • Incremental framing is on by default, I turn it off using this bog
  • For benchmarking, I adapted an existing tool , Direct Lake load testing( I just changed writing the results to Delta instead of CSV), I used 8 concurrent users, the main fact Table is around 120 M records, the queries reflect a typical user session , this is a real life use case, not some theoretical benchmark.

Results

P99

P99 (the 99th percentile latency, often used to show worst-case performance):

  • Improvement of 9x–10x, again, your results may varied depending on workload, Parquet layout, and data distribution.

P90

P90 (90th percentile latency):

  • Less dramatic but still strong.
  • Improved from 500 ms → 200 ms.
  • Faster queries also reduce capacity unit usage.

Geomean

just for fun and to show how fast Vertipaq is, let’s see the geomean, alright went from 11 ms to 8 ms, general purpose OLAP engines are cool, but specialized Engines are just at another level !!!

This does not solve Bad Table layout problem

This feature improves support for Delta tables with frequent appends and deletes. However, performance still degrades if you have too many small Parquet row groups.

VertiPaq does not rewrite data layouts—it reads data as-is. To maintain good performance:

  • Compact your tables regularly.
  • In my case, I backfill data nightly. The small Parquets added during the day don’t cause major issues, but I still compact every 100 files as a precaution.

If your data is produced inside Fabric, VOrder helps manage this. For external engines (Snowflake, Databricks, Delta Lake with Python), you’ll need to actively manage table layout yourself.

Some Observations on Running TPCH 1 TB on Microsoft Fabric

This is not an official Microsoft benchmark, just my personal experience.

Last week, I came across a new TPCH generator written in Rust. Luckily, someone ported it to Python, which makes generating large datasets possible even with a small amount of RAM. For example, it took 2 hours and 30 minutes to generate a 1 TB scale dataset using the smallest Fabric Python notebook (2 cores and 16 GB of RAM).

Having the data handy, I tested Fabric DWH and SQL Endpoint. I also tested DuckDB as a sanity check. To be honest, I wasn’t sure what to expect.

I shared all the notebooks used and results here

I ran the test 30 times over three days, I think I have enough data to say something useful,In this blog, I will focus only on the results for the cold and warm runs, along with some observations.

For readers unfamiliar with Fabric, DWH and SQL Endpoint refer to the same distributed SQL engine. With DWH, you ingest data that is stored as a Delta table (which can be read by any Delta reader). With SQL Endpoint, you query external Delta tables written by Spark and other writers (this is called a Lakehouse table). Both use Delta tables.

Notes:

  • All the runs are using a Python notebook
  • to send queries to DWH/SQL Endpoint, all you need is
    conn = notebookutils.data.connect_to_artifact("data")
    conn.query("select 42")
  • I did not include the cost of ingestion for the DWH
  • The cost include compute and storage transaction and assume pay as you go rate of 0.18 $/Cu(hour)
  • For extracting Capacity usage, I used this excellent blog

Cold Run

  • The first-ever run on SQL Endpoint incurs an overhead, apparently the system build statistics. This overhead happened only once across all tests.
  • Point 2 is an outlier but an interesting one 🙂
  • The number of dots displayed is less than the number of tests runs as some tests perfectly match, which is a good sign that the system is predictable !!!
  • vorder improves performance for both SQL Endpoint and DuckDB. The data was generated by Rust and rewritten using Spark; it seems to be worth the effort.
  • Costs are roughly the same for DWH and SQL Endpoint when the Delta is optimized by vorder, but DWH is still faster.
  • DuckDB, running in a Python notebook with 64 cores, is the cheapest (but the slowest). Query 17 did not run , so that result is moot. ,Still, it’s a testament to the OneLake architecture: third-party engines can perform well without any additional Microsoft integration. Lakehouse for the win.

Warm Run

  • vorder is better than vanilla Parquet.
  • DWH is faster and a bit cheaper than SQL Endpoint.
  • DuckDB behavior is a bit surprising, was expecting better performance , considering the data is already loaded into RAM.

Impact on the Parquet Writer

I added a chat showing the impact of using different writers on the read performance, I use only warm run to remove the impact of the first run ever as it does not happen in the DWH ( as the data was ingested)

  • given the same table layout, DWH and SQL Endpoint perform the same, it is expected as it is the same engine
  • surprisingly using the initial raw delta table vs spark optimize write gave more or less the same performance at least for this particular workload.

Final Thoughts

Running the test was a very enjoyable experience, and for me, that’s the most important thing. I particularly enjoyed using Python notebooks to interact with Fabric DWH. It makes a lot of sense to combine a client-server distributed system with a lightweight client that costs very little.

There are new features coming that will make the experience working with DWH even more streamlined.

Edit :

  • update the figures for Dcukdb as Query 17 runs but you need to limit the memory manually set memory_limit='500GB'
  • added a graph on the impact of the parquet layout.