4 Gig limit in SSAS 2008 Dimension Processing
*Please note that this post is a work in progress and I’m still waiting on more answers but I wanted to get the information out there in case anyone else is running into this issue too:
OK that title might be a little bit over the top for what I’m dealing with but if you were in my situation that’s the exact kind of article title that you have been looking for. I have been trying to migrate a set of cubes from Analysis Services 2000 to SQL Server Analysis Services 2008 and have run into a number of glitches but the most annoying of them was this one…
- File system error: While attempting to read information from disk, a read error occurred for physical file: ?D:MSSQLMSAS10OLAPTempMSMDCacheRowset_1868_258c_nompm.tmp, logical file: . Errors in the OLAP storage engine: An error occurred while the ‘generated attribute’ attribute of the ‘MyDimension’ dimension from the ‘MyCubes’ database was being processed.
I searched online and saw that there is a KB that describes this same type of error message: http://support.microsoft.com/kb/970184/
After downloading and installing Cumulative update package 5 for SQL Server 2008 Service Pack 1 I once again tried to Process and came up with the exact same error message.
Unfortunately, I’ve now installed CU5 for SP1 on SQL 2008 and I was still getting that same error message. Here’s what’s going on. According to the SQL CSS support person that I spoke to, this message is actually telling me that I’ve run out of space in the temp file that SSAS has created for my cube while I was trying to process it. My problems are (well, problesms as it relates to this cube migration, not all my problems in general 🙂 ) is that A) it’s a really ugly way of telling me that and B) my understanding is this is only supposed to happen to string stores when the Key is a string. This dimension that I am working with has an int for a key, so apparently it’s also possible to happen for the Attribute Name, not just the Attribute Key.
As it turns out, I shouldn’t have run into this limitation in the first place though if settings had been kept like they were in AS2000. What had happened was that when I used the Cube Migration Wizard it had decided to process my dimensions “ByTable”. To correct this what I needed to do was go into every dimension that gets processed in the cube and set it to process “ByAttribute”.
After that I obviously had to build, deploy and process my cubes but with that change to each and every dimension in my cube I was finally able to start processing all the way through again. There are a few other issues that that I’ve run in to but I will keep this post short and cover those in other blogs. One thing that I wi ll note before I go though is that I have noticed a few people on MSDN running into this issue with string KeyColumns but in my case I was using an interger for my KeyColumn so you can get that error with an INT too.
“From a processing perspective, it is a best practice to assign a numeric source field to the KeyColumns property rather than a string property. Not only can this reduce processing time, in some scenarios it can also reduce the size of the dimension. This is especially true for attributes that have a large number of members, i.e., greater than 1 million members.”
You can read more about what this change means in the help files and get more info on increasing performance in the ‘performance guide’ (you’ll want to skip ahead to page 53).