Share via

6. Known issues and workarounds for PowerPivot & PowerView

  • Article

This is an article part of a series of articles

  1. Introduction
  2. Hardware requirements and sizing [Coming soon]
  3. Software requirements [Coming soon]
  4. Database storage requirements [Coming soon]
  5. Install and configure PowerPivot & PowerView in a scale out environment
  6. Known issues and workarounds --> You are here

Introduction

In this section I highlighted few known issues for PowerPivot, PowerView and related technologies like SQL Server Reporting services and Analysis services

Analysis Services and PowerPivot

 

Document Type is Not Supported

The PowerPivot Gallery Document content type is not supported. If you enable the PowerPivot Gallery Document content type for a document library, and attempt to create a new document of that type, you will see an error message similar to one of the following:

  • ‘New Document’ requires a Microsoft Sharepoint Foundation-compatible application and web browser. To add a document to this document library, click the ‘Upload Document’ button.
  • "The Internet address 'https://[server name]/testSite/PowerPivot Gallery/ReportGallery/Forms/Template.xlsx' is not valid."“Microsoft Excel cannot access the file 'https://[server name]/testSite/PowerPivot Gallery/ReportGallery/Forms/Template.xlsx'. There are several possible reasons:

The PowerPivot Gallery Document content type is not automatically added to document libraries, You will not encounter this issue unless you manually enable the unsupported content type.

 SQL Server Analysis Server (PowerPivot) Properties Page doesn’t open

 

Issue: If you need to update the SSAS permissions manually using the SQL Management Studio you might run into an error opening the properties page for the SSAS instance if you are running on Window 8.1 or Windows Server 2012 R2:

===================================Cannot show requested dialog.===================================Cannot show requested dialog. (SqlMgmt)------------------------------Program Location:at Microsoft.SqlServer.Management.SqlMgmt.DefaultLaunchFormHostedControlAllocator.AllocateDialog(XmlDocument initializationXml, IServiceProvider dialogServiceProvider, CDataContainer dc)at Microsoft.SqlServer.Management.SqlMgmt.DefaultLaunchFormHostedControlAllocator.Microsoft.SqlServer.Management.SqlMgmt.ILaunchFormHostedControlAllocator.CreateDialog(XmlDocument initializationXml, IServiceProvider dialogServiceProvider)at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.InitializeForm(XmlDocument doc, IServiceProvider provider, ISqlControlCollection control)at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm..ctor(XmlDocument doc, IServiceProvider provider)at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ToolMenuItemHelper.OnCreateAndShowForm(IServiceProvider sp, XmlDocument doc)at Microsoft.SqlServer.Management.SqlMgmt.RunningFormsTable.RunningFormsTableImpl.ThreadStarter.StartThread()

 

 Workaround: To overcome this issue download the following update from this article KB 2889492. The article also lists possible workarounds.

Issue: The PowerPivot Configuration Tool provisions a Team Site, and therefore the PowerPivot Gallery is not created.

Workaround: Create a new app (library).

  1. Verify the site collection feature PowerPivot Feature Integration for Site Collections is active.
  2. From the Site Contents page of an existing site, click add app.
  3. Click PowerPivot Gallery.

To use PowerPivot for Excel with Excel 2013, you must use the add-in that is installed with Excel

Issue: With Office 2010, PowerPivot for Excel is a stand-alone add-in that is downloadable from https://www.microsoft.com/en-us/bi/powerpivot.aspx. Alternatively it can also be downloaded from the Microsoft Download Center. Note that there are two versions of the PowerPivot add-in available as a download: One that shipped with SQL Server 2008 R2 and another that shipped with SQL Server 2012. However, for Office 2013, PowerPivot for Excel ships with Office and is installed when you install Excel. While the SQL Server 2008 R2 and SQL Server 2012 versions of PowerPivot for Excel 2010 are not compatible with Excel 2013, you still can install PowerPivot for Excel 2010 on your client computer if you want to run Excel 2010 side-by-side with Excel 2013. In other words, the two versions of Excel can coexist and so can the corresponding PowerPivot add-ins.

Workaround: To use PowerPivot for Excel 2013 you must enable the COM add-in. From Excel 2013, select File | Options | Add-Ins. From the Manage drop-down box, select COM Add-ins and click Go. From COM Add-ins, select Microsoft Office PowerPivot for Excel 2013 and click Okay.

 Reporting Services and PowerView

 Install and Configure SharePoint Server 2013 Prior to Installing Reporting Services

Issue: Complete the following requirements before you install SQL Server Reporting Services (SSRS).

  1. Run the SharePoint 2013 Products Preparation Tool.
  2. Install SharePoint Server 2013.
  3. Run the SharePoint 2013 Product Configuration Wizard, or complete an equivalent set of configuration steps to configure the SharePoint farm.

Workaround: If you installed Reporting Services SharePoint mode before the SharePoint farm was configured, the required work around depends on what other components are installed. For more information on the work around, see “Required Installation Sequence” section of Install SQL Server BI Features with SharePoint 2013.

Power View in SharePoint Server 2013 Requires Microsoft.AnalysisServices.SPClient.dll

Issue: Reporting Services does not install a required component, Microsoft.AnalysisServices.SPClient.dll. If you install SharePoint Server 2013 Preview and SQL Server 2012 SP1 Reporting Services in SharePoint mode, but do not download and install the PowerPivot for SharePoint 2013 installer package, spPowerPivot.msi then Power View will not work and Power View will exhibit the following symptoms.

Symptoms: When you attempt to create a Power View report, you see an error message similar to the following:

  • "Cannot create a connection to data source..."

The inner error details will contain a message similar to the following:

  • "The value 'SharePoint Principal' is not supported for the connection string property 'User Identity'."

Workaround: Install the PowerPivot for SharePoint 2013 installer package (spPowerPivot.msi) on the SharePoint Server 2013. The installer package is available as part of the SQL Server 2012 SP1 feature pack. The feature pack can be downloaded from the Microsoft download center at SQL Server 2012 SP1 Feature Pack

Power View sheets in a PowerPivot workbook are deleted after a scheduled data refresh

Issue: In the PowerPivot add-in for SharePoint, using Scheduled Data Refresh on a workbook with Power View will delete any Power View sheets.

Workaround: To use Scheduled Data Refresh with Power View workbooks, create a PowerPivot workbook that is just the data model. Create a separate workbook with your Excel sheets and Power View sheets that links to the PowerPivot workbook with the data model. Only the PowerPivot workbook with the data model should be scheduled for data refresh.

Refresh All in excel service 2013 failed because web application has Kerberos authentication

Issue: You get an error when you hit refresh all in an excel workbook with external data connection for a SharePoint web application that is configured to use Kerberos

Resolution:

The Claims to Windows Token Service (C2WTS) is a component of the Windows Identity Foundation (WIF) which is responsible for converting user claim tokens to windows tokens. Excel services uses the C2WTS to convert the user’s claims token into a windows token when the services needs to delegate credentials to a back-end system which uses Integrated Windows authentication. WIF is deployed with SharePoint Server 2010 and the C2WTS can be started from Central Administration.

Each SharePoint service application must run the C2WTS locally. The C2WTS does not open any ports and cannot be accessed by a remote caller. Further, the C2WTS service configuration file must be configured to specifically trust the local calling client identity. 

As a best practice you should run the C2WTS using a dedicated service account and not as Local System (the default configuration). But Local System will work if you configure the Kerberos constrained delegation to use the machine name account. The C2WTS service account requires special local permissions on each server the service runs on so be sure to configure these permissions each time the service is started on a server. Optimally you should configure the service account’s permissions on the local server before starting the C2WTS, but if done after the fact you can restart the C2WTS from the Windows services management console (services.msc). 

To start the C2WTS using Domain Account

1. Create a service account in Active Directory to run the service under. In this example, we have created 'vmlab\svcC2WTS'. 

2. Add an arbitrary Service Principal Name (SPN) to the service account to expose the delegation options for this account in Active Directory Users and 
 Computers. The SPN can be any format because we do not authenticate to the C2WTS using Kerberos authentication. It is recommended to not use an HTTP SPN to avoid potentially creating duplicate SPNs in your environment. In our example, we have registered 'SP/C2WTS' to the 'vmlab\svcC2WTS' using thefollowing command:

Collapse this tableExpand this table

SetSPN -S SP/C2WTS vmlab\svcC2WTS

3. Configure Kerberos constrained delegation on the C2WTS services account. In his scenario we will delegate credentials to the SQL service running with the 'MSSQLSVC/MySqlCluster.vmlab.local:1433' service principal name.

Key configuration options on the delegation tab are the following (screenshot below):

a) Select “Trust this user for delegation to specified services only"

b) Select “Use any authentication protocol”

External data refresh failed error for PowerPivot\Excel Services

Issue: You get the following error:

An error occurred while working on the Data Model in the workbook. Please try again. We are unable to refresh one or more data connections in this workbook.

Depending on what data provider you are using for your external data connection you will get a different error:

For SQL Native Client:

Failed to create an external connection or execute a query. Provider message: Out of line object 'DataSource', referring to ID(s) '20102481-39c8-4d21-bf63-68f583ad22bb', has been specified but has not been used. OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; SSL Provider: The requested security package does not exist ; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001. , ConnectionName: ThisWorkbookDataModel, Workbook: book1.xlsx.

For Microsoft OLE DB Provider for SQL Server:

Failed to create an external connection or execute a query. Provider message: Out of line object 'DataSource', referring to ID(s) '6e711bfa-b62f-4879-a177-c5dd61d9c242', has been specified but has not been used. OLE DB or ODBC error. , ConnectionName: ThisWorkbookDataModel, Workbook: OLEDB Provider.xlsx.

For .NET Framework Data Provider for SQL Server:

Failed to create an external connection or execute a query. Provider message: Out of line object 'DataSource', referring to ID(s) 'f5fb916c-3eac-4d07-a542-531524c0d44a', has been specified but has not been used. Errors in the high-level relational engine. The following exception occurred while the managed IDbConnection interface was being used: Could not load file or assembly 'System.Transactions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. Either a required impersonation level was not provided, or the provided impersonation level is invalid. (Exception from HRESULT: 0x80070542). , ConnectionName: ThisWorkbookDataModel, Workbook: NETProvider.xlsx.

Resolution: Configure Analysis Services Server in SharePoint mode service account to “Act as operating system” as follows:

1. On the Analysis Services Server running in SharePoint mode, Add the Analysis Services service account to the "Act as part of the operating system" privilege:

a. Run “secpol.msc”

b. Click Local Security Policy, then click Local policies, and then click User rights assignment.

c. Add the service account.

2. Restart Excel Services and reboot the Analysis Services server.

3. Delegation from the Excel Services service account or from Claims to Windows token service (C2WTS) to the Analysis services instance is not required. Therefore no configuration for KCD from Excel Services or C2WTS to PowerPivot AS service is necessary. Note: If the backend data source is on the same server as the Analysis Services instance, delegation is not required.

 

For more information, see Act as part of the operating system

Resources

Comments

  • Anonymous
    July 17, 2015
    Hi Ahmed, I have a Web application in SharePoint 2013 HTTPS. I have powerpivot installed and it is working fine when I try to access the PowerPivot Gallery with the port number. For e.g. https://servername:45176/sites/bi/pp2/Forms/Gallery.aspx. Everything is working fine. But when I try to accesss the same https://servername/sites/bi/pp2/Forms/Gallery.aspx without port number I am getting the error. Appreciate any help or pointer to fix the issue.

"Sorry, something went wrong Could not load type 'Microsoft.AnalysisServices.SPAddin.ReportGallery.ReportGalleryView'. Correlation ID: 4ef61a9d-6ec4-d0c8-efd5-070a86f751a5

CorelationID details as given below. Application error when access /sites/bi/pp/Forms/Gallery.aspx, Error=Could not load type 'Microsoft.AnalysisServices.SPAddin.ReportGallery.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) System.Web.HttpException: Could not load type 'Microsoft.AnalysisServices.SPAddin.ReportGallery.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) Getting Error Message for Exception System.Web.HttpParseException (0x80004005): Could not load type 'Microsoft.AnalysisServices.SPAddin.ReportGallery.ReportGalleryView'. ---> System.Web.HttpParseException (0x80004005): Could not load type 'Microsoft.AnalysisServices.SPAddin.ReportGallery.ReportGalleryView'. ---> System.Web.HttpException (0x80004005): Could not load type 'Microsoft.AnalysisServices.SPAddin.ReportGallery.ReportGalleryView'. Regards Sakti

  • Anonymous
    July 20, 2015
    @Sakti Do you have the right binding in IIS? so both port 80 and port 45176 are pointing to the same web application?

  • Anonymous
    July 20, 2015
    Hi Ahmed, I have resolved this issue. Solution available at following location. social.msdn.microsoft.com/.../powerpivotgallery-view-issue-for-https-web-applicationcould-not-load-type Regards Sakti

  • Anonymous
    July 20, 2015
    Hi Ahmed, I resolved the issue. If web application is running on any other port apart from 443 (as the case here is the we app was running under: https://servername:45176/) then please deploy the solution "powerpivotwebapplication.wsp" on the web application running on port 443. For me it resolve the issue and the PowerPivot Gallery start rendering without the error. Regards Sakti

  • Anonymous
    July 21, 2015
    @Sakti Ok. Make sense. Glad the issue is resolved.

  • Anonymous
    October 01, 2015
    I've been trying to do an in place upgrade of PowerPivot and SSRS from SQL 2008 R2 to SQL 2014. The installation of the bits for the PowerPivot instance on the first farm server (running Central Admin) goes well and after a reboot I then run the PowerPivot Configuration Tool but it fails at step 4/12 because the version of the .NET Runtime is not supported. I've previously added the -version 2.0 switch to my various PowerShell shortcuts but doing the same thing in the scripts that are called by the configuration tool doesn't work and in my opinion shouldn't be necessary. Is there a known workaround for this? N03L