Verifying the SharePoint 2013 Configuration for Accessing Workbooks as a Data Source from Within the Farm

Článek
08/06/2012

My previous blog post, “Verifying the Excel Services Configuration for PowerPivot in SharePoint 2013,” discussed steps to verify that Excel Services can load workbook data models on an out-of-farm PowerPivot server running SQL Server 2012 SP1 CTP3 Analysis Services (SSAS) in SharePoint deployment mode so that users can enjoy full workbook interactivity in the browser. But data exploration in the browser is only the first step. The next logical step is to consume the data model as a data source in PerformancePoint Services, Power View, or in a custom solution running inside the farm. Let’s take a look at an architecture diagram to understand how this scenario works in SharePoint 2013.

Note that the depicted architecture does not cover access to workbooks as a data source from outside the farm, which requires the deployment of the PowerPivot add-in for SharePoint 2013. This will be the subject of a subsequent blog article. For now, let’s stay in a SharePoint 2013 farm that doesn’t have the PowerPivot add-in deployed. In this environment, accessing a workbook as a data source still works from inside the farm, which involves the following steps:

A client process inside the farm, such as PerformancePoint Services, Power View, or a custom solution, loads the SQL Server 2012 SP1 CTP3 ADOMD.NET data provider and specifies the URL of a workbook as the desired data source. The specified workbook contains the data model that the client process wants to access.
Based on the data source URL, the ADOMD.NET data provider detects that the specified workbook resides in the local farm. The provider now uses a special SharePoint 2013-specific assembly called Microsoft.AnalysisServices.SPClient.dll to communicate with Excel Calculation Services (ECS). ADOMD.NET uses SPClient to send ECS a request to load the data model.
ECS receives the request and retrieves the specified workbook from the content database.
ECS selects an available SSAS server from its server pool and loads the data model. ECS then returns the name of the chosen SSAS server to the ADOMD.NET data provider as well as the database name that ECS generated for the data model.
Having received the server and database name from ECS, the data provider establishes a direct connection to that database. The client process can now query the data model. When the client process is done and closes the connection, ADOMD.NET informs ECS that the data model can be unloaded.

Note: The workbooks as a data source from inside the farm scenario works in the way when using the Microsoft OLE DB provider for Analysis Services (MSOLAP).

There are a number of important facts to take away from this data provider architecture:

Excel Services is at the core of all SharePoint 2013 scenarios involving data models. If Excel Services isn’t configured correctly, users won’t be able to interact with workbooks in the browser and client processes inside the farm won’t be able to access data models as a data source.
Client processes must use a SQL Server 2012 SP1 CTP3 (or later) version of ADOMD.NET or MSOLAP to access a workbook as a data source in SharePoint 2013. Earlier provider versions do not work and are not supported because these versions are unable to communicate with Excel Services.
The data providers, ADOMD.NET and MSOLAP, require Microsoft.AnalysisServices.SPClient.dll to communicate with ECS. Note that this assembly is only included in SQL Server Setup and in the PowerPivot installation package for SharePoint 2013, called spPowerPivot.msi. This special assembly is not included in the individual ADOMD.NET and MSOLAP installation packages, so do not install SQL_AS_ADOMD.msi or SQL_AS_OLEDB.msi on your SharePoint 2013 servers. Install the providers by using spPowerPivot.msi instead.
The data providers establish direct TCP/IP connections to the data models on the SSAS server. This direct approach helps to minimize latencies in the client/server communication, which ultimately provides best performance, but it also requires the security accounts of the client processes to have SSAS administrator permissions. Only SSAS administrators can load data models on an Analysis Services instance running in SharePoint mode.

That’s enough theory for one article. The following sections demonstrate how to verify the SharePoint 2013 configuration for workbooks as a data source based on a realistic multi-server environment. A trivial single-server installation is not suitable because it hides important dependencies. The following screenshot shows my test environment, deployed using SharePoint 2013 Preview and SQL Server 2012 SP1 CTP3.

Here’s a reproducible issue that you can encounter in your own preview environment: Install the computers running SQL Server as outlined in the SQL Server 2012 SP1 CTP3 documentation and then install and configure SharePoint 2013 and Reporting Services Power View on a separate application server. Verify that Excel Services can load data models and then try to create a Power View report on top of a workbook. If all goes as advertised, then you should be able to interact with the workbook in the browser, but Power View won’t be able to create a report. Now, don’t jump to conclusions. Leave the Reporting Services configuration alone for a moment. Let’s first double-check the dependencies for workbooks as a data source by running some more or less straightforward PowerShell scripts. Start SharePoint 2013 Management Shell with elevated permissions and then copy and paste the following lines:

1. Is at least one SSAS server registered in Excel Services?

$ssasServers = Get-SPExcelServiceApplication | Get-SPExcelBIServer $_
if(-Not $ssasServers) { Write-Host "The server pool is empty." } else { $ssasServers }

Yes, AS2012SP1\POWERPIVOT is registered in the Excel Services configuration. So far so good.

2. Is the correct data provider version installed?

$adomd = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient")
Write-Host (dir $adomd.Location).VersionInfo.FileVersion –ForegroundColor Yellow

Yes, the SQL Server 2012 SP1 CTP3 version (11.0.2809.24) is installed. No problem here.

3. Can you connect to the SSAS server directly?

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient")

$server = "AS2012SP1\POWERPIVOT"
$adomd = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$adomd.ConnectionString = "Data Source=$server;"
$adomd.Open()
if($adomd.State –eq "Open") {Write-Host "Connected" –ForegroundColor Yellow} else {Write-Host "Connection failed." –ForegroundColor Red}
$adomd.Close()

Yes, it’s possible to establish a direct connection so the SSAS server is available.

4. Can you connect to a workbook in the local farm?

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient")

$server = "https://sp2013-svr/Shared Documents/SharePoint Images.xlsx"
$adomd = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$adomd.ConnectionString = "Datasource=$server;"
$adomd.Open()
if($adomd.State –eq "Open") {Write-Host "Workbook loaded" –ForegroundColor Yellow} else {Write-Host "Workbook loading failed." –ForegroundColor Red}
$adomd.Close()

Oops, this didn’t go well. The error message is not very informative, but it reveals that the data provider is having an issue. So, my issue is apparently not a Power View-specific problem. It is a general issue and SPClient is my prime suspect.

5. Is the SPClient assembly installed on the SharePoint server?

$spclient = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.SPClient")
if($spclient) {Write-Host "SPClient is installed." –ForegroundColor Yellow} else {Write-Host "SPClient is not installed." –ForegroundColor Red}

And there you have it, SPClient is missing! Apparently, I forgot to install the data providers via spPowerPivot.msi on this computer. I installed Reporting Services using SQL Setup, but in SQL Server 2012 SP1 CTP3, the Reporting Services options do not install the SPClient assembly. This is fixed in later builds. For CTP3, downloading and installing spPowerPivot.msi on the SharePoint server solves this problem. Note that you don’t need to run the PowerPivot Configuration Tool. Simply install spPowerPivot.msi to fully deploy the data providers included in the installer package, as illustrated in the following screenshot. Rerunning the PowerShell script now shows that the SPClient assembly is present. Of course, you can also check Programs and Features in Control Panel to see if the spPowerPivot.msi package is installed.

6. Are the service accounts SSAS administrators?

The next step is to check that all SharePoint service accounts have the required server administrator permissions in Analysis Services, which is quickly done by using AMO.

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

$server = "AS2012SP1\POWERPIVOT"
$amo = New-Object Microsoft.AnalysisServices.Server

$amo.Connect($server)
$admins = $amo.Roles.GetByName("Administrators")
foreach($admin in $admins.Members){Write-Host $admin.Name –ForegroundColor Yellow}
$amo.Disconnect()

Yes, all my SharePoint service accounts are listed as SSAS administrators, so life is good. Note that this script requires your interactive account to have SSAS admin permissions as well. Otherwise, it fails with an error that the Administrators role doesn’t exist. If you aren’t a SSAS administrator, you might have to log on using a SharePoint service account or work with another person who does have the required permissions in Analysis Services.

7. Is the SSAS server running SQL Server 2012 SP1 CTP3 (or later)?

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

$server = "AS2012SP1\POWERPIVOT"
$amo = New-Object Microsoft.AnalysisServices.Server

$amo.Connect($server)
Write-Host "The SSAS server version is: " $amo.Version –ForegroundColor Yellow
$amo.Disconnect()

Yes, the SSAS server is running SQL Server 2012 SP1 CTP3, which corresponds to the version number 11.0.2809.24.

8. Is the SSAS server operating in SharePoint deployment mode?

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

$server = "AS2012SP1\POWERPIVOT"
$amo = New-Object Microsoft.AnalysisServices.Server

$amo.Connect($server)
$mode = $amo.ServerMode
Write-Host "The SSAS server is operating in $mode mode." –ForegroundColor Yellow
$amo.Disconnect()

Yes, the SSAS server is running in SharePoint mode, so let’s try loading the workbook again as a data source.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient")

And sure enough, the script now loads the workbook successfully. Going back to the architecture diagram, you know at this point that ADOMD.NET and all the components below it are functioning. If you still can’t create a report on top of the workbook, you might indeed have to tweak the Power View configuration. For starters, make sure that the Claims to Windows Token Service (C2WTS) is running and check out the Reporting Services team blog at https://blogs.msdn.com/b/sqlrsteamblog/ for further troubleshooting tips. In my test environment, I needed to start the C2WTS service because I wanted to use Windows authentication to access the data models loaded on my SSAS server running in SharePoint mode outside my SharePoint farm.

Comments

Anonymous
February 10, 2013
These scripts and the knowledge behind them most appreciated!Our implementation is broken just now, but we have at least demonstrated that SSAS exists, in the right mode, and can connect, thanks to you.
Anonymous
October 30, 2013
The comment has been removed
Anonymous
November 01, 2013
Hi Perpetillo,Somehow Excel Services doesn't want to recognize your server as a valid choice for some reason. I'm afraid that I can't troubleshoot this in this blog forum. Any chance you can contact customer support?Cheers,Kay
Anonymous
March 27, 2014
Hi,This is an old blog post but hopefully you will respond to my question. My SharePoint farm has two app servers, two WFE's and five instances of SQL Servers ( 1) Relational for SP content databases, 2) SSAS MD 3) SSAS Tabular 4) Realtional for User databases 5) SSAS PowerPivot). We have configured Kerberos Constrained Delegation for the backend services (for instance 2,3 and 5) but the web application is claims based NTLM. I am able to successfully validate the entire environment except for the PowerPivot Gallery. I have following issues:1) From terminal server, I am unable to use PowerPivot Workbook as a data source ( by clicking Open in excel icon from the gallery)2) When I create a PowerPivot workbook connected to Tabular Instance - I don't see the icons in gallery for 'Open in Excel', 'PowerView report' and 'Data Refresh'PowerPivot Gallery functionality in my environment appears flaky and I am unable to figure out how to validate it. Can you please provide any guidance?Thanks a lot.
Anonymous
March 27, 2014
Small correction to my earlier post. We have configured KCD for 2,3,4 and 5. (( 1) Relational for SP content databases, 2) SSAS MD 3) SSAS Tabular 4) Realtional for User databases 5) SSAS PowerPivot). We have configured Kerberos Constrained Delegation for the back end services (for instance 2,3,4 and 5)
Anonymous
March 28, 2014
Hi Sathya,Re 1) Are you able to connect to the Workbook directly, in other words, select to connect to Analysis Services and specify the URL to the workbook as the data source?Re 2) Workbooks that connect directly to a Tabular data model are not PowerPivot workbooks, hence you cannot create Power View reports on top of them or configure Data Refresh. Data Refresh would be Processing on the SSAS server. Power View can connect directly to the Tabular data model.Hope this helps.Cheers,Kay
Anonymous
June 11, 2014
Very helpful, thank you! We've been troubleshooting Data Refresh in PowerPivot through Excel Services for a few days now. Your scripts confirmed that we were not able to connect to the Analysis Services instance and we found an SPN in Kerberos was not configured correctly.
Anonymous
June 21, 2014
Thanks for the post.I am getting an error in step 4 "Exception calling "Open" with "0" argument(s): "We cannot locate a server to load the workbook Data Model." What could be the issue?
Anonymous
July 31, 2014
This is an amazing post. Truely. I have my issue tacked down to the connection to the workbook. I get the following error:Exception calling "Open" with "0" argument(s): "The connection cannot be opened because the redirector service did not provide required database information."Any ideas what it might mean?
Anonymous
July 31, 2014
The comment has been removed
Anonymous
October 09, 2014
Hi Kay,We've configured powerpivot for SharePoint and the uploaded workbook correctly refreshes its SQL connected datasources. But we can't connect to say a csv or text file located neither in a file share nor published in Sharepoint. Do you have some tips of what could be happening?Thanks!
Anonymous
October 10, 2014
Hi Estanislao,Text and CSV files are indeed an issue for Power Pivot refresh operations in SharePoint. The issue is that the data provider used to import this data (that is the Microsoft.ACE.OLEDB.12.0 provider) in Excel on the desktop is not available or supported on the server. So, your refresh request reaches the Analysis Services server in the backend, and that server cannot load Microsoft.ACE.OLEDB.12.0 and therefore cannot refresh the data from the CSVs. You could try to install this provider on the server (such as by installing Excel), but keep in mind that this is an unsupported configuration as far as the ACE provider is concerned.If you have an O365 subscription, you could switch to importing the CSV files via Power Query and then configuring Scheduled Data Refresh in the cloud to reach back to your CSV files on-prem, via the Data Management Gateway. The following article might be a good starting point for Power BI in O365 and Scheduled Data Refresh: blogs.msdn.com/.../scheduled-data-refresh-update-new-data-sources.aspx. Unfortunately, Power Query is not yet supported on Analysis Services servers on-prem. It will happen one day, but not in the near future.Hope this helps.Cheers,Kay
Anonymous
December 18, 2014
Very helpful post, thanks. Hoping on help someone facing the same error, I would add on step 4: if you get "we cannot locate a server to load the workbook data model", check if the SSAS servisse account has read-only access to the data source (the workbook). Pretty obvious after you nail it, but it costed me some sleeping hours.
Anonymous
April 06, 2015
The comment has been removed
Anonymous
May 22, 2015
The comment has been removed
Anonymous
July 31, 2015
The comment has been removed
Anonymous
July 31, 2015
Hi Sakti, Looks like SPClient cannot get through to Excel Services. Can you open this workbook in the browser? That would help verify if Excel Services is at all able to open this workbook. I would also check the SharePoint ULS logs. They should contain some hints why the call to Excel Services failed. You should see some Excel Services exception. Maybe access denied or workbook not found or something along those lines. Alternatively, if you suspect https to cause an issue, do you have an http-accessible site perhaps to test without SSL? Might help to narrow down the issue. Hope this helps. Cheers, Kay
Anonymous
July 31, 2015
Hi Kay, Thanks a lot for your reply. It was pretty fast and I was expecting a reply so early. Thanks again. Please find my reply with my initials.

Looks like SPClient cannot get through to Excel Services. Can you open this workbook in the browser? That would help verify if Excel Services is at all able to open this workbook. Sakti-> Yes. The work book is opening in browser and Filters are working fine.

I would also check the SharePoint ULS logs. They should contain some hints why the call to Excel Services failed. You should see some Excel Services exception. Maybe access denied or workbook not found or something along those lines. Sakti-> I will look into the ULS log and post the details.

Alternatively, if you suspect https to cause an issue, do you have an http-accessible site perhaps to test without SSL? Might help to narrow down the issue. Sakti->The business requirement is to have the site as HTTPS. I did not find any article where it is saying clearly about HTTPS data refresh. Excel Service with power pivot is working fine in my DEV environment with HTTP. Again I will try to have a HTTP web application and test out and post here.

Regards Sakti

Anonymous
July 31, 2015
The comment has been removed
Anonymous
August 02, 2015
Hi Sakti, I'm afraid that we might not be able to troubleshoot this sufficiently over blog article comments. In general, if GetWorkbookConnections fails, I’d say you are not dealing with a PowerPivot workbook but a general Excel workbook that does not contain a data model. If you absolutely cannot get this to work, please contact Microsoft support. My strategy is to find a basic working scenario first. It’s easier to build from there. That’s why I suggested testing HTTP first. HTTPS shouldn’t be a problem, but it’s more complex due to the possibility of certificate issues. If HTTP works, you at least know that the SharePoint/Excel Services/PowerPivot deployment is OK, which enables you to investigate the issue with a narrower focus. On the other hand, if HTTP doesn’t work, well, then you know that SSL isn’t likely the root cause. And don’t try to fix too many things at once. Maybe scheduled data refresh fails for the same reason as HTTPS. Anyway, crossing my fingers that you can resolve these issues quickly, otherwise Microsoft support is your friend. Best wishes. Cheers, Kay
Anonymous
August 02, 2015
Hi Kay, Thanks for your response. Appreciate your effort to take time and reply. If I am able to fix this or take help from Microsoft Support, I will post the details. Regards Sakti
Anonymous
August 05, 2015
Hi Kay, This issue is resolved. Data refresh is working fine for http and https(with and without port). Solution-The issue was with PowerPivot Instance and Installation. So I have to uninstall and reinstall the PowerPivot (spPowerPivot.msi). After that everything is working fine. Thanks for your help and replies. Regards Sakti
Anonymous
February 25, 2016
Excellent!I solved my problem!Congratulations!

Sdílet prostřednictvím

Verifying the SharePoint 2013 Configuration for Accessing Workbooks as a Data Source from Within the Farm

Comments

Looks like SPClient cannot get through to Excel Services. Can you open this workbook in the browser? That would help verify if Excel Services is at all able to open this workbook. Sakti-> Yes. The work book is opening in browser and Filters are working fine.

I would also check the SharePoint ULS logs. They should contain some hints why the call to Excel Services failed. You should see some Excel Services exception. Maybe access denied or workbook not found or something along those lines. Sakti-> I will look into the ULS log and post the details.

Další materiály