The Impact Factor of Power BI External Tools
IN THIS ARTICLE
When discussing external tools, we often reflect upon their need from a development point-of-view. Most users making their own datasets from Excel files don’t use or need external tools; there are likely many more datasets which have not been modified via XMLA read/write than those who have. However, many of these self-service datasets often have smaller reach & impact in an organization. Larger enterprise models have wider distribution and are commonly thought to require more advanced development, and thus external tools.
In this article, I reflect on the importance of external tools & open source development for Power BI. With regard to measuring how important a tool, feature or artifact is, I explore the need to consider the end-to-end impact factor over the primary usage metrics, to more accurately reflect the need it fills.
Helicopter view of a Power BI dataset ecosystem. Since External Tools are used in Enterprise Datasets, they have a higher impact factor.
Introduction: The Need for External Tools
The Challenge for External Tools
Measuring use & need of features - thinking about impact factor instead of primary use
THE NEED FOR EXTERNAL TOOLS
When scaling a Power BI solution, there comes a point where the out-of-the-box Power BI tools are not able to meet all challenges & needs. This is particularly true among enterprise solutions serving many users from a certified truth. Use-cases like batching/automating tasks, advanced DAX or model development, model optimization, source control, or even advanced or extended visualizations require use of third-party solutions to avoid costly custom development. For data model development, these external tools are commonly known among the Power BI development community. While some tools like Tabular Editor 3 and Power BI Sentinel are commercially available, many tools like DAX Studio, Tabular Editor 2, Bravo and ALM Toolkit are open-source - free; produced, maintained & distributed by generous stewards of the Power BI community.
External tools like this are increasingly needed in order to effectively scale & manage a Power BI data model. This is something even acknowledged by Microsoft, where the docs list several open source tools that “…belong in every Power BI Desktop data modelers [sic] toolbox”. This usage scenario - referred to as Advanced data model management - is commonly believed to be more frequently observed. This is perhaps also why external, open-source tools have even been integrated as a measured skill for the new DP-500 certification exam, which tests “…when to use external tools, including Tabular Editor 2 and DAX Studio”. These tools don’t just unlock better scaling, efficiency & productivity, but also fill feature gaps in the out-of-the-box Power BI offering. For example, creation of calculation groups and perspectives are not possible in Power BI Desktop, and source control only works when extracting model or report metadata using tools like Tabular Editor for datasets and pbi-tools for thin reports.
“We’ve seen countless success stories in large enterprises, including here at Microsoft, where using these tools has significantly improved and accelerated solution development. The message is clear, community and third-party tools are here to stay.”
Despite this, there exist two significant challenges for external tools with Power BI:
Difficulties adopting third-party tools among some organizations
Power BI “Desktop Hardening”, or having .pbix contents as a supported public API
THE CHALLENGE FOR EXTERNAL TOOLS
ADOPTION AMONG ORGANIZATIONS
As written above, external tools are third-party; they are not from Microsoft. They do not have official endorsement outside of the linked articles / documentation, which might be interpreted as implicit. Organizations new to Power BI or scaling for the first time might be confused about this. They might ask why they should use third-party tools to get what they might interpret to be the ‘complete’ enterprise Power BI development experience (all of the features & utility). Questions posed might be -
“Why are there features we can only use with tools not from Microsoft?”
“If this tool is for some reason discontinued, how do we maintain or use these features?”
“If these tools are so valuable & necessary, why aren’t they from Microsoft?”
Many organizations might exhibit skepticism either because they are unfamiliar with the tool’s origin or practical need, or because they are open source, or because the organization has valid security & compliance concerns.
MAKING A CASE
Without formal endorsement, it is often up to the Power BI developer to convince IT of the value of these open-source tools. The developer is usually aware of their value, and needs permission to either install them, or to convince IT Admins to enable XMLA read/write on the service. To do so, the developer can present arguments like the below:
Microsoft sees use of external tools as a necessary skill & legitimate development scenario
External tools can reduce development time and thus cost
External tools unlock features otherwise unavailable with out-of-the-box Power BI Desktop, i.e.:
Calculation Groups (Tabular Editor)
Perspectives (Tabular Editor)
Object-Level Security (Tabular Editor)
Metadata Extraction & Analysis (VertiPaq Analyzer)
Custom Partition & DetailRows definitions (Tabular Editor)
Source & Version Control (Tabular Editor, pbi-tools, Power BI Sentinel)
External tools can improve the quality of what is made vs. with Power BI Desktop alone, i.e.:
DAX Code-Assist (DAX Studio, Tabular Editor)
Optimization (DAX Studio, Report Analyzer, VertiPaq Analyzer)
Best Practice Detection & Analysis (Tabular Editor, Report Analyzer)
However, this Power BI developer might end up playing middle fiddle to security questions about the tools that they can’t easily answer, requiring communication with the tool developer or digging into API documentation beyond their expertise. What’s more challenging, they might face the question “Is this really supported by Microsoft? What if something running breaks because of changes made in an external tool?”
DESKTOP HARDENING
What is or is not supported cannot be addressed without touching on the concept of ‘Desktop Hardening’. There is a great post by Matthew Roche that explains well what desktop hardening is. To quote directly Matthew’s definition:
“Desktop hardening is the internal shorthand used by members of the Power BI product team to describe a broad set of ongoing investments to expose the full contents of the Power BI Desktop PBIX file format as a supported public API.”
I suggest you visit & read Matthew’s excellent blogpost if you want to read more. To summarize, the idea is to provide programmatic read/write support for the full contents of the .pbix file, which is planned, but is a complex process. While tools can modify dataset metadata in the Power BI service in full, a set of write limitations exist for modifying .pbix file (metadata). Further, datasets modified via the XMLA endpoints cannot be downloaded to .pbix files, nor can reports created in the service (either manually or via deployment pipelines). It seems to be the intent to support this fully in the future. However, presently (June 2022) this is not yet the case; many operations are not yet supported.
This is an obstacle to external tools as so long as Desktop Hardening exists. In rare cases, tools may wander into unsupported territory to provide in-demand features. For example, they might have features that work only with the service, but not Power BI Desktop. Further, some organizations will respond with a hard “no” immediately when hearing that a .pbix or .pbit cannot be retrieved from datasets modified with XMLA endpoints, even if it’s not needed when the model metadata is used in entirety to manage the solution.
WHERE WE’RE GOING
So external tools are not just valuable, but essential for enterprise Power BI development. But to use external tools, you must overcome organizational adoption challenges, and the tools themselves face challenges with Desktop Hardening. To solve these challenges, two things may help:
First, endorsement and dialogue about open-source development in the Power BI solution space.
Second, progress on desktop hardening, which is planned, though not dated.
The first point is particularly important when reflecting on the sustainability of all this - without explicit support, can we expect third-party developers to continue to freely support these open-source tools? Is that fair?
HOW IMPORTANT IS THIS?
So, should this be a priority? External tools clearly have a place in the Power BI Development process, but how prevalent are they, really? How many users need or use these features & tools?
With a low-code, self-service tool, are most users relying only on Power BI Desktop, anyway?
QUESTION, HYPOTHESIS & MEASUREMENT
When considering a feature or tool, decisions are often prioritized by usage data. For example, that report with 100 users has a higher priority than the report with 2 users. For reports, looking at usage is an easy way to couple the asset and it’s impact. For features within reports or datasets, this is more difficult. Do you measure how frequently creators use these features, or the downstream, “inherited” impact, by looking at the usage of assets that use those features?
Circling back to external tools, do we measure how frequently XMLA read/write is used vs. all dataset artifacts, or do we look at the usage impact, downstream?
A QUESTION
“What is the impact factor of external tools in the Power BI solution space? ”
The purpose of this question is to understand the scope of external tool usage in an organization’s Power BI workflow. This an important question, as a good answer would highlight objectively the prevalence and value external tools have; to what extent an organization’s successful adoption of Power BI is supported by external tools. To ask it more scientifically - are organizations whom adopt external tools more successful with Power BI implementation & adoption than those who do not?
Lastly, on a broader scale, the answer to the top question could make more clear the need to support & acknowledge key tools.
A HYPOTHESIS
Based on the increasing interest in external tools among the community, organizations & in Microsoft documentation, one might hypothesize that external tools are increasingly important for successful Power BI implementation & adoption; they have a high impact on successful Power BI implementation.
But this is just a hypothesis. How would we test it?
MEASURING IT
Surely, we can just look at telemetry; at usage? The number of datasets modified with XMLA endpoint - or the number of .pbix files modified this way - versus the number that are not?
Below is a hypothetical scenario of 10 datasets to illustrate
We might then find that of 10 datasets made by 10 creators, only 1 has been modified with external tools. Thus, we may conclude that external tools are rare; the impact is small on the userbase compared to other things. 90% of our users do not use external tools.
But there’s a problem with this approach; it hides the real impact.
First, this pools together usage scenarios; it considers equally all datasets created both in enterprise and self-service scenarios. Enterprise scenarios are where certified datasets are created from the center-of-excellence (CoE) or IT to deliver broader-distribution of reports, or for business user consumption in Power BI or Excel. Self-service datasets are usually created either for personal or team analysis by a business end-user, either fully or as a composite model from the certified dataset.
To measure impact of external tools, we need to also consider the inherited impact on composite models. In the previous example, 2 of our users used the first dataset - made with external tools - in a composite model. They are thus indirectly impacted by the external tool; those calculation groups they are using or referencing weren’t made in Power BI desktop. Now, only 7 of 10 datasets have no impact by external tools.
However, this still is a far from accurate depiction of the impact external tools have in our Power BI space. Ultimately, successful Power BI implementation comes down to end-user adoption. Defining any metric without considering the impact on end-users divorces it from the real endpoint; the real value.
So if we use Activity Logs to look downstream at the usage of these datasets, we get much more interesting and valuable results. Counting the number of users who consume from these datasets in the last 14 days (in reports, consumption experiences, etc.), we can see the real impact these datasets have.
Taking an impact factor-based approach, we can understand the potential problem with the statement “most users don’t need external tools” - if we look at the real adoption endpoint, our end users, it’s not necessarily true.
In this hypothetical example, most of our users do need external tools, but indirectly; without external tools, they might not have the reporting or model features they are using or need on a daily basis, or they wouldn’t have new reports & data delivered at the same cadence they are used to. Even this scenario doesn’t take into account when external tools are used for auditing & monitoring self-service datasets. For example, CoE teams using Tabular Editor’s Best Practice Analyzer with the command-line interface to automatically scan datasets for issues, or auditing / collecting data with the Vertipaq Analyzer.
In a single organization, this is also important to know. It’s important when we reflect on tools & features that we understand the inherited impact factor they have in our organization, from creators to consumers. From a governance point-of-view, we can use this approach when appraising data artifacts (datamarts, dataflows & datasets) in addition to whether we want to disable settings in the admin portal. If only one user is using Python / R visuals, but their report page using that visual serves 500 users, the impact is 500 people, not 1 person.
TO SUMMARIZE
TAKE-AWAY MESSAGES
External tools are increasingly an important part of a Power BI solution
Many tools are open-source; it’s important to support open-source developers for Power BI
Some organizations struggle to adopt external tools, endorsing certified tools might help
‘Desktop Hardening’ limits what some tools can do without providing unsupported features
When debating a tool, feature or asset, consider not only its immediate usage, but the full end-to-end impact factor it has in an organization.
A big thank you to all open source developers of Power BI tools
