HELP! I cannot process a large dimension.

Question

When I try and process a dimension with roughly 13 million rows I keep getting the following error:

Error Messages 1
                File system error: A FileStore error from WriteFile occurred. Physical file: \\?\G:\DATA\OLAP\Data\WebAnalytics.0.db\Dim Cookie.0.dim\58.Dim Cookie.asstore. Logical file: . . Errors in the OLAP storage engine: An error occurred while the 'Dim Cookie' attribute of the 'Dim Cookie' dimension from the 'WebAnalytics' database was being processed.
Errors and Warnings from Response
    File system error: A FileStore error from WriteFile occurred. Physical file: \\?\G:\DATA\OLAP\Data\WebAnalytics.0.db\Dim Cookie.0.dim\58.Dim Cookie.asstore. Logical file: . .
    Errors in the OLAP storage engine: An error occurred while the 'Dim Cookie' attribute of the 'Dim Cookie' dimension from the 'WebAnalytics' database was being processed.
    Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.

I've looked all over for a solution but keep hitting dead ends:
(http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=255149&SiteID=1 , http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1219836&SiteID=1 )

I keep hearing mention of people switching the ProcessingGroup option for the dimension from ByAttribute to ByTable (and vice versa). But I can't find this setting anywhere. I know it is in the advances properties of the dimension but I still can find it.

The offending dimension consists of a key field and a single attribute (string). It has about 13 million rows right now and will grow still.

I do notice that the .asstore file grows to roughly 4 GB before the error (so it might have something to do with KB912016 ... but we are already at SP2).

Any help would be greatly appreciated.
Jake



OS: Win2k3
SQL Server 2005
AS 2005
64 GB RAM

Microsoft SQL Server Management Studio                        9.00.3042.00
Microsoft Analysis Services Client Tools                        2005.090.3042.00
Microsoft Data Access Components (MDAC)                        2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML                        2.6 3.0 6.0
Microsoft Internet Explorer                        6.0.3790.3959
Microsoft .NET Framework                        2.0.50727.832
Operating System                        5.2.3790

Answer 1

I should note that I've tried unprocessing and reprocessing the dimension and the failure occurs when performing a ProcessFull

Answer 2

Analysis Services has a limitation that the string store file for a single attribute cannot exceed 4GB in size. 

 

You can watch the growth of ?.Dim Cookie.asstore during processing.  If it grows to 4GB then you hit that limit.

Answer 3

Thanks Geoffrey.

So how can I get around this problem? This dimension table is only going to grow larger with time.

Answer 4

Hi Jake,

Perhaps the number of rows in the dimension (13 million) is because the dimension is at the same grain as the fact table? If this is the case, perhaps we can move the attributes to the fact table?

If this is not the case (the dimension's grain is different from the fact table's), perhaps we can break some of the 'rapidly changing dimension attribute(s)' into a 'mini dimension' which is directly connected to the fact table. This will reduce the grain of the fact table. Alternatively perhaps we can move the 'rapidly changing dimension attribute(s) to the fact table'.

If this very large dimension is a 'junk dimension', which contain all possible combinations of several attributes, perhaps we could consider limiting the number of attributes that goes into the 'junk dimension'.

It would help if you could describe this large dimension.

Kind regards,

Vincent

Answer 5

Thanks Vincent. I really appreciate the help.

I have more than one dimension table with 10 mil+ rows.

The cube I am building is tracking / reporting on the web logs for one of our (very active) web servers.

My fact table currently has 436 Million records and is partitioned by month.

The offending dimension table has just shy of 14 million records and it is tracking the cookies from the web logs. I have another table sitting at 17 million records tracking the Refer URLS (with referer domain as an attribute). I have yet another Dimension tracking the URIStem with attributes on Directory, filename and file type. This last one is sitting at 15 million records right now.

As I mentioned previously these tables are only going to grow larger over time so I need some kind of strategy to deal with them.

Any help would be greatly appreciated.
Jake

Answer 6

Sorry I should be more specific regarding the dimensions in question.

The Cookie dimension table has three fields:
ID (int), Cookie (varchar), CheckSum (int).

The only attribute is based on the Cookie string.

The referer dimension has 4 fields and two attributes:
ID (int), RefererURL (varchar), RefererDomain (varchar), CheckSum (int)

the attributes are on the RefererURL and the Referer Domain.

Hopefully this helps.

Answer 7

Hi Jake, I see, so you are experiencing the situation that Sally mention on her post on 31st March 2007 (and Jeffery Wang too), i.e. the string store files (asstore, bsstore or ksstore) of the cookie, referrer and/or URIStem dimensions have reached 4 GB size when you use the ProcessFull command on these dimension you get the "A FileStore error from WriteFile occurred" message. Is this your situation Jake?

If yes, Jeffery Wang mentioned how to use PropertyBufferRecordLimitEnabled to solve the 4GB limit on temporary string store on http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1629338&SiteID=1.

Deepak mentioned about using ROLAP StorageMode for large dimension (instead of MOLAP StorageMode), which could be the only viable solution to your situation, given that the error message occurs when you do ProcessFull, not ProcessUpdate.

Microsoft mentioned a hotfix to this 4 GB limitation on http://support.microsoft.com/kb/912016, but the hot fix would not help if the reason for reaching 4 GB is large amount of data, as in your case.

Subhash Subramanyam mentioned on http://subhashsubramanyam.blogspot.com/2007_09_01_archive.html that changing ByTable to ByAttribute probably help. Probably worth checking. It's on ProcessingGroup property of the dimension.

SSAS Performance Guide mentions a few things about Refreshing Dimension Efficiently, including Removing Bitmap Indexes (AttributeHierarchyOptimizedState property).

Out of the things mentioned above I think ROLAP is the most probable option, if not the only viable option, for your case, Jake.

Kind regards,

Vincent

Answer 8

Thank you Vincent for all the suggestions.

I am not in the office today, but will try them out first thing on Wednesday.

Just one question, could you help me find the setting that changes the dimension ProcessingGroup between ByTable and ByAttribute? I could not for the life of me figure out where this property is set? Is it within BIDS? Analysis Services?

Thanks again.
Jake

Answer 9

It's in BIDS, Jake. It's the property of the dimension. After you double click the dimension on the Solution Explorer to open it, don't click on an attribute name on the left panel (this will show the attribute property). Instead, click on the dimension name on the left panel.

Good luck,

Vincent

 

Answer 10

Hi Vincent.

I'm afraid I'm not having much luck.

Here's what I tried.

The ProcessingGroup was already set to "ByAttribute" I tried changing it to "ByTable" but got the same results.

I located the PropertyBufferRecordLimitEnabled in the msmdsrv.ini file but it was already set to zero.

I didn't try and apply the hotfix because it doesn't look like it addresses my particular problem.

The only outstanding option is designating these dimensions as ROLAP. I was talking to my team lead and he wants to exclude these dimensions for the time being (since the reporting benefits is questionable) rather than try ROLAP.

Hopefully that will be enough to get us by.

Thanks for all your help.

Answer 11

I've been having a similar problem...Check your server error logs for something like:
Error Event ID 333
An I/O operation initiated by the Registry failed unrecoverably. The
Registry could not read in, or write out, or flush, one of the files that
contain the system's image of the Registry.

Another person and myself were getting these messages and the processing errors. Since I've dealt with this server a bit already, I know it has a memory leak somewhere I haven't been able to track down. I just avoid it by restarting SQL service when running large things (Which seems to work). However, now that I know they are related, I am going to work through some of the fixes on this page:
http://eventid.net/display.asp?eventid=333&eventno=5757&source=Application%20Popup&phase=1

Hopefully that will solve the errors I'm getting, which will fix the memory leak, which will fix the AS processing. Maybe you'll find you are also receiving that error and this will help, let me know!