Data Refresh Error using Excel Services in MOSS 2007

I created an Excel Report in MOSS 2007, which received data from a data connection to a MS Analysis Services Cube, stored locally on the same machine as the SharePoint Server. Everything worked fine until I tried to select an item from the PivotTable defined in the report. At that point I received the following “Data Refresh” problem.

Data Refresh Error

To get the Report to refresh, I first had to ensure that the Data Connection Library that I had created and referenced in my Excel Report, appeared in my “Trusted Data Connection Libraries”.

1. Go to the Sharepoint Central Administration Site.
2. Under the “Shared Services Administartion” link, select your Shared Services Provider link.
3. Under “Excel Services Settings“, select “Trusted data connection Libraries“
4. Make sure your Data Connection Library appears in the list, otherwise add the location.

Now, this didn’t solve the data refresh problem on its own. My data connection used “Windows Authentication” to connect to the SSAS Cube. When using this mode you need to be aware of the following.

 

Ensure Excel Services is configured to use the Delegation access model rather than the Trusted Subsystem model. You must set this configuration even if Excel Services and the back-end data source are running on the same server. To configure Excel Services to use the Delegation access model, use the Stsadm command-line tool. Type the following commands at a command prompt on the server, replacing sspname with the name of your Shared Services Provider (SSP):

 

stsadm -o set-ecssecurity -accessmodel delegation -ssp sspname
                e.g. stsadm -o set-ecssecurity -accessmodel delegation -ssp "Shared Services"

stsadm -o execsvcadmjobs
iisreset

I can’t claim full credit for this nugget of information, and must direct you to the following link entitled “Ten Tips for Using SharePoint Server 2007 with Excel Services“, which highlighted me to the Windows Authentication issue, plus other interesting notes.

150 Simple and Clear Hint & Tips for Windows XP & Vista

I’ve been a subscriber to the magazine ComputerActive for quite a few years and appreciate its “simple clear advice in plain english” approach on general computer topics.

In Issue 278, Computeractive has pulled together 150 hints and tips to help you get more out of Windows XP and Vista.

This handy booklet is useful for all levels of skill and will enable you to customise Windows to suit your needs. You will also discover how to make Windows faster and improve its performance.

You can download the booklet from here.