SSRS 2008R2 Shared Datasets vs Stored Procs

Posted on 2011-03-03
Last Modified: 2012-05-11
Can anyone provide any advantages/disadvantages of using shared datasets over stored procedures when creating reports in SSRS 2008R2?  

I will be moving from a legacy reporting environment that was built using stored procs to a SSRS 2008 R2 environment.  I can leave the data abstraction as stored procs (many of which pull similar sub sets of data) but in an effort to more streamline the old process i was thinking of using Shared Datasets with the hope of eliminating many redundant procs. Just wondering if anyone has any experince doing this or any advice.
Question by:d1cjm1ex
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
LVL 40

Expert Comment

ID: 35031009
I would ask one question - does your business requires SSRS subscriptions? Like create subscription on a SSRS report to send an email with the report and/or its link when content refreshed? If yes then keep in mind that this does not work with shared data sets.
LVL 40

Expert Comment

ID: 35031137
"The shared data source that the shared dataset depends on cannot use Prompt or Windows Integrated credentials."

On the other side if you don't use subscriptions and your SSRS reporting server is split from your SQL a shared cached data set between multiple reports can help reports sharing that data set running faster and less trafic/execution of SP's against SQL.

Author Comment

ID: 35031296
Thanks for the info.

The plan is to use subsriptions and pass the required parameters (Region, etc)   and create the report.  So, based on your first post do I understand you correctly in that Shared Datasets do not support the use of Subscriptions (or even the other way, the use of Subscriptions are not support ed against Shared Datasets?

If I understood you correctly can you explain why?....I realize you are not Microsoft, it just baffles me as to why subscriptions and shared datasets can not work together.
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

LVL 40

Expert Comment

ID: 35031508
The problem is that you need to save the credentials of the data source for a report that needs to trigger a subscription for instance on snapshot updated but according to the info from above link "The shared data source that the shared dataset depends on cannot use Prompt or Windows Integrated credentials". We had the same issue so each SSRS report sending subscriptions is set with custom data source and saved credentials.

Author Comment

ID: 35031916
Based on how I read that article the statement "The shared data source that the shared dataset depends on cannot use Prompt or Windows Integrated credentials" only applies when Caching is enabled.  

If I do not enable cahcing does the subscription process still work with using shared datasets?  If so, does this negate any real benefit of shared datasets over stored procs?
LVL 40

Accepted Solution

lcohan earned 500 total points
ID: 35037740
I believe you are right but without cahching then why shared data sets when you hit SQL on each report execution? With SP's if the same SP is used by mutliple reports the SP plan and data will be cached in SQL right? I think it makes sense to use the shared data sets only with cache enabled especialy on split SSRS - SQL boxes just to offload sql cache by loading ssrs caching and reduce network trafic right?

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question