Power Query and SSRS Metadata (Remix)

After spending some quality time with Power Query, we now have a new and improved version of our Excel ‘SSRS Metadata’ solution.

For some background information, and to see more on how to get started with Power Query and ‘SSRS Metadata’, please see our earlier post: Power Query and SSRS Metadata

Why the ‘Remix’?

Part of this (remix) effort was simply to see if Excel’s Power Query was robust enough to duplicate the functionality in SSIS that is required to build more complex ETL processes. In this case the answer happily is ‘yes’.

Another reason for this approach was simplicity. Our ‘BI Stack’ solution required an existing SQL database and schema, and also needed configuration and integration of the SSIS and SSRS components. In short, a lot of complexity, where a simple solution could work.

Back to this free SSRS Metadata solution…

The Excel workbook has one worksheet that holds the configuration values to point to an instance of SSRS:

After configuring the above, you just need to click on ‘Refresh All’ (found under the Data menu) in Excel:

This ‘Power Query’ based solution will then extract all kinds of interesting information from the ReportServer database, including Report details, Data Sources, Datasets and embedded queries, and Parameters. This metadata can be used to answer key questions such as:

• Have we deployed redundant data sources?

• When was the latest change made to a report, and by whom?

• Are we being consistent with our Parameter usage?

• Are we being consistent with the settings of Margins, Headers and Footers?

• What SQL code is embedded in our SSRS Datasets?

If your reports number in the hundreds, you can imagine what a time saver it is to automate the retrieval of this metadata. Now this capability is just a few clicks away. To get started, just click  http://www.bitracks.com/#!ssrs-metadata/dxrkr  for your free Excel ‘SSRS Metadata’ solution download.