Monday, 3 December 2007

Custom roll-up performance problems

Sam - a colleague of mine - came across an issue when working with custom roll-ups in SSAS 2005, so I thought I'd post it here for future reference. Apparently since SP2 performance is significantly degraded (in our case by about 10 times) when a custom member formula is used to calculate a measure. MS are quoted
as suggesting that changing a the Cache Policy setting in the msmdsrv.ini to 9 and restarting SSAS solves the problem. This worked for us. Problem is that its a server-wide setting so lets hope it won't cause a problem somewhere else in the future.

Why bother using custom rollups? Well they're particularly useful when creating accounting reports because the aggregations sometimes depend on values of other members, rather than on directly related measures.

No comments: