PPS M&A SP1 DataSource Cache Bug

After installing SP1 I had a demo dashboard that all of a sudden performed far slower than it did on the original RTM build. The analytic charts pointing to my OLAP cube were unbelievably sluggish; like 30 sec+ to return result sets on queries that, when executed in SSMS, ran in milliseconds. After some testing I came up with the answer. It was the Cache Setting on my data source. At some point I had set the cache interval on the data source to 0 and not changed it back to a more reasonable number.

In the RTM build of M&A a cache interval of 0 on a multidimensional data source didn't have a noticeable performance effect, you simply lost any caching benefits as the cube was always re-queried. A bug in SP1 makes queries to MD data sources with a 0 cache setting take far longer. When I contacted them the PPS team confirmed that this is indeed a bug and they're already working on a fix.

The workaround: If you have already applied SP1 find any published multidimensional data sources with a cache setting of 0 and set it to something higher - anything above zero seems to work. Unless you are hitting an absolute zero-latency cube there aren't many good reasons for not taking advantage of some caching. I often find that the 0 creeps in there when testing and gets forgotten.

As far as I can ascertain tabular data sources do not seem to be affected.

An easy way to find your published data sources with a 0 cache setting? Easy! Use the MAUDFs. Once MAUDF is set up finding the offending data sources is as simple as running this query:

SELECT * 
FROM MAUDF.DataSourceMetadata()
WHERE DataSourceType = 'Multidimensional'
  AND MinutesToCache = 0