SSAS 2008 R2 – Improvements for slow Metadata

February 8, 2010 · 2 min read

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.

This article is part of the GWB Archives. Original Author: Darren Gosbell

New on Geeks with Blogs

  • We Won The One Award I Actually Care About

    Full Scale made the Inc. 5000 for the fifth year straight, the 12th listing across my three companies. Here is why the one award you cannot buy is worth stopping for.

  • Your Customers Build the Features Now

    I let a tool I liked sit dead for a year rather than build the features I wanted. An MCP server meant I never had to, and your customers can do the same to your product.

  • Get the Size of a Directory in Linux the Easy Way

    du -sh for the quick answer, ncdu for the cleanup, df for the disk itself: every command for checking directory size in Linux, plus why du and df never agree.

  • Vim Search and Replace: The Ultimate Guide

    One :%s command replaces every match in a file before a find dialog would even open. The Vim substitute patterns worth the muscle memory: flags, ranges, capture groups, and multi-file edits.