SSAS: ProcessUpdate - Too much of a good thing

I came across something interesting on the Analysis Services MSDN Forum a little while ago - this post has been on my todo list for a while :). When processing a large dimension (10 million+ members) the following error was encountered.

File system error: A FileStore error from WriteFile occurred. Physical file: ....

At first it sounded like some sort of data corruption which was what someone else suggested and the proposed resolution of re-deploying and fully re-processing would have corrected the issue, but the original poster contacted Microsoft Support and got the root cause of the error confirmed and I thought I would re-post it here as it may save some people some hair pulling.

I had this problem many times, until I found a totally unrelated article pertaining to a 4 GB limitation for the .asstore file in MOLAP dimensions.  I talked to Microsoft, and they investigated and found that the limitation does exist.  If it happens again, I would suggest browsing the data folder where the database exists and opening the failing dimension folder.  If you see that the dimension file with a .asstore extension, or any other one that is over 4 GB, you have found your problem.  There is nothing short of doing a full process on the dimension (which the Microsoft guy suggested, and I had to yell at him (in a nice way)) when it hits the limit. If you MUST Process Update and you cannot use ProcessAdd on your dimension, I would suggest breaking the dimension in a linear fashion into several dimensions (column by column), or finding a new way to build the dimension where it will not grow exponentially.  A Process Update appends string data to the file, and that is why it gets so large.  I would also suggest taking a look at the AS dimension and see if there are any character columns you can cut down in size. 

(The original thread can be found here )

This brings about another issue that a few of us on the Analysis Services Stored Procedure Project had been discussing. If you are doing ProcessUpdate on a regular basis does your dimension fragment? 

We were speculating that the internal keys used by SSAS would get fragmented and have an impact on the performance of member lookups, similar to the fragmentation that can occur with indexes in the database engine. While we don't have any concrete evidence to support this yet, given the information above it does sound reasonable.

So if you are doing ProcessUpdate on a regular basis, I would suggest keeping an eye on the size of your dimension files and on query performance. Feel free to leave a comment if you have any input on this.

Print | posted on Sunday, April 29, 2007 9:59 PM