• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1707
  • Last Modified:

SSRS 2008R2 Shared Datasets vs Stored Procs

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.
0
d1cjm1ex
Asked:
d1cjm1ex
  • 4
  • 2
1 Solution
 
lcohanDatabase AnalystCommented:
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.
0
 
lcohanDatabase AnalystCommented:
"The shared data source that the shared dataset depends on cannot use Prompt or Windows Integrated credentials."  http://207.46.16.248/en-us/library/ee636149(SQL.110).aspx

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.
0
 
d1cjm1exAuthor Commented:
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.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
lcohanDatabase AnalystCommented:
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.
0
 
d1cjm1exAuthor Commented:
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?
0
 
lcohanDatabase AnalystCommented:
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?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now