UPDATE 2023-09-09 This article is still current in 2023 after original publishing in 2021. Microsoft now references external tools like DAX Studio and Tabular Editor in its documentation: Power BI usage scenarios: Advanced data model management.

 

Microsoft recently announced the more recent improvements to make Power BI Premium a superset of Azure Analysis Services. While a few of the features are not yet available in Power BI Premium – and that may stop a few companies from migrating to Power BI Premium – the licensing available in Power BI Premium Per User is making the transition a no-brainer for many small-to-medium companies.

You can watch the recorded Development Tools for Power BI and Analysis Services models – Live Event with Marco Russo, Daniel Otykier, Darren Gosbell, and Alberto Ferrari. They discussed the past, present, and future of the development tools for Power BI and Analysis Services models with the authors of the more important tools out there: Tabular Editor and DAX Studio!

The development tools to create enterprise-level models for Analysis Services and Power BI require some clarification. In a specific post on the Power BI blog, Microsoft explains that community and third-party tools are a fundamental part of the toolbelt required to develop enterprise models in Power BI and Analysis Services. This article describes the role of these tools in more detail, and why they are required to access advanced features and improve the productivity of the development process.

Executive summary

The development of enterprise-level models for Power BI Premium and Analysis services requires both the Microsoft tools and a set of third-party tools – most of them open-source – to get the job done. Microsoft offers a rich, scalable and enterprise-ready backend technology, which relies heavily on third-party tools to take advantage of all of these enterprise features.

Since the introduction of Power BI, Microsoft has focused its tooling effort on the self-service, entry-level authors. This singular focus on ease of use and on quick results is still a major driver for the spectacular success of Power BI. However, this same focus left the professional developers wanting tools more suited for large and complex enterprise projects, and that can take advantage of the full range of features of Analysis Services and Power BI Premium.

This need was met by a collection of open-source, third-party tools that are now considered by the broader community – and even by Microsoft – an indispensable part of the enterprise development story of Power BI and Analysis Services. In fact, the success and popularity of these third-party tools for professional developers allowed Microsoft to reduce its investments in tools for professional developers, prioritizing new features that increase Power BI adoption rather than providing an incremental advantage to professional enterprise BI developers.

Tabular Editor and DAX Studio are two tools that are fundamental in order to create, maintain, and optimize an enterprise-level Tabular model. Both DAX Studio and Tabular Editor use libraries provided and supported by Microsoft. However, the tools themselves are neither built nor supported by Microsoft.

Participating in crowdfunding for DAX Studio and buying the commercial version of Tabular Editor is an essential step to ensure the continuity and support of the existing ecosystem.

A short history of Microsoft development tools

In the first wave of products for Business Intelligence, Microsoft provided a single comprehensive tool called Business Intelligence Development Studio (BIDS) to develop models, reports, and ETL modules. BIDS was initially released in 2005 as an extension of Visual Studio, and it is still available today.

With the advent of self-service BI, in 2010 Power Pivot for Excel introduced a different development environment. Part of the development experience moved to a new Visual Studio extension created to edit Tabular models, which was initially part of SQL Server Data Tools (SSDT). This tool is now known as Analysis Services Projects. If you want to create a model for Analysis Services or Power BI Premium with a Microsoft tool today, you use this Visual Studio extension.

In 2015, Microsoft introduced a new development tool to create models with the first release of Power BI. The target audience of Power BI was the same as Power Pivot, if not larger: report users. It is worth repeating this: Power BI primarily targets users, not developers. Indeed, the user interface of Power BI is simple and it does not provide access to many details, usually required only for large Tabular models developed in Visual Studio. At this point, the development ecosystem had different Microsoft tools targeted to different users. At the same time, the availability of API, libraries, and documentation also favored the development of community and third-party tools that increased the productivity of model authors.

The success of Power BI also increased its adoption for professional BI developers, in enterprise-level models. Power BI is great to build the first prototype – but at some point, it is necessary to control more details than what is available in the Power BI user interface. While Visual Studio was the tool of choice for an enterprise-level model created in Power BI, it does not support other specific Power BI features only available through Power BI Desktop. However, this is not a concern thanks to a rich third-party tools ecosystem that provides access to all the advanced features available in Analysis Services and Power BI.

While third-party tools are not directly supported by Microsoft, they are based on supported .NET libraries provided by Microsoft to modify the model for Analysis Services. Since July 2020, these libraries can let external tools work in a better-integrated way with Power BI Desktop. Finally, since January 2021 external tools can deploy and edit models published on Power BI Premium through XMLA endpoints.

The three most established tools are DAX Studio, Tabular Editor, and ALM Toolkit. Thanks to these tools, Microsoft can focus its development effort on features that can widen the adoption of Power BI to a broader audience. These development efforts include complex and advanced features such as the new composite models.

The days of a single comprehensive tool are gone. The Power BI ecosystem is made up of a large variety of model authors, report authors, and business users, with many people playing several of these roles at the same time. Asking for a single monolithic tool serving all these different needs is not possible. The right approach is to choose the tools that provide access to the required features in the most productive way.

Looking for support

Several successful companies have already adjusted to this new normal: not a single monolithic tool, but rather a set of tools provided by different developers / vendors. Some companies refused this approach: they want to use only Microsoft tools for Tabular model development because they do not want to lose their access to Microsoft support. This position highlights a lack of understanding of reality: not only are several enterprise features not accessible through Microsoft tools, but Microsoft must also support any valid model deployed according to the technical specification and the libraries described in more detail later in the article. You do not lose access to Microsoft support if you use Tabular Editor or ALM Toolkit to modify a model.

The problem today is not the lack of support by Microsoft. The real question is whether these third-party tools will continue to be developed and supported. In the current ecosystem, most of these tools are open-source, whereas some tools are starting to shift towards a paid model. The situation for the three tools we mentioned before is the following:

  • DAX Studio: open-source, free, maintained by Darren Gosbell and another nine minor contributors. Crowdfunding is now available to support the development.
  • Tabular Editor: open-source, free, maintained by Daniel Otykier and another nine minor contributors. A new commercial version is becoming available by June 2021, with additional productivity features and support provided by Kapacity.
  • ALM Toolkit: open-source, free, maintained by Christian Wade and other contributors. Christian Wade is a Principal Program Manager at Microsoft.

 

Microsoft only contributes to one of these tools, ALM Toolkit. ALM Toolkit covers only a small part of the development cycle by comparing and merging different versions of the same semantic model. DAX Studio and Tabular Editor do not benefit from Microsoft’s resources – be it developers or funds. Two different economic models sustain their development: crowdfunding and a commercial version with additional productivity features.

Companies that create Tabular models cannot rely on Microsoft for the support of these tools. The best choice is to fund their development by contributing to the crowdfunding and / or purchasing the commercial version of the tool they use.

The remaining part of the article explains in more detail the statements made so far. We invite you to read that part before writing comments, or if you need more elements to sustain a budget request for the development tools you need.

What is a Tabular model?

A Tabular model is a set of metadata like tables, relationships, measures, KPIs, calculation groups, hierarchies, translations, security roles, and many other elements that form the “semantic model” used to provide a navigation system in client tools like Power BI and Excel reports.

A model corresponds to a dataset in the Power BI service and to a database in Analysis Services. Datasets and databases are models populated with data. The definition of the model without any data is the “Tabular model” we discuss in this article.

The Tabular model is defined in structures and protocols documented by Microsoft:

By following these specifications, anyone can create a model and communicate with the backend server without using an existing tool. There is nothing stopping someone from creating an application that runs on operating systems other than Windows. However, creating the communication stack that implements the technical specifications is a large investment.

As of today, Microsoft provides the following Windows libraries that allow the creation / deployment / querying of Tabular models:

In February 2021 Microsoft released these libraries for .NET Core. Therefore, the communication stack is now available for operating systems other than Windows.

Thanks to these libraries, it is possible to write scripts in many languages supporting .NET Core (including PowerShell) that create and deploy Tabular models on Analysis Services and Power BI Premium through XMLA endpoint.

The TOM library can save the Tabular model in a JSON file usually called “model.bim”, which contains the serialization of the object model representing the Tabular model itself. The TOM library can also load a “model.bim” file and deploy it to a Tabular server. By using the TOM library, anyone can create a “model.bim” JSON file and deploy it on Power BI Premium and Analysis Services, not only using Windows but also with Macintosh and Linux operating systems.

Thus, a Tabular model is a “model.bim” file, a JSON file that you can open with Notepad. The simplest development tool for a Tabular model is a text editor. As long as the JSON file is valid, deploying it through the TOM library is fully supported by Microsoft. This is a first, important clarification.

A development tool for Tabular models is an application that manipulates a “model.bim” file and possibly deploys it to the server without requiring additional actions from the developer. Writing a “model.bim” file in a text editor is not a pleasant task, but it is feasible.

Existing Tabular model development tools

As of April 2021, the existing development tools for Tabular models are:

  • Power BI Desktop
  • Visual Studio (with Analysis Services Projects)
  • Tabular Editor

Both Power BI Desktop and Visual Studio are Microsoft products. They are available for free and in both cases the approach requires an active instance of Analysis Services to edit the data model. This active instance is called a “workspace”.

When you use Power BI Desktop, you also edit the reports and the Power Query transformation to load data; however, for the purpose of this article we only consider the Tabular model, which is made up of the Data view and the Diagram view of Power BI Desktop.

Visual Studio offers the choice between an integrated workspace like Power BI Desktop, and a separate workspace which can be any instance of Analysis Services. The relationship between workspaces and development tools is described in an unplugged video we have recently published, Tabular tools and workspaces.

The workspace is constantly updated by Power BI and Visual Studio during the editing of the model. This constant synchronization requires the workspace to be “close” to the editor in order to reduce the communication latency. For this reason, the integrated workspace in Visual Studio is usually the preferred choice. The advantage of a constant synchronization is that the model is always up to date, and can be queried to check whether the change that was applied works as expected. However, when a Tabular model contains hundreds of objects like tables and measures, the synchronization process pauses the development environment, which has to wait for the workspace to respond before allowing any further edit. When the synchronization time is above one second, the impact translates into lower productivity of the developers.

Tabular Editor is a tool available in two versions:

While Tabular Editor 3 has more advanced features and a better user interface, both versions share the same approach: they edit the “model.bim” file in a “disconnected” way. Connecting the editor to a workspace is an option, it is not mandatory. Moreover, even when there is a workspace connected, the synchronization happens only on-demand and it is not enforced for every action made on the editor. This approach is a productivity booster when working on large Tabular models.

The other important difference between Tabular Editor and Microsoft tools is that the former covers any possible deployment scenario, whereas the latter are not so flexible and complete. This is described in the following section, “Development scenarios”.

Two other tools should be part of the developer toolbelt:

  • DAX Studio provides part of the features offered by SQL Server Management Studio (SSMS) and SQL Server Profiler. You can write DAX queries, check their result, and analyze their query plan and performance. DAX Studio also integrates other features like data export, query builder, and VertiPaq Analyzer.
  • ALM Toolkit compares two Analysis Services databases or Power BI datasets, showing the differences and merging the changes if required.

DAX Studio and Tabular Editor are used by thousands of users every day. The Microsoft documentation mentions both these tools and ALM Toolkit.

Development scenarios

The choice between Power BI Desktop and Visual Studio should be made at the very beginning of a project, because it is not possible to switch between them once a project has begun. While this might not seem an issue at the beginning of a small project, the switch might become necessary at a more advanced development stage. Tabular Editor is more neutral from this point of view and can be used at any time of the development cycle. A few examples can clarify the previous statements.

Migrating from Analysis Services to Power BI Premium

A Tabular model created in Visual Studio cannot be edited in Power BI Desktop. It is possible to deploy a model from Visual Studio to Power BI Premium. The backup of an Analysis Services model can be restored in Power BI Premium starting in April 2021 (the feature is not available at the time of writing). A model published from Visual Studio or restored from an Analysis Services backup cannot be edited in Power BI Desktop. Therefore, features available only in Power BI Desktop (such as Incremental Refresh) are not accessible using Microsoft tools.

Tabular Editor can edit a published model in Power BI Premium and can modify the properties for Incremental Refresh. However, in order to access Incremental Refresh it is necessary to make sure that the compatibility level of the Tabular model is 1450 or higher. If the compatibility level must be updated, it requires a deployment of the model as a new database from Tabular Editor.

Migrating from Power BI Premium to Analysis Services

A model published on Power BI Premium from Power BI Desktop cannot be edited nor imported in Visual Studio. Therefore, it is not possible to migrate a model published on Power BI Premium to Analysis Services.

By using Tabular Editor, you can extract the Tabular model published on Power BI Premium, but the enhanced metadata format used by Power BI is not currently supported by Analysis Services. A manual manipulation of certain database properties and data sources is required in order to obtain a model that can be published on Azure Analysis Services, whereas publishing to an on-premises version of SQL Server Analysis Services could be more difficult because of the compatibility level and the larger number of manual fixes required.

Creating custom partitions on Power BI Premium

If you create a model in Power BI Desktop, the only way to manage partitions is through the Incremental Refresh feature. Power BI Desktop does not provide any control over the partitions. Once the model is published, it is possible to manage partitions using SQL Server Management Studio (SSMS) or by creating a PowerShell script using the TOM libraries.

If you create the model in Visual Studio, you can customize the partitions. As explained earlier, the model deployed to Power BI Premium this way cannot be edited in Power BI Desktop.

Tabular Editor does not come with any of these limitations. It can manipulate partitions on any Tabular model, regardless of the server and the compatibility level.

Deployment to Power BI Premium through XMLA endpoint

Any change applied through XMLA endpoints (using Tabular Editor, SSMS, PowerShell, or any other tool) to a model published on Power BI Premium with Power BI Desktop makes the model no longer editable by using Power BI Desktop. In other words, once you publish a model to Power BI Premium using Power BI Desktop, the only way to edit it in Power BI Desktop is by editing the original file or by downloading the PBIX file from Power BI.

However, the download of the PBIX file is possible only if the original model did not use incremental refresh and was only refreshed without any change made through XMLA endpoint. Moreover, if the storage format for the Power BI Premium workspace is the “large dataset storage format”, then the download of the PBIX file is never possible.

Team development and source control

Neither Power BI Desktop nor Visual Studio allows concurrent editing of the same data model. Merging the changes applied to the same “model.bim” file by multiple developers is not possible using the standard Microsoft tools.

ALM Toolkit is a tool that compares two models and generates a file to deploy their differences. Tabular Editor can split the model into multiple files (one for each object) thus enabling better integration with a standard source version control system like GitHub.

Accessing Tabular model features

Another area of comparison between the development tools is the ability to access Tabular model features. In general, both Power BI Desktop and Visual Studio do not expose all the Tabular Object Model (TOM) properties, for different reasons. The only tool that provides complete access to these properties is Tabular Editor. The alternative is the manual manipulation of the “model.bim” file with a text editor, which is possible only by using Visual Studio and not when using Power BI Desktop.

These limitations affect several enterprise-level features:

Creating calculation groups

Power BI Desktop does not support calculation groups directly. By using Tabular Editor as an external tool, you can edit a Power BI Desktop model and manage calculation groups.

Visual Studio supports calculation groups, even though the user experience is not ideal: for example, controlling the sort order of the calculation items in a calculation group requires adjusting the Ordinal property manually, whereas Tabular Editor manages that property automatically.

Many-to-many cardinality relationships

The many-to-many cardinality relationships have been available since Analysis Services 2019. However, the relationship editor in Visual Studio does not provide the ability to change the filter direction. The default is “Both”, which is seldom the appropriate choice. The filter direction can be modified in Power BI Desktop.

One of our customers (Jürgen Faßbender, Orbit Gmbh) wanted to use Visual Studio to control this setting, but he was not able to do so: “We opened a support ticket as you suggested, but Microsoft closed the issue without delivering a fix. The workaround provided is editing the model.bim manually, or use a different editor like Tabular Editor.“

Metadata translations

Power BI Premium supports the translation of Tabular model metadata, but Power BI Desktop does not provide any user interface to access this feature. Visual Studio only allows the import / export of translation files. Tabular Editor can import / export translation files like Visual Studio can, and it can also edit the translations in the Tabular Editor user interface.

Object-level security (OLS)

Power BI Premium supports the Object-level security feature, but Power BI Desktop does not provide any user interface to access this feature. Both Visual Studio and Tabular Editor support the OLS feature.

The future of Microsoft development tools

After reading this long article, you probably have one question: Why is Microsoft not providing the tools we need?

One of the reasons is the presence of the developer tools ecosystem described in this article. As described in the Power BI blog by Amir Netz (CTO Power BI): “The presence of tools that satisfy the requirements of more advanced developers makes it possible for us to prioritize new features that increase tool adoption rather than providing an incremental advantage to a small number of existing customers.”

Thus, though Microsoft is going to address the lack of tools for entreprise-level models, it is a long-term plan that does not affect developers who manage large and complex Power BI and Analysis Services models in 2021 and 2022. However, Microsoft provides the support (libraries and documentation) required to create third-party tools that fill the gap.

Many of these tools started as free open-source projects; their growth now requires a sustainable economic model, which is also a better guarantee for support and future upgrades. Increasing the resources to develop these tools will also increase the features and productivity gap between Microsoft tools and third-party tools, making the former less appealing and more expensive to build to remain competitive with the latter.

Conclusions

Managing enterprise-level models requires the use of Tabular Editor to access all the features available in Power BI Premium. Every time a model is created initially in Power BI Desktop, Tabular Editor is the only way to access several advanced enterprise features. DAX Studio is equally important as a tool to write and optimize DAX queries and measures.

Starting a project in Visual Studio is an alternative that provides access to more features that also exist in Analysis Services – although this way it is not possible to access specific Power BI features such as Incremental Refresh.

Tabular Editor is the only tool that guarantees access to all the features of a Tabular model under every possible condition. This is achievable because Tabular Editor exposes the features provided by the Microsoft libraries. While it would be possible to use a text editor to achieve the same goal, the productivity that is possible using Tabular Editor is superior.

In this article, we did not cover the specific features in Tabular Editor 3 that improve the productivity of a developer and justify the purchase of the commercial version. However, all the features described in the article are also available in Tabular Editor 2.

We suggest moving the development of enterprise-level Tabular models to Tabular Editor, and choosing the version (free open-source or commercial supported) according to the company’s productivity requirements and budget. We also suggest adopting DAX Studio to optimize data models and DAX expressions, as well as participating in crowdfunding to support its development.

Note: Donations made to DAX Studio do not go to SQLBI. SQLBI supports DAX Studio by donating to its development and by sponsoring it as a gold sponsor.