It’s helpful to provide documentation for an Analysis Services Tabular model so analysts and developers can easily understand the relationships, calculations, security, and other business logic embedded in the model. But it can be tedious to keep the documentation up to date with each update.
If you have been manually documenting your tabular model, I’d like to show you a more efficient way. Documenting your Analysis Service Tabular model doesn’t have to be tedious since we can use Dynamic Management Views (DMVs) to help us gather the necessary metadata and make a refreshable report. Analysis Services Dynamic Management Views are query structures that expose information about local server operations, server health, and database metadata.
In the past we could use the MDSCHEMA DMVs to document our Analysis Services database. With SQL Server 2016 we have new TMSCHEMA DMVs that help us document the new 1200 compatibility level models. These DMVs are also available for Power BI models.
By creating a Power Pivot or Power BI model, I can query data from the DMVs and transform that data into an easily browsed model to provide information about:
- The SSAS Database
- Connection strings
- Source queries for each table
- Tables
- Columns
- Hierarchies
- Measures
- Relationships
- Security
- KPIs
- Perspectives
In this Demo Day video, I demonstrate how I used DMVs to create dynamic documentation that can be updated to point at any SSAS 2016 (level 1200 compatibility) Tabular or Power BI model. I built two versions of the end report: one that uses Excel to provide simple documentation, and one that uses Power BI to allow for more interactive analysis of my Tabular model metadata.
{{ script_embed(‘wistia’, ‘o7araa7zfo’, ‘undefined’, ‘responsive’) }}
For more technical information on how I built the model to support this tabular model documentation, see my post on DataSavvy.me.
If you would like help building or documenting your SSAS Tabular or Power BI model, please contact us today.
