Cascade Skyline - with Microsoft Logo and Project Support header - author Brian Smith

Project Server 2010: Business Intelligence Center–Common Errors

Project Server 2010: Business Intelligence Center–Common Errors

  • Comments 22

This follows on from the posting on the structure of the Business Intelligence Center in Project Server 2010 - http://blogs.msdn.com/b/brismith/archive/2010/08/24/project-server-2010-business-intelligence-center-behind-the-scenes.aspx and covers a few of the possible errors you might run in to.

For Project Server 2007 one of my most read, or at least “most viewed” and commented pages was Common Error Messages when Building Cubes with Project Server 2007.  I think with the architecture of 2010 we resolve many of those cube building difficulties, but the configuration or Excel Services Application and Secure Store Service will fill the gap that leaves.  I am preparing some internal training on this topic so thought I’d re-purpose many of my screenshots in this blog posting.  I start with no configuration or Excel Services or the Secure Store Service at all, as if I had never read or even heard of the excellent article at http://technet.microsoft.com/en-us/library/ee662106.aspx that describes how things should be configured and then hit one error after another until finally I have it all working.  I will attempt to run the SimpleProjectList sample report from the default English (United States) library.  I will number the errors, and then at the end of the posting I will list the ULS log entries you can expect to see for each error, along with the text from these error dialogs, and hopefully this will allow you to work backwards from the logs to the cause.  I want to include the logs so the search engines have something cool to read without breaking the flow of the story too much – and the text from the dialogs as the search engines may struggle with the pictures.

image

Error 1

image

The first error I see tells me that it will not open that report as the Data Connection file is not in a trusted location.  Well that’s an easy one to fix (referring to the TechNet document referenced above) so I add the right URL as a trusted location and move on…

Error 2

image

Well at least this ones just a warning – and I can continue just by clicking “Yes”, but we should really fix that.  This is an interesting one as you might have wondered (if you know already what needs configuring) why we didn’t get an error about our file itself;f not being in a trusted location.  The reason is that by default there is a trusted location of http://.  And this is configured to trust children, and also to allow External Data.  In fact the only thing it is missing from our recommended configuration is that Refresh warning enabled is still checked – hence the warning we are getting at Error 2.  Best practice would probably be to delete the http:// location and then just explicitly set the locations as per the TechNet document.  So moving on…

Error 3

image

Now we have Excel Services Application configured just right – so we start hitting the Secure Store Errors.  In a bid to hit most of the possible errors I created a Secure Store Service Target Application but didn’t set any credentials…

Error 4

image

Hmm.  Same error, but the ULS logs distinguish between them (– see the section at the end of the blog for the differences.  So now I set some credentials, but put in a bad password…

Error 5

image

Different error, and this makes sense.  We have some credentials, but they don’t work.  I’ll put that password right, but for the group that can make use of this target application I will use a group that I do not belong to…

Error 6

image

Back to the generic error again, but ULS comes good and gives us a better idea what might be wrong. “Claims stored in the credentials did not match with the group claim for a group app.”  Put in the right group, of which I am a member and hey presto!  All that just for one project!

image

I’m sure there may be other combinations of issues that will give different errors, but I have been very impressed with the wording in the ULS logs (see below) which does make it quite easy to understand what might have gone wrong.

Here are the details of the errors again, more for the search engines, but feel free to read on.  I haven’t listed all the lines associated with the failure – just the main ones that will help identify the failure mode.  As mentioned above I have also included the text from each of the dialogs above

Error 1

Dialog - The Data Connection File <url to odc file> used in the workbook is not in a trusted location.  
The following connections failed to refresh:

Project Server - Simple Projects List
ULS - 
  • w3wp.exe (0x230C) 0x18DC Excel Services Application External Data 5647 Information The ODC file http://brismith8100. /PWA/ProjectBICenter/Data%20Connections%20for%20PerformancePoint/English%20(United%20States)/Project%20Server%20-%20Simple%20Projects%20List.odc is not in a DCL that is trusted by Excel Services Application. To fix this problem, add the DCL to the trusted DCL list in the administrator console.
  • w3wp.exe (0x230C) 0x18DC Excel Services Application Excel Calculation Services erw5 Medium ConnectionInfoManager.GetConnectionInfo: InitConnInfoFromOdcFile threw an exception: Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInfoException: Exception of type 'Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInfoException' was thrown. at Microsoft.Office.Excel.Server.CalculationServer.ConnectionInfoManager.InitConnInfoFromOdcFile(ExtendedConnectionInfo extendedConnInfo) at Microsoft.Office.Excel.Server.CalculationServer.ConnectionInfoManager.GetConnectionInfo(Request request, String externalSourceName, Int32 externalSourceIndex, Boolean& shouldReportFailure)
Error 2
Dialog - This workbook contains one or more queries that refresh external data. A malicious user can design a query to access
confidential information and distribute it to other users or perform other harmful actions.
If you trust the source of this workbook, click Yes to enable queries to external data in this workbook. If you are not sure, click No so that changes are not applied to your workbook.
Do you want to enable queries to external data in this workbook?
ULS – Not applicable – just a warning
Error 3
Dialog -  An error occurred while accessing application id ProjectServerApplication
from Secure Store Service. The following connection failed to refresh:
Project Server - Simple Projects List
ULS -
  • w3wp.exe (0x230C) 0x2344 Secure Store Service Secure Store elm4 High SQL command failed: Sproc name: dbo.proc_sss_GetCredentials, Application Id: ProjectServerApplication, Error code: 80630490, Error message: Target application not found (application id: ProjectServerApplication).
  • w3wp.exe (0x230C) 0x2344 Secure Store Service Secure Store 7493 Critical The Microsoft Secure Store Service application Secure Store Service failed to retrieve credentials. The error returned was 'Target application not found (application id: ProjectServerApplication).'. For more information, see the Microsoft SharePoint Products and Technologies Software Development Kit (SDK).
  • w3wp.exe (0x230C) 0x2344 Secure Store Service Secure Store efp4 Monitorable GetCredentials failed with the following exception: System.ServiceModel.FaultException`1[Microsoft.Office.SecureStoreService.Server.SecureStoreServiceTargetApplicationNotFoundFault]: Target application not found (application id: ProjectServerApplication). (Fault Detail is equal to Microsoft.Office.SecureStoreService.Server.SecureStoreServiceTargetApplicationNotFoundFault).
  • w3wp.exe (0x230C) 0x21C0 Excel Services Application Web Front End 4969 Warning SSS has failed with the following exception: Target application not found (application id: ProjectServerApplication). [Session stuff here...]
Error 4
Dialog - Same as Error 3.
ULS -
  • w3wp.exe (0x1BF8) 0x09A8 Secure Store Service Secure Store elm4 High SQL command failed: Sproc name: dbo.proc_sss_GetCredentials, Application Id: ProjectServerApplication, Error code: 80630001, Error message: Credentials were not found for the current user within the target application 'ProjectServerApplication'. Please set the credentials for the current user.
  • w3wp.exe (0x1BF8) 0x09A8 Secure Store Service Secure Store 7493 Critical The Microsoft Secure Store Service application Secure Store Service failed to retrieve credentials. The error returned was 'Credentials were not found for the current user within the target application 'ProjectServerApplication'. Please set the credentials for the current user.'.
  • w3wp.exe (0x1BF8) 0x09A8 Secure Store Service Secure Store efp4 Monitorable GetCredentials failed with the following exception: System.ServiceModel.FaultException`1[Microsoft.Office.SecureStoreService.Server.SecureStoreServiceCredentialsNotFoundFault]: Credentials were not found for the current user within the target application 'ProjectServerApplication'. Please set the credentials for the current user. (Fault Detail is equal to Microsoft.Office.SecureStoreService.Server.SecureStoreServiceCredentialsNotFoundFault).
  • w3wp.exe (0x1BF8) 0x12A8 Excel Services Application Web Front End 4969 Warning SSS has failed with the following exception: Credentials were not found for the current user within the target application 'ProjectServerApplication'. Please set the credentials for the current user. [Session info here…]
  • w3wp.exe (0x1BF8) 0x12A8 Excel Services Application External Data 3238 Information Unable to establish a connection using credentials retrieved from SSS. This could be because the Unattended Service Account is not configured or because the credentials retrieved from SSS are not valid. [Session info here…]
Error 5
Dialog - Access was denied by the external data
source. The following connections failed to refresh:
Project Server - Simple Projects List
ULS -
  • w3wp.exe (0x1BF8) 0x242C Secure Store Service Secure Store efr5 High ValidateCredentialClaims - Access Denied: Claims stored in the credentials did not match with the group claim for a group app.
  • w3wp.exe (0x1BF8) 0x242C Secure Store Service Secure Store 7493 Critical The Microsoft Secure Store Service application Secure Store Service failed to retrieve credentials. The error returned was 'Access is denied.'. For more information, see the Microsoft SharePoint Products and Technologies Software Development Kit (SDK).
  • w3wp.exe (0x1BF8) 0x242C Secure Store Service Secure Store efp4 Monitorable GetCredentials failed with the following exception: System.ServiceModel.FaultException`1[Microsoft.Office.SecureStoreService.Server.SecureStoreServiceFault]: Access is denied. (Fault Detail is equal to Microsoft.Office.SecureStoreService.Server.SecureStoreServiceFault).
  • w3wp.exe (0x1BF8) 0x12A8 Excel Services Application Web Front End 4969 Warning SSS has failed with the following exception: Access is denied. [Session stuff here]
  • w3wp.exe (0x1BF8) 0x12A8 Excel Services Application External Data 3238 Information Unable to establish a connection using credentials retrieved from SSS. This could be because the Unattended Service Account is not configured or because the credentials retrieved from SSS are not valid. [Session stuff here...]

Error 6
Dialog – Same as Error 3.
ULS -
  • w3wp.exe (0x230C) 0x2344 Excel Services Application Excel Calculation Services 7mor Medium TrustedLocationCacheNode.Find: Found a uri that does not belong to any defined trusted location: https://brismith8100.redmond.corp.microsoft.com/PWA/ProjectBICenter/Sample%20Reports/English%20(United%20States)/SimpleProjectsList.xlsx
  • w3wp.exe (0x230C) 0x2344 SharePoint Server Logging Correlation Data xmnv Medium Result=Id=OpenWorkbookLocationNotTrusted; Microsoft.Office.Excel.Server.CalculationServer.FileOpenException: This workbook cannot be opened because it is not stored in an Excel Services Application trusted location. at Microsoft.Office.Excel.Server.CalculationServer.Session.OpenWorkbookAsync(AsyncHandler`1 callback, Object userState, Request request, Uri url, Boolean loadedOnDemand)…
  • w3wp.exe (0x230C) 0x2344 Excel Services Application Excel Calculation Services ech0 Medium ExcelService.PostProcessRequest: web method: OpenWorkbook, got exception Id=OpenWorkbookLocationNotTrusted; Microsoft.Office.Excel.Server.CalculationServer.FileOpenException: This workbook cannot be opened because it is not stored in an Excel Services Application trusted location. at Microsoft.Office.Excel.Server.CalculationServer.Session.OpenWorkbookAsync(AsyncHandler`1 callback, Object userState, Request request, Uri url, Boolean loadedOnDemand)…
  • w3wp.exe (0x230C) 0x2344 Excel Services Application Excel Calculation Services 2017 Information Failed to load 'https://brismith8100.redmond.corp.microsoft.com/PWA/ProjectBICenter/Sample%20Reports/English%20(United%20States)/SimpleProjectsList.xlsx' with error: 'The workbook is not in a trusted location.'. [Session stuff here...]
Leave a Comment
  • Please add 4 and 8 and type the answer here:
  • Post
  • Very nice article and thanks for useful information

  • Hi Brian,

    I've had "so much fun" with the errors above, I think the BI centre has become the new OLAP cube.  Thanks for sharing, I'm sure that this will become a useful resource.

  • Certainly one of my 'most viewed' blogs of recent months Ben.  I think cube building in 2010 is relatively straightforward - we had to come up with something else to fill your time :).  See you in Barcelona!

    Best regards,

    Brian.

  • Thanks for the info...still i am a bit stuck @ error 3: "An error  occurred while accessing application  id ProjectServerApplication from Secure Store Service. The following connections  failed to refresh:Project Server - Resource Capacity"

    Have checked every thing but it's not working..i tried by opening the report in Excel n it's working there with all the resource capacity stuff. so how can i resole this browser issue??

  • Brian Smith,

    Could you please list out the available standard out -of- the- box reports in EPM.

    Regards,

    Panicker

  • stuck at error 6 what do you mean with: Put in the right group, of which I am a member ??? where do i need to put that group??

  • Hi there,

    Excellent post. Is helping me a great deal! However I am stuck at point error #3. I am not sure what do I have to do at Secure Store... All I did before was to generate a key and then I was able to use the Dashboard designer, but that is all... I am supposed to create a new Target Application? What are the parameters for it?

  • Hi PJ, pmdci,

    This document is meant to be used along with the TechNet article at technet.microsoft.com/.../ee662106.aspx which gives the details of all the groups and settings needed.

    Best regards.

    Brian

  • Hi,

    Just a simple question

    It seems like I have to be running Active Directory to view/author a report....is this true?

    I get that impression from the accompanying TechNet article: "To provide your users with the needed access to the Business Intelligence Center in PWA and the reports within, you must populate the Report Authors and Report Viewers Active Directory groups as follows".

    I was hoping to get away with using a simple little workgroup on a standalone machine for student purposes.

    Many thanks.

  • Thanks ! Went through a dozen plus articles, but yours finally solved the problem

  • Hi Brian,

    I am still bit stuck with BI and reporting facility in Project Server 2010. Despite following steps in technet article to configure the SQL, Excel services and SSS I have same error message as Error 3/4 but there are no error entries in ULS. Sample reports is errorying and then display excel spreasheet with no data in it..

    I have tried multiple time to re-create the services and config without any luck.. Any hints on how to fix it? I can provide the ULS log if needed.

    Kind regards,

    Tomasz

  • Hey Brian,

    When the farm is setup to use an alias (that has been entered into alternate access mappings), what URL's do you use for trusted file locations and data connection library configuration pieces?  I didn't see that outlined in the article and haven't found anything online that helps me understand what to do in this scenario.

    Thanks!

  • Hi Dana - you would use the same alias as used in AAM - as that will be the URL the client will be seeing when they need to validate those paths. And if you change anything in AAM you will need to refresh the BI Center as per my other post referenced at the top of this posting.

    Best regards,

    Brian.

  • Never mind, I rebooted all servers one more time and it came back working... before I had rebooted different server at different points and recreated SSS and set credential multiple times. Also before I had SSS and project server app running on different app servers however at the end switched them to run on the same app server, not sure it helped or it is just waiting for one more reboot... This is very funcky... you can get everything right and it still not work but somehow you recreate the same thing or reboot then it start working.

  • Am eperiencing an eror every time Ipon pastel. It keep telling me an error occured while trying to load the Business Intelligent Centre nodes!

    Bad file Name or Number.

    How do i sort this out.

Page 1 of 2 (22 items) 12