Install the Analysis Services OLE DB Provider on SharePoint Servers

  • Article

The Microsoft OLE DB Provider for Analysis Services (MSOLAP) is an interface that client applications use to interact with Analysis Services data. In a SharePoint environment that includes PowerPivot for SharePoint, the provider handles connection requests for PowerPivot data.

The data provider is automatically included in a PowerPivot for SharePoint installation. There are two reasons why you might need to manually install client libraries or data providers on a SharePoint server.

  • Enable backwards compatibility. SQL Server 2008 R2 workbooks specify the SQL Server 2008 R2 version of the Analysis Services OLE DB provider in their connection string. As such, this provider must be present on the computer in order for the request to succeed.

  • Enable data access on a dedicated Excel Services instance. If your SharePoint farm includes Excel Services on a server that does not also have PowerPivot for SharePoint, install the SQL Server 2012 version of the provider and other client connectivity components.

    Note

    These scenarios are not mutually exclusive. Hosting multiple workbook versions on a farm that includes application servers running Excel Services without a PowerPivot for SharePoint instance, requires that you install both older and newer versions of the provider on each Excel Services computer.

This topic contains the following sections:

Versions of the OLE DB Provider Supporting PowerPivot Data Access

Why you need to install the OLE DB Provider

Install the SQL Server 2008 R2 OLE DB Provider to host earlier version workbooks

Install the SQL Server 2012 OLE DB Provider and client connectivity components on standalone Excel Services servers

Versions of the OLE DB Provider Supporting PowerPivot Data Access

A SharePoint farm might include multiple versions of the Analysis Services OLE DB provider, including older versions that do not support PowerPivot data access.

By default, SharePoint installs the SQL Server 2008 version of the provider. Although it is identified as MSOLAP.4 (the same version number used for SQL Server 2008 R2), this version does not work for PowerPivot data access. In order for connections to succeed, you must have the SQL Server 2008 R2 or SQL Server 2012 version of the provider.

A post-SQL Server 2008 version of the OLE DB provider includes transports and connection support for Analysis Services data structures that are stored in memory. PowerPivot workbooks use newer versions of this provider to request query processing from PowerPivot servers in the farm. To get an updated version, you can download and install it through a SQL Server Feature Pack page.

The following table describes the valid versions:

Product Version

File Version

Valid for:

SQL Server 2008 R2

MSOLAP100.dll in the file system

MSOLAP.4 in an Excel connection string

10.50.1600 or later in file version details

Use for data models created using the SQL Server 2008 R2 version of PowerPivot for Excel.

SQL Server 2012

MSOLAP110.dll in the file system

MSOLAP.5 in an Excel connection string

11.00.0000 or later in file version details

Use for data models created using the SQL Server 2012 version of PowerPivot for Excel.

Why you need to install the OLE DB Provider

There are two scenarios that call for manually installing the OLE DB provider on servers in the farm.

The most common scenario is when you have older and newer versions of PowerPivot workbooks that are saved in document libraries in the farm. If analysts in your organization are using the SQL Server 2008 R2 version of PowerPivot for Excel, and they save those workbooks to a SQL Server 2012 PowerPivot for SharePoint installation, the older workbook will not work. Its connection string will reference an older version of the provider, which won’t be on the server unless you install it. Installing both versions will enable data access for PowerPivot workbooks created in older and newer versions of PowerPivot for Excel. SQL Server 2012 Setup does not install the SQL Server 2008 R2 version of the provider, so you must install it manually if you are using workbooks from a previous version.

The second scenario is when you have a server in a SharePoint farm that runs Excel Services, but not PowerPivot for SharePoint. In this case, the application server that runs Excel Services must be manually updated to use both the newer version of the provider, as well as install an instance of the Microsoft.AnalysisServices.Xmla.dll file in the global assembly. These components are necessary for connecting to a PowerPivot for SharePoint instance. If Excel Services is using an older version of the provider, the connection request will fail.

Install the SQL Server 2008 R2 OLE DB Provider to host earlier version workbooks

Use the following instructions to install the SQL Server 2008 R2 version of the MSOLAP.4 provider, and register the Microsoft.AnalysisServices.ChannelTransport.dll file. The ChannelTransport is a subcomponent of the Analysis Services OLE DB provider. The SQL Server 2008 R2 version of the provider reads the registry when using ChannelTransport to make a connection. Registering this file is a post-installation step required only for connections handled by the SQL Server 2008 R2 provider on a SQL Server 2012 server.

Step 1: Download and install the client library

  1. On the SQL Server 2008 R2 Feature Pack page, find Microsoft Analysis Services OLE DB Provider for Microsoft SQL Server 2008 R2.

  2. Download the x64 Package of the SQLServer2008_ASOLEDB10.msi installation program. Although the file name contains SQLServer2008, it is the correct file for the SQL Server 2008 R2 version of the provider.

  3. On the computer that has an installation of PowerPivot for SharePoint, run the .msi to install the library.

  4. If you have other servers in the farm that run just Excel Services, without PowerPivot for SharePoint on the same server, repeat the previous steps to install the 2008 R2 version of the provider on the Excel Services computer.

Step 2: Register the Microsoft.AnalysisServices.ChannelTransport.dll file

  1. Use the regasm.exe utility to register the file. If you have not run regasm.exe before, add its parent folder, <drive>:\Windows\Microsoft.NET\Framework64\v4.0.30319\, to the system path variable.

  2. Open a command prompt with administrator permissions.

  3. Go to this folder <drive>:\Windows\assembly\GAC_MSIL\Microsoft.AnalysisServices.ChannelTransport\10.0.0.0__89845dcd8080cc91

  4. Enter the following command: regasm microsoft.analysisservices.channeltransport.dll

  5. Repeat the previous steps for any computer on which you manually installed the 2008 R2 version of the provider.

Verify installation

  1. You should now be able to slice or filter SQL Server 2008 R2 workbooks. If an error occurs, verify that you used the 64-bit version of regasm.exe to register the file.

  2. Additionally, you can check the file version.

    Go to Program files\Microsoft Analysis Services\AS OLEDB\10. Right-click msolap100.dll and select Properties. Right-click msolap100.dll and select Properties. Click Details.

    View the file version information. The version should include 10.50.<buildnumber>.

Install the SQL Server 2012 OLE DB Provider on an Excel Services server

Use the following instructions to add the OLE DB provider and other client connectivity components to SharePoint servers that do not already have them installed, such as application servers that run Excel Services without PowerPivot for SharePoint on the same hardware.

Use these instructions to install the current Analysis Services OLE DB provider and to add the Microsoft.AnalysisServices.Xmla.dll to the global assembly.

Run SQL Server Setup and Install the Client Connectivity Tools

  1. On the application server that hosts Excel Services, run SQL Server Setup.

  2. On the Installation page, choose New SQL Server stand-alone installation or add features to an existing installation.

  3. On the Installation Type page, choose Perform a new installation of SQL Server 2012.

  4. On the Setup Role page, choose SQL Server Feature Installation.

  5. On the Feature Selection page, click Client Tools Connectivity. This option installs Microsoft.AnalysisServices.Xmla.dll

    Do not select any other features.

  6. Click Next to finish the wizard, and then click Install to run Setup.

  7. Repeat the previous steps if you have other servers running Excel Services, without a PowerPivot for SharePoint installation on the same server.

Verify MSOLAP.5 is a trusted provider

  1. In Central Administration, click Manage service applications, and then click the Excel Services service application.

  2. Click Trusted Data Providers.

  3. Verify that MSOLAP.5 appears in the list. Depending on how you configured PowerPivot for SharePoint, MSOLAP.5 might already be trusted. If you used the PowerPivot Configuration tool, but then excluded this action from the task list, MSOLAP.5 will not be trusted by Excel Services and now needs to be added manually.

  4. If MSOLAP is not listed, click Add Trusted Data Provider.

  5. In Provider ID, type MSOLAP.5.

  6. For Provider Type, ensure that OLE DB is selected.

  7. In Provider Description, type Microsoft OLE DB Provider for OLAP Services 11.0.

Verify installation

  1. Go to Program files\Microsoft Analysis Services\AS OLEDB\110.

  2. Right-click msolap110.dll and select Properties.

  3. Click Details.

  4. View the file version information. The version should include 11.00.<buildnumber>.

  5. In the Windows\assembly folder, verify that Microsoft.AnalysisServices.Xmla.dll, version 11.0.0.0, is listed.

See Also

Concepts

PowerPivot for SharePoint Installation (SharePoint 2010)