Developer ToolsFree generatorPDF, Excel, Word exportOracle Hyperion / EssbaseAll Oracle Hyperion and Essbase tools

Essbase Calc Script Generator: FIX, CALC DIM, AGG and DATAEXPORT

This generator writes a block storage (BSO) Essbase calc script that restricts the calculation with FIX, calculates dense dimensions with CALC DIM, aggregates sparse dimensions with AGG and exports level-0 data with DATAEXPORT. It works for Essbase 11.1.2.x on-prem and Essbase 21c, including the cubes behind Hyperion Planning.

Fill in

Application

Database (cube)

Calc script name (max 8 chars on 11.1.2 file systems)

FIX members (quoted, comma separated)

One or more members per dimension you want to restrict. Quote every member name.

Clear before calculating

Dense dimensions to CALC DIM

Sparse dimensions to AGG

CALCPARALLEL threads

Export file (full path on the Essbase server)

Export delimiter

Missing value text

Generated code

Download:

Aggregation calc script (AggAct.csc)

/* AggAct: aggregate Plan1.Fin for "Actual", "FY26", "Working" */
SET UPDATECALC OFF;
SET AGGMISSG ON;
SET CACHE HIGH;
SET CALCPARALLEL 4;
SET MSG SUMMARY;

FIX ("Actual", "FY26", "Working")
  CLEARBLOCK UPPER;
  CALC DIM ("Account", "Period");
  AGG ("Entity", "Product");
ENDFIX

Level-0 export calc script (run separately or append)

SET DATAEXPORTOPTIONS
{
  DataExportLevel "LEVEL0";
  DataExportDynamicCalc OFF;
  DataExportColFormat ON;
  DataExportDimHeader ON;
  DataExportOverwriteFile ON;
};

FIX ("Actual", "FY26", "Working")
  DATAEXPORT "File" "," "/u01/exports/fin_actual_fy26_lev0.txt" "#Mi";
ENDFIX

Run it from MaxL (essmsh)

login admin identified by 'password' on 'essbase-host';
execute calculation Plan1.Fin.AggAct;
logout;
exit;

Checked against the Essbase Technical Reference (11.1.2 and 21c) for SET UPDATECALC, SET AGGMISSG, SET CACHE, SET CALCPARALLEL, SET MSG, CLEARBLOCK, CALC DIM, AGG, SET DATAEXPORTOPTIONS and DATAEXPORT. SET CACHE HIGH only takes effect if CALCCACHEHIGH is set in essbase.cfg. AGG only works on sparse dimensions (error 1012023 on a dense one). Confirm dimension names and storage types in your outline before running on production.

Send us a slow calc script

Share the script and the application log lines for one run and we will point out what is forcing serial mode or extra blocks.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Type the members you want to restrict in the FIX list, usually one scenario, one year and one version. List the dense dimensions that carry member formulas or need consolidation (often Account and Period) and the sparse dimensions that only need to roll up (Entity, Product, Cost Center). Pick a clear step, the number of parallel threads and the export file. Copy the first block into a .csc file in the database directory, or paste it into the calc script editor in EAS or the 21c web interface. The second block is a separate script so you can run the export on its own, for example after a nightly load.

Quote every member name, even ones without spaces; numbers like "100" must be quoted.
Keep the FIX on sparse members where you can: it limits the blocks Essbase touches.
Start with 2 to 4 threads and watch the application log before raising CALCPARALLEL.

CALC DIM versus AGG: which to use

AGG consolidates sparse dimensions using the outline operators only. It does not run member formulas, which is why it is usually faster than CALC DIM on dimensions with fewer than six consolidation levels; on deeper dimensions the Technical Reference says CALC is usually faster. CALC DIM consolidates and runs member formulas. Use CALC DIM for dense dimensions and for any sparse dimension that has formulas on stored members; use AGG for pure roll-up sparse dimensions. AGG on a dense dimension fails with message 1012023 (aggregating on dense dimension is not supported). SET AGGMISSG ON lets parents be overwritten by #Missing when all children are missing, which is what you want after a clear. SET UPDATECALC OFF stops Intelligent Calculation from skipping blocks it considers clean, which is the safer default for scripted calcs.

Worked example

With the defaults, the script fixes on Actual, FY26 and Working, clears upper-level blocks, recalculates Account and Period inside every remaining block, then rolls up Entity and Product. The export block writes one row per level-0 combination to /u01/exports/fin_actual_fy26_lev0.txt with a dimension header row, comma delimiters and #Mi for missing cells. DataExportColFormat ON writes one member per column, which is what a SQL loader or another Essbase rules file expects. DataExportOverwriteFile ON replaces yesterday's file instead of failing. DataExportDynamicCalc OFF keeps Dynamic Calc members out of the export, which also makes it much faster. Run it from MaxL with execute calculation Plan1.Fin.AggAct, or from a batch file through essmsh.

Gotchas and how to check the result

CLEARBLOCK UPPER clears consolidated-level blocks so stale parents do not survive a structural change, but it also wipes any values loaded directly at upper levels; if you load at upper levels, use CLEARBLOCK NONINPUT, which clears only blocks created entirely by calculation. A FIX on a Dynamic Calc member fails with 1012051. Each SET DATAEXPORTOPTIONS resets every option to its default, so put all options in one block. The export path is on the Essbase server, not your PC, and the Essbase service account needs write access. After a run, read the application log: Essbase reports the number of threads used (1012678), task dimensions (1012680) and Total Calc Elapsed Time (1012550). If it says it is calculating in serial mode, a formula or @XREF forced it (1012569 to 1012571).

Frequently Asked Questions

What is the difference between CALC DIM and AGG in Essbase?

Both consolidate a dimension. CALC DIM also executes member formulas and works on dense and sparse dimensions. AGG only uses the consolidation operators, ignores member formulas and works only on sparse dimensions, so it is usually faster for large sparse roll-ups such as Entity or Product. If a stored sparse member has a formula, use CALC DIM for that dimension.

How do I export Essbase data to a file from a calc script?

Use SET DATAEXPORTOPTIONS to choose the level and format, then wrap DATAEXPORT "File" "delimiter" "path" "missingChar" in a FIX that limits the slice. DataExportLevel accepts ALL, LEVEL0 or INPUT. The missing character is optional and defaults to #MI. DATAEXPORT also supports "Binfile" for binary exports and "DSN" to insert straight into a relational table.

Why is my calc script running in serial instead of parallel?

SET CALCPARALLEL only helps when Essbase can split the work into independent tasks. Backward dependencies in formulas, circular dependencies along a dimension or @XREF usage force serial mode, and the application log says so with messages 1012569, 1012570 or 1012571. Check the log for the number of task dimensions and empty tasks to see how well the calc parallelised.

Does this calc script work in Essbase 21c and in Planning?

Yes. The commands used here exist in both the 11.1.2 and 21c Technical Reference. In Planning, run it as a calc script against the plan type cube or convert it into a business rule. In 21c you can run it from the web interface, from MaxL, or as a calc job through the REST API using the script name.

If your finance team wants to ask Essbase questions in plain English on your own servers, with the calc and export jobs explained back to them, we can set that up.

Talk to an Expert Explore More Free Tools