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.
After the connection was made, Power BI Desktop (prior to the December 2020 release) looked like the following:
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.
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).
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:
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..
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.
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)
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.
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.
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.
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.
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.
Architecture
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.
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).
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:
- Power BI DirectQuery for Power BI datasets – https://powerbi.microsoft.com/en-us/blog/directquery-for-power-bi-datasets-and-azure-analysis-services-preview/
- Roadmap item for DirectQuery for Power BI datasets – https://docs.microsoft.com/en-us/power-platform-release-plan/2020wave2/power-bi/direct-query-over-power-bi-azure-analysis-services
- Use composite models in Power BI Desktop – https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models
Source for the featured image: https://www.pexels.com/photo/selective-focus-photography-cement-2219024/
Pingback: Power BI Composite Model V2 Demo – Curated SQL
Pingback: DirectQuery for Power BI Datasets – What does Chaining mean? | workingondata
Pingback: Chaining with DirectQuery for Power BI Datasets – Curated SQL