Windows PowerShell is a shell scripting language often used in conjunction with Windows Task Scheduler for the automation of tasks and activities. Being able to automate tasks takes one more thing off an end-user’s plate. In this article, we will explore how to use Windows PowerShell to execute a data management job from OneStream. Data Management jobs in OneStream can be used to consolidate data, clear data, run a calculation, export data, kick off a business rule, and more.
Note: This script works only for clients not authenticating with SSO.
First, we initialize the OneStream Client API by adding the path to the OneStream API located in OneStream’s configuration files. Typically, the path listed below is where the API is located.
Add-Type -Path "C:\Program Files (x86)\OneStream Software\OneStreamStudio\OneStreamClientApi.dll"
Modern Accounting: How to Overcome Financial Close Challenges
Improvements in each of the following period-close core tasks can provide transformative change and are reviewed in this guide include closing the books and external reporting, periodic reconciliations, and managing the period-close process.
Next, we declare a variable that contains the command to create an instance of the API.
$xfApi = new-object OneStream.Client.Windows.OneStreamClientApi
This part is used to log in and open the application (Note: This only works for OneStream native IDs. It will not work for Single-Sign-On). Here, we declare a variable that starts the instance of the API. In the script, you need to define the server address, OneStream user name, password, and application name.
$xfLogonInfo = $xfApi.Authentication.LogonAndOpenApplication("SERVER_ADDRESS", "OneStream_Username", "OneStream_Password", "OneStream_Application", [OneStream.Shared.Wcf.XFClientAuthenticationType]::ClearTextPW)
We run a loop to check if the application was successfully open. If it was, the console prints out that the login was successful. A new variable is then declared to execute the data management sequence. You need to define the name of the data management job and any parameters/variables here. If there are no parameters/variables, leave the second quotes empty, as shown below. Once the data management job is executed, you will get a message printed out in the console to let you know whether there was an error (typically on the OneStream side) or if it was executed successfully. The script then logs out of OneStream and closes out the API connection. If the application was not opened successfully, a message on why the application did not open will be printed in the console.
if ($xfLogonInfo.IsAppOpen) { "Log in successful." "Executing Data Management Sequence..." $xfResult = $xfApi.DataManagement.ExecuteSequence("DataManagementJobName", "") $xfResult.Message $xfApi.Authentication.Logoff() "Logged off." } else { $xfLogonInfo.ErrorMessage }
The completed code looks like this:
Add-Type -Path "C:\Program Files (x86)\OneStream Software\OneStreamStudio\OneStreamClientApi.dll" $xfApi = new-object OneStream.Client.Windows.OneStreamClientApi $xfLogonInfo = $xfApi.Authentication.LogonAndOpenApplication("SERVER_ADDRESS", "OneStream_Username", "OneStream_Password", "OneStream_Application", [OneStream.Shared.Wcf.XFClientAuthenticationType]::ClearTextPW) if ($xfLogonInfo.IsAppOpen) { "Logged on successfully." "Executing Data Management Sequence..." $xfResult = $xfApi.DataManagement.ExecuteSequence("DataManagementJobName", "") $xfResult.Message $xfApi.Authentication.Logoff() "Logged off." } else { $xfLogonInfo.ErrorMessage }