Introduction
In this article, we will guide you through the steps to create and schedule the automated process of deploying an application, syncing up AD users, and managing workflow plans.
Create PowerShell Script
1. Open Notepad, create a new file named Task.ps1, and save it in a location accessible to the SQL Server Agent.
2. Copy and paste the following PowerShell commands to Task.ps1:
## Web Service Definition
$url = "http://<KEPION_SERVER_NAME>:8888/Services/Admin.svc?wsdl" ## Setup Proxy
$proxy = New-WebServiceProxy -uri $url -namespace WebServiceProxy -UseDefaultCredential
$proxy.Timeout = [System.Int32]::MaxValue ## Call Web Service
## Same as Deploy to OLAP from UI (Relational, OLAP, Process step from UI)
$proxy.UpdateOlapDatabase("<APPLICATION_NAME>")
Write-Output "UpdateOlapDatabase '<APPLICATION_NAME>'"
## Same as AD Sync + Save (Security step from UI, plus AD Sync)
$proxy.UpdateOlapSecurity("<APPLICATION_NAME>")
Write-Output "UpdateOlapSecurity '<APPLICATION_NAME>'"
## System wide AD Sync
$proxy.ADSyncForAllApplications()
Write-Output "ADSyncForAllApplications()"
## Purge Plan – Clears all saved plans
$proxy.ClearPlan("<APPLICATION_NAME>","<APP_NAME>")
Write-Output "ClearPlan '<APPLICATION_NAME>' '<APP_NAME>'"
## Reset Plan – Updates forms in saved plans with latest definition and variables
$proxy.ResetPlan("<APPLICATION_NAME>","<APP_NAME>")
Write-Output "ResetPlan '<APPLICATION_NAME>' '<APP_NAME>'"
The automation file contains PowerShell commands that can communicate with Kepion Server.
3. Replace the following placeholders with appropriate values:
| Placeholder | Description |
|---|---|
| <KEPION_SERVER_NAME> | The name of the web server for Kepion Planning. |
| <APPLICATION_NAME> | The name of the Kepion Application/database to automate. |
| <APP_NAME> | The name of the App. This value can be found in the AuthorizationName column of the [dbo].[Authorizations] table. |
Connect to SQL Server Agent
1. Open SQL Server Management Studio and connect to your SQL Server.
2. Locate SQL Server Agent can be found under the server name.
3. Ensure the Server Agent is on. Skip to the next section if it is.
Start SQL Server Agent
If the Server Agent is on, right-click SQL Server Agent and click Start.
Click Yes.
Verify Domain Account
Ensure you have a domain account that can be used to connect to Kepion Planning for automation. This account will need to be assigned with both Modeler and Administrator privileges or be a system admin within Kepion.
Tip: Refer to System Security Roles to learn how to assign these security privileges.
Create and Configure Credentials
1. Under Security, right-click Credentials and select New Credentials….
2. Enter the Domain Account from the Verify Domain Account step in Identity and the Password. 3. Click OK.
Create and Configure Proxy
1. Under SQL Server Agent, right-click Proxies and select New Proxy….
2. Enter a proxy name (e.g., Kepion Automation Proxy).
3. Select the credential you created in the previous section and select the PowerShell checkbox.
4. Click OK.
Create Job
Under SQL Server Agent, right-click Jobs and select New Job….
General
Give the job a name (e.g., Automate Kepion Task) and then click on Steps (under Select a page)
Create Step
Click New… to define a new step. The step will contain an “action” that this job will run.
Define Job Step
1. Enter a Step name (e.g., Call Kepion Web Service).
2. Select PowerShell from the Type drop-down, and select the proxy you created from the Run as drop-down.
3. Copy the script from the Create PowerShell Script section, paste it to the Command window, and ensure you have edited it correctly. OR click Open... to locate the Task.ps1, which will load the script content from the file to the Command window.
4. Click OK.
Create and Configure Schedule
1. Click New… to schedule times for the job to run.
2. Configure the job to run on your desired schedule.
In the example below, we are creating a weekly schedule to run on every Sunday at 12 AM.
Test Job
Right-click the newly created job and select Run. You should be able to see a Success message like the one below. If not, please check the error message, fix the issue and test again.