locked
Trouble accessing workbook in local farm via powershell - Testing SSAS connections RRS feed

  • Question

  • I am troubleshooting a SharePoint 2013 setup for BI.

    I am following this troubleshooting guide, and the step that fails is "access to a local workbook".

    http://blogs.msdn.com/b/analysisservices/archive/2012/08/06/verifying-the-sharepoint-2013-configuration-for-accessing-workbooks-as-a-data-source-from-within-the-farm.aspx

    Heres the code:

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient")
    
    $server = "https://URL.com/ppp/PowerPivot%20Gallery/ATT_Production_V1.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 failure:

    Exception calling "Open" with "0" argument(s): "We cannot locate a server to load the workbook Data Model."
    At line:6 char:1
    + $adomd.Open()
    + ~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : NoAvailableStreamingServerException
     
    Workbook loading failed.

    Service accounts have contribute access to the workbook in the SharePoint UI.

    SPClient does appear to be install:

    PS C:\Windows\system32> $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}
    SPClient is installed.

    Not sure what else to check here???

    This is SharePoint 2013 on Server 2012, with SQL 2014.


    Joshua Fuente


    • Edited by NachoScript Tuesday, July 14, 2015 1:41 PM added URL
    Tuesday, July 14, 2015 1:40 PM

Answers

All replies

  • Looks like some other people had that issue in the comments section of that post and the answer was to contact Microsoft, one person only had read access but that doesn't appear to be your problem unless you are giving the right service account access.  Have you read through this post as well?

    http://social.technet.microsoft.com/wiki/contents/articles/3870.troubleshoot-powerpivot-data-refresh.aspx

    Tuesday, July 14, 2015 2:19 PM
  • thanks for the reply,

    Question, could this particular failure actually be due to the Excel Service acct not having Admin access on the SSAS instance? (I am still waiting to have that access provisioned)


    Joshua Fuente

    Tuesday, July 14, 2015 2:38 PM
  • I could absolutely see that, all of those tools create an environment that is so complex that it is very difficult to get all of the permissions sorted out in a non-trivial (not a single server) environment. The error message indicates that it cannot find a server (could definitely be permissions) for loading the data model. It makes sense that it would need access to Power Pivot SQL server instance in order to load the data model properly.
    Tuesday, July 14, 2015 3:52 PM
  • Tuesday, July 14, 2015 4:08 PM
  • Yes, I did find a missing delegation for the CTWTS, but I am still getting the error in powershell.

    IISReset on all servers after the delegation. (should I logout or something?)

    But I did find that after the delegation change, I don't get an error when I test the connection to the workbook in the browser.  (/_layouts/15/ReportServer/DataSourceProperties.aspx)

    But still get the error when I run this code.

    $WorkBookURL = "https://URL.com/ppp/PowerPivot%20Gallery/WorkbookName.xlsx"
    $server = "$WorkBookURL"
    $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()

    Exception calling "Open" with "0" argument(s): "We cannot locate a server to load the workbook

    Data Model."

    At line:8 char:1

    + $adomd.Open()

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

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

        + FullyQualifiedErrorId : NoAvailableStreamingServerException

    Workbook loading failed.



    Joshua Fuente

    Friday, July 24, 2015 7:13 PM