Installation PowerPivot for SharePoint with SQL 2008 R2 SP1

 

 

· Log in met Farm account op de SharePoint application server

· Mount de SQL 2008 R2 Enterprise Edition

· Start de Setup (Run as Administrator)

· Doorloop de eerste stappen

·

· Next

·

· Kies SQL Server PowerPivot for SharePoint – Existing Farm

· Next

·

· Opties zijn “ greyed out” en kunnen dus niet worden gewijzigd

· Next

·

· Next

·

· Instance ID kan aangepast worden

· Next

·

· Next

·

· Kies het aangemaakte SQL Service account for Analysis; svcWEB-AnalysisSP en voer het bijbehorende wachtwoord in

·

· Gecontroleerd op andere SQL server; daar staat Latin1_General_CP1_CI_AS maar die zie ik hier niet tussen staan

·

· Add Current User

·

· Wellicht andere Data- , Log-, TEMP- en Backup directories

· Next

·

· Next

·

· Next

·

· LET OP!!!!!!!!!!!!!!!!!!!!!!!

· HACK #1

· At the bottom of the window, Setup is displaying the path to the “ConfigurationFile.ini” file. Copy the path to the file. We have to pause the install here because the location of this .ini file changes on every install as Setup will take the current date/time stamp and use it for the …Log\<YYYYMMDD_HHMMDD>\… part of the folder location

·

· Open the “ConfigurationFile.ini” file with Notepad

· Using Ctrl+F, locate the reference to “FARMADMINPORT”.

·

· FARMADMINPORT = “ 28290”

· Your port number will show something random. This is because PowerPivot assumes that its installing at the same time as a default SharePoint install. Since we already have SharePoint installed, we need to edit this value and change it to match the port of our Central Admin location.

· Change the value between the quotes to match the port number of your current Central Admin. Our Central Admin Port = 55555

·

· Exit and save the .ini file

· HACK #2

· The other problem with the current PowerPivot install is that it looks for the Microsoft.AnalaysisServices.SharePoint.Integration.dll file in the bootstrap folder, but it doesn’t exist there. In past failed installs of PowerPivot, you may have encountered references to this DLL being “missing”.

· Copy Microsoft.AnalysisServices.SharePoint.Integration.dll to the PowerPivot bootstrap location of C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\x64

· HACK #3

· Now we have to ensure that there are no remnants of this dll left over within the GAC. Open Windows Explorer.

· Browse to “C:\Windows\assembly”.

· Scroll down and look for Microsoft.AnalysisServices.SharePoint.Integration.

· You should not find it because it’s not installed yet. This was the case in my install as can be seen below.

·

· That’s not necessarily true though as can be seen from my environment screen shot below:

·

· Open a command shell via Start/Run/cmd

· Execute “cd\windows\assembly\gac_msil” from the command line

· Execute “dir microsoft.analysisservices.s*” from the command line

· Your output SHOULD show 0 files and 0 dirs. If it doesn’t, like in my example above, the odds are you’ve had a failed install of AnalysisServices before and remnants were left behind when the installer didn’t cleanup behind itself.

· If there is a folder named “Microsoft.AnalysisServices.SharePoint.Integration”, you need to delete it by executing “rd Microsoft.AnalysisServices.SharePoint.Integration” from the command line.

· Now that we’ve cleared the path for the installer by deleting remnants from the GAC, hacking the configuration file and manually providing a copy of the integration DLL, we can switch back to Setup and click “Install”.

· Setup should complete successfully this time around as in my example below:

·

· Close

·

· OK

·

· OK

Herstart de server om de installatie af te ronden

Log in op Central Admin

Ga naar System Settings

Manage Farm Solutions

Kies powerpivotwebapp.wsp

Kies Deploy Solution

Kies de juiste Web Application

OK

A PowerPivot for SharePoint deployment requires that your farm include the following services: Excel Calculation Services, Secure Store Service, and Claims to Windows token service.

Both Excel Services and PowerPivot for SharePoint use the Claims to Windows Token Service to establish connections to external data sources using the Windows identity of the current SharePoint user. If the service is not already started, you must start it now to enable Excel Calculation Services to forward authenticated requests to the PowerPivot System Service.

1. In Central Administration, in System Settings, click Manage services on server.
2. Start the Claims to Windows Token Service.
3. Start Excel Calculation Services.
4. Start Secure Store Service.
5. Verify that both SQL Server Analysis Services and SQL Server PowerPivot System Service are started

Create a PowerPivot Service Application

1. In Central Administration, in Application Management, click Manage service applications.
2. In the Service Applications ribbon, click New.
3. Select SQL Server PowerPivot Service Application. If it does not appear in the list, PowerPivot for SharePoint is not installed or the solution is not deployed.
4. In the Create New PowerPivot Service Application page, enter a name for the application. The default is PowerPivotServiceApplication<number>. If you are creating multiple PowerPivot service applications, a descriptive name will help other administrators understand how the application is used.
5. In Application Pool, create a new application pool and select a security account for it. A domain user account is required.
6. In Database Server, choose a database server on which to create the service application database. The default value is the SQL Server Database Engine instance that hosts the farm configuration databases.
7. In Database Name, the default value is PowerPivotServiceApplication1_<guid>. The default database name corresponds to the default name of the service application. If you entered a unique service application name, follow a similar naming convention for your database name so that you can manage them together.
8. In Database Authentication, the default is Windows Authentication. If you choose SQL Authentication, refer to the SharePoint administrator guide for best practices on how to use this authentication type in a SharePoint deployment.
9. Select the checkbox for Add the proxy for this PowerPivot service application to the default proxy group. This adds the service application connection to the default service connection group. You must have at least one PowerPivot service application in the default connection group.

If a PowerPivot service application is already listed in the default connection group, do not add a second service application to that group. Adding two service applications of the same type in the default connection group is not a supported configuration. For more information about how to use additional service applications in a connection group, see Connect a PowerPivot Service Application to a SharePoint Web Application.

10. Click OK. The service will appear alongside other managed services in the farm's service application list

Configure Excel Services

---

In this step, you modify Excel Services configuration to support large file uploads and allow preview image generation in PowerPivot Gallery.

Alternatively, if you want to preserve the Excel Services default settings, you can skip this step for now. Later, after you have created sites on your SharePoint server, you can specify a new trusted location for those sites that support PowerPivot data access, and then configure those sites for large file uploads and preview image generation. For more information, see Create a trusted location for PowerPivot sites.

1. In Central Administration, in Application Management, click Manage service applications.

2. Select Excel Services Application.

3. Click Trusted File Locations and on this page, select your trusted location. (Typically, this is listed as http:// in the Address column.)

4. In the Workbook Properties area, set Maximum Workbook Size to 50.

5. In External Data, verify that Trusted data connection libraries and embedded is selected. This setting is required for PowerPivot data access in a workbook.

6. Clear the Warn on Data Refresh checkbox to allow preview images of individual worksheets in PowerPivot Gallery. If you choose to keep the warning and workbook settings specify refresh on open, you might get a single preview image of the warning instead of the pages in your workbook.

7. Click OK.

Configure Secure Store Service and Create the PowerPivot Unattended Data Refresh Account

---

PowerPivot for SharePoint requires Secure Store Service to store credentials and the unattended execution account for data refresh. Secure Store Service is already enabled for your farm, but further configuration is required to make it available. At a minimum, you must generate a master key. Additionally, you should also create target applications for the PowerPivot unattended data refresh account and any other credentials that need to be stored for subsequent use in data refresh.

1. In Central Administration, in Application Management, click Manage service applications.

2. Select Secure Store Service.

3. In the Service Applications ribbon, in Key Management, click Generate New Key.

4. Enter and then confirm a pass phrase. The pass phrase will be used to add additional secure store shared service applications.

5. Click OK.

For additional instructions on how to enable Secure Store Service and prepare a server for PowerPivot data refresh, see Enable and Configure PowerPivot Data Refresh.

For instructions about how to create the unattended account later or configure other stored credentials used in data refresh, see Configure and Use the PowerPivot Unattended Data Refresh Account and Configure and Use Stored Credentials for PowerPivot Data Refresh.