SSAS 2008 R2 – Improvements for slow Metadata

[Darren Gosbell]

There was a thread last year on the SSAS MSDN forum SSAS 2008 -- Why is metadata so slow??? in relation to slow metadata queries on a cube with 250+ measure groups. The good news is that despite the fact that the SSAS team has largely been focussed on PowerPivot for the SQL Server 2008 R2 release, they have also tried to address a few of the customer pain points in SSAS. Below is part of a recent response to this thread from Akshai Mirchandani from the SSAS development team:

"As an update to this discussion, we did implement an improvement to the way this EstimatedSize property works in the upcoming SQL Server 2008 R2 release -- the latest CTP includes this change. It would be great to hear from any of you who can try this out to see what the impact is on the performance in real-world situations.


This change does require you to update all your partitions and dimensions in some way -- the server will persist the estimated size of those objects at that point and not require scanning their file sizes every time you discover metadata. ProcessUpdate on the dimensions and ProcessClearIndexes+ProcessIndexes on the partitions should do the trick. Without updating the dimension/partition objects things will still work, but without the performance gain...*
Additional note: due to these types of changes, you may not be able to take a 2008 R2 database downlevel to a 2008 server.


As most of you have probably heard, AS 2008 R2 is primarily targeted at the self-service user with the new PowerPivot solution -- but we've tried to tackle a few of the pain points for our existing customers along the way. Some examples:

• This discovery of metadata issue
• Processing of a single (typically small) partition in the context of a very large number of metadata objects (e.g. 1000s of partitions) should now be significantly quicker. The algorithm that analyzes dependencies was optimized for this scenario.
• Also, R2 now supports calculated members in subselects -- and the new Excel 2010 pivot tables will let you make use of that feature."

If are having issues with slow metadata for a large database/cube then it would probably be worth giving the November CTP of SQL 2008 R2 a try. If you have any feedback for this issue feel free to post a comment either here or on the original forum thread.






Get More from the original blog...