March 29, 2017 at 8:51 am
I know it has been asked before, but does anybody have SSRS 2016 Table Definitions, even if in part? I am working on a SSRS report monitoring project - trying to design something and was hoping there were some table definition out there that may assist.
Looking to capture the following historical data
1. Report Locations
2. Subscriptionconfiguration
3. BI Team want PerformanceReport of the SSRS reports and a baseline
4. BI team want any ReportChanges
5. DBAs want any Data Source changes
6. ReportOwner
7. UsageReports, last run, how often and was it a subscription of user driven.
8. Executiondetails for supporting reports
9. Create an alerting system when threshold changes occur
Just looking to do a proof of concept for the team is proving a challenge because there is so little documentation. At least in 2008 we could dredge the report server with VBS and build a small data mart.
Anybody out there doing similar that has started a dictionary yet?
March 29, 2017 at 2:23 pm
lnpurdie - Wednesday, March 29, 2017 8:51 AMI know it has been asked before, but does anybody have SSRS 2016 Table Definitions, even if in part? I am working on a SSRS report monitoring project - trying to design something and was hoping there were some table definition out there that may assist.Looking to capture the following historical data
1. Report Locations
2. Subscriptionconfiguration
3. BI Team want PerformanceReport of the SSRS reports and a baseline
4. BI team want any ReportChanges
5. DBAs want any Data Source changes
6. ReportOwner
7. UsageReports, last run, how often and was it a subscription of user driven.
8. Executiondetails for supporting reports
9. Create an alerting system when threshold changes occur
Just looking to do a proof of concept for the team is proving a challenge because there is so little documentation. At least in 2008 we could dredge the report server with VBS and build a small data mart.Anybody out there doing similar that has started a dictionary yet?
As you already found, there is no such documentation since Microsoft doesn't support querying the tables. The only thing supported to query is the execution views. The only thing I've ever seen listed in quite a few places is the type on the Catalog table - and I've seen different things posted on that as well. Usually it's a long the lines of:
1=Folder
2=Report
3=File
4=Linked Report
5=Datasource
6=Model
7=ReportPart
8=Shared Dataset
It's not really a relational database I would guess is part of the reason. The supported method is to go through the SOAP API to get what you need using something like Powershell.
If you actually do go through the tables, you'll find that there is a lot of variation on what get stored where based on what combination of report, authentication, etc. It has some odd things like the DataSource table having the names for the shared data sources using integrated security. Then you have others as just DataSource1. Those with Link populated, that points back to the Catalog table ItemId where type = 5 and that's where the data source name is. So the way things point to each other is conditional. You can find a lot of examples of different queries that can give you an idea of what data is where - with many report pieces being in the Content column of the Catalog table so you would need to do some XML shredding to get at that information. Overall, it seems that you can find bits and pieces based on different scripts that have been written. And you can also find that some of them don't work. I would guess that's mostly due to all the possible combinations of everything and often companies have some standards they follow so someone writing a script wouldn't necessarily find that somethings are, as an example, in the Catalog table instead of the DataSource table.
If you are used to going through the tables using the SOAP API which is how you would have accomplished it before then you may want to start looking for some Powershell scripts that get some of the information you are after. There are quite a few examples if you search for them. This is kind of an introduction to it with a couple of examples:
Scripting and PowerShell with Reporting Services
In terms of going through the tables directly and shredding some of the XML, there was recently an article posted up here that demonstrates some of that - and also check the discussion link as there is another great example in there by Lowell showing some of the changes he used to get it working in his environment:
Dynamic SSRS report documentation via a ReportServer.dbo.Catalog query
Sue
March 30, 2017 at 2:14 am
Sue
This is ever so helpful, I cannot thank you enough. We are using VBS to dredge the logs now to get information and build a Data Mart. It looks like a previous DBA used the approach in this article to build 2005 & 2008 monitoring: https://www.mssqltips.com/sqlservertip/1908/analyze-report-execution-and-usage-statistics-in-sql-server-reporting-services/
However, when the company moved to 2012 they did not implement anything and are looking to in 2016 now. I cannot thank you enough for your help and I will post back when we come up with a design and implement it. Sharing is caring. 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply