Home > Reporting Services > Shared Data Sets – The Game Changer

Shared Data Sets – The Game Changer

Nothing like a snappy headline to get your attention. A nice surprise I stumbled upon while fooling around with the November CTP is something called Shared Data Sets. I now believe that it is a feature I will use a lot in future, as it has many advantages, but firstly , what is it ?


Quite simply it is a dataset that you can create independently of a report, which can then be deployed to the Report Server. Once that is done , the following interesting scenarios are available to us :


·         You can quickly create reports without worrying about the query as long as the dataset serves the needs of multiple reports.

·         The dataset can be cached to provide better performance while at the same time reducing the load on the SQL server across the execution of multiple reports.

·         It leads to interesting development scenarios , such as datasets being set up by and managed by developers , and report writers simply using them in report builder without having to worry about the back end side of a report. This scenario further builds on the “self-service” concept that Microsoft is pushing.


Creating a Shared Data Set

The first step is to fire up Report Builder 3. This can be done from the Report Manager website. You will then see a new screen asking you what you would like to create in Report Builder. On the left , select Shared Data Set.

On the next screen , you will be asked to choose a data source. Hence we see that to use a Shared Data Source, you will have to deploy a Shared Data Source ( something we could always do ). Select one that you have ( or create one quickly ) . You may be asked to supply credentials to connect to the source.



One the next screen , you will see , depending on the type of source , a query builder for either SQL server or Analysis Services ( haven’t tested this with those other databases yet). You can even call a Stored Procedure from your Shared Data Set. Once you have defined the query , you can click on a button called Set Options to define fields,  parameters and filters for the data set. At any time you can change the Data Source connection by hitting Select.




That’s it for creating it. You can then choose to save it on the Report Server itself. I like to now create a folder called Data Sets and put them there.


Now you can create some reports to use the shared data set. Create a report as normal , but instead of first defining a data source, you can now jump straight to creating a data set. Select the radio button called “Use a Shared Data Set” and you will be able to hit Browse and choose one from the server. Hit Refresh Fields and you’re good to go !



After deploying your report , you will see that it works as normal. If you navigate back to the dataset ( In report manager ), you can click on it and view all the properties. One of these is the Caching option.


This allows you to cache the dataset so that no matter how many report requests there are ( across multiple reports ) , the data will be served from the cache and no there will be no activity on the source database itself. The difference here compared to caching individual reports is that you only manage the cache on the data set , not each report itself ( once again , assuming you can create a dataset that can serve multiple reports ).  The other option you will notice there is the Security tab, which allows you to manage access to the data sets. With that in mind , watch out for the following issues :


·         Granting users access to a report but not the dataset.

·         If you’re using “pass through” authentication ( ie. Windows Authentication ) , the credentials will now be passed from Report Manager to the Report , to the Data Set , to the Data Source and finally the database.


Overall though I’m quite positive about the benefits. Even if you cannot cache your report data due to small allowable latencies, the benefit of editing a single data set instead of multiple reports for back end changes will be great in large deployments.




Categories: Reporting Services
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: