FDMEE Automation – Send Email Notification Reports Automagically!

Welcome to the first of a multi-part series exploring techniques you can use in an automated data load into PBCS using FDMEE. FDMEE is an ETL (Extract, Transform, Load) tool that comes free bundled in with PBCS, and is mostly used to create structured data loads for data that requires mapping or processing before loading into PBCS.

However, by combining FDMEE with a batch scripting tool called EPM Automate (again, bundled in for free with PBCS), we can create a fully automated load from any transactional system into PBCS. I’ll be exploring the full build process for an example ERP system (JD Edwards in this case) in a future blog, however for now I’d like to focus on a great technique for producing a status report following an FDMEE load, and automatically emailing it to the administrator responsible for the automated load.

Here’s an example of the great reports that FDMEE has hidden away:

The fish are my favourite touch of this awesome report.

As you can see, this report runs on all data load rules for a FDMEE Location – this means that if you have several different data sources coming into the same PBCS Application, you can easily pull together one report displaying the status of all loads. The report also details the time that each rule last ran at, and shows the accounting period for good measure.

So, how can we automate the generation of this report? Using a batch script and the command runDMReport:

call epmautomate runDMReport "Process Monitor (Cat, Per)" "Location=ACTUAL_DATA" "Accounting Period=Apr-17" "Category=Actual_Data" "Report Output Format=PDF"

Lets break down the components of the command above:

• call epmautomate this section tells your script that you want to use the EPMAutomate tool
• runDMReport – This is the EPMAutomate command to run an FDMEE Report
• “Process Monitor (Cat,Per)” – The name of this specific report. There are several templates to try out!
• The FDMEE Location you want to use. This defines the import format used, the source (a file) and the target (your application). You set up the location while setting up an FDMEE rule, prior to automating it.
• "Accounting Period=Apr-17" – The accounting period. Because this is a batch script, you can replace any of this with variables. For example, if you defined a variable period=Apr-17 then the following code would also work: "Accounting Period=%period%"
• "Category=Actual_Data" – The category of data. Usually corresponds to your scenario, so you’ll often want to load in your actual data.
• "Report Output Format=PDF" – PDF is the preferred output format of your report.

Running this code (remember you will need to run this from the EPMAutomate/bin folder) will kick off this process in FDMEE:

If you hit the Download button, then you will see your FDMEE Report! Hooray!

Hold the celebrations…this is only the first step. What you’ve done is run the report from a batch sure, but it’s still a manual process for now. Your report also isn’t doing much good just sitting there in the Workspace. You need to download this report to your server so that you can email it out.

This is where we hit our first snag. The report, by default, is saved as ProcessID.pdf. What’s the process ID? It’s a sequential ID number assigned to each FDMEE process. So, for the screenshot above, we end up with a report called 4968.pdf. Not much use to man nor beast…and quite difficult to identify from an automation point of view. This is where we need to use a sneaky trick to identify the name of the report you just created.

Conceptually, we need to do the following:

1. Identify the report name
2. Download the report from the PBCS Cloud inbox to local server
3. Rename report to something better than 4968.pdf
4. Automatically email the report to the administrators.

The report name is the tricky bit. We’re going to use a function called listfiles to identify the report name. Listfiles works as follows:

Conveniently, we can also output the results of listfiles to a temporary file.

call epmautomate listfiles >C:\Temp\ListFiles.csv

This command will create a file called ListFiles.csv which contains a sequential list of all files that are on the server. But how does this help us? By a stroke of luck, reports are saved to the lowest folder in the inbox hierarchy:

  

And, since the reports are assigned the process ID as their name, which I may remind you is a sequential number starting from 1, you can prove mathematically that the latest report will always be at the bottom of a listfiles extract (provided you delete a report once downloaded).

The secret to all of this working - here is the bottom of a listfiles extract

This is key. I’m going to give you guys a freebie here, and give you the code which does the above. Aren’t I good to you?! The truth is that I found most of this through trial and error, so I can’t take too much credit for it. All this code does, is take the last line that starts with outbox from our listfiles extract, and leaves you with the variable repname with the value: outbox/reports/4968.PDF

UPDATE: In recent versions of EPM Automate, snapshots will appear underneath the reports. So, I've updated the code below to ignore the snapshots. REPORTNAME is your listfiles extract.

setlocal enabledelayedexpansion
for /F "tokens=*" %%A in (%REPORTNAME%) do (
set x=%%A
set y=!x:~0,6!
if !y!==outbox (
    set repname=%%A
    )
)

After you have this, you can do anything. You can use the downloadfile command to download your report to your local server, and I’d also recommend deleting it from the cloud repository so your script cleans up after itself.

Finally, you’ve downloaded the report (which by default goes to the EPM Automate directory and now, you just need to rename it. We’re going into normal batch scripting territory here but I will spoonfeed you one last helpful trick, the below code will take your repname as input and will output ReportName = 4968.pdf. Doesn’t seem like much, but with the knowledge of the default download location and this variable, you can reference this report.

for /f "tokens=1-3 delims=/" %%a in ("%repname%") do (

set ReportName=%%c >>%LOGURL%

)

All that’s left after this is to rename it to something nice, timestamp it and email it out to your administrators. We use Powershell email tools to do this. I'll explore this in another blog.

Now I need to add a caveat here, I’m sure there are more efficient ways to code than what I’ve shown here, but if you’re lazy then feel free to copy it. If you need any help with the rest of the process, drop me an email using the contact form and I’ll be sure to get back to you.

Until next time,

Mike