Problem
For most BI professionals and Power BI developers, developing tabular models and Power BI reports starts and ends with using Power BI desktop or other Tabular Model development environments like Visual Studio. Most don’t know much about what to do with the ever-improving External Tools, or to some it is the case of they do not know what to do with them or how to use them to support their development tasks in Power BI in particular.
There have already been several articles written by well-respected authors and experts in the field of Power BI and Analysis Services demonstrating how to automatically use these External Tools. This article however is concentrating on what you can do with External Tools and how to access them.
Solution
Before we talk about the types of External Tools in detail, it would be great to understand what they are and how they integrate with Power BI.
What are Power BI Desktop External Tools
Power BI External Tools are simply community and third-party tools used to increase the productivity of report and model authors in Power BI.
Since July 2020, External Tools has become more better integrated with Power BI Desktop and, although these external tools being third-party tools and are not supported by Microsoft, but they are based on Microsoft supported .NET libraries provided by Microsoft. These .NET libraries help to modify the Power BI model. If you want to, you can get more information on these .NET libraries here. Since the January 2021 Power BI update, external tools now have the capability to deploy and edit models published on Power BI Premium through XMLA endpoints.
External Tools helps Power BI professionals to create, maintain, and optimize an enterprise-level Tabular model. They are much more suited for large and complex enterprise level projects. There are several types External Tools nowadays, but some are more popular than others due to their use in creating, maintaining, and optimization of enterprise-level Tabular models. These include DAX Studio and Tabular Editor. We will talk more on the different types of External Tools later in this article.
There are four main categories of External Tools used in Power BI and Analysis services projects, these are summarized as follows:
- Those used for Semantic Modeling: These include Open-source tools such as Tabular Editor, DAX Studio, ALM Toolkit as well as Metadata Translator. These extend Power BI Desktop functionality for specific data modeling scenarios.
- Those used for Data Analysis: These include Tools that are used for connecting to a model in read-only to query data and perform other analysis tasks. This category also includes tools that document Power BI datasets. Some of these tools are Power BI Reports Builder, Excel, and tools that launch Python in Power BI Desktop.
- Those used for Miscellaneous purposes: These include those External Tools that don’t have a connection to the Power BI model at all, but they help extend the capabilities of Power BI Desktop in making helpful tips and ensure helpful contents are readily available. Examples may include PBI.tips tutorials, DAX Guide from sqlbi.com, and the PowerBI.tips Product Business Ops community tool.
- Those made as Custom Tools: These include custom made external tools and scripts which are integrated into Power BI Desktop by adding a *. pbitool.json document to the Power BI Desktop\External Tools folder.
Besides these four main categories, there are other important External Tools that help in one way or the other in enhancing the use of Power BI Desktop as a Business Intelligence development tool. Example of such External Tools is DAX Studio. Like the Miscellaneous tools, these are not directly connected to Power BI models at all, and likewise help to extend the capabilities of Power BI Desktop in the formatting of DAX codes.
Prerequisites for installing and accessing External Tools in Power BI Desktop
- As at the time of writing this article, before installing external tools it is important to know that External Tools are not supported in Power BI Desktop for Power BI Report Server.
- Before installing external tools, it is important to know the fact that external tools are provided by external, third-party contributors.
- Also, it is important to remember that Microsoft does not provide documentation or support for external tools.
- Ensure you have the latest version of Power BI Desktop.
- Ensure you have the latest version of the external tool you want to install.
- You would need to first install the external tools and the json in folder path before external tools tab is shown in Power BI.
How to access external tools in Power BI Desktop
Power BI external tools now have its own tab at the top of the Power BI Desktop page as seen in the diagram below.
However, you may not be able to see this External Tools tab on your Power BI Desktop. This can be due to any of the factors which has been mentioned in the prerequisites as highlighted above. If you are not seeing the tab, do the following:
Ensure you have updated your Power BI Desktop to the latest version:
You can check your version of Power BI Desktop by following these instructions. Click on “Help” tab, then click on “About”, and a window will pop out showing your current version of Power BI Desktop. Update it if it’s not in the last two months at least. See the diagram below on how it’s done.
Install the external tools:
Even after updating your Power BI Desktop version, you still might not see the External Tool tab yet. This is most likely because you are yet to install the external tools you need. As part of the prerequisite mentioned earlier, do ensure you are installing the latest version of the external tools you need.
Once the external tools are installed, you should be able to see the External Tools tab at the top ribbon of your Power BI Desktop. The external tools you installed should be showing on the ribbon.
You could look to get all most important and popular external tools installed in one go by downloading and installing PowerBI.tips Product Business Ops community tool, which makes installation of a large selection of external tools possible. And you would be able to see all the selected external tools downloaded and installed as part of the PowerBI.tips Product Business Ops community tool.
How do External Tools integrate with Power BI
At the time any Power BI Desktop report is opened, it is important to know that a mix of various components are working together including report canvas, visuals, model metadata, and any data already loaded to the model from data sources. Additionally, at the background of any open pbix file there is an Analysis Services process which helps to load the model so that the report visuals and the data modeling features can access the query model data and the model metadata.
Anytime Analysis Services is launched in Power BI Desktop, it randomly assigns a port number dynamically and loads the model with a randomly generated globally unique identifier (GUID). As you can deduce, these random GUID numbers as parameters in Power BI Desktop would change with every Power BI Desktop session. To overcome this limitation, would be expected to make it difficult for external tools to connect to every instance of Analysis Services and model in Power BI Desktop.
However, with the introduction of Power BI External Tools integration it is now possible to allow Power BI Desktop to communicate the Analysis Services port number, server name, and model name to the tool as command-line parameters when we start the external tool from within Power BI Desktop External Tools ribbon. I have put together a diagram that summarizes this as shown below.
The diagram above is summarized as follows:
- Stage 1: Power BI Desktop passes port number, server name and database name as command-line arguments to the external tool.
- Stage 2: The external tool uses Analysis Services client libraries to establish a connection to the Power BI Desktop model, retrieve metadata, and then execute DAX or MDX queries.
- Stage 3: For every update done to the metadata by the external data modeling tool, Power BI Desktop synchronizes the changes which is then reflected accurately on the Power BI Desktop user interface, although there are some documented limitations on this.
Some important and popular External Tools
There are several external tools for Power BI Desktop nowadays, but we will only be discussing more on some important and popularly used ones in this article. These external tools include DAX Studio, Tabular Editor, ALM Toolkit, DAX Formatter, and PowerBI.tips – Business Ops. They are so important that some Power BI professionals even spend more time in these tools during development than within Power BI Desktop itself.
I will not go into the details of how each external tool is used in this article, but I will write on other aspects of external tools like, what the different types are, what you can do with each, how to download the tools, how to connect the tool to Power BI Desktop, as well as a brief description of how the User Interface looks.
DAX Studio
DAX Studio as an external tool in Power BI Desktop is a tool to write, execute, and analyze DAX queries. In general, DAX Studio is the ultimate tool for analyzing and executing DAX queries against Microsoft Tabular models. You can get more information on DAX Studio from the official documentation page here.
What can you do with DAX Studio
There are several tasks you can do with DAX studio in Power BI/Tabular Model development nowadays, but we will mention some popular ones in this article Testing and troubleshooting performance of Power BI models and measures.
- You can use DAX Studio to write more complex DAX formulas that contain “tables” as part of the formula.
- You can use DAX Studio to extract a list of measures in your Power BI data model into a spreadsheet.
- You can use DAX Studio to check more detailed information about the contents (columns, rows, query execution statistics) in a table in Power BI Desktop.
- DAX Studio can help you leverage your Power BI Desktop model as an instance of Analysis Services server.
How to download and Install DAX Studio
It is important that you should always download the latest version of DAX Studio as I did describe earlier in this article. To do this, do follow the steps below:
- Go to your favorite browser, and search on Google “DAX Studio download” or you can just use this link to the DAX Studio download page as seen in the diagram below.
- You can choose to download the normal version, or the portable version as shown as 1 and 2 respectively in the diagram above. Note that the portable install will come as a Zipped folder unlike the normal install.
- Once its downloaded, you only then need to follow the “run” process, and your DAX Studio is ready and would then be visible in Power BI Desktop in the “External Tools” tab.
How to connect DAX Studio to Power BI Desktop
It is very straight-forward to launch and connect Power BI Desktop model to DAX Studio nowadays. As mentioned earlier, once you download and install DAX Studio on your working computer, you actually have also included the DAX Studio json in a folder path, which enables you to see the DAX Studio in the External Tools ribbon in Power BI Desktop as seen in the diagram below.
So, to connect DAX Studio to Power BI Desktop model you would need to click on the DAX Studio icon on the External Tools ribbon and a this will automatically kick start the connection process and a window of the DAX Studio UI is opened with a connection already established with the Power BI Desktop model I have opened as seen below.
The DAX Studio User Interface
The DAX Studio UI is relatively complex nowadays, it’s not possible within the scope of this article to describe everything (besides, there are new updates every now and then), thus, we will only talk about some few important points you may need to know.
Using the diagram below, I will describe briefly what each numbered area are in DAX Studio.
- Point 1 is the run button to execute your query (you can also use F5 on keyboard for this purpose).
- Point 2 allows you to change the default output on the output pane (10) to other alternatives like Excel or a file (CSV or TXT).
- Point 3 is the DAX Format Query that helps to format any query you might have in Point 9 area when selected.
- Point 4 is the Server Timings button which helps in performance testing.
- Point 5 is the connect button which allows DAX Studio to connect to another data model.
- Point 6 is the pane where you can see the list of all tables, measures, and columns in your model.
- Point 7 is the area with three buttons to switch from model metadata to Functions pane or DMV (Dynamic Management Views) pane. Get some more information on this here.
- Point 8 is the local port number representing an Analysis Services instance of the Power BI Desktop model connected to the DAX Studio. Get some more information on getting port number in DAX Studio here.
- Point 9 is the query pane area where DAX codes are written and formatted.
- Point 10 is the default output pane area where you can see results of your queries.
Tabular Editor
Tabular Editor as an external tool in Power BI enables BI professionals to easily manipulate tabular models using an incredibly intuitive, lightweight editor. It allows Power BI professionals to easily manipulate and manage measures, calculated columns, and display folders. You can get more information relating to Tabular Editor here.
What can you do with Tabular Editor
Like DAX Studio, Tabular Editor is extremely popular in Power BI and Analysis model development, and there are several tasks you can do with it in Power BI/Tabular Model development nowadays, but we will mention some popular ones as it relates to Power BI in this article.
- One very popular use of Tabular Editor in Power BI development is for creating Calculation Groups.
- Another similarly popular way Power BI developers use Tabular Editor is for Best Practice Analyzer.
- Tabular Editor is also very popular where it is used as a scripting interface to automate common tasks for creating measures, building common calculations, adding descriptions, etc.
- Tabular Editor is also popular for creating of Custom KPIs in a Power BI Desktop data model.
- Tabular Editor is also often being used to migrate Analysis Services models to Power BI.
How to download and Install Tabular Editor
Like DAX Studio, it is important that you should always download the latest version of Tabular Editor as I did describe earlier in this article. It is important to note that Tabular Editor 2 is an Open-source free tool, however, at the time of writing this article Tabular Editor 3 is out and it is based on licensing (not free!). In this article we are going to focus on the open-source version that is readily available as external tool in Power BI after installation. To do this, do follow the steps below:
- Go to your favorite browser, and search on Google “Tabular Editor download” or you can just use this link to the Tabular Editor 2 download page as seen in the diagram below.
- Like in DAX Studio, you can choose to download the normal version, or the portable version. See “Next Steps” at the end of this article if you wish to download the portable version. Note that the portable install will come as a Zipped folder unlike the normal install.
- Once its downloaded, you only then need to follow the “run” process, and your Tabular Editor is ready and would then be visible in Power BI Desktop in the “External Tools” tab.
How to connect Tabular Editor to Power BI Desktop
Like DAX Studio, it is very straight-forward to launch and connect Power BI Desktop model to Tabular Editor. Once you download and install Tabular Editor on your computer, you actually have also included the Tabular Editor json in a folder path, which enables you to see Tabular Editor in the External Tools ribbon in Power BI Desktop as seen in the diagram below.
So, like in the case of DAX Studio, to connect Tabular Editor to Power BI Desktop model you would need to click on the Tabular Editor icon on the External Tools ribbon and this will automatically kick start the connection process and a window of the Tabular Editor UI is opened with a connection already established with the Power BI Desktop model I have opened as seen below.
The Tabular Editor User Interface
The Tabular Editor UI is relatively complex, like in the case of DAX Studio, it’s not possible within the scope of this article to describe everything (besides, the tool is updated frequently), thus, we will only talk about some few important points you may need to know.
Using the diagram below, I will describe briefly what each numbered area are in Tabular Editor.
- Point 1 is the pane where you can see the list of all tables, measures, and columns in your model.
- Point 2 is the query pane area where DAX codes are written and formatted.
- Point 3 is the default output pane area where you can see results of your queries.
- Point 4 is the DAX Format Query that helps to format any query you might have in Point 2 area when selected.
- Point 5 is the “Navigate back” and “Navigate forward” buttons.
- Point 6 is the local port number representing an Analysis Services instance of the Power BI Desktop model connected to the Tabular Editor.
ALM Toolkit
ALM Toolkit is a popular External Tool in Power BI development used to manage Power BI datasets. It is an Open-source and free tool mainly associated with schema comparison in aspects of application lifecycle management (ALM).
What can you do with ALM Toolkit
ALM Toolkit can be used in various ways in Power BI development, among the most popular uses are listed below.
- Database comparison
- Code merging
- Easy deployment
- Source-control integration
- Reuse definitions
- Self-service to corporate BI
To get some information on how to use ALM Toolkit, checkout the detail on alm-toolkit.com.
How to download and Install ALM Toolkit
Like the other external tools mentioned already, it is important that you should always download the latest version of ALM Toolkit. To do this, you can either download it from the sqlbi.com site or from the alm-toolkit.com site as shown in the steps below:
- Go to your favorite browser, and search on Google “ALM Toolkit download” or you can just use this link to the sqlbi ALM Toolkit download page as seen in the diagram below.
Or you can do so from the alm-toolkit download page as seen below.
- Once its downloaded, you only then need to follow the “run” process, and your ALM Toolkit external tool is ready and would then be visible in Power BI Desktop in the “External Tools” tab.
How to connect ALM Toolkit to Power BI Desktop
Like the other external tools already mentioned, it is very straight-forward to launch and connect Power BI Desktop model to ALM Toolkit. Once you download and install ALM Toolkit on your computer, you actually have also included the ALM Toolkit json in a folder path, which enables you to see ALM Toolkit in the External Tools ribbon in Power BI Desktop as seen in the diagram below.
To connect ALM Toolkit to Power BI Desktop model you would need to click on the ALM Toolkit icon on the External Tools ribbon and this will automatically kick start the connection process and a window of the ALM Toolkit UI is opened with a connection already established with the Power BI Desktop model I have opened as seen below. But currently you would be required to choose a Target connection too (unlike the other two external tools discussed already).
The ALM Toolkit User Interface
I would recommend you get more details on the full list of actions to perform within the ALM Toolkit UI from the official documentations page in GitHub. Like in the case of DAX Studio and Tabular Editor, it’s not possible within the scope of this article to describe everything within the UI of ALM Toolkit (besides, the tool is updated frequently). The typical ALM Toolkit page should look like the diagram below.
DAX Formatter
As an external tool in Power BI Desktop, DAX Formatter is a free tool by SQLBI that transform your raw DAX formulas into clean, beautiful, and readable code. The syntax rules used in DAX Formatter helps to improve the readability of the DAX expressions.
What can you do with DAX Formatter
DAX Formatter is one of my best and mostly used external tool I use to support my development in Power BI Desktop. Although it is not directly connected to the Power BI data model when opened as an external tool in Power BI Desktop, but you can just copy your Power BI calculated measures or calculated columns over to the DAX Formatter UI and paste it to format it into a more readable, clean, and beautiful code that you would copy again and paste back into the formular bar in Power BI Desktop UI. See this video by sqlbi on how to use DAX Formatter.
How to download and Install DAX Formatter
DAX Formatter does not actually require a download and install before it can be used. You can just get to the DAX Formatter page via the https://www.daxformatter.com/ URL and use it. Alternatively, to have it as part of your External Tools in the external tools ribbon in Power BI Desktop you can download it as part of the PowerBi.tips Business Ops download. When you do this via the Business Ops option, you would have an option to download versions based on your browser preferences as seen in the diagram below. We will talk more about Business Ops later in this article.
How to connect DAX Formatter to Power BI Desktop
As mentioned earlier, DAX Formatter does not actually have a direct connection with Power BI Desktop data model, but it can be opened via the External Tools ribbon in Power BI Desktop like other external tools as seen in the diagram above.
So, to use DAX Formatter in your Power BI development as an external tool you would need to copy your DAX measures or Calculated Columns codes and paste it in DAX Formatter opened from the external tools ribbon and once you have formatted the code in a beautify, readable and clean format, you then copy it again and paste back into your Power BI Desktop formular bar.
The DAX Formatter User Interface
Unlike the other External Tools we have seen so far in this article, the DAX Formatter UI is relatively very simple and straight-forward.
Using the diagram below, I will describe briefly what each numbered area are in DAX Formatter.
- Point 1 is the pane where you can see the DAX code you need to format.
- Point 2 is where you copy the formatted DAX code to take back to Power BI Desktop.
- Point 3 is where you can copy in an HTML format (I have never used this nonetheless!)
- Point 4 can be used to save the DAX formatted code in word document.
- Point 5 is used to re-edit the DAX code within DAX formatter UI.
- Point 6 is used to close current code and open a new blank pane for a new code to be pasted into Point 1.
Business Ops
Business Ops is a product of PowerBI.tips which was designed to serve as a one-stop shop for all the latest versions of external tools.
What can you do with Business Ops
Although, on its own Business Ops is not actually a tool to connect Power BI data model to or for you to format you DAX codes or compare your DAX codes, but once you start using external tools in Power BI Desktop, Business Ops would become the go to tool to get any other popular external tools readily available and up-to-date versions. I would recommend this tool any day, do give it a try.
You can get some more information about this tool here.
How to download and Install Business Ops
Business Ops being a product of PowerBI.tips can be downloaded directly from the PowerBI.tips website. To do this, do follow the steps below.
- Go to your favorite browser, and search on Google “Business Ops download” or you can just use this link to the Powerbi.tips Business Ops download page as seen in the diagram below.
- Note that the download will come as a Zipped folder unlike the normal install.
- Once its downloaded, your Business Ops tool is ready and would then be visible in Power BI Desktop in the “External Tools” tab.
- To get the standard external tools from the Business Ops tool you would need to open your Power BI Desktop and on the External Tools tab you need to open the Business Ops Tool as in the Ribbon below.
- Once opened, you can select the external tools you want as seen in the diagram below. Then you click on “ADD EXTERNAL TOOLS” at the bottom of the screen.
The Business Ops User Interface
The Business Ops tool UI is relatively simple to navigate through. It’s a one-stop tool to get loads of Power BI related documentations and tools, which can be extremely helpful in Power BI development. For instance, it has the “Home” section where you can get more information on Business Ops as a tool and any relevant information about the External Tools within it as seen in the diagram below.
There is also the “Add External Tools” section that shows all the External Tools you can get from Business Ops and a check box in front of each so you can tick on the one you would need as seen in the diagram below.
Next, we have the “Edit External Tools” section where you can edit or modify the names of your external tools as seen in the diagram below.
Then we have the “Learning” section where you can get more resources to learn about some popular external tools as seen in the diagram below.
The rest of the sections include the Themes section, the Gallery section, the Dax Guide section, the Dax.do section, and a section for everything you need to know about the developers of the Business Ops tool and others. Download the tool to explore more!
Summary
In summary, in this article, we have successfully discussed what External Tools are, how they relate to Power BI Desktop and some information about their User Interfaces. As mentioned, we are not able to discuss about all existing External Tools within the scope of this article, but we have managed to discuss some important and popular ones (again this is subjective, depending on what you do mostly in Power BI Desktop). It is worth to know that there are other very important external tools including Metadata Translator, DAX Beautifier, DAX Guide, Analyze in Excel, Power BI Report Builder, SQL Server Profiler, etc. I would encourage you to try these tools in your Power BI development, and the best way to start is to download the PowerBI.tips Business Ops tool which will make the whole process of downloads easier and seamless.
Next Steps
- See this article from BI Insight on using different ways to find Power BI local port numbers here.
- Check out this very comprehensive article by sqlbi on Development Tools for Tabular Models.
- Checkout this article by exceleratorbi on DAX Studio here.
- See the YouTube video from Guy in a Cube for more details on what you need to understand about External Tools Ribbon in Power BI Desktop.
- Get some more information on Best Practice Analyzer using Tabular Editor here.
- Download portable Tabular Editor edition from official GitHub site.
- Get more detailed information on ALM Toolkit from the official documentations page.
- Try this tip out in your own data as business requires.
