Power BI Composite Models using Analysis Services -Direct Query Mode
Microsoft released a new exciting feature for Power BI, which users have been waiting for a long time. i.e Direct Query against Analysis Services in Power BI ( still in preview)
So far we can connect to an Analysis Service using either a Live Connection or Import, but the Direct Query option is not available for Analysis Services Data Sets
With this new capability, we can connect to an Analysis Service model using Direct Query and extend its feature with the additional data sets ( composite models), customized tables, columns, and calculations. So exciting …right!!!
Now let's see how we can connect to Analysis Service using the Direct Query option,
Before we jump into the action, let's download the latest version of the Power BI Desktop(Download Power BI Desktop) and Install
Open the Power BI Desktop and enable the Preview feature Direct Query for Power BI datasets and Analysis Services, then restart the Power BI desktop
Click on Get Data and select Analysis Services, for this example, I am connecting to a sample Azure Analysis Service model called adventureworks
It's Not Working..ugh
You must be wondering why the Direct Query option is still not available, ok relax..this is a little tricky, the option is not directly available for selection, so first let's connect using Connect Live
Now you are connected to your Analysis Service Model, make sure you see all your tables in the Fields pane. We all know that the Live connection doesn't allow us to add additional data sources or a new column to the existing model, which is a very essential feature for a data analyst,
Now to change the connection to the Direct Query mode, Click on the Transform Data and then click on the Add a local model
Boom!!!, the connection is changed to Direct Query mode and now we can add new columns and bring additional data sources to this model…..how cool is that :-)
Add new columns to the Power BI Model
Select the table that you want to add a new column and click on the New Column
For this example, let's add a new column to the product table and see how it works..Click on New Column and add a new column called Class Description
Class Decsription = SWITCH( TRUE() , ‘Product’[Class] = “L” , “Low”, ‘Product’[Class] =”H” , “High” , ‘Product’[Class]=”M”, “Medium”)
See how simple it is to add a new column in the model without disturbing the underlying model in the cloud.
The new column is available now for us to use in the reports
Now let's see how we can create a composite model using the Analysis Service Direct Query option
Create a Composite Model in Power BI
To make this model composite, we have to bring an additional data source to the model
For this example, let’s add a file from Azure blob storage.
the above illustration represents a composite model using Azure Analysis Service and the Blob source
Uploaded a sample data file to the Azure blob storage as shown in the below picture
Click on the Get Data → Select More
Select Azure Blob storage and click connect → enter the Account Name or URL, and connect to the blob.
Load the data from the file → transform → click Close & Apply
The composite model is created and the table from the new blob source is ready to be used in the model
hooray!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
not so fast my friend, before we use the data from the new source we have to establish the relationship with existing tables in the model
To create a relationship, click on the Manage Relationships → Edit the relationship → Click OK
The composite model is now complete and the new dataset is all set to use in your reports.
I hope you enjoyed reading this article, see you in my next article, until then stay safe and enjoy the holiday season
