?
Solved

SSRS 2008R2 Shared Datasets vs Stored Procs

Posted on 2011-03-03
6
Medium Priority
?
1,641 Views
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.
0
Comment
Question by:d1cjm1ex
[X]
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
6 Comments
 
LVL 40

Expert Comment

by:lcohan
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.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 35031137
"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
 

Author Comment

by:d1cjm1ex
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.
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 40

Expert Comment

by:lcohan
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.
0
 

Author Comment

by:d1cjm1ex
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?
0
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 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?
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

752 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