PowerPivot allows you to store massive amounts of data which can then be displayed as a powerful data mashup. By turning your workbooks into shared applications on SharePoint 2010, you gain the collaborative and document management features that are provided out of the box such as versioning and workflows.
The Installation
We begin by installing SQL PowerPivot for SharePoint on our Application Server or dedicated PowerPivot Server with SharePoint running.
Launch the SQL Server Installation Centre and Select Installation on the left navigation pane and then click on New Installation or add features to an existing installation.
Enter the product key at the Product Key screen and click Next.
Accept the License Terms and then click Next.
Click Install to install the Setup Support Files.
The setup support rules are then run. At this stage you should rectify any errors if present.
Click Next.
You will now be greeted with the Setup Role Screen identical to the below. Select SQL Server PowerPivot for SharePoint and select Add PowerPivot for SharePoint to Existing Farm.
Click Next.
The next screen will automatically select the Analysis Services for SharePoint Integration feature.
Click Next.
The installation rules are run. If you receive any failures, you will need to address these before you can continue.
Click Next.
You will then be greeted with the Instance Configuration screen in which you will accept the default named instance and Instance ID.
Click Next.
The wizard will then check the Disk Space Requirements and provide you with a green tick to continue.
Click Next.
Here we will use a dedicated domain account for SQL Server Analysis Services. (Please note that the installation wizard will not accept any built in machine accounts. This could be your existing SharePoint SQL account that may already be setup in your environment or a dedicated SharePoint SQL Server Analysis Services Account. This account will be a member of the SQLServerMSASUser$<server>$PowerPivot security group and the WSS_WPG security groups on the local computer. This will occur automatically.
Click Next.
The Analysis Services Configuration screen will then appear in which you will specify and add your Analysis Services administrators. I would add the current user as a minimum and your SQL Server Analysis Services Account. Click on Data Directories and make any necessary drive changes in there. Note that Analysis Services administrators will have unrestricted access to Analysis Services.
Click Next.
The Error Reporting screen appears. Click Next.
The Installation Configuration Rules screen appears. Address any Errors before you proceed.
Click Next.
Review the Summary and click on Install.
The installation will now proceed.
You may receive a warning about restarting your computer at the completion. Please restart your computer to complete the installation process.
In order to check whether the installation was successful, venture into SharePoint Central Administration > System Settings > Manage services on server. The SQL Server Analysis Services and SQL Server PowerPivot System Service should be set to started.
Deploying the PowerPivot Solution Package
Now that we have confirmed that our PowerPivot services are up and running in Central Administration, we will now proceed to deploy the PowerPivot Solution Package.
Launch Central Administration and navigate to System Settings > Farm Management > Manage farm solutions.
You will notice there are two power pivot Solutions listed as below.
You will notice that the powerpivotfarm.wsp is already Globally deployed. This is automatically deployed when you install the first PowerPivot for SharePoint instance in your SharePoint farm. You will not need to deploy this ever again. The powerpivotwebapp.wsp on the other hand is only deployed to SharePoint Central Administration Web Application and you must manually deploy it to each Web Application that will require PowerPivot data access. In order to do so;
Click on powerpivotwebapp.wsp.
Click on Deploy Solution.
Specify your time and select your SharePoint Web Application and then click OK.
If you need to deploy the solution to other SharePoint Web Applications, repeat the above process each time.
We now need to ensure that the necessary pre-requisite services are started. These are Excel Calculation Services, Secure Store Service and Claims to Windows token service.
Launch Central Administration and navigate to System Settings > Manage Services on server. In my instance, the Claims to Windows Token Service and Secure Store Service were stopped. Click on Start and ensure their status is set to started.
Creating the PowerPivot Service Application
We can now proceed and create our first PowerPivot Service Application. Launch Central Administration and navigate to Application Management > Service Applications > Manage service applications.
Click on New and select SQL Server PowerPivot Service Application.
Enter the details or the Service Application Pool as follows;
Name: PowerPivotServiceApplication1
Application Pool: Create new application pool; SharePoint-PowerPivotApplication1
Select your registered managed account. This account must have Analysis Services system administrator permissions on the local Analysis Services service that is installed on the same computer. This was set during the installation of SQL in our earlier steps above.
In my case, I am using the account sp_sql.
Enter your Database Server and enter a Database Name and select Windows Authentication.
Also ensure the that the “Add the proxy for this PowerPivot service application to the default proxy group” is checked.
Click OK.
You should hopefully receive a window stating that the PowerPivot service application has been successfully created.
Click OK.
Activating the PowerPivot Feature Integration for your Site Collections
We now need to activate the PowerPivot Feature activation to make application pages available to your SharePoint sites.
Click on Site Actions from within your SharePoint Site and select Site Settings > Site Collection Administration > Site Collection Features.
Navigate down the page until you reach PowerPivot Feature Integration for Site Collections and click on Activate.
You will need to do this for all other site collections where you would like to run PowerPivot on.
Configuring the Excel Services Application
PowerPivot relies heavily on Excel Services so we need to ensure that our Excel Services Application is configured.
Click OK
Your Excel Services Application should now be listed under Service Applications in Central Administration and Started.
Click on the newly created Excel Services Application and click on Trusted File Locations. This is the area where we need to specify our SharePoint Locations. Please note that PowerPivot cannot access any workbook that is stored outside SharePoint.
Configuring the Secure Store Service Application
We need to ensure that we provision the Secure Store Service Application in order to allow PowerPivot to store its credentials. Essentially we are configuring a PowerPivot unattended account for data refresh to work.
Click OK.
You should receive confirmation that the Secure Store Service Application was successfully created.
The Secure Store Service will now be listed with the other service applications.
We will now need to generate the master key. Click on the Secure Store Service application. This will direct you to a page with the below warning
“Before creating a new Secure Store Target Application, you must first generate a new key for this Secure Store Service Application from the ribbon”
Click on Generate New Key from the Ribbon.
You will be asked to enter a Pass Phrase. You will need to keep this pass phrase in a secure location as it will be required to be entered when adding a new secure store service server or when restoring.
After you have entered and confirmed your pass phrase, click OK.
Configuring the Unattended PowerPivot Data Refresh Account
Now that we have configured the Secure Store Service, we can proceed to configure our PowerPivot unattended data refresh account. This account is used when you schedule PowerPivot data refresh jobs and allows workbook authors to schedule data refreshes without using their own credentials. Because we are setting up PowerPivot on an existing farm, this is a manual step configure all in Central Administration.
Click Next
The Specify the credential fields for your Secure Store Target Application page is displayed as per the below screen capture.
Accept the default values and click Next.
The Target Application Administrators page is displayed next. Here we need to specify the account used for the PowerPivot Service Application Pool. You should also add any other Windows domain account account which requires Full control permissions.
Click OK.
Your Target Application ID should now be listed under the Secure Store Service Application.
Click on the checkbox beside the newly created Target Application ID and click on Set Credentials from the Ribbon interface.
Here we need to specify the Credential Owner first which is the Account we specified right at the start when we ran the Analysis Services Setup. In my instance I had used the account sp_sql.
You will also be required to enter the Windows User Name and password for an account that we specified above under Target Application Administrators.
Click OK
Navigate back to Central Administration > Application Management > Manage Service Applications.
Click on the PowerPivot Service Application. This will now load the PowerPivot Management Dashboard. Under Actions, click on Configure service application settings.
Scroll down until you reach the Data Refresh section and enter the Target Application ID that we created earlier; PowerPivotUnattendedAccount.
We now must grant the contribute permissions to the PowerPivot Unattended data refresh account. This needs to be applied to any PowerPivot workbook for which it will be utilised.
So how do we know if this is all working?! There are a number of steps, but a quick indication that we are pretty much there is to navigate to the PowerPivot Management Dashboard. We do so via Central Administration > General Application Settings > PowerPivot Management Dashboard. We should receive something similar to the below without any errors or warnings.
As you can see from the above there are a number of steps involved but hopefully this guide will provide with a good basis.
The Installation
We begin by installing SQL PowerPivot for SharePoint on our Application Server or dedicated PowerPivot Server with SharePoint running.
Launch the SQL Server Installation Centre and Select Installation on the left navigation pane and then click on New Installation or add features to an existing installation.
Enter the product key at the Product Key screen and click Next.
Accept the License Terms and then click Next.
Click Install to install the Setup Support Files.
The setup support rules are then run. At this stage you should rectify any errors if present.
Click Next.
You will now be greeted with the Setup Role Screen identical to the below. Select SQL Server PowerPivot for SharePoint and select Add PowerPivot for SharePoint to Existing Farm.
Click Next.
The next screen will automatically select the Analysis Services for SharePoint Integration feature.
Click Next.
The installation rules are run. If you receive any failures, you will need to address these before you can continue.
Click Next.
You will then be greeted with the Instance Configuration screen in which you will accept the default named instance and Instance ID.
Click Next.
The wizard will then check the Disk Space Requirements and provide you with a green tick to continue.
Click Next.
Here we will use a dedicated domain account for SQL Server Analysis Services. (Please note that the installation wizard will not accept any built in machine accounts. This could be your existing SharePoint SQL account that may already be setup in your environment or a dedicated SharePoint SQL Server Analysis Services Account. This account will be a member of the SQLServerMSASUser$<server>$PowerPivot security group and the WSS_WPG security groups on the local computer. This will occur automatically.
Click Next.
The Analysis Services Configuration screen will then appear in which you will specify and add your Analysis Services administrators. I would add the current user as a minimum and your SQL Server Analysis Services Account. Click on Data Directories and make any necessary drive changes in there. Note that Analysis Services administrators will have unrestricted access to Analysis Services.
Click Next.
The Error Reporting screen appears. Click Next.
The Installation Configuration Rules screen appears. Address any Errors before you proceed.
Click Next.
Review the Summary and click on Install.
The installation will now proceed.
You may receive a warning about restarting your computer at the completion. Please restart your computer to complete the installation process.
In order to check whether the installation was successful, venture into SharePoint Central Administration > System Settings > Manage services on server. The SQL Server Analysis Services and SQL Server PowerPivot System Service should be set to started.
Deploying the PowerPivot Solution Package
Now that we have confirmed that our PowerPivot services are up and running in Central Administration, we will now proceed to deploy the PowerPivot Solution Package.
Launch Central Administration and navigate to System Settings > Farm Management > Manage farm solutions.
You will notice there are two power pivot Solutions listed as below.
You will notice that the powerpivotfarm.wsp is already Globally deployed. This is automatically deployed when you install the first PowerPivot for SharePoint instance in your SharePoint farm. You will not need to deploy this ever again. The powerpivotwebapp.wsp on the other hand is only deployed to SharePoint Central Administration Web Application and you must manually deploy it to each Web Application that will require PowerPivot data access. In order to do so;
Click on powerpivotwebapp.wsp.
Click on Deploy Solution.
Specify your time and select your SharePoint Web Application and then click OK.
If you need to deploy the solution to other SharePoint Web Applications, repeat the above process each time.
We now need to ensure that the necessary pre-requisite services are started. These are Excel Calculation Services, Secure Store Service and Claims to Windows token service.
Launch Central Administration and navigate to System Settings > Manage Services on server. In my instance, the Claims to Windows Token Service and Secure Store Service were stopped. Click on Start and ensure their status is set to started.
Creating the PowerPivot Service Application
We can now proceed and create our first PowerPivot Service Application. Launch Central Administration and navigate to Application Management > Service Applications > Manage service applications.
Click on New and select SQL Server PowerPivot Service Application.
Enter the details or the Service Application Pool as follows;
Name: PowerPivotServiceApplication1
Application Pool: Create new application pool; SharePoint-PowerPivotApplication1
Select your registered managed account. This account must have Analysis Services system administrator permissions on the local Analysis Services service that is installed on the same computer. This was set during the installation of SQL in our earlier steps above.
In my case, I am using the account sp_sql.
Enter your Database Server and enter a Database Name and select Windows Authentication.
Also ensure the that the “Add the proxy for this PowerPivot service application to the default proxy group” is checked.
Click OK.
You should hopefully receive a window stating that the PowerPivot service application has been successfully created.
Click OK.
Activating the PowerPivot Feature Integration for your Site Collections
We now need to activate the PowerPivot Feature activation to make application pages available to your SharePoint sites.
Click on Site Actions from within your SharePoint Site and select Site Settings > Site Collection Administration > Site Collection Features.
Navigate down the page until you reach PowerPivot Feature Integration for Site Collections and click on Activate.
You will need to do this for all other site collections where you would like to run PowerPivot on.
Configuring the Excel Services Application
PowerPivot relies heavily on Excel Services so we need to ensure that our Excel Services Application is configured.
- Launch Central Administration and navigate to Application Management > Manage Service Applications.
- Click New and select Excel Services Application
- Enter a Name, and Create a new application pool as per the below screen capture
- Ensure Add to default proxy list is checked.
Click OK
Your Excel Services Application should now be listed under Service Applications in Central Administration and Started.
Click on the newly created Excel Services Application and click on Trusted File Locations. This is the area where we need to specify our SharePoint Locations. Please note that PowerPivot cannot access any workbook that is stored outside SharePoint.
- Click on the existing http entry and make any modifications such as increasing the Maximum Workbook size.
- Under External Data, select Trusted data connection libraries and embedded for the PowerPivot access.
- Deselect the Warn on Refresh check box.
Configuring the Secure Store Service Application
We need to ensure that we provision the Secure Store Service Application in order to allow PowerPivot to store its credentials. Essentially we are configuring a PowerPivot unattended account for data refresh to work.
- Launch Central Administration and navigate to Application Management > Manage Service Applications.
- Click New and select Secure Store Service
- Enter a name for the service application, specify your database settings and create a new application pool similar to the below screen captures
Click OK.
You should receive confirmation that the Secure Store Service Application was successfully created.
The Secure Store Service will now be listed with the other service applications.
We will now need to generate the master key. Click on the Secure Store Service application. This will direct you to a page with the below warning
“Before creating a new Secure Store Target Application, you must first generate a new key for this Secure Store Service Application from the ribbon”
Click on Generate New Key from the Ribbon.
You will be asked to enter a Pass Phrase. You will need to keep this pass phrase in a secure location as it will be required to be entered when adding a new secure store service server or when restoring.
After you have entered and confirmed your pass phrase, click OK.
Configuring the Unattended PowerPivot Data Refresh Account
Now that we have configured the Secure Store Service, we can proceed to configure our PowerPivot unattended data refresh account. This account is used when you schedule PowerPivot data refresh jobs and allows workbook authors to schedule data refreshes without using their own credentials. Because we are setting up PowerPivot on an existing farm, this is a manual step configure all in Central Administration.
- Launch Central Administration and navigate to Application Management > Service Applications > Manage Service Applications and select the Secure Store Service Application that we created earlier.
- Click New which will bring us to the Target Application Settings Window.
- Enter a name for your Target Application ID such as PowerPivotUnattendedAccount and a Display Name and Contact Email Address.
- Leave Target Application Type set to Individual and select Use default Page
Click Next
The Specify the credential fields for your Secure Store Target Application page is displayed as per the below screen capture.
Accept the default values and click Next.
The Target Application Administrators page is displayed next. Here we need to specify the account used for the PowerPivot Service Application Pool. You should also add any other Windows domain account account which requires Full control permissions.
Click OK.
Your Target Application ID should now be listed under the Secure Store Service Application.
Click on the checkbox beside the newly created Target Application ID and click on Set Credentials from the Ribbon interface.
Here we need to specify the Credential Owner first which is the Account we specified right at the start when we ran the Analysis Services Setup. In my instance I had used the account sp_sql.
You will also be required to enter the Windows User Name and password for an account that we specified above under Target Application Administrators.
Click OK
Navigate back to Central Administration > Application Management > Manage Service Applications.
Click on the PowerPivot Service Application. This will now load the PowerPivot Management Dashboard. Under Actions, click on Configure service application settings.
Scroll down until you reach the Data Refresh section and enter the Target Application ID that we created earlier; PowerPivotUnattendedAccount.
We now must grant the contribute permissions to the PowerPivot Unattended data refresh account. This needs to be applied to any PowerPivot workbook for which it will be utilised.
So how do we know if this is all working?! There are a number of steps, but a quick indication that we are pretty much there is to navigate to the PowerPivot Management Dashboard. We do so via Central Administration > General Application Settings > PowerPivot Management Dashboard. We should receive something similar to the below without any errors or warnings.
As you can see from the above there are a number of steps involved but hopefully this guide will provide with a good basis.
No comments:
Post a Comment