Even though this is the PowerPivot and Excel Services blog, I felt that adding a blog on this common PowerView error was worthy of a post.

Many times I have seen customers with the error:

"Power View

An error occurred while loading the model for the item or data source '<filename.extension'>.  Verify that the connection information is correct and that you have permissions to access the data source."

If you expand the "Show details", you will see Claims errors, but what does that mean?

<detail><ErrorCode xmlns="http://www.microsoft.com/sql/reportingservices">rsCannotRetrieveModel</ErrorCode><HttpStatus xmlns="http://www.microsoft.com/sql/reportingservices">400</HttpStatus><Message xmlns="http://www.microsoft.com/sql/reportingservices">An error occurred while loading the model for the item or data source 'http://microsoft/sites/bidemo/PowerPivot/Tabular.bism'. Verify that the connection information is correct and that you have permissions to access the data source.</Message><HelpLink xmlns="http://www.microsoft.com/sql/reportingservices">http://go.microsoft.com/fwlink/? inkId=20476&amp;EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&amp;EvtID=rsCannotRetrieveModel&amp;ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&amp;ProdVer=11.0.3349.0</HelpLink><ProductName xmlns="http://www.microsoft.com/sql/reportingservices">Microsoft
SQL Server Reporting Services</ProductName><ProductVersion xmlns="http://www.microsoft.com/sql/reportingservices">11.0.3349.0</ProductVersion><ProductLocaleId xmlns="http://www.microsoft.com/sql/reportingservices">127</ProductLocaleId><OperatingSystem xmlns="http://www.microsoft.com/sql/reportingservices">OsIndependent</OperatingSystem><CountryLocaleId xmlns="http://www.microsoft.com/sql/reportingservices">1033</CountryLocaleId><MoreInformation xmlns="http://www.microsoft.com/sql/reportingservices"><Source>ReportingServicesLibrary</Source><Message
msrs:ErrorCode="rsCannotRetrieveModel" msrs:HelpLink="http://go.microsoft.com/fwlink/?LinkId=20476&amp;EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&amp;EvtID=rsCannotRetrieveModel&amp;ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&amp;ProdVer=11.0.3349.0"
xmlns:msrs="http://www.microsoft.com/sql/reportingservices">An error occurred while loading the model for the item or data source 'http://microsoft/sites/bidemo/PowerPivot/Tabular.bism'. Verify that the connection information is correct and that you have permissions to access the data source.</Message><MoreInformation><Source>Microsoft.ReportingServices.ProcessingCore</Source><Message msrs:ErrorCode="rsErrorImpersonatingUser" msrs:HelpLink=http://go.microsoft.com/fwlink/? inkId=20476&amp;EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&amp;EvtID=rsErrorImpersonatingUser&amp;ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&amp;ProdVer=11.0.3349.0 xmlns:msrs="http://www.microsoft.com/sql/reportingservices">Cannot
impersonate user for data source 'TemporaryDataSource'.</Message><MoreInformation><Source>Microsoft.ReportingServices.ServiceRuntime</Source><Message msrs:ErrorCode= "rsClaimsToWindowsTokenError" msrs:HelpLink=http://go.microsoft.com/fwlink/? inkId=20476&amp;EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&amp;EvtID=rsClaimsToWindowsTokenError&amp;ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&amp;ProdVer=11.0.3349.0 xmlns:msrs= "http://www.microsoft.com/sql/reportingservices">Cannot convert claims identity to windows token. </Message><MoreInformation> <Source>Microsoft.SharePoint</Source><Message>Could not retrieve a valid Windows identity.</Message><MoreInformation><Source>mscorlib</Source><Message>Access is  enied.</Message></MoreInformation> </MoreInformation></MoreInformation></MoreInformation></MoreInformation><Warnings xmlns=http://www.microsoft.com/sql/reportingservices /></detail>

If you want to analyze this more you can use Rodney Viana's tool to analyze the C2WTS:

Troubleshooting Claims to Windows NT Token Service (c2WTS) in SharePoint 2010 may be difficult if you don’t know where to start

http://blogs.msdn.com/b/rodneyviana/archive/2011/07/19/troubleshooting-claims-to-windows-nt-token-service-c2wts-in-sharepoint-2010-may-be-difficult-if-you-don-t-know-where-to-start.aspx

I ran Rodney's tool and saw:

 Testing Service c2WTS
+- Service c2WTS found
+- Service c2WTS is running
+- Path of service: C:\Program Files\Windows Identity Foundation\v3.5\c2wtshost.exe
+- Config File: C:\Program Files\Windows Identity Foundation\v3.5\c2wtshost.exe.config
+- Service Logon: SYSTEM\NT AUTHORITY
----- start of config file ----
<?xml version="1.0"?>
<configuration>
  <configSections>
    <section name="windowsTokenService" type="Microsoft.IdentityModel.WindowsTokenService.Configuration.WindowsTokenServiceSection, Microsoft.IdentityModel.WindowsTokenService, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" />
    <supportedRuntime version="v2.0.50727" />
  </startup>
  <windowsTokenService>
    <!--
        By default no callers are allowed to use the Windows Identity Foundation Claims To NT Token Service.
        Add the identities you wish to allow below.
      -->
    <allowedCallers>
      <clear />
      <add value="WSS_WPG" />
    </allowedCallers>
  </windowsTokenService>
</configuration>
-----  end of config file  ----
Retrieving security groups/users allowed to use the service from config file
+- WSS_WPG
Trying to login .........
Using current Windows Credentials
***** c2WTS could not provide a valid Windows Token. Reason: WTS0003: The caller is not authorized to access the service.

Server stack trace:
   at System.ServiceModel.Channels.ServiceChannel.ThrowIfFaultUnderstood(Message reply, MessageFault fault, String action, MessageVersion version, FaultConverter faultConverter)
   at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc)
   at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
   at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)
   at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)

Exception rethrown at [0]:
   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
   at Microsoft.IdentityModel.WindowsTokenService.S4UClient.IS4UService_dup.UpnLogon(String upn, Int32 pid)
   at Microsoft.IdentityModel.WindowsTokenService.S4UClient.<>c__DisplayClass1.<UpnLogon>b__0(IS4UService_dup channel)
   at Microsoft.IdentityModel.WindowsTokenService.S4UClient.CallService(Func`2 contractOperation)
   at c2WTSTest.Form1.button2_Click(Object sender, EventArgs e)

Now Verifying if user msft\test has rights on c2WTS
+- User  msft\test has no access to the service
*** Analysis Complete ***

The first highlighted item tells me the account that was running the C2WTS: SYSTEM\NT AUTHORITY

The second highlighted item gives me the error: c2WTS could not provide a valid Windows Token. Reason: WTS0003: The caller is not authorized to access the service. 

The error tells me that the account running the Claims to Windows Token Service lacks permissions/security.

Steps to resolve the issue.

I changed the Claims to Windows Token Service to a managed account and then applied the below changes (Note: you can attempt to apply the below changes to the existing account running the C2WTS):

a. Add the service account to the local Administrators Groups.

b. In local security policy (secpol.msc) under user rights assignment give the service account the following permissions:

i. Act as part of the operating system

ii. Impersonate a client after authentication

iii. Log on as a service

Restart IIS

After making this change the error went away.

When opening a workbook after a Schedule Data Refresh, the workbook does not open in Excel Services:

This is because after a Scheduled Data Refresh, the “Refresh data when opening the file” is checked.  This is by design, because after a Scheduled Data Refresh, the workbook’s Data Model is updated with new information and the workbook is re-published to the SharePoint Library.  To ensure that you are getting fresh data in the browser, the “Refresh data when opening the file” is checked.  (I will get to the reason why it is spinning later under the Cause section).

If we uncheck “Refresh data when opening the file” and open the workbook in the browser, the workbook opens, but if we click on a slicer (a workbook refresh is triggered), the error “Unable to refresh data for a data connection in the workbook.  Try again or contact your system administrator.  The
following connections failed to refresh: PowerPivot Data” is thrown:

This is caused by a Bug.  When the Scheduled Data Refresh runs, a Process cube is created under the SharePoint Server running PowerPivot > Analysis Services > PowerPivot Instance > Databases.  The Process cube should be deleted, but it is not.  Therefore many (not sure I can call them “duplicate” since they have a unique GUID so I will use the term “multiple”) “multiple” Data Models are created and not deleted.  When the workbook is refreshed, the workbook looks for the Data Model associated with that workbook under the SharePoint Server running PowerPivot > Analysis Services > PowerPivot Instance > Databases.  As you can see there are many “multiple” Data Models and the workbook refresh either hangs (spinning when using “Refresh data when opening the file”) or fails when clicking on a slicer (error “Unable to refresh data for a data connection in the workbook.  Try again or contact your system administrator.  The following connections failed to refresh: PowerPivot Data).

To resolve this, you will need to upgrade all servers running PowerPivot to SQL SP1 (11.0.3000.0) and apply the Cumulative Update 4.

Microsoft® SQL Server® 2012 Service Pack 1 (SP1)
http://www.microsoft.com/en-us/download/details.aspx?id=35575

Cumulative update package 4 for SQL Server 2012 SP1
http://support.microsoft.com/kb/2833645/en-us

FIX: PowerPivot database is not deleted from SSAS memory when PowerPivot database processing fails
http://support.microsoft.com/kb/2820918

The SQL Server 2012 builds that were released after SQL Server 2012 Service Pack 1 was released
http://support.microsoft.com/kb/2772858

Much of the existing content for converting a PowerPivot workbook to a SQL Server Analysis Services (SSAS) tabular instance assumes that the reader is a developer.  This post details the process of upsizing a PowerPivot workbook to SSAS from the point of view of a knowledge worker and or a SharePoint administrator.

This post assumes you are using PowerPivot as a data source, so no UI is present in the workbook hosting the PowerPivot model.  The display of the data would happen from another workbook that connects to the PowerPivot workbook as a data source or from some other reporting tool like PowerView or Excel Services.

What is upsizing PowerPivot (aka convert PowerPivot to tabular model, aka convert PowerPivot to SSAS):

Upsizing PowerPivot is simply moving the solution from a SharePoint centric storage mode to a SSAS centric storage mode.  The end result is that the PowerPivot model and data will not be stored in SharePoint. 
PowerPivot stores models in Excel Workbooks when using a SharePoint centric mode and stores the models in native SSAS files when using SSAS storage mode.

The key takeaway is that the same query engine is used regardless of where the model is stored, Excel client, SharePoint or SSAS.  

Why upsize a PowerPivot workbook:

You may want to consider upsizing your PowerPivot workbook after it exceeds 100MB in size for the following reasons.

1. Stability
• SharePoint was not optimized to host very large files and it has a hard limit of 2GB for a single file.
• You will find that SharePoint features may fail or behave oddly when you are using very large files, this is also a burden on the SharePoint system that could adversely affect other users.
2. Performance
• When a PowerPivot workbook is stored in SharePoint the PowerPivot model and all of the data must be streamed from the SharePoint database to a special instance of SSAS to build the database.
• If your users are experiencing intermittent poor performance this could be because the cache time has expired and the backend SSAS database needs to be rebuilt.
• In SharePoint 2013 the default behavior of Excel Services data \ “Refresh all connections” is to have the PowerPivot data completely reload itself from source data.  For large workbooks this could cause poor user experience.
3. If you want to have a PowerPivot workbook larger than 2GB your only options are to upsize or only use it in the Excel client (64bit), SharePoint only support files 2GB or smaller. 

Issues to consider before upsizing:

1. Your administrators will need to install and configure a tabular instance of SSAS in your network.
2. You will need to install “SQL Server Data Tools” on your system.
3. SSAS does not use the SharePoint security system, if you want to restrict who can access the workbook data you will need to configure it on the SSAS database.
• This consists of assigning Windows users and or group’s access to the database.
• Can be done with “SQL Server Data Tools”
4. If you were not using the workbook as a data source you will need to create a separate workbook to view the data.  This would be in cases where both the data and the pivot tables that view the data are stored in the same workbook. 

Steps to upsize a PowerPivot workbook:

1. Have your administrator setup an tabular instance of SSAS in your network
• Use the SQL 2012 media, pick Analysis Service and Tabular mode
• http://msdn.microsoft.com/en-us/library/hh231722.aspx
• Make sure to have the SSAS service run under a domain account, it will need to access the file share made in step 2
• You must install SQL 2012 SP1 if you are using Excel 2013 workbook as the source.                                                            
  i. http://www.microsoft.com/en-us/download/details.aspx?id=35575
• The “SQL Server Browser” service must be enabled and running
2. Create a file share somewhere where both the service account used in step 1.c and the users building the model have full control.
3. On the client PC that will author the model install the SQL Server Data Tools (formerly called BIDS) from the SQL Server 2012 media.
   • Run setup, pick “Installation” then “New SQL Server stand-alone installation or add features to an existing installation”
   • On the “Setup Role” screen  pick “SQL Server Feature Installation”
   • On the “Feature Selection” screen pick “SQL Server Data Tools” (see image below)

    

4. Finish the installation wizard.
5. Install SQL Server 2012 SP1 on the client computer, this allows for support of Office 2013 workbooks.
• http://www.microsoft.com/en-us/download/details.aspx?id=35575
6. Copy the workbook you want to upsize to the fileshare created in step 2
7. Start the “SQL Server Data Tools” program
8. Pick “Business Intelligence Settings” in the “Choose Default Environment Settings” popup (see below)
9. 
10. Create a new Project.  Menu: File \ New \ Project
11. Within the “Business Intelligence \ Analysis Service” template pick “Import from PowerPivot” project type.
12. Enter a good project name, this project will hold the model and build the database on the SSAS server.
13. Enter the tabular mode SSAS server\instance name in the next popup window (see below)
14. 
15. Test the connection to ensure the SSAS Server was entered correctly and is functioning properly.
16. Click “Yes” to the warning below, it is simply asking that you trust where the PowerPivot workbook is getting data from. And informing you that the data will not be imported, this is OK because the server will fetch data from the data sources defined in the PowerPivot model. If you have data in the model that is from a linked sheet that will need to be copied by hand into this solution via the “Past Append” function.
17. 
18. Select the workbook you want to upsize in the file open dialog box that pops up next. This file should reside on the file share created in step 2 and referenced in step 6.
19. If all goes well, several progress bars should pass by in the lower right corner of the screen and you should end up with an open Model.bim file like screen shot below:
20. 
21. Click the Existing Connections (  ) toolbar button and review any existing connection(s) included in the model (see image below).
• The domain account used in step 1.c should have read access to all of the data sources used.
22. 
23. Deploy the project to the server via the menu item: Build \ Deploy
• Should get Success message as shown below
24. 
25. At this point you have a working data source that you can access from new workbooks and other data source consumers.  In Excel you just treat it like any other Analysis Services data source.  
• http://office.microsoft.com/en-us/excel-help/connect-to-or-import-data-from-sql-server-analysis-services-HP010342296.aspx
26. All is good, except that the new SSAS database is not getting refreshed with the latest data, it is a static snapshot of the data at the time you deployed it to the server.  You can setup an automated processing schedule by: (the following steps can be accomplished using the “SQL Server Management Studio” tool):
• Ensure that the “SQL Server Agent” service is running on the SSAS server
• Ensure that the account running the “SQL Server Agent” service has permissions to process the new SSAS tabular database. You can create a new role for the database that has “Process” permissions and assign the agent account to that.
• Create a new SQL Server Agent job, with one step.
• The step should be setup with Type = SQL Server Analysis Services Command, Run as = SQL Server Agent Service Account, Server = Name of you SSAS Tabular server (see image below)
• Command should be:
• <Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Type>ProcessDefault</Type> <Object>  <DatabaseID>TabularProject1</DatabaseID> </Object></Process>
27. Where DatabaseID should equal the name of the SSAS database you just created.
28. 
29. Schedule the job to run daily or however often you want changes made in the source data to reflect in the SSAS tabular database.
30. FYI, New data is fetched from the source data sources when the SSAS database is processed.

Congratulations you have now upsized your PowerPivot model to a powerful full featured SSAS database. 

When opening an Excel workbook (.xlsx) in the browser the error "The workbook cannot be opened" is thrown:

This is caused by the account running Excel Services not having proper permissions to the Content Database.  When you upload an Excel workbook to a SharePoint library, the workbook is stored as blobs in the Content Database.  When you choose to open this in Excel Services, the account running Excel Services needs to retrieve and reassemble the workbook.  If the account running Excel Services lacks proper permissions, "The workbook cannot be opened".

We have a KB on how to resolve this issue:

The Excel Services Application for SharePoint 2010 does not load or display workbooks

http://support.microsoft.com/kb/981293

In this blog, I will step you through this KB; where to collect data and explain what the two below SharePoint Management Shell Commands do:

$w = Get-SPWebApplication –Identity <URL of the Web application>
$w.GrantAccessToProcessIdentity("<insert service account>")

You need to find the <URL of the Web application>.  To do this:

1. Browse to the report library where the failing workbook is located and make note of the URL.

2. Central Administration > Application Management > Manage web applications compare the URL to the list of Web Apps.  Find the correct Web App URL and add it to “URL of the web application”.

You now have the first command: 

$w = Get-SPWebApplication –Identity http://tschauer

3. Central Administration > Security > Configure Service Accounts > from the first dropdown, select the application pool running "Excel Services Application" and add that account where is says “<insert service account>”.

You now have the second command:

$w.GrantAccessToProcessIdentity("Devtest\Excel_Services_Account")

So now you can run these commands by following the below steps:

1. Click Start, click All Programs.
2. Click Microsoft SharePoint 2010/2013 Products.
3. Click SharePoint 2010/2013 Management Shell.
4. At the Windows PowerShell command prompt (PS C:\>), type the following command, and then press ENTER:

$w = Get-SPWebApplication –Identity http://tschauer
$w.GrantAccessToProcessIdentity("Devtest\Excel_Services_Account")

When you run these commands you are:

1. Adding the account running Excel Services as a User to the Content Database (Security > Users).
2. Giving the account running Excel Services the SPDataAccess Membership (Account > Membership)
3. Giving the account running Excel Services Full Control to the Web Application (Via "Policy for Web Application")