Using Batch & MaxL Scripts to Build a Simple Data Utility

Avatar

By
September 24th, 2014


Introduction

Loading data to Essbase cubes can be done in many ways. Oracle provides a number of applications that can perform this task. One of such tools is MaxL. Combining Maxl Scripts with batch files can provide low cost, simple, and fully customizable solution to the task of loading data into Essbase. In this post, I will give a detailed example of how to use a simple DOS batch file to call a MaxL script that will clear and load data to five different Essbase cubes.

The benefits of using MaxL to load data are:

  1. Easy to debug.
  2. Easy to maintain.
  3. Easy to deploy.
  4. Low cost.
  5. Plenty of documentation and help available.

Building Batch and MaxL scripts can often provide quick solutions to complex problems.

 

The Data_Utility

 

The example I will use comes from an environment that had five separate ASO cubes. The cubes had similar structure (same dimension names, same scenarios, same time and period dimensions). The load rules used to load data into the cubes are the same in all the cubes. They all use the same data files and load only the data that applies to each individual cube.

 

In this environment, I built a utility to clear and load data into the cubes. The utility included two files:

  1. bat
  2. mxl

 

The complete text of both files is provided at the end of this post.

 

To use the utility, a user would launch the Data_Utility.bat file. This file asks the user to respond to a number for simple questions:

  • Select the data to be loaded
  • Select the cube data is to be loaded into
  • Select the time period (year)
  • Confirm the selections

 

After the user would make appropriate responses, the batch file launches the MaxL script which performs the following operations:

  • Spools actions to a log file
  • Clears data in the relevant intersections in the given cube
  • Loads data as specified by the user

 

Combining the Batch file and the MaxL script allows the end user to interact with Essbase without going into the EAS. This allows the end user to perform administrative actions without actually having access to administrative consol. In some organizations, this can be a big win for both the end user and the admin staff.

Setting up the CMD Window

 

Before starting to interact with the user, it is a good idea to clean up the cmd window so that the user only sees information that is pertinent to the task at hand. Long paths and other commands lines can muck up the screen and make it hard for the user to make quick and easy decisions. The following lines clear up the CMD window and set the path to the D: drive:

 

@echo off
cls
D:

 

This section sets up a date and time stamp. This can stamp can be used in log and error file names for reference purposes.

 

rem **** Set Time Stamp *****
set DateStamp=%DATE:~4,2%%DATE:~7,2%%DATE:~10,4%
set TimeStamp=%TIME:~0,2%%TIME:~3,2%
set DateAndTime=%DateStamp%~%TimeStamp%

 

The echo command can be used to display information on the screen. In the following example, it is used to define a title in the CMD window. This title is not necessary, but it provides a context to for the user:

 

echo *********************************

echo ***** TopDown ASO Data Utility ******

echo *********************************

Interacting With the User

 

Next, display the first set of options for the user to choose from. In this case, the user can choose the type of data to load. The options are Actuals, Budget, and Forecast. In this case, the options are the same as the members of the Scenario dimension.

 

 

echo.
echo Please select the data you want to load:
echo.
echo A. Actuals
echo.
echo B. Budget
echo.
echo F. Forecast
echo.

 

The above section, combined with the title section before it will look like this:

Batch_MaxL1

 

 

The following line allows the user to respond to the question above by entering a keystroke. In this case, the user can only respond with pressing one of the following keys: “A”, “B”, or “F”:

 

choice /C:ABF /M "-->> Enter a letter for the data you want to clear and load: "%1

 

After the user responds by pressing a valid key, the program performs a series of actions depending on the user’s response.

 

IF ERRORLEVEL==3 GOTO IsForecast
IF ERRORLEVEL==2 GOTO IsBudget
IF ERRORLEVEL==1 GOTO IsActual

 

If the user pressed the “A” key, the software will set the DataType variable to “Actual”, and the MTDRule variable to FY_MTD. The GoToCubSelection line skips the settings for Budget and Forecast.

 

:IsActual
Set DataType=Actual
Set MTDRule=FY_MTD
GoTo CubeSeclection

 

If the user pressed the “B” key, the software will set the DataType variable to “Budget”, and the MTDRule variable to “FY_MTDB”. The GoToCubSelection line skips the settings for Forecast.

 

:IsBudget
Set DataType=Budget
Set MTDRule=FY_MTDB
GoTo CubeSeclection

 

 

If the user pressed the “F” key, the software will set the DataType variable to “Forecast”, and the MTDRule variable to FY_MTDF. The “GoToCubSelection” line skips the settings for Forecast.

 

:IsForecast
Set DataType=F1
Set MTDRule=FY_MTDF
GoTo CubeSeclection

 

The variables are used by the MaxL script to launch clear data (DataType). In this application, Essbase uses different load rules for each data type. This makes it necessary to set the variable MTDRule so that the MaxL script can use the proper load rule when loading the data.

 

Another way to let the user pick options is to set the variables directly after ERRORLEVEL check. In the following example, the code asks the user to pick a cube, and then sets the variable CubeName to the appropriate name based on the user’s response:

 

 

:CubeSeclection
cls
echo.
echo Please select a cube:
echo.
echo X. ALL
echo.
echo A. AcctASI
echo.
echo C. AcctCON
echo.
echo F. AcctFDN
echo.
echo G. AcctGRP
echo.
echo L. AcctLCE
echo.

 

The above section will look like this:

BatchMaxL2

 

 

The command to let the user pick the cube is the same command used in the previous section:
choice /C:XACFGL /M “–>> Enter a letter for the cube you want to load: “%2
However, since the script is only setting one variable, that section is much more simple than the one used in the previous section:

IF ERRORLEVEL ==1 Set CubeName=ALL
IF ERRORLEVEL ==2 Set CubeName=AcctASI
IF ERRORLEVEL ==3 Set CubeName=AcctCON
IF ERRORLEVEL ==4 Set CubeName=AcctFDN
IF ERRORLEVEL ==5 Set CubeName=AcctGRP
IF ERRORLEVEL ==6 Set CubeName=AcctLCE

 

Note that in this case, the choice provided for the user response corresponds to the label of each option in the display section (“X” for “All”, “A” for “AcctASI”, “C” for “AcctCON”, etc…). These are controlled by the /C: paramater of the choice command (choice /C:XACFGL).

 

Using the cls command at the beginning of the section will clear the CMD window and provide a clean new display for the user. Again, this will allow the script to present only the information that is necessary for the user to process.

 

If the user selects all cubes (option “X”), the script sets the VerifyQ variable to “ALL”. For all other response, the VerifyQ variable will be set to a string containing the application and database name (in this application, they are both the same. This is done in the following line of code:

 

IF %CubeName%==ALL (set VerifyQ=ALL) ELSE (Set VerifyQ=%CubeName%.%CubeName%)

 

For example, if the user selects “C”, VerifyQ will be set to AcctCON.AcctCON. 

The VerifyQ variable is not passed to MaxL. It is used to ask the user to verify the selection made before it calls the MaxL script. It is also used for the script to skip the single cube operations and go to the all cubes operation.This provides more flexibility in displaying information to the user. It is not required, but it adds a touch customization to the script. 

Verifying Action

 

After all the information is collected from the user, it is a good idea to ask the user to verify the information collected before proceeding to the MaxL script. Using the echo command, display the options the user selected up to this point:

 

:Verify
cls
echo *************************************
echo ***** TopDown ASO Data Utility ******
echo *************************************
echo.
echo Are you sure you want to clear and load data to:
echo.
echo Data Type: …………... %DataType%
echo.
echo Applicaiton.Cube: ... %VerifyQ%
echo.
echo Year: …………………... %YearName%
echo.
echo MTD Rule Name:... ^%MTDRule%
echo.

 

This will look like this if the user selected the AcctCON cube:

BatchMaxL3

 

 

This is how it will look if the user selected the All Cubes option:

BatchMaxL4

 

The following section of the code asks the use to accept and continue entering “Y”, or reject and abort by entering “N”.

CHOICE /M "-->> Enter Y to continue, or N to abort: "
if ERRORLEVEL==2 goto END

 

In this example, the log files are stored in the “Logs” folder, and the MaxL scripts are stored in the “Scripts” folder. The first step is to change the working directory to the Logs folder:

D:
CD\Hyperion\Scripts\Logs

 

If the user selected to load data to all cubes, the script will jump to the LoadAll section of the code. If not, it will continue to the next line of code:
IF %VerifyQ%==ALL GoTo LoadAll
Before calling the MaxL scripts, delete the log files for the cube that is being modified. This is done by the following command:

Del Data_Utility~%CubeName%~%DataType%*.*

Calling the MaxL Scripts

 

At this point, the script calls the MaxL script that executes the operations in Essbase. To do this, the working directory must be set to the folder that contains the MaxL script:

 

CD\Hyperion\Scripts\Scripts

 

Now the MaxL script Data_Utility.mxl can be called. This MaxL script uses the CubeName, DataType, YearName and MTDRule variables. These variables were set using information provided by the user as described in the above sections:

 

echo Load all data in %CubeName%
Call essmesh Data_Utility.mxl %CubeName% %DataType% %YearName% %MTDRule%
GoTo END

 

The GoTo END line skips the section of the code that loads data to all cubes (covered in the following section.

 

As noted above, if the user elects to load data to all cubes, the MaxL script is called for each cube. Each time the MaxL script is called, the variable for the cube name is modified. This way, there is no need to maintain multiple scripts for each operation. This is how it is done:

 

First, delete all the log files:

:LoadAll
Del Data_Utility*.*
CD\Hyperion\ Scripts\Scripts
Next, call the MaxL script for each cube:
echo Load all data in %CubeName%
Call essmesh Data_Utility.mxl AcctASI %DataType% %YearName% %MTDRule%
Call essmesh Data_Utility.mxl AcctCON %DataType% %YearName% %MTDRule%
Call essmesh Data_Utility.mxl AcctFDN %DataType% %YearName% %MTDRule%
Call essmesh Data_Utility.mxl AcctGRP %DataType% %YearName% %MTDRule%
Call essmesh Data_Utility.mxl AcctLCE %DataType% %YearName% %MTDRule%

 

Note that the cube name is hardcoded each time the MaxL script is called instead of using the CubeName variable. 

The MaxL Script

 

The advantage of using the batch file to collect information from the user about the operation to be performed is that the single batch file can call a single MaxL script. This makes it easier for the administrator to maintain.

 

The first section of the MaxL script deals with setting the variables in the file. In this example, all variables start with a lower case “v”. Some of the variables in the file are passed on by the batch file:

 

/************Set Variables*******************/
Set vCube = $1;
Set vDataType = $2;
Set vMTDRule = $3;
Set vYearName = $4;
Other variables are hard coded into the MaxL Script:
Set vDataFolder = D:\\Hyperion\\AARPHyperion\\Data\\;
Set vLogFolder = D:\\Hyperion\\AARPHyperion\\AARP_Scripts\\Logs\\;

 

After the variables are set, turn on spooling to and send it to the log file. Note that the log file is named by the vCube variable, which is passed on by the batch file:

 

/************Turn spooling on*******************/
Set message level all;
Set vLogFileName = "$vLogFolder\Data_Utility~$vCube~$vDataType.log";
echo vLogFileName is "$vLogFileName";
Spool on to "$vLogFileName";

 

Displaying the parameters can help debug the script:

/************Display Parameters*******************/
echo This script clears and loads data using the following parameters:;
echo Cube: "$vCube";
echo DataType: "$vDataType";
echo Year: "$vYearName";
echo MTD Rule: "$vMTDRule";
echo YTD Rule: "$vYTDRule";
echo Data Folder: "$vDataFolder";
echo Log Folder: "$vLogFolder";

 

Before starting any operation in Essbase, the script must login to the environment. This login information can be encrypted using the MaxL encryption feature. In this example, no encryption is used:

 

/****************Login**************************/
login "Admin" "Password123" on '10.158.128.72:1423';

 

After login, the script clears the section of the data provided by the user’s responses to in the batch file:

 

/************ clear data ***************/
echo *****************************************************************************;
echo ****** Clear all "$vDataType" data in "$vCube" ******;
alter database "$vCube"."$vCube" clear data in region "{([$vDataType],[$vYearName])}" physical;
echo *****************************************************************************;
echo ****** Clear complete ******;
echo ;

 

After clearing the section of the cube, the data loading starts. In this example, there is a separate data file for current and prior year. The file names are constant and therefore they are hardcoded into the MaxL script. These filenames could be set as variables that are provided by the user just like the cube name and the data type.

 

/************ Load data ***************/
echo *****************************************************************************;
echo ****** Load "$vYearName" "$vDataType" data to "$vCube" using "^$vMTDRule" Rule File ******;
Set vErrFileName = "$vLogFolder\Data_Utility~$vCube~$vDataType~$vYearName.err";
goto "$vYearName";
define label 'CurYear';
Set vDataFileName = "$vDataFolder\ABF_GL2HYPERION.csv";
goto 'ShowDataFileName';
define label 'PriorYear';
Set vDataFileName = "$vDataFolder\ABF_GL2HYPERION$vYearName.csv";
define label 'ShowDataFileName';
echo Data File Name: "$vDataFileName";
echo Error File Name: "$vErrFileName";
import database "$vCube"."$vCube" data from local text data_file "$vDataFileName" using server rules_file "^$vMTDRule" on error write to "$vErrFileName";
echo *****************************************************************************;
echo ****** Load complete ******;
echo ;
After the data is loaded, the MaxL script logs out and exists the MaxL schell.
/****************Logout and Exit**************/
logout;
exit;
spool off;

Data_Utility.bat

@echo off
cls
D:
rem **** Set Time Stamp *****
set DateStamp=%DATE:~4,2%%DATE:~7,2%%DATE:~10,4%
set TimeStamp=%TIME:~0,2%%TIME:~3,2%
set DateAndTime=%DateStamp%~%TimeStamp%
echo *************************************
echo ***** TopDown ASO Data Utility ******
echo *************************************
echo.
echo Please select the data you want to load:
echo.
echo A. Actuals
echo.
echo B. Budget
echo.
echo F. Forecast
echo.
choice /C:ABF /M "-->> Enter a letter for the data you want to clear and load: "%1
IF ERRORLEVEL==3 GOTO IsForecast
IF ERRORLEVEL==2 GOTO IsBudget
IF ERRORLEVEL==1 GOTO IsActual
:IsActual
Set DataType=Actual
Set MTDRule=FY_MTD
Set YTDRule=FY_YTD
GoTo CubeSeclection
:IsBudget
Set DataType=Budget
Set MTDRule=FY_MTDB
Set YTDRule=FY_YTDB
GoTo CubeSeclection
:IsForecast
Set DataType=F1
Set MTDRule=FY_MTDF
Set YTDRule=FY_YTDF
GoTo CubeSeclection
:CubeSeclection
cls
echo.
echo Please select a cube:
echo.
echo X. ALL
echo.
echo A. AcctASI
echo.
echo C. AcctCON
echo.
echo F. AcctFDN
echo.
echo G. AcctGRP
echo.
echo L. AcctLCE
echo.
choice /C:XACFGL /M "-->> Enter a letter for the cube you want to load: "%2
IF ERRORLEVEL ==1 Set CubeName=ALL
IF ERRORLEVEL ==2 Set CubeName=AcctASI
IF ERRORLEVEL ==3 Set CubeName=AcctCON
IF ERRORLEVEL ==4 Set CubeName=AcctFDN
IF ERRORLEVEL ==5 Set CubeName=AcctGRP
IF ERRORLEVEL ==6 Set CubeName=AcctLCE
IF %CubeName%==ALL (set VerifyQ=ALL) ELSE (Set VerifyQ=%CubeName%.%CubeName%)
:CubeSeclection
cls
echo.
echo Please select a Year:
echo.
echo.
echo C. Current Year
echo.
echo P. Prior Year
echo.
echo B. Both
echo.
choice /C:CPB /M "-->> Enter a letter for the year you want to load: "%3
IF ERRORLEVEL ==1 Set YearName=CurYear
IF ERRORLEVEL ==2 Set YearName=PriorYear
IF ERRORLEVEL ==3 Set YearName=Both
:Verify
cls
echo *************************************
echo ***** TopDown ASO Data Utility ******
echo *************************************
echo.
echo Are you sure you want to clear and load data to:
echo.
echo Data Type: .......... %DataType%
echo.
echo Applicaiton.Cube: ... %VerifyQ%
echo.
echo Year: ............... %YearName%
echo.
echo MTD Rule Name:....... ^%MTDRule%
echo.
:echo YTD Rule Name:...... ^%YTDRule%
echo.
CHOICE /M "-->> Enter Y to continue, or N to abort: "
if ERRORLEVEL==2 goto END
D:
CD\Hyperion\AARPHyperion\AARP_Scripts\Logs
IF %VerifyQ%==ALL GoTo LoadAll
Del Data_Utility~%CubeName%~%DataType%*.*
CD\Hyperion\AARPHyperion\AARP_Scripts\Scripts
echo Load all data in %CubeName%
Call startmaxl Data_Utility.mxl %CubeName% %DataType% %YearName% %MTDRule% %YTDRule%
GoTo END
:LoadAll
Del Data_Utility*.*
CD\Hyperion\AARPHyperion\AARP_Scripts\Scripts
echo Load all data in %CubeName%
Call startmaxl Data_Utility.mxl AcctASI %DataType% %YearName% %MTDRule% %YTDRule%
Call startmaxl Data_Utility.mxl AcctCON %DataType% %YearName% %MTDRule% %YTDRule%
Call startmaxl Data_Utility.mxl AcctFDN %DataType% %YearName% %MTDRule% %YTDRule%
Call startmaxl Data_Utility.mxl AcctGRP %DataType% %YearName% %MTDRule% %YTDRule%
Call startmaxl Data_Utility.mxl AcctLCE %DataType% %YearName% %MTDRule% %YTDRule%
:END
set CubeName=
Set YearName=
Set MonthName=
Set MonthNameDisplay=
Set VerifyQ=

Data_Utility.mxl

 
/************Set Variables*******************/
Set vCube = $1;
Set vDataType = $2;
Set vMTDRule = $3;
Set vYearName = $4;
Set vDataFolder = D:\\Hyperion\\AARPHyperion\\Data\\;
Set vLogFolder = D:\\Hyperion\\AARPHyperion\\AARP_Scripts\\Logs\\;
/************Turn spooling on*******************/
Set message level all;
Set vLogFileName = "$vLogFolder\Data_Utility~$vCube~$vDataType.log";
echo vLogFileName is "$vLogFileName";
Spool on to "$vLogFileName";
/************Display Parameters*******************/
echo This script clears and loads data using the following parameters:;
echo Cube: "$vCube";
echo DataType: "$vDataType";
echo Year: "$vYearName";
echo MTD Rule: "$vMTDRule";
echo YTD Rule: "$vYTDRule";
echo Data Folder: "$vDataFolder";
echo Log Folder: "$vLogFolder";
/****************Login**************************/
login "Admin" "Password123" on '10.158.128.72:1423';
/************ clear data ***************/
echo *****************************************************************************;
echo ****** Clear all "$vDataType" data in "$vCube" ******;
alter database "$vCube"."$vCube" clear data in region "{([$vDataType],[$vYearName])}" physical;
echo *****************************************************************************;
echo ****** Clear complete ******;
echo ;
/************ Load data ***************/
echo *****************************************************************************;
echo ****** Load "$vYearName" "$vDataType" data to "$vCube" using "^$vMTDRule" Rule File ******;
Set vErrFileName = "$vLogFolder\Data_Utility~$vCube~$vDataType~$vYearName.err";
goto "$vYearName";
define label 'CurYear';
Set vDataFileName = "$vDataFolder\ABF_GL2HYPERION.csv";
goto 'ShowDataFileName';
define label 'PriorYear';
Set vDataFileName = "$vDataFolder\ABF_GL2HYPERION$vYearName.csv";
define label 'ShowDataFileName';
echo Data File Name: "$vDataFileName";
echo Error File Name: "$vErrFileName";
import database "$vCube"."$vCube" data from local text data_file "$vDataFileName" using server rules_file "^$vMTDRule" on error write to "$vErrFileName";
echo *****************************************************************************;
echo ****** Load complete ******;
echo ;
/****************Logout and Exit**************/
logout;
exit;
spool off;

 


Avatar

About TopDown Team

The TopDown Team includes members of TopDown Consulting who want to let the community know about webcasts, conferences, and other events. The team also conducts interviews on various EPM industry topics.

3 comments on “Using Batch & MaxL Scripts to Build a Simple Data Utility”

  1. JOJ says:

    Great Article. We developed something similar at our comapny that enabled the end user to put in a parameter to load to the entity (without the need to access EAS) they chose but then had troubled getting it deployed. The Network guys would not allow end users to get on the admin VLAN which had access to the essbase servers. I’m speechless.

  2. Allan says:

    Is there a possible typo on this page ? You reference xml files twice on this page. Dont you mean mxl ? They are two completely different things although they are spelled similarly. If there is a way to wrap MXL in an XML wrapper, I would like to know how you did it.

    Thank you.

    1. Gidon Albert says:

      Good catch. Yes. This is a typo. The extensions for the MaxL files are mxl. Not xml.

Leave a Reply

Your email address will not be published. Required fields are marked *