Power Query and SSRS Metadata

(Note: an update to this post is available here)

If you have ever tried to navigate XML using SSIS and/or SQL Server, and found it to be a little tedious, then you may want to try using ‘Power Query’ for your XML parsing. In this post, I’ll go step-by-step to demonstrate how easy it is to pull XML based SSRS report metadata from the Catalog table, located in the ReportServer database using Power Query:

The ‘Catalog’ table has an abundance of information concerning your deployed reports, however most of this data is locked away in an ‘image’ column which is named ‘Content’:

To demystify this column, and extract the associated metadata, open Excel and click on the ‘POWER QUERY tab:

Choose ‘From Database’, and select ‘SQL Server’. You will then want to enter the name of the server which holds your ‘ReportServer’ database. In this example we’ll simply use ‘localhost’:

Once you connect to SQL Server, double click on the database used by Reporting Services (typically this is called ‘ReportServer’), choose the ‘Catalog’ table and click on the ‘Edit’ button:

This will open the Power Query editor displaying all columns in the ‘Catalog’ table. Since we are only interested in ‘Reports’ at this point, you can filter out other catalog types by clicking on the down arrow next to the ‘Type’ column, choosing only type ‘2’, and clicking the OK button:

Notice that that ‘Content’ is listed as ‘Binary’ column. This can be converted to an XML column by right clicking the column header, choosing ‘Transform’, and then selecting ‘XML’. This will change the column type to table, which is actually parsed XML:

Next click on the ‘double arrow’ next to the ‘Content’ column header, and you can expand the current result set to include this ‘first level’ XML content (note, for cleaner column naming, you may want to ‘un-click’ the option to use the ‘original column name’ as a prefix):

Click the ‘OK’ button, and notice that Power Query has parsed the first level of XML tags into usable columns:

You’ll also notice that many more ‘tables’ have appeared as columns in the result set. This is because of the hierarchical nature of XML. To dive further requires repeating the ‘drill-down’ technique demonstrated above. If you want to expand additional “table columns”, you will probably want to save the query at this point using the “Only Create Connection” option. This technique will make the query ‘reusable’, so you can base another query on the results of this query. To accomplish this, choose “Close and Load to”, select “Only Create Connection”, and then click “Load”:

Once this is complete, you can ‘re-use’ the query by right clicking on the connection (labeled ‘Catalog’ below), and selecting ‘reference’:

This will create a second query where you can begin to drill into additional (XML based) table nodes. When you are ready to return the data in a usable form to Excel, choose the Close and Load to ‘Table’ option, and click ‘Load’.

If you would like a free copy of this Excel Power Query solution, just send an email to: info@bitracks.com and place “Power Query and SSRS Metadata” in the Subject line.