Issue
In some scenarios, an Excel Web Access web part won’t display an Excel workbook that will display correctly with Excel Services (xlviewer). Instead of seeing the published workbook contents in the web part, there is just a placeholder icon where the workbook should be displayed, and there are no errors in the web part or on the page.
In this scenario, if you look at the Internet Explorer Developer Tools (IE > Tools > Developer Tools) while you attempt to display an Excel workbook in an Excel Web Access web part, you may see errors like this one “TypeError: o.getName is not a function”.
We have seen this issue with at least these versions of SharePoint 2013:
Cause
This can be caused by an issue with compatibility between the .NET AJAX Control Toolkit and the Excel Web Access web part.
Workaround
Do not use the .NET AJAX Control Toolkit to implement Ajax functionality for a site using Excel Web Access web parts.
We have worked several cases where BISM icons are no longer present in a SharePoint Library. In the below screenshot you can see the icon is a blank square.
We have found that the mapping is no longer present in the DOCICON located in one of the two directories (dependant on the version you are using):
C:\Program Files\Common Files\microsoft shared\Web Server Extensions\14\TEMPLATE\XMLC:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\TEMPLATE\XML
The missing mapping is: <Mapping Key="bism" Value="PowerPivot\icbism.png" />
This points to the icbism.png
You will want to add this to the DOCICON and Save.
Note: You may need to Stop and Start IIS, because the file may have a lock on it.
As you can now see the BISM Icon is now present!
Many customers come to us with questions surrounding the use of BISM files. Primarily why should they use them and how to configure them. In the following I will attempt to answer both questions.
Why:
When you upload a PowerPivot workbook to SharePoint, SharePoint is intelligent enough to create a Data Model under the Analysis Services PowerPivot Instance (If you have an Analysis Services Server in PowerPivot Mode and listed under the Excel Services > Data Model Settings). You now have a Data Model using Microsoft's In Memory X-Velocity technology and all you had to do was upload a PowerPivot workbook to SharePoint! This workbook could hypothetically be up to 2GB in size (SharePoint's file limitation is 2GB) and lets also say you want to create 10 unique reports off that same data. You could re-create 10 identical workbooks in the Excel Rich Client, upload them to SharePoint ,and these workbooks would all have their own Data Models occupying system memory OR you could point these 10 thin (or shim) Excel workbooks to that one already existing Data Model. This is great because you don't have to load 10 individual 2GB Data Models under the PowerPivot Instance.
These BISM files are essentially connection files (like an .odc) to Data Source. If you do not want to grant users certain permission to an actual database, but you want them to be able to build reports in Excel off of data from that database, you can achieve this via BISM files. Users can access this data (Data Model), build reports, and these workbooks will show fresh data daily (as long as you configure a Scheduled Data Refresh to the source PowerPivot workbook).
Lastly, if you click on the BISM file (or choose Create Power View Report), it will load a PowerView Report.
How:
First you need to add the BI Semantic Connection Content Type. To do this, you will need to browse to Library Settings
Click on Advanced Settings
Choose Yes under Allow management of content types?
Click Add from existing site content types
Choose BI Semantic Model Connection and click Add >
Browse to the document library, choose the File tab > New Document > BI Semantic Model Connection
This will open a New BI Semantic Model Connection, name the item and then add the URL of the PowerPivot workbook in SharePoint. In this example, the PowerPivot workbook will be stored in the same location as the BISM file, we merely right clicked on the workbook and chose Properties (http://sp/Shared%20Documents/PowerPivotWorkbook.xlsx):
You now have a BISM file that points to the PowerPivot workbook in SharePoint
You can choose to open this in PowerView via Create PowerView Report
As you can see, this opens a PowerView Report
You can also use this BISM file in Excel, but first you will want to make note of the BISM URL. Right click on the BISM file and choose Properties (http://sp/Shared%20Documents/BISM%20File.bism):
Launch the Excel Rich Client > click the Data tab > From Other Sources > From Analysis Services
Paste the BISM URL (http://sp/Shared%20Documents/BISM%20File.bism) next to Server Name: and click Next >
As you can see, this will find the Data Model located under the Analysis Services PowerPivot Instance
Data Model under the Analysis Services PowerPivot Instance via SQL Management Studio
You can then modify the File Name: and Friendly Name: (I chose "BISM Connection") > Finish
Choose PivotTable Report > OK
You can then build your PivotTable and insert Slicers via PIVOTTABLE TOOLS (make sure you have the PivotTable select so the Contextual Tool Tab is present) > ANALYZE > Insert Slicer
Save and Publish this workbook to SharePoint
You can now open this workbook in Excel Services and interact with the Data Model.
IMPORTANT:
At the moment, there is a bug in the product and you will not be able to interact with the Data Model in Excel Services when pointing to a BISM file built off a PowerPivot workbook stored in SharePoint 2013. You will get the error:
"External Data Refresh Failed
An error occurred during an attempt to establish a connection to the external data source. The following connections failed to refresh:
BISM Connection"
If you run a SQL Profile Trace against this Analysis Services PowerPivot Instance while reproducing this error, you will see the Error "You do not have permission to perform the requested operation."
This was fixed in SQL 2014 CU5:
Cumulative update package 5 for SQL Server 2014 http://support.microsoft.com/kb/3011055
KB Article:
Excel Services workbook fails to refresh using a .bism filehttp://support.microsoft.com/kb/3016518
Issue:
The PowerPivot management dashboard does not show all usage data like it is suppose to and you see that the PowerPivot Management Dashboard Processing Timer Job fails.
You may see errors like:
"Call to Excel Services returned an error"
“Cannot query internal supporting structures for column 'Date'[Rolling52Weeks] because they depend on a column, relationship, or measure that is not processed. Please refresh or recalculate the model”
Verify Issue:
The error information can be generic and not very useful for this issue. You can verify this specific issue by starting SQL Server Management Studio and pointing it to your PowerPivot Service Application database, it is likely on the same SQL Server as you SharePoint 2013 content databases. The database will likely be named something like this "DefaultPowerPivotServiceApplicationDB-57bc03ae-bd41-4102-ab6f-f03201e3b583"
Expand the database in the tree and expand the Views folder.
Right click on Usage.vDate and pick "Select top 1000 rows"
Scroll all the way to the right in the results grid and look at the values for the "Today" column.
If the "Today" column has NULL in it this is our problem (the column should have a valid date in it)
Workaround:
Open a new query window to the PowerPivot Service application database (same as referenced in the above steps)
Run this command:
EXECUTE sp_refreshview N'Usage.vDate';
More Info:
This issue was caused because the table the view references has been modified and the view was put into an invalid state.
I am working with the product group to have SQL updates refresh these views automatically.
We have seen several cases where customers can view an Excel Workbook in the browser, but not in the Excel Web Access Web Part. They are seeing the below error in the Excel Web Access Web Part:
“Excel Services
We don’t know what happened, but
something went wrong.
Could you please try that again?”
When reviewing the ULS logs, we are seeing:
11/24/2014 11:37:37.26 w3wp.exe (0x2864) 0x0BBC SharePoint Foundation Database d0d6 High System.Data.SqlClient.SqlException (0x80131904): The EXECUTE permission was denied on the object 'rbs_fn_get_blob_reference', database 'SharePoint_Content_80', schema 'mssqlrbs'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows) at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more) at System.Data.SqlClient.SqlDataReader.Read() at Microsoft.SharePoint.SPSqlClient.ExecuteQueryInternal(Boolean retryfordeadlock) at Microsoft.SharePoint.SPSqlClient.ExecuteQuery(Boolean retryfordeadlock) ClientConnectionId:932b5260-fed3-44df-9e6a-72292182dfd9 c057cf9c-9345-605f-7c08-0f50fcd1dfd4
This has to do with permissions and to resolve this, grant the following permissions to the Application Pool account on the content database:
db_rbs_admin
db_rbs_filestream_maintaner_1
db_rbs_filestream_reader_1
db_rbs_filestream_writer_1
db_rbs_maintainer
db_rbs_reader
db_rbs_writer
When you first install PowerPivot for SharePoint, there are some configuration tasks that need to be completed to get basic functionality working for the PowerPivot Management Dashboard, as well as to get the groundwork laid out for data refresh and slicing inside of workbooks.
While not difficult to do, these steps are often missed and we see quite a few support cases opened up for these simple configuration issues.
These items are not in any particular order, but all of them are relevant to every installation of PowerPivot for SharePoint 2010 and 2013 (Unless otherwise noted). All of these tasks should be completed after the successful installation of the PowerPivot Analysis Services SQL instance as well as the running of the PowerPivot Configuration Tool on at least one server in the farm successfully.
For this example, I will use screenshots from one of my isolated test farms, so please do not take the names of servers or service accounts literally. They will be different for every scenario depending on your configuration.
Also, I will not be covering Kerberos or any data refresh configuration as part of this article. Nor will I be covering any troubleshooting. This will only cover getting the PowerPivot Management Dashboard into a working state after a fresh install.
After all of this is done, you can test to see if the dashboard will retrieve data when it is available by force running the PowerPivot Management Dashboard Processing Timer Job.
Barring any other unforeseen issues, after a few days of using PowerPivot in your environment, you should start seeing data populated inside of the dashboard. Note that this data is not updated in real time. It utilizes the SharePoint Web Analytics to populate data, so at a minimum you will see updates every 24-48 hours.
Interested in other post configuration tasks? Check out our blog for more!
http://blogs.technet.com/b/excel_services__powerpivot_for_sharepoint_support_blog/
As we all know :) with SharePoint 2013, manual data refresh in the browser (Data > Refresh All Connections) for PowerPivot workbooks will refresh data all the way back from the original PowerPivot data source, which is specified in the PowerPivot workbook's PowerPivot connection. This functionality is different with SharePoint 2010, where manual data refresh in the browser (Data > Refresh All Connections) will refresh data only from the cube (in its current state) that exists for that PowerPivot workbook in the PowerPivot instance of Analysis Services.
The Issue: With SharePoint 2013, we have found that a PowerPivot workbook with a PowerPivot connection to a SharePoint list cannot be refreshed manually in the browser (Data > Refresh All Connections) using Windows authentication in SharePoint 2013.
Many have tried to get this refresh functionality working using a number of different Service Principle Name and constrained delegation settings and configurations, and so far efforts have been unsuccessful with getting delegation to work.
In this scenario, the workaround is to use a Secure Store ID for the workbook list connection, instead of using Windows authentication.
Of course, the SSS ID (mine was PowerPivotUnattendedAccount) will need to have credentials set for it that have permissions to the SharePoint list that is being refreshed from.
Users server has SQL Server 2014 version of PowerPivot
User creates a PowerPivot workbook with Excel, and saves it to SharePoint.
User creates a .bism file that points to the PowerPivot workbook and saves the .bism file to SharePoint.
User creates second workbook that uses the .bism file to point to the first PowerPivot workbook as a data source.
User views the workbook with Excel Services and tries to slice the data.
User gets error "An error occurred during an attempt to establish a connection to the external data source. The following connections failed to refresh."
There is a software defect that prevents .bism files from working for this use case.
Instead of creating a .bism file, create a .odc file and use that.
How to create .odc file and data connection library
http://msdn.microsoft.com/en-us/library/office/ms772101(v=office.14).aspx
You may see a "Call to Excel Services failed" error when trying to perform a scheduled data refresh using PowerPivot for SharePoint on a workbook in SharePoint 2013 that has a Power Query connection to a data source. At this time, Power Query is not supported by SharePoint 2013 on-premise. It is only supported by Power BI for Office 365 (SharePoint Online).
http://blogs.msdn.com/b/powerbi/archive/2014/05/02/scheduled-data-refresh-for-power-query.aspx
As a workaround, you can connect to the data source without using Power Query by creating a data connection using PowerPivot. To determine whether or not the workbook currently has a Power Query data connection, look at the connection string in Excel by going to Data > Connections > Properties > Definition. If the Provider is Microsoft.Mashup.OleDb.1, this indicates that the data connection was made using Power Query.
You may receive the following error when running a scheduled data refresh on a PowerPivot workbook in SharePoint 2013 running PowerPivot for SharePoint:
Could not find the schedule for this work item
This error occurs when the workbook is checked out when the scheduled data refresh runs. Scheduled data refresh for workbooks that are checked out is not supported. The following article has further details: http://msdn.microsoft.com/en-us/library/ee210651.aspx It states that the workbook must be checked in at the time the refresh operation is finished. If the file is checked out to another user, the refreshed data will be thrown out.
I have seen several cases where the background\parts of Excel workbooks are turning black:
Here is an example in the browser:
Here is an example from the PowerPivot Gallery:
From my experience the W3WP.exe is growing in size and this causes issues with performance. Many times this is the result of a corrupt workbook. I like to think of it this way; imagine opening a corrupt workbook on your machine in the Excel rich client. If that workbook hangs, you will notice the Excel.exe process slowly growing in size when looking at Task Manager. You will also experience performance issues on your machine. The only way to close Excel is to kill (End Process) the Excel.exe in Task Manager. If this happen on the server, the ExcelService.asmx will be trying to open this under the W3WP.exe. You will see that the W3WP.exe is slowly growing in size until it affects the server performance. Sometimes I am lucky and can find a corrupt workbook in the ULS logs, but this can be very difficult because performance issues can occur many hours after the workbook was opened.
05/20/2014 18:51:00.10 w3wp.exe (0x1A1C) 0x279C Excel Services Application Excel Calculation Services 2021 Information Failed to load 'http://SharePoint.Contoso.com/sites/Documents/Services/Operations/Workbook.xlsx' with error: 'The workbook is corrupt.'. [Session: 1.V22.891OcC38Mp/PRDHw7Ze1tF90.5.en-US5.en-US73.+0360#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.b06179e8-2e64-440c-99a6-ca7f9f8d93161.N User: User@contoso.com] 3a9bffee-c087-48fb-b34e-8d968bdc6f24
If you find this corrupt workbook (note: there could be more than one) and remove/rebuild it, the issue will go away. If you cannot locate this workbook (again, you may need to dig through massive ULS logs), you will need to Recycle the Excel Services Application Pool.
If you recycle the application pool daily (after hours), a growing W3WP.exe will be reset and you should be able to avoid see performance issues.
Here are instructions on how to recycle the application pool running Excel Services.
In IIS, you will need to expand “SharePoint Web Services” (make sure you are using “Content View”). You will need to click on each GUID under “SharePoint Web Services” until you see the “ExcelService.asmx”, once you find this (special note, if you have multiple “Excel Services Service Applications”, there will be multiple GUIDs with the ExcelServices.asmx under it, you will need to either delete one of the “Excel Services Service Applications” or set both to Recycle), click on “Advanced Settings…”. Here you will see the “Application Pool” once you find the GUID (in this case 9bf574c7cfc04e7e9a8595ec2e887fb8), you will know which Application Pool to Recycle.
Go to Application Pools > Right Click on 9bf574c7cfc04e7e9a8595ec2e887fb8 > Advanced Settings…
You can set the time you want to Recycle under Recycling > Specific Times
Scenario:
You get the following error while running a scheduled data refresh on a PowerPivot workbook:
Problem:
The "Require Check Out" option inside of the Document Library Settings > Versioning Settings of any given document library is likely set to "Yes":
Resolution:
PowerPivot does not attempt to check out documents when it makes its modification, therefore you will need to turn this "Require Check Out" option to "No" for scheduled data refresh to work on the document library in question.
This case may be a one-off, but I wanted to write about it in the event someone else comes across it in the future.
Here is the synopsis. A user was creating an Excel workbook in the rich client using "Use Windows Authentication":
When you do that, the connection string looks like:
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=AdventureWorks2012;Data Source=Data;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=RICKCLIENT;Use Encryption for Data=False;Tag with column collation when possible=False
The Security Support Provider Interfacehttp://technet.microsoft.com/en-us/library/bb742535.aspx
The company had a group of Authors with Role Based permissions to the SDL Data. The Authors were able to connect to the data source and build the workbooks using their Logon Credentials. However, the Authors were changing the "Excel Services Authentication Settings" to "Use a stored account Application ID:" prior to publishing to SharePoint. The reasoning behind this made complete sense, the Members of the Secure Store Application ID did not have Role Based permissions to the SQL Data. The "Set Credentials" the account was a SQL account that did have access to the SQL Data. See below how it was built.
That Application ID "Fargo" was using SQL Credentials. This is how they built it:
Now when they use a workbook that was created in Excel using "Use Windows Authentication" with a Secure Store Service Application ID that uses SQL Credentials, things go wacky. We noticed that the account running Excel Services needs to be given permissions in SQL.
BUT, if the workbook was created (In Excel Rich Client) using "Use the following UserName and Password", "Integrated Security=SSPI" is not present and the SQL "UserID=XXXXX" is saved in the Connection String and there is no further action required, the refresh in Excel Services completes without error.
Provider=SQLOLEDB.1;Persist Security Info=True;User ID=EddieVedder;Initial Catalog=AdventureWorks2012;Data Source=Data;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=AMENTCLIENT;Use Encryption for Data=False;Tag with column collation when possible=False
Analysis:
This is design. You must have the workbook created (in the Excel Rich Client) with the same Authentication Type as the Secure Store Service. So if the Secure Store Service is holding SQL User ID and Password for SQL Auth, the workbook must be using SQL Auth when it is built, if you see SSPI in the connection string that means that Windows Auth Mode is used. A connection can only use one AuthMode, either SQL Auth or Windows Auth.
If you have a workbook with a Power View worksheet that was created in Excel, and you upload that workbook to the PowerPivot Gallery in SharePoint, you may receive a red X instead of a thumbnail because of a failure with the snapshot generation:
This is expected behavior as the snapshot process does not support embedded Power View reports in workbooks.
One workaround is to only publish all worksheets except for the Power View worksheets when uploading the workbook to the PowerPivot Gallery. Click on Save As in Excel when saving to SharePoint and you will get a Browser View Options button in the Save As dialog box:
Click on Browser View Options, and on the Show tab, select the dropdown menu and change Entire Workbook to Sheets. This will allow you to uncheck the Power View worksheet:
Click OK and continue to save the workbook. The Power View worksheet will not be displayed in the PowerPivot Gallery thumbnails or in Excel Services when you open the workbook, but it will still be visible when you open the workbook in Excel client. This allows the thumbnails for the rest of the worksheets to be generated successfully in the PowerPivot Gallery, but you will lose the ability to view the Power View report in the browser. As you can see below, the third worksheet, which is the Power View worksheet, is not shown in the workbook thumbnail, and is not part of the worksheet thumbnails:
Another option is to place the Power View report as the last worksheet in the workbook, and then display the first worksheet before saving the file. This will allow snapshot generation of all worksheets before the Power View worksheet as the worksheets are enumerated in order during the snapshot process. As you can see below, the workbook snapshot shows that there are 3 worksheets, but only thumbnails are only generated for the first 2 worksheets as the third one is a Power View worksheet:
The advantage of this option is that the Power View worksheet is published to SharePoint, so even though the thumbnail is not generated, when you open the workbook in Excel Services, the third worksheet is visible and the Power View report is rendered.
In order to view a Power View report thumbnail, you will need to use Power View for SharePoint to create the Power View report instead of creating a Power View worksheet in Excel. To do this, upload the PowerPivot workbook to the PowerPivot Gallery. Next to the thumbnail for the workbook, you have a few icons, one of which allows you to create a standalone Power View report in RDLX format based on the PowerPivot workbook. When you hover over the button it says Create Power View Report:
After building the report and saving it to the PowerPivot Gallery, a thumbnail will be generated because in this case, Power View generates the thumbnail instead of PowerPivot using a different snapshot generation process:
Please note that the Power View feature for SharePoint is only available with SQL Server 2012 SP1 and above as part of the Reporting Services add-in for SharePoint.
Issue Overview:
Post migration of a working PowerPivot system from Windows Authentication to Claims Authentication sometimes not all workbook files author property is updated to the new format, this causes the "PowerPivot Management Dashboard Processing Timer Job" to fail with error:
“Microsoft.SharePoint.SPException: User cannot be found” at Microsoft.AnalysisServices.SPAddin.UsageProcessingTimerJob.PrepareUsageData
Some or all of your PowerPivot files like have their author property set in the old Windows style name like “MYDOMAIN\someuser”
If the author property was set to a claims format the error would go away, like “i:0#.w|MYDOMAIN\someuser”
The workaround for this issue is to delete and re-upload the PowerPivot workbooks affected.
The author field can not be fully examined from the SharePoint UI, so it can be difficult to track down all of these workbooks.
Attached to this post (very bottom) is sample code that shows one way to list all of the workbooks for a PowerPivot service application that have an author property in the Windows style format (these files will throw and error when the "PowerPivot Management Dashboard Processing Timer Job" runs).
** THIS SAMPLE CODE IS PROVIED AS IS, WITH NO WARRANTEES OR SUPPORT OF ANY KIND, USE AT YOUR OWN RISK **
Rename PPTimerUserError.bak to PPTimerUserError.exe so you can run the program (.bak so zip scanners don't block the file)
The .exe must be ran from one of the farm SharePoint 2013 servers
The output is tab delimited so you can open with Excel to sort and filter as needed
useage: PPTimerUserError <DB Server> <PowerPiviot DB Name>
example: PPTimerUserError warrenr-ws1 DefaultPowerPivotServiceApplicationDB-57bc03ae-bd41-4102-ab6f-f03201e3b583
output at C:\temp\PPTimerAuthorReport.txt and PPTimerAuthorReportDetails.txt
Update
This PowerShell code is known to update the author field if you do not want to remove and re-add the affected files,
**USE AT YOUR OWN RISK **
$web = get-spweb http://reports2013/analytics
$list = $web.lists['Reports']
$user = get-spuser -Web $web -limit all | ? {$_.userlogin -match "SPAdmin"}
$sUser = $user.id.ToString() + ';#' + $user.DisplayName
$listItems = $list.Items
write-host $listItems[0]["Author"]
write-host $listItems[1]["Author"]
write-host $listItems[2]["Author"]
$li = $listItems[1]
$li["Author"] = $sUser
$li.properties["vti_author"] = $user.loginname
$li["Editor"] = $sUser
$li.UpdateOverwriteVersion()
write-host $li["Author"]
This issue with the email notifications was fixed with Cumulative update package 7 for SQL Server 2012 SP1, but we are seeing cases where the email notifications still do not happen, even after installing CU7 for SQL Server 2012 SP1.
Cause:
The PowerPivot for SharePoint update is not complete until the PowerPivot Configuration Tool is run.
Run the PowerPivot for SharePoint Configuration Tool to complete the CU7 update for PowerPivot for SharePoint, and then the email notifications will begin working.
Cumulative update package 7 for SQL Server 2012 SP1
http://support.microsoft.com/kb/2894115
Summary:
If the Excel Services global option "Use the EffectiveUserName" is turned on, then all workbooks that use secure store service ID's (SSID) will not refresh data and can not create connections to the SSAS data sources. So effectively the SSID authentication option is not useable if the EffectiveUserName global option is turned on.
ULS entry:
ExternalSource.ValidateConnection: Unable to get a connection: ... Microsoft.Office.Excel.Server.CalculationServer.ExternalSource.TryGetValidatedConnection
Status:
The product group is investigating the issue right now, there is an estimated fix date of November 2014.
If you are using a Power View Sheet and it is not rendering in the browser (see below screenshot):
You will want to verify if you are using Office Web Apps Server 2013 (WopiFrame.aspx) to render the workbook by examining the URL:
URL Example:
http://servername/sites/BICenter/_layouts/15/WopiFrame.aspx?sourcedoc=%2Fsites%2FBICenter%2FTest%20PowerPivot%20Gallery%2FPower%2Exlsx&action=view
If you are indeed using the WopiFrame.aspx, you will want to flip this (via the SharePoint 2013 Management Shell) to use the ExcelService.asmx via:
New-SPWOPISuppressionSetting -extension xlsx -action view
In the below article, you will see that Power View Sheets are not visible in Office Web Apps 2013 (WopiFrame.aspx):
Power View in Excel on Office 365 or in SharePoint Serverhttp://office.microsoft.com/en-us/excel-help/power-view-in-excel-on-office-365-or-in-sharepoint-server-HA103276078.aspx
Additional Information:
To verify the Suppression, run this command in the SharePoint 2013 Management Shell:
Get-SPWOPISuppressionSetting
It should return: XLSX VIEW
To remove this Suppression run this command in the SharePoint 2013 Management Shell:
Remove-SPWOPISuppressionSetting -extension xlsx -action view
To understand the above Office Web Apps SharePoint 2013 Management Shell Commands refer to the below article:
Windows PowerShell for Office Web Apps (SharePoint 2013)http://technet.microsoft.com/en-us/library/jj219457(v=office.15).aspx
You may encounter PowerPivot data refresh schedules getting automatically disabled in SharePoint 2013. If a schedule gets disabled, you may see one of the following messages when viewing the Data Refresh History page:
The schedule was disabled due to consecutive failures.
The schedule was disabled because the workbook is inactive.
There a couple settings in the PowerPivot service application settings that can cause schedules to be come disabled due to the reasons stated in the messages above. Here is a screenshot of those settings that you can access in SharePoint Central Administration > PowerPivot Management Dashboard > Configure service application settings:
The first settings is Disable data refresh due to consecutive failures and the default value is 10. This means that if a data refresh schedule encounters 10 consecutive failures, the schedule will be disabled. The second setting is Disable data refresh for inactive workbooks and the default value is also 10. This means if a workbook has not been accessed by a user for 10 days, the schedule will be disabled.
You can specify a different value for these settings. To prevent the data refresh schedule from becoming disabled automatically due to failures or inactivity, specify a value of 0 for these settings.
You may receive the following error when running a scheduled data refresh on a PowerPivot workbook in SharePoint 2013 running PowerPivot for SharePoint using SQL PowerPivot 2012 SP1:
A schedule cannot be enabled for a workbook with no external data sources.
This can happen when a PowerPivot workbook created in Excel 2010 using the PowerPivot 2012 add-in is used in SharePoint 2013, and the Excel Services Authentication Setting in the data connection properties is set to anything other than Windows Authentication:
If you upload the workbook to SharePoint 2013 and you go to the workbook's Manage Data Refresh page from the PowerPivot Gallery, you will receive the error. This is because the SSS and None options cannot be used in this scenario. Change the setting to Windows Authentication and save the workbook back to SharePoint to fix the error and enable the scheduled data refresh for the workbook.
PowerPivot for SharePoint scheduled data refresh does not work for the first day of the month, an error is generated every two minutes. ULS shows:
PowerPivot Service Data Refresh 99 High EXCEPTION: Microsoft.AnalysisServices.SharePoint.Integration.DataRefreshException: The data refresh job failed because it did not run within the time period allotted for running this schedule
This looks to be a software defect.
If you refresh the workbook daily, the first day of the month will refresh fine.
If you refresh on the second day of the month the refresh will work.
The product group is looking into this issue and it may be resolved in a future release.
You may see the following error in Windows Event Viewer or the SharePoint ULS logs:
Cannot open database "<Database name of PowerPivot Service application>" requested by the login. The login failed. Login failed for user '<username>'.
This is an indication of an orphaned record regarding the PowerPivot service application database in SharePoint. This may happen after deleting a PowerPivot service application. The records for the deleted PowerPivot service application database are sometimes not removed from the the SharePoint configuration database, so SharePoint throws an error when it cannot open the PowerPivot service application database since it no longer exists.
The solution to this is to remove these orphaned records. Open the SharePoint Management Shell on a SharePoint server and run the following command:
Get-SPDatabase | where {$_.exists -eq $false}
This will display a list of the databases that no longer exist on the SharePoint database server. The PowerPivot database referenced in the error above should show up in this list. After confirming this, you may remove the orphaned record by running this command:
Get-SPDatabase | where {$_.exists -eq $false} | foreach {$_.delete()}
At this point, the orphaned record pointing to the old PowerPivot service application database will be removed, and the error that you noticed earlier will stop occurring.
I have seen the below errors that required a server reboot to fix the issue:
Error in GUI:
Error in ULS:
03/06/2014 17:43:45.29 w3wp.exe (0x3B38) 0x574C Excel Services Application Excel Calculation Services 89rs Exception ExcelServerApp..ctor: An unhandled exception occurred during boot. Shutting down the server. Microsoft.Office.Excel.Server.CalculationServer.Interop.LoadDllException: Failed to load engine dll.Acceso denegado. at Microsoft.Office.Excel.Server.CalculationServer.Interop.EngineInterop.LoadDll(EngineFlags engineFlags, String binPath, String muiPath, String ppPath, IRequestTelemetryEvents requestTelemetryEvents) at Microsoft.Office.Excel.Server.CalculationServer.ExcelServerApp.Initialize() at Microsoft.Office.Server.Diagnostics.FirstChanceHandler.ExceptionFilter(Boolean fRethrowException, TryBlock tryBlock, FilterBlock filter, CatchBlock catchBlock, FinallyBlock finallyBlock) StackTrace: at Microsoft.Office.Server.Native.dll: (sig=35339f9c-8871-4ef1-9118-9c1bd0cb6598|2|microsoft.office.server.native.pdb, offset=131CE) at Microsoft.Office.Server.Native.dll: (offset=21B4D) 47bf7a9c-949d-c0e3-278b-d714155b5356
03/03/2014 16:46:13.09 w3wp.exe (0x3EA4) 0x4120 Excel Services Application Data Model 27 Monitorable Uncaught CLR exception crossing the Interop boundary: Microsoft.AnalysisServices.Streaming.ServerNotFoundException: No eligible servers meet the operation's requirements. ---> System.InvalidOperationException: Sequence contains no matching element at System.Linq.Enumerable.First[TSource](IEnumerable`1 source, Func`2 predicate) at Microsoft.AnalysisServices.Streaming.OnPremise.MemoryBasedLoadBalancer.BeginOperation(OperationRequirements requirements, IEnumerable`1 availableServers) --- End of inner exception stack trace --- at Microsoft.AnalysisServices.Streaming.OnPremise.MemoryBasedLoadBalancer.BeginOperation(OperationRequirements requirements, IEnumerable`1 availableServers) at Microsoft.AnalysisServices.Streaming.OnPremise.LoadBalancer.GetLoadBalancedOperation(Func`1 onBegin) at Microsoft.AnalysisServices.Streaming.OnPremise.Database.Loading.Create() at Microsoft.AnalysisServices.Streaming.OnPremise.ServerPool.<>c__DisplayClass9.<Microsoft.AnalysisServices.Streaming.OnPremise.IServerPool.CreateConnection>b__8(Guid id) at Microsoft.AnalysisServices.Streaming.Internal.KeyedReferenceCountedEnumeration`2.Node.<>c__DisplayClassc.<.ctor>b__a() at System.Lazy`1.CreateValue() at System.Lazy`1.LazyInitValue() at Microsoft.AnalysisServices.Streaming.Internal.KeyedReferenceCountedEnumeration`2.Node.GetReference() at Microsoft.AnalysisServices.Streaming.Internal.KeyedReferenceCountedEnumeration`2.GetOrCreateReference(TKey key, Func`2 valueFactory) at Microsoft.AnalysisServices.Streaming.OnPremise.ServerPool.Microsoft.AnalysisServices.Streaming.OnPremise.IServerPool.CreateConnection(Guid imageId, Func`1 allocationFactory) at Microsoft.AnalysisServices.Streaming.OnPremise.Database.Loading.EstablishConnection() at Microsoft.AnalysisServices.Streaming.OnPremise.Database.ConnectWithoutOperationSetup() at Microsoft.AnalysisServices.Streaming.OnPremise.Database.Microsoft.AnalysisServices.Streaming.IDatabase.Connect(DatabaseConnectionProperties props) at Microsoft.AnalysisServices.Streaming.Interop.StreamingServerPoolModule.Microsoft.AnalysisServices.Streaming.Interop.IStreamingConnector.Connect(SSPM_CONNPROPS in_connprops, SSPM_CALLBACKS in_callbacks) d1c4799c-d568-c0a0-395b-bd0ecb4a84f0
When in doubt, reboot.
I have seen several customers who have custom farm solutions deployed to their farm (Central Administration > System Settings > Manage farm solutions). Some of these are for document libraries. I have noticed the below error several times when opening a workbook from this location(s).
"Couldn't Open the Workbook
The workbook cannot be opened"
From what I have discovered, you need the solution to be installed on the same machine that is running Excel Services. This currently is design. It may change in the future.
If these Solutions are not on the machine(s) running Excel Services the workbooks will fail to open and you will see the below errors in the ULS Logs:
01/17/2014 10:47:40.49 w3wp.exe (0x19E8) 0x2B50 SharePoint Foundation Object Cache ai107 Medium Failed on try1 to load XML document at path 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\Template\Features\TeamSite\feature.xml': System.IO.DirectoryNotFoundException: Could not find a part of the path 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\Template\Features\Site\feature.xml'. at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share) at Microsoft.SharePoint.SPXmlDocCache.<>c__DisplayClass2.<GetGlobalXmlDocumentFullPath>b__0() 7b6b3d63-1900-43f6-8ffb-6a7a7b69f0a4
01/17/2014 10:47:40.49 w3wp.exe (0x19E8) 0x2B50 Excel Services Application Excel Calculation Services tufa Medium SharePointFileLoader.GetSPFile: Sharepoint threw a handled exception - turning it into a FileOpen exception. Exception is: System.ArgumentException: Feature 'b4c5a5b0-514e-4c27-8fd5-d2c323118eac' for list template '101' is not installed in this farm. The operation could not be completed. at Microsoft.SharePoint.SPFeatureManager.<>c__DisplayClass19.<GetFeatureRootAndListSchemaPaths>b__18() at Microsoft.SharePoint.SPSecurity.RunAsUser(SPUserToken userToken, Boolean bResetContext, WaitCallback code, Object param) at Microsoft.SharePoint.SPFeatureManager.GetFeatureRootAndListSchemaPaths(Byte[]& userToken, Guid& tranLockerId, Int32 nZone, Guid databaseid, Guid siteid, Guid webid, Guid featid, Int32 ltid, String& sPathToFeatureRoot, String& sPathToSchemaXml) at Microsoft.SharePoint.Library.SPRequestInternalClass.GetListItemDataWithCallback2(IListItemSqlClient pSqlClient, String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pPagingPrevCallback, ISPDataCallback pFilterLinkCallback, ISPDataCallback pSchemaCallback, ISPDataCallback pRowCountCallback, Boolean& pbMaximalView) at Microsoft.SharePoint.Library.SPRequest.GetListItemDataWithCallback2(IListItemSqlClient pSqlClient, String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pPagingPrevCallback, ISPDataCallback pFilterLinkCallback, ISPDataCallback pSchemaCallback, ISPDataCallback pRowCountCallback, Boolean& pbMaximalView) at Microsoft.SharePoint.SPListItemCollection.EnsureListItemsData() at Microsoft.SharePoint.SPListItemCollection.get_Count() at Microsoft.SharePoint.SPWeb.GetItem(String strUrl, Boolean bFile, Boolean cacheRowsetAndId, Boolean bDatesInUtc, String[] fields) at Microsoft.SharePoint.SPFile.get_Item() at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.GetSPFile(IClaimsIdentity claimsIdentity) 7b6b3d63-1900-43f6-8ffb-6a7a7b69f0a4
01/17/2014 10:47:40.49 w3wp.exe (0x19E8) 0x2B50 Excel Services Application Excel Calculation Services ecg6 Verbose ExcelServiceBase.PostProcessRequest: Called with ex=Id=GenericFileOpenError; Microsoft.Office.Excel.Server.CalculationServer.FileOpenException: The workbook cannot be opened. ---> Microsoft.Office.Excel.Server.Host.HostFileException ---> System.ArgumentException: Feature 'b4c5a5b0-514e-4c27-8fd5-d2c323118eac' for list template '101' is not installed in this farm. The operation could not be completed. at Microsoft.SharePoint.SPFeatureManager.<>c__DisplayClass19.<GetFeatureRootAndListSchemaPaths>b__18() at Microsoft.SharePoint.SPSecurity.RunAsUser(SPUserToken userToken, Boolean bResetContext, WaitCallback code, Object param) at Microsoft.SharePoint.SPFeatureManager.GetFeatureRootAndListSchemaPaths(Byte[]& userToken, Guid& tranLockerId, Int32 nZone, Guid databaseid, Guid siteid, Guid webid, Guid featid, Int32 ltid, String& sPathToFeatureRoot, String& sPathToSchemaXml) at Microsoft.SharePoint.Library.SPRequestInternalClass.GetListItemDataWithCallback2(IListItemSqlClient pSqlClient, String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pPagingPrevCallback, ISPDataCallback pFilterLinkCallback, ISPDataCallback pSchemaCallback, ISPDataCallback pRowCountCallback, Boolean& pbMaximalView) at Microsoft.SharePoint.Library.SPRequest.GetListItemDataWithCallback2(IListItemSqlClient pSqlClient, String bstrUrl, String bstrListName, String bstrViewName, String bstrViewXml, SAFEARRAYFLAGS fSafeArrayFlags, ISP2DSafeArrayWriter pSACallback, ISPDataCallback pPagingCallback, ISPDataCallback pPagingPrevCallback, ISPDataCallback pFilterLinkCallback, ISPDataCallback pSchemaCallback, ISPDataCallback pRowCountCallback, Boolean& pbMaximalView) at Microsoft.SharePoint.SPListItemCollection.EnsureListItemsData() at Microsoft.SharePoint.SPListItemCollection.get_Count() at Microsoft.SharePoint.SPWeb.GetItem(String strUrl, Boolean bFile, Boolean cacheRowsetAndId, Boolean bDatesInUtc, String[] fields) at Microsoft.SharePoint.SPFile.get_Item() at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.GetSPFile(IClaimsIdentity claimsIdentity) --- End of inner exception stack trace --- at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.GetSPFile(IClaimsIdentity claimsIdentity) at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.CheckForPermissions(IClaimsIdentity claimsIdentity) at Microsoft.Office.Excel.Server.MossHost.MossHostHelperMethods.WithEnsureClaimsIdentitySetOnThread(IClaimsIdentity claimsIdentity, MethodToRun action) at Microsoft.Office.Excel.Server.MossHost.MossHostHelperMethods.TryExecuteWithUserContext(IIdentity userIdentity, Action`1 method) at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.Init(Uri uri, Guid requestSiteId, IIdentity currentIdentity, ScenarioRestrictions restrictions, IExcelServerDocumentContext documentContext, FileLoaderHostInfo& outFileLoaderHostInfo) at Microsoft.Office.Excel.Server.CalculationServer.SharePointFileLoader.Init(Uri uri, Guid requestSiteId, ScenarioRestrictions restrictions, IExcelServerDocumentContext documentContext) --- End of inner exception stack trace --- at Microsoft.Office.Excel.Server.CalculationServer.SharePointFileLoader.Init(Uri uri, Guid requestSiteId, ScenarioRestrictions restrictions, IExcelServerDocumentContext documentContext) at Microsoft.Office.Excel.Server.CalculationServer.FileLoader.CreateFromTrustedLocationAndInit(Uri uri, TrustedLocation trustedLocationSettings, Guid requestSiteId, ScenarioRestrictions restrictions, IExcelServerDocumentContext documentContext) at Microsoft.Office.Excel.Server.CalculationServer.BaseWorkbookManager.CreateAndInitFileLoaderForWorkbookLoad(Uri uri, Boolean newWorkbook, TrustedLocation trustedLocationSettings, Guid requestSiteId, ScenarioRestrictions restrictions, IExcelServerDocumentContext documentContext) at Microsoft.Office.Excel.Server.CalculationServer.BaseWorkbookManager.GetBaseWorkbookAndMarkUsedAsync(AsyncHandler`1 callback, Object userState, Request request, Uri uri, Uri uriForKey, Boolean newWorkbook, Boolean useCollection, OpenWorkbookFlags openWorkbookFlags, ScenarioRestrictions restrictions, SessionId previousSessionId, TimeZoneInformation timeZone) at Microsoft.Office.Excel.Server.CalculationServer.Session.OpenWorkbookAsync(AsyncHandler`1 callback, Object userState, Request request, Uri url, OpenWorkbookFlags openWorkbookFlags, ScenarioRestrictions restrictions, SessionId previousSessionId, TimeZoneInformation timeZoneInfo) at Microsoft.Office.Excel.Server.CalculationServer.Operations.OpenWorkbookOperation.StartExecution() at Microsoft.Office.Excel.Server.CalculationServer.Operations.Operation.RunOperationAsync() at Microsoft.Office.Excel.Server.CalculationServer.Operations.OperationSite.PrepareComplete(PrepareAsyncArgs args) 7b6b3d63-1900-43f6-8ffb-6a7a7b69f0a4
To set up PowerPivot for SharePoint scheduled data refresh for a PowerPivot workbook that has a connection to a SharePoint list in it, you do not need to use a data connection file like a List.atomsvc file (you do not need to use Export as Data Feed).
Another way that you can set up PowerPivot for SharePoint scheduled data refresh for a PowerPivot workbook that has a connection to a SharePoint list in it, is to make an OData data feed connection directly to the SharePoint list data service (and skip the List.atomsvc file altogether).
Here are some steps that I used that worked for me (using SharePoint 2013 and PowerPivot for SharePoint 2012 SP1).
Create a new Secure Store Service target application ID
make it type Group
add the domain group called Domain Users to the Members group for the ID
set the ID’s credentials to a domain account and password that for sure has permissions to access the SharePoint list that we will be refreshing from (TIP: to start with, use the domain account and password that you will be logged on with on the machine where you will be launching the Excel client application and creating the Excel workbook in the next steps).
In Central Administration, edit the PowerPivot service application settings and set the PowerPivot Unattended Data Refresh Account to use the Secure Store Service target application ID created above.
Create a new workbook with Excel 2013, click on the PowerPivot add-in tab at the top, then click Manage on the far left.
Then choose From Data Service > From OData Data Feed
Then type in the Data Feed Url like this: http://YourSPservername/_vti_bin/listdata.svc and then click Next to connect to the service and view the items that appear.
Then select your SharePoint list and click Finish then Close.
Then set up your workbook the way that you would like.
For mine, I clicked on the PivotTable button at the top, I chose Existing worksheet, selected a PivotTable field called Value so that my PivotTable had some data, clicked on the Insert tab and added a slicer.
Then set the workbook’s connection authentication setting to use the Secure Store Target Application ID created above by going to the Data menu at the top > Connections > Properties > Definition tab > Authentication Settings > select Use a stored account > and type in the name of the ID that you created above.
Then test to make sure that both slicers and manual data refresh (Data > Refresh All Connections) work in the Excel client application, and then save the file to the PowerPivot gallery in SharePoint and open it in the browser by clicking on it.
Make sure that slicers work in the browser.
Make sure that manual data refresh works in the browser by clicking Data > Refresh All Connections.
Then manage the data refresh for the workbook by clicking on the calendar icon for the workbook when viewing it in the PowerPivot gallery.
Click Enable, click Also refresh as soon as possible, make sure that the setting below called Use the data refresh account configured by the administrator is selected, then click OK.
Then manage the data refresh for the workbook again by clicking on the calendar icon for the workbook again, and then refresh the page a few times using F5 to confirm that the scheduled refresh for the workbook runs and succeeds (refresh can take up to one minute to begin).
Assuming that the rest of your PowerPivot for SharePoint 2013 environment is set up well, PowerPivot scheduled data refresh from a SharePoint list should work in this configuration.
For steps on how to set up PowerPivot for SharePoint scheduled data refresh using a data connection file like a List.atomsvc file (Export as Data Feed), see:
Excel Services - Using a SharePoint List as a data source
http://blogs.technet.com/b/excel_services__powerpivot_for_sharepoint_support_blog/archive/2013/07/11/excel-services-using-a-sharepoint-list-as-a-data-source.aspx