Speaking the language of business intelligence with an Australian accent

Monday, June 9, 2008

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.

ZeroCacheSetting

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

ZeroCacheDataSources

2 comments:

Brookmyre said...

No cubes...

Not a relevant place to put this but it is vaguely linked...

What would you suggest if a client does not want to use Analysis Services? Would PP Monitoring be worth the money without cubes?

Nick Barclay said...

It's possible to implement M&A without SSAS data sources but you would definitely be missing out on the most compelling and valuable features of the product.

M&A without an SSAS cube behind it is a waste IMHO. I would question someone's analytic intentions if they are unwilling to implement SSAS on their data.