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.
Some of the other references that I looked at during this to fix the processing issue were the following:
- Using ByAttribute or ByTable Processing Group Property with Analysis Services 2005
- HELP! I cannot process a large dimension
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: