How to Execute an Oracle Essbase Calc Script with the REST API

Short answer: You can execute an Oracle Essbase calc script through the REST API by posting a calc job to the Essbase jobs endpoint. The request identifies the application, database, calc script, and any runtime substitution variables, so the job can run under a system account instead of the current Excel user.

This article walks through the pattern we used after a client moved from a legacy on-premise Essbase instance to Oracle Essbase Cloud 21c. The goal was simple: trigger a calc script from an Excel-driven workflow while keeping execution tied to a controlled integration user.

What the Essbase REST API Calc Job Does

An Essbase REST API calc job lets an external process ask Essbase to run a named calculation script. Instead of opening the Essbase console manually, the calling script sends a request with the application, cube, job type, script name, and parameters.

Prerequisites

Before you automate the calc script, make sure you have the pieces below in place.

• An Essbase application and database where the calc script is deployed
• A calc script that can run successfully inside Essbase before automation is added
• A service or system user with permission to submit calc jobs
• The Essbase REST base URL for the target Oracle tenant
• A way to store credentials outside the script, such as environment variables
• A REST client for testing, such as Postman, before you wire the request into code

Step 1: Create the Essbase Calc Script

First, create a basic calc script in Essbase. In this example, the script defines a runtime substitution variable named TevWho. That default value can be overridden when the REST job is submitted.

SET UPDATECALC OFF;

SET RUNTIMESUBVARS
{
  TevWho="C-DAL";
};

FIX("BTC", "BG")
  FIX(@IDescendants(&TevWho), @Ancestors(&TevWho))
    Calc Dim("Years", "Time", "Accounts");
  EndFix
  @IDescendants(&TevWho);
  @Ancestors(&TevWho);
EndFix

The important detail is not the specific dimensional logic. The important detail is that the script declares the runtime substitution variable and then uses it inside the calculation scope.

Step 2: Test the REST Request in Postman

Before converting the workflow into code, test the request shape in Postman. This helps isolate authentication, URL, permissions, and request-body issues before they get mixed into an Excel macro or JavaScript script.

The request should submit a calc job and include the same runtime substitution variable name used in the calc script. If the Postman request works, you have a clean baseline for automation.

Step 3: Submit the Calc Job from JavaScript

Once the REST request works, the next step is to automate it. The JavaScript below uses Axios to post a calc job to the Essbase jobs endpoint. The same request structure can later be translated into VBA for Excel.

require("dotenv").config();
const axios = require("axios");

async function callCalcScript() {
  const authToken = Buffer.from(
    `${process.env.ESSBASE_USERNAME}:${process.env.ESSBASE_PASSWORD}`
  ).toString("base64");

  const body = {
    application: "TevTest",
    db: "DBMain",
    jobtype: "calc",
    parameters: {
      script: "TevCons.csc",
      rtsv: [{ name: "TevWho", value: "HTOWN" }],
    },
  };

  const response = await axios({
    method: "POST",
    url: `${process.env.ORACLE_TENANT_URL}/essbase/rest/v1/jobs`,
    headers: {
      authorization: `Basic ${authToken}`,
      "content-type": "application/json",
      accept: "application/json",
    },
    data: body,
  });

  return response.data;
}

callCalcScript().then(console.dir);

The Request Body Is the Key Piece

Most of the script is standard HTTP setup. The part that matters most is the request body.

• application tells Essbase which application to use.
• db identifies the target database or cube.
• jobtype is set to calc.
• parameters.script names the calc script to run.
• parameters.rtsv passes runtime substitution variables into the job.

In the example, the calc script has a default value of C-DAL, but the REST call submits HTOWN. That lets the external workflow control the calculation scope without editing the calc script itself.

Step 4: Verify the Job in Essbase Cloud Console

After the script runs, verify the result in the Essbase Cloud Console. You want to confirm that the job was submitted by the expected system user, used the intended calc script, received the runtime substitution variable, and completed successfully.

Step 5: Convert the Pattern to an Excel Macro

After the JavaScript proof of concept works, converting the pattern to VBA is mostly a matter of recreating the same HTTP request. The macro needs to authenticate, build the JSON body, post to the Essbase jobs endpoint, and report whether the submitted job succeeded.

The useful part of this approach is control. Excel can initiate the workflow, but Essbase executes the calculation under a known integration account with predictable permissions.

Production Hardening Checklist

• Store Essbase credentials outside the macro or script.
• Use a dedicated integration user instead of a personal account.
• Limit the integration user to the Essbase permissions needed to run the target job.
• Validate runtime substitution variable values before submitting the job.
• Log the request ID or job ID so support teams can trace failures.
• Return useful errors to the Excel user instead of hiding the REST response.
• Test the calc script directly in Essbase before debugging the API layer.

FAQ

Can you run an Essbase calc script through the REST API?

Yes. In Essbase 21c and Essbase Cloud, you can submit a calc job to the REST API jobs endpoint and identify the application, database, calc script, and runtime substitution variables in the request body.

Which REST endpoint runs an Essbase calc script?

The common pattern is to POST a calc job to /essbase/rest/v1/jobs. The request body sets jobtype to calc and includes the target application, database, script name, and parameters.

How do runtime substitution variables work with Essbase REST jobs?

Runtime substitution variables are passed in the job parameters as name and value pairs. The calc script must define and use the same runtime variable names, so the API value can replace the default value at execution time.

Can this be called from an Excel macro?

Yes. The JavaScript example proves the API request shape first. The same request can then be translated into VBA so an Excel macro can trigger the calc script with a system user instead of the logged-in Excel user.

Need Help Automating Essbase Workflows?

Tevpro helps teams automate Oracle Essbase, OneStream, Excel-driven planning workflows, and custom integration layers. If you want to trigger calculations safely from Excel or connect Essbase to another business process, contact us and we can help you map the cleanest path.

Photo by Markus Winkler.