Running PowerPivot for SharePoint on Windows 7
Given the allure of being able to work on PowerPivot for SharePoint solutions on a single workstation, laptop, or personal computer, my first choice in development environments is to run everything on a single machine. However, given Microsoft's decisions around both their virtualization products (Hyper-V and Virtual PC) and the move of the application platform to 64-bit–only versions (SQL Server 2008R2 and Office SharePoint Server), running PowerPivot for SharePoint leaves some significant decisions for the training environment.
As an example of the choices, unless you are prepared to install (or at least dual-boot) a server operating system (Windows Server 2008 R2), then Hyper-V is not really a viable option. The virtualization technology for Windows 7 Microsoft Virtual PC as of this writing does not allow for the hosting of 64-bit operating systems. Therefore, Virtual PC is not an option for hosting a learning installation. Virtualization technologies are on the market that will host the 64-bit operating systems required for your development environment; VirtualBox and VMware come to mind.
While I personally evaluated a number of configurations, what has personally worked best for me is running SharePoint 2010 Server under a Windows 7, 64-bit installation, without using any virtualization at all. Running SharePoint under Windows 7 is an alternative and unsupported configuration suitable only for individual educational purposes. There are significant security risks to running anything other than a developmental training installation in this configuration. Additionally, as an unsupported installation, you are “working without a net” as far as Microsoft Support is concerned. Be warned, I have had a feature or two fail to work as advertised, especially during speaking engagements and demonstrations; however, the core PowerPivot for SharePoint features (solution publishing, security, and data refresh) have yet to fail me.
The procedure I describe next is a mash-up from resources that I poured through on the Web and otherwise, in an attempt to develop a method of getting myself and my team productive on engagements quickly. Thanks to Stacia Misner and Vidas Matelis for their work on this problem as well.
The Road Map
After exhaustive (and exhausting) testing of various permutations and combinations, the following is the only recipe with which I have been able to reliably create a PowerPivot for SharePoint environment on a Windows 7 machine. In short, we will
- Install SQL Server, in order to have a datastore for SharePoint
- Install the SharePoint prerequisites and prepare the operating systems
- Install and configure a SharePoint farm
- Add PowerPivot for SharePoint to an existing farm using the SQL Server 2008 R2 installation program
- Perform any final configuration changes that you may need
First and foremost, if you don't have access to the binaries, the installation of software for SQL Server 2008 R2 Enterprise Edition or SharePoint 2010 Server Enterprise is not an option. As of this writing, both are available from Microsoft.com in the form of time-limited (180-day) licenses. Alternatively, the full versions (with time-limits removed) are available via Microsoft's TechNet Plus Direct subscription service (http://technet.microsoft.com). I strongly endorse the TechNet Plus Direct subscription as a tool for anyone learning Microsoft Business Intelligence technologies.
Second, both the PowerPivot for SharePoint Unattended Service account and the SQL Server Analysis Services SharePoint Integrated Mode database will require domain credentials. Local user accounts are simply not supported. Therefore, if the target of your installation is not part of a Windows domain, the following procedure will not work.
Step One: Install SQL Server 2008 R2
From the SQL Server 2008 R2 (Enterprise Edition) media, run the Setup.exe program in the root directory. This will display a dialog similar to Figure 6-8. Select “New installation or add features to an existing installation.”
Figure 6-8. SQL installation: Step One
From the next dialog, a group of setup support rules will execute. Press OK to continue. The next dialog will require a product key (unless you are installing an evaluation edition). Enter the key if required and proceed by pressing the Next button. Agree to the software license terms in the next dialog, and press the Next button to continue.
In the dialog illustrated in Figure 6-9, Setup Support Files, it is important to press the Install button to actually install the support files. After a short progress bar, the dialog will vanish and a new set of windows will be generated.
Figure 6-9. Setup Support Files
The next set of dialogs will begin with a group of Setup Support Rules being executed. This is a means of ensuring common problems with the particular installation environment are avoided. Assuming no rules require correction, press Next to proceed.
The next dialog, Role Selection, is of great importance to the process. Because we are creating only a SharePoint farm and not a PowerPivot for SharePoint installation, in this step, select SQL Server Feature Installation, as depicted in Figure 6-10.
In the next dialog, Feature Selection, all that is strictly required is the relational database. However, in order to have the client tools, Business Intelligence Developer Studio, etc., I generally use the Select All button and then deselect SQL Server Replication and Full-Text Search features. The remaining dialogs in the installation are of little impact to PowerPivot, though you will want to add the account performing the installation as the administrator for both relational databases and in the event of a Select All installation, the Analysis Services databases. After the pressing the Next button within the Features Selection dialog, the Installation Rules dialog pictured in Figure 6-11 will be shown.
Figure 6-11. Installation Rules dialog
Step Two: Install the SharePoint Prerequisites
Because you're doing an unsupported installation, using the normal SharePoint Server installer to check for and load prerequisite software will not be possible. That said, there are a number of components that will have to be installed onto the target machine manually. The first element needed is the Microsoft FilterPack 2.0, which is contained in the SharePoint installation media. Additionally, as we are “hacking” an installation normally not supported on Windows 7, we will have to do some editing of the setup .xml file. To facilitate both of these operations, copy the installation media to a directory on a local drive. In my case, I used a folder named SPInstall off the root of the C: drive.
Edit the Config.xml File
In order to coax Windows 7 into running a setup routine specifically created for a server operating system, the config.xml file will have to be modified. Since I have copied the installation media contents to a directory on my drive, I can find the target .xml file at <Install Files Location>FilesSetupconfig.xml. In my specific case, this is C:SPInstallFilesSetupconfig.xml. The sole modification of the file is to add the following line inside the </Configuration> tag.
The resulting file should look like Figure 6-12. Save this file in the location on the local drive for use later.
Figure 6-12. Completed config.xml edit
Install Microsoft Filter Pack 2.0
Expanding the installation media to the local directory also affords an easy method of installing one of the prerequisites, the Microsoft FilterPack 2.0. Executing the installer is dead simple. Just run the following command from a command prompt.
<Install Files Location>PrerequisiteInstallerFilesFilterPackFilterPack.msi.
This command will launch the Microsoft Filter Pack 2.0 Setup Wizard. Proceed through the Wizard, agreeing to the license terms, and the dialog in Figure 6-13 will indicate installation success.
Figure 6-13. Filter Pack installation complete
Install WCF Hotfix KB976462
A known issue involving Windows Communication Foundation requires this hotfix to be applied. One of the first symptoms of not having installed it will be an inability to perform PowerPivot slicer operations. The hotfix can be obtained at http://go.microsoft.com/fwlink/?LinkID=166231. This URL will result in an installation file to be executed that will apply the update. Download the installation file and execute it.
Install ADO.Net Data Services Update
Another needed hotfix applies to Windows 7 machines using REST data services (which we will). The hotfix can be obtained at http://go.microsoft.com/fwlink/?LinkId=163524. Similar to the WCF hotfix, this URL will result in an installation file to be executed that will apply the update. Download and install the update.
Install the Microsoft Sync Framework
Another needed hotfix applies to Windows 7 machines using REST data services (which we will). The hotfix can be obtained at http://go.microsoft.com/fwlink/?LinkID=141237. This URL will launch a series of installation dialogs, including a license agreement. The process will conclude with a dialog box similar to Figure 6-14.
Figure 6-14. Microsoft Sync Framework complete
Install SQL Server Native Client
As SharePoint will need to connect to a SQL Server database to store configuration and content data, the SQL Server Native Client would normally be installed as part of the installer, executing on a Windows server. However, as we are hacking the software installation process to use Windows 7, this is an item to be manually installed. The client components can be downloaded from http://go.microsoft.com/fwlink/?LinkId=123718. The only part of this installation that warrants further instruction is in the dialog pictured in Figure 6-15. Only the client components need be installed to use PowerPivot for SharePoint. The SQL Server SDK need not be installed.
Figure 6-15. SQL Server Native Client
Install Windows Identity Foundation
In short, this hotfix is required for certain identity capabilities that may be used in SharePoint solutions. Ensure you install the 6.1 version of the hotfix for 64-bit operating systems. The hotfix can be downloaded from http://support.microsoft.com/kb/974405. As with other hotfix items, the installation is short and sweet after a quick agreement to the software license terms.
Install Chart Controls
The chart controls, another software component the server installation handles well, must be added manually for our Windows 7 platform. The software can be downloaded from http://go.microsoft.com/fwlink/?LinkID=122517. The installation is similar to a hotfix, with yet another license agreement.
Install Microsoft SQL Server 2008 Analysis Services ADOMD.NET
This package contains components necessary for communication with SQL Server Analysis Services. The software is available for download at http://download.microsoft.com/download/A/D/0/AD021EF1-9CBC-4D11-AB51-6A65019D4706/SQLSERVER2008_ASADOMD10.msi. Similar to the other components, a simple license terms agreement will result in successful installation.
Enable Required Windows Features
The final step in this preparation and prerequisite phase is to ensure required Windows features are enabled. A command file to execute this operation is available from the book's web site. However, the entire list of features is contained in the following figures. You may verify the completeness of your work by checking your own Windows Features dialog (from the Control Panel, Programs area) against Figures 6-16 and 6-17.
Figure 6-16. Upper portion of required Windows features
Figure 6-17. Lower portion of required Windows features
Step Three: Install SharePoint 2010 Server
With the operating system and other software prerequisites installed, the next step is to install, but not configure, the SharePoint 2010 Server instance. This process will begin with issuing the following command from a Windows command prompt, substituting your specific location for the SharePoint installation files for <Install Files Location>. At the command prompt, issue the command <Install Files Location>Setup.exe. If the modifications to the config.xml file have been done correctly, the next result should be a dialog requesting a product key, similar to Figure 6-18.
Figure 6-18. SharePoint product key
After entering a correct and successfully validated key, read and agree to the Microsoft Software License Terms in the dialog window that follows. After agreeing to the license terms, the Installation Type dialog will appear. PowerPivot for SharePoint requires a farm installation. Although it is a bit confusing, because we are in fact creating an all-in-one (aka stand-alone) installation, the stand-alone option must not be chosen. Click the Server Farm button as indicated by Figure 6-19.
Figure 6-19. SharePoint installation type
The next dialog is no less confusing than its predecessor. Based on the installation type (Server Farm), the installer is attempting to determine which server type we are adding in this installation. At this dialog, which should be similar to Figure 6-20, choose the Complete radio button and click the Install Now button in the lower right-hand area of the window.
Figure 6-20. Server type selection
At this point, take a break; get a cup of coffee or tea. Do something to take your mind off the progress bar indicating Installation Progress. At this point, SharePoint Server 2010 is being installed onto your Windows 7 target machine. When the installation is completed, you will see a dialog similar to Figure 6-21. Before clicking the Close button, ensure the check box to the left of “Run the SharePoint Products Configuration Wizard now” is in an unchecked state. The reason for this is twofold. First, we don't have a database in which to store our SharePoint configuration (and content) at this point. Second, the SQL Server 2008R2 Installer for PowerPivot for SharePoint will take care of that for us.
Figure 6-21. End of the SharePointServer installation
Step Four: Add PowerPivot for SharePoint Existing Farm
Congratulations, you have installed SharePoint 2010 Server on your Windows 7 machine. However, before you can actually show it off and make use of it, we have to configure SharePoint and install PowerPivot for SharePoint. In order to do that, we execute the SQL Server 2008 R2 installer. Thankfully, no modifications to the SQL Server installation scripts are necessary, as SQL Server has been available on client operating systems for some time.
Instead, consider what will be happening when we install SQL Server. Similar to how the PowerPivot add-in for Excel created an in-memory runtime version of SQL Server Analysis Services, the PowerPivot for SharePoint installer will create the analogous server-hosted Analysis Services instance with which to store the data that comprises our PowerPivot solutions.
To get started with the final, major software installation required to enable our development environment, load the SQL Server 2008 R2 installation program (setup.exe). At the first dialog box, similar to Figure 6-22, choose Installation from the left-hand navigation, then “New installation or add features to an existing installation.”
Figure 6-22. SQL Server Installation Center
The installer will execute a set of setup rules, and eventually present a dialog for entry of the product key. After entering a valid license key, agree to the license terms, which will render a dialog similar to Figure 6-23. Ensure that you click the Install button at this dialog, as nothing will happen otherwise. Clicking Install will cause the installation program to install the needed Setup Support Files and eventually present a dialog similar to Figure 6-23.
Figure 6-23. Setup Support Files
At the conclusion of the Setup Support Files phase, the SQL Server portion of the installation may appear to be finished, as the dialog vanishes without user input. Eventually, you will be presented with a set of setup support rules the installer has completed. If a rule fails, correct the situation (usually as simple as uninstalling software) and return to the installer. Don't be alarmed with a Warning for the Windows Firewall rule—as this is a “stand-alone” machine, we will not have to be too concerned with connections to and from our development instance of SQL Server. Unless your Setup Support Rules dialog looks vastly different than Figure 6-24, you will be able to press the Next button to continue.
Figure 6-24. Setup Support Rules
The next step in the installation is where we can begin to see the PowerPivot wheels begin to turn. In the Setup Role dialog, similar to Figure 6-25, choose the SQL Server PowerPivot for SharePoint radio button. In the “Add PowerPivot for SharePoint to:” pulldown, ensure New Server is selected. At this point, the Setup Role dialog must look like Figure 6-25.
At this point, things get a little easier, because the PowerPivot for SharePoint installer dictates many of the selections. For example, in the dialog immediately after Setup Role, the features selected and installation directories are predetermined by the installer. Click the Next button to proceed.
Step Five: Final Configuration
The maximum file upload size for PowerPivot for SharePoint solutions is a function of two SharePoint settings. First, the web application limit needs to be adjusted. This is accomplished via SharePoint Central Administration. Second, the Excel Services maximum file upload size must also be adjusted upward from the default setting in order to accommodate typical PowerPivot file sizes.