Dynamic Multi Period Load Automation for FDMEE – Solved!

I have a couple prefaces to my blog post:

1. I have never touched the Oracle FDMEE application. Like ever. Ever, ever.
2. In a prior life, I was a junior DBA. I love, love, love SQL. I loved being a SQL DBA.
3. I love automation (if you read my blog frequently, you already know this). It’s nice to see things come together with no human intervention. All the pieces are working!
4. I love (need?!) a challenge.

I was given the challenge last week of figuring out a way to pull the Start and End Periods for an FDMEE POV and running those values through an automated process for loading data into Essbase. Hmm. Without going into how I finally got into the meat of it, I’m going to show you how to do it. 🙂

Here is a screenshot of my FDMEE script folder:

• Logs – holds all the logs generated from the scripts
• _RunAppLoad.bat – this is the “big” script that runs the other scripts needed to run the automation piece and pull in the variables
• FDMEE.bat – the script to run the multi period loads in FDMEE
• LogoutEssbase.mxls – MaxL script to logout the username used to load the FDMEE data to Essbase.
• Output.csv – output from the SQL script that contains the Start and End Periods from the POV
• PeriodSQL.sql – SQL run from SQL*Plus command line
• RunSQL.bat – batch script that runs PeriodSQL.sql

The first piece you will need is the SQL. The script below should be able to be used in your environment. We included a where clause to trap certain applications, but this is not shown in the example. Note that you will need at least read access to the database tables to get the POV information.

The next piece is the batch script to call the SQL using the SQL*Plus command line. The Oracle client will need to be installed on the server. The script will use the login information provided (note that I temporarily put the SQL password as an environment variable for demo and testing purposes) and the import the PeriodSQL.sql file to run via SQL*Plus.

In FDMEE.bat, you will need to specify the load rule you are using to automate the data loads. We have multiple databases, so I am passing in the application variable from the “big” batch script (_RunAppLoad.bat). This piece will need to be modified accurately for your environment. Note that I’m not using the typical location for sqlplus.exe. Because I was having issues with the script exiting with and running the other scripts, I had to call it directly from the main location rather than a command location.

The last piece you will need is the Essbase MaxL to logout the automation username. This script simply logs into EAS, creates log and error files then logs out each instance of the automation username. FDMEE has kept about 45 connections to Essbase open after the data load, so this closes that current bug from FDMEE.

The “big” script will call each of these batch scripts, but also parse out the Start and End Periods from the file outputted by SQL*Plus in the file called “Output.csv”, shown below. The “big” script skips reading the first 6 lines of this file and then pulls in the first token – Feb-2014, the Start Period and the second token – Mar-2014, the End Period.

Here is the “big” script. It’s not BIG, but it runs everything:

1. Runs the SQL (RunSQL.bat)
2. Parses out the StartPeriod and EndPeriod as variables from Output.csv
3. Runs the multi period data load from the command line while using the StartPeriod and EndPeriod variables (FDMEE.bat)
4. Runs the MaxL to logout all automation user sessions in EAS (LogoutEssbase.mxls)

Log Files

There are 3 log files generated from this process:

1. General log of the activitities from FDMEE and Essbase (ESSBASE_Sample_LR_04-03-2015_13_37_17).
2. Essbase log file (LogoutEssbase_04-03-2015_13_37_17).
3. Essbase error file (ERROR_LogoutEssbase_04-03-2015_13_37_17).

General log file (showing first line where FDMEE finished successfully):

Essbase log file:

Essbase error file (if errors occurred, they would show here):

Special thanks to Wayne for helping me fine tune my SQL. I was a bit rusty. 🙂