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.