Why is it that many organizations have invested millions in financial analytical tools that not only offer visibility to their data but also promise business process automation and I am hearing the term “MANUAL” frequently these days e.g. manual sourcing and transformation, manual loads, manual validation, manual e-mail notification of errors, manual migration, manual report delivery, and manual database object updates. I am exhausted of listening to these manual tasks … are you?
I'd like to believe this misfortunate manual business process administration is due to the current economic condition that has forced organizations to eliminate or consolidate functional roles, cut back on training and development resulting in poor business system administration, poor data analysis and poor reporting.
As a developer and system administrator, the good news is that all of these manual processes can be automated. The key to successful business application automation processes are as easy as going back to the basics:
- Business Acumen - What is the nature of the business?
- Data Process Flow - Where does the data source reside and what are the dependencies?
- Customer Needs - What do the customer need to make effective business decisions?
- Resources - What are their strengths and where can you leverage their talent?
- Oracle EPM Analytical and Reporting tools – What are the strengths of the application?
It is easy to lose site of the basics when one is wearing many hats. Understanding the strengths of the Oracle EPM applications, will give you a clearer view and understanding of:
- How you can align business knowledge to begin the build of a database?
- How you can leverage the tools to source and transform data?
- How you can align your resources?
- How you can leverage the strengths of the application and minimize the burden of administration?
- How you can deliver data and reporting timely to meet your customer needs?
If you are using multiple Essbase and Planning applications you can significantly reduce the burden of administration by automating database substitution variables, data loads, data transfers between multiple cubes by leveraging the XRef function, report scripts and/or Data Export via Maxl.:
I. Automating Substitution Variable Updates
a. Define Global Variables - Create a batch file that contains the names of your analytical servers, databases , log directories and load rules as follows:
REM***********************************************************************
REM This file sets Global variables to be used for all Month End administration functions
REM i.e Sub Variable, Data Load, Dimension Build, Calculation Scripts and Report Scripts
REM***********************************************************************
Set Log_Dir=C:\Hyperion\Automation\Admin\Jobs\RPTG\Logs\
Set Log_Dir2=C:\Hyperion\Automation\Admin\Jobs\Plan\Logs\
Set RPTG_Process_Dir=C:\Hyperion\Automation\Admin\Jobs\RPTG\Bat
Set PLAN_Process_Dir2=C:\Hyperion\Automation\Admin\Jobs\Plan\Bat
Set RPTG_LoadBat_Dir=C:\Hyperion\Automation\Admin\Jobs\RPTG\Bat\LoadFile_Bats\
Set PLAN_LoadBat_Dir=C:\Hyperion\Automation\Admin\Jobs\PLAN\Bat\LoadFile_Bats\
Set Fin_Server=10.100.80.01
Set Plan_Server=10.100.80.02
Set APP1=RPTG
Set APP2=PLAN
Set Database=Finance
II. Substitution Variable Batch Script –
a. Create a central batch file that contains all of your substitution variables for all applications as follows:
REM ********************************************************************
REM UPDATE APPLICATION SUBSTITUTION VARIABLES
REM ********************************************************************
Set LOAD_CUR_MONTH=Aug
REM **** ***************************************************************
REM UPDATE FORECAST SUB VARIABLE REM*********************************************************************
Set FCST_SCEN=FCT_SEP
REM ********************************************************************
REM UPDATE YEARLY SUB VARIABLE
REM ********************************************************************
Set Load_Year=FY10
b. Create a Maxl file directory that contains all of your Global maxl scripts i.e. c:\Hyperion\Automation\Mxl\Set_SubVar.txt
/*Global script can be used for any applications */
spool stderr on to "$1";
set message level error;
login $2 $3 on $4;
iferror 'ErrHandler';
alter database $5.$6 drop variable "$7";
alter database $5.$6 add variable "$7";
iferror 'ErrHandler';
alter database $5.$6 set variable "$7" "$8";
iferror 'ErrHandler';
logout;
spool off;
exit;
define label 'ErrHandler';
exit;
c. Create a batch script to update substitution variable as follows:
REM **********************************************************************
REM SetDB_SubVars.bat
REM Special Instructions:
REM Update Monthly_SubVariable.bat with current month
REM variables prior to execution
REM **********************************************************************
REM *********************** SET VARIABLES SECTION *************************
Call C:\Hyperion\Admin\Jobs\Bat\Monthly_SubVariable.bat
Call C:\Hyperion\Admin\Jobs\Bat\Global_System_Variables.bat
Set Load_Dir=C:\Hyperion\Admin\Jobs\RPTG_Load\Data\%Load_Year%_%Load_Cur_Month%
REM *********************** UPDATE RPTG APP SUB VARIABLE SECTION *********
Set Application=%App1%
REM ***************************************
REM Update Current Month Sub Variable
REM ***************************************
Set SubVar=Cur_Month
Set SubVal=%Load_Cur_Month%
C:\Hyperion\AnalyticServices\Bin\essmsh.exe C:\Hyperion\Automation\MXL\set_subvar.mxl %Load_Dir%Set%Application%_%SubVar%.txt %USER% %PSWD% %Server% %Application% %Database% %SubVar% %SubVal%
REM ****************************************************
REM UPDATE FORECAST SCENARIO
REM ****************************************************
Set SubVar=FCST
Set SubVal=%FCST_SCEN%
C:\Hyperion\AnalyticServices\Bin\essmsh.exe C:\Hyperion\Automation\MXL\set_subvar.mxl %Load_Dir%Set%Application%_%SubVar%.txt %USER% %PSWD% %Server% %Application% %Database% %SubVar% %SubVal%
III. Audit Trail –
a. Not Certain if the substitution variables are updated. You can either log-on to the AAS console or check the Load Directory file initiated in the SETDB_SubVar.bat file to verify the substitution variable updated successfully. The results should appear in the log file as follows:
MAXL> display variable on database RPTG.Finance;
application database variable value (The log file will display pre and post Substitution Variable)
+-------------------+-------------------+-------------------+-------------------
RPTG Finance Cur_Mo Jul
RPTG Finance Cur_Yr FY10
RPTG Finance FCST FCT_AUG
MAXL> alter database RPTG.Finance set variable Cur_Mo "'Aug'";
MAXL> display variable on database RPTG.Finance;
application database variable value
+-------------------+-------------------+-------------------+-------------------
RPTG Finance Cur_Mo Aug
RPTG Finance Cur_Yr FY10
RPTG Finance NEW_FCST FCT_SEP
