Build your own SQL 2012 Demo Machine – Part 5 – SharePoint & PowerPivot

Step 6: Install SharePoint and PowerPivot for SharePoint

So, now for another couple of meaty installs. First of all, SharePoint. Note this must be Enterprise Edition otherwise the PowerPivot components won’t work.

1. First up we need to run the PreRequisiteInstaller that can be found in the root of the Installation Media directory:

Fig 6.1: Prerequisite Installer
Fig 6.1: Prerequisite Installer

This uses an Internet connection so make sure yours is up and running before proceeding. Double-click to get it running. It will present you with the SharePoint 2010 Products Preparation Tool dialog.

Fig 6.2: SharePoint 2010 Products Preparation tool
Fig 6.2: SharePoint 2010 Products Preparation tool

2. Click Next, Accent the license terms on the next screen and click Next, then enjoy the progress bar:

Fig 6.3: SharePoint Products Preparation Tool
Fig 6.3: SharePoint Products Preparation Tool

This is another “cup of tea” step, so off you go. When done, you will be presented with this dialog:

Fig 6.4: SharePoint 2010 Products Preparation tool
Fig 6.4: SharePoint 2010 Products Preparation tool

3. Click Finish.

4. Run Windows Update, install whatever it finds and then restart before we get on to installing SharePoint.

5. First up in installing SharePoint is entering your product key:

Fig 6.5: SharePoint Product Key
Fig 6.5: SharePoint Product Key

6. Enter your key and click continue. At the next screen accept the licence terms and click continue. The next screen is the File Location screen:

Fig 6.6: File Locations
Fig 6.6: File Locations

7. Accept the defaults and click “Install Now”. Enjoy the progress bar – go and make a light meal or something, this one takes a while.

Fig 6.7: Install Progress
Fig 6.7: Install Progress

When it’s complete, you’ll get the option to run the configuration wizard:

Fig 6.8: SharePoint Configuration Wizard
Fig 6.8: SharePoint Configuration Wizard

8. Very Important: Uncheck this option and click “Close”. Restart the VM.

9. Next we are going to install Service Pack 1. Copy it locally on to your VM and run it (if you are doing this using Oracle VirtualBox, do this via the Shared Folders feature).

Fig 6.9: SharePoint 2010 Service Pack 1
Fig 6.9: SharePoint 2010 Service Pack 1

10. Accept the licence terms and click Continue. Watch the various progress bars:

Fig 6.10: SharePoint 2010 Service Pack 1
Fig 6.10: SharePoint 2010 Service Pack 1

11. Once complete, click OK and restart the VM.

Now it’s time to install SQL Server for the 3rd and final time! Note the source of all of below is from this MSDN Article.

12. Run though setup to install a new stand-alone installation as show in part 4 right up until we get to the Setup Role dialog.

Fig 6.11: Setup Role
Fig 6.11: Setup Role

13. Here we choose to install “SQL Server PowerPivot for SharePoint” – and we also Check the “Add SQL Server Database Relational Engine Services to this installation”.

14. Click Next to get the Feature Selection dialog:

Fig 6.12: Feature Selection
Fig 6.12: Feature Selection

15. You have no options here so just click Next.

16. Click Next at Installation rules and move on to the Instance Configuration screen:

Fig 6.13: Instance Configuration
Fig 6.13: Instance Configuration

An Instance called POWERPIVOT is suggested – nothing else needs to be done

17.  Click Next for the Disk Space requirements screen

18. Click Next and get to the Server Configuration dialog:

Fig 6.14: Server Configuration
Fig 6.14: Server Configuration

19. As in previous Installs we need to set the Account Name and Password of the SSAS instance to that of Administrator.

20. Click Next. Repeat the steps for installation as for the first instance:

  • Database Engine Configuration – Add Current User as SQL Administrator
  • Analysis Services Configuration – Add Current User as SSAS Administrator
  • Click Next at Error Reporting
  • Click Next at Installation Configuration Rules
  • Click Install at Ready to Install
  • Go make another cup of tea.
  • When it’s complete – click Complete!

Once it’s complete, it’s time to configure SharePoint.

21. Close the SQL Server Installation window.

22. Open the PowerPivot Configuration Tool from Start Menu > All Programs > SQL Server 2012 > Configuration Tools:

Fig 6.15: PowerPivot Configuration Tool
Fig 6.15: PowerPivot Configuration Tool

This launches the tool:

Fig 6.16: PowerPivot Configuration Tool
Fig 6.16: PowerPivot Configuration Tool

23. As it’s the first run, there’s only one option – click OK. Expand the window to full size – sometimes it appears blank as if it’s hung, but all is OK once expanded. Here we have the full screen of the tool:

Fig 6.17: PowerPivot Configuration Tool
Fig 6.17: PowerPivot Configuration Tool

24. Enter the Account Password and decide on a Passphrase – this is only needed for adding servers to the farm, which doesn’t matter in this case. Note down the port number for the SharePoint Central Administration Port – it’s randomly generated so won’t be the same for each install.

25. Click “Register SQL Server Analysis Services (PowerPivot) on Local Server” on the left hand side:

Fig 6.18: PowerPivot Configuration Tool
Fig 6.18: PowerPivot Configuration Tool

26. Enter the Administrator Account password in the text box on the right.

27. Click Validate, and if everything passes click Run. You will see the following warning:

Fig 6.19: PowerPivot Configuration Tool
Fig 6.19: PowerPivot Configuration Tool

This is fine.

28. Click Yes. Then wander off for another cup of tea. You will eventually get this:

Fig 6.20: PowerPivot Configuration Tool
Fig 6.20: PowerPivot Configuration Tool

29. Click OK, and then Exit on the Main  PowerPivot configuration tool menu.

30. Restart so we can move on to the final part – enabling the components in SharePoint (as per this MSDN article)

First we need to Install and Start the Reporting Services SharePoint service, which we do by running a handful of Powershell commands.

31. From the Start button, choose All Programs > Microsoft SharePoint 2010 Products and right click on SharePoint2010 Management Shell and Run as Administrator:

Fig 6.21: SharePoint 2010 Management Shell
Fig 6.21: SharePoint 2010 Management Shell

This will bring up the shell, which is a PowerShell command prompt:

Fig 6.22: SharePoint 2010 Management Shell
Fig 6.22: SharePoint 2010 Management Shell

32. From the prompt run the following  commands:

  • Install-SPRSService
  • Install-SPRSServiceProxy
  • get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance

You won’t get much feedback from running those commands:

Fig 6.23: SharePoint 2010 Management Shell
Fig 6.23: SharePoint 2010 Management Shell

But don’t worry – stuff has happened.

Now, we need to create the Reporting Services Application in SharePoint.

33. From the Start button, choose All Programs > Microsoft SharePoint 2010 Products and click on SharePoint Central Administration:

Fig 6.24: SharePoint 2010 Central Administration
Fig 6.24: SharePoint 2010 Central Administration

It will take a little while to start up, so don’t panic if it just looks like it’s taking forever to load. If it throws an error just hit F5 to refresh – the services take a while to start up first time round. Eventually you will see this:

Fig 6.25: SharePoint 2010 Central Administration
Fig 6.25: SharePoint 2010 Central Administration

When we get to the page, first note that there is a warning about some critical issues – ignore these – as this is a Demo machine the issues raised don’t actually matter.

34. Under the “Application Management” group, click “Manage Service Applications”:

Fig 6.26: Create New Application
Fig 6.26: Create New Application

35. In the Ribbon, click New and in the dropdown choose “SQL Server Reporting Services Service Application.”. This will bring up the “Create SQL Server Reporting Services Application” dialog:

Fig 6.27: Create SQL Server Reporting Services Application
Fig 6.27: Create SQL Server Reporting Services Application

36. As shown above enter a Name for the Service Application. In the Application Pool Section choose “Create new application pool” (the default) and give it the same name as the Service Application. Scroll down to the next part of the dialog:

Fig 6.28: Create SQL Server Reporting Services Application
Fig 6.28: Create SQL Server Reporting Services Application

37. Leave the Database server & name as default, and leave Windows authentication selected. In the “Web Application Association” check the only available box. Then click OK and watch it spin:

Fig 6.29: Create SQL Server Reporting Services Application
Fig 6.29: Create SQL Server Reporting Services Application

You will ultimately see this:

Fig 6.30: Create SQL Server Reporting Services Application
Fig 6.30: Create SQL Server Reporting Services Application

If you want to Provision Subscriptions and Alerts, read this: http://msdn.microsoft.com/en-us/library/hh231725.aspx. I’m not doing it in this example, so just click OK to close the dialog.

The below section added 02 May 2012

38. Now for enabling Report Builder components and other goodies on your SharePoint site. This section is an extension of this MSDN article. Go to the home page of your SharePoint page (not central admin) which should be found by browsing to http://localhost/ – or http://{your machine name}/. It may take a while to load but you should see this:

Fig 6.31: Your SharePoint BI Home Page
Fig 6.31: Your SharePoint BI Home Page

39. Click on the Shared Documents link in the left hand side navigation pane:

Fig 6.32: Your SharePoint BI site Shared Documents Page
Fig 6.32: Your SharePoint BI site Shared Documents Page

40. Click on the Library Tab in the Ribbon:

Fig 6.33: Shared Documents Library
Fig 6.33: Shared Documents Library

41. Click on the Library Settings button at the far right of the ribbon.

Fig 6.34: Library Settings
Fig 6.34: Library Settings

42. Under General settings, click on Advanced Settings.

Fig 6.35: Library Settings - Advanced
Fig 6.35: Library Settings - Advanced

43. At the top change the option under “Content Types” for “Allow management of content types” to Yes.

44. Scroll to the bottom and click OK. You’ll return to the Library Settings:

Fig 6.36: Library Settings
Fig 6.36: Library Settings

45. At the bottom you will see an option under Content Types to “Add from existing site content types”. Click it.

46. You will be presented with the Content Types to add. Select everything under “SQL Server Reporting Services Content Types” and “Business Intelligence”.

Fig 6.37: SQL Server Reporting Services Content Types
Fig 6.37: SQL Server Reporting Services Content Types
Fig 6.38: Business Intelligence Content Types
Fig 6.38: Business Intelligence Content Types

47. Click OK. Now when you try to add a Document you can add Data Sources, and launch Report Builder by choosing a Report Builder Report document type.

48. Now, we can move on to Build your own SQL 2012 Demo Machine – Part 6 – Office Components

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>