Before we can configure SharePoint, we will have to install the Analysis Service instance that will host the PowerPivot workbooks used in SharePoint, also known as “the PowerPivot instance”.
The first step is to create a service account for this instance. I named it SP2013BI\SPPowerPivot.
The next step is attaching the SQL Server image to our virtual machine and launching the SQL Server setup. Go through the usual steps of the setup wizard until you need to choose the installation type. Choose the type Perform a new installation of SQL Server 2012 and click Next.
Enter the product key and accept the license terms.
Choose the SQL Server PowerPivot for SharePoint setup role. We already have a SQL Server instance installed, so you can deselect the checkbox for adding another instance. Click Next.
Everything is already selected in the Feature Selection page, so just click Next to go to the next step.
A set of operation rules will run. It will for example check if the PowerPivot instance name has not already been taken. If everything is validated, click Next.
In the Instance Configuration page, leave everything as default and click Next.
Review the disk space requirements and go to the Server Configuration page. Configure the SPPowerPivot account as the service account for our Analysis Services instance. Leave the collation to the default. Click Next.
Add yourself and the SharePoint farm account as administrators. If you have multiple drives, you can optionally move the data and log files to another disk. Click Next twice.
The Installation Rules will run. If every rule is validated, click Next.
Review the installation and click Install if you’re satisfied.
Go grab a beverage of your choice while the SSAS instance is being installed…
Once the installation is finished, click Close to finish the setup.
There is now a SSAS instance called PowerPivot running on the server. When your SharePoint environment is finished and you have PowerPivot workbooks in SharePoint, you get the following list of databases when you log into the instance:
Each PowerPivot workbook is a Tabular Model – with a bunch of gibberish appended to the workbook name - stored in the instance. The interaction is limited though. For example you cannot process the tables.
The last piece is running SQL Server 2012 service pack 1 again. Run the sp1 setup and make sure the PowerPivot instance is selected.
After the installation, finish the setup.
We are now ready to launch the PowerPivot Configuration Wizard, which will be discussed in the following part of the series.