I’m a big fan of providing a Power BI Dataset with an accompanying data dictionary built from metadata within the model. You can see my method in this video from my YouTube channel.
The dictionary I build is intended to be lightweight and aimed at report builders and consumers, so I keep the scope of it deliberately narrow and only provide descriptions of tables, columns and measures. For a far more comprehensive set of model documentation, I recommend checking out Marc Lelijveld’s Model Documenter.
The Requirement
Whilst demoing my data dictionary to a client recently, a question came up asking if they are able to extend the contents of the dictionary. At first I misinterpreted the request, as I thought they meant adding additional metadata fields that are already contained in the model. But what they meant, is that they wanted to create custom attributes. Specifically, for each field in the model, they wanted to assign a data owner who would be responsible for the definition, description and any business logic the field might be reliant on.
The Solution
At first, I wasn’t sure this was possible, but in a flash of inspiration I came up with a solution.
My existing method relies on the descriptions in the Power BI dataset being populated. You can fill this information out in the properties pane in the model view of Power BI Desktop, or else it’s also a field available in the object properties pane in Tabular Editor.
To add extended metadata, I originally went down the path of a ridiculously over engineered solution that involved adding customer cultures and an associated set of descriptions. But as I drew towards the end of writing that solution up, I found a much easier way.
Extending your Metadata
The solution does however require that you enable unsupported Power BI features in Tabular Editor.
With that enabled, you now have an option in the object properties pane to add “Extended Properties” – here I’ve added two; one for Data Owner Name and one for Data Owner Role.
Querying the new Metadata
With changes saved and the dataset deployed, I’m now able to connect via the XMLA endpoint and create my data dictionary by querying the Dynamic Management Views (DMVs). In addition to the DMVs I already use in my data dictionary, I need to add one more: TMSCHEMA_EXTENDED_PROPERTIES
If we retain the ObjectID, Name and Value columns and then pivot this data we can create one row per object in my data model.
Finally, this new table can be joined back to column and measure metadata in the model and allow me to report on my new extended properties.
What do you think?
So there you have it – a method for extending the metadata available to you in your Tabular Model. What do you think? Is this a feature you might have use for? What other additional data points might it be useful for you to capture in models?
0 Comments