Back to Insights

Manage Excel Services data model settings in SharePoint 2013 with PowerShell

I'm working on a SharePoint 2013 project where we're setting up Excel Services with SQL Server Analysis Services (SSAS). Our internal repositories didn't yet have PowerShell scripts for managing Excel Services so I carved out some time to build these. SharePoint allows you to connect the Excel Services application to a number of SSAS instances by registering the servers in the Data Model Settings.

Microsoft documents how to configure this using Central Administration in the TechNet article, Manage Excel Services data model settings (SharePoint Server 2013). You will note there is no documentation there for PowerShell which is honestly not a surprise. I took a stroll over to list of Excel Services cmdlets in SharePoint Server 2013 and there I also didn't find any cmdlets that looked like what I needed. 0 for 2.

Next I did what any self-respecting SharePoint implementor would do. I opened a PowerShell window and tried some tab completion. All the existing cmdlets are in the form Verb-SPExcel*. Since I wanted to create a server, I figured it would be worth starting with New-SPExcel*.

What happens if I try to tab complete New-SPExcel?

When I hit Tab:

a (welcomed) surprising result -- New-SPExcelBIServer

New-SPExcelBIServer? Could I have found what I was looking for on the first attempt? I did a quick search and sure enough there's no official documentation. 0 for 3. I did find blog posts about new cmdlets which listed the *-SPExcelBIServer cmdlets and I even found a few mentions about how these cmdlets weren't documented.

I did a Get-Help New-SPExcelBIServer and the results looked promising.

Trying to figure out how to use New-SPExcelBIServer via Get-Help

Next I did what any self-respecting SharePoint implementor would do. I tried it out. After a bit of fiddling around with the cmdlet I found I had to either pipe the Excel Services application to New-SPExcelBIServer or alternatively grab the service application into a variable or copy the GUID and provide this to the -ExcelServiceApplication parameter.

Adding a server

In this example I'm going to add a reference to SSAS instanced called "TEST" on the server "Test":

Get-SPExcelServiceApplication | New-SPExcelBIServer -ServerId "Test\TEST" -Description "Awesome new SSAS instance"

New-SPExcelBIServer outputs the newly registered server:

ServerId                      Description                   Identity                      ExcelServiceApplication
--------                      -----------                   --------                      -----------------------
Test\TEST                     Awesome new SSAS instance     Test\TEST                     Excel Services

To double check I opened up Central Administration and went to the Data Model Settings (Application Management -> Manage Service Applications -> Excel Services application -> Data Model Settings)

New-SPExcelBIServer registers the server and we can see it in Central Administration

Check that out, it worked!

Next I did what any self-respecting SharePoint implementor would do. I tried out the other cmdlets.

Getting the existing registered SSAS servers

Get-SPExcelBIServer will display all the registered servers.

Get-SPExcelServiceApplication | Get-SPExcelBIServer
ServerId                      Description                   Identity                      ExcelServiceApplication
--------                      -----------                   --------                      -----------------------
Test\TEST                     Awesome new SSAS instance     Test\TEST                     Excel Services

Edit an existing instance

This is a way to change the instance perhaps because you moved it. With the SQL Server database engine we would use a client alias and be able to move the instance around all willy-nilly. Analysis Services doesn't have this ability and thankfully we have the ability to change it in Excel Services.

I found it was easiest to first get the server and assign it to a variable.

$BIServer = Get-SPExcelServiceApplication | Get-SPExcelBIServer
Get-SPExcelServiceApplication | Set-SPExcelBIServer -Identity $BIServer -ServerId "NEWSERVER\TEST" -Description "Moved to NEWSERVER 2014-03-10"
Get-SPExcelServiceApplication | Get-SPExcelBIServer

Outputs:

ServerId                      Description                   Identity                      ExcelServiceApplication
--------                      -----------                   --------                      -----------------------
NEWSERVER\TEST                Moved to NEWSERVER 2014-03-10 NEWSERVER\TEST                Excel Services

And in Central Administration, we see the server name and description:

Set-SPExcelBIServer changes the configuration of the registered server. Here we see the results in Central Administration.

Remove an SSAS instance

If you've registered a single server, it's easy to remove it by chaining together the cmdlets with pipes:

Get-SPExcelServiceApplication | Get-SPExcelBIServer | Remove-SPExcelBIServer

Otherwise, get the server with Get-SPExcelBIServer and then pass this to the -Identity parameter.

With the server removed the output is null:

PS> Get-SPExcelServiceApplication | Get-SPExcelBIServer
PS> 

When we check Central Administration we no longer see a server:

When we remove the server it disappears as expected from Central Administration.


Here's a picture of the entire process:

Using the SPExcelBIServer cmdlets

cmdlet documentation

Honestly, it's annoying that the documentation for these cmdlets isn't public and this time I was lucky to have accidentally figured it out quickly. My guess (hope) is the documentation exists and Microsoft simply hasn't published it publicly. For now in lieu of official documentation on TechNet, here is the Get-Help output for each of the SPExcelBIServer cmdlets.

Register An Analysis Services server - New-SPExcelBIServer

NAME
    New-SPExcelBIServer

SYNTAX
    New-SPExcelBIServer -ServerId  -ExcelServiceApplication  [-Description
    ] [-AssignmentCollection ] [-WhatIf] [-Confirm]  []


PARAMETERS
    -AssignmentCollection 

        Required?                    false
        Position?                    Named
        Accept pipeline input?       true (ByValue)
        Parameter set name           (All)
        Aliases                      None
        Dynamic?                     false

    -Confirm

        Required?                    false
        Position?                    Named
        Accept pipeline input?       false
        Parameter set name           (All)
        Aliases                      cf
        Dynamic?                     false

    -Description 

        Required?                    false
        Position?                    Named
        Accept pipeline input?       false
        Parameter set name           (All)
        Aliases                      None
        Dynamic?                     false

    -ExcelServiceApplication 

        Required?                    true
        Position?                    Named
        Accept pipeline input?       true (ByValue, ByPropertyName)
        Parameter set name           (All)
        Aliases                      None
        Dynamic?                     false

    -ServerId 

        Required?                    true
        Position?                    Named
        Accept pipeline input?       false
        Parameter set name           (All)
        Aliases                      None
        Dynamic?                     false

    -WhatIf

        Required?                    false
        Position?                    Named
        Accept pipeline input?       false
        Parameter set name           (All)
        Aliases                      wi
        Dynamic?                     false

    
        This cmdlet supports the common parameters: Verbose, Debug,
        ErrorAction, ErrorVariable, WarningAction, WarningVariable,
        OutBuffer and OutVariable. For more information, see
        about_CommonParameters (http://go.microsoft.com/fwlink/?LinkID=113216).


INPUTS
    Microsoft.Office.Excel.Server.Cmdlet.SPExcelServiceApplicationPipeBind
    Microsoft.SharePoint.PowerShell.SPAssignmentCollection


OUTPUTS
    System.Object

ALIASES
    None


REMARKS
    None

List the Analysis Services servers - Get-SPExcelBIServer

NAME
    Get-SPExcelBIServer

SYNTAX
    Get-SPExcelBIServer [[-Identity] ] -ExcelServiceApplication
     [-AssignmentCollection ]  []


PARAMETERS
    -AssignmentCollection 

        Required?                    false
        Position?                    Named
        Accept pipeline input?       true (ByValue)
        Parameter set name           (All)
        Aliases                      None
        Dynamic?                     false

    -ExcelServiceApplication 

        Required?                    true
        Position?                    Named
        Accept pipeline input?       true (ByValue, ByPropertyName)
        Parameter set name           (All)
        Aliases                      None
        Dynamic?                     false

    -Identity 

        Required?                    false
        Position?                    0
        Accept pipeline input?       true (ByValue)
        Parameter set name           (All)
        Aliases                      None
        Dynamic?                     false

    
        This cmdlet supports the common parameters: Verbose, Debug,
        ErrorAction, ErrorVariable, WarningAction, WarningVariable,
        OutBuffer and OutVariable. For more information, see
        about_CommonParameters (http://go.microsoft.com/fwlink/?LinkID=113216).


INPUTS
    Microsoft.Office.Excel.Server.Cmdlet.SPExcelServiceApplicationPipeBind
    Microsoft.Office.Excel.Server.Cmdlet.SPExcelBIServerPipeBind
    Microsoft.SharePoint.PowerShell.SPAssignmentCollection


OUTPUTS
    System.Object

ALIASES
    None


REMARKS
    None

Edit an Analysis Services server - Set-SPExcelBIServer

NAME
    Set-SPExcelBIServer

SYNTAX
    Set-SPExcelBIServer [-Identity]  -ExcelServiceApplication
     [-ServerId ] [-Description ] [-AssignmentCollection
    ] [-WhatIf] [-Confirm]  []


PARAMETERS
    -AssignmentCollection 

        Required?                    false
        Position?                    Named
        Accept pipeline input?       true (ByValue)
        Parameter set name           (All)
        Aliases                      None
        Dynamic?                     false

    -Confirm

        Required?                    false
        Position?                    Named
        Accept pipeline input?       false
        Parameter set name           (All)
        Aliases                      cf
        Dynamic?                     false

    -Description 

        Required?                    false
        Position?                    Named
        Accept pipeline input?       false
        Parameter set name           (All)
        Aliases                      None
        Dynamic?                     false

    -ExcelServiceApplication 

        Required?                    true
        Position?                    Named
        Accept pipeline input?       true (ByValue, ByPropertyName)
        Parameter set name           (All)
        Aliases                      None
        Dynamic?                     false

    -Identity 

        Required?                    true
        Position?                    0
        Accept pipeline input?       true (ByValue)
        Parameter set name           (All)
        Aliases                      None
        Dynamic?                     false

    -ServerId 

        Required?                    false
        Position?                    Named
        Accept pipeline input?       false
        Parameter set name           (All)
        Aliases                      None
        Dynamic?                     false

    -WhatIf

        Required?                    false
        Position?                    Named
        Accept pipeline input?       false
        Parameter set name           (All)
        Aliases                      wi
        Dynamic?                     false

    
        This cmdlet supports the common parameters: Verbose, Debug,
        ErrorAction, ErrorVariable, WarningAction, WarningVariable,
        OutBuffer and OutVariable. For more information, see
        about_CommonParameters (http://go.microsoft.com/fwlink/?LinkID=113216).


INPUTS
    Microsoft.Office.Excel.Server.Cmdlet.SPExcelBIServerPipeBind
    Microsoft.Office.Excel.Server.Cmdlet.SPExcelServiceApplicationPipeBind
    Microsoft.SharePoint.PowerShell.SPAssignmentCollection


OUTPUTS
    System.Object

ALIASES
    None


REMARKS
    None

Remove an Analysis Services server - Remove-SPExcelBIServer

NAME
    Remove-SPExcelBIServer

SYNTAX
    Remove-SPExcelBIServer [-Identity]  -ExcelServiceApplication
     [-AssignmentCollection ] [-WhatIf] [-Confirm]
    []


PARAMETERS
    -AssignmentCollection 

        Required?                    false
        Position?                    Named
        Accept pipeline input?       true (ByValue)
        Parameter set name           (All)
        Aliases                      None
        Dynamic?                     false

    -Confirm

        Required?                    false
        Position?                    Named
        Accept pipeline input?       false
        Parameter set name           (All)
        Aliases                      cf
        Dynamic?                     false

    -ExcelServiceApplication 

        Required?                    true
        Position?                    Named
        Accept pipeline input?       true (ByValue, ByPropertyName)
        Parameter set name           (All)
        Aliases                      None
        Dynamic?                     false

    -Identity 

        Required?                    true
        Position?                    0
        Accept pipeline input?       true (ByValue)
        Parameter set name           (All)
        Aliases                      None
        Dynamic?                     false

    -WhatIf

        Required?                    false
        Position?                    Named
        Accept pipeline input?       false
        Parameter set name           (All)
        Aliases                      wi
        Dynamic?                     false

    
        This cmdlet supports the common parameters: Verbose, Debug,
        ErrorAction, ErrorVariable, WarningAction, WarningVariable,
        OutBuffer and OutVariable. For more information, see
        about_CommonParameters (http://go.microsoft.com/fwlink/?LinkID=113216).


INPUTS
    Microsoft.Office.Excel.Server.Cmdlet.SPExcelBIServerPipeBind
    Microsoft.Office.Excel.Server.Cmdlet.SPExcelServiceApplicationPipeBind
    Microsoft.SharePoint.PowerShell.SPAssignmentCollection


OUTPUTS
    System.Object

ALIASES
    None


REMARKS
    None

Share