Solved

SSRS 2008R2 Shared Datasets vs Stored Procs

Posted on 2011-03-03
6
1,516 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 39

Expert Comment

by:lcohan
Comment Utility
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 39

Expert Comment

by:lcohan
Comment Utility
"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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 39

Expert Comment

by:lcohan
Comment Utility
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
Comment Utility
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 39

Accepted Solution

by:
lcohan earned 500 total points
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now