Reporting on Analysis Services metadata using Power BI

Published by Thomas foster on

Analysis services has a series of what are called Data Management Views, which are essentially a way of seeing a bunch of meta data about your analysis services instance. With DMVs you can see a whole host of information such as activity, memory usage, active users, and more. However, to solve the issues I often come across the most useful DMVs are around the columns, measures, and tables that exist in the model. While you can query these DMVs through a tool like SSMS with SQL. Viewing these through Power BI can make the data exploration more interactive.

The four DMVs that are useful for this are called TMSCHEMA_TABLES, TMSCHEMA_RELATIONSHIPS, TMSCHEMA_COLUMNS, and TMSCHEMA_MEASURES. Combined all of these let you see all tables, fields, relationships, and measures within the model. To demonstrate this I have built a Power BI Report that uses all of these to show information around an Adventure Works DW model.

From using this pattern a few times, the most useful part is the ability to search items and being able to search for a measure and see it’s definition without having to launch up a separate tool such as DAX Studio. This technique will also work against datasets hosted in Power BI Premium tenants using the XMLA endpoints.

Download .pbix file

Leave a Reply

Your email address will not be published. Required fields are marked *