SharePoint By Bugday

This is a series of posts about the Business Intelligence features of SharePoint 2010

Read about the previous posts in this series:

1. How to: Load data into SharePoint 2010 using SQL Server Integration Services

2. How to: Integrate SharePoint 2010 and Reporting Services 2008 R2

Microsoft SQL Server PowerPivot for SharePoint extends SharePoint 2010 and Excel Services to add server-side processing, collaboration, and document management support for the PowerPivot workbooks that you publish to SharePoint.

Powerpivot can be installed on an existing SharePoint Farm or in a new Farm.

Be sure not to run the SharePoint Configuration Wizard if you install SharePoint on a new farm before you run the PowerPivot installation!

For this demo, we are installing PowerPivot on an existing SharePoint Farm.

1. Start by inserting the SQL 2008 R2 media on one of your application roles(can be any role in farm)

2. Click “Installation”

3. Click “New installation or add features to an exising installation”

4. Click next OK “Setup Support Rules”

5. Click INSTALL on “Setup Support Rules”

6. Click Next on “Setup Support Rules”

7. Click Next on “Installation Type”

8. Click Next on “Product Key”

9. Select the “I accept the license terms” and click Next

10. On Setup Role, Click “SQL Server PowerPivot for SharePoint”

11. In the drop down choose “Existing Farm”, and choose Next

12. In Feature Selection, Choose Next

13. In Installation Rules, click Next

14. In Instance Configuration, click Next

15. In Disk Space Requirements, verify you have sufficient disk capacity to install the feature, and then click Next.

16. In Server Configuration, specify a domain user account for SQL Server Analysis Services(a dedicated domain account!), click Next

17. In Analysis Services Configuration, click Add Current User. Click Next

18. Click Next on each of the remaining pages until you get to the Ready to Install page

19. Click Install

After installation has completed, we need to deploy the PowerPivot Solution Package in the Farm:

Deploying a PowerPivot solution to SharePoint web application is required for server configuration.

Start the Services on the Server

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.

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 Excel Services Trusted Location

1. In Central Administration, in Application Management, click Manage service applications.
2. Click Excel Services Application
3. Click Trusted File Locations
4. For Address, type your URL
5. Check the “Children Trusted” checkbox
   
6. For maximum Workbook size, set it to 2000
7. For Maximum Chart or Image Size, set it to 100
   
8. Click Ok

Enable Secure Store Service and Configure Data Refresh

Part 1:

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

2. In the Service Applications ribbon, in Create, click New.

3. Select Secure Store Service.

4. In the Create Secure Store Application page, enter a name for the application.

5. In Database, specify the SQL Server instance that will host the database for this service application.

6. In Database Name, enter the name of the service application database. The default value is Secure_Store_Service_DB_<guid>.

7. In Database Authentication, the default is Windows Authentication.

8. In Application Pool, select Create new application pool.

9. Select a security account for the application pool. Specify a managed account to use a domain user account.

10. Accept the remaining default values, and then click OK. The service application will appear alongside other managed services in the farm's service application list.

Part 2

1. Click the Secure Store Service application from the list.

2. In the Service Applications ribbon, click Manage.

3. 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.

Part 3:

Creating an unattended data refresh account for PowerPivot data access is often required for external data access during data refresh. For example, if Kerberos is not enabled, you must create an unattended account that the PowerPivot service can use to connect to external data sources

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.
   

4. In the Create New PowerPivot Service Application page, enter a name for the application. The default is PowerPivotServiceApplication<number>.

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.

7. In Database Name, the default value is PowerPivotServiceApplication1_<guid>.

8. In Database Authentication, the default is Windows Authentication.

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 of the default connection group is not a supported configuration. For more information about how to use additional service applications in a connection group, see How to: 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 PowerPivot Dashboard

1. In Central Administration, click General Application Settings
2. Under PowerPivot, Click “Manage Dashboard”
3. Under Actions, “Click configure Usage Logging”
   
4. Ensure that the following are selected:
1. Enable usage data collection
2. Enable health data collection
5. Click Ok

Enabel PowerPivot on a Site Collection

1. On your site collection, Click on Site Actions and then choose Site Settings
2. Click Site Collection Features
   
3. Ensure that “Powerpivot Feature Integration for Site Collection” is Active(otherwise, Choose Activate)

Create a PowerPivot Gallery

1. Click All Site Content
2. Click Create
3. Choose Library
4. Click PowerPivot Gallery
   
5. For name, type “My PowerPivot Gallery”
6. Click Create

You have now successfully installed and configured PowerPivot for SharePoint 2010