TFS2010: Troubleshooting SharePoint Dashboards data source problems

In the TFS 2010 release, if you have the Enterprise features of Microsoft Office SharePoint Server (MOSS) configured (i.e. Single Sign-On and Excel Services), then you can make TFS Excel Reports available to your users via a web site.  This is very compelling since Excel makes PivotTables and PivotCharts easy to use and for users viewing the rendered charts via the web it is painless user experience.

For more information see the Excel Reports topic on MSDN. Here is an example of a working SharePoint Dashboard

However, there are a lot of moving parts required to get all this working. TFS 2010, SQL Analysis Services, MOSS (Single Sign-On Service, Excel Calculation Services).  The purpose of this article is not to describe how to get it working, but where to look when it stops working.  If you are starting from scratch, then you should refer to the “Checklist: Multiple-Server Installation” topic in the TFSInstall.chm guide for TFS 2010 and the Configure Settings for Dashboard Compatibility topic on MSDN. I’ve been through those steps more times than I can remember and my feedback went into it to help you be successful where I stumbled.

Error: Unable to retrieve external data

In my experience, this is the most common error:

Unable to retrieve external data for the following connections:
TfsOlapReport
The data sources may be unreachable, may not be responding, or may have denied you access.

It can mean a few different things:

1. Excel Services cannot connect to Analysis Services or SQL because of a connectivity issue  (or it’s trying to connect to the wrong server)
2. The credentials returned from the SSO service are incorrect or they don’t have the right permission to connect to Analysis Services / SQL
3. The user browsing the website is not not a member of the group that the SSO credentials are allowed to be used by
4. The SSO service can’t retrieve the credentials from it’s store

The first thing to do is to check the connection.

• Open one of the Excel reports from the Excel Reports document library in Excel (the desktop application)
• Click on the Data tab in the ribbon
• Click on the Connections button to display the Workbook Connections dialog box
• Click on the TfsOlapReport data connection
• Click on the Properties... button
• Click on the Definition tab of this dialog box

This should show a connection string like the following:

Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;User ID="";Initial Catalog=Tfs_Analysis;Data Source=ASSERVER;Location=ASSERVER;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

If the connection string is incorrect, then you need to open the Team Foundation Administration Tool on your Application Tier and configure the reporting settings correctly.

The next thing to check is the Single Sign-On configuration.

• Click on the Authentication Settings... button

The SSS radio button should be selected and an SSS ID specified

If an SSS ID (sometimes incorrectly referred to as an SSO ID) is not specified, then you need to open the Team Foundation Administration Tool on your MOSS server and configure an SSS ID.

Troubleshooting data source problems

The best place to start looking is the combined SharePoint and Excel Calculation Services trace logfiles.

1. Logon to the server where MOSS is installed
2. Open a command prompt as administrator
3. Type: explorer C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\LOGS
4. Open the workbook in a web browser – you should receive the error “Unable to retrieve external data” error message
5. At the command prompt type: net stop sptrace && net start sptrace
6. Select ‘Update > Refresh all data connections’ in the excel workbook (below)
7. Type: net stop sptrace && net start sptrace

Then if you sort the files in the LOGS directory by Newest to Oldest, the second most recent one should have the Excel Services & SSO tracing in it.  That usually has a stacktrace or error message indicating where the failure is. You can also use ULSViewer to look at the log files, however I find Notepad is adequate.

Here’s an example of a successful workbook refresh.  

Excel Services Web Front End      6k38   Medium        ExcelServerProxy.ExecuteWebMethod: sending request of type OpenWorkbook, to server=http://mossserver:56737/SharedServices1/ExcelCalculationServer/ExcelService.asmx, session=(null), state id=0   

Excel Calculation Services        766u   Medium        ExcelService.LogRequest: starting request of type OpenWorkbook 

Excel Services Session            5pbu   Medium        SessionManager.BeginRequest: There are 1 active users 

Excel Services Session            1sxs   Medium        SessionManager.BeginRequest: Created Session id=23.485TDKRh5qu7zU3Ghay5tQ590.5.en-US5.en-US73.+0480#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060, caller=fe80::982:6438:527c:31f1%11   

SSO                               0      Medium        RPC request to ncalrpc_AuthenticateClientBinding()      

SSO                               0      Medium       RPC request to s_GetCredentials  

SSO                               0      Medium        RPC request to GetCredentials()  

SSO                               0      Medium        Cached entry is stale, so will not return credentials  

SSO                               0      Medium        CSsoResourceManager succeeded in getting the connection string   

SSO                               0      Medium        Provider=SQLOLEDB;Extended Properties='Server=SQLSERVER;Database=SSO;Trusted_Connection=yes;App=Windows SharePoint Services;Timeout=15';     

SSO                               0      Medium        User is a member of the group.

SSO                               0      Medium        Destroying CCommandEx     

SSO                               0      Medium        Destroying CSessionEx     

SSO                               0      Medium        CSsoResourceManager succeeded in getting the connection string   

SSO                               0      Medium        Provider=SQLOLEDB;Extended Properties='Server=SQLSERVER;Database=SSO;Trusted_Connection=yes;App=Windows SharePoint Services;Timeout=15';     

SSO                               0      Medium        RPC request to ncalrpc_AuthenticateClientBinding()      

SSO                               0      Medium        RPC request to s_GetApplication  

SSO                               0      Medium        RPC request to GetApplication()  

SSO                               0      Medium        CSsoResourceManager succeeded in getting the connection string   

SSO                               0      Medium        Provider=SQLOLEDB;Extended Properties='Server=SQLSERVER;Database=SSO;Trusted_Connection=yes;App=Windows SharePoint Services;Timeout=15';     

SSO                               0      Medium        Destroying CCommandEx     

SSO                               0      Medium        Destroying CSessionEx     

SSO                               0      Medium        CSsoResourceManager succeeded in getting the connection string   

SSO                               0      Medium        Provider=SQLOLEDB;Extended Properties='Server=SQLSERVER;Database=SSO;Trusted_Connection=yes;App=Windows SharePoint Services;Timeout=15';     

SSO                               0      Medium        RPC request to s_GetApplicationFields   

SSO                               0      Medium        RPC request to GetApplicationsFields()  

SSO                               0      Medium        CSsoResourceManager succeeded in getting the connection string   

SSO                               0      Medium        Provider=SQLOLEDB;Extended Properties='Server=SQLSERVER;Database=SSO;Trusted_Connection=yes;App=Windows SharePoint Services;Timeout=15';     

SSO                               0      Medium        CSsoResourceManager succeeded in getting the connection string   

SSO                               0      Medium        Provider=SQLOLEDB;Extended Properties='Server=SQLSERVER;Database=SSO;Trusted_Connection=yes;App=Windows SharePoint Services;Timeout=15';     

SSO                               0      Medium        Destroying CCommandEx     

SSO                               0      Medium        Destroying CSessionEx     

Excel Services External Data      13l9   Medium        ExternalSource.GetExternalKeyAndStateAndMarkUsed: Key exists in cache - waiting for it to be created, sessionId=23.485TDKRh5qu7zU3Ghay5tQ590.5.en-US5.en-US73.+0480#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060, externalsource=TfsOlapReport, key=[ts: 12/03/2009 13:04:14.192943, [serviceaccount, en-US, en-US, +0480#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060, [Oledb, Stored, TFS, False, ], ()] BaseWB: "http://mossserver/sites/COLLECTION/Project/Reports/Bug Progress.xlsx" [0x409] [11/06/2009 19:14:04.000000] [BaseWB ID: 2]]  00000412-f5f3-0da1-0000-000050f7b00b

Excel Services External Data      13la   Medium        ExternalSource.GetExternalKeyAndStateAndMarkUsed: Key exists in cache - wait was successful, sessionId=23.485TDKRh5qu7zU3Ghay5tQ590.5.en-US5.en-US73.+0480#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060, externalsource=TfsOlapReport, key=[ts: 12/03/2009 13:04:14.192943, [serviceaccount, en-US, en-US, +0480#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060, [Oledb, Stored, TFS, False, ], ()] BaseWB: "http://mossserver/sites/COLLECTION/Project/Reports/Bug Progress.xlsx" [0x409] [11/06/2009 19:14:04.000000] [BaseWB ID: 2]]  00000412-f5f3-0da1-0000-000050f7b00b

Excel Calculation Services        2m5e   Information   Session opened with workbook http://mossserver/sites/COLLECTION/PROJECT/Reports/Bug Progress.xlsx. [Session: 23.485TDKRh5qu7zU3Ghay5tQ590.5.en-US5.en-US73.+0480#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060 User: REDMOND\granth]    00000412-f5f3-0da1-0000-000050f7b00b

Excel Calculation Services        5cag   Medium        ExcelService.PostProcessRequest: finished request of type OpenWorkbook    00000412-f5f3-0da1-0000-000050f7b00b

Excel Services Web Front End      6k39   Medium        ExcelServerProxy.ExecuteWebMethod: request of type OpenWorkbook succeeded, from server=http://mossserver:56737/SharedServices1/ExcelCalculationServer/ExcelService.asmx, session=23.485TDKRh5qu7zU3Ghay5tQ590.5.en-US5.en-US73.+0480#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060, new state id=1  

Excel Services Web Front End      6k30   Medium        ExcelServerProxy.SetReachable: server: http://mossserver:56737/SharedServices1/ExcelCalculationServer/ExcelService.asmx.

Excel Web Access                  5cbj   Medium        InternalEwr.RenderWebPart: Finished rendering internal EWR

And here’s the sorts of messages you’ll get if the workbook cannot be refreshed.

The workbook 'http://mossserver/sites/COLLECTION/PROJECT/Reports/Bug Progress2.xlsx' attempted to access external data using the unsupported provider 'Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;User ID="";Initial Catalog=Tfs_Analysis;Data Source=ASSERVER;Location=ASSERVER;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error'. [Session: 23.3897Bz4UV5Lllg0FPFLZm8k90.5.en-US5.en-US73.+0480#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060 User: DOMAIN\user]                00000301-f5f3-0da1-0000-000050f7b00b

Refresh failed for 'TfsOlapReport' in the workbook 'http://mossserver/sites/COLLECTION/PROJECT/Reports/Bug Progress2.xlsx'. [Session: 23.3897Bz4UV5Lllg0FPFLZm8k90.5.en-US5.en-US73.+0480#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060 User: DOMAIN\user]           

ExternalSource.ValidateConnection: Unable to get a connection: Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException: Exception of type 'Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException' was thrown.     at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.GetConnection(ConnectionRequest connectionRequest, ExtendedConnectionInfo extendedConnInfo, Credentials credentials, Int64 privateConnectionId, Boolean auditConnection)     at Microsoft.Office.Excel.Server.CalculationServer.ExternalSource.TryGetValidatedConnection(Request request, Credentials credentials, ExtendedConnectionInfo extendedConnectionInfo, Boolean shouldReportFailure, Boolean auditConnection, Connection& connectionOut). sessionId=23.3897Bz4UV5Lllg0FPFLZm8k90.5.en...             00000301-f5f3-0da1-0000-000050f7b00b

Hopefully this is enough to lead you in the right direction, but for further help you should try the following:

• Run the Team Foundation Server 2010 Best Practices Analyzer (BPA) tool that’s included in the Team Foundation Server Power Tools. It’s purpose is to help you find & fix configuration problems.
• Ask a question on the Team Foundation Server - Reporting & Warehouse forums