A while back I created the Tabular Model Documenter Power BI model that can connect to your SSAS Tabular or Power BI model and display metadata about the model to help you see relationships, calculations, source queries, and more. Â I had been meaning to turn it into a parameterized template since templates became available and just finally got around to it.
You can now download the PBIT file here. Note: This works for SSAS 2016/compatibility level 1200, but may need some adjustments for Azure AS and SSAS 2017.
When you open it, you’ll need to fill in the instance and database name, then allow some native queries to run. Then you will have your tabular model documented for you without a lot of manual effort.
For more info on how to use the Tabular Model Documenter, see the original post.
what version of tabular did you use? I am getting an error.
This works for 1200 compatibility level models (SQL Server 2016). The DMVs changed at that point when we switched to the JSON (TMSL) back end. I haven’t tested with 1400, but I think it should still work. Let me know if you find otherwise.
I’m finding that it doesn’t work for either – there are a bunch of columns in DBSCHEMA_CATALOGS for 1600 that don’t exist in 1400. Do you mind if I just fix it up myself?
Thanks, really looking forward to using it.
* oops meant to say that it doesn’t work for me either in 1400 – fine in SQL Server 2016.
Thanks for letting me know. Feel free to take and modify.
Reblogged this on MS Excel | Power Pivot | DAX | SSIS |SQL.
Nice work! Do you think this could be modified for a PowerBI model?
Thanks for this tool. I had to make some slight tweaks as well to get it to work.
Thanks for the template!
Thanks for sharing. It has made an excellent addition to the ‘required’ documentation for hand overs and new starters.
Many thanks for useful article, you helped me a lot.
This is cool. I like the relationship map.
I am trying to use the template but am getting the query.firewall error from your previous post on this. I do not have any perspectives so am thinking that might be causing an issue. What would you suggest. 2016 tabular model.
I am having trouble building the model and am coming across the query.firewall issue you outlined in your previous post. Is there a workaround to this?
Someone else had that issue. They went into Edit Queries and selected the Perspectives tables and refreshed them that solved it. Let me know if that doesn’t do it for you.
We have been using your Documentation files successfully since your first blog post. However, recently (past month or two), the file does not refresh. There is an error at the Perspective Items query… “Query ‘Perspective Items’ (step ‘Expanded NewColumn2) refrences other queires or steps, so it may not directly access a data source. Please rebuild this data combination.”
Any ideas on how to fix this as we love the Power BI tabular documentation solution?!
Someone else contacted me about that as well. We tried opening the Power BI model and refreshing the queries individually in the Query Editor and everything worked fine from there. If you don’t have perspectives (because you are using this on another Power BI model) you can delete any queries related to perspectives. I’m currently working on an updated version, but I have a lot going on so it might not be ready for a couple of months.
Thanks for the quick reply. Telling our clients to have to refresh each query seemed a little clunky so I went hunting for another fix. It seems to be a query folding issue regarding the Privacy level of the data source you are documenting. So if you go to Data Source settings and Edit Permissions and then change from None to be either Organizational or Private, the query.firewall error is not thrown upon refresh.
I think that there is a better way to fix this by adjusting the underlying query structure but I needed a quick win. There are some articles posted about this error that I think may help us but I am not as savy with M/SQL as you are I am sure. See https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/
Spoke too soon, it worked for our internal SSAS cube but not on our clients. I am definitely confused now…
OK…I was able to fix the firewall error. Under File – Options, there are two Privacy tabs, the first is global and you will want to make sure the center radio button is selected that says Combine data according to each file’s Privacy Level settings. Next, click on the Current FIle – Privacy tab in the same options menu and select the Ignore the Privacy Levels and potentially improve performance option. This will allow you to refresh your report without error or having to bother with every query in the Edit queries view.
Thanks!
Hi, the Onedrive link to the .pbit is broken. Could you please give us the updated one ?
Cheers
Hi, Tristan. The link is working for me. I had someone else test it and it works fine for them as well. If you are still unable to download it, feel free to DM me on twitter (@mmarie) and we can work out another way to get it to you.
Hi,
I have downloaded the Power BI template file. I opened the file using Power BI desktop. In the server parameter, I have entered the Azure Analysis Services URL that starts with asaazure://… and selected the authentication as Windows Authentication(Also tried with Microsoft account authentication). After I click “Run” for each DMV query, it is throwing an error “The Server Parameter must be a valid host name or HTTP URL” for catalog table and no other tables were loaded. Can you help me to resolve this.
the link for the template doesn’t appear to be working. Any chance it could be update/fixed please?
I just tried the link and it is working just fine. It goes to OneDrive. If you want to use the contact form on the About page, I can email it to you.
ah… its the network I’m on… It blocked it… logged in differently and it worked.. thanks
How do you create this documentation using a PBI Model via PBI file?
Hi, Joe: You have to open your PBIX file and then find the local port. This blog post should help you do that: https://www.biinsight.com/four-different-ways-to-find-your-power-bi-desktop-local-port-number/. This template is a few years old now, so I can’t promise it will work on the latest PBIX files.