One question that is frequently raised around the installation of PowerPivot under SQL 2012 is “What do I do if I have PowerPivot under SQL 2008 R2?” There is an important distinction that needs to be made here regarding versions. PowerPivot is a single-tier upgrade path only. This means that you cannot go from version 1 (SharePoint 2010 and 2008 R2) to version 3 (SharePoint 2013 and SQL 2012 SP1) without upgrading to version 2 (SharePoint 2010 and SQL 2012) first.

To upgrade from PowerPivot version 1 to PowerPivot version 2, the following must be true:

After the prerequisites have been met, run SQL Server 2012 setup on the application server that runs SQL Server Analysis Services (PowerPivot). Once the upgrade of the engine is complete, use either PowerShell or the configuration tool for PowerPivot version 2 to perform the upgrade of the service application. These steps should be done on all servers running the PowerPivot service application in the farm.

You can validate that the upgrade was completed properly to the solution and service by checking the version of Microsoft.AnalysisServices.SharePoint.Integration.dll in the Global Assembly Cache (GAC), which is located at %SystemDrive%WindowsAssembly (see Figure 35-1). Check the properties of the file to validate that it is at minimum version11.00.x. There are multiple copies of this DLL on a server running PowerPivot. Copies of the DLL will be found in %SystemDrive%inetpubwwwrootwssVirtualDirectories\%webapplication%in and %SystemDrive%Program Files Microsoft SQL Server100SDKAssemblies because powerpivotwebapp.wsp and SQL setup (if you install connectivity components) add them where they are needed. Depending on where in the installation process you are, the GAC DLL and these DLLs may be different version numbers as long as they are all version 11.x or higher (see Figure 35-2).

Figure 35-1. Validating the installation in the Global Assembly Cache

Figure 35-2. Validating the installation in the bin and Assemblies folders

Another key validation point is to verify that the new MSOLAP Data Provider Version has been added to the Excel Services Trusted Data Providers. To do this, go to Central Administration → Application Management → Manage Service Applications and choose your Excel service application. Under Trusted Data Providers, you should see MSOLAP.5 in the list, as shown in Figure 35-3.

Figure 35-3. Validating the MSOLAP version in Central Administration

Installation of SQL Server 2012 starts with first understanding what and where to do the installation. In order to support PowerPivot SQL Server 2012, it must be installed on the application tier server that will host the PowerPivot service application. The installation is done from the SQL Server installation media and should only be performed after you have patched your system to the most up-to-date patch level you can.

To install PowerPivot version 2, the following must be true:

Once in the SQL Server setup, select the SQL Server PowerPivot for SharePoint installation option, as shown in Figure 35-4.

Figure 35-4. SQL Server PowerPivot for SharePoint install

Next, continue with the default options as they cannot be changed.

Even though you are given the option to change the Instance ID name of the PowerPivot instance as seen in Figure 35-5, you must not do so. If you name the instance anything other than PowerPivot, you will have issues as Microsoft has programmed PowerPivot in such a way that it needs this specific named instance.

Figure 35-5. Analysis Services instance naming of PowerPivot

The next screen, shown in Figure 35-6, has the options preselected for you and does not give you the ability to make any changes.

Figure 35-6. Features locked in for PowerPivot installation

Now, you will be prompted to grant administrator permissions to the SQL Server Analysis Services that you are about to install (see Figure 35-7). It is recommended that you grant administrator permissions to the account that you are running the install with, as well as an Active Directory Security Group to which you can add users later.

Figure 35-7. Adding administrators to PowerPivot instance

Next you will be prompted to give the service account and password that will run the PowerPivot SQL Service. This needs to be a separate account that doesn’t run anything else. Special permissions will be granted to this account during configuration and the account should be kept separate for this reason. See Figure 35-8.

Figure 35-8. Assigning the service account to run Analysis Services

Lastly, you will be given an opportunity to review the configuration.ini to validate the configuration information, as shown in Figure 35-9. Unlike during the PowerPivot version 1 installation, there are no real tricks here (the PowerPivot version 1 installation tried to install Central Administration on a randomly selected port number and was a terribly painful process). We mention it here for informational purposes, not because action is required.

Figure 35-9. Review installation configuration for PowerPivot instance

Once the SQL Server configuration is complete, PowerPivot is ready to be configured. However, at this point you should stop and complete the SQL Server Reporting Services Integrated Mode bits as well. To do this, you will need to rerun the SQL Server setup and it is our recommendation that you add these features to the PowerPivot instance of SQL Server (see Figure 35-10).

Figure 35-10. Selecting the PowerPivot instance

Select the features that you want to install to support SQL Server Reporting Services Integrated Mode (see Figure 35-11). If your installation is like most and is running on a server that contains the Web Server role, at this time you should also install the SQL Server Reporting Services Add-in for SharePoint Products, which is required on all web servers in the farm to support Power View. At this point, we highly recommend installing the SQL Server Data Tools, which will assist you in managing your SQL environment. Additionally, we also recommend installing the other features you can see in Figure 35-11 to best set yourself up for success.

Figure 35-11. Adding features to PowerPivot instance for Reporting Services

If you have opted to install the SQL Server Integration Services option, you will be prompted to enter the credentials for the account that will run these services (see Figure 35-12). This should be a separate account that doesn’t run anything else. Special permissions will be granted to this account during configuration, which is why it needs to be kept separate.

Figure 35-12. Assigning the service account to run Reporting Services

Unlike previous versions of SQL Server Reporting Services, there is no option for configuring the service at this time. It is an install only option because SQL Server Reporting Services is now a service application inside of SharePoint 2010. See Figure 35-13.

Figure 35-13. Setting to Install only for Reporting Services

Upon successful completion of this installation, you are now ready to begin the configuration of the SharePoint BI tools, starting with the PowerPivot instance in the next chapter.