Saturday, October 16, 2010

Simplified Essbase Administration - Part I

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