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: 

• 15.0.4420.1017
• 15.0.4569.1000

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\XML
C:\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 file
http://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.

• Ensure that the spPowerPivot.msi has been installed on all servers in the farm. Note that this will provision the PowerPivot configuration tool on all servers, but you do not have to run it. The main reason here is to install the ADOMD.NET drivers that are relevant to your install. You can also download the ADOMD.NET drivers from the SQL Server feature pack that is relevant to the version of SQL you are using for your PowerPivot AS instance.
  • Note that the version of the spPowerPivot.msi and\or ADOMD.NET drivers you install will depend on your version of PowerPivot AS. For instance, if you are using SQL 2014, the SQL 2012 SP1 version of the msi will not install the proper drivers.
    • SQL 2014 Feature Pack: http://www.microsoft.com/en-us/download/details.aspx?id=42295
    • SQL 2014 PowerPivot Add-in for Sharepoint: http://www.microsoft.com/en-us/download/details.aspx?id=42300
    • SQL 2012 SP1 Feature Pack: http://www.microsoft.com/en-us/download/details.aspx?id=35580
    • SQL 2012 SP1 PowerPivot Add-in for SharePoint: http://www.microsoft.com/en-us/download/details.aspx?id=35577
    • SQL 2012 SP2 PowerPivot Add-in for SharePoint: http://www.microsoft.com/en-us/download/details.aspx?id=43342
    • SQL 2008 R2 SP1 Feature Pack: http://www.microsoft.com/en-us/download/details.aspx?id=26728
    • Note that SQL 2008 R2 does not have a PowerPivot add-in as it is required to set up the PowerPivot AS instance on one of the SharePoint servers. The "add-in" is implemented into the farm as part of the installation of the instance.

• Define your PowerPivot instance (SharePoint 2013 ONLY)
  • We need to tell SharePoint which PowerPivot AS instance we are using to host data models. In Central Administration, navigate to your Excel Services service application and click on "Data Model Settings".
    • Add your PowerPivot instance here in the "servername\PowerPivot" format:
      
      In this example, my PowerPivot instance is hosted on a server named "Reporting", therefore my PowerPivot AS instance is denoted as such: "servername\PowerPivot"

• Define your unattended account for PowerPivot.
  • During the running of the configuration tool, it creates an unattended data refresh account inside of the Secure Store service. Within the scope of PowerPivot, this account is used to populate the information inside of the PowerPivot Management Dashboard. As this ID is already configured, all we need to do is define a service account to be stored in the ID. This service account must have at least "db_reader" permissions on the PowerPivot Service Application database. This database is located with the rest of your service application databases on whatever instance stores your SharePoint databases. Unless you explicitly changed its name during configuration, it can usually be identified by the service application name followed by a GUID just like in the screenshot below:
    
    

• • • To define this account, navigate to your Secure Store Service application in Central Administration.
      • Find the Secure Store ID labeled "PowerPivotUnattendedAccount"
      • Hover your mouse over the ID and click the dropdown menu.
      • Click "Set Credentials"
      • Enter a farm account for a Credential Owner if there isn't one present.
      • Enter the desired data access account in the "Windows User Name" field in a "domain\username" format.
      • Enter and confirm the password.
      • Click "OK"
    • Navigate to your PowerPivot Service application and click "Configure service application settings"
  • • Find the "PowerPivot Unattended Data Refresh Account" field, and ensure that the "PowerPivotUnattendedAccount" is listed. If it isn't, enter it here, and then click "OK"
      
      

• If you are using different service accounts for all of your services, you may need to run the following PowerShell commands to give your Excel Services account access to Central Admin. This will allow the service to render workbooks in the browser.
  • Run the following from an administrator enabled SharePoint 2013 Management Shell:
    • $w = Get-SPWebApplication –Identity <URL of the Central Administration>
    • $w.GrantAccessToProcessIdentity("<insert Excel Services service account here>")
      
      
• Make the Excel Services and PowerPivot service accounts Administrators on the PowerPivot Analysis Services instance. This is required for the appropriate services to create and manage data models on the instance.
  • Open up SQL management Studio and connect to your PowerPivot AS instance.
  • Right click on the root node and select "Properties"
    
    
  • Click "Security"
  • In this section, ensure that your accounts that run the PowerPivot Service application and Excel Services are listed. If they are not, add them.
    
    Note that my screenshot has a few more accounts added…..ignore them.
    
    
• After the accounts are added, click "OK" and close out of SQL management Studio.

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.

• Navigate to your PowerPivot Service Application and click "Review Timer Job Definitions".
• Click "PowerPivot Management Dashboard Processing Timer Job".
• Click "Run Now", then "Job History" once the page loads.
• Refresh this page until you see the timer job near or at the top of the list. If the status is succeeded, you can go back to the Management Dashboard, though it is unlikely to populate any usable data as no workbook activity has been recorded yet.
  • If this timer job fails, you will need to troubleshoot the error. Most of the steps I went through above cover most of the troubleshooting needed to resolve this. 9 times out of 10, the unattended account either isn't properly set or does not have the correct permissions on the PowerPivot Service Application database. You may have to dig into the ULS logs to uncover the error if it is not obvious.
    
    

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/