Add columns in Power BI connected to an Azure Analysis Services model
Make the self service Power BI experience better
Direct Query and compositie model
I’m very excited about the new possibilities of DirectQuery and composite models in Power BI. For me, the possibility to add columns to tables on top of an existing Tabular model, could be a game changer in the usage and adoption of Power BI as an enterprise self service tool.
One of the cons of connecting Power BI to an Azure Analysis Services model via live-connection was that, while (power) users could add their own measures, they could not add their own columns or groupings. When they had found out that a particular grouping of the data better suited their analysis, they had to ask a member of the ACE (Analytics Center of Excellence) to add this to the model. But since the introduction of composite models, this is no longer needed.
Activate Composite Model for your Analysis Services
Activate the preview function ‘DirectQuery for Power BI datasets and Analysis Services’ in Power BI Desktop. After a required restart of the application you are able to use this new feature. More information can be found in the Learn more.
When your Power BI is connected to a Analyses Services database via live connection you are used to the fact that all other get data functionality in the ribbon is grayed out. But after activation of the preview function you are now able to connect to other sources besides the existing connection. Doing so, by connecting to an Excel or Enter data manually, will trigger a warning that the live connection will switch to a DirectQuery connection and add a local model to the Power BI.
Add new column to a table on top of the existing model
Now you are able to enrich the data provided bij the existing model by adding new columns based on your own logic or adding customs groups as was already supported by Power BI data connections in import mode.
This really helps your (power) users get the most value out of their analyses, while still having all the benefits of reporting on a central maintained data model.
Be sure to also keep in close contact with your (power) users to establish a feedback loop and incorporate useful aggregations and business definitions back into the Azure Analyses Service model so they can be used in multiple reports within the organization.
Limitations
There are limitations to take into consideration and this will not work for all models yet. DirectQuery does limit the performance and you’ll have to test if working with your model in this mode will have to much of an impact.
Also there are modeling and security limitations, for which you will have to test functionality of your model after the switch to DirectQuery.
References:
Microsoft documentation:
