May 28, 2009 at 6:55 am
Hi all,
After working on several clients sites using SSRS:05/08, also SQL05/08 and Oracle, I have seen multiple ways of setting up their reports.
I have two main issues, being whether Option 1 or Option 2 is the way to go, and also I am keen to find out how people are managing their releases in regards to their stored proc's. I come from a developer background.. Not a DBA background, and currently working in the BI space.
Setup 1: SSRS hitting a stored proc to bring back a results set
PRO:
- Stored proc's are nicely contained and can possibly be re-used (doesn't generally occur in practice). - This may be an efficient option too as the all the processing is occuring at the database level. Parameters are passed into the stored proc.
CON:
- Management. I have found that if the stored proc doesn't match the report, it breaks. Sometimes when updating 5 reports and 5 stored proc's one may be missed, resulting in a dodgy release. And in practice the stored procs aren't really re-used.
Setup 2: Putting the SQL code directly in the report and using :Variable to pass in parameters.
PRO: Management: When you release the report, you know that the report will match the SQL contained. One point of failure, not 2.
CON: Microsoft's terrible development environment (Ctrl A doesn't even work, no intellisense or even highlighting). I end up using TOAD/SSMS to develop and then copy and paste the SQL in and out of the report.
Other things to consider. With option 1, the management of the stored proc's is a nightmare. This is due to the stored proc's being rolled up into a script that is deployed with every release.
If one character is wrong (easy to do when updating several proc's), the script and a report may not run. The other option here is to run delta's to update only an individual script, however at some point the client needs the scripts rolled up in-case the reporting server fails.
So the question is, what is the best practice? Does anyone have any microsoft articles to read?
And what are people actually doing out there?:unsure:
May 28, 2009 at 7:23 am
Option one there is no Con if you have source control and some design and plan phase, the stored procedures are prefixed report RPT so there is no confusion, in deployment you just copy the RDL and Solution files because the stored procedures are in the database. The most important benefit execution of the report generating code is controlled by the relational engine not SSRS so code that may fail will run.
One known issue when changes are made to the stord procedure to add more data, it is faster to rewrite the report because SSRS will not show the new data.
Kind regards,
Gift Peddie
May 29, 2009 at 7:00 am
Nice point on the efficiency argument, as I did suspect there would be performance issues with running the sql within the report.
I do still believe though, that stored proc's do come with a management overhead. Just the fact that if the sql is stored within the report, then everything is stored (in source control) in one place. The report is released with no other sql having to be released along with it. Also if a report is deleted, the stored proc may remain? Leaving orphansed stored proc's everywhere...
Also, what happens if the reporting server is blown away every time the schema is changed? Everything would need to be scripted, therefore maintaining this script would be an issue, and the script would need to be kept in-line with the report files...
What do Microsoft recommend? And how are other people maintaining their reporting servers?
May 29, 2009 at 7:57 am
Microsoft and most big companies use stored procedures because it is all stored in the datasource database under programming. You make changes to the stored procedure based on business needs.
Kind regards,
Gift Peddie
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply