In this section I highlighted few known issues for PowerPivot, PowerView and related technologies like SQL Server Reporting services and Analysis services
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:
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.
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).
Issue: With Office 2010, PowerPivot for Excel is a stand-alone add-in that is downloadable from http://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.
Issue: Complete the following requirements before you install SQL Server Reporting Services (SSRS).
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.
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:
The inner error details will contain a message similar to the following:
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
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.
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
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 Account1. 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”
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=22.214.171.124, 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