Analysis Services PowerShell

Article
03/11/2017

SQL Server 2014 Analysis Services (SSAS) includes an Analysis Services PowerShell (SQLAS) provider and cmdlets so that you can use Windows PowerShell to navigate, administer, and query Analysis Services objects.

Analysis Services PowerShell consists of the following:

SQLAS provider used for navigating the Analysis Management Object (AMO) hierarchy.
Invoke-ASCmd cmdlet used for executing MDX, DMX, or XMLA script.
Task-specific cmdlets for routine operations, such as processing, role management, partition management, backup and restore.

Prerequisites

Windows PowerShell 2.0 must be installed. It is installed by default on newer versions of the Windows operating systems. For more information, see Install Windows PowerShell 2.0

You must install a SQL Server feature that includes the SQL Server PowerShell (SQLPS) module and client libraries. The easiest way to do this is by installing SQL Server Management Studio, which includes the PowerShell feature and client libraries automatically. The SQL Server PowerShell (SQLPS) module contains the PowerShell providers and cmdlets for all SQL Server features, including the SQLASCmdlets module and SQLAS provider used for navigating the Analysis Services object hierarchy.

You must import the SQLPS module before you can use the SQLAS provider and cmdlets. The SQLAS provider is an extension of the SQLServer provider. There are several ways to import the SQLPS module. For more information, see Import the SQLPS Module.

Remote access to an Analysis Services instance requires that you enable remote administration and file sharing. For more information, see Enable Remote Administration in this topic.

Supported Versions and Modes of Analysis Services

Currently, Analysis Services PowerShell is supported on any edition of SQL Server 2014 Analysis Services running on Windows Server 2008 R2, Windows Server 2008 SP1, or Windows 7.

The following table shows the availability of Analysis Services PowerShell in different contexts.

Context	PowerShell Feature Availability
Multidimensional instances and databases	Supported for local and remote administration. Merge-partition requires a local connection.
Tabular instances and databases	Supported for local and remote administration. For more information, see an August 2011 blog about Manage Tabular Models Using PowerShell.
PowerPivot for SharePoint instances and databases	Limited support. You can use HTTP connections and the SQLAS provider to view instance and database information. However, using the cmdlets is not supported. You must not use Analysis Services PowerShell to backup and restore in-memory PowerPivot database, nor should you add or remove roles, process data, or run arbitrary XMLA script. For configuration purposes, PowerPivot for SharePoint has built-in PowerShell support that is provided separately. For more information, see PowerShell Reference for PowerPivot for SharePoint.
Native connections to local cubes "Data Source=c:\backup\test.cub"	Not supported.
HTTP connections to BI semantic model (.bism) connection files in SharePoint "Data Source=https://server/shared_docs/name.bism"	Not supported.
Embedded connections to PowerPivot databases "Data Source=$Embedded$"	Not supported.
Local server context in Analysis Services stored procedures "Data Source=*"	Not supported.

Authentication Requirements and Security Considerations

When connecting to Analysis Services, you must make the connection using a Windows user identity. For the most part, a connection is made using Windows integrated security, where the identity of the current user sets the security context under which server operations are performed. However, additional authentication methods become available when you configure HTTP access to Analysis Services. This section explains how the type of connection determines which authentication options you can use.

Connections to Analysis Services are characterized as either native connections or HTTP connections. A native connection is a direct connection from a client application to the server. In a PowerShell session, the PowerShell client uses the OLE DB provider for Analysis Services to connect directly to an Analysis Services instance. A native connection is always made using Windows integrated security, where Analysis Services PowerShell executes as the current user. Analysis Services does not support impersonation. If you want to perform an operation as a specific user, you must start the PowerShell session as that user.

HTTP connections are made indirectly through IIS, allowing for additional authentication options, such as Basic authentication, to connect to an Analysis Services instance. Because IIS supports impersonation, you can provide a connection string that includes credentials IIS will use to impersonate when making a connection. To provide credentials, you can use the -Credential parameter.

Using the -Credential Parameter in PowerShell

The -Credential parameter takes a PSCredential object that specifies a user name and password. In Analysis Services PowerShell, the -Credential parameter is available for cmdlets that make a connection request to Analysis Services, as opposed to cmdlets that run within the context of an existing connection. Cmdlets that make a connection request include Invoke-ASCmd, Backup-ASDatabase, and Restore-ASDatabase. For these cmdlets, the -Credential parameter can be used, assuming the following criteria are met:

The server is configured for HTTP access, which means that IIS handles the connection, reads the user name and password, and impersonates that user identity when connecting to Analysis Services. For more information, see Configure HTTP Access to Analysis Services on Internet Information Services (IIS) 8.0.
The IIS virtual directory that was created for Analysis Services HTTP access is configured for Basic authentication.
The user name and password provided by the credential object resolves to a Windows user identity. Analysis Services uses this identity as the current user. If the user is not a Windows user, or lacks sufficient permissions to perform the requested operation, the request will fail.

To create a credential object, you can use the Get-Credential cmdlet to collect the credentials from the operator. You can then use the credential object on a command that connects to Analysis Services. The following example illustrates one approach. In this example, the connection is to a local instance (SQLSERVER:\SQLAS\HTTP_DS) configured for HTTP access.

$cred = Get-Credential adventureworks\dbadmin  
Invoke-ASCmd -Inputfile:"c:\discoverconnections.xmla" -Credential:$cred

When using Basic authentication, you should always use HTTPS with SSL so that username and passwords are sent over an encrypted connection. For more information, see Configure Secure Sockets Layer in IIS 7.0 and Configure Basic Authentication (IIS 7).

Remember that credentials, queries, and commands that you provide in PowerShell are passed unchanged to the transport layer. Including sensitive content in your scripts increases the risk of a malicious injection attack.

Providing a password as a Microsoft.Secure.String object

Some operations, such as backup and restore, support encryption options that are activated when you provide a password in the command. Providing the password signals Analysis Services to encrypt or decrypt the backup file. In Analysis Services, this password is instantiated as a secure string object. The following example provides an illustration of how to collect a password from the operator at run time.

$pwd = read-host -AsSecureString -Prompt "Password"  
$pwd -is [System.IDisposable]

You can now backup or restore an encrypted database file, passing the $pwd variable to the password parameter. To view a complete example that combines this illustration with other cmdlets, see Backup-ASDatabase cmdlet and Restore-ASDatabase cmdlet.

As a follow up step, remove both the password and variable from the session.

$pwd.Dispose()  
Remove-Variable -Name pwd

Analysis Services PowerShell Tasks

You can run Analysis Services PowerShell from the Windows PowerShell management shell or a Windows command prompt. Running Analysis Services PowerShell from SQL Server Management Studio is not supported.

This section describes common tasks for using Analysis Services PowerShell.

Load the Analysis Services Provider and Cmdlets
Enable Remote Administration
Connect to an Analysis Services Object
Administer the Service
Get Help for Analysis Services PowerShell

Load the Analysis Services Provider and Cmdlets

The Analysis Services provider is an extension of the SQL Server root provider that becomes available when you import the SQLPS module. Analysis Services cmdlets are loaded simultaneously; you can also load them independently if you want to use them without the provider.

Run the Import-module cmdlet to load SQLPS that includes all of the Analysis Services PowerShell functionality. If you cannot import the module, you can temporarily change the execution policy to unrestricted for the purpose of the loading the module. For more information, see Import the SQLPS Module.
```
Import-Module "sqlps"  
```
Alternatively, use import-module "sqlps" -disablenamechecking to suppress the warning about unapproved verb names.
To load just the task-specific Analysis Services cmdlets, without the Analysis Services provider or the Invoke-ASCmd cmdlet, you can load the SQLASCmdlets module as an independent operation.
```
Import-Module "sqlascmdlets"  
```

Enable Remote Administration

Before you can use Analysis Services PowerShell with a remote Analysis Services instance, you must first enable remote administration and file sharing. The following error indicates a firewall configuration issue: "The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)".

Verify that both local and remote computers have the SQL Server 2014 Analysis Services (SSAS) versions of the client and server tools.
On the remote server that is hosting an Analysis Services instance, open TCP port 2383 in Windows Firewall. If you installed Analysis Services as a named instance or are using a custom port, the port number will be different. For more information, see Configure the Windows Firewall to Allow Analysis Services Access.
On the remote server, verify that the followings services are started: Remote Procedure Call (RPC) service, TCP/IP NetBIOS Helper service, Windows Management Instrumentation (WMI) service, Windows Remote Management (WS-Management) service.
On the remote server, start the Group Policy Object Editor snap-in (gpedit.msc).
Open Computer Configuration, open Administrative Templates, open Network, open Network Connections, open Windows Firewall, and then open Domain Profile.
Double-click Windows Firewall: Allow inbound remote administration exception, select Enabled, and then click OK.
Double-click Windows Firewall: Allow inbound file and printer sharing exception, select Enabled, and then click OK.
On the local computer that has the client tools, use the following cmdlets to verify remote administration, substituting the actual server name for the remote-server-name placeholder. Omit the instance name if Analysis Services is installed as the default instance. You must have previously imported the SQLPS module in order for the command to work.
```
PS SQLSERVER:\> cd sqlas
PS SQLSERVER:\sqlas> cd <remote-server-name\instance-name>  
PS SQLSERVER:\sqlas\<remote-server-name\instance-name> dir  
```

In some cases, additional configuration might be necessary. You might need to type the following on the remote server before you can issue commands to it from another computer:

Enable-PSRemoting

Connect to an Analysis Services Object

The Analysis Services PowerShell provider supports navigation of the Analysis Services object hierarchy and sets the context for running commands. The provider is an extension of the SQLSERVER root provider available through the SQLPS module. After you load the SQLPS module, you can navigate the path.

You can connect to a local or remote instance, but some cmdlets only run on a local instance (namely, merge-partition). You can use a native connection or an HTTP connection for Analysis Services servers that you configured for HTTP access. The following illustrations show the navigation path for native and HTTP connections. The following illustrations show the navigation path for native and HTTP connections.

Native Connections to Analysis Services

Native connection to Analysis Services

The following example is a demonstration of how to use a native connection to navigate object hierarchy. From the provider, you can issue a dir to view instance information. You can use cd to view objects of that instance.

PS SQLSERVER:> cd sqlas  
PS SQLSERVER\sqlas:> dir  
PS SQLSERVER\sqlas:> cd localhost\default  
PS SQLSERVER\sqlas\localhost\default:> dir

You should see the following collections: Assemblies, Databases, Roles, and Traces. Continuing to use cd and dir, you can view the contents of each collection.

HTTP Connections to Analysis Services

HTTP Connection to Analysis Services

HTTP connections are useful if you configured your server for HTTP access using the instructions in this topic: Configure HTTP Access to Analysis Services on Internet Information Services (IIS) 8.0

Assuming a server URL of https://localhost/olap/msmdpump.dll, a connection might look like the following:

PS SQLSERVER\sqlas:> cd http_ds  
PS SQLSERVER\sqlas\http_ds:> $Url=Encode-SqlName "https://localhost/olap/msmdpump.dll"  
PS SQLSERVER\sqlas\http_ds:> cd $Url  
PS SQLSERVER\sqlas\http_ds\http%3A%2F%2Flocalhost%2olap%2msmdpump%2Edll:> dir

You should see the following collections: Assemblies, Databases, Roles, and Traces. If you cannot view the contents of these collections, check the authentication settings on the OLAP virtual directory. Make sure that Anonymous Access is disabled. If you are using Windows Authentication, be sure that your Windows user account has administrative permissions on the Analysis Services instance.

Administer the Service

Verify the service is running. Returns status, name, and display name for SQL Server services, including Analysis Services (MSSQLServerOLAPService) and the Database Engine.

Get-Service mssql*

Returns properties about a process, including process ID, handle count, and memory usage:

Get-Process msmdsrv

Restarts the service when you issue the following cmdlet from the administrator shell:

Restart-Service mssqlserverolapservice

Get Help for Analysis Services PowerShell

Use any of the following cmdlets to verify cmdlet availability and to get more information about services, processes, and objects.

Get-Help returns the built-in help for an Analysis Services cmdlet, including examples:
```
Get-Help invoke-ascmd -Examples  
```
Get-Command returns a list of the eleven Analysis Services PowerShell cmdlets:
```
Get-Command -module SQLASCmdlets  
```

Get-Member returns properties or methods of a service or process.

Get-Service mssqlserverolapservice | Get-Member -Type Property

Get-Service mssqlserverolapservice | Get-Member -Type Method

Get-Process msmdsrv | Get-Member -Type Property

Get-Member can also be used to return properties or methods of an object (for example, AMO methods on the server object) using the SQLAS provider to specify the server instance.

PS SQLSERVER:\sqlas\localhost\default > $serverObj = New-Object Microsoft.AnalysisServices.Server  
PS SQLSERVER:\sqlas\localhost\default > $serverObj = | Get-Member -Type Method

Get-PSdrive returns a list of the providers that are currently installed. If you imported the SQLPS module, you will see the SQLServer provider in the list (SQLAS is part of the SQLServer provider and never appears separately in the list):
```
Get-PSDrive  
```

Share via