BUSINESS INSIGHTS

Nov 07, 2016

Demo Day: Document Your Tabular Model with Excel or Power BI

Meagan Longoria Posted by Meagan Longoria

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.

demo-day-document-tabular-models-power-bi.jpg

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.

undefined

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 to download and use the Tabular model documentation solution, please click the links below to download the Power BI file or Excel file.

If you would like help building or documenting your SSAS Tabular or Power BI model, please contact BlueGranite today.

New Call-to-action
Meagan Longoria

About The Author

Meagan Longoria

Meagan is a Solution Architect and Microsoft MVP who helps people understand their data and use it to learn and make good decisions. She works mostly with the Microsoft BI stack, doing everything from data modeling and data warehouse design to Analysis Services models to data visualization. From time to time, Meagan also dabbles in Tableau. Check out my blog at https://datasavvy.me for interesting links, knowledge, and experiences with BI tools and concepts.