Configuring PowerPivot version 1 was a significant task that caused many of us to lose large amounts of hair, sleep, and sanity. Happily, PowerPivot version 2 has made amends for these transgressions and provides us with two primary methods for configuration: PowerShell and a wizard-driven experience. Even the most seasoned veteran of the PowerShell gurus among us acknowledge the beauty of this wizard-driven experience partly because it provides us with the exact PowerShell code that it will run for us. We get the opportunity to utilize the wizard to write this code rather than trying to come up with it on our own.
The requirements for running the PowerPivot configuration are:
The computer must have PowerPivot for SharePoint installed according to the guidance given in Chapter 35.
The SharePoint server hosting the PowerPivot service application must be running SharePoint 2010 SP1 with the August 2010 (or later) cumulative update installed.
The account performing the upgrade must be a farm administrator, have db_owner permissions on the Analysis Services instance, and be a local administrator to execute the upgrade of a PowerPivot for SharePoint installation.
You must have domain user accounts for all services that will be provisioned during configuration.
You can start with using a single default account, and then change the service accounts later using Central Administration.
There must be at least one SharePoint web application running under classic mode authentication.
You can deploy this during configuration of PowerPivot; however, port 80 must be available.
Using the configuration tool, if you have met the prerequisites stated here, you will be greeted with the PowerPivot Configuration Tool screen that has only one option available for selection, Configure or Repair PowerPivot for SharePoint (see Figure 36-1).
After a quick verification step, you will come to the main configuration screen. Provide the default account username and password, the database server that will host the backing PowerPivot Service database, and the SharePoint farm passphrase (see Figure 36-2).
You can then provide the password for the service account that you are using for the SQL Server Analysis Services instance for PowerPivot.
Next, you need to name your PowerPivot service application and database. By default, the database will include a GUID, but you can and should remove this from the name for ease of management (not to mention that your DBAs will love you for it).
Now you can select the web application to which you want to deploy PowerPivot initially. You will be able to deploy PowerPivot to additional web applications after the initial configuration is complete, however you can only select one in this wizard. The other option that is being offered in this screen is to set a Maximum File Size. The default is 2047 MB, which we recommend leaving as is (see Figure 36-4). The reason not to change this setting is that SharePoint respects the principle of least privilege and will take the lowest setting, which you should have set at the web application level. By leaving this option alone, you are allowing yourself the ability to make changes to this setting in only one place, the Web Application General Settings.
Next, you can select the site that you want to activate PowerPivot on initially (see Figure 36-5). Just like the web application setting, you can only select one through this wizard, but can deploy to additional sites after the initial configuration is complete.
For the purposes of this configuration, we are assuming that you have already configured your Secure Store Service and when creating the Unattended Account for Data Refresh that you will not need to set this up as well. If you have not set up your Secure Store Service, this wizard can assist with that.
For the Unattended Account for Data Refresh, you need to give a Target Application ID and Friendly Name for Target Application. These can be anything that you want; however, we recommend that you use something that will be easily identifiable inside the system for troubleshooting purposes. You can then enter the service account that you want to use for the Unattended Data Refresh and give the password. This will be stored in the Store (see Figure 36-6).
Once you have entered all of the required information, click Validate. If you entered all of the required information properly, you will get the confirmation shown in Figure 36-7.
Once you have confirmation that the configuration is valid, you should click on the second tab in the right-hand side of the configuration tool and examine the PowerShell script that was generated for you, as shown in Figure 36-8. We recommended that you store a copy of this configuration with your disaster recovery plan and SharePoint build documentation so that you can repeat the process in the event of a disaster.
Now you’ll notice that the Run button is activated, as shown in Figure 36-9. It is time to run the Configuration Tool.
The next set of operations is a walkthrough of the actual configuration of the 16 steps to configure PowerPivot. Once these have completed successfully, you will get a confirmation on the screen (see Figure 36-10).
Now that you have successfully completion of the PowerPivot instance configuration, you are ready to configure the PowerPivot service application inside of SharePoint in the next chapter.
In this chapter, we covered the configuration process for enabling PowerPivot in your SharePoint environment. We gave a detailed accounting of the required prerequisites that must be done before attempting the configuration. We also gave a complete walkthrough of configuring the settings as required by the installation process.