Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Large Dimension Processing issue – File system error

Posted by denglishbi on May 11, 2009

A couple of weeks back I experienced an issue with a some what large dimension.  The dimension had around 15.5MM rows and it continued to error while processing the key attribute within the dimension in Analysis Services (SSAS) 2008.  I don’t believe this is a version issue at all and I was able to come up with a solution that resolved the processing issue which I will explain.

What was happening was that all of the other attribute hierarchies were processing fine, but every time the key attribute was being processed it would continue to error out in the same spot (around  60K rows) and it would return this error message (I swapped out a few names to protect the innocent):

File system error: While attempting to read information from disk, a read error occurred for physical file: \?D:Program FilesMicrosoft SQL ServerMSAS10.MSSQLSERVEROLAPTempMSMDCacheRowsetBadRows_1932_16629_jm313.tmp, logical file: . Errors in the OLAP storage engine: An error occurred while the ‘DimensionName Key’ attribute of the ‘DimensionName’ dimension from the ‘SSAS_DB’ database was being processed.

This processing was going against an Oracle data store, not that I feel that the issue was related to that, but just a disclaimer.

I started in by verifying the drive space for the Temp directory that gets utilized during the processing to ensure that enough space was allocated.  I also review the design of the dimension to make sure that options like hierarchies are not optimized and not ordered were being set properly.  I also verified the SSAS server properties to make sure that the EnternalCommandTimeout was adequate to make sure that the queries would not timeout during processing.  Unfortunately none of this seemed to make a difference and I continued to get the same error message.  I even verified the size on the asstore file for the dimension attribute thinking that maybe I was bumping up against the 4GB limit, but it was only around 355MB.

After searching the web and forums I came across an item that seemed like a potential game winner – Using ProcessingGroup Dimension property option ByTable vs. ByAttribute may error with string keys.  At this point I had nothing to lose and based on the hardware – Dell 2950s with 32GB of RAM and 2 quad core processors, plus we were running x64 software – I felt that this was a good solution.  I went ahead and switched the ProcessingGroup property on the dimension from the default ByAttribute to ByTable.  So basically a massive query gets executed and the results get cached which is then used to process all of the attribute hierarchies.  In running this process not only did the process complete successfully, but it actually shaved about 20% of the time off for processing the dimension.  You definitely need to read up on this process, make sure that you have the hardware in place, and do some proper testing.

image

Some of the other references that I looked at during this to fix the processing issue were the following:

As always you should refer to the whitepapers that have been provided on the SQL Server Best Practices site and a few key ones to point out are the following:

Advertisements

One Response to “Large Dimension Processing issue – File system error”

  1. Sam Kane said

    Here are some other articles on SSAS Processing: http://ssas-wiki.com/w/Articles#Processing

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: