MaxL Basics: Automate Essbase Data Loads and Calculations

📎 This article includes 1 downloadable practice file ↓

MaxL is Essbase’s scripting language for administration. Instead of clicking through the console every month, you save the steps in a .mxl file and run it — manually or from a scheduler.

A typical monthly load script

/* monthly_load.mxl */
spool on to 'monthly_load.log';

login $1 identified by $2 on $3;

/* clear the month we are about to reload */
execute calculation 'FIX("Actual", "Mar") CLEARDATA "Sales"; ENDFIX' on Sample.Basic;

/* load data using a rules file, send rejected records to a file */
import database Sample.Basic data
    from server text data_file 'sales_mar.txt'
    using server rules_file 'LdSales'
    on error write to 'sales_mar_errors.txt';

/* run the calculation stored on the server */
execute calculation Sample.Basic.CalcAll;

logout;
spool off;
exit;

What each part does

• spool writes everything to a log file — your audit trail.
• $1, $2, $3 are parameters passed on the command line (user, password, server), so the script contains no passwords.
• execute calculation ‘…’ can run an inline calc string — here a targeted CLEARDATA with FIX, so only March Actuals are cleared.
• import database … using … rules_file loads the file through a load rule that maps and cleans columns.
• on error write to keeps rejected records (e.g. unknown members) in a file you can fix and reload.

Running it

essmsh monthly_load.mxl admin_user %ESS_PWD% essbase-server

essmsh is the MaxL shell. Read the password from an environment variable or a secure vault — never hard-code it.

New to Essbase? Start with Essbase explained. On EPM Cloud the equivalent automation tool is EPM Automate.

Try it yourself: step by step

1. Download monthly_load.mxl. Replace Sample.Basic, the data file and the rules file with your application’s names.
2. On a test server run essmsh monthly_load.mxl youruser %ESS_PWD% yourserver.
3. Open monthly_load.log: each statement shows “OK/INFO” lines. Check the elapsed times.
4. Open sales_mar_errors.txt. If it has lines, they are records Essbase rejected — usually a member that does not exist in the outline.
5. Fix the outline or the rules file and re-run only the import step.