Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Browsing SSAS cube from excel throwing error

Answered Browsing SSAS cube from excel throwing error

  • Saturday, February 02, 2013 6:35 AM
     
     
    Sign In to Vote
    0

    Hi,

    We have SSAS cube created on 2008 R2 version. We are using 32-bit PCs and trying to browse cube from excel. When adding columns from one of the huge dimension table we are facing "Memory Error" saying that "Data could not be retrieved from external source". upgrade to 64-bit or increase memory of the system.

    upgrading to 64-bit is not possible right now. 

    Is anyone has any idea to resolve this issue. It is very critical as user testing is going on and we can't say any exceptions.

    Quick response will be really appreciable.

    Thanks & Regards,

    Sindhu

     

All Replies

  • Saturday, February 02, 2013 6:59 AM
     
     
    Sign In to Vote
    0
    first select a measure that has a direct relation with the dimension you want to browse, then select an attribute from that huge dimension.
    by the way, what is the number of rows in that huge dimension ?
     
  • Saturday, February 02, 2013 10:46 AM
     
     
    Sign In to Vote
    0

    Hi,

    Thanks for the reply.

    But it is not working.. :( we have almost 1 million records in dimension and placing all those records on excel causing memory issue.

    Regards,

    Sindhu

     
  • Saturday, February 02, 2013 11:33 AM
     
     
    Sign In to Vote
    0

    Excel 2010 can contain around 1004900 rows.

    I think you are trying to view more than one attribute from that huge dimension, do you ? and those attributes are from different attribute hierarchies, ain't ?  if yes, a cross-joins are performed between sets containing members of the same dimension, relationships between the attribute hierarchies, whether direct or indirect, SSAS automatically limits the resulting combinations to those actually observed within the dimension (This is referred to as the auto-exists feature of Analysis Services), add to that the aggregations done on those combinations, and imagine this on a million rows dimension ... this is what i think causes the memory issue that you have, yet not sure.
    open the sql server profiler and try to get the query the excel tried to execute. run that MDX query on SSAS, if it got you the result then your problem is in the excel. if you got the same error, then i advise to create some hierarchies on that dimension.

     
  • Friday, February 08, 2013 5:15 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hi,

    Thanks for the reply.

    But it is not working.. :( we have almost 1 million records in dimension and placing all those records on excel causing memory issue.

    Regards,

    Sindhu

    Hi Sindhu,

    I recommand you upgrade the Excel to 64 bit version to solve this issue. Please refer to the the similar thread below:
    Memory Error in Excel when going to more than 30,000 records (SSAS 2008R2 with Excel 2010): http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/fcb7950f-a7e9-45f9-8ae5-1b154854e2d3

    Regards,
    Bin Long

    Elvis Long
    TechNet Community Support