FDMEE Automation – Automatically identifying the load period

Welcome to the second part of my series on automatically loading data from source systems into PBCS. I’ve got another couple of tips and tricks up my sleeve for this release, and these tips are related to how to identify key inputs into the data load process.

The first one is the load period. Now, this might seem like a straightforward process – you’re loading in March, so let’s load March, right? Not necessarily. You’re usually uploading Actuals through FDMEE and, in most companies, month end is not guaranteed to happen on a consistent date each month. Even if your company is totally on top of their month end procedure, it’s usually best to prepare for the worst.

Because what happens if you fix the FDMEE load period and then you haven’t switched to your new forecast on that agreed date? Your Actuals come in and overwrite the current month forecast, destroying the forecast that your employees have been hard at work on for a month. Oops.

Another approach is using the getsubstvars command to pull out the Actual month from whatever it’s set as in the system. It’s a step in the right direction, and very easy to do. The problem is, your current forecast is usually controlled by a substitution variable too. And if you’re doing regular updates from your source system, can you rely on your admin to change all substitution variables in their time window between loads? What if their internet goes down midway through the process? No, there must be a better way.

The best way for data integrity in my opinion is to pull the load period straight out of the data file. In hindsight, this is obvious. There’s no chance of getting May data inadvertently loaded into April if you pull May straight out of the file. And, the cherry on the top is that if, like most companies, you run 0+12 through to 1+11 Forecasts each year, then you can directly map the period to the forecast you’re loading into.

Think about it. In May, you want to load your latest actuals into the 5+7 Forecast. January to April Actuals will be set in stone and can be copied from the previous forecast, so you can call your load rule Load_May and fix that to the category 5+7 Forecast. Thus you’re guaranteed to load your data to the appropriate scenario.

How do we read our file and use that logic to drive our FDMEE process? I’m glad you asked:

set datafile=TestDataFile.csv

for /f "skip=1 tokens=x-y delims=," %%a in (%datafile) do (

set period=%%a

set year=%%b

goto :endfor

)

:endfor

This simple piece of code will take your data file name as an input, skip the header row, and then search the first record between columns x and y to find your period and year. Basically, look at your data file and put xx as the column containing your period, and yy as the period containing the year. Then, change %%b to the (y-x)th letter of the alphabet.

Example:

In my data file, period is the 5^th data field and year is the 12^th. 12 – 5 = 7, and the 7^th letter of the alphabet is g. So the code is as follows:

set datafile=TestDataFile.csv

for /f "skip=1 tokens=5-12 delims=," %%a in (%datafile) do (

set period=%%a

set year=%%g

goto :endfor

)

:endfor

You can then do some ETL on the period and year to put it into the correct format for FDMEE (May-17 is the correct format) and use that to run a data load rule:

call epmautomate rundatarule Load_%month% %month%-%year% REPLACE REPLACE_DATA %datafile%

Hopefully this will help you in your next FDMEE implementation. Stay tuned for more in my FDMEE series.

Mike