Memory error : the operation cannot be completed because the memory quota estimate (3569MB)exceeds the available system memory (1331MB)

0

Hi,

While  processing  particular dimession of my cube , i am getting error as mentioned below.

memory error the operation cannot be completed because the memory quota estimate (3569MB)exceeds the available system memory (1331MB)

My Server has 4 GB RAM  and has installed 32bit Windows Server 2003 operating system .

About my dimension processing , I am processing one dimension at a time through SSIS Package dynamically.

 There will be around 20 millions of record.  There are  10 columns for each record.

Please anybody help me to resolve this issue .

Thanks in advance !

You have two options:

1.  Upgrade your server to 64bit with more RAM

2.  Reduce the size of your dimension

If #1 is not an option, you could divide up your dimension.  For example, if the dimension is customer you could divide it into three dimensions like Customers A-J, Customers K-T, Customers U-Z.  

Each customer dimension would contain only customers whose last names begin w/ the letters A-J for the first, K-T for the second, and U-Z for the third.

First try to get A-J to process to see if that reduces the size enough.

Mind you, this is not an ideal solution.  #1 is what you should do in my opinion.

Also, you could reduce the number of attributes in the dimension.

Good Luck!

Hi prashantm.

Have you already checked Memory\TotalMemoryLimit and other values, of your AS Server?

Hi MMilligan,

Lets suppose I have dimension which has two attribute .

First one is IdentityNumber(numeric) and Second one is varchar(30) field whcih has 30 numeric character.

In above scenario , How can i divide my dimension into multiple dimensiones and how can i identify like which dimension should be process ?

I am doing ProcessAdd operation on the dimension

Thanks in advance !

Hi Marcoadf,

Can you please provide me list of propertie's value which i need to check  ?  and also  what value should be assign to them so that i will check it form my end  and will let you know if there is any differences .

Thanks in advance !

Is IdentityNumber(numeric) your key? If so, it should be an int or bigint if absolutely necessary.  The method I was describing would be accomplished by:

First find an acceptable size for the dimension “partitions.”

1. Split your dimension in half.  Literally Delete all records over a certain key.  Delete all the facts that have dimension keys not in the dimension after that.  (This is a test and assumes you have a dev or QA environment similar to production.)
2. See if it processes if not, repeat. 
3. Once you discover an acceptable size create separate tables for each dimension partition
4. Alter your fact table to include the additional keys

Again, I would take this as a last resort. 

I have a lot of other question about your setup.  Is your dimension built upon a view?  If so, are there joins or data cleansing going on in your view?  Perhaps there are other optimizations that can be done to the cube or the server settings to accomplish the task. 

Another option would be to decrease the granularity of the dimension.  For example, rather than a customer dimension consisting of one member per customer you could group your customers by region or demographics.  If the user needs more detail you can create an action item that calls a relational report against the data mart.

I Googled “SSAS ProcessAdd” and I came across this page:

http://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?List=83c71d76%2Da4d8%2D4197%2D9257%2D38d6b857634f&ID=7

It had a reference to this page which has another method you could try:

http://cwebbbi.wordpress.com/2006/06/27/breaking-up-large-dimensions/

You might consider sending an email to support at PragmaticWorks.com and scheduling a virtual mentoring hour to have a consultant take a look at the big picture.

Based on your error message and what you have described, bottom line is that you need a 64bit server with more RAM or you need to make some design decisions.

Good Luck and keep us posted!

 Mike