Managing tabular models using PowerShell

Article
08/25/2011

As I mentioned in my previous post on deploying tabular models using msbuild, we now have AMO for PowerShell support. Unfortunately, the documentation for the AS PowerShell cmdlets didn’t make it into TechNet or into the product for CTP3, so they are quite difficult to use at this time (unless you are good at psychically divining functionality and parameters). This post should help fill the gap until RC0, when the docs come online. Once there are docs, you can use Get-Help <cmdlet name> to get help while you are inside the product, and there will also be docs on TechNet.

Getting started with PowerShell cmdlets

The PowerShell cmdlets work for both multidimensional and tabular models. Like AMO, the cmdlets are UDM-shaped. They take measure group, dimension, and cube parameters. This works fine for tabular models as well.

There are two ways to get started using the cmdlets:

Launch the sqlps snap-in from C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\sqlps.exe
If you’d rather use just regular old PowerShell, call the following command to put the AS commands at your disposal:

 Import-Module 'C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLASCMDLETS'

You need to have SSMS installed for either of those to work.

If you are running PowerShell on the same machine as your AS instance, you can just start issuing commands at this point. However, if you are on a different machine, and you want to call commands that do not take a –Server parameter (almost all of them), you will need to navigate to the appropriate server. You do this by issuing the command:

 cd sqlas\<machine name>

Now that you are in the right place, start issuing commands. You can navigate down through the server to see which objects are available, in case you need help figuring out exactly what needs managing. Don’t rely on the processing state reported by PowerShell though – it does not seem to be working properly.

Note: For CTP3, only the Invoke-ASCmd cmdlet works with tabular named instances. All the other cmdlets fail. This problem should be fixed with RC0. So for now, only try the other cmdlets that I show here on a default tabular instance.

Example 1: Processing

You have two cmdlets dedicated to processing at your disposal – Invoke-ProcessDimension and Invoke-ProcessPartition. For Denali, Invoke-ProcessCube does not work for tabular models. This means that if you want to process your database, you need to use the Invoke-ASCmd cmdlet to execute the required XMLA. I will show you how to do this in Example 4.

Here are two examples of calling Invoke-ProcessDimension. One call is interactive, where I provided the parameters in sqlps.exe after issuing the command. In the other call, I provided the parameters when I called the command.

 PS SQLSERVER:\> Invoke-ProcessDimension

cmdlet Invoke-ProcessDimension at command pipeline position 1
Supply values for the following parameters:
Name: DimCurrency
Database: TabularProject1
ProcessType: ProcessClear

PS SQLSERVER:\> Invoke-ProcessDimension -Name DimCurrency -Database TabularProject1 -ProcessType ProcessFull

Here is the second example, two successive calls of Invoke-ProcessPartition.

 PS SQLSERVER:\> Invoke-ProcessPartition

cmdlet Invoke-ProcessPartition at command pipeline position 1
Supply values for the following parameters:
CubeName: Model
MeasureGroupName: DimCurrency
Name: DimCurrency
Database: TabularProject1
ProcessType: ProcessData

PS SQLSERVER:\> Invoke-ProcessPartition -CubeName Model -MeasureGroupName DimCurrency -Name DimCurrency -Database TabularProject1 -ProcessType ProcessDefault

Example 2: Managing Roles

There are two cmdlets for you to use for role management – Add-RoleMember and Remove-RoleMember. Both do what they say on the tin. Here are two examples for Add-RoleMember:

 PS SQLSERVER:\> Add-RoleMember

cmdlet Add-RoleMember at command pipeline position 1
Supply values for the following parameters:
MemberName: redmond\cathyk
Database: TabularProject1
RoleName: Data Readers

PS SQLSERVER:\> Add-RoleMember -MemberName redmond\cathyk -Database TabularProject1 -RoleName "Data Readers"

I will omit the example for Remove-RoleMember since it takes exactly the same parameters.

Example 3: Backup and Restore

For backup and restore, you can use the Backup-ASDatabase and Restore-ASDatabase cmdlets.

Let’s start with Backup-ASDatabase. The interactive version just backs up your database. It uses the default backup directory for the server (OLAP\Backup) if you do not specify the path.

 PS SQLSERVER:\> Backup-ASDatabase

cmdlet Backup-ASDatabase at command pipeline position 1
Supply values for the following parameters:
BackupFile: Model.abf
Name: TabularProject1
The database backup operation completed successfully.

You can do all the things you’d expect with backing up your database, such as compressing it and encrypting it, by specifying the appropriate parameters at the command line. Here I am going to do two commands. First, I will create a super-secret password. Then, I’ll encrypt my ABF with it.

 PS SQLSERVER:\> $pwd = ConvertTo-SecureString "hello world" -asplaintext -force

PS SQLSERVER:\> Backup-ASDatabase "C:\temp\model.abf" TabularProject1 -ApplyCompression -FilePassword $pwd
The database backup operation completed successfully.

Now to restore. I will skip the interactive example, I think you get the point now. Here, I will now restore the image I just created, providing the password and forcing overwrite:

 PS SQLSERVER:\> Restore-ASDatabase "C:\temp\model.abf" TabularProject1 -AllowOverwrite -Password $pwd

Example 4: Running arbitrary commands

Invoke-ASCmd is a great cmdlet because it allows you to do anything you want.

Here is an important example. Some processing operations (ProcessAdd, ProcessData, ProcessClear) leave your model in an unqueryable state. I will explain why in another post. It is very important to do a ProcessRecalc after performing these operations to rebuild the internal structures that allow your model to be queried.

Let’s use Invoke-ASCmd to do a ProcessRecalc. Here is the XMLA you want to execute:

 <Process xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">
    <Type>ProcessRecalc</Type>
  <Object>
    <DatabaseID>TabularProject1</DatabaseID>
  </Object>
</Process>

To do this at the command line, we will stuff this XMLA in a variable and then execute the command. I am just going to use a very long line to avoid doing file reading or string concatenation, use common sense in the real world.

 PS SQLSERVER:\> $processRecalcCommand = "<Process xmlns="https://schemas.microsoft.com/analysisservices/2003/engine"><Type>ProcessRecalc</Type><Object><DatabaseID>TabularProject1</DatabaseID></Object></Process>"

PS SQLSERVER:\> Invoke-ASCmd -Query $processRecalcCommand
<return xmlns="urn:schemas-microsoft-com:xml-analysis"><root xmlns="urn:schemas
-microsoft-com:xml-analysis:empty"></root></return>

Invoke-ASCmd also takes –Server and –Credentials if you need them to connect to the server. Substitute –InputFile for –Query as needed if you are going to read from file.

Feel free to play around with these cmdlets and send your feedback. Have fun.