Overview
If you do not already have SQL Server 2012 PowerPivot and SharePoint 2010 installed please check out one of the earlier blogs in this series. All of the blogs in the series are listed below. If you did not follow the SharePoint 2010 Installation for SQL Server 2012 blog then your PowerPivot configuration might vary slightly from what I shown below.
Installation Order:
- SQL Server 2012 (RC0) Business Intelligence Configuration
- SQL Server 2012 (RC0) Installation and Configuration
- SharePoint 2010 Installation for SQL Server 2012 (RC0)
- Additional SSAS Instance Installations in SQL Server 2012 (RC0)
- SQL Server 2012 (RC0) PowerPivot for SharePoint 2010 Configuration
- SQL Server 2012 (RC0) SSRS Configuration in SharePoint Integrated Mode (including Power View) (Coming Soon)
- SSRS Alerting (Coming Soon)
- MDS (Coming Soon)
To me, one of the best features of SQL Server 2012 PowerPivot is that it can easily (and from my experience consistently) also configure SharePoint 2010. Granted, I am doing this on a new server, but so far I have had very little issues when following the steps described below.
Note: You must have SharePoint 2010 SP1 installed to successfully perform all of the following steps.
Step-by-Step Instructions
When we installed SSAS in PowerPivot for SharePoint earlier in the series, we also installed the PowerPivot Configuration Tool. This configuration tool can be found in the Start Menu, then Microsoft SQL Server 2012, and finally the Configuration Tools folder.
When the configuration tool opens we only have one option since we have not configured PowerPivot for SharePoint on this server. I would like to point out that if you do have issues with your installation you can always come back to this configuration tool and Repair or Remove a botched installation. Additionally, if you already have a previous version of PowerPivot you can try to upgrade the Features, Services, Applications, and Solutions of an existing SharePoint 2010 farm. I have not tried this yet but would be glad to hear how it has worked for you.
If you click OK without having insalled SharePoint 2010 SP1 the configuration tool will not let you proceed any further. Reference my installation of SharePoint 2010 earlier in the series for how to install SP1 if you have not done so already.
After selecting to Configure or Repair PowerPivot for SharePoint we are taken to a screen that lists all of the tasks completed by the configuration tool. This is where all the magic happens. If you read through all of the configuration tasks it is actually quite incredible what this configuration tool does. It does everything from configuring a new farm (since PowerPivot requires a SharePoint farm), deploys all the PowerPivot integration, starts the Claim to Token Service, and even configures Secure Store and Excel Services for us! Pretty cool right? If your server is configured properly with all the prerequisites you will be able to successfully validate before running the configuration tool.
Note: Do not forget the Passphrase that you choose since this is used in the SP2010 setup. If you lose this, you will have to reinstall SharePoint later down the road or will be unable to completely configure the installation. Also, if anything fails you can hop over to the Output tab for more information. For example, I got an error due to the fact that a SharePoint site already existed in SSIS with the same name. This was from a previous installation on the server. I went into IIS, deleted the old site, validated again and everything worked fine.
Another error you could get during the validation is that the Analysis Services password is incorrect. If this is the case, use the left menu to click on the menu item for registering SSAS and type in the password as shown below. Once validated, run the configuration wizard to completion.
The next couple steps may not be needed, but I have found that if they are completed they can go a long way towards avoiding hours of troubleshooting some initial errors that PowerPivot and the PowerPivot Admin site will throw. Go to the new Central Admin site and in System Settings click on Manage services on server.
After this ‘Stop’ and ‘Start’ the ‘Claims to Windows Token Service’ and then run an IIS Reset and return to Central Admin. Until I did this, the PowerPivot administration screen would throw errors when I tried to view it. Additionally, when I tried to refresh PowerPivot workbooks I would get errors as well.
To view the PowerPivot management screen click on Application Management in Central Admin, then Manage service applications, and then Default PowerPivot Service Application. This step validates that the PowerPivot Admin page is up and running. You probably won’t have much data to look at, but as long as no errors are thrown then you should be good to go.
At this point, you will also need to confirm that PowerPivot workbooks can be viewed and refreshed in SharePoint. To do this, you will obviously need a PowerPivot workbook! Creating a PowerPivot workbook is beyond the scope of this blog, but in order to do it you will need the PowerPivot Add-in for Excel. This is a free download and the SQL Server 2012 (RC0) version can be found here. Once I had this installed I created the PowerPivot document shown below.
Lastly, to validate the installation, navigate to your new SharePoint homepage. Click on the PowerPivot Gallery library and upload the newly created PowerPivot document. You might need to let the timer job run or refresh the browser in order for the gallery to update the thumbnails of each tab within the Excel document. If everything is working, you should see a view similar to the one below.
Click on the sheet you wish to view to confirm that the document can load in the browser. Click on a couple of the slicers to validate that the document is able to refresh the data. If so, your installation is successful!
