Understand the queries behind Composite Models

With the December 2020 release of Power BI Desktop we got an early christmas present. A loooong waited feature that will change how we build Power BI solutions! The feature is called “DirectQuery for Power BI datasets and Azure Analysis Services” also known as Composite Models Gen2.

This is neither nice nor cool – this is HUGE!

Alberto Ferrari

It is truly amazing and opens up so many scenarios. If you want to learn more, then take a look at the official article from Microsoft or the blog posts from community experts:

• https://docs.microsoft.com/en-gb/power-bi/connect-data/desktop-directquery-datasets-azure-analysis-services
• https://www.sqlbi.com/articles/new-composite-models-in-power-bi-a-milestone-in-business-intelligence/
• https://radacad.com/directquery-for-power-bi-datasets-and-analysis-services-the-composite-model-with-analysis-services-what-is-it-and-why-it-is-a-big-deal
• https://exceleratorbi.com.au/the-most-important-update-to-power-bi-desktop-ever/

In this blog post, I will dig a little deeper and give you an understanding of how the feature works. Although it’s a new shiny feature, it builds on the same concepts from Composite Models Gen1. Import data combined with DirectQuery to SQL database has been around quite some time. Unfortunately this comes with some performance implications that also applies to Gen2 ? Mainly because of the nature of DirectQuery as opposed to Live Connection.

Let’s start by looking at a very realistic example where I combine data from an Enterprise Azure Analysis Services model and a Self-Service Power BI Dataset. Both models have many tables, but let’s make it very simple and zoom in on two tables – one from each of the models. I have my calendar table with 11k rows in AAS and a small 339 row fact table in the Power BI Dataset and have set up a relationship between a date column in each of the tables. I then create a card visual based on a measure from the fact table and a slicer with the weekdays from my calendar table. And now for the real question – what happens when I click on the slicer?

Let’s have a look at the Performance Analyzer to see the performance: Nearly two seconds – fast or slow? It depends ? Then let’s grab the query to get an understanding of what’s going on.

First we have a DAX query and the two SQL queries? No – it’s also DAX queries. It’s just legacy from Gen1 of Composite Models ? The really interesting part to understand in the _Var0 query that contains a very long list of dates. We only see three lines here, but it’s actually 25.831 characters long! It’s the list of all the Sunday’s in my 30 year Calendar Table. This long list is then send from the AAS model to the fact table in the Power BI dataset, so it can make the filter and only show the rows with data on Sundays. Makes sense?

It’s crucial to understand this “pattern” when working with Composite Models – and specially models with big dimension tables that is joined between the underlying models/datasets. You could potentially generate VERY large queries and then get slow performance… I’m sure the clever engineers at Microsoft will do all they can to optimize, optimize and optimize, but this is the nature of how it works and we will probably NEVER get the same performance with DirectQuery as we are used to with Live Connection.

I’m pretty sure Marco, Alberto and other experts in the community will do a whole lot more investigations – as with every other new technology. It’s definitely a very interesting path we are walking on. I started this post with an quote and will also end it with one, as I highly agree:

We need to understand how to use it the proper way. Start learning.

Alberto Ferrari