none
PowerPivot data refresh error

    Question

  • I recently setup PowerPivot for Sharepoint 2010, installed all necessary products and setup all services and permissions on the server, including the PowerPivot Unattended refresh account.

    I created a PowerPivot workbook which connects to our SQL Server data warehouse.  I was able to save to the PowerPivot gallery and can open the workbook and work with the slicers.

    However, when I create a data refresh schedule using the  "<label for="ctl00_PlaceHolderMain_ctl06_rbUseUnattendedAccount">Use the data refresh account configured by the administrator" option, i get the following error when it tries to refresh:</label>

    "Target application not found (application id: PowerPivotUnattendedAccount). (unable to send notification email)<label for="ctl00_PlaceHolderMain_ctl06_rbUseUnattendedAccount"></label>"

    I've tried using different accounts for the PowerPivot unattended account, checked permissions on the data source, but nothing seems to work.  I've checked the ULS logs, but don't see anything having to do with refresh or PowerPivot failures.

    Does anyone have any steps I can take to ensure all my processes are correct?  I must be missing something somewhere along the line.

    thanks

    Sunday, January 06, 2013 3:38 PM

Answers

All replies

  • Here is Microsoft's guidance on setting up PowerPivot Data Refresh:

    http://msdn.microsoft.com/en-us/library/ee210679.aspx


    SharePoint - Nauplius Applications
    Microsoft SharePoint Server MVP
    MCITP: SharePoint Administrator 2010

    -----------------------
    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Sunday, January 06, 2013 5:24 PM
    Moderator
  • Trevor,

    thanks for the info.  I've gone through all of the docs and links and setup the accounts and permissions according to the documentation, but still receive the error when refreshing:

    *************

    The Microsoft Secure Store Service application Secure Store Service Application failed to retrieve credentials. The error returned was 'Target application not found (application id: PowerPivotUnattendedAccount).'.

    **************

    What does it mean when it says that the Target Application was not found?  Can it not see the PowerPivotUnattendedAccount application that i created in Secure Store?

    Also, if I try to use the "Connect using the following Windows User Credentials" I receive the following error:

    Access is denied.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.ServiceModel.FaultException`1[[System.ServiceModel.ExceptionDetail, System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]: Access is denied.

    Source Error:
    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

    Stack Trace:
    [FaultException`1: Access is denied.]
       System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) +10260970
       System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) +539
       Microsoft.AnalysisServices.SharePoint.Integration.IGeminiServiceApplication.CreateTargetApplication(String appID) +0
       Microsoft.AnalysisServices.SharePoint.Integration.GeminiServiceApplicationProxy.CreateTargetApplications(IGeminiServiceApplicationClient channel, DataRefreshSchedule drs) +171
       Microsoft.AnalysisServices.SharePoint.Integration.GeminiServiceApplicationProxy.PersistDataRefreshSchedule(DataRefreshSchedule drs) +89
       Microsoft.AnalysisServices.SharePoint.Integration.ManageRefresh.OKButton_Click(Object sender, EventArgs e) +2244
       System.Web.UI.WebControls.Button.OnClick(EventArgs e) +115
       System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +140
       System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +29
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2981
    
    Wednesday, January 09, 2013 9:35 PM
  • Does your Application Pool account running the Web App have "Manage Target Application" right on the Secure Store Service application (highlight your SSS and click "Administrators" in the ribbon)?

    SharePoint - Nauplius Applications
    Microsoft SharePoint Server MVP
    MCITP: SharePoint Administrator 2010

    -----------------------
    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Wednesday, January 09, 2013 9:42 PM
    Moderator
  • wow,

    I can't beleive it was that easy...

    I've been working on this issue for over a week now.  I don't remember seeing that step in the documentation anywhere. 

    thanks for your help!

    Thursday, January 10, 2013 6:37 PM
  • Trevor,

    as a quick side question, i'd like to install Powerpivot on our Sharepoint Prod site, but the database and application are on separate servers (in DEV they are on the same Server).

    When installing PowerPivot, do i install it on the Application or Database Server?

    Also, does the database server have to be a certain edition?  Right now our Dev SQL server is Enterprise 2008 R2, but our PROD SQL server is 2000 Standard (not sure why we haven't upgraded PROD yet, but it is what it is...).

    thanks for your help!

    Thursday, January 10, 2013 7:00 PM
  • PowerPivot always needs to be installed on a server that has SharePoint installed on it as it is a SharePoint-specific SQL feature.

    According to http://msdn.microsoft.com/en-us/library/ee210640(v=SQL.110).aspx#software, PowerPivot can leverage a Database Engine supported by SharePoint 2010.


    SharePoint - Nauplius Applications
    Microsoft SharePoint Server MVP
    MCITP: SharePoint Administrator 2010

    -----------------------
    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Thursday, January 10, 2013 7:05 PM
    Moderator
  • Thanks Trevor,

    as far as the install media, should i use the SQL 2008 R2 install media or the SQL 2008 install media?

    i found this:

    Step 2: Install SQL Server PowerPivot for SharePoint

    Run SQL Server 2008 R2 Setup to add PowerPivot for SharePoint to a SharePoint farm. If the farm consists of multiple SharePoint servers, you must run SQL Server 2008 R2 Setup on an application server that is already joined to the farm.

    which states using SQL 2008 R2 media.  I was just checking since i do not have R2 version of the database in PROD.

    Thursday, January 10, 2013 7:24 PM
  • PowerPivot is only included on SQL 2008 R2 and SQL 2012 Enterprise editions (and BI editions).

    SharePoint - Nauplius Applications
    Microsoft SharePoint Server MVP
    MCITP: SharePoint Administrator 2010

    -----------------------
    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Thursday, January 10, 2013 7:26 PM
    Moderator