Business Intelligence Error Message "This workbook cannot be opened because it is not stored in an excel services application trusted location"

Generally if you encounter problems with your Project Server Business Intelligence on the Project Server 2010 server check out also Brian's Blog: https://blogs.msdn.com/b/brismith/archive/2010/09/16/project-server-2010-business-intelligence-center-common-errors.aspx that could triage most of the common errors.

I've encounterd also another issue when having 2 PWA's and when configuring the second BI it didn't worked displaying the following error message:

"This workbook cannot be opened because it is not stored in an excel services application trusted location"

"To create an excel services application trusted location, contact your system administrator"

ULS LOGS:

ExcelServiceBase.BeginProcessOperation: Caught an 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.ExcelServiceBase.BeginProcessOperation(CommandParameter parameter, WebMethodBehaviorAttribute webMethodBehavior, WebMethodType webMethodType, AsyncCallback callback, Object state, UserOperation operation)
Failed to load 'https://test2/PWA/ProjectBICenter/PWA_%20Cube%20Reports/Cube%20Test.xlsx' with error: 'The workbook is not in a trusted location.'.

Restarting different services/server won't help and everything was correctly configured.

To fix this you just need to to run the clear sharepoint cache procedure. https://blogs.msdn.com/b/jamesway/archive/2011/05/23/sharepoint-2010-clearing-the-configuration-cache.aspx For some reason Project is looking into the cache and not taking the latest configuration into consideration, basically it's reading old settings from the sharepoint cache.