«
»

SharePoint 2010 and PowerPivot for SharePoint

In my previous blogs, I looked at PowerPivot for Excel and how it enabled the average user to access and analyze data without the aid of an IT professional. However, the advantages do not stop there. The other side of making business decisions is the sharing of those decisions along with the data used to support them.

PowerPivot for SharePoint 2010 is part of the new SQL Server 2008 R2 Analysis Services when installed with SharePoint integration. Installing PowerPivot for SharePoint looked to be a simple matter. However, I found that there are some very important planning steps required for a smooth installation.

I was fortunate enough to begin with a clean slate. Because PowerPivot for SharePoint only works with SharePoint 2010, and because SharePoint 2010 is in beta, I had the luxury of starting with the SharePoint installation. PowerPivot for SharePoint can be installed on an existing SharePoint Server, or on a newly installed server. If your organization is planning to use PowerPivot for SharePoint, seriously consider installing both products at the same time. Waiting until your SharePoint server is configured and well established means that much of the PowerPivot installation and configuration will require manual efforts.

The following are the most important things I learned while installing both products:

  • SharePoint 2010 Beta
    • Both SharePoint 2010 and PowerPivot for SharePoint require the 64-bit version of Windows Server 2008 SP2 or R2. I used Windows Server 2008 R2.
    • The minimum 4GB of RAM really is the minimum, at least for installation. SharePoint 2010 Beta would not install properly with less RAM. Believe me, I tried. Using Hyper-v virtual machines to create the server requires some tweaking of RAM to make sure resources are available for other virtual machines in a demo setup. After completing the install and configuration of the SharePoint 2010 and PowerPivot virtual machine, I was able to run the server with 2GB of RAM. This made it run very slow, but it did run fine. If possible, I would run with the full 8GB that is recommended.
    • 80 GB of disk space is recommended. However, PowerPivot caches data to speed retrieval and will require considerably more space. Remember that PowerPivot is designed to handled many millions of rows of data and plan accordingly.
    • If you run SharePoint 2010 on Windows Server 2008 R2 you need to download the Windows6.1-KB976462-v2-x64.msu hotfix file located at http://go.microsoft.com/fwlink/?LinkID=166231. The file fixes an issue that prevents PowerPivot data refresh and the PowerPivot Management Dashboard from working properly. Interestingly, this hotfix is apparently automatically installed as part of the Windows Server 2008 SP2 setup.
    • SharePoint 2010 should be installed using the Server Farm option on the Choose the installation you want dialog. Choose this option even if the server will be the only one.
    • Make sure and select the Complete option on the Server Type dialog.
    • Do not run the SharePoint Configuration wizard after the install process is complete. The PowerPivot for SharePoint installation will take care of all configuration tasks for you. If you forget and run the wizard, you will be forced to configure everything for PowerPivot manually.
  • PowerPivot for SharePoint – SQL Server 208 R2 Analysis Services
    • Run the SQL Server 2008 R2 setup application.
    • On the Feature Role dialog select Analysis Services with Sharepoint Integration and be sure an change the Install Analysis Service in option to A new farm (install separately). This tells the PowerPivot portion of the setup to configure the new SharePoint server with the necessary settings to run PowerPivot.
    • Download and run the SQLSERVER2008_ASAMO10.msi file located at http://go.microsoft.com/fwlink/?LinkID=168693&clcid=0×409. The SQL Server 2008 R2 Analysis Services OLEDB Provider version 10.5 update is required on each PowerPivot for SharePoint server to enable smooth data connections between clients and Analysis Services for data refresh.
    • The Analysis Services service account must be a domain account. Setup will display an error if you try to use the local machine Service or Network Accounts.
    • PowerPivot Service Application Pool accounts must also be domain accounts. When installing PowerPivot on multiple servers in a farm, this account does not necessarily need to be the same for all servers. The account requires connect, read, and write permissions to the PowerPivot service application database.
  • SharePoint settings in SharePoint 2010 Central Administration
    • Change the value in Maximum upload size under Application Management | Manager web applications | SharePoint – 80 to 2047. This will allow PowerPivot workbooks of up to 2GB in size to be uploaded to the SharePoint PowerPivot Gallery.
    • In the Application Management | Manage service applications | ExcelServiceApp1 | Trusted File Locations | http:// change the following values:
      • Change the Maximum Workbook Size to 2000.
      • Change the Maximum Chare or Image Size to 10,

If you followed all of these recommendations, your SharePoint 2010 server with PowerPivot is ready to go!

In my next blog, I will look at what PowerPivot for SharePoint adds to the SharePoint server.

Share

Posted Saturday, January 30th, 2010 at 1:56 pm by peterl
2,929 views

This entry was posted on Saturday, January 30th, 2010 at 1:56 pm and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

Comments are closed.