Solved

SSRS 2008R2 Shared Datasets vs Stored Procs

Posted on 2011-03-03
6
1,592 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
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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 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?
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

685 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