If you are familiar with documenting your SSAS model using the MDSchema rowsets, you may have noticed that some of them do not work with the new tabular models. However, with the new compatibility level 1200 SSAS Tabular models, new DMVs (Dynamic Management Views) have been introduced. These DMVs provide useful information about the model, but unfortunately, they are not documented at this time.

Don’t worry though, I have published a Gist that provides a list of these views along with some notes on the kind of information they contain. These DMVs can be used to document your model and are also compatible with Power BI models.

One of the main challenges when working with DMVs is that they require DMX queries, which limits your ability to join the data from the DMVs together. To overcome this limitation, it is necessary to store the data somewhere before transforming and merging them. One approach is to use Power Pivot in Excel 2016.

In my example model, I connected Power Pivot to a quick demo SSAS model based on the Wide World Importers DW database. I used Power Query to import the data from the DMVs, allowing me to merge the data into a model that makes the most sense without too many relationships and hidden tables.

However, when working with Power Query, you may encounter a limitation known as the “Formula.Firewall” error. This error occurs when a query references other queries or steps and directly accesses a data source. To work around this issue, you can redesign your queries to use “staging tables” and build finalized tables from them.

The Excel file I created documents various aspects of the tabular model, including database and model info, tables and source queries, columns, hierarchies, measures, KPIs, security roles, membership, and permissions, relationships, and perspectives. The file uses cube functions, pivot tables, and slicers to display the information in a user-friendly manner.

If you have Excel 2016 or 2013 with Power Query, you can download and use this model. Simply update the values in the TabularInstanceName and TabularDBName queries and refresh the data.

While the Excel file provides a quick way to look up specific information, we can take the analysis further by using visualizations in Power BI Desktop. By importing the Excel model into Power BI and making a few adjustments, we can create interactive reports that provide a deeper understanding of the model.

The Power BI report includes summary numbers, a bar chart for filtering table source queries and columns, a visualization of relationships between tables, details behind each measure, and security by role and table. These visualizations allow for a more comprehensive analysis of the tabular model.

Overall, the combination of Power Pivot in Excel and Power BI Desktop provides a powerful toolset for exploring and documenting SQL Server tabular models. Whether you need a quick reference or in-depth analysis, these tools can help you make the most of your tabular models.

For further explanation and a demo of the Tabular Model documentation, you can check out the Demo Day video on the BlueGranite blog.

Download the Excel model here.

View the Power BI report here.