Category: dax

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.

Using Direct Query mode with Fabric DWH

TL;DR : Direct Query with Fabric SQL is considered a background operation, which means the usage is smoothed over a 24 hours period, this blog is definitely not a recommendation nor a good practise or anything in that nature, but I was just curious, what if this make Direct Query an attractive proposition in some scenarios ?

Use Case, Small Data with frequent refreshes

We assume it is a small company ( 10 users),we will test using a Fabric F2 SKU and PowerBI Pro license as a front end. ( free PowerBI readers start from F64)

Monthly cost = Fabric 156 $ + PowerBI Pro 10 x 10 = 256 US $/Month

In this case, the business requirement is to have the freshness of 5 minutes, the user needs to the see the latest data, which make import not an option as it is limited to 8 times per day

The Data Model

The data model is relatively small, 5 Tables, 3 dimensions and two fact tables, the biggest one is 9 Million rows, the facts are ingested with new data every 5 minutes, Table State and Settlement Date are fixed, and DUID changes very slowly, maybe once every couple of months.

Fabric Notebook as an ingestion tool

To reduce compute usage, we used Spark notebook with the smallest compute, 4 cores and 32 GB of RAM

How to simulate concurrency 

This one is tricky, 10 users does not mean, they will all open the same report at the same time and continuously clicking refresh, I know there are tools to test load PowerBI but you needs to install Powershell and stuff, I just simulated by using a dynamic slicer and running multiple copies of the report concurrently.

Two Workspaces

we will  try to keep it as simple as possible, no medallion architecture here, just two workspaces

Backend Workspace    : using an F2 capacity

Front End Workspace  : old school pro license workspace

Direct Lake Mode vs Direct Query vs Import in Fabric

As an oversimplification and specially for People not familiar with microsoft BI stack, PowerBI Engine is called analysis Service and it does basically two thing

Formula Engine : Translate DAX using the semantic model to SQL

Storage Engine : get the data from storage using SQL

Direct Query mode :  The data is served by a DB like Synapse or BigQuery, SQL Server etc

Direct Lake mode    : The Data is served by Vertipaq, the data format is open source

Import  mode           : The Data is served by Vertipaq, the data format is proprietary 

Note that Import and Direct Lake difference is in the storage format, but the In-Memory format is the same ( that’s a very clever design decision)

Vertipaq will always be the Fastest Engine 

 Vertipaq is designed for one  thing, pure speed, so I don’t have any expectation that other Engines can compete with it, we are talking milliseconds even with joins and multiple tables,  I am more interested in resource usage though 

Resource Usage Profile

Direct Lake (interactive tasks are smoothed over a short period of time)

Direct Query with Fabric SQL (Background are smoothed over 24 hours)

To be fair both modes worked as expected, Direct Lake is definitely faster which is expected but what got my attention is the DWH did well and drained the capacity with only a rate of  2 CU/s,  there is no bursting, it is the baseline performance here, that’s extremely encouraging as one of the biggest complaint about cloud DWH is they don’t scale down very well.

Keep in mind in both cases, the total capacity you can consume in 24 hours is still limited by

 24 X 2 CU =  172,800 CU(s).

Having a look at this documentation as it is important to understand how to properly size your capacity

PowerBI is still very chatty

PowerBI does generate a lot of SQL Queries in Direct Query mode, most of it took between 500 ms to 1 second. that’s not bad for a SQL Engine that cost 0.36 $/Hour

Ok what does this all mean ?

I reserve the right to change my view after further testing, but my hypothesis is, given that the DWH has a very good performance but more importantly very efficient engine at lower scale and with the fact it is considered a background operation, Direct Query maybe an interesting option if you need more than 8 refresh per day and you are using PowerBI Pro license with a small scale F SKU.

But as always test using your own data.

PowerBI Query plan when using Top N filter

The October release of PowerBI Desktop introduced a very interesting feature, Top N filtered is pushed down for Direct Query Sources, I thought I may give it a try and blog about it, for some reason it did not work, which is great for the purpose of this blog as if you came from a SQL Background you will be surprised how PowerBI DAX Engine Works.

let’s try with one table in BigQuery as an example, and ask this Question, what’s the top 5 Substation by Electricity produced, in PowerBI, it is a trivial exercise, just use the Top N filter

First Observation, 3.7 Second seems rather Slow, BigQuery or any Columnar Database should return the results way faster, specially that we are grouping by low cardinality columns ( around 250 distinct values)

Let’s try SQL Query in BigQuery Console

And let’s check the duration, 351 ms, the table has 91 Million records, that’s not bad at all, but we need to account for the data transfer latency to my laptop, still that does not explain the difference in duration !!!

DAX Engine Query Plan

let’s have a look at the Query Plan generated by DAX Engine using the excellent free tool, DAX Studio

That’s very strange, 2 SQL Query and 1 Second spent by the Formula Engine, and the two SQL Queries are not even in parallel

Looking at the SQL Queries, I think this is the logic of the Query Plan

  • Send a SQL Query to get the list of all the substation and the sum of of MWH.
  • Order the result using the Formula Engine and select 5 substation.
  • Send another SQL Query with a filter of those 5 substation

Probably you are wondering why this convoluted Query Plan, Surely DAX Engine can just send 1 SQL Query to get the results, why the two trips to the source system, which make the whole experience slow.

Vertipaq Don’t support Sort Operator

Vertipaq which is the internal storage engine of PowerBI does not support the sort operator, hence the previous Query do make sense if your Storage engine don’t support sort.

But My Source do support Sorting ?

That’s the new feature, DAX Engine will generate a different plan when the source system do support sorting.

Great, again , Why Vertipaq don’t support sort Operator ?

No idea, probably only a couple of engineers from Microsoft Know the answer.

Edit : 23 October 2022

Jeffrey Wang ( One of the Original Authors of DAX Engine) was very kind and provided this explanation why the optimization did not kick in for BigQuery

Multi fact support in DAX and Malloy

This is a quick blog showing how the two languages behave when dealing with multiple fact tables.

let’s start with a simple Model, Two Tables Budget and Actual storing items sold by country and color

Budget

Actual

For example we want to ask, how many items were sold by continent, we don’t have this information, we need a dimension table that map state to continent.

DAX

And the Data Model will look like this.

To get the results, we write this DAX Query in DAX Studio ( Btw, the new version 3 is very slick !!!)

DAX will generate two SQL Query to get the results from the two tables and merge the results, using the internal “Formula” Engine

Malloy

in Malloy we do the same by writing code, you can download the Data Model here

In DAX we use summarize columns to aggregate measures from different tables, as far as I can tell, Malloy don’t support this model yet, The tables Budget and Actual are independent, basically you need to manually join the two Queries generated from the two tables.

Query: Budget_by_state is Budget -> {
  aggregate:_Budget
  group_by : dim_state.state
}

Query: Actual_by_state is Actual ->{
  aggregate:Quantity
  group_by : dim_state.state
}
query: merge_results is from_sql(state_source_) {
   join_one: q2 is from(->Budget_by_state ) with state
   join_one: q3 is from(->Actual_by_state) with state
} ->{
  
  group_by : continent
  aggregate: QTY_Budget is sum(q2._Budget),QTY_Sold is sum(q3.Quantity)
}

And we get the same results, Malloy always generate one SQL Query, as there is no way to merge the results internally, as a matter of fact the only “calculation” engine is the SQL Database, which is in this particular case DuckDB.

Obviously you can always create new source by using state as a base table, but I don’t think it is a sustainable solution, as the whole point is to have One model that answers a lot of different Questions.

Take Away

Native support to multiple tables is obviously not unique to DAX, thoughtspot TML support it out of the Box, I hope Malloy developers consider this common scenario for future development.