This is the third and last part in a small series on my adventures to get Excel Services working with SQL Server 2005 Analysis Services. In this part we will create and test an Excel sheet.

Part 1 – Preparing the AD for Kerberos
Part 2 – Preparing the MOSS server
Part 3 – Create and test an Excel sheet
Part 4 – Overview and updates

Step 1 – Set permissions

The first thing to do is make sure that your user account has acces to the Analysis Services cube.

Step 2 – Create a data connection file

• The first step is to create an ODC file the holds the connection to the Analyses Services cube. In my test environment I used the Microsoft Adventure Works sample. Go to the ‘My Data Sources’ folder in My Documents and double click ‘+Connect to New Data Source’
      
  
• When the Data Connection Wizard starts in Excel, select Microsoft SQL Server Analysis Services and click Next.

• Or start Excel 2007, select the Data tab and click ‘From Other Sources’ and then select ‘From Analysis Services’.
      
• In the next screen of the wizard, select the server– and instance name of the Analysis Services server. Set the log on credentials to Use Windows Authentication.
      
• In the next screen select your cube:
      
• In the last screen of the wizard, enter a filename for the ODC files and also add a friendly name and description. Make sure that you check the box ‘Always use this file to attempt to refresh data’.
• Click the button ‘Authentication Settings’ and select ‘Windows Authentication’
      
• Your ODC file is now saved.
• Navigate to the Data Connection Library (you created this in step 2) and upload your ODC file.
       

Step 2 – Create the Excel sheet

Start Excel and on the Data tab click Existing Connections. Click the button ‘Browse for more’ and navigate to the data connection library in your SharePoint site. Select the ODC file.
    

Now create the report you want.
     

Step 3 – Publish the Excel Sheet

After you finished creating your report, click on the Office button (you need the Enterprise version of Excel for this) and select ‘Publish’. Enter the url to your document library (see step 2) that was marked as a trusted file location and save the Excel sheet. After you have done this, Excel Web Access will open (if you checked the box) and load the Excel sheet.
    

If you succesfully configured all elements that I have discussed, you will be able to drilldown into this pivottable. If you did not, you will end up with this message:
     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.
This is shown as in the screenshot below.
    

If you did it all well, you should be able to drilldown into your pivot table, or refresh the data from the server: