A few months ago, I wrote about automating the archiving of Narrative Reporting snapshots with a batch script using EPM Automate. The same process can be used for archiving other EPM Cloud snapshots like Planning. While I’m comfortable writing and using batch scripts for this purpose (and other EPM Automate commands), there is an alternative that can be used: PowerShell. There are pros and cons to using PowerShell. I do not have a programming background, so it’s been challenging to learn, batch scripting was easier for me. But PowerShell opens up more possibilities, it’s more robust, and some clients prefer that scripts running on their servers be PowerShell instead of batch. So, with some help from colleagues and a lot of Googling, I put together a PowerShell script for archiving Planning snapshots, and scheduling it from Windows Task Scheduler.
Our PowerShell script will login to EPM Automate, execute the download, rename and move the snapshot file, and delete old files so as to maintain the most recent downloads. The script also includes logging of all the activity.
- I normally set the number of backups to 10, but it can be whatever number is appropriate for your situation.
- The script name and the paths for the script, log files, and downloaded snapshots.
- The path on the local computer or server where EPM Automate is installed, application URL and login credentials. User has to be provisioned as Service Administrator. Recommended to use encrypted password.
- Variable to get the current date and time, which will be used in the log file and to append the snapshot name.
- Setting the path for the initial download. This is a critical step as I discovered when testing the script with Task Scheduler. Without the “pushd” command to set the location, the initial download was going to an obscure folder on my local C:\ drive and causing issues with the rename and move steps.
- Logging into the environment using the variables defined.
- Writing to the log file if there is an error or if login was successful. This is done throughout the script to record the different steps.
Download the Snapshot
- The snapshot is downloaded with the command “downloadfile”. Included is the error check and logging of the step.
At this point, we have logged into the environment and downloaded the snapshot. The file is in the folder we designated with “pushd”.
Now that the snapshot has been downloaded, the file can be renamed and moved to the archive directory.
Rename and Move
- PowerShell command used to rename file. Variable defined at the beginning is used for the NewName and appended with the timestamp.
- PowerShell command used to move file. Variables defined at the beginning is used for source and destination paths.
Once the file is moved into the destination directory, old snapshots can be deleted. As mentioned previously, the number of files retained is up to you. I typically retain 10, you can change the variable “NumberOfBackups” at the beginning of the script as needed.
- PowerShell commands to count the number of files in the directory and delete the oldest.
To recap, we have logged in, downloaded the snapshot, renamed the file, moved the file to the archive folder, and deleted the oldest file so we have a total of the 10 most recent snapshots saved. All that’s left is to logout. But wait, there’s one more task we can do. Thanks to a colleague for showing me this, I’m mad at myself for not thinking of it. Oracle recommends updating EPM Automate whenever a new version is released, which happens almost monthly. This is easy to accomplish when using EPM Automate on our own computer, but when it’s on a server (as it should be to run the archive script), it can be a more tedious task to get EPM Automate updated. A super easy and efficient way to make sure EPM Automate is always up to date is to incorporate the “upgrade” command into the archive script.
- Run EPM Automate “upgrade” command. If a new version of EPM Automate is available, it will be installed. If the current installed version is up to date, then nothing happens.
- Logout of EPM Automate and exit the script.
Here’s the full script from start to finish.
And here’s what the log looks like.
The last step is to schedule this script to run daily. Windows Task Scheduler works well for this process. It did take some trial and error to get it working correctly, mainly due to the initial location of the downloaded file. But we resolved that by using the “pushd” command to set our folder location at the beginning of the script.
Let’s configure a task in Task Scheduler. Open Task Scheduler and select Create Basic Task.
Name the task and add a description, click Next.
Set the Trigger as Daily, click Next.
Set the date and time for the task to start, click Next.
Select Start A Program for Action, click Next.
This next step was a little tricky. For Program/Script, I entered “powershell.exe”. I found many opinions on the web as to entering the file name by itself or entering the full path and file name. Just the name worked in my situation, it may be different in yours. Similarly, I found multiple ways to configure the argument. For my purposes, entering -File with the full path to the script worked.
- The file name “powershell.exe” by itself, it may be necessary to enter the full path of the file.
- Argument -File “G:\My Drive\xxxxxx\EPMAutomate\epmArchive_PowerShell\epmAutomate_ArchiveSnapshot_Sample.ps1”
Review the task and click Finish.
The task is now available in the library.
- Task Schedule Library
- epm_DailyArchive task
- Scheduled to run at 6am daily
- Task can be Run on-demand
Let’s do an on-demand Run to test the script. A window opens to show the status of the task.
And we can see that there are 10 snapshots archived, most recent at the top.
And the log shows success for all steps.
There you have it, a PowerShell script to archive the daily snapshot from EPM Planning, and to update EPM Automate if a new version is available. The script is scheduled in Windows Task Scheduler to run daily. This script can be used as the starting point for various other EPM Automate tasks. Some that come to mind are to download data exports and archive, download audit logs, and update substitution variables. And since it’s a PowerShell script, maybe sending an email with success or failure would be useful. I’ll save that for a future post.
As always, happy EPM’ng!
Full documentation for EPM Automate can be found at https://docs.oracle.com/en/cloud/saas/enterprise-performance-management-common/cepma/index.html.