Step-by-Step Guide to Installing SharePoint with SQL 2012 PowerPivot, PowerView, and Reporting Services

By Danielle Arad - May 22, 2012

We get a lot of requests for templates that include BI solutions. Recently, we published a new template containing SharePoint 2010 Enterprise SP1, SQL Server 2012 Business Intelligence Edition with PowerPivot, PowerView, SSRS (Native Mode and SharePoint Mode), SSAS (Multidimensional and Data Mining Mode, PowerPivot for SharePoint, Tabular Mode) and PowerPivot features for Excel 2010.

* Editor’s warning: This post is long, but worth it!

The installation of this server is not a traditional SharePoint Server installation. It can be a bit complicated and quite confusing. I have decided to make your life a bit easier by writing this post with a step-by-step guide to installing SharePoint Server 2010 With SQL 2012 PowerPivot, PowerView and Reporting Services. I would like to note that this server is good for Development/Test environments. In a production environment it is typically not best practice to install all of those items on a stand-alone server.

If you want to test it, I created a Pre-Configured Environment here with the below virtual machine

Ok, Let’s begin!

1. Our first step will be installing and configuring a Domain Controller on our Windows Server 2008 R2 Server.

2. Before we start the installation process we need to create user accounts for the SQL, PowerPivot and SharePoint Services. Navigate to the Active Directory Users and Computers. Click on Start -> Administrative tools -> Active Directory users and computers.

3. Navigate to your Domain -> Users -> New -> User. Create the following user accounts:

    • SQLSvc (SQL Server database engine account)
    • SPAdmin (SharePoint Admin Account)
    • SPFarm (SharePoint Farm account)
    • SSAS (SQL Server Analysis Services Account)
    • SSRS (SQL Server Reporting Services Account)

 

 

 

 

 

 

 

It’s best practice to create an account for each SQL and SharePoint service. The Service accounts should be added to the “Administrators” group as local administrators on the server and as Domain Administrators.

4. Our next step will be installing SharePoint 2010 Enterprise Edition. Before starting the installation of SharePoint, I recommend reading the following article about hardware and software requirements.

5. Install SharePoint Server 2010 Software prerequisites.

Sometimes, the installation of prerequisites fails; I recommend trying again. If that still does not work, or you don’t have Internet access from your server, you can download them. Again, see the article for hardware and software requirements.

Once the installation of prerequisites completes successfully, this is what you will see:

Now, you can click on Finish and continue to SharePoint Server installation. Click on “Install SharePoint Server” and choose your preferred type of installation (Standalone or Server farm). I prefer the Server farm because it gives me more flexibility to be able to add additional SharePoint Servers to the farm in the future.

6. Next, the installation is in progress.

Important: When the installation is complete, don’t run the configuration wizard.

If you don’t have SP1 for SharePoint installed you can install it at this point.

7. Our next step is Installation of SQL 2012 BI Edition. Click on the install file to open the SQL Server Installation Center.

8. Click on the Installation link on the left side of the menu and then click on the option to install a new stand-alone SQL Server.

9. At this point, the installation will check some Setup Support Rules. Please take care of any warnings or issues before continuing with the setup process.

10. The setup might warn us that we are installing SQL Server on a domain controller. Since we are installing everything on a stand-alone server, we cannot avoid installing SQL Server on a domain controller. You can click on Next and continue the installation.

11. On the Setup Role screen, select the SQL Server Feature Installation option.

We will get back to this screen a couple times in order to install additional instances of SSAS in Tabular mode, or PowerPivot for SharePoint. Click on Next and continue with the installation.

12. Because we are installing a stand-alone server, we should select all features available in the Feature Selection screen.

13. Select a name for the Database Engine instance and the Instance ID, and click on Next.

14. The Server Configuration screen is where we define which accounts will be used in order to run each of the services. We already created all necessary users in section 3.

15. In the Database engine configuration, select the Windows authentication mode and add user permissions.

16. Our next step is configuration of Analysis Services. Since we are going to install three instances of SSAS (Multidimensional, Tabular and PowerPivot) on this server, we will first install the Multidimensional and Data Mining Mode.

Choose the Multidimensional and Data Mining Mode option and click on next.

17. In the following screen, choose the Install only option and click on Next.

18. Click on Next until the installation process starts.

Wait for the process to finish.

19. Up to this point in our configuration, we have already installed and configured one instance of SSAS in Multidimensional and Data Mining Mode. Our next step will be installing and configuring the other two types of SSAS services available for installation in SQL 2012. The first is SSAS in Tabular Mode, and the second is PowerPivot for SharePoint Integrated Mode.

20. We will start by installing SSAS in Tabular Mode. Click on the install file to open the SQL Server Installation Center. Continue through the Setup Support Rules until you are asked to choose the installation type. Choose to perform a new installation of SQL Server 2012.

21. In the Setup Role screen select the SQL Server feature option.

22. In the feature Selection screen, choose the Analysis Services feature and click on Next.

23. Name your instance; I suggest naming it with a legible name, such as “TABULAR”, and click on Next.

24. Define SQL Server Analysis Services service account and click on Next.

25. In the Analysis Services configuration screen choose the Tabular Mode option, add an administrative user account, and click on Next.

Navigate through the installation wizard until the installation starts. After the installation ends successfully, we can continue onto our next step.

26. Upon completion of the installation of SSAS in Tabular Mode, we are now going to install SSAS for PowerPivot for SharePoint.

I will skip over steps in section 8 – 10 because they are the same as in the Tabular Mode installation.

On the Setup Role screen select the SQL Server PowerPivot for SharePoint Installation option.

Notice that the option to add an SQL Server database relational engine to the installation is checked. Since we already installed a new instance of SQL Server Relational Engine earlier, you should uncheck this option and click on Next.

27. Continue to the feature selection screen. Notice that the features have been selected for you and click next.

28. During the Setup Rules check process we get the following warning:

“This warning is caused by unselecting the option to install the database engine on the previous Setup Role screen.”

If you don’t have any other issues with the rule check, continue and click on Next.

29. The instance name should be PowerPivot. In order to keep things clear, I suggest leaving the instance ID as “PowerPivot” and then click on Next.

30. Define SQL Server Analysis Services service account and click on Next. Continue with the installation process, and wait until the installation completes successfully.

31. Our next step will be the configuration of the PowerPivot and SharePoint servers. This will be done through the PowerPivot configuration wizard. It’s important to say that in order to successfully perform all of the following steps you must have SharePoint 2010 SP1 installed on your server.

32. Click on Start -> All Programs -> SQL Server 2012 -> Configuration Tools -> PowerPivot configuration Tool.

33. In the following screen click on the “Configure or repair PowerPivot for SharePoint” option and wait for the running process to finish.

34. The following screen shows all of the tasks that need to be done by the configuration tool.

You should pay attention to a couple of things. Go over all tasks listed in the wizard and make sure that the credentials of the service accounts and other properties are configured correctly.

Put your farm account credentials and a passphrase for your SharePoint instance. Then, continue with the additional tasks below.

When you finish, click on “Validate” and after the following screen appears, click on “Run”. If the validation did not complete successfully, correct the gaps and validate again.

This is how the wizard looks initially:

And this is how the wizard should look after the validation. Once the validation completes successfully, click on Run. Exit the wizard after it finishes running.

35. Navigate to the following folder:

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN

Run the following command:

PSConfig.exe -cmd upgrade -inplace b2b -force -cmd applicationcontent -install -cmd installfeatures

36. Navigate to SharePoint Central Administration.

37. Navigate to Application Management -> Manage Services on the Server.

38. In the following screen stop the ‘Claims to Windows Token Service’ and start it again. Then perform IIS reset.

39. Navigate to Application Management -> Manage Server Applications -> Default PowerPivot Service Application.

40. If you see the following screen without any errors it means you have done everything correctly!

41. In order to create PowerPivot workbooks, you need the PowerPivot Add-in for Excel. You can download the add-in here: http://www.microsoft.com/en-us/download/details.aspx?id=29074.

Congratulations! If you’re reading this, it means you have a SharePoint server with full BI stack up and running.