This article describes how to create, edit and execute a PowerShell Power as an external executable, as well as how to add this executable in an execution package, to allow for a scheduled execution of the PowerShell script. It also covers required permissions for the execution of the script.
Creating and Executing a PowerShell Executable
Expand the data warehouse and the underlying data area, and open up the execution pane.
Right click on the "External Executables" folder and select "Add Execute PowerShell Script Step"
Then add a PowerShell script. In this example, the below script is used, which runs the Invoke-ProcessASDatabase command, and processes a SSAS database. The use case for this could be that a SSAS database has been developed outside TimeXtender, and it would be useful to be able to process this database on a scheduled basis using TimeXtender, without having to rebuild the SSAS database as a semantic model within TimeXtender. Other commands could also be used such as Invoke-ProcessDimension to process a tabular table, Invoke-ProcessPartition to process a partition, Backup-ASDatabase to backup a tabular database, Add-RoleMember to add a member to Tabular roles, etc.
Invoke-ProcessASDatabase -Server "localhost" -DatabaseName "SalesOnPrem" -RefreshType "Full"
By clicking execute in the top right corner, a message will displayed in the results pane, which will indicate if the script was executed successfully or results in an error. This script executes successfully.
However, the below script refers to a database which does not exist on the specified server, which is shown in the error message.
After creating the PowerShell executable, try to execute it.
The last data refresh or last processed property in SSMS for the SSAS database should now been updated.
Editing the script
The script can be edited by right clicking on the external executable and selecting edit.
The Timeout setting can also be changed for the script. If this is set to 0, the timeout is infinite.
Adding the external executable to a Execution Package
In order to add the external executable to a execution package. Add or edit an execution package and then drag the external executable to the "Include Steps" section.
If an error message similar to the following when executing the script
C:\Users\ChristianHauggaard\AppData\Local\Temp\3cd3f25b-81c8-4acc-a386-9aa6b2218842.ps1 : File
C:\Users\ChristianHauggaard\AppData\Local\Temp\3cd3f25b-81c8-4acc-a386-9aa6b2218842.ps1 cannot be loaded because
running scripts is disabled on this system. For more information, see about_Execution_Policies at
At line:1 char:1
+ C:\Users\ChristianHauggaard\AppData\Local\Temp\3cd3f25b-81c8-4acc-a38 ...
+ CategoryInfo : SecurityError: (:) , PSSecurityException
+ FullyQualifiedErrorId : UnauthorizedAccess
Then the user executing the script needs to have the permissions on the machine running TimeXtender.
Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy Unrestricted