Posts Tagged ‘PowerPivot’

Install the KB982307 hotfix as a prerequisite for PowerPivot

Written by Cornelius J. van Dyk on . Posted in Blog

NOTE:  This KB hotfix has to be installed on all web front end as well as application servers in your SharePoint farm.

  1. Begin by downloading the applicable architecture (x86/x64/i64) version for your environment from here:
  2. Login to your server as an administrator.
  3. Open Windows Explorer.
  4. Browse to the downloaded .msu file and double click it.  In the case of a x64 server, the file name is “Windows6.1-KB982307-x64.msu”.
  5. Confirm installation by clicking “Yes”. image_7_6893988B
  6. Wait while the updates is being installed. image3_6893988B
  7. Upon completion of the installation, you will need to restart your server.  Click “Restart Now” to complete the installation. image6_6893988B



How do I – Uninstall PowerPivot for SharePoint 2010

Written by Cornelius J. van Dyk on . Posted in How Do I...

Given how much effort is was to get PowerPivot installed and working in the first place, you may never need this post, but just in case something goes wrong and you have to uninstall it to start over, here’s how to do that:

  1. Launch Programs and Features via Start/Control Panel/Uninstall a Program.
  2. Locate the “Microsoft SQL Server 2008 R2 (64-bit)” entry and select it with a left click.
  3. The Uninstall/Change button becomes active.  Click it.
  4. Windows may warn you that there are other users that’ll be affected.
  5. Click “Continue” to ignore the warning and proceed.
  6. The SQL Server configuration tool opens.
  7. Click “Remove”.
  8. Setup will load all the support rules.
  9. Once all rules were loaded, click “OK”.
  10. Change the “Instance to remove feature from” dropdown value to “POWERPIVOT”.
  11. Click “Next”.
  12. Ensure that “Analysis Services” and it’s sub categories under POWERPIVOT is checked.
  13. Click “Next”.
  14. Setup runs all the removal rules.
  15. Once the rules have run successfully, click “Next”.
  16. On the Ready to Remove screen, click “Remove”.
  17. Once setup has removed PowerPivot, you’ll be taken to the Complete screen.
  18. The status should indicate that the removal was successful.
  19. Click “Close”.

    If you have previously removed PowerPivot and am trying to reinstall it again, the installation may fail because the removal didn’t cleanup everything properly.

    If you install PowerPivot and go to the Solution administration page, there’s a .wsp for PowerPivot called “powerpivotfarm.wsp”.  If this solution shows an error with a message of “Some of the files failed to copy during deployment of the solution”, then the uninstaller didn’t cleanup everything successfully.


    Looking at the error above, the Microsoft.AnalysisServices.SharePoint.Integration.dll assembly didn’t get deployed to the GAC.

    If you browse to the GAC via Windows Explorer, you’ll see this:


    OK, so it is NOT there.  That’s what we expected because that’s what the error indicated.

    Ah… but wait… there’s more…

    Open a command prompt on the server.

    Now manually change directory to the GAC_MSIL folder and do it dir for that DLL.

    And what do we get?…


    It seems that the folder was not cleaned up successfully during a past uninstall.  The installer isn’t smart enough to figure out that’s the right folder to put the DLL and simply throws an error.  It wants to create the folder from scratch.

    Us the RD command to remove the offending folder and then retry your installation.  Odds are it’ll work this time.



How do I – Install PowerPivot into an EXISTING SharePoint 2010 farm

Written by Cornelius J. van Dyk on . Posted in How Do I...

This one was a BEAR to work through.  There’s not much out there for it and everywhere I turned it seems everyone else was either having the same problem OR suggestion we “start with a clean install”.  Nice suggestion that last one.  Almost like… try rebooting the computer… but I digress.  So hopefully this guide will help some other poor soul save some time when they run into the same issue.

  1. Due to a farm admin error during PowerPivot install that incorrectly claims that the current user is NOT a Farm Administrator, you’ll need to logon to your SharePoint server, using the SharePoint Admin Service account credentials.

  2. Install the Microsoft Access Database Engine 2010 Redistributable.

  3. Startup the SQL Server 2008 R2 installer in Admin mode:

  4. Browse to the CD folder of the install media.

  5. Right click “Setup.exe”.

  6. On the popup menu, click “Run as administrator”.

  7. image_60_05C9C7A4

  8. On the SQL Server Installation Center menu, click “Installation”.

  9. image

  10. Click “New installation or add features to an existing installation”.

  11. image

  12. On the Setup Support Rules page, correct any issues that are identified and then click “OK”.

  13. image

  14. Now click “Install” to install the Setup file required.

  15. image

  16. On the Setup Support Rules page, Setup will run it’s support rules to check for valid install.  Correct any issues that are identified and then click “Next”.

  17. image

  18. On the Installation Type page, Setup will list already installed instances of SQL Server.  Here you want to select “New installation or add shared features”.  DO NOT select the “Add features…” option.

  19. Click “Next”.

  20. image

  21. On the Product Key page, select the “Enter the product key” radio button.

  22. Enter your SQL Server 2008 R2 product key that you received from Microsoft in the edit box.

  23. Click “Next”.

  24. image

  25. On the License Terms page, check the “I accept the license terms” checkbox.

  26. Click “Next”.

  27. image

  28. On the Setup Role, select the “SQL Server PowerPivot for SharePoint” radio button.  DO NOT select the SQL Server Feature Installation option.

  29. From the dropdown list box, select “Existing Farm”.

  30. Click “Next”.

  31. image

  32. On the Feature Selection page, Setup will show you the installation options to be added.  These cannot be changed.  Click “Next”.

  33. image

  34. On the Installation Rules page, Setup will run rules to check for blocks.  Correct any issues that are identified and then click “Next”.

  35. image

  36. On the Instance Configuration page, you cannot change the Named Instance name, but you can change the Instance ID, if you wish.

  37. Click “Next”.

  38. image

  39. On the Disk Space Requirements page, click “Next”.

  40. image

  41. On the Server Configuration page, Setup requires that you configure an account for SQL Server Analysis Services.  Best Practices dictates that this should NOT be a network service or system account, but rather it should be a dedicated domain account.

  42. Enter the domain account ID e.g. “DOMAIN\ServiceAccountName” in the Account Name edit box.

  43. Enter the domain password in the Password edit box.

  44. Leave the Startup Type as “Automatic”.

  45. Click “Next”.

  46. image

  47. On the Analysis Services Configuration page, Setup is asking you to identify the administrators of Analysis Services.

  48. Click “Add Current User”.

  49. The SharePoint Admin Service account should now be listed as an admin.

  50. Click “Add…” to add each account that needs to have Admin rights to Analysis Services and PowerPivot.

  51. Click “Next”.

  52. image

  53. On the Error Reporting page, click “Next”.

  54. image

  55. On the Installation Configuration Rules page, Setup will run some more rules.

  56. Resolve any identified issues and click “Next”.

  57. image

  58. On the Ready to Install page, STOP RIGHT HERE!!!

  59. If you click “Install” here, your installation WILL fail… but you already knew that. 😉  That’s why you’re reading this post. 😀

  60. HACK #1

  61. At the bottom of the window, Setup is displaying the path to the “ConfigurationFile.ini” file.  Copy the path to the file.  We have to pause the install here because the location of this .ini file changes on every install as Setup will take the current date/time stamp and use it for the …Log\<YYYYMMDD_HHMMDD>\… part of the folder location.

  62. image

  63. Open the “ConfigurationFile.ini” file with Notepad.

  64. Using Ctrl+F, locate the reference to “FARMADMINPORT”.

  65. image

  66. Your port number will show something random.  This is because PowerPivot assumes that its installing at the same time as a default SharePoint install.  Since we already have SharePoint installed, we need to edit this value and change it to match the port of our Central Admin location.

  67. Change the value between the quotes to match the port number of your current Central Admin.

  68. image

  69. Exit and save the .ini file.

  70. HACK #2

  71. The other problem with the current PowerPivot install is that it looks for the Microsoft.AnalaysisServices.SharePoint.Integration.dll file in the bootstrap folder, but it doesn’t exist there.  In past failed installs of PowerPivot, you may have encountered references to this DLL being “missing”.

  72. Copy Microsoft.AnalysisServices.SharePoint.Integration.dll to the PowerPivot bootstrap location of C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\x64

  73. You may, or may not have a copy of this DLL file handy.  If you don’t, you can download the copy I placed on my SkyDrive here:


  75. HACK #3

  76. Now we have to ensure that there are no remnants of this dll left over within the GAC.  Open Windows Explorer.

  77. Browse to “C:\Windows\assembly”.

  78. Scroll down and look for Microsoft.AnalysisServices.SharePoint.Integration.

  79. You should not find it because it’s not installed yet.  This was the case in my install as can be seen below.

  80. image

  81. That’s not necessarily true though as can be seen from my environment screen shot below:

  82. image

  83. Open a command shell via Start/Run/cmd

  84. Execute “cd\windows\assembly\gac_msil” from the command line.

  85. Execute “dir microsoft.analysisservices.s*” from the command line.

  86. Your output SHOULD show 0 files and 0 dirs.  If it doesn’t, like in my example above, the odds are you’ve had a failed install of AnalysisServices before and remnants were left behind when the installer didn’t cleanup behind itself.

  87. If there is a folder named “Microsoft.AnalysisServices.SharePoint.Integration”, you need to delete it by executing “rd  Microsoft.AnalysisServices.SharePoint.Integration” from the command line.

  88. Now that we’ve cleared the path for the installer by deleting remnants from the GAC, hacking the configuration file and manually providing a copy of the integration DLL, we can switch back to Setup and click “Install”.

  89. Setup should complete successfully this time around as in my example below:

  I hope this saves somebody some time and frustration out there.



Step-by-Step – Installing the Microsoft Access Database Engine 2010 Redistributable

Written by Cornelius J. van Dyk on . Posted in How Do I...

This is a prerequisite to Installing PowerPivot for SharePoint into an EXISTING SharePoint Farm.  PowerPivot seems to work just fine being installed together with a clean install of SharePoint, but when you already have SharePoint installed and try to add PowerPivot… all hell breaks loose.  I was never able to find proper documentation on how to pull this off and managed to hack and debug my way through it like Holmes unraveling a mystery.  Hopefully this will save someone else some time and frustration…

  1. Logon to your server with the SharePoint service account.
  2. Download and run the appropriate setup package for the target version (x64 or x86) of your farm
  3. Click “Run” to execute the install package.
  4. image
  5. On the Welcome screen, click “Next”.
  6. image
  7. Check the “I accept the terms of the License Agreement” check box and click “Next” to continue.
  8. image
  9. Select the folder location to which you wish to install and finally click “Install”.
  10. image
  11. The installation should complete successfully.
  12. image