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

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

Rate This
  • Comments 6

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:

  1. 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.
  2. 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.
  3. ECS receives the request and retrieves the specified workbook from the content database.
  4. 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.
  5. 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:

  1. 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.
  2. 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.
  3. 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.
  4. 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 = "http://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")

$server = "http://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()

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 http://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.

Leave a Comment
  • Please add 4 and 1 and type the answer here:
  • Post
  • 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.

  • Hello I have a problem that is driving me crazy. This is the scenario.

    I have two tier architecture, Server A with Web and app and Server B with DB.

    I have already applied all the sugestions made above  and everything works perfectly except when I try to load the workbook. I have this error:

    Excepción al llamar a "Open" con los argumentos "0": "We cannot locate a

    server to load the workbook Data Model."

    En línea: 1 Carácter: 1

    + $adomd.Open()

    + ~~~~~~~~~~~~~

       + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException

       + FullyQualifiedErrorId : NoAvailableStreamingServerException

    I have already added the server data model to the excel service aplication in the central administration console.

    What can I do? I have been two weeks struggling with this problem and I can't find a solution..

    Thanks

    PD: I did in one server solution and everything work perfectly so I guess the problem ocurrs because the two tier architecture

  • 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

  • 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.

  • 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)

  • 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

Page 1 of 1 (6 items)