SSRS #48 – Reporting Services Data Cache in Development

If you have developed a few SSRS reports in BIDs, you must have noticed that BIDs uses a data cache to speed up your development work. Your design-preview-design-preview cycle is fast enough that you probably have never complained . Not yet.

One of my reporting Services reports is still in development phase. After receiving a Success email notification from my ETL job in the morning, I opened up the BIDs, and tried to preview my report. I was expecting data from yesterday, but I didn’t see it.

Issue

I am using BIDs 2008. The SSRS report is accessing an Analysis Services database using MDX. I checked my fact table which has data from yesterday. I know my cube processing was successful. Browsing the cube in SQL Management Studio also showed data from yesterday. However, in BIDs, when previewing the cube, I didn’t see my data from yesterday.

A Quick Solution

I understand the data cache feature. So I didn’t panic. right click on the dataset, select Query.

On the Query Designer, click the link to execute the query.

This will pull fresh data from the back-end data source. Now I see data from yesterday on Preview.

Another Even Quicker Solution

After I published this post the first time, Mark W, a regular reader, pointed out that the Refresh button is an even quicker way to retrieve the most recent data from the back-end data source.  Thanks Mark!

Reasons for this blog

There are three reasons for this post. One is for the benefit of new SSRS report developers who might be unaware of the data cache feature in BIDs.

Second I am still not sue if BIDS 2008 behaves differently from BIDS 2005 in terms of data cache, or it behaves differently when accessing a cube.

Third reason is to give you a link to this blog post Disable Reporting Services Data Cache in Development. In this blog, the author pointed out that you can permanently disable the data cache feature in BIDs. I personally will not do this, because I really like the data cache feature.

In case somebody wants to give it a try, here is what the author suggested.

Find the config file that controls the Report Designer in BIDS. For SQL Server 2008 SSRS, this should be:

C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\RSReportDesigner.config

Then change CacheDataForPreview to “false”.

SSIS – More secrets about package configuration

I’ve written one post about SSIS – Enable Package Configuration for Deployment, and another one about  SSIS – Add Configuration Files for Deployment.

I am afraid that after reading these two posts you are quite confused. You are probably screaming silently. Why is she talking about environment variables, together with configuration files? Do we need both, or just one of them? Why did my job still fail after I changed my password to DB2? Questions, one after another…

So here I have more writing to do to clarify the confusion.

First of all, after reading those two posts, you already know:

1) how to create the environment variables,

2) how to create the configuration files in XML, and

3) how to enable package configurations in BIDS.

4) You also know how to point to a configuration file for your SQL job step when executing a SSIS package.

The first 3 “hows” are important as a SSIS developer. There are a few more”secretes” regarding package configuration you will also need to know as an experienced SSIS developer.

Here are those “secretes” (assuming that you have done the first 3 hows in BIDS).

1)  There are a couple of ways to test your package in BIDS. Here is one, by right-clicking on the package name in the Solution Explorer.

Have you ever thought about how you are connecting to your sources when you test you package in this manner?

Is the connection done through one of the configuration files?

Or by the login credential you entered in the Connection Manager?

The answer is by the package configuration files you’ve specified in your  environment variables.

If this is your habit to test your package locally, then you will need to maintain your own package configuration files with the correct user id and password (or through Integrated Security mode).

2) Well, the above is not my favorite way for testing locally. I very often test just parts of my package at a time by right-clicking on a sequence container. When I test in this manner, how is my package connecting to the sources?

The correct answer this time is through the connection manager. So you better make sure that you have the correct credentials for each of your connection manager.

3) so what’s the third secrete? The third one has to do with the SQL Agent job you’ve created to execute your package.  

Again, I assume that you have done all the right things in your BIDS locally, and have successfully uploaded your package to the Integration Services, and have created a job step to execute your package.

I will also assume that your server administrator has also done on the server exactly the same things you have done locally. 

The secrete is that you do nothing in the job step. After all the hard work you have put in, you deserve a break.

By nothing, I mean putting nothing on the Configurations tab.

I also mean doing nothing on the Data sources tab. Do not check any of the check boxes for the connection managers.

By doing nothing, your job step will use the environment variables on the server to point to the correct package configuration files (DTSConfig files we’ve created), which have the correct credentials to connect to the data sources.

4) Last secrete. This is a secrete I’d rather not know. Adding package configuration files for your job step, is only necessary if you want to overwrite the package configuration files defined by your environment variables. There are case where you want to do this, but I am hoping that you will only need to do this for very few of the packages you’ve designed.

I am hoping that this post will leave you feel better about package configuration files and package deployment.