In my last post we talked about shared vs embedded data sources, in this post we’ll be talking about shared vs embedded when using data sets.
In SQL Server Reporting Services (SSRS) you create “data sources” to tell your reports where you want to pull your data from, but you then need to create “data sets” to tell the report what data you actually want. Data sets store the query that will be issued against the data source. Just like when you go to create a data source, one of the first decisions you’ll need to make when creating a new data set is if it will be “Shared” or “Embedded”. And just as I mentioned in the last post, choosing wisely and creating the right type from the beginning can definitely save you some aggravation in the long run and make things much easier to manage overall. The difference between a shared and an embedded data sets is pretty straight forward, but let’s go ahead and review them real quick.
Embedded data sets: When you create an embedded data set, the query, and which data source the query should be issued against is stored inside of the report itself, and can not be referenced by outside reports.
Shared data sets: When you create a shared data set, the query and which data source it references is saved as its own object, independent of any report. When you create shared data sets, they must reference shared data sources.
So that sounds pretty straight forward, but which is best? Again, it sort of depends, but interestingly enough my advice is exactly the opposite of what I gave for Shared Data sources. In general I tend to use embedded data sets almost exclusively. (Opposed to data sources, where I used shared almost exclusively)
My reasoning for using primarily embedded data sources is this: While you’ll probably only have a handful of data sources for a report project, you’ll likely have WAY more data sets. (One for every query your report uses). It’s not uncommon to have 5-8 data sets per report. Lets say you have 20 reports, that turns into a ton of management. Some of the questions you might find yourself asking are:
- How do you know if a shared data set already exists or if you have to create a new one?
- If you change one of the data sets, what reports are impacted?
My experience is that shared data sets are seldom reused, and instead data sets are re-created anyway. The one place where I think they can come in handy though is if you’re populating parameter dropdown values with data sets, and several of the reports need to have the same parameter values on/in them. In this case, using shared data sets can be a nice was to drive consistency between reports and save yourself a little work having to re-create the queries for each report.
Have you leveraged shared data sets in your environment? Tell me about its success/failure it in the comments.