In this blog post we go over one of the most important parts of the series: running the PowerPivot Configuration Wizard in order to configure our SharePoint installation. If you followed the previous walkthroughs, you can find the wizard in the start menu. It is not necessary to run the wizard, you can do everything yourself using the standard SharePoint configuration wizards. However, using the Configuration Wizard will install some extra components, such as scheduled data refresh, data providers and very important: the PowerPivot Management Dashboard. Read the following MSDN page for more info.
If you get the following message when launching the wizard, it means you forgot to apply SQL Server 2012 service pack 1 to the PowerPivot instance (like I first did if you’re wondering how I made the screenshot). So hurry back to part 6 of the series and apply that service pack!
If the wizard does launch successfully, you have the choice to configure or repair the PowerPivot for SharePoint installation, or to remove or upgrade features, services, applications and solutions. Since it is the first time we are running the wizard, you can only configure. Click OK.
The wizard consists of a list of sections. In some sections you need to supply parameters, such as passwords or service accounts, in other sections you are not required to take action (indicated with green flags). If you have already setup a farm and configured some services, the wizard will detect this and will automatically fill in the necessary values. In our case we are dealing with a blank server. In each section you can also inspect the PowerShell script that eventually will do the actual configuration. It might come in handy if you want to do a scripted deployment in the future. Since the wizard executes the PowerShell statements behind the scenes, it is possible to configure SharePoint without the use of a domain controller. I leave it up to you to decide if you want to include a domain controller or not.
I’ll go over each section where input is needed, but a lot of parameters might have already default values specified.
The first one is Configure New Farm. Here we configure the database server, the farm account (see part 5) and the passphrase. As you can see, I used a local account for the farm account, not a domain account. In a simple demo environment like this it helps if you specify the same password/passphrase for everything. It makes your demos go a lot smoother! (The usual disclaimer: do not do this in production). Finally specify an easy to remember port for the SharePoint Central Administration site.
Next up is Create PowerPivot Service Application. You need to specify a name for the service application, the database server and the name for the database. Some sections, like this one, are optional. If you do not want those configured, you can deselect the checkbox next to Include this action in the task list.
Create Default Web Application follows in the list. Specify a name for the web application, an URL, the database server and a name for the database. As with most service accounts, I specified the farm account.
Check if the values specified at Deploy Web Application Solution satisfy your requirements.
You can create a site collection in Create Site Collection (they tend to keep things logical). Specify an URL, an administrator – the email address can be fake – and the title for the site.
If you want to add the PowerPivot features to the site, such as the carrousel in the PowerPivot library, select the checkbox in Activate PowerPivot Feature in a Site Collection.
Next we are going to configure the Secure Store Service, which is important if you want to work with unattended accounts. Specify a name for the Secure Store Service service application (what an alliteration) and the database server in Create Secure Store Service Application.
Also configure the Secure Store Service proxy in Create Secure Store Service Application Proxy (the defaults are fine).
And specify a passphrase in Update Secure Store Service Master Key.
Finally specify an unattended account for the data refresh in Create Unattended Account for Data Refresh. Configure the target application ID, a friendly name if you must, an account and the site URL.
Excel Services is next in the list. Specify a name for the service application in Create Excel Service Application.
Add the SSAS PowerPivot instance we installed in part 6 to the Registered PowerPivot Servers in Configure PowerPivot Servers.
Verify the Service Application Name in Register PowerPivot Addin as Excel Services Usage Tracker.
You get an overview of the most important information at the top, in Configure or Repair PowerPivot for SharePoint.
Click Validate at the bottom of the wizard to validate all your specifications. If everything is OK, all items get a green flag.
Click on Run to start the configuration of the SharePoint environment.
Once the configuration is done, you can go to the Central Administration site or your very own PowerPivot site to check if everything works. At first the sites might load a bit slowly, but once everything is in the cache it should go quicker.
In the next walkthrough, we’ll go deeper into verifying if everything behaves as expected.