Execute a Calc Script using Oracle Essbase REST API

Problem

We recently had a client migrate to Oracle Essbase Cloud 21c from a legacy on-premise instance, Essbase 6.5. They needed to execute a calculation (calc) script using an Excel macro, but they wanted to execute the script using a system user and not the logged-in user.

Solution

After doing some research into the Oracle Essbase REST API we found a way to accomplish just that. The first thing we did was create a basic calc script in Essbase that contained a runtime substitution variable (rtsv).

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

Now that we have our calc script set up and ready to execute, we needed to build a script to make the magic happen.

The first thing we did to test our theory was to see if we could get it working using Postman. This turned out to be pretty straightforward since we had some previous experience integrating with Oracle IDCS.

Now that we knew things would work using Postman, it was time to start trying to automate this using code. When I need to write a quick script, my weapon of choice is usually JavaScript.

Here is the script I whipped up to test out our theory:

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_TENET_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 language and syntax here aren't important. The key piece is in the variable named body. This contains the 'meat and potatoes' of the API call we need to make.

We specify the basic information around the application and database we want to connect to. From there, we specify the script we want to run and pass in any rtsv's (Runtime Substitution Variables). Once we have everything set up, we run our script and check the Essbase Cloud Console to see that our script was run successfully.

After we found out that what we wanted to accomplish, is indeed possible outside of Excel, we take this work logically and convert it into an Excel Macro using VBA.

Conclusion

While the task here wasn't complicated, navigating the Oracle documentation can seem daunting. Oracle has some examples, but none that describe how you go about calling a script using rtsv's.

Hopefully, this article helps shed some light on how to call calc scripts using the Oracle Essbase REST API. Feel free to reach out if you have any comments or questions. Here at Tevpro we love a good challenge and look forward to helping you automate workflows whether using Oracle or OneStream.

Photo by Markus Winkler