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.

the problem with silos of Power BI files

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.

DirectQuery for Power BI datasets and analysis services

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
self-service BI and enterprise BI comes together with DirectQuery for Power BI datasets and analysis services

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 on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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.

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

  • Do i understand it correct that it isn’t yet possible to combine SSAS live connection with an excel in one power bi model? And if so, when will this be possible, because that would be a major feature if you ask me…

    • This new DirectQuery option is only available for Azure AS and Power BI datasets. for SSAS I presume it will be available when the next version of SQL Server released.
      Cheers
      Reza

  • Hello,
    would it be possible then to publish a SSAS cube to Power BI and then using that cube as a Power BI dataset.

    And finally doing then some changes on the model, I tried this out and this i not possible.

    • SSAS as a source connection for this feature is not available yet. It would be available hopefully in the next edition of SQL Server.
      Cheers
      Reza

  • Got this error when converting from live connection to DQ, by clicking “Make changes to this model”. The dataset use Snowflake as data source. Does this new feature support snowflake?

    DirectQuery to AS – Dataset Name
    An error occurred while loading the model. Verify that the connection information is correct and that you have permissions to access the data source.

    • Hi Julia
      Snowflake connection in Power BI is a DirectQuery connection.
      the DirectQuery for Power BI dataset, only works for live connection sources which are Power BI dataset and Azure analysis service. I don’t think this yet support a Power BI dataset with a DirectQuery to snowflake. Although, this is just my thinking, I haven’t tested it.
      Cheers
      Reza

  • We have Power BI Datasets that are created by doing making a direct query connection to an SAP HANA calculation view. Are we able to chain these PBI datasets with SAP HANA as the source together using this feature?

  • Hi Raza, great videos so far, thanks a lot.
    We are having Some SSAS cubes (Multi dimensional) which having great futures like compex hierarchies and lot’s of tables and calculations. we can have the life connection and gate way all works fie in PBI. is there a way to comnine also import queries with the live connection> is composite model supporting this mix of data sources?
    thanks,

    • Hi Hamada
      The ability to have your AS as the DirectQuery (or Live as you call it) and other data sources as Import in the same PBIX file exists only for Power BI Datasets and Azure Analysis Services (which is tabular) as far as I am aware. I don’t think you can do that with your multi-dimensional cube. one option can be migrating to tabular.

Leave a Reply