A New Era for Self-Service BI: Direct Query for Power BI DataSets

Do you remember the times, when Business Intelligence meant to only have one single point of truth within your organization? And this single source of truth was your enterprise semantic model?

How often do you wanted to extend this central model with your own data? And how often did you extend the central data by downloading it into an Excel file and merging it with your new data?

Have you counted the number of times you copied and extended a Power BI data model to support your new needs and include your extended data?

These times are now gone – because the Power BI team introduced the concept of DirectQuery for Power BI datasets! yeah! yeah!?!?!?

But – I hear some of you say’in – this isn’t new – we could already live-connect or DirectQuery or how you named it – to a Power BI dataset (published to the Power BI service)! So this is not really new…

Live Connect

This is not really true, because in the past, you could only create a live-connection to an already published Power BI data set. And with the live-connection, you could only consume the existing dataset. No additional data sources could be added because in this scenario, Power BI (Desktop) served as a visualization tool only. The semantic model remained in the Power BI service.

Even the tooltip for the Power BI datasets connection told us, that we are going to create a live connection.

Create a live connection to a Power BI Dataset (from Power BI Desktop)

After the connection was made, Power BI Desktop (prior to the December 2020 release) looked like the following:

Power BI Desktop with a live connection to a Power BI dataset

In the status bar (1), you’ll find the information about the connection type (live connection) and the name of the dataset (Sales) and the workspace name (Blog Demos). The tables from the data model are displayed in the Fields list (2). Because of the live connection, all other options to add other data sources are not available (3). In this case, Power BI Desktop can only be used as a visualization tool – build reports based on the data set.

There are still some options to enhance the remote model – add measures, but that’s it.. no calculated columns, no hierarchies, no changes in the model itself.

The options available when live-connected to a remote Power BI dataset.

With the December 2020 release of Power BI Desktop, this approach changed. You are now able to change a live connection to a Power BI dataset (or an Azure Analysis Services connection) to DirectQuery mode. Which allows us, to enhance the remote model with new columns, tables, additional datasources and create relationships between the datasources.

Let’s dive deeper into this and look at the story together with a sample.

Let’s try it – Basic configuration

The DirectQuery support (for Power BI datasets) was introduced in December 2020 as a preview feature, which means you need to activate the option in Power BI Desktop to be available (File -> Options -> Preview features).

Power BI Desktop – Activate Preview Feature for DirectQuery

And now – Let’s create a composite model v2

After we’ve activated the option (and restarted Power BI Desktop), we are able to create a new report based on a published Power BI dataset. Again, select the dataset you want to work with and after the connection is achieved, Power BI Desktop (December 2020 and newer) looks like the following:

Power BI Desktop – December 2020 with the option to switch to DirectQuery

Please have a look at the menu bar – the icons to add more data sources are not greyed out plus there is a hint in the status bar: Make changes to this model.

And this hyperlink is the starting point into the future of Self-Service Business Intelligence! Power BI Desktop informs you, that a local model is required for DirectQuery to work..

Power BI Desktop warning before the DirectQuery switch

If you ask yourself, what a local model is for and how it fits to the overall structure, I will post a reference to the description of the Power BI roadmap item (read the entry here).

What I really like in that picture is the graphical representation of the data model extension. We have one (remote) Enterprise Semantic Model that gets extended by two local models. And these two models allow us to combine data from the two / three sources.

After you confirmed the dialog, Power BI Desktop changes the internal data model structure and changes the storage mode to DirectQuery (see the changed status bar).

In my example, my remote Power BI dataset is based on the Wide World Importers database and contains Sales information. In the Model View of Power BI Desktop, you can have a look at the structure of the remote data model.

The remote model

With DirectQuery mode enabled, we have all the option to extend, change and modify our data model. What we can do for example, is to add a column to calculate the line total (= Quantity * Unit Price)

Context menu options for DirectQuery models
Add a new column in a DirectQuery model

If you want to have a look at the calculation results you’ll see that there is no Data View option in Power BI Desktop available for this model. The data itself resides in the remote model and the local model is just here to add some more semantics and structures. If you want to test the calculation, add a matrix to the report page.

Testing the new column.. it seems to work

Let’s add some (Excel) Data

One piece of information is missing in our Sales data model – the list of Sales Employees is not included in the model. But – we have a list of our employees in an Excel file.

Power BI Desktop – Add an Excel data source

When you’ve done the required transformation steps, Power BI Desktop provides you with a warning, that potentially data from one source can flow into another data source (system). But why?

This is because Power BI needs some values from one part of your local data model (ie. sales person name) to use it as a filter in a query that relates into the Sales dataset.

Warning that data from one source can flow into another one

The local data model looks like the following screenshot after we’ve add the employees list. What is missing is the connection between our two data islands. What we need to do is to build a bridge (=relationship) between the Sales and the Employees table.

The local model (still disconnected data islands)

Easy as usual, just drag and drop the column (Salesperson Key) from one table (Sales) onto the target column (Employee Key in Employees table). Et voila – the relationship between the remote model and our local (imported) Excel data is established.

Local model – now it’s connected


What happened behind the scenes? When we switched the model from live-connect to DirectQuery, Power BI Desktop added a local model to the created pbix-file.

As depicted in the next diagram, the local model only contains a reference to the remote model (Sales)- no data is copied, it’s “just” a reference to the remote model.

The local model (Sales with Employees) is our composite model bringing together the Sales information with the Excel employees list.

Power BI composite models architecture

Test the model

To test the new model, I’ve added a new matrix to display

  • the LineTotal (=calculated column in the local model)
  • for every employee (coming from the Excel file to the local model)
  • for every calendar year (attribute out of the remote data model).
Power BI Desktop – test the composite model in a matrix visula

For us as Power BI users, this simple connection / relationship between the two data islands looks like a small feature but I know and heard from the Power BI team that this was not a small, but a huge engineering effort. And the Power BI team DID IT!

That’s it for today! I hope I could give you a small overview about the options that came into Power BI with the DirectQuery / composite Model v2 implementation.

Be aware – it’s in preview and there are still things to mention and not available in this preview version. Have a look at the blog post and the documentation for further information:

Source for the featured image: https://www.pexels.com/photo/selective-focus-photography-cement-2219024/

About wolfgang

Data Platform enthusiast
This entry was posted in Business Applications, Cloud, InformationSharing, PowerBI. Bookmark the permalink.

3 Responses to A New Era for Self-Service BI: Direct Query for Power BI DataSets

  1. Pingback: Power BI Composite Model V2 Demo – Curated SQL

  2. Pingback: DirectQuery for Power BI Datasets – What does Chaining mean? | workingondata

  3. Pingback: Chaining with DirectQuery for Power BI Datasets – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s