Generally if you encounter problems with your Project Server Business Intelligence on the Project Server 2010 server check out also Brian's Blog: http://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"
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. http://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.