Execute PowerShell Script as External Executable

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.


Check and Update the PowerShell Execution Policies in order to ensure that the user executing the script has adequate permissions on the machine running TimeXtender.

Get current Execution Policy

Set current Execution Policy 
Set-ExecutionPolicy RemoteSigned
Install-Module -Name Az -Scope CurrentUser -Repository PSGallery -Force


Creating and Executing a PowerShell Executable

  1. Expand the data warehouse and the underlying data area, and open up the execution pane.mceclip0.png
  2. Right click on the "External Executables" folder and select "Add Execute PowerShell Script Step"mceclip1.png
  3. Add a PowerShell script.mceclip2.png
  4. Click 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.mceclip6.png
  5. After creating the PowerShell executable, try to execute it.mceclip10.png
  6. In SSMS you can see the SSAS database has been updated. mceclip11.png 


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.



Example Scripts

Process Analysis Services Database

Invoke-ProcessASDatabase `
-Server "localhost" `
-DatabaseName "SalesOnPrem" `
-RefreshType "Full"

This example 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. Please note that in the script above, the accent character (`) is used to denote a new line. 

Process a Single table in an Azure Analysis Services Tabular model

$secpasswd = ConvertTo-SecureString "***Pass***" -AsPlainText -Force; `
$mycreds = New-Object System.Management.Automation.PSCredential ('****UID****’, $secpasswd); `
Invoke-ProcessTable -TableName "<tablename>" -Database "<DatabaseName>"  `
-RefreshType Full   -Server "asazure://<AS_serverName>" `
-Credential $mycreds


Scale Up Azure SQL Db

Set-AzSqlDatabase `
  -ResourceGroupName <ResourceGroupName> `
  -ServerName <ServerName> `
  -DatabaseName <DB Name> `
  -Edition "Premium" `
    -RequestedServiceObjectiveName "P1"

Scale down azure SQL Db

Set-AzSqlDatabase `
  -ResourceGroupName <ResourceGroupName> `
  -ServerName <ServerName>`
  -DatabaseName <DB Name> `
  -Edition "Basic"

Pause PBI Embedded

Suspend-AzPowerBIEmbeddedCapacity `
  -Name "<ResourceName>" `
  -ResourceGroupName "JTDemo" `

Change and Overwrite Roles in a SSAS database 

Execute an XMLA script via PowerShell, for example to implement row level security based on CUSTOMDATA(). 

  • Go to the properties of the SSAS role in SSMS


  • Under Row Filters, create the desired DAX filter on the table based on CUSTOMDATA()


  • Script the Role as "CREATE OR REPLACE To" in order to generate the XMLA script.



  • Save the script in a .xmla file.
  • Use the following PowerShell script to execute the XMLA script.
Import-Module SQLPS -DisableNameChecking `
sqlserver: `
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") `
$serverAS = New-Object Microsoft.AnalysisServices.Server `
$serverAS.connect("localhost") `
$db = $serverAS.databases["SalesModel"] `
Invoke-ASCmd -InputFile:"C:\Users\ChristianHauggaard\Desktop\XMLAChangeRole.xmla" 


If an error message similar to the following is returned when executing the script, ensure that the user running the script has adequate permissions, as referred to in the Prerequisites section above.

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


Was this article helpful?
1 out of 1 found this helpful


Please sign in to leave a comment.