top of page
  • MK

The Best Way to Manage Descriptions

Updated: May 11, 2021

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:



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 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!

24 Comments


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?

Like
Replying to

Does the folder exist?

feel free to bring the question to reddit https://www.reddit.com/r/TE_CSharp/

Like

Kyle
Kyle
Sep 08, 2023

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!

Like

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

Like
Johnny W
Johnny W
Dec 10, 2022
Replying to

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


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

Like

Bernat Agulló
Bernat Agulló
Sep 17, 2022

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!

Like

Lucas
Lucas
May 04, 2022

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)).

Like
bottom of page