Using Excel & Excel Services with SQL Server Analysis Services 2008
[Cross-posted from Excel Team Blog.]
With the recent announcement of SharePoint Server 2007 supporting SQL Server 2008, like you, I was excited to setup my Excel / Excel Services environment to take advantage of the great new capabilities available, and there are many. I encourage you to take a look at how the new SQL environment will benefit your SharePoint deployment, and your Business Intelligence reporting and analysis capabilities.
SharePoint Server 2007 can use SQL Server 2008 as a repository without any extra configuration requirements. What about connecting Excel client to the new Analysis Services environment?
Excel accesses Analysis Services 2008 the same way it does 2005. From within Excel, select the Analysis Services drop down from the Data tab -> From Other Sources drop down, and then walk through the data connection wizard to identify location, cube, and credentials. Ensure that the necessary SQL Server 2008 client components are installed prior to making the connection as the provider for Analysis Services 2008 is MSOLAP.4 (more about this later).
You should now have a connection to a cube within Excel and a pivot table ready.
So far so good right? With a few simple clicks you are accessing the cube on Analysis Services 2008. You've created a great looking report, and now want to distribute it on SharePoint Excel Services. Here's how you go about setting up the connection so that Excel Services can access Analysis Services 2008.
If you take a look at the connection string in Excel, you'll notice that connecting to Analysis Services 2008 uses the following provider, MSOLAP.4. This is not in the list of providers that ships with Excel Services so you will need to add this to the list. You will also need to install the client access components of SQL Server 2008 on each of your SharePoint servers that will require access to SQL Server 2008. For example, you can install the client access components on the Shared Service where Excel Services runs.
To add the provider to the list of approved providers in Excel Services, go to the Shared Services administration page (Central Administration -> Shared Services Administration) for Excel Services. Select Trusted Data Providers from the Excel Services Settings of the Shared Services Administration page. You will see that by default MSOLAP, MSOLAP.1, MSOLAP.2 and MSOLAP.3 are installed by default. You will need to add MSOLAP.4 to the trusted list in order for the connection to work in Excel Services.
Click Add Trusted Data Provider at the top of the list, and enter:
Provider ID = MSOLAP.4
Data Provider Type = OLE DB
Description = Microsoft OLE DB Provider for OLAP Services 10.0.
You are now set. Publish your workbook to Excel Services and you will be able to view, interact and refresh data from Analysis Services 2008 in Excel Services.
Pej Javaheri, Senior Product Manager for Business Intelligence
Microsoft SharePoint Products and Technologies
