CloudShare Blog

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.

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.

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.

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.

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 AnalysisServices 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.