There are many blog posts showing how to automate processing of a SQL Server Analysis Services model using either the SQL server agent or SSIS.
There are also many posts out there showing different ways of processing an Azure Analysis Services database through the use of Azure Functions, automation accounts, and runbooks. (SQLDusty.com, technet, Azure blog, official AAS docs, byobi.com)
All of the above require some level of server administration privileges or Azure resource deployment.
What if you don’t have server admin rights or can’t create Azure resources?
What if all you’ve got is the “process” rights granted to a role that you belong to?
If that’s the case, then this post is for you!
The strategy is relatively simple and uses PowerShell in combination with Task Scheduler.
You will require:
- administration rights over a local machine in order to enable PowerShell scripting execution
- an internet connection in order to send the processing command to the server
- an account with processing rights over the database
Here are the steps that you need to follow:
1. Change the execution policy to allow you to execute PowerShell scripts. Run the below script as an administrator:
Set-ExecutionPolicy remotesigned
2. Make sure you have the SQLServer PowerShell module installed. Information can be found here:
Install-Module -Name SqlServer -force -allowclobber
3. You may now want to test that you can process the database via PowerShell by executing the following code. Information can be found here:
Invoke-ProcessASDatabase -Server "YourServerName" -DatabaseName "YourDatabaseName" -RefreshType "RefreshTypeHere"
Here’s an example:
Invoke-ProcessASDatabase -Server "asazure://region.asazure.windows.net/databaseinstance1" -DatabaseName "Database1" -RefreshType "Full"
You should be prompted for your login credentials, enter the account that has access to process the model.
4. To automate the script, you should first create an encrypted password for your account.
To do this, execute the script at the end of this step. It will ask you for your username and password credentials.
Use the credentials for the account that has access to process the database, e.g. domain\username if on SQL Server Analysis Services, or the UPN (i.e. username@domain.com) for Azure Analysis Services.
The script will then create an encrypted password text file for the account where you ran the code from.
Make sure you place this encrypted password somewhere that it can be accessed by an unattended account, ideally just in a new folder on the C: drive. For this example I have stored it in C:\Scripts\
Further information can be found here:
$credential = Get-Credential
$credential.Password | ConvertFrom-SecureString | Set-Content c:scriptsencrypted\_password1.txt\
5. Next you will want to create the script that processes the model using your credentials (username and encrypted password). Again, make sure that your encrypted password file is accessible by an unattended account and is not in any specific user folders. If you run the below script it should execute without any prompts.
$username = "username@domain.com"
$encrypted = Get-Content "C:\\Scripts\\scriptsencrypted\_password.txt" | ConvertTo-SecureString
$cred = New-Object System.Management.Automation.PsCredential($username, $encrypted)
Invoke-ProcessASDatabase -Server "asazure://domain.asazure.windows.net/databaseinstance1" -DatabaseName "database1" -RefreshType "Full" -Credential $cred\
6. The final step is to configure Task Scheduler to run the script. First you should create a new task, name it whatever you want (i.e. Process Analysis Services). Select the user that will have access to run the script, it can be the same account that you’re using to set up this task. You can also select “Run whether user is logged on or not”, and “run with highest privileges”.
Next create a new trigger according to your own required schedule. I’ll leave this for you to work out.
Finally, create a new action, When creating a new action, you want to run the PowerShell executable, it should be C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
You can test this by opening Run and the path above in and seeing if PowerShell opens.
In the arguments section you should add the -file argument followed by the path to your local PowerShell script. Note that the PowerShell script must be located somewhere that the account can access it from. It is probably best to also put this in your C drive next to your encrypted password file.
7. After you’ve set up the above, you can test to see if it processes! Note that because the task is set to run whether the user is logged on or not, you will not see the PowerShell window open, it will instead run in the background.
