Managing tabular models using PowerShell
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="http://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="http://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.