Microsoft is Ready to Unify Self-Service BI with Enterprise Models

“this is neither nice nor cool: this is huge: finally, can seal the marriage between self-service and corporate BI” – Alberto Ferrari (Source)

Okay, I know. I am quoting Alberto Ferrari (the DAX Maestro), but he is right. My last post on this blog was about the upcoming composite models being available in PowerBI. Now, I must correct one item: Composite Models were introduced in 2019, with PowerBI having the ability to DirectQuery several sources and building a localized model. This differs from the Live Connections, which do not allow us to combine more than one data source. But what Microsoft and its PowerBI Team announced on December 16th 2020, is massive: the ability to use DirectQuery for Power BI datasets and Azure Analysis Services.

image credit: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery

How will this change the approach to self-service BI?  

Up until now, sophisticated business users knew that they were able to consume pre-built datasets in PowerBI, some were even labeled with a ‘Certified’ or ‘Promoted’ label. Unfortunately, most of these datasets would serve the user with about 90% of the data they need, just missing those extra three columns from the local Excel file they desperately needed to add. Now, they can! And it is important that they do!

via GIPHY

This will increase the usage of the pre-built datasets in PowerBI, encouraging self-service users to use them as a trustworthy baseline, since in the past, when a user was missing data, they had to rebuild everything on their own, without utilizing previous and trusted work. I hope that more users will be encouraged to trust what BI analysts have built for them.

What is chaining? 

In addition, the PowerBI team knows that end-users will try to re-use the dataset they have just created by combining an existing dataset with their local Excel file. So, they have made it possible for users to chain the datasets. When you 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, which started with the  Sales Azure Analysis Services model and ends with your “Sales and Budget Europe Power BI dataset. See the image below:

There are many limitations and things to note, but I will refer you to the PowerBI Blog Post for further information.

How do we manage this?

Some of you might be wondering about the governance of this new feature and how it can easily become a nightmare to manage. It looks like the PowerBI Team in Redmond has already thought about this and promised that we will see some features that will address this.

• Using third-party tools, a “discourage chaining” flag can be set on a model to prevent a chain from being created or extended. To set it, look for the DiscourageCompositeModels property on a Model. We have plans to add this functionality to dataset settings in the Power BI service.
• What’s coming: Settings for limiting who can use DirectQuery for Power BI datasets and Azure Analysis Services in the Power BI service.

Nevertheless, be cautious about how you roll this feature out in your organization. Keep in mind as well, this feature is still only in preview, so some things will change based on feedback!

Exciting times are ahead for the PowerBI Community and Enterprise Analytics as a whole. Microsoft has done incredible work to push all these updates in 2020, so much so that we will need to tackle some more features in future blog posts. Stay tuned!

What are your thoughts on DirectQuery over PowerBI Datasets and Azure Analysis Services? I would love to hear them, send me an email at [email protected] and let’s talk!

Written By:

SEBASTIAN RICHTERS, DATA ANALYTICS LEAD NEWCOMP ANALYTICS [email protected]

Sebastian is an Analytics Consultant at Newcomp Analytics and our lead PowerBI developer and trainer. He is a trusted analytics advisor for some of the largest companies in Canada, where he spends his time developing analytics roadmaps, solution architectures and leading proof of concepts.