If you were used to documenting your SSAS model using the MDSchema rowsets, you might have noticed that some of them do not work with the new tabular models. For example, the MDSCHEMA_MEASUREGROUP_DIMENSIONS DMV seems to just return one row per table rather than a list of the relationships between tables.
Not to worry, though. With the new compatibility level 1200 SSAS Tabular models came some new DMVs. They are, unfortunately, not documented at this time (as of 3 Oct 2016). I expect them to show up here at some point in the future. Until then I published a Gist that provides a list of those views as well as some notes on what kind of useful info is in each one. While there are 36 TMSCHEMA DMVs, the main DMVs you will want to check out for model documentation purposes are:
- TMSCHEMA_COLUMNS
- TMSCHEMA_MEASURES
- TMSCHEMA_MODEL
- TMSCHEMA_RELATIONSHIPS
- TMSCHEMA_TABLES
There are a few fields that show IDs rather than descriptions (e.g., ExplicitDataType in TMSCHEMA_COLUMNS, Type in TMSCHEMA_DATA_SOURCES). I’ve been able to figure out the descriptions that correspond to some of the values, but I’m still missing a few.
As with the MDSCHEMA DMVs, the TMSCHEMA DMVs can be used to document your model. This also works with Power BI models. You’ll just need to change the connection information to your Power BI instnce while the .pbix file is open in Power BI Desktop. For more information analyzing your Power BI model, see Chris Webb’s post. Since DMVs require DMX queries, you are somewhat limited in your ability to join the data from the DMVs together. This makes it necessary to store the data somewhere before transforming and merging them. Since I needed to document a tabular model for a client, I created a Power Pivot model in Excel 2016. You can download my documentation model here. My example model was connected to a quick demo SSAS model I made based upon the Wide World Importers DW database.
My Power Pivot Model
My Power Pivot model uses Power Query to import the data from the DMVs. This enables me to merge data into a model that I feel makes the most sense and doesn’t suffer from too many relationships and hidden tables (which is what would happen if you just imported the results of each DMV query into the model). In order to make this work for any model, I employed a trick I learned from Chris Webb to avoid duplicating connection information across queries. I built my model with the normal embedded connection strings and then converted them to use the connection Connection Info tables. As I did that, I ran into a Power Query limitation as evidenced by the error message below.
“Formula.Firewall: Query ‘QueryName’ (step ‘StepName’) references other queries or steps and so may not directly access a data source. Please rebuild this data combination.”
Ken Puls has a great explanation of how to get around this issue. Basically, you have to redesign your queries so that you have “staging tables”, which you can then use to build finalized tables. This redesign gets you around the error since the finalized tables don’t have the embedded source connection, just a reference to the results from another query.
This Excel file documents:
- Database and model info
- Tables and source queries
- Columns
- Hierarchies
- Measures
- KPIs
- Security roles, membership, and permissions
- Relationships
- Perspectives and the columns, hierarchies, and measures they contain
The Database Info tab uses cube functions to display the values shown. The rest of the tabs use pivot tables and slicers.
You can download and use this model if you have Excel 2016 or 2013 with Power Query. Just download the file, change the values in the TabularInstanceName and TabularDBName queries, and refresh. I was able to open the file and update the queries with no issues and no changes in Excel 2013.
This is great if you just need documentation or a quick way to look up specific information. But we could really analyze this using visualizations other than tables in Power BI Desktop.
My Power BI Model
I imported the Excel model into Power BI, made a few adjustments, and then added some visualizations. This report is a bit paired down from a visualization standpoint compared to the Excel file because I eliminated some of the data that wasn’t analytically relevant for me.
Click here to view this report in Power BI.
On the first page, I have several summary numbers that tell me about the contents of the model. It gives me a decent idea of the size and complexity of the model.
The second page uses a bar chart as an interactive filter against a list of table source queries and columns.
The third page shows relationships between the tables. Slicers on the left filter both the force directed graph and the table. Smart filters at the top of the page let you filter the Table and To Table in the relationships.
The fourth page shows the details behind each measure. A column chart at the bottom provides a count of measures by table and whether the measure is hidden, which acts as another interactive filter.
The last page shows security by role and table, again using column charts as interactive filters.
Check out the Demo Day video I made on the BlueGranite blog for further explanation and a demo of the Tabular Model documentation.
Update 19 Sep 2017: I have turned the Power BI report into a Power BI Template for easier use. You can download it here.
Data Savvy 
Wonderful tool, thank you!
This is a ridiculously useful tool, handed down on a silver platter to us mere mortals of BI. Thanks for sharing this!
Thanks for the positive feedback, Jorge and Steve!
Dear Meagan. This is the first time I use tmschema_* tables. I plug-and-play your excel with my 2016 Tabular cube, but it did not return any data (I have admin access to cube). I also go directly to do a select from tables (Select * from $SYSTEM.TMSCHEMA_*), it said query completed, but return 0 row. What am I missing?
Hello, Sue. Please check that you do not have any typos in the tabular instance and database queries. Next please check that your tabular model is deployed to that server and confirm that it is set to compatibility level 1200.
Rebuilding the cube (XMLA create) with compatibility level 1200 resolved the issue. Thank you Meagan, this is a excellent post.
Awesome, Megan. It was a piece of pie documenting my Excel & PowerBI models. Thanks!
Thank you Megan; that’s really handy and saves so much time
Hi Meagan, Thanks a lot for sharing this template, I got to know about this in Denver SQL Saturday. Can I use this template to document my Power BI models? It looks like the “Change Data Source” option has been disabled.
Yes, feel free to use the template. To change the data source, go to Edit Queries and change the text in the TabularInstanceName and TabularDBName tables. I’ve been meaning to parameterize and make this a true template, but just haven’t gotten around to it. It works for Power BI – you just have to find the port being used. Here’s a blog post about that: http://biinsight.com/connect-to-power-bi-desktop-model-from-excel-and-ssms/
wow.. that works perfect. Thanks again for creating this awesome PBI template.
I am having trouble using the template to document a PBIX file. Everything seems to be working (I hit OK several times to run the native queries) but it balks with this error:
! Perspective Items
Formula.Firewall: Query ‘Perspective Items’ (step ‘Expanded NewColumn2′ references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.’
I looked in the queries and I think the issue is that the table PerspectiveItemStage is empty which is causing “Perspective Items” to fail? Maybe?
Any pointers appreciated–this looks too cool for school… thanks!
Hmmm… it used to just leave those blank without problem. Since Power BI files don’t have perspectives, that is probably the issue. You can probably delete the tables related to perspectives and see if it works. I’ll see if I can get an update out in the next few days that works with Power BI.
I hit the perspective items issue too in powerbi. Just removing the table like you suggest and its staging table solved the issue.
Big thanks for providing this, it works really well.
Thx for an excellent template, helped us a lot 🙂
Had the same issue with Perspectives, but just going into Edit Queries and selecting the Perspectives tables and refreshing that solved it.
Thanks for the quick reply. I was not feeling adventurous yesterday… sure enough, just deleting the Perspective query and a Hierarchy query that was giving the same error allowed the model to load. VERY COOL–thanks for sharing!!!
works w/ Azure AS too!
That’s great. Thanks for trying it out, Bill!
Hi Bill , can you please tell me what to use as connection string for Azure AS I am trying to do that as well.
same question! This solution to documenting an Azure Analysis Tabular Model looks like the best answer to the question: “where’s my enterprise reporting model?” but when i use standard server asazure://southcentralus.asazure.windows.net/abcdefg i get an error about the dangers of native queries… so what tabluarinstancename are you putting in?
Pretty sure the native queries message is a warning, not an error.
Thank you Megan! That’s brilliant! I have one issue though: How to add the staging tables (which were disabled to load in QueryEditor) to the Data Model? The thing is that all the SQL syntax is there and not in the final Data Model tables. Or am I missing something?……. Many thanks!
The staging tables are purposefully disabled from loading to a table in the data model. I used them as sources for the final tables. I’m not sure why you would want them there as they would be duplicate information and make the model needlessly larger. But if you wanted to add one to the model, just find the query in the query editor, right click, and choose enable load. Hope that helps.
Thank you Meagan for your quick reply!
I think i didn’t explain it well… So my QueryDefinition column doesn’t show the real queries used to retrieve the data from the SQL Database, they just show “select * from… PowerBI query name”.
E.x. : SELECT * FROM [Fact_Urlaub]
And I need the initial source query with joins and etc. which run against SQL Server.
Ah, I understand now. Since this is built off of the SSAS DMVs, there is not a way to see the queries that don’t load to the model.
Thank you for the sharing! It’s Amazing!
Thanks for your template! This is brilliant! By the way, I found documentation of TMSCHEMA here: https://msdn.microsoft.com/en-us/library/mt719260(v=sql.105).aspx
Unfortunately, it points out that “The ColumnStorage object is reserved for internal use only.” Probably because the row counts and distinct column counts on that table are wrong. Best to remove RowCounts from your Table&Columns report. You can get row counts from DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS, but it would take a little work to integrate that into your template because of different format of the keys.
Thanks again for the template! You saved me a lot of work updating documentation.
Glad to know they finally documented them. And thanks for the tip about the counts. I’ll update the model when I get some time.
Thanks a lot for your template! It’s Amazing!
This is awesome! great work! Love the Power BI document
hi,
Has the OneDrive link been changed as the Power BI template is no longer available?
Thanks
The file is still there and available when I click the link . I had a coworker try it and it works for him as well, but it was rather slow.. If you can’t get to it, send me a DM on twitter (mmarie) or use the contact me form at the bottom of my About Me page with your email address and I’ll send it to you. Sorry for the inconvenience. Not sure what is going on with OneDrive.
This is so useful!! And so beautiful!! Thank you so much for sharing!
Very nice, Meagan! I especially liked the Force-Directed Graph that you used for Model Relationships! I was also able to quickly see which of my measures lacked description and fill them out quickly in my original PBIX file. I have one question: I am using the best practice of having measures be in their own “measure” tables (with no columns) – is that why they are not coming through to the visuals on “Measures” tab? I do see them with all of their attributes in the “Measure” table of your file, though… Thanks, Meagan!
Hi Megan,
Thanks for your great work here megan, it’s been a massive help.
I’ve just refactored the model as an exercise to try to apply tabular design best practise using Tabular Editor and as part of some work that I’m doing to analyse both the model and the dependent reports. I’ve pulled all the metrics into a single table and filed them into display folders, changed data types, refactored the lookups, changed column and measure references to follow SQLBI’s https://www.sqlbi.com/articles/rules-for-dax-code-formatting/
The refactor makes it easier for me to move the whole model between pbix files using Tabular Editor as we try to extend the functionality, so hope it will help any future users.
You can find it here and use it if you wish to use it, it is based on the tweaks done by Mindaugas over at https://www.laconiccharts.com/turbocharging-the-cube-documenter/ but it is very close to your original model should you wish to use it.
https://1drv.ms/u/s!AhXPNZ6Q3R7cgbJnZEecF5FnL0USFA?e=mk6fdE