Installation SQL Server PowerPivot for SharePoint 2013 Add-In

 

PowerPivot for SharePoint is a collection of middle-tier and backend services that provide PowerPivot data access in a SharePoint 2013 farm. The PowerPivot for SharePoint add-in (spPowerpivot.msi) is an installer package used to install the middle-tier components

Middle-tier: Enhancements to the PowerPivot functionality in SharePoint 2013. Functionality includes using workbooks as a data source, scheduled data refresh, and the PowerPivot Management Dashboard.

PowerPivot for SharePoint 2013 is a Microsoft Windows Installer package (spPowerpivot.msi) that deploys Analysis Services client libraries and copies PowerPivot for SharePoint 2013 installation files to the computer. The installer does not deploy or configure PowerPivot features in SharePoint. The following components install by default:

· PowerPivot for SharePoint 2013. This component includes PowerShell scripts (.ps1 files), SharePoint solution packages (.wsp), and the PowerPivot for SharePoint 2013 configuration tool to deploy PowerPivot in a SharePoint 2013 farm.

· Microsoft OLE DB Provider for Analysis Services (MSOLAP).

· ADOMD.NET data provider.

· SQL Server 2012 Analysis Management Objects.

Note: This topic describes installing the middle-tier PowerPivot solution files and PowerPivot for SharePoint 2013 Configuration tool. After the installation, see the following topic for information on the configuration tool and additional features, Configure PowerPivot and Deploy Solutions (SQL Server 2012 SP1).

 

Download the spPowerPivot.msi via http://www.microsoft.com/en-us/download/details.aspx?id=35577

Our SP Farm contains 2 App, 2 Web, 2 Search servers and an Office Web Apps farm so the below diagram is used:

PowerPivot for SharePoint 2013 Three Server Deployment

In the following three-server deployment, the SQL Server Database Engine, Analysis Services server running in SharePoint mode, and SharePoint, each run on a separate server. In SQL Server 2012 SP1 the PowerPivot for SharePoint 2013 installer package (spPowerPivot.msi) must be installed on the SharePoint server.

 

(1)	Excel Service Application. The service application is created as part of the SharePoint installation.
(2)	PowerPivot Service Application. Default name is Default PowerPivot Service Application.
(3)	RUN the spPowerPivot.msi to install data providers, the PowerPivot configuration tool, PowerPivot Gallery, and schedule data refresh.
(4)	An Analysis Services Server in SharePoint Mode. Configure the Excel Services Application Data Model Settings to use this server.
(5)	The SharePoint content, configuration, and service application databases.

A recommended best practice is to install spPowerPivot.msi on all servers in the SharePoint farm for configuration consistency, including Application servers and web-front end servers. The installer package includes the Analysis Services data providers as well as the PowerPivot for SharePoint 2013 configuration tool. When you installspPowerPivot.msi you can customize the installation by excluding individual components.

Data providers: Several SharePoint and SQL Server technologies use the Analysis Services data providers including Excel Services, PerformancePoint Services, and Power View. Installing spPowerPivot.msi on all SharePoint servers ensures the full set of Analysis Services data providers and PowerPivot connectivity is consistently available across the farm.

You must install the Analysis Services data providers on a SharePoint 2013 server using spPowerPivot.msi. Other installer packages available in the SQL Server 2012 SP1 Feature Pack are not supported because these packages do not include the SharePoint 2013 support files that the data providers require in this environment.

Configuration Tool: The PowerPivot for SharePoint 2013 configuration tool is required on only one of the SharePoint servers. However a recommended best practice in multi-server farms is to install the configuration tool on at least two servers so you have access to the configuration tool if one of the two servers is offline.

 

 

Requirements and Prerequisites

---

• Microsoft SharePoint Server 2013.
• spPowerPivot.msi is 64-bit only, in accordance with the requirements of SharePoint products and technologies.
• SQL Server 2012 Analysis Services (SSAS) server in PowerPivot mode. Excel Services will use the SQL Server Analysis Services instance as a PowerPivot server. Analysis Services can run on the local or a remote computer.
• To install PowerPivot for SharePoint 2013, the current user is required to be an administrator on the computer and a SharePoint Farm Administrators group.
• For more information on PowerPivot for SharePoint requirements and pre-requisites, go to Hardware and Software Requirements (PowerPivot for SharePoint)(http://go.microsoft.com/fwlink/?LinkID=247828).

 

 

To Install PowerPivot for SharePoint

---

The spPowerpivot.msi installer package supports both a graphical user interface and a command-line mode. Both methods of installation require that you run the .msi with administrator privileges.

Next

I accept the terms….

Next

All features are selected by default

Make sure you are not using the System Drive

Next

Install

Finish

 

 

Deploy the SharePoint Solution Files with the PowerPivot for SharePoint 2013 Configuration Tool

---

Three of the files copied to the hard drive by spPowerPivot.msi are SharePoint solution files. The scope of one solution file is the farm level while the scope of the other file is the Web application level. The files are the following:

• PowerPivotFarmSolution.wsp
• PowerPivotFarm14Solution.wsp
• PowerPivotWebApplicationSolution.wsp

The solution files are copied to the following folder:

C:\Program Files\Microsoft SQL Server\110\Tools\PowerPivotTools\SPAddinConfiguration\Resources

Following the .msi installation, run the PowerPivot for SharePoint 2013 Configuration Tool to configure and deploy the solutions in the SharePoint farm.

When running the configuration tool, be aware that lots of settings will be automatically set for you. If you don’t want that and if you want to configure the below options yourself, DO NOT USE THIS TOOL.

There are a lot of services, service application, managed accounts (application pool accounts), permissions etc involved!! And the tool can also break stuff so be careful!

 

 

Requirements for Using the Configuration Tools

---

• You must be a farm administrator.
• You must be a server administrator on the Analysis Services instance SharePoint 2010 only).
• You must be db_owner on the farm’s configuration database.

http://msdn.microsoft.com/en-US/library/616877e3-464a-4c97-bc74-1fa6f4faa756

On a Windows 2012 Server – open the start screen and look for:

Run as Admin

Yes

Run

Yes

 

If you want to configure all settings yourself

Create a Managed account and use that account as the Application Pool account for your PowerPivot Service Application (Pool)

Make sure that the Application Pool Account you use for both Excel Services and PowerPivot Service application have enough permissions on your SQL box on all databases. Both accounts should have

SPDataAccess permissions on all your content databases

Make sure that bot the Application Pool accounts have WSS_Content_Application_Pools role membership on your SharePoint Admin and Config database.

Make sure that the Application Pool account which you use for the PowerPivot Service Application has Full Read access on all Web Applications

Make sure that the Secondary logon service is set to Manual and start the service on your SharePoint servers!!

 

 

Technorati Tags: SQL Server PowerPivot,PowerPivot,SharePoint 2013,Managed Account

Install Analysis Services Server in SharePoint Mode for SharePoint 2013 (SQL Server 2012 SP1)

 

PowerPivot for SharePoint is a collection of middle-tier and backend services that provide PowerPivot data access in a SharePoint 2013 farm.

· Backend services: If you use PowerPivot for Excel to create workbooks that contain analytical data, you must have PowerPivot for SharePoint to access that data in a server environment. You can run SQL Server Setup on a computer that has SharePoint Server 2013 installed, or on a different computer that has no SharePoint software. Analysis Services does not have any dependencies on SharePoint.

· Middle-tier: Enhancements to the PowerPivot experiences in SharePoint including PowerPivot Gallery, Schedule data refresh, Management dashboard, and data providers.

You do not need to deploy the PowerPivot for SharePoint 2013 add-in into the farm. You only need to install an Analysis Services server in SharePoint mode and register the server within the Excel Services Data Model settings.

Deploying the PowerPivot for SharePoint 2013 add-in enables additional functionality and features in your SharePoint farm. The additional features include PowerPivot Gallery, Schedule Data Refresh, and the PowerPivot Management Dashboard

In this blog I will explain the installation and configuration of the Analysis Services (SQL Server PowerPivot for SharePoint) and the integration with Excel Services

 

Prerequisites

1. You must be a local administrator to run SQL Server Setup.

2. SharePoint Server 2013 enterprise edition is required for PowerPivot for SharePoint. You can also use the evaluation enterprise edition.

3. The computer must be joined to a domain in the same Active Directory forest as Excel Services.

4. The PowerPivot instance name must be available. You cannot have an existing PowerPivot-named instance on the computer on which you are installing Analysis Services in SharePoint mode.

5. Review Hardware and Software Requirements for Analysis Services Server in SharePoint Mode (SQL Server 2012 SP1).

6. Review the release notes at SQL Server 2012 Service Pack 1 Release Notes (http://go.microsoft.com/fwlink/?LinkID=248389)

SQL Server Edition Requirements

Business intelligence features are not all available in all editions of SQL Server 2012. For details, see Features Supported by the Editions of SQL Server 2012 (http://go.microsoft.com/fwlink/?linkid=232473) and Editions and Components of SQL Server 2012.

The current release notes can be found at SQL Server 2012 SP1 Release Notes (http://go.microsoft.com/fwlink/?LinkID=248389).

Microsoft SQL Server 2012 Release Notes (http://go.microsoft.com/fwlink/?LinkId=236893).

Step 1: Install PowerPivot for SharePoint

---

In this step, you run SQL Server Setup to install an Analysis Services server in SharePoint mode. In a subsequent step, you configure Excel Services to use this server for workbook data models.

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

2. Click Installation on the navigation pane to the left.

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

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

5. If you see the Product Key, specify the evaluation edition or enter a product key for a licensed copy of the enterprise edition. Click Next.

6. Accept the Microsoft Software License Terms of agreement, and then click Next.

7. The Install Setup Files page runs for several minutes.

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

Note: Because Windows Firewall is enabled, you see a warning to open ports to enable remote access.

9. On the Setup Role page, select SQL Server PowerPivot for SharePoint. This option installs Analysis Services in SharePoint mode.

Optionally, you can add an instance of the Database Engine to your installation. You might add the Database Engine when setting up a new farm and need a database server to run the farm’s configuration and content databases. This option also installs SQL Server Management Studio.

If you add the Database Engine, it is installed as a PowerPivot named instance. Whenever you specify a connection to this instance, enter the database name in this format: [servername]\PowerPivot.

Click Next.

10. In Feature Selection, a read-only list of the features is displayed for informational purposes. You cannot add or remove items the preselected items for this role. Click Next.

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

12. On the Instance Configuration page, a read-only instance name of 'PowerPivot' is displayed for informational purposes. This instance name is required and it cannot be modified. However, you can enter a unique Instance ID to specify a descriptive directory name and registry keys. Click Next.

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

14. On the Server Configuration page, configure all of the services for Automatic Startup Type. Specify the desired domain account and password for SQL Server Analysis Services, (1) in the following diagram.

o For Analysis Services, you can use a domain user account or NetworkService account. Do not use LocalSystem or LocalService accounts.

o If you added the SQL Server Database Engine and SQL Server Agent, you can configure the services to run under domain user accounts or under the default virtual account.

o Never provision service accounts with your own domain user account. Doing so grants the server the same permissions that you have to the resources in your network. If a malicious user compromises the server, that user is logged in under your domain credentials. The user has the permissions to download or use the same data and applications that you do.

o

Click Next.

15. If you are installing the Database Engine, the Database Engine Configuration page appears. In Database Engine Configuration, click Add Current User to grant your user account administrator permissions on the Database Engine instance.

Click Next.

16. On the Analysis Services Configuration page, click Add Current User to grant your user account administrative permissions. You will need administrative permission to configure the server after Setup is finished.

o In the same page, add the Windows user account of any person who also requires administrative permissions. For example, any user who wants to connect to the Analysis Services service instance in SQL Server Management Studio to troubleshoot database connection problems must have system administrator permissions. Add the user account of any person who might need to troubleshoot or administer the server now.

Note

All service applications that require access to the Analysis Services server instance need to have Analysis Services Administrative permissions. For example, add the service accounts for Excel Services, Power View, and Performance Point Services. Also, add the SharePoint farm account, which is used as the identity of the web application that hosts Central Administration.

17. Click Next.

18. On the Error Reporting page, click Next.

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

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

21. If you see the dialog Computer Restart Required, click OK.

22. When the installation is complete, click Close.

23. Restart the computer.

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

 

Verify the SQL Server Installation

Verify that the Analysis Services Service is running.

1. In Microsoft Windows click Start, click All Programs, and click the Microsoft SQL Server 2012 group.
2. Click SQL Server Management Studio.
3. Connect to the Analysis Services instance, for example [your server name]\POWERPIVOT. If you can connect to the instance, you have verified the Service is running.

 

 

Step 2: Configure Basic Analysis Services SharePoint Integration

Grant Excel Services Server Administration Rights on Analysis Services

You do not need to complete this section if during the Analysis Services installation; you added the Excel Services Application service account as an Analysis Services administrator.

1. On the Analysis Services server, start SQL Server Management Studio and connect to the Analysis Services instance, for example [MyServer]\POWERPIVOT.
2. In Object Explorer, Right-click the instance name and click Properties.

3. In the left pane, click Security. Add the domain login you configured for the Excel Services Application.

In case you have already defined service accounts for PowerView and/or Performance Point Services, add them as well.

Configure Excel Services for Analysis Services integration

1. In SharePoint Central Administration, in the Application Management group, click Manage Service Applications.
2. Click the name of your service application, the default is Excel Services Application.
3. On the Manage Excel Services Application page, click Data Model Settings.
4. Click Add Server.
5. In Server Name, type the Analysis Services server name and the PowerPivot instance name. For example MyServer\POWERPIVOT. The PowerPivot instance name is required.

Type a description.

6. Click Ok.
7. The changes will take effect in a few minutes or you can Stop and Start the service Excel Calculation Services.

Another option is to open a command prompt with administrative privileges, and type iisreset /noforce.

You can verify the server is recognized by Excel Services by reviewing entries in the ULS log. You will see entries similar to the following:

Excel Services Application            Data Model        27           Medium                Check Administrator Access ([ServerName]\POWERPIVOT): Pass.        f127bd9b-bae3-e0e0-9b48-3f7b5ad1eae6

Excel Services Application            Data Model        27           Medium                Check Server Version ([ServerName]\POWERPIVOT): Pass (11.0.2809.24 >= 11.0.2800.0).         f127bd9b-bae3-e0e0-9b48-3f7b5ad1eae6

Excel Services Application            Data Model        27           Medium                Check Deployment Mode ([ServerName]\POWERPIVOT): Pass.            f127bd9b-bae3-e0e0-9b48-3f7b5ad1eae6

Make sure you see the above entries, if not visible, your connection is not OK!!

 

Technorati Tags: Excel Services,PowerPivot,SharePoint 2013,Analysis Services,SQL Server 2012 SP1