Troubleshoot Data Refresh of SharePoint Excel Services

To follow the Walkthrough: Creating a Dashboard Page of Chapeter 7 of Inside Microsoft Office SharePoint Server 2007, With VS 2008 and MS SQL 2008, you need to do these extra steps:

1. Download Adventure Works 2008 Sample Database from http://www.codeplex.com/MSFTDBProdSamples

2. After the installation, follow next steps to build and deploy the Analysis Services Sample Database.

http://www.ssas-info.com/analysis-services-faq/29-mgmt/242-how-install-adventure-works-dw-database-analysis-services-2005-sample-database

A2: For Analysis Services 2008 (Sample Refresh 1). Steps if on the server you have full text search enabled:

• Go to website http://www.codeplex.com/MSFTDBProdSamples
• Select Releases tab. At the time of publishing this article latest release was for SQL Server 2008 RTM and direct link to this release is: http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=18407
• If you have full text search, you should download one of the msi files matching your processor architecture (x86, x64, ia64):

SQL2008.AdventureWorks_All_Databases.x86.msi
SQL2008.AdventureWorks_All_Databases.x64.msi
SQL2008.AdventureWorks_All_Databases.ia64.msi

• Start msi file. Accept licence agreement. In the "Custom Setup" screen leave default selections. In the next "Database Setup" screen choose database instance where you would like to restore SQL Server database. Note - it is hard to see that you have option to select database from the drop down list, so please be careful. In the "Ready to install" screen choose "Install".
• If during installation you get error message: "PrepInstance() failed for MSSQLSERVER. The following features are missing: Full Text Search. Fix the problems and re-run setup.", you should use different steps described below (A3).
• Now you have to setup sample Analysis Services database. The same installation file that you downloaded above also installed Adventure Works Analysis Services Project in the folder "C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008 Analysis Services Project". There you will find 2 sub-folders: "Enterprise" and "Standard". If you have SQL Server Enterprise or Developer edition, then use project from Enterprise sub-folder as it has features available just in SQL Server Enterprise Edition.
• From Enterprise or Standard folder open solution project by clicking on "Adventure Works.sln" solution file. Tip for Windows Vista users: if you are doing installation from/on Windows Vista OS, I would recommend that you copy sample project out of "c:\Program Files" folder. In Windows Vista this folder has extra security and you might not be able to save changes in project files as files will be read-only.
• If SQL Server Adventure Works DW database is installed not on the local machine, then after you open solution in BIDS (Business Intelligence Development Studio), double click on data source "Adventure Works.ds", click "Edit" on connection string and change Server name from localhost to your SQL Server name where Adventure Works DW database is installed.
• In BIDS Choose menu option "Project" and then "Adventure Works DW Properties". In Deployment screen change Server name from localhost to name of server where Analysis Services database should be deployed.
• Deploy project. This might take few minutes.
• Now you have Analysis Services sample database "Adventure Works DW 2008".

3. Now follow the walkthrough to create Data Connection to the Analysis Services and upload the Data Connection to Data Connection Library of SharePoint.

4. Configure the Excel Sample file to use this data connection and publish the Excel file as Report

5. Now try to refresh the data in the report. You many encounter this error:

Data Refresh Failed

Unable to retrieve external data for the following connections: Adventure Works The data sources may be unreachable, may not be responding, or may have denied you access. Verify that data refresh is enabled for the trusted file location and that the workbook data authentication is correctly set. How do I ensure a secure and valid refresh operation?

Check the SharePoint log file, you will find error like next:

05/10/2009 16:12:10.63 w3wp.exe (0x0A84) 0x2308 Excel Services Excel Services External Data 2m4m Information The workbook 'http://MOSSlap01/sites/Reports/ReportsLibrary/Top10ProductSubCategories.xlsx' attempted to access external data using the unsupported provider 'Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Adventure Works DW 2008;Data Source=MOSSlap01;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error'. [Session: 22.82zJET5wfSg1RXzz17lGQH90.5.en-US5.en-US73.+0480#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060 User: MOSSLAP01\MOSS] 000001e3-d675-0e04-0000-000050f7b00b

05/10/2009 16:12:10.64 w3wp.exe (0x0A84)

The reason for this error is that “MSOLAP.4” is not configured as “Trusted Data Provider” in Shared Services Admin.

Add “MSOLAP.4” as trusted data provider.

Do IISReset after changing the Excel Service configurations.