1. Introduction to the Power BI Model Documenter
Welcome to the Power BI Model Documenter Wiki! Here you will find more information about this external tool, as well as an FAQ page to answer the most recurring questions and get you going. Please read along!
In the Power BI Desktop July 2020 release, the External Tools capability was released (in preview). With that 3rd party tooling integration is now possible. Tools such as ALM Toolkit, DAX Studio and Tabular Editor integrate nicely with Power BI Desktop and are capable of writing data in your Power BI model. External Tools capabilities are open source, so everyone can create his own tools to integrate with Power BI. Inspired by all the great content that was already created by others in the community.
I build and External Tool to document your Power BI Model. I know, documentation is not a very famous topic, but I believe a very important one! Lead time from data to insights is very short with Power BI, but often we forget to look back on what kind of monster we created. Especially if you want to share the dataset for reuse, I believe it is important to deliver some documentation as well. Maybe you even promote or certify this dataset in the future, which implicates that the model matches certain quality metrics and best practices.
Although the sentiment around documentation is not very positive, I believe that we as Data Analytics professionals should continue advising our customers to care about documentation. By providing an External Tool in Power BI Desktop, I hope that I can make it easier for a lot of people to deliver at least some basic documentation with their solutions.
To install the Power BI Model documenter, you can run an easy next-next-finish installer (Model documenter v2.0.0 and up). This will ease the installation of the tool, and saves you from manually adding files to your program files directory. In order to install the tool, you must have local admin permissions on your device. Without these permissions, it is not possible to install or run the Model Documenter.
In case you run into issues, kindly check the FAQ page first. There are some things you might run into, as some functionality is depending on your local Power BI settings to work. I try to add all possible issues there including how to solve them. If you still encounter issues after reviewing the FAQ, please review existing issues or open a new issue.
In this section, you will find explanation for every page in the Model Documentation template. The data model used for this sample, is fictive and includes sample data.
The info page includes all generic information about the data model, such as compatibility mode and level, but also when the data model was created and when it was last processed. From here, you can navigate through the report and find all other information about the data model. Also, on top it shows the name of the model. If you run the Model Documenter from Power BI Desktop directly, you will most likely see a GUID presented. If you run the Model Documenter from Desktop connected to an existing model in the Power BI Service using the Analysis Services connector (live connection), the name of the data model will appear.
Introduced starting Model Documenter v2.1.0 On the parameter page, you will find two things, being Power Query parameters and referenced queries.
For Parameters, you will see the current value of these parameters and the expression which tells you whether the parameter is required and the value type. In case you have a list of options provided in the parameter setting, these will not directly show.
For the referenced queries, you will see all queries you build in Power Query, but did not load to your model. These queries are listed and show the M-query expression.
The Tables page includes information about all tables in the data model. Relevant information like the storage mode (Import / Direct Query / Dual) is show, as well as the number of rows in a table and number of partitions. Also the table type describes if the table is generated using Data Analysis Expression (DAX), M (Power Query) or using an Incremental Refresh policy.
While hovering over a table, the tooltip will show up, containing information about the specific table. Such as;
- Table state
- Last refresh time
- Last modified time
By selecting a single table, you can drill through the partition specific page (button 2). Two new icons will also appear in the top header after selecting. Other alternative drill paths are measures (button 1) or all columns (button 3) in the selected table.
Introduced starting Model Documenter v2.1.0 On the partitions page, you will find more information about the partitions associated to the tables in the model. You can access this page via drill through from the table page, or directly via the page navigation. All tables by default have one partition, which will show as teh table name concatenated with a GUID. In case you specified Incremental Refresh, your model will show all the different partitions generated.
Please know that the partitions from Incremental Refresh are only generated in the Power BI Service. In order to visualize the partition information, you must connect using the Analysis Services connector to the XMLA endpoint of your Power BI dataset hosted in any type of Power BI Premium. Once connnected, you can run the Model Documenter from Power BI desktop to get the results out.
By selecting an invidual partition, the expression of this partition will show on the right-hand side of the screen. In case of incremental refresh, the bottom of the page shows the rolling window (period of data in the model) and the incremental period (active section that is refreshed).
The columns page shows an overview of all columns in the data model, and to which table they belong. Additional information is shown such as the column cardinality (number of unique values in the column), specific sort order (if specified) and format string (if specified). Also there is an indicator when a column is generated using Data Analysis Expression (DAX).
When hovering over a column, the tooltip will appear to show information such as;
- Data type (sting / integer / etc)
- Expression (only applicable in case of a calculated column)
- Column Description
Below tooltip items which were available in version v2.0.x of the model documenter are discontinued starting v2.1.0 - Column type - Encoding (compression type)
Introduced starting Model Documenter v2.1.0 All calculation groups in the model, will show in a dedicated page to calculation groups. They will no longer show up in the tables overview, as no details were shown there. In this new dedicated page, you will see all calculation groups including the calculation items, descriptions and item expressions.
By selecting a calculation item, the expression will show on the right-hand side.
Introduced starting Model Documenter v2.1.0 In case you were using Field Parameters in versions of the Model Documenter prior to v2.1.0, running the tool might have caused you issues. Introducing v2.1.0 and onwards, these issues are resolved. In case you are using field parameters, they will now show up in this dedicated page including the descriptions and number of items listed in the field parameter. By selecting a field parameter, the expression will show on the right-hand side.
Measure page includes all details about the DAX measures in the data model. The overview directly shows the table where the measures are located, measure names and (if added) descriptions. Descriptions are key to your elements created in Power BI that do not exist in upstream data sources, such as measures and roles. Read more about the importance of descriptions in this blog post. When a single measure is selected, the measure expression will be shown on the right-hand side.
The relationships view gives an overview of all relationships in the data model. In-active relationships will be displayed in a grey font, where all other relationships are displayed like all other information. Also, in case you have relationships with cross filter direction set to both, or many-to-many relationships, this can lead to ambiguous data models and/or limited relationships. Therefore these relationships are highlighted in red. On top, a list of all relationships with the cardinality and on the bottom part of the page shows a visual overview of all relationships in the model.
Finally, there is an invalid rows count. This indicator shows the count of rows affected by a mismatch in relationships. Imagine there are 8 distinct productkeys in the many-side of the relationship (fact table) and only 5 in the one-side of the relationship (dimension table), this means that there are 3 missing keys. As there might be many products sold (many rows in the fact table) for these 3 missing keys, there will be multiple rows affected by this mismatch in the relationship. This is shown as count of invalid rows. If you want to learn more about this concept, I encourage you to watch this explanatory video by SQL BI.
The security page shows all security roles applied to the data model. You will see the DAX expression used to filter the table, as well as which table is affected by this. Also, the description of the role is shown. Power BI Desktop does not have a native way of adding descriptions to roles. However, with Tabular Editor you can add descriptions to roles. Please read this blog post to learn how to do this.
