All articles
Documentation Automation Best Practices

The Best Way to Manage Descriptions

Learn the best method for adding descriptions to objects in your model.

Adding descriptions to objects in your model (i.e. tables, columns, measures etc.) seems to always be the lowest priority task when creating a tabular model. If descriptions have been added to a model it seems to show that someone went above and beyond their job. This lack of priority is partly due to descriptions not being a necessary component of the model but also because they are cumbersome to manually enter into your modeling tool - whether it be Power BI Desktop, Tabular Editor or SSDT. Additionally, the descriptions for objects may even be officially set by someone other than the developer which ends up in a long process of copying and pasting. Ugh! It’s too much work! No wonder such few models have descriptions!

The Value of Descriptions

While much of the focus of modeling is spent on performance (which of course is completely valid), there is another component which is very important. It is the user experience. The user experience includes aspects such as a star-schema architecture which is easy to navigate, proper formatting and naming conventions, as well as generally ensuring that the user understands the model as best as possible. This last point is aided by creating a Data Dictionary. This dictionary includes descriptions for each object in the model and helps users acquaint themselves with elements in the model. Thus, if the model has no descriptions, the Data Dictionary is not so helpful and it can be said that the development team could be doing more to aid its users. After all, users who know the model better will likely use it better, thereby creating better queries (i.e. not causing cartesian products) which in turn makes performance better for all users. In other words, adding descriptions can indirectly improve performance.

For more on creating automated Data Dictionaries, see my previous posts:

Automated Data Dictionary

Automated Data Dictionary - Reinvented

Automated Data Dictionary - Excel

The Solution

Now that we have discussed the value of adding descriptions, let’s talk about the best way to go about it. Since the descriptions are often managed by a stakeholder group, it is a good idea to externalize the model objects and descriptions outside of the development tool. The first tool that comes to mind for this purpose is Excel. It is an easy place to make quick changes on a list (of descriptions) and pretty much everyone is familiar with it. The other benefit of using Excel is that one can see all the descriptions at once (and therefore which objects do not have descriptions) which I believe gives it an advantage. That being said there is also a best practice rule for this.

Now that we identified Excel as our intermediary tool, let’s lay out exactly what we need to do.

  1. Create a list (table) of all the model objects and their current descriptions.
  2. Export this list to Excel.
  3. Make changes in Excel.
  4. Import the Excel file back to the model.

Using a bit of C# it turns out that is pretty simple!

Running the Scripts

I’ve created two scripts for this - one for exporting the description metadata to Excel and another for importing the description metadata back to the model.

Here are the steps for exporting the descriptions to Excel:

  1. Open your model in Tabular Editor.
  2. Download the ExportDescriptions C# script.
  3. Copy & paste the C# script into the Advanced Scripting window within Tabular Editor.
  4. Update the filePath parameter to be the location where the Excel file with the descriptions will be saved (do NOT include the .xlsx suffix).

\5. Click the ‘Play’ button or press ‘F5’.

After completing these steps, your description metadata will have been exported to an Excel in the location specified in step 4. Now you can update the descriptions in the Excel file or even share the Excel file with other folks for them to update it. Once you have updated the descriptions and are ready to import them into the model, follow the steps below.

Here is an example of the descriptions within the Excel file.

Here is an example of the descriptions within the Excel file.

Here are the steps for importing the descriptions to the model:

  1. Open your model in Tabular Editor.
  2. Download the ImportDescriptions C# script.
  3. Copy & paste the C# script into the Advanced Scripting window within Tabular Editor.
  4. Update the filePath parameter to be the location of the saved Excel file with the descriptions (do NOT include the .xlsx suffix).
  5. Click the ‘Play’ button or press ‘F5’.

After completing these steps, the descriptions in the Excel file will have been loaded into the model. You can check this by navigating to an object in the model and viewing the description within the properties window.

Additional Notes

Other automated solutions for this may export the data to a text or .tsv file. However, those file types are not so friendly to work with - hence Excel. Actually, if you examine the C# script for exporting the data to Excel you will notice that it actually does export to a text file but then the code converts it into an Excel file. Simple things like this make life much easier. No one wants to edit a giant text file. Excel’s user interface is far superior so why not take advantage of it.

With regard to managing the Excel file, I recommend putting it in a shared location so that both the development team and the stakeholder team can modify the file. And, whenever new objects (tables, measures, columns, hierarchies etc.) are added to the model, the export script is executed so that the Excel file has all the latest objects. If the Excel file has been tampered with and has mismatching objects, they will be skipped (and no error will show). This helps maintain accuracy and also stresses the importance of exporting the descriptions on a regular basis so that the definitions can be updated properly.

The Excel file that is generated by the export script includes a column indicating whether the object is hidden or not. Since hidden objects are not shown to the end user it is generally advisable to only set descriptions for visible objects. As such, simply filter the Excel file to see where the ‘HiddenFlag’ equals ‘No’.

One additional point to mention is that the descriptions will not only show in your Data Dictionary but will also appear when hovering over an object in the Fields pane (as shown below).

Conclusion

In summary, descriptions are a great way to give your users additional context when navigating your model. I believe that this technique lowers the barrier to entry and makes managing descriptions easier as it allows for an automated loading process and eases collaboration on the description content. Naturally, if you know of a better method please comment below. I’m always looking to improve and find an even better way!

Comments 26

Comments with links are reviewed before they appear.

  1. Ron van der Poel

    Hi, I used the export and import script. Worked fine. The only thing is that every time I open the PBIX Power BI keeps on asking me if I want to apply the changes. I already applied them more than once, but keeps on asking. Any idea? regards Ron

    1. Michael Kovalsky Author

      This warning is provided by Tabular Editor as a note of caution since only a limited set of operations are supported when using External Tools with Power BI Desktop. There is no way to turn off this warning.

    2. Ron van der Poel

      Does it harm?

    3. Michael Kovalsky Author

      Descriptions should be fine as modifying them does not change the structure of the model. However, make sure to take a backup of the .pbix just in case.

  2. x37

    Hello,

    Thank you for the scripts. Would it be possible to add an import/export for translations as well ?

    Although I am not 100% sure how they can be seen in the model or web (except from within Tabular Editor).

    Thanks!

    1. Michael Kovalsky Author

      I have scripts for import/export of translations on GitHub. MetadataExport, MetadataImport_Translations https://github.com/m-kovalsky/Tabular

  3. Ken Loewen

    I really like your import & export scripts. One thing that popped right up is that I've got a couple of <cr><lf> in one of my descriptions and the export script threw the new lines of text to column A in the next row rather than keeping it in the same cell. I would have the same situation in nearly all of my DAX measures as well as some measures that have a TAB character in them.

    When I export my measures to TSV, the <cr><lf> comes across as \n and the tab as \t. That successfully imports to Excel and I'm then able to find/replace them. These are manual steps for me now but the sort of thing I hope to automate.

    1. Michael Kovalsky Author

      Hi Ken, it’s not exactly a best practice to have tabs and new lines within descriptions. I would recommend removing these. It can be easily accomplished with the REPLACE function in c#. If you really want the \n & \t to show you can use the replace function to replace such indents with those values.

  4. Garrett Fox

    Hi Michael, I am a HUGE fan of yours! For my report builders, the best description for a measure is really just the DAX code itself. Is there an automated way to make sure that the measure descriptions that the report builder sees when hovering over a measure is DAX code itself? Thanks, Garrett

    1. Michael Kovalsky Author

      Hi Garrett, you can accomplish this by running the following code in Tabular Editor’s advanced scripting window: foreach (var m in Model.AllMeasures.ToList()) { m.Description = m.Expression; } You can expand this to calculated columns as well if you like with a bit more c#.

  5. Berbouze

    Hi guys, quite new here so question might be stupid or I might be doing something wrong. But whenever I try and update the description from Tabular Editor to my PBI Desktop, after I apply changes in PBI Desktop, Tables and Columns description are reset to their old value in PBI Desktop. Measures description is pushed however. Any idea why ?

    1. Michael Kovalsky Author

      Hi, you must update the following setting in Tabular Editor: File->Preferences->Allow unsupported Power BI changes (make sure this is checked).

    2. Berbouze

      Thanks for the reply Michael, however, it didn't quite fix the issue for me. I've tried playing with pretty much all the preferences options but couldn't get the tables/columns descriptions to update in PBI Desktop (I do see the new description temporarily, but after applying changes it reverts back to the previous PBI Desktop description).

    3. olivierlenard

      Same for me Berbouze. Don't get the rootcause. Did you fix it by the time

  6. Bernat Agulló

    Once you have the descriptions, why not bring them into the report, as tooltips for instance -- here I suggest a way of doing it https://www.esbrina-ba.com/showing-measure-descriptions-in-tooltips/

    1. Michael Kovalsky Author

      Hi Bernat, yes you can certainly do that. However you want to surface the descriptions is your choice. However, I would caution adding descriptions as tooltips to all your objects in visuals as it would create an overhead for performance. That is why I prefer a single data dictionary page (organized as not to interfere with the other report pages).

  7. Lucas

    Anyone have feedback on why I am getting an error message when I try to run this script in Tabular Editor. I am getting an "Unable to Cast" error message that I will type in below for reference. I am VERY new to this so apologize ahead of time for my lack of knowledge.

    Error on line 129

    InvalidCastException

    Unable to cast COM object of type

    'Microsoft.Office.Interop.Excel.ApplicationClass' to interface

    type 'Microsoft.Office.Interop.Excel._Application'. This

    operation failed because the QueryInterface call on the COM component for the interface with IID

    '{000208D5-0000-0000-C000-000000000046}' failed due to the

    following error: Error loading type library/DLL. (Exception

    from HRESULT: 0x80029C4A (TYPE_E_CANTLOADLIBRARY)).

  8. Bernat Agulló

    I've seen that the descriptions might not be inmediately visible in PBID. But if you save and reopen the file they should be there. They were for me!

  9. niall.dillon

    This is a really good solution for documentation. For us the ideal solution would be PBI could read the Description (Extended Properties) value directly from SQL Server but with that not available this is a very good alternative. I've got this solution mostly working, the Column Descriptions all update as expected but the Table Descriptions initially update but on saving the dataset they disappear. I've set File->Preferences->Allow unsupported but this has not made any difference. It's not a huge issue as I can manually enter the Table Descriptions as there are not that many, just curious as to why Column Descriptions are updating but Table Descriptions are not.

    Thanks,

    Niall

    1. Michael Kovalsky Author

      Are you saving this back to a Power BI Desktop file? I just tested this and it works just fine for both the column and table descriptions (as long as you have that preference set in Tabular Editor). Make sure the only thing you modify in the Excel file is the description column. And that you first use the export script to generate the Excel file.

    2. Johnny W

      Darren Gosbell has a script that can read SQL Server extended properties and do exactly that

      https://gist.github.com/dgosbell/477c021035b206c97b26ea28db1550ee

  10. Kyle

    Hey guys I was having issues with measure descriptions that contain newlines. The export would have the descriptions containing newlines spread out across multiple rows - meaning the export format no longer worked on the import step. I've created a fork of the ExportDescriptions.cs file which resolves the issue by replacing the newlines with a placeholder string before they are appended to the excel file. You can then use these placeholders to quickly re-insert the newlines in excel before you import. https://github.com/Kyle-Ross/Tabular/blob/master/Descriptions/ExportDescriptions-NewLineRemover Thanks to the author and let me know if you find an easier way of dealing with this issue!

  11. bilalzeine23

    Hey guys. Ive tried to export using tabular editor, however i keep getting an error on line 119 - DirectoryNotFoundException Could not find a part of the path 'C:/Desktop/Descriptions.txt'. how can i fix this?

Search articles