PowerPivotGeek?

Who is this mystery man?
Click on the icon to find out. Who is powerpivotgeek?

Reported Problems

Below are frequently reported problems with “PowerPivot for SharePoint” (summarized from many sources).
Most recently discovered issues are at the top.

How to:

Issues:

SharePoint reports "Could not load type ‘Microsoft.AnalysisService.SharePoint.Integration.ReportGalleryView’." (09-Jan-2010)

In the ULS log you will see an error:

12/16/2009 20:54:40.42 w3wp.exe (0×225C)                       0×1DD4 SharePoint Foundation                 Runtime                               dwickert                Unexpected       System.Web.HttpException: Could not load type ‘Microsoft.AnalysisServices.SharePoint.Integration.ReportGalleryView’.    at System.Web.UI.TemplateParser.GetType(String typeName, Boolean ignoreCase, Boolean throwOnError)     at System.Web.UI.TemplateParser.ProcessInheritsAttribute(String baseTypeName, String codeFileBaseTypeName, String src, Assembly assembly)     at System.Web.UI.TemplateParser.PostProcessMainDirectiveAttributes(IDictionary parseData)                e4b0c8b0-87b8-4391-84b9-c4805d52bedd

To fix the problem, use Central Administration. Click on System Settings \ Manage Farm Solutions. Look for the “powerpivotwebapp.wsp” solution file (or something similar – this is the name for RTM). If the solution is not deployed then deploy it. If you have multiple web applications it might have to be deployed several times. The assembly is part of the PowerPivot solution that is deployed during installation. PowerPivot actually has two solutions: one that is deployed on web applications (this one) and another one that is deployed on application servers.

Cache manager cannot delete folders with long file paths (15-Dec-2009)

If you have a workbook with long table names the system may not be able to properly delete the embedded databases when loaded into the PowerPivot engine. The system uses managed APIs for deleting file and folders, and these APIs are limited to 260 characters. In RTM builds we will use a different set of APIs for managing files and folders, but in CTP3, since the table names are part of the underlying SSAS database, and these get mapped to folders within the databases, the system may not be able to properly delete the folders.

As a workaround, delete the files manually. See the workaround in the next issue for the steps needed to delete the folder structure and use shorter names for your tables.

A corrupted database may result from the interplay between data refresh and a workbook being loaded (15-Dec-2009)

The PowerPivot system has a cleaner thread that is attempting to identify databases that are no longer being used. If the cleaner thread detects a database that is no longer needed (and can be deleted) at the same time as when the data refresh job runs, then a corrupted database might result. Depending on where the corruption occurred, the Engine may not be able to load any databases because of this one corrupted database.

The error reported may be similar to:

The following error occurred while opening the file ‘\\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GEMINIBI\OLAP\Backup\Sandboxes\DefaultGeminiServiceApp\27a8abcc-6310-42b1-98a7-bd8de25e4d3c.1.db\DimProduct.0.dim\H$DimProduct$AvailableForSaleDate.1.tbl.xml’. An error occurred when loading the DimProduct dimension, from the file, ‘\\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GEMINIBI\OLAP\Backup\Sandboxes\DefaultGeminiServiceApp\27a8abcc-6310-42b1-98a7-bd8de25e4d3c.1.db\DimProduct.6.dim.xml’.      at Microsoft.AnalysisServices.XmlaClient.CheckForException(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError)     at Microsoft.AnalysisServices.AnalysisServicesClient.Discover(IMajorObject obj, ObjectExpansion expan…

And no databases can be queried or data refreshed.

To workaround, delete the files manually:

  1. Stop the SSAS instance (instance name is GEMINIBI) via a NET STOP MSOLAP$GEMINIBI
  2. Delete the SSAS data folder, typically all files under: “C:\Program Files\Microsoft SQL Server\MSAS10_50.GEMINIBI\OLAP\Data\”
  3. Delete the SSAS backup folder, typically all files under: “C:\Program Files\Microsoft SQL Server\MSAS10_50.GEMINIBI\OLAP\Backup\Sandboxes\”
  4. Start the SSAS instance, via a NET START MSOLAP$GEMINIBI

The system should now be able to start loading and working with new workbook files for editing. Since all files in PowerPivot are stored in the SharePoint Content databases, there is no loss of data as a result of this operation. There is some downtime while the temporary files are being deleted, and the system will run more slowly for a time as frequently used databases are cached into memory.

Installation fails with: ‘The term "New-SPExcelServiceApplication” is not recognized as the name of a cmdlet, function, script file or operable program.’ (15-Dec-2009)

This error message can be returned in the following circumstances

  1. The user is not a machine administrator.
  2. The user does not have sysadmin rights to the SharePoint “Config” database.
  3. The user does not have farm administrator rights.
  4. Excel Services is not available because the farm is running SharePoint Foundation or SharePoint Standard Edition – not Enterprise Edition. Excel Services is only available on SharePoint Enterprise Edition.

NEW FARM installation fails with: “Object reference not set to an instance of an object.” (07-Dec-2009)

PowerPivot for SharePoint is not supported on a SharePoint “standalone” server. Inspection of the logs reveals:

2009-11-10 08:38:01 AS: SharePoint Products Configuration Wizard version 14.0.4514.1009. Copyright (C) Microsoft Corporation 2010. All rights reserved.

2009-11-10 08:38:01 AS:

2009-11-10 08:38:01 AS: The user parameter specified with the configdb command is invalid.

2009-11-10 08:38:01 AS: When installed in standalone mode, you cannot specify a farm administrator user account or password.  The local account NetworkService becomes the farm administrator by default when installed in standalone mode.

Workaround

  • Complete the New Farm uninstall.
  • Uninstall SharePoint.
  • Reinstall SharePoint, selecting “Farm”, then “Complete.”
    IMPORTANT: Do not run the configuration wizard when given the opportunity.
  • Rerun new farm setup.

Installing PowerPivot for SharePoint on a domain controller reports a “timeout” (07-Dec-2009)

Symptom

Timeout has expired and the operation has not been completed.

        2009-11-21 20:51:34 AS: Exception!!:System.ServiceProcess.TimeoutException: Time out has expired and the operation has not been completed.

Cause

On domain controller, AS service account is switched to local service, causing downstream setup failure

Workaround

Various errors after a previous uninstall fails when running NEW FARM install again (07-Dec-2009)

After a previous uninstall of AS SharePoint integration, you are running the NEW FARM install again. We are installing new farm after a failed uninstall. Setup erroneously attempts to repair, rather than reinstall, the AS SharePoint feature. Setup fails with one of the following messages:

Symptom

1. Setup fails with the following error:

2009-11-25 09:23:26 Slp: Could not load file or assembly ‘Microsoft.AnalysisServices.SharePoint.Integration, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91′ or one of its dependencies. The system cannot find the file specified.

Further inspection of logs reveals:

2009-11-25 09:23:19 AS: Running ASSPI Repair action…

2009-11-25 09:23:19 Slp: Error: Action "Microsoft.SqlServer.Configuration.AnalysisServices.ASSPIRepairAction" threw an exception during execution.

2009-11-25 09:23:19 Slp: Microsoft.SqlServer.Setup.Chainer.Workflow.ActionExecutionException: Could not load file or assembly ‘Microsoft.AnalysisServices.SharePoint.Integration, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91′ or one of its dependencies. The system cannot find the file specified.

OR

2. ASSPI_Repair_ASSPIRepairStartupFinalize_Cpu64

Workaround

  • After uninstalling SQL Server, run the SharePoint configuration wizard to disconnect from the server farm. You can access the wizard from the Start menu, under “Microsoft SharePoint 2010 Products”
  • After addressing any other reinstall issues, run new farm setup again.

Setup fails with the error: “AS: Object reference not set to an instance of an object.” (07-Dec-2009)

Various possibilities:

1. SharePoint’s back end database has been deleted, but SharePoint remains configured. Creation of the new SharePoint farm fails. Further inspection of logs reveals:

2009-11-30 17:33:41 AS: The configdb command is invalid or a failure has been encountered.
2009-11-30 17:33:41 AS: Failed to detect if this server is joined to a server farm.  Possible reasons for this failure could be that you no longer have the appropriate permissions to the server farm, the database server hosting the server farm is unresponsive, the configuration database is inaccessible or this server has been removed from the server farm.  To diagnose this problem further, review the extended error information located at C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS\PSCDiagnostics_11_30_2009_17_33_23_16_669865114.log.  If this problem persists, run this wizard again to disconnect from the server farm.  After disconnecting, run the wizard again to create or connect to an existing server farm.

Workaround

  • After uninstalling SQL Server, run the SharePoint configuration wizard to disconnect from the server farm. Access the wizard from the Start menu, under “Microsoft SharePoint 2010 Products.”
  • After addressing any other reinstall issues, run new farm setup again.

OR

2. A previous SharePoint farm is unregistered, but the old admin web site remains, blocking the creation of a new farm. Further inspection of logs reveals:

2009-12-01 14:52:38 AS: The adminvs command is invalid or a failure has been encountered.
2009-12-01 14:52:38 AS: An IIS web site by the name of SharePoint Central Administration v4 already exists on this server.  This name is reserved for the SharePoint Central Administration Web Application.  The web site must deleted or renamed before the SharePoint Central Administration Web Application can be created.

Workaround

  • After uninstalling SQL Server, use the server manager to remove the old “SharePoint Central Administration v4” site.
  • After addressing any other reinstall issues, run new farm setup again.

On reinstall, cannot create file for Secure Store or State Services (07-Dec-2009)

The database was created in the first install, and its presence blocks the follow on install. Inspection of logs reveals:

2009-12-01 17:50:03 AS: Cannot create file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.GEMINIBI\MSSQL\DATA\StateServiceDB.mdf’ because it already exists. Change the file path or the file name, and retry the operation.

CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

OR

Inspection of ULS logs finds:

Unknown SQL Exception 5170 occurred. Additional error information from SQL Server is included below.  Cannot create file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.GEMINIBI\MSSQL\DATA\Secure Store Service.mdf’ because it already exists. Change the file path or the file name, and retry the operation

Workaround

  • Open a command prompt as administrator and then run:

del C:\Program Files\Microsoft SQL Server\MSSQL10_50.GEMINIBI\MSSQL\DATA\StateServiceDB*.*
del “C:\Program Files\Microsoft SQL Server\MSSQL10_50.GEMINIBI\MSSQL\DATA\Secure Store Service*.*”

  • After addressing any other reinstall issues, run new farm setup again.

Issue: SharePoint do not seem to work after installation; ULS logs report “allowInsecureTransport” errors (29-Nov-2009)

Resolution: If running Windows Server 2008 R2, ensure that you have manually installed QFE KB976462 located here (http://go.microsoft.com/fwlink/?LinkID=166231) either after or before the SharePoint 2010 beta installation.

Additional information: You will find an entry similar to this one in the ULS log files located at:
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS\<date-time>.log

The errors will report “Unrecognized attribute: ‘allowInsecureTransport’” for any backend SharePoint service (including PowerPivot). If running Windows 2008 SP2, the SharePoint installation will stop you if you do not install KB971831 (which is the matching KB article for Windows 2008), but for R2 there is no enforcement for this required QFE. Anyone installing PowerPivot onto R2 will likely run into this problem.

Issue: Data refresh reports “Access to the port is denied” errors (29-Nov-2009)

Resolution: On the application server where PowerPivot is installed, add the PowerPivot System service account manually to the SQLServerMSASUser$<server>$GEMINIBI group. If this was a NEW FARM installation, then this is the farm account specified during installation. You may also need to configure the service account as an OLAP administrator to the GEMINIBI instance.

Additional information: You will find an entry similar to this one in the ULS log files located at:
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS\<date-time>.log

11/22/2009 11:21:22.22 w3wp.exe (0×0ED0)                       0×123C SSAS Mid-Tier Service         Data Refresh                   99 High     EXCEPTION: System.UnauthorizedAccessException: Access to the port is denied.     at System.IO.Ports.InternalResources.WinIOError(Int32 errorCode, String str)     at System.Threading.Semaphore.OpenExisting(String name, SemaphoreRights rights)     at Microsoft.AnalysisServices.SharePoint.Integration.DataRefreshService.Execute() <guid>

The PowerPivot Configuration Timer Job is responsible for ensuring that the correct permissions are given to the PowerPivot System service. Normally this is done at installation time and then repeatedly when the timer job runs. However if the farm service account (under which all timer jobs run) does not have enough permissions on the machine, then the service account might not be configured properly. There are two permissions that that the PowerPivot System service needs: (1) it must be an SSAS administrator on the GEMINIBI instance, and (2) it must have access rights to the underlying SSAS data folder. The SQLServerMSASUser$<server>$GEMINIBI group is used to control ACLs for the data folder.

Issue: Snapshots cannot be generated (big “X” on all icons) (29-Nov-2009)

Resolution: On the WFE, interactively logon using the web application’s service account. If this was a NEW FARM installation, then this is the farm account specified during installation. On some machines performing this interactive logon is enough to create an Internet Explorer profile, but if the problem still persists (even after a reboot), start up Internet Explorer and add the WFE machine name (or NLB cluster name) into the Intranet Trusted zone. If using multiple WFEs, you will need to repeat on each WFE.

Additional information: You will find an entry similar to this one in the gemini.log file typically located at:
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS\gemini.log

11/28/2009 11:24:44 PM :  <file>.xlsx – INFO: ‘http://<server name>:<port>’ cannot be added to the list of trusted intranet sites: -2147024890

To generate a snapshot, the Gallery has an event handler that launches “snapshot.exe” against the new or updated file in the document library. “Snapshot.exe” programmatically create a web browsers control in IE that renders the file and stores its graphic image as a property. To use Internet Explorer in this manner, it has to be configured so the URL is in the trusted Intranet zone. If the system is unable to configure IE, the request will timeout and a big “X” will be used.

 

——————————————————————————————————————————————

How to’s

How to: Completely uninstall a NEW FARM installation

Some setup failures are caused when configuration information remains on the machine after a previous install or uninstall. To completely uninstall a “New Farm” setup, do the following:

  • Uninstall SQL Server GEMINIBI instance. Select all features.
  • Run the SharePoint configuration wizard to disconnect from the server farm. You can access the wizard from the Start menu, under “Microsoft SharePoint 2010 Products.”
  • Use the Windows server manager to remove the old “SharePoint Central Administration v4” site.
  • Delete C:\Program Files\Microsoft SQL Server\MSSQL10_50.GEMINIBI\MSSQL\DATA\StateServiceDB*.*
  • Delete C:\Program Files\Microsoft SQL Server\MSSQL10_50.GEMINIBI\MSSQL\DATA\Secure Store Service*.*

How to: Report infrastructure problems/issues

These include issues with any PowerPivot for SharePoint component, such as:

  • Querying data embedded in PowerPivot workbooks, either directly through client tools, such as Excel desktop, or on the SharePoint Server via Excel Services or Reporting Services, or PerformancePoint Services.
  • Generating snapshots for PowerPivot Gallery files or any of the visualization techniques provided by the Gallery.
  • Automatic data refresh on SharePoint (scheduling or re-processing)
  • PowerPivot Mgmt Dashboard; its reports or data gathering usage infrastructure

Whatever the failure, it is important to identify, work around (if possible), and fix your issue. You can best help us do that by creating a separate bug, and attaching a complete set of log files to it.

  • Create your own bug at the MSConnect site following the instructions provided here: http://connect.microsoft.com/sqlserver
  • Make sure that your bug includes the <date-time> for when the problem occurred. The log files can be quite large and many times the only way to analyze them is to know when to look for the errors.
  • Create a zip file with the contents of C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS\<date-time>.log and attach it to the bug you created. Make sure you include the correct log for the time when the error occurred. The ‘<date-time>’ filename is for the period starting at that time.
  • If your bug includes PowerPivot Gallery issues, make sure that you include the “Gemini.log” file that is the \LOGS\ folder also.

By creating your own issue, we can correspond individually with you. Also, different bugs may show the same symptoms, and we don’t want to miss any issues. Only when we get the information about your machine online can we be confident that we have identified the cause of your problem.

How to: Report installation failures

Whatever the failure, it is important to identify, work around (if possible), and fix your issue. You can best help us do that by creating a separate bug, and attaching a complete set of log files to it.

  • Create your own bug at the MSConnect site following the instructions provided here: http://connect.microsoft.com/sqlserver
  • Create a zip file with the contents of C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log and attach it to the bug you created.

By creating your own issue, we can correspond individually with you. Also, different bugs may show the same symptoms, and we don’t want to miss any issues. Only when we get the information about your machine online can we be confident that we have identified the cause of your problem.

Using the Log Files to Investigate an Install Failure:

In general, you can diagnose install failures by inspecting the log files created during setup.

The C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log folder contains one folder for each setup session you have conducted. Their names The file names are timestamped, so they sort in order.

In the log folder, Summary.txt contains a summary of setup progress in your most recent setup session. After checking out Summary.txt, open the most recent timestamped folder for more information. PowerPivot setup information is found in Detail.txt. Search for “ASSPI_Install_ASSPIInstallStartupFinalize_Cpu64” to see log info generated during PowerPivot Server configuration. Errors will generally leave a record in this section of the log.

On occasion, inspection of the “ULS” logs, generated by SharePoint, yields more useful information. They can be found at: C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS.

  • Share/Bookmark

6 comments to Reported Problems