Export (0) Print
Expand All

Configure Analysis Services for processing data models in Excel Services

SharePoint 2013

Published: September 4, 2012

Summary: Install SQL Server Analysis Services 2012 SP1 for processing data models in Excel Services in SharePoint Server 2013.

Applies to:  SharePoint Server 2013 Enterprise 

In Excel Services in SharePoint Server 2013, you can specify one or more instances of SQL Server 2012 Analysis Services (SSAS) for use in processing Data Models that are created in Excel 2013. In this configuration, SQL Server 2012 Analysis Services provides a backend service for Excel Services to load, query, and refresh Excel 2013 Data Models so that users can interact with these types of workbooks in the browser.

This scenario describes how to install and configure an instance of Analysis Services 2012 SP1 for use in processing data models in Excel Services.

In this article:

Before you begin

Before starting, read the following information about permissions and software requirements. Follow the specified steps to install or configure prerequisite software or to modify settings. For example:

  • This scenario requires SQL Server 2012 with Service Pack 1 (SP1). You can download it from the Microsoft Download Center (http://go.microsoft.com/fwlink/p/?LinkId=286738).

  • To configure Excel Services, you must be a Farm Administrator or an Excel Services service application administrator.

  • You must know what account is being used to run the Excel Services application pool. This account will be added to the Analysis Services administrators group.

Install SQL Server Analysis Services 2012 SP1

The first step is to install SQL Server Analysis Services 2012 SP1. This must be installed on a server on the domain, but does not need to be installed on a SharePoint Server application server. If you plan on a large amount of data model use in your organization, we recommend installing this instance of Analysis Services on its own server.

To install Analysis Services 2012 SP1

  1. Run the SQL Server Installation Wizard (Setup.exe).

  2. In the left pane, click Installation.

  3. Click New SQL Server stand-alone installation or add features to an existing installation.

  4. On the Setup Support Rules page, review any warnings and click OK.

  5. On the Product Key page select Evaluation or type a product key, and then click Next.

  6. On the License Terms page, accept the license agreement, and then click Next.

  7. If you see another Setup Support Rules page, review any warnings and click Next.

  8. On the Setup Role page:

    • Select the SQL Server PowerPivot for SharePoint option.

    • Clear the Add SQL Server Database Relational Engine Services to this installation check box.

      note Note:

      If you want to install the SQL Server management tools, you can leave this check box selected even if you do not plan to use the SQL Server Database Relational Engine.

    • Click Next.

  9. On the Feature Selection page, click Next.

  10. On the Installation Rules page, review any warnings and click Next.

  11. On the Instance Configuration page, click Next.

  12. One the Disk Space Requirements page, verify that you have sufficient disk capacity to install the feature and then click Next.

  13. On the Server Configuration page, type NT AUTHORITY\NETWORK SERVICE (recommended), or the account name and password for an Active Directory account to run the Analysis Services instance, in the SQL Server Analysis Services row, and then click Next.

  14. On the Analysis Services Configuration page:

    • Click Add Current User if you want to add the current user as an Analysis Services administrator.

    • Click Add, and on the Select Users, Computers, Service Accounts, or Groups dialog box, type the name of the account that runs the Excel Services application pool and click OK.

      note Note:

      You can add additional administrators here as required by your organization.

    • Click Next.

  15. On the Error Reporting page, click Next.

  16. On the Installation Configuration Rules page, review any warnings and click Next.

  17. On the Ready to Install page, click Install.

  18. When the installation is complete, click Close.

  19. Restart the computer.

If you have a firewall in your environment, review the SQL Server Books Online article, Configure the Windows Firewall to Allow Analysis Services Access.

Configure data model settings in Excel Services

After the instance of Analysis Services has been installed, you must configure Excel Services to use this instance to process data models. This is configured in Excel Services Global Settings.

To configure data model settings

  1. On the SharePoint Central Administration website, under Application Management, click Manage service applications.

  2. Click the Excel Services service application.

  3. On the Manage Excel Services Application page, click Data Model Settings.

  4. On the Excel Services Application Data Model Settings page:

    • Click Add Server.

    • In the Server Name text box, type the name of the instance of Analysis Services that you installed, in the format <ServerName>\POWERPIVOT.

    • Optionally, type a description for the server.

    • Click OK.

After you have configured the instance of Analysis Services in Excel Services Global Settings, Excel Services is ready to process data models.

note Note:

It can take up to five minutes for the changes to take effect.

If your data processing needs require it, you can specify multiple instances of SQL Server Analysis Services 2012 SP1 to provide greater scale and processing power.

Verify that data models are loading correctly

To verify that Excel Services can load a data model, upload a sample workbook that contains a data model and a slicer that uses that data model. Open the workbook in the browser, and then click on a slicer. Excel Services typically shows cached data without loading the data model when opening a workbook. Clicking on a slicer requires Excel Services to update the data cache, which in turn requires loading the data model on the registered Analysis Services instance.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft