locked
Error on data refresh RRS feed

  • Question

  • I'm running a sharepoint portal on 4 servers, 1 db, 2 app and 1 front end.

    The powerpivot serives is installed and running on one of the app servers. it got a AS local instance. But when I look in that one there are no sandbox cubes. and the following error occures when I try to open a excel file with powerpivot in sharepoint, the same sheet can be opened localy and access the datasources on the sharepoint portal.

    Unable to gather health information from the server: 'Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: The '\MSOLAP$POWERPIVOT:Memory\Memory Limit High KB' performance counter could not be found. System error -1073738824

    at Microsoft.AnalysisServices.AdomdClient.AdomdDataReader.BeginNewRow()

    at Microsoft.AnalysisServices.AdomdClient.AdomdDataReader.InternalRead()

    at Microsoft.AnalysisServices.SharePoint.Integration.EngineServiceInstance.GetHealthInfo(Int64& availableMemory, Int64& availableShrinkableMemory, Single& availableCpuAtLoad, Single& averageCpuUtilization)'

    Anyone got any idea?

    /Daniel

    Monday, January 2, 2012 12:22 PM

Answers

  • Hi cab-dad,

    What's the process now? could you please give a feedback? Based on my research, here I will also give some advice,

    You know PowerPivot is an in memory solution, if you data is too large and wont fit into your current memory you have on your server, I suspect performance will start to plummet. 

    So Utilizing the full capabilities of the PowerPivot for Excel requires that you have the 64-bit edition of Excel 2010. On the Windows platform, a 64-bit operating system and client application provides more addressable memory so that you can build workbooks that contain millions of rows of data.

    For more information about workstation requirements, see Hardware and Software Requirements (PowerPivot for SharePoint).

    Thanks,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Challen Fu Thursday, January 12, 2012 12:19 PM
    Monday, January 9, 2012 3:30 AM

All replies

  • Does Analysis service account have enough permissions on the excel file folders?
    http://uk.linkedin.com/in/ramjaddu
    Monday, January 2, 2012 1:12 PM
  • Hi

    Yes it does.

    The error is generated in AS, running the SQL server profiler I get the same error (The '\MSOLAP$POWERPIVOT:Memory\Memory Limit High KB' performance counter could not be found.  System error -1073738824) on the inital DMX query is executed:

    Select PERF_COUNTER_NAME, PERF_COUNTER_VALUE from systemrestrictschema($system.discover_performance_counters, PERF_COUNTER_NAME='\MSOLAP$POWERPIVOT:Memory\Memory Limit High KB', PERF_COUNTER_NAME='\MSOLAP$POWERPIVOT:Memory\Memory Limit Low KB', PERF_COUNTER_NAME='\MSOLAP$POWERPIVOT:Memory\Memory Usage KB', PERF_COUNTER_NAME='\MSOLAP$POWERPIVOT:Memory\Cleaner Memory shrinkable KB', PERF_COUNTER_NAME='\Processor(_Total)\% Processor Time')

     

     

    Monday, January 2, 2012 1:30 PM
  • Hi cab-dad,

    Have you specified a domain user account for SQL Server Analysis Services. Do not use a built-in system account, such as Local System or Network Service. Besides SQL Server Analysis Services and SQL Server PowerPivot System Service should be started, you have to start the Claims to Windows Token Service; Start Excel Calculation Services; Start Secure Store Service. Please have a check to these.
    Here is the link about Installation PowerPivot for SharePoint http://msdn.microsoft.com/en-us/library/ee210616.aspx#SSS you can have a look to make sure you follow the correct installation steps.
    Thanks,
    Challen Fu

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    • Edited by Challen Fu Wednesday, January 4, 2012 8:44 AM
    Wednesday, January 4, 2012 8:44 AM
  • Hello thank you for the replies. I have followed microsofts installation guid for powerpivot.

    I have an account to run powerpivot service that can access sharepoint. And got all services started that are needed, the thing is that it's working perfect in QA. with the same setup.

    We will add a new server to the farm next week and will move powerpivot onto that machine. So we will remove the powerpoint intallation and get a new fresh one, dont know if this will help, but fingers crossed.

     

    Wednesday, January 4, 2012 10:08 AM
  • Hi cab-dad,

    What's the process now? could you please give a feedback? Based on my research, here I will also give some advice,

    You know PowerPivot is an in memory solution, if you data is too large and wont fit into your current memory you have on your server, I suspect performance will start to plummet. 

    So Utilizing the full capabilities of the PowerPivot for Excel requires that you have the 64-bit edition of Excel 2010. On the Windows platform, a 64-bit operating system and client application provides more addressable memory so that you can build workbooks that contain millions of rows of data.

    For more information about workstation requirements, see Hardware and Software Requirements (PowerPivot for SharePoint).

    Thanks,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Challen Fu Thursday, January 12, 2012 12:19 PM
    Monday, January 9, 2012 3:30 AM
  • I know this is old..

    I found out that the Windows patching that occured every 3rd week of the month was wiping the perf counters. I ended up backing up the perf counters, perfc and perfh files then restoring the C:\Windows\System32\PerfStringBackup.INI and importing the msmdctr.ini and perf-MSOLAP$POWERPIVOTmsmdctr.ini as seen in the script below. After a service restart the power pivot cubes are again working.

    As with anything, backup your work

    lodctr /S:"L:\Backup\PerCounters\%DATE:/=_%"\perfcounters.ini"
    copy "C:\Windows\System32\perfc009.dat" "L:\Backup\PerCounters\%DATE:/=_%"\perfc009.dat"
    copy "C:\Windows\System32\perfh009.dat" "L:\Backup\PerCounters\%DATE:/=_%"\perfh009.dat"
    copy "C:\Windows\System32\PerfStringBackup.INI" "L:\Backup\PerCounters\%DATE:/=_%"\PerfStringBackup.INI"

    lodctr /R

    lodctr "L:\Backup\PerCounters\%DATE:/=_%"\PerfStringBackup.INI"
    lodctr "L:\Program Files\Microsoft SQL Server\MSAS10_50.POWERPIVOT\OLAP\bin\Counters\msmdctr.ini"
    lodctr "L:\Program Files\Microsoft SQL Server\MSAS10_50.POWERPIVOT\OLAP\bin\Counters\perf-MSOLAP$POWERPIVOTmsmdctr.ini"

    NET STOP "MSOLAP$POWERPIVOT"
    NET STOP "SNMP"

    NET START "MSOLAP$POWERPIVOT"
    NET START "SNMP"


    Wednesday, May 23, 2012 9:29 PM
  • Thank you for posting the following:

    lodctr "L:\Program Files\Microsoft SQL Server\MSAS10_50.POWERPIVOT\OLAP\bin\Counters\msmdctr.ini"
    lodctr "L:\Program Files\Microsoft SQL Server\MSAS10_50.POWERPIVOT\OLAP\bin\Counters\perf-MSOLAP$POWERPIVOTmsmdctr.ini"

    NET STOP "MSOLAP$POWERPIVOT"
    NET STOP "SNMP"

    NET START "MSOLAP$POWERPIVOT"
    NET START "SNMP"

    After I had followed the directions on a different blog, I ruined data refresh in my SharePoint environment.  I had done lodctr /R but didn't have the last bit that you posted above.    Because of this, I was receiving the error

    The '\MSOLAP$POWERPIVOT:Memory\Memory Limit High KB' performance counter could not be found. System error -1073738824

    Once I followed your directions (changing from L: to C: in my case) and stopping and starting the two services, I was once again able to get my PowerPivot refresh to schedule.

    This was a 3 tier SharePoint farm using SharePoint 2010 Enterprise and 2008 SQL R2 PowerPivot.

    Thursday, July 11, 2013 10:51 PM