A third-party tool is a piece of hardware or software developed by a company other than the product manufacturer and designed to improve product performance.
There are many third-party tools available for the usage of Power BI. The most commonly used are Tabular Editor, Dax Studio, ALM Toolkit. In the remainder of this article each of these tools will be explained in detail.
The purpose of this article is to inform and instruct the reader on how to use third-party tools.
https://www.clearpeaks.com/external-tools-in-power-bi-desktop/
Tabular Editor
Tabular Editor is a third-party tool developed by Daniel Otykier. With Tabular Editor you can edit and view a (Power BI) data model in an alternative way. This can be particularly useful if you can no longer access the original desktop file and can only access the model via the Power BI service. This can happen with an incremental refresh policy or due to the size of the data model.
There are two versions of Tabular Editor.
Tabular Editor 2 is ideal if you no longer have access to the Power BI Model. One must then look for an alternative to view calculations, relationships, and model metrics. It should be noted that the functionalities in TB2 are limited. TB2 is recommended for a small to medium-sized model without incremental refresh. The functionalities are sufficient for looking up values such as DAX queries, M-Expressions, shared expressions, and relationships.
Tabular Editor 3 (the commercial version) is recommended if the data model becomes too large to manage in Power BI desktop due to the volume of data. Incremental refresh also plays a major role in the use of TB3. If you enable incremental refresh on the data model it will have to be managed, and you will no longer be able to download the data model from the Power BI service. There will come a time when the data in the model can no longer be accessible. TB3 is ideally suited to solve this problem. TB3 is much more comprehensive than TB2. TB3 allows you to view imported data. One can manage the partitions associated with incremental refresh. TB3 has a model view (relationship management) and an internal VertiPaq Analyzer.
TB2 is free to download from https://www.sqlbi.com/tools/tabular-editor/
TB3 is licensed and can be downloaded from https://tabulareditor.com/downloads
Tabular editor 2
TB2 allows you to view and modify a tabular model*. There is the possibility to view or create tables and columns, to create or modify calculations and to view relationships. TB2 also has the option to run a C# script.
*A tabular model is a set of metadata such as tables, relationships, measures, KPIs, calculation groups, hierarchies, translations, security roles, and many other elements that make up the “semantic model” used to provide a navigation system in client tools such as Power BI and Excel reports.
Tabular Editor 3
TB3 has all the functionalities that exist within TB2. TB3 also has the option to manage partitions with incremental refresh, the option to view the data model, and a built-in VertiPaq Analyzer.
Manage partitions
Incremental refresh* means that Power BI will only refresh a dataset at a prespecified time period (for example: last 2 months). All data prior to this period will not be refreshed and will be stored as historical data. With incremental refresh, one can drastically reduce the refresh time of a dataset.
See article incremental refresh about how incremental refresh works.
If incremental refresh* is enabled on a dataset (table), Power BI creates partitions (data parts). Power BI then stores the data in these partitions and based on the refresh policy* it will refresh these partitions. *See article incremental refresh on how incremental refresh works.
Because a part of the data will be stored as historical in partitions it will therefore not be automatically refreshed. This can happen in the event of a data change with retroactive effect. As a result, this change is not visible. To fix this, the partition needs to be refreshed manually. TB3 is ideal for this.
The first step is to find out in which partition the change happens. Once you have found the partition, navigate to the correct table in the TOM Explorer* and open the number of partitions in the partitions tab. Right-click to refresh and do a full refresh on the specific partition. In the event of a refresh, the partition will contain the data and the change will be visible.
*https://docs.tabulareditor.com/te3/views/tom-explorer-view.html
View data model
TB3 has the option to view and manage the data model (relationships). By opening a new chart and dragging in different tables, one then gets to see the relationships. These can be edited by right-clicking and changing the relationship.
VertiPaq Analyzer
With VertiPaq Analyzer one can analyze a data model based on static data. It provides insight into:
- Total size & last updated.
- Table information (number; cardinality; size)
- Column information (number; cardinality; size)
- Relationships
- Partitions with
- Number of rows with table
- Data size
You can open the built-in VertiPaq Analyzer by clicking on VertiPaq Analyzer at the bottom and then on collect statistics at the top right.
DAX Studio
DAX Studio is a tool for writing, executing, and analyzing DAX queries. In addition, it has the option to be able to analyze Power BI metadata using SQL select statements.
DAX studio is easy to use for both large and small Power BI environments. It provides insight and supports the development process and is therefore a must have for every Power BI developer.
DAX studio can be downloaded for free from: https://www.sqlbi.com/tools/dax-studio/
Functionalities
The most important functionalities of DAX Studio are analyzing DAX queries, looking up Power BI metadata, looking up data examples and of course the VertiPaq Analyzer.
Analyzing DAX queries
When writing a DAX calculation, the performance question often comes up. It can well happen that a visualization is slow due to an incorrectly written calculation and then there is a need to be able to analyze this calculation.
For example:
A single matrix visualization with a single measure and a normal five-level hierarchy can consist of more than one hundred hidden background queries. Each background query has a certain weight (measured in KB or CPU time). To analyze background queries, one can use DAX studio and the server timing function.
Server timing provides insights into the number of queries, CPU time, number of KBs, formula engine usage, and storage engine usage, which is exactly what will be needed to analyze the visualization/calculation and see where it can be improved.
The query can be copied and pasted into DAX studio. If you then run this query with server timing active, the result will look something like this:
See also: https://bi-inside.com/home/blog-exceeded-resources/
How to look up Power BI Metadata
With DAX Studio – DMV, one can easily retrieve and export information from the Power BI model.
For example:
For an extraction of all calculations (DAX), open DAX Studio and on the DMV tab, click TMSCHEMA_MEASURES. Run the SQL-select statement…
DAX Studio then gives an overview of all calculations including expressions, format string etc….
Tip! Change the output option from Grid to Excel Static to export it directly to Excel for further analysis.
ALM Toolkit
ALM toolkit is an open-source and third-party tool to compare or match different Power BI datasets. The ALM Toolkit is ideal for Power BI developers who use multiple datasets that should be similar to each other in large parts, for example when using an OTAP* method where one has multiple datasets that should be equal to each other. ALM Toolkit provides insight into any differences between the datasets at table, column, row (?) and measure level. With the help of ALM Toolkit, the developer can immediately equalize any differences between the datasets.
The ALM Toolkit is open-source and free to download. It is therefore highly recommended that every Power BI developer has this tool in their arsenal.
The ALM Toolkit can be downloaded from http://alm-toolkit.com/
Functionalities
The main functionality of the ALM Toolkit is to be able to compare a source and a target dataset and make changes from dataset source to dataset target. This can be done by opening the ALM Toolkit and selecting both datasets for connections. Make sure both datasets are open or use a workspace connection API otherwise.
When connecting, an overview of the differences in the datasets on the components Model, Expression, Table, Relationship, Measure will show.
http://alm-toolkit.com/HowToUse
With select actions – skip objects can be hidden. These are the objects that are still the same and therefore irrelevant. This leaves you with an overview of differences.
http://alm-toolkit.com/HowToUse
It is also possible to set differences to skip if they do not need to be moved to the new data model. If the correct overview of the differences has been created and the differences are ready to be imported, the validate selection can validate the differences. This gives an extra check.
http://alm-toolkit.com/HowToUse
Once done, by pressing update the ALM Toolkit will overwrite the metadata of dataset source with dataset target. It is also possible to make a printout of the changes and save them for later review.
