Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I'm trying to fix the "failed to refresh: TfsOlapReport" error on the TFS project portal dashboards in SharePoint 2010. I've followed the instructions here to make sure the Secure Store Service and the TFS Reports are configured correctly.

The only error log I can see is the following stack trace in the SharePoint 2010 server ULS logs when Excel Services attempt to connect to the data source.

The Application ID is "TFS", which links to an account in SSS "DOMAIN\TFSReports". You can see that this lookup succeeds. It looks to me like there is some strange error when opening the OLEDBConnection - PF_CHECK_ERROR.

(This is paraphrased from another machine without internet access)

StoredCredentialProvider.GetCredentials: Credentials added to stored cache for Secure Store Application Id: TFS
ConnectionManager.GetConnection: ConnectionKey=[[3344175, TFSReports], [Oledb, Stored, TFS, False], 1033, ]
ConnectionManager.GetConnection: Didn't find the connection without the workbook version. Updated connection key with the workbook version for another lookup ConnectionKey=[[3344175, TFSReports], [Oledb, Stored, TFS, False], 1033, ConnectionInfo.WorkbookVersion: Uri=http://sharepoint/Reports/Burndown.xlsx, Version=Wednesday 06 June 2012 14:31:00]
Credentials.TryLogonUser: called for connection username: TFSReports, domain: DOMAIN
PF_CHECK_ERROR return 'hresult error' 0x80040154 ; Stack Trace: NA
OLEDBConnection::SetInitProperties: Class not registered
PF_CHECK_ERROR return 'hresult error' 0x80040154 ; Stack Trace: NA
ConnectionManager.GetConnection: Failed to created new connection [...]
ConnectionManager.GetConnection: Caught an exception [...]
Refresh failed for 'TfsOlapReport' in the workbook http://sharepoint/Reports/Burndown.xlsx
ConnectionManager.InitConnInfoFromOdcFile: ODC File Uri is not valid
ExternalSource.ExecuteOperation: We exhausted all available connection information.

I've run out of things to search for now. Has anyone seen anything like this before? Does anyone have a stack trace of a successful Excel Services refresh so I can see where things are going wrong? Any help appreciated.

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

Since SharePoint 2010 and Analysis Services 2008 were on seperate machines, I could see the logon via Windows Authentication as DOMAIN\TFSReports was succeeding on the Analysis Services machine in Event Viewer\Security logs. So I knew Excel Services was getting that far...

Googling in general about the PF_CHECK_ERROR and "class not registered" it looked like some sort of COM error in the OLEDBConnection. What class or DLL could it be trying to load that isn't registered?

Well, the OLE DB connection type is MS OLAP, and the connection string in the sheet's embedded ODC file specifies OLAP.3. Surprise, surprise, OLAP 2 and 4 are present on the Analysis Services machine, but not MSOLAP.3!! (Confirmed with regedit - HKEY_CLASSES_ROOT\MSOLAP).

So I opened the report, changed the connection string to Provider=MSOLAP.4 in the connection properties for TfsOlapReport, saved and published - that particular report then refreshed correctly. I have now updated all of the Excel Reports in the Team Project to use OLAP 4 and all dashboards are working.


Hope this helps anyone else trying to use Excel Services and the Secure Store Service - I was about to go even further down the rabbit hole with Kerberos, especially as client and server are on different (but trusted) domains, but no you don't need any of that. It really is as simple as the instructions here as long as all the dependencies are installed correctly!

To get the initial logs I post in the question, I had to turn on Verbose logging for Excel Services Application and Secure Store Service in Central Administration > Monitoring > Configure diagnostic logging. Logs will then appear in %Program Files%\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS (obvious, huh?).

(One thing I'm still unclear about is whether installing MSOLAP 3 on the Analysis Services machine would be a way fix the problem without changing connection strings. Is OLAP loaded by Excel Services on the SharePoint server or by Analysis Services on the Analysis Server? I will confirm when I get hold of an x64 installer for it).

share|improve this answer
add comment

Please confirm the following as mentioned below

  1. The ODC file specified in the workbook is in an accessible trusted data connection library, or configure the trusted file location where the workbook resides to allow embedded connections.

  2. The unattended service account is configured in Excel Services Global Settings, that the designated target application exists in Secure Store, and that the Excel Services application pool account is a Member of the target application.

  3. The document library where the workbook resides is configured as a trusted file location in Excel Services.

If embedded connections are allowed and Always use connection file is not selected, Excel Services will attempt to connect using the embedded connection information. If the connection fails, the appropriate error will display. Excel Services will then attempt to use this ODC file.

A detailed data refresh flowchart has been provided by Microsoft for excel services SP 2010 which should assist you further for self troubleshooting :- http://technet.microsoft.com/en-us/library/hh369968.aspx

share|improve this answer
    
Thanks for your troubleshooting tips Moss. The exact cause was down to a missing dependency on OLAP 3 (see answer). –  idlemind Jun 27 '12 at 14:17
1  
That was a good catch ! Versions with MS products can be a real pain at times :) –  Arko D Jun 28 '12 at 10:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.