Create Simplified Composite Models using Power BI — Direct Query to Azure Analysis Service
In Power BI the DirectQuery option is generally available for Relational databases but not for the Analysis Service databases. The Direct Query to AAS is currently in preview and One can use this feature to connect to AAS and combine it with other data sources of their own, such as an Excel spreadsheet, also to enrich the metadata from their enterprise semantic model.
This is really a fantastic feature, but report authors were not completely satisfied with the feature as they don't have an option to select specific tables that they need from the AAS database. Microsoft heard the feedback from the users and announced an improvement to the Direct Query option to AAS.
Before this announcement, when you connect to the AAS model using DirectQuery connection, you don't see an option to select individual tables from the model. But now with the new release, users will get the flexibility to choose individual tables from the model, the below picture depicts the same
Let's see how we can enable the Direct Query option and connect to the AAS model.
Enable Direct Query Option
To enable this preview feature in Power BI Desktop
1. Open Power Bi desktop
2. Click on File > Options and settings > Options
3. Click on Preview features section and select the DirectQuery for Power BI datasets and Analysis Services checkbox.
4. Restart Power BI Desktop
Connect to AAS Model using Direct Query
Now when you connect to the Azure Analysis Service you should see the Direct Query option. Select Directly Query → Click OK
Earlier, with the DirectQuery connection from Power BI to an AAS model, the model was used to contain all the tables. But now with the new release, Report authors can get more control over the model and they can choose specific tables that they need from the model or remove a table from the model.
This is a great feature for the Report authors if we have a large model with hundreds of tables.
Select the tables you need and click on the Submit button and click ok if you see the below screen
Now in my local model, I see only the tables that I need for my reports, but not all. This way we can keep our model simple and small even if there are hundreds of tables in the dataset or model you are connected to.
Also, you can automatically add any table that might get added to the dataset or model after you connect to your model by selecting the below option (Include tables added later).
Hope this is informative, see you in my next article until then have a great time