Use Task Scheduler and PowerShell to process your SSAS database

Most of the times you will use SSIS or a SQL Agent job to process a SSAS model, but sometimes you don’t have either one at your disposal. Today we will look at using the regular Windows Task Scheduler and PowerShell to process a partition.

The first thing you have to make sure of is that the SQL Server PowerShell commandlets are installed, more on this in this help document. In short if you have SSMS on this machine, you should be fine.

Next you need to get the command you want to execute. In my case I just go to SSMS and capture the command used to process a partition:

{
“refresh”: {
“type”: “automatic”,
“objects”: [
{
“database”: “Sales”,
“table”: “DimDate”,
“partition”: “DimDate”
}
]
}
}

As you can see this is a SQL Server 2016 TMSL command but this will also work for XMLA. I then save this to a file in my case “c:\temp\query.xmla”.

Next I construct the Power Shell script. I selected to use the Invoke-ASCmd command. I could also have selected to use individual cmlets like Invoke-ProcessDimension to execute tables or partitions. This might be interesting in a real world case where I want add some logic to my processing scheme but for now I keep it simple this is about the mechanism not what is possible with PowerShell (hint a lot )

Here is the command I finally used:

Invoke-ASCmd –InputFile “C:\temp\query.xmla” -Server “localhost”

Calling this from the PS command shell gives me the resultset returned from AS:

I could write some PowerShell that would analyze these results and do something with it.

To be able to call this command from PowerShell I need to create yet another file that contains the PowerShell command. This is really straightforward, just create a new file called .ps1 and paste in the command. Done..

Now executing this gives me the same results:

Now lets schedule this with the Task Scheduler:

First I open the Task Scheduler and add a new Task:

(Observe I did select “Run whether user is logged on or not”).

Next is to define a new action, using the following command””:

powershell -file “c:\temp\command.ps1”

The scheduler is smart enough to figure out which part is the command and which the parameters, so it takes care of it for us:

Lastly I went back to the triggers tab to actually add a schedule:

This made it ready to run, in my case I manually started it:

Voila that gives you a light weight scheduler for your SSAS tasks. If you want to read more on PowerShell and the task manager check out this blog here and here with more on setting up the task scheduler with Power Shell.