I have a report that uses a shared dataset. It also has several different slicers for viewing the data. The dataset is very large, so I created a cache for it so it doesn't take an eternity to load every time the user clicks on a slicer. The cache is set to expire every morning at 3:30am and refresh at 4am. The report is going to be used by 15 different clients and my company has a separate database set up for each client. So there are 15 versions of the report, each with a different data source.
The problem I'm having is that the cache is not working consistently. One day, all the reports run off the morning cache, the next day only a few reports use the morning cache and the others pull the live data (which means it takes several minutes to load). I've gone in and cleared the cache for each client, and the next day everything works fine, but a couple days go by and it's back to inconsistent.
One thought I had was there may be multiple copies of the same cache being stored and the report doesn't know which to use, so it doesn't use any. This shouldn't happen because the cache is cleared a half hour before it is refreshed, but is this possible? I would think if there were multiple copies of a cache, the report would use the most recent.
Another idea I had was that because there are 15 reports caching with the same parameters at the same time, maybe this is confusing the report. I would think it would use the cache associated with its data source, but could this be happening? Should I add a parameter to the dataset that has the client name, so there is no confusion?
Any other thoughts on what could be causing this would be helpful, thanks.
I figured out what was happening. Since all the reports were trying to cache at the same time, not all the caches were being successfully saved to the report server. I staggered the cache times, and that fixed my problem