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 Smile this is about the mechanism not what is possible with PowerShell (hint a lot Smile)

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:

image

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:

image

Now lets schedule this with the Task Scheduler:

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

image

(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:

image

 

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

image

 

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

image

 

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.

  • Milhouse77BS

    Good idea. I’ve used VBA from Excel scheduled from Task Scheduler, but this is lightweight.

  • Gilbert Quevauvilliers

    Great blog post. I was recently using task scheduler recently for a Powershell script that was running every 2 hours on Windows Server 2012. It had to be run under the Administrator context. And found that after a few days the task scheduler started not completing the task. And to confirm that I was running the scheduled task via command prompt via SSIS. My solution was to run the Powershell script within SQL Server agent. And it works flawlessly.

  • Pingback: Something to read (W35) | Steven's BI Blog()