With the server and database known, parameters and queries can be created in Power BI Desktop to stage the DMV data for further transformations. In the following screenshot from the Power Query Editor, three query groups are used to organize the parameters, the DMV queries, and the enhanced queries (Metadata Report Tables) used by the report:
Power Query Editor in Power BI Desktop
As per the TablesDMV query, the two parameters (AnalysisServicesServer and AnalysisServicesDatabase) are passed to the AnalysisServices.Database() function for each DMV query. As indicated by the gray font of the DMV queries and the parameters, these queries are not loaded to the data model layer.
To update the metadata report in a future session to reflect changes to the dataset, the server and database parameter values would need to be retrieved again. These values could then be passed to the data model parameters, thus allowing all queries to update. This manual update process is necessary with Power BI Desktop files, given changes to the port and database ID, but is not necessary for metadata reports based on Analysis Services models.
Given the small size of the DMV data and the limitations of SQL SELECT queries against DMV data, a simple SELECT * is used to expose all columns and rows. The Metadata Report Table queries contain all the joins and transformations to prepare the data for reporting.
Given the small size of the DMV data and the limitations of SQL SELECT queries against DMV data, a simple SELECT * is used to expose all columns and rows. The Metadata Report Table queries contain all the joins and transformations to prepare the data for reporting.