Announced last week is a major new feature for Power BI: you can now use DirectQuery to connect to Azure Analysis Services or Power BI Datasets and combine it with other DirectQuery datasets and/or imported datasets. This is a HUGE improvement that has the Power BI community buzzing! Think of it as the next generation of composite models. Note this requires the December version of Power BI Desktop, and you must go to Options -> Preview features and select “DirectQuery for Power BI datasets and Analysis Services”.
You begin with a Power BI dataset that has been published to the Power BI service. In Power BI Desktop you connect to the dataset, where you can build a new model over the original one. You can extend the original model by adding tables, columns, and measures, and you can also connect to other datasets and combine them into a single semantic model. While doing this you do not lose any elements from the original model – measures and relationships continue to work. You do not have to worry about anything but the additional data you want to integrate. When the original model is refreshed, your local model also sees any updated information. You work as if you have a local copy of the model and full rights to modify and expand it, even though you are not duplicating any data already stored on the server.
This feature is ideal for report authors who want to combine the data from their enterprise semantic model with other data they may own like an Excel spreadsheet, or who want to personalize or enrich the metadata from their enterprise semantic model. This seals the marriage between self-service and corporate BI.
The main technology that makes this work is DirectQuery storage mode. DirectQuery will allow composite models to work with live connected sources and other data sources like Excel or SQL Server.
Using DirectQuery for Power BI datasets and Azure Analysis Services requires that your report has a local model. You can start from a live connection to an existing dataset and upgrade to a local model, or start with a DirectQuery connection or imported data, which will automatically create a local model in your report.
Live connection is basically a connection to the remote model (the model is not inside of Power BI Desktop). Converting the remote model to DirectQuery gives you a local model in Power BI Desktop. So if you want to make changes to your live connection, you will first convert to a DirectQuery connection. If you don’t need to make changes to the remote model and just combine it with other models, you can keep the remote model as a live connection.
Keep in mind that when you publish a report with a local model to the service, a dataset for that local model will be published a well. This is the same behavior as when you publish a report with imported data to the service.
When connecting to a remote model, the data for that model is kept in the cloud, and you can join it with another local model with it’s data to create new columns, new measures and new tables without ever moving the data from the remote model to your local PC. It’s an easy way to extend that remote model, which could be managed by IT and refreshed all the time. You are just responsible for managing and refreshing your local model and data. This is how you are combining the efforts of enterprise IT and end-users.
With this new feature I can see many companies creating enterprise semantic models in Azure Analysis Services or in a premium instance of Power BI. These semantic models can have the entire business logic of your company using the huge amount of data from your data warehouse. Then users can use DirectQuery against those models and extend those models locally with their own calculations, without having to download any data from the semantic models to Power BI Desktop. This is definitely a game changer.
This new feature allows you to do dataset chaining. Chaining allows you to publish a report and a dataset that is based on other Power BI datasets, which was previously not possible. Together, datasets and the datasets and models they are based on form a chain.
For example, imagine your colleague publishes a Power BI dataset called Sales and Budget that is based on an Azure Analysis Services model called Sales, and combines it with an Excel sheet called Budget.
If you create and publish a new report and dataset called Sales and Budget Europe that is based on the Sales and Budget Power BI dataset published by your colleague, making some further modifications or extensions, you are effectively adding a report and dataset to a chain of length three (the max supported), which started with the Sales Azure Analysis Services model and ends with your Sales and Budget Europe Power BI dataset:
This opens a whole new world of possibilities. Before this feature was available, to modify a dataset you would have to get a copy of the pbix file with the dataset and make your own pbix copy, which would also include the data. Also, you were not able to chain models together or to combine datasets (i.e. making models of models). This is quite an improvement!
Share your feedback on this new feature at this Power BI Community forum post.
More info:
New composite models in Power BI: A milestone in Business Intelligence
Power BI Direct Query Composite Models = Amazing
Composite Models Gen 2 and DirectQuery over Power BI Datasets
Power BI Composite Models using Analysis Services -Direct Query Mode
Composite models over Power BI datasets and Azure Analysis Services
“Composite” arrived – CAUTION!
Prologika Newsletter Winter 2020
The post DirectQuery for Power BI datasets and Azure Analysis Services (preview) first appeared on James Serra's Blog.
