SSRS Inception – Part 1

large_inception_blu-ray_4

Hi everyone,

This is the first part of a series of blogs looking at a session I am delivering at SQL Saturday Exeter on the 25th of April 2015. The session is titled “SSRS Inception: Reporting on Reporting Services”. In this session I hope to give you the necessary tools and information you need to begin monitoring SSRS.

Why should we monitor SSRS? 

In SSRS there is no obvious way to see which reports are being used and how often. If you have a large SSRS environment then this can have an impact how your users consume your reports. How do you know if no one is looking at a report because it takes too long to run. With this in mind I went about developing a way to see which reports are being used, how often and by whom.

How to monitor SSRS

Before we begin Microsoft has a warning about playing with the SSRS database:

The table structure for the databases is optimized for server operations and should not be modified or tuned. Microsoft might change the table structure from one release to the next. If you modify or extend the database, you might limit or prevent the capability to perform future upgrades or apply service packs. You might also introduce changes that impair report server operations… All access to a report server database must be handled through the report server.. [1]

Ok, so it is clear Microsoft do not want you to mess with your production instance of SSRS and if you do you your instance might no longer be supported by Microsoft. You have been warned. But… that doesn’t mean we can’t play with a replicated version and leave the primary relatively untouched. To do this you have a few options. You could use transactional replication, log shipping, restoring a nightly backup or something different- I leave this part up to you. It really depends on how often you want your data to refresh. For my purposes a nightly run is fine.

But before we get in to how we can do this, let first have a look around the tables which comprise the SSRS DB.

SSRS database architecture 

I am using SQL Server Reporting Services with SQL Server 2012. When you configure SSRS you’re prompted to create a database to hold the definition of your reports. When you connect to SQL Server you’re able to see the database listed. Your database will be listed along with a TempDB.

SSRS_1

 

 

 

If you expand this database you can see 34 tables, these tables are used to manage report items, subscriptions, schedules, report snapshots, system properties, data sources, datasets, symmetric keys and the execution log details – where most of this session is focused.

To support monitoring our executions we are going to need data from the following tables:

Catalog – The catalog holds all the juicy metadata about all the objects stored in SSRS.
Datasets – Info about published shared datasets.
DataSources – Info about published shared data sources.
ExecutionLogStorage – This is where the magic happens, the who, how, when why. All your questions are answered here!
ModelItemPolicy – Bridge between Catalog and Policies.
Policies – Used as part of the SSRS security
PolicyUserRole – Used as part of the SSRS security
ReportSchedule – Bridges Catalog, Schedule & subscriptions together.
Schedule – Details about when and how often a report is set to auto generate.
Subscriptions – Who is subscribed to receive a report.
SecData – All the detail about who has access.
Users – Who has access.

Before we go on, there are some caveats to consider before you start working with the SSRS DB.

1. Firstly a lot of the content is stored in XML. To get all the best bits of data you need to be able to write basic XML queries (XPATH).

2. A lot of what you find in the SSRS DB has very little documentation . For example there are tonnes of integer values for attributes like type, but there is no reference/enum/lookup table for type, this is common for almost all the tables. Where this happens I have done my best to outline which each value relates to.

3. SSRS DB will only store data for as long as you need it, be default it only stores data for 60 days – So when you start this process it is unlikely you will be able to mine anything more than 60 days of executions. However this can be altered by logging on to the SSRS DB and updating the ConfigurationInfo table to set the ExecutionLogDaysKept to something greater than 60 – depending on how many users and reports you have something like 120 is fine.

UPDATE  ConfigurationInfo
SET     Value = '365'
WHERE   Name = 'ExecutionLogDaysKept'

As SSRS DB only keeps executions for a short amount of time (unless it has been changed), I have modelled this project as a data warehouse – in the Kimball style. We essentially then have a lot of dimension tables such as Dim_Catalog, Dim_User and all the associated dimensions Dim_Date & Dim_Time. This follows the basic Kimball star schema. This is loaded as often as you need it to be, using SSIS. As part of the scripts section there is a script to create a job that will execute the SSIS package. The SSIS package isn’t very complicated and I will go in to more about it in the next blog.

At the end of this series of blogs I will make the scripts & packages available to download. Included will be everything you need to set up a clone of your SSRS DB, extract data from that, build where the data is to be loaded (the SSRSDB) and all the views for interrogating the data warehouse. Also included are a set of reports that will allow you to manage your reporting server. There is a lot more built in to this that I will also blog about and make available.

Within the SSRSDB, there are additional tables that allow you to create a metadata driven theme layer on top of your reports. This requires you to load information about each report in to the ReportServerDW. To help you to do this an Excel file is also included in the download. Once this has been populated, a job can be created (again in the download) that will notify users (via email) who asked for a report to be created, that their report has not been used for X amount of days and give them the option to save their report. If the users fails to save their report it was either removed or hidden. This is configured per report in the Excel document.

What this project can also do which is pretty cool, is act as a source control for your reports. Ok it isn’t really a source control because it doesn’t allow all the nice thing like branching, however it will store a version (in XML) of each iteration of a report uploaded. I will also include an SSIS package that will export all the historic versions on a report.

Think this is neat? Me to. In the next instalment we will look more in to the data being stored and the SSIS package which is used to load the data.

Thanks for reading.

[1] https://msdn.microsoft.com/en-gb/library/ms156016.aspx