DirectQuery for Power BI datasets and Analysis Services. The composite model with Analysis Services. What is it and why it is a big deal?

The news of the latest update of Power BI Desktop, December 2020, is now everywhere. Especially the most exciting feature of all is the DirectQuery for Power BI datasets and analysis service, or in other terms composite model for Power BI datasets. However, you might ask, what that mean? what is it? and how it can be useful? In this article, I’ll explain that.

Power BI with Import Data

The option that is used most commonly in the world of Power BI users is to import the data from the source into the Power BI. This option provides the ultimate flexibility in the modeling. If we use this approach, we can customize the tables using Power Query transformations, create relationships in the way we want, and write any DAX calculations and finally do any visualizations. The import data mode of Power BI gives us untainted power and flexibility.

In this article I have explained the Import data mode full in details;

One important problem of Import data

Import data is fantastic, but nothing comes without a side effect. With import data, one of the big problems is that every self-service user starts to import data into his/her own data model, and soon you’ll end up with tens of data models, a lot of duplications and silos of Power BI models everywhere. And in fact many of those models might do the same thing over and over.

Power BI using Live Connection

An approach that comes very handy to solve the problems mentioned above is to have a Power BI report with a live connection to another dataset. This dataset can be a Power BI dataset hosted in the Power BI service, or an Azure analysis services dataset, or SSAS dataset hosted on-premises. The Power BI report in this case would be just visualizing data from the main model.

Using Power BI as a live connection for users means they cannot change the model and build their own. so their won’t be a problem of inconsistency, the won’t be silos of data models. there would be one or a few central models only.

In this blog article I explained what a live connection means for Power BI;

The big limitation of live connection (Until now)

Live connection is great for governing the Power BI implementation and avoiding silos. However, it is not a useful option for self-service users. Because they cannot (or we have to use the past tense now) import other data sources and build variations on top of it. The most they could do was to create report level measures. In fact I wrote a blog article once, explaining how to IMPORT data from a Power BI dataset so that these types of users can still leverage some of the calculations done in the central model. here is the blog article;

DirectQuery for Power BI datasets or Analysis Services datasets

Now that you know the background information, let’s talk about the main topic. This feature, which announced today, means that you can now have a new dataset that is using the central dataset, but bring your variations into it.

This new feature provides the ability for the self-service users to re-use the existing model and add more things to it in a new model. Instead of re-inventing the wheel, they add more components to it. This is where the two worlds of self-service and enterprise BI come together.

As an example: A developer in the BI team builds a data model that has some entities about Sales from the SQL Server database, Dynamics 365, Azure blob storage, SharePoint and a few other sources. This model can be used for a report directly, or it can be used by a self-service user to bring some more data from Excel and CSV files and become more complete for a specific use.

The model built by that self-service user can also be customized by another self-service user. As you see in the screenshot above, this feature provides layers of implementation and development on top of each other rather than re-doing.

Why this feature is a big deal?

You probably already guessed it based on the explanation above, here is the summary:

• Leverage the central model
• Bring more datasets
• Self-service and enterprise model combined
• Less redundancy
• More consistency
• Re-use instead of re-do
• More trust in Power BI results

This feature is more than a feature, this is where the two worlds of enterprise BI and self-service BI comes together. Instead of blocking each other’s way and going their own ways, now these two help each other to reach a common goal.

Under the hood; How it works

This blog is not enough to explain how it works and why it is good all in one place. However, we can go through the HOW part very briefly. Using this feature, you’ll have a new copy of the data model that gets part of the data as a DirectQuery source from the Power BI dataset connection, and other part from the data sources you select to import.

This blog explains how it works, and also explains some limitations. Yes, there are limitations to this function at the moment. However, these limitations would be mostly lifted one by one gradually. In another article, I’ll explain HOW this feature works in detail and what are things to be aware of.

Video

Summary

In summary, this feature is a major milestone in developing Power BI solutions in any organizations. This is a feature that can help you to have less redundant code, better consistency across your analysis, re-use whatever has been done so far, and customize on top of it. This feature changes the way Power BI used in a development environment significantly.

What do you think? please share your thoughts here in the comments below.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.