1. Right click on SQL Server Agent -> New -> Job

2. Go to General:

  • Name: should be application’s name + model’s name_RefreshExample: my application name is pcDATA_pcFinancials_E9 & model name is Financials, so here I will name it pcDATA_pcFinancials_E9_Financials_Refresh
  • Owner: should be the service account that running CallistoService 

 

3. Click on Steps -> New:

  • Step name: Refresh (or whatever you think is suitable)
  • Type: PowerShell
  • Run as: SQL Server Agent Service Account
  • Command: as below, yellow part is what you need to modify according to yours

[System.Net.WebRequest]$webRequest = [System.Net.WebRequest]::Create("http://localhost:88/Callisto_Server/callistoadmin.asmx/ProcessModelData")

$webRequest.UseDefaultCredentials = $true

$webRequest.PreAuthenticate = $true

$webRequest.ContentType = "application/x-www-form-urlencoded"

$webRequest.Timeout = 900000

$webRequest.Method = "POST"

$enc = [System.Text.Encoding]::GetEncoding("UTF-8")

[byte[]]$bytes = $enc.GetBytes("sAppLabel=pcDATA_pcFinancials_E9&sModelLabel=Financials")

$webRequest.ContentLength = $bytes.Length

[System.IO.Stream]$reqStream = $webRequest.GetRequestStream()

$reqStream.Write($bytes, 0, $bytes.Length)

$reqStream.Flush()

$resp = $webRequest.GetResponse()

$rs = $resp.GetResponseStream()

[System.IO.StreamReader]$sr = New-Object System.IO.StreamReader -argumentList $rs

Write-Output(([int]$resp.StatusCode).ToString() + "-" + $resp.StatusCode)

Write-Output($sr.ReadToEnd())



4. Click on Schedules -> Click Yes to below window

 

5. In Schedule, click on New (below example is to set every 20 minutes during extended working hours to refresh data in Financials cube)

  • Name: name it what you preferable
  • Schedule type: Recurring (tick Enabled)
  • Frequency / Daily frequency / Duration as example below

 

Important: please check how long the night job takes every night. It’s NECESSARY to avoid that period. (Right click on ***_Load from SQL Server Agent & choose View History)

 

6. Notifications -> tick as below screenshot


7. Please feel free to test it now.