Monday, November 28, 2016

Open Batch, putting some light on it!

Hi,
after some discussions with different customers I got feedback about Open Batch documentation. It's true that is not clear and it omits some details. However, I also got feedback from Oracle about this being updated soon.

In the meantime I think is a good idea to put some light on it, especially on Import and Export modes available for batch files. I will try to keep things simple and will focus in the common scenarios.

Please bear in mind that this post assumes you are familiar with Open Batch concept. In case you don't, you can visit Oracle's documentation in this link.

Basically, an open batch is a type of batch definition which lets you automate/schedule workflow processing (So Import, Validate, Export, Load and Check). This functionality is typically used when source files are generated from external systems like SAP. If you come from FDM Classic World, this is the replacement of Batch Loader.

Open Batch file names
Open batch uses files which have the following information in their names:
  • POV: Point-of-View where data is processed (Location, Period, etc.)
  • Import Mode: how data is imported into FDMEE's POV
  • Export Mode: how data is exported into the target system (HFM, Planning, etc.)
They need to be located below openbatch folder (sub-folders can be also used):
Being said that, let's see how all fit in the file name. There are two naming conventions for open batch files:
  • Data Load Rule auto-creation: used when the DLR does not exist yet
  • Existing Data Load Rule: used when the DLR already exists in FDMEE
Data Load Rule auto-creation
This naming convention is used when we want to create the Data Load Rule on the fly.
Assuming delimiter is set to @ (valid delimiters are "@", "_", "~" and ";"):
      FreeText@Location@Category@Period@IE.ext
where:
  • FreeText is a free text which is also used to define the order of execution for multiple batch files.
  • Location is the POV Location
  • Category is the POV Category
  • Period is the POV Period
  • I is the Import Mode into FDMEE
  • E is the Export Mode into Target
  • ext is the file extension (typically txt or csv)
To use this approach you need to enable Auto Create Data Rule option in your batch definition:
In this case, we are using a specific sub-folder within openbatch folder: BATCH_FOLDER (this is very useful when you have multiple source systems and you want to have individual batches for them)
Let's say we have the following open batch file:
As a result of running the open batch, a new DLR has been created (LocName_Category):
Finally, you can check the FDMEE process log for details:
Questions:
  1. What happens if we run the same batch file again? Nothing, FDMEE just reuses it.
  2. What happens if we disable Auto Create DLR because rule is already created? sames as above

Existing Data Load Rule
This naming convention is the most common one. Open batch assumes the DLR already exists in FDMEE so there is no need to create it.
Assuming delimiter is set to @:
      FreeText@DLR@Period@IE.ext
where:
  • FreeText is a free text which is also used to define the order of execution for multiple batch files
  • DLR is the POV Data Load Rule
  • Period is the POV Period
  • I is the Import Mode into FDMEE
  • E is the Export Mode into Target
  • ext is the file extension (typically txt or csv)
Why don't we need Location and Category? They are implicitly defined in the DLR.

Import and Export Modes
I tried to summarize the most common source/target import and export modes in the following tables:
When you run an open batch, you can see import and export modes in the process log:
Process Level
We can configure the workflow steps we want the batch to execute:
As you can see, there is no Import/Validate split any more. The reason is that, as you may know already, if we import data, then it is automatically validated.

Open Batch for Multi-Period
We can also use open batches when working with multi-period files (Excel or Text)
The naming convention for batch files will be:
FreeText@DLR@Start Period@End Period@IE.ext

I will cover multi-period batches in future posts.

Free Text, be creative!
Free text field is commonly used to define sequence when batch files are processed sequentially. However, it is also a good place where to have some useful information for our batch custom actions.

Let's say that we have batch files generated from external system like SAP. Our batch process has been customized to send email notifications with results after each batch file is processed.
To who? to SAP users who triggered the file generation.
How do I know their email address? we can have the user name in the free text field. Then, our custom process can easily connect to the LDAP Server in order to get the email address based on user name.

For example, after processing file:
    FAmores@DLR@Period@RR.txt
FDMEE will send an email notification to FAmores@mycompany.com which is stored in the active directory. Nice, isn't it?

Open Batch files for ERP or Source Adapters
Open batch files are commonly used for File to EPM interfaces. However, they can also be used with other sources (E-Biz, PSFT, JDE, SAP, etc.)
The only difference is that batch filename is empty as it is actually used only as "trigger".
For example, for Universal Data Adapter (UDA):
And the DLR is executed in the same way as having File as source:
Scheduling
FDMEE built-in scheduler has some limitations:
  • Can't be easily scheduled for high frequency (ex: every 5 minutes)
  • When executed, it creates a new process id (and therefore DB information, batch sub-folder ,etc.) even if there are no batch files
  • Can't be easily delete specific scheduling if you have several
  • We can't easily see which batch have been scheduled and when
Regarding the 2nd bullet point: in current version, nothing prevents you to execute an open batch if there are no batch files. As a result, you end with several batch entries in the process details page:

Luckily, there are different workarounds for limitations above. I will cover some of them in future posts.

Some Tips & Tricks
Based on experience, I think this information can be useful:
  • You can group multiple open batches in a master batch (type Batch) although all open batches must be configured for the same target application
  • I would not use parallel open batches in current version (11.1.2.4.200). There are some known issues
  • When customizing your batch process, bear in mind that batch files for same DLR, will overwrite data and some audit information in the FDMEE database. For example, if you have one DLR where multiple files for different entities are processed
  • Avoid using "_" as file delimiter. Reason is that underscore is commonly used in DLR names
  • Try to force source systems generating batch files to use batch file name convention. Otherwise, you can execute a custom script to rename files before batch is processed
  • Add email notification system to your batch process. This is not an out of the box functionality in FDMEE but Admins and end-users will appreciate it. Who doesn't want something like this?

That's all folks.

I hope you enjoyed reading this post and have a clearer idea about open batches. And remember, the best of way of learning is to use them :-)

2 comments:

  1. Hi Francisco, Shouldn't R specify an export mode of replace even for ESSBASE exports.

    ReplyDelete

Thanks for feedback!