Automation for Processing Batch Burst Report Outputs in Oracle EPBCS and PBCS

This KPI blog post is the last of a 3-part series on nuanced topics pertaining to Financial Report (FR) batch bursting and distribution for Oracle EPBCS and PBCS. The first part of the series is an earlier blog post entitled “Batch Bursting of Reports by Property”, written in December 2017 and available here:  http://www.keyperformanceideas.com/batch bursting-of-reports-by-property-in-oracle-epbcs-and-pbcs

The second part of the series is an earlier blog post entitled “Scheduling and Outputting Reports via Batch Bursting in Oracle EPBCS and PBCS”, written in March 2018 and available here:  http://www.keyperformanceideas.com/scheduling-and-outputting-reports-via-batch-bursting-in-oracle-epbcs-and-pbcs

Are you using Oracle Enterprise Planning and Budgeting Cloud Service (EPBCS) or Planning and Budgeting Cloud Service (PBCS) today for reporting purposes? If so, have you ever wanted to pull report snapshots out of the Cloud and into your organization’s internal repository in a streamlined and automated manner? 

If your answers are “yes” – then this blog post is for you! In this article, we’ll be picking up from a sub-section of the previous blog post “Scheduling and Outputting Reports via Batch Bursting in Oracle EPBCS and PBCS”. For your reference, this sub-section is Output Option (2) – “Snapshots Output to the Inbox/Outbox Explorer”. With that output option, we covered a means to produce, and pull down, report snapshot(s) from the Cloud.

In this blog post, we’ll look at an option for pulling down the report snapshot(s) through an automated means, as well as a path for archival in a shared environment, such that non-Cloud team users can easily access them.

Recap: What is Batch Bursting?

“Batch Bursting” is a functionality that allows you to run (“burst”) a pre-defined “batch” of reports (and/or related items) across a set of parameters (for example – a set of members, e.g., reports for all lowest-level departments in the Department dimension). Multiple Financial Reports (as well as FR “Books” and other items) can be added within a designated “batch”, and that “batch” can then be bursted via a schedule or in an ad hoc fashion. The snapshots commonly have an output format of PDF – and that output can be produced within Oracle Cloud and then downloaded (for example, from the Inbox/Outbox Explorer), and/or, emailed to designated recipients. Batch Bursting is beneficial in multiple business contexts because it allows for:

  • Time saving – no need to run report member-by-member
  • Distribution (e.g., emailing the output to recipient(s))
  • Convenient means of snapshot archival (e.g., through Inbox/Outbox Explorer)

Recap: Snapshots Output to the Inbox/Outbox Explorer

One of the report snapshot output destinations allowed by the batch bursting process is “ExportFolder”:

“ExportFolder” is actually embedded within “Inbox/Outbox Explorer”, accessible within Oracle PBCS/EPBCS:

Specifically, the output is stored in the “Scheduler Output” folder within the Inbox/Outbox Explorer:

And, once inside this sub-folder, it is easy enough to download the bursted batch and unzip:


Automating Download of FR Snapshots from Oracle PBCS/EPBCS

Now that we’ve finished our recap, let’s look at a means for automating download of the bursted batch.

Provided you have the security provisioning, begin by setting up EPM Automate, if you haven’t already. EPM Automate is a utility that facilitates automation for Oracle Cloud. More detail is available here: https://docs.oracle.com/cloud/latest/epm-common/CEPMA/using_epmctl.htm#CEPMA-using_pbcs_book_2570

The utility can be downloaded from the Oracle PBCS/EPBCS Simplified UI directly, either for Windows or for Linux:

Once downloaded and installed (on a local machine or preferably a server environment), we can begin to build a script to facilitate the automation of the snapshot(s) download. Begin by opening a new text file (perhaps via an editor such as Notepad++):

Let’s assume we are in a Windows environment. Next steps therefore will involve Windows batch scripting. We can begin by adding commentary for context:

*Tip: Using “@echo on” allows for you to more easily visualize the code as it is being processed through Command Prompt. And “REM” allows you to make comments.

Additionally, we must point to the EPM Automate utility. In this example, we have the utility located in the “C” drive:

Next, “login” to your Oracle Cloud environment using the “login” command. Per the documentation, the detail for this command is:

For the sake of example, we will use an unencrypted password and apply this command to our script, which at this point looks like:

Now, for a more nuanced piece. If we were to run this script as a “.bat” file at this point via Command Prompt, it would log in to the system (just like we manually do for PBCS). But after that – we would have to figure out how to navigate to our report snapshots of interest, and then download them.

An EPM Automate command that can facilitate this task is: “downloadfile”:

Looking at this “Usage”, we need to identify the file path where our report snapshots are being stored on Oracle Cloud. Again, this location is within “Scheduler Output”:

So, the nuance is that the file path is “Scheduler Output” and we can call this out directly in the script:

Note that at the time of this writing, this path is not explicitly called out as an option in the EPM Automate command documentation, but it works just as one would expect!

Also please note that line 11 in the screenshot above is incomplete. We also need to accommodate the time frame for the produced output, for example:


Accommodating this piece of the filename can be even more nuanced, especially when we consider scheduling our automation script to download the snapshot ZIP file. If you would like to discuss options for handling this time frame aspect, please reach out via my email below or with Key Performance Ideas! 

Automating Unzipping of FR Snapshots Following Download

Let’s assume that at this point we are able to successfully download our FR snapshot output. In that case, let’s extend the scripting such that we are able to unzip the ZIP file downloaded and archive the PDF files it contains such that others from the organization can access them. For the sake of time, we’ll discuss in summary.

We can logout of Oracle Cloud via EPM Automate by using the “logout” command:

At this point, we have downloaded the report snapshots and are no longer connected to Oracle PBCS/EPBCS.

At the time of this writing, the downloaded ZIP file containing the report snapshots by default will download to the same folder in which the EPM Automate utility lives. We can move it from there to a more suitable location using the “Move” batch command:

Note: %BatchBurstFile% is an example batch variable that delineates the name of the ZIP file of interest. Batch variables allow for more dynamic processes and should be integrated into scripting where possible.

In this example, we’re moving the ZIP file to a more easily accessible location for the organization: “Company_Shared_Drive”:

At this point, if we ran the script we can assume the ZIP file made it to this location. We can then use VB (Visual Basic) Scripting to unzip the file if we wanted to. VBS is a separate topic, but the key in the VBS code would be to delineate the “from” (i.e., the ZIP file location) and “to” (where you’d like to extract the content to).

For example, part of that code might look like:

Once the VBS code is run, we have successfully isolated our reports of interest. We can therefore delete the original ZIP file via automation using the delete command, “Del”. For example:

So at this point, if we were to run the script we’d have our report snapshots downloaded and isolated, out of the ZIP folder and in PDF format.

Automating Archival of FR Snapshots Following Download

Now that we have them in PDF format and in a state that is consumable for other, non-PBCS/EPBCS users, we can archive the report snapshots as we would want.

If you’ll recall, in the precursor blog post “Scheduling and Outputting Reports via Batch Bursting in Oracle EPBCS and PBCS”, part of what was discussed was naming convention for our snapshot reports. In our example, this customization is defined as:

So in our particular case, if the “<<FinancialReportingObjectName()>>”” is defined as “Income_Statement”, and the “<<MemberName()>>” is the Department dimension member name that we are bursting across, assume one of my snapshots defined by:

<<FinancialReportingObjectName()>>-<<MemberName()>>

is labeled “Income_Statement-Accounting”. (And another is “Income_Statement-Finance_Organization”, and another is “Income_Statement-….”, etc.

If I know all the Departments which I bursted across and should have snapshots for, then I can move them to a new destination, and rename them, for example:

Item (1) – we use the “Move” command to move to paths defined in (2)

Item (2) – we’re moving the report snapshot out of its sub-folder that it was extracted to and into a new sub-folder customized for the particular department

Item (3) – we’re adding a datestamp to the snapshot to allow for uniqueness

We can also add a final step to delete the sub-folders created when we unzipped the file and in which the PDF snapshots existed originally.

Once the above steps are run, the final directories would look like this in our example:

And a particular snapshot would look like (for example):

The “Accounting” organization user can then easily access this folder for the particular day and open the report snapshot:

Future report snapshots can then easily be added to this folder through the automated process!

Conclusion

And at this point, we should be all set! Taken in context with the other blog posts in this series, by this point you should be able to setup batch bursting processes in Oracle PBCS/EPBCS, as well as utilize various output and distribution methods for produced snapshots. You should also be able to take advantage of automation processes for downloading and archiving snapshots to allow for more convenient accessibility within the organization. When dealing with frequent report snapshot production and/or output of multiple reports, these processes should help you save time and improve efficiency! Thanks for reading!

References:

http://www.keyperformanceideas.com/batch bursting-of-reports-by-property-in-oracle-epbcs-and-pbcs

http://www.keyperformanceideas.com/scheduling-and-outputting-reports-via-batch-bursting-in-oracle-epbcs-and-pbcs

https://docs.oracle.com/cloud/latest/epm-common/CEPMA/epm_automate_command_ref_general.htm#CEPMA-GUID-5400C490-264F-493A-9EAD-267AB2A320F1

Blog Post by Noah Neal of Key Performance Ideas.

 

RECENTLY
FROM THE BLOG

 

9/25

PCMCS Webinar