Using ByAttribute or ByTable Processing Group Property with Analysis Services 2005
As noted within the Analysis Services 2005 Performance Guide, there are some niche situations where setting the Processing Group property to ByTable provides more optimal processing than the default value of ByAttribute.
In a customer scenario, we had discovered that they had two dimensions (each of which has >25 million members and 8-10 attributes) where the Processing Group property was set to ByTable. While the ByTable setting could theoretically have Analysis Services process faster (because it takes the entire set of dimension data and places it in memory), it didn’t in this case because it had taken approximately 80% of all available memory (approximately 25.6GB out of 32GB physical memory) to place just one dimension into memory. Due to the large size of the dimensions, there were also issues in the dimension processing completing in a timely manner. The setting ByTable is an optimization that bypasses normal checks and assumes that there is enough memory to process all attributes concurrently in memory. If this is not true, this may result in processing issues and/or errors. Therefore, it is important for you to monitor memory usage; especially if dimension size grows over time.
Note, we had investigated using the MaxParallel setting to limit concurrency. Often this is helpful during partition processing, but it was not helpful during dimension processing for our scenario. We feel it was not useful because the root cause is the large amount of memory consumed when using ByTable, and either it is only one dimension causing the problem, or the memory quota mechanism was sufficient to prevent doing two very large dimensions concurrently.
With the attribute set to ByAttribute, the maximum amount of memory Analysis Services had to take up during processing was 9GB (vs. 25.6GB on the same server). That is, the default setting in many cases will use less resources and process in a timely manner. More specifically for this scenario, the reason for the processing issues was because of the large size of the dimensions (# of members, # of attributes, etc.) and when using the ByTable setting, AS had tried to put the entire dimension into memory instead of delegating this process to the relational database (i.e. when using the ByAttribute setting).
Contributors: Denny Lee, Richard Tkachuk, Akshai Mirchandani, Eric Jacobsen
Comments
Anonymous
October 19, 2007
There is an interesting post over at blogs.msdn.comAnonymous
March 31, 2009
Author: Nicholas Dritsas Reviewers: Thomas Kejser, Stuart Ozer In SSAS 2005 and later, there is a dimensionAnonymous
February 23, 2011
Here are some other articles on SSAS Processing: ssas-wiki.com/.../Articles