Use an Essbase Substitution Variable in a Shell Script

A couple of weeks back I was writing some automation scripts on a *nix system (using KornShell, in this case) and needed to do something I haven’t previously tried – grabbing the value of a couple of Essbase substitution variables to use elsewhere in the shell script. I’m sharing a generic version since (as a relatively inexperienced programmer on Unix-like systems) I was pleasantly surprised by how simple the toolset made meeting this requirement, and more generally, the solution I came up with demonstrates several useful techniques from which other *nix neophytes may benefit:

  • Command substitution
  • Inline redirection
  • Filtering multiple lines with grep
  • Extracting tokens from a single line with AWK

Constructive criticism and comments are most welcome!

The Code

USER=username
PASS=password
SERVER=servername
MAXLDIR=/home/essbase/bin
LOGFILE=/tmp/GetSubVarError.log

MAXLOUT=$(${MAXLDIR}/startMaxl.sh  2>> ${LOGFILE} << EOF | grep 'MyVariable'
set column_width 1028;
login ${USER} identified by ${PASS} on ${SERVER};
display variable MyVariable;
logout;
EOF)

MYVAR=$(echo ${MAXLOUT} | awk '{print $2}')

The Explanation

The first thing the script does is assign some variables. USER, PASS and SERVER are self-explanatory. MAXLDIR contains the location of the startMaxl.sh script; LOGFILE specifies a file into which any MaxL errors will be written. This script doesn’t contain error trapping or handling, so anything placed in this file is purely to help with manual trouble-shooting.

MAXLOUT=$($MAXLDIR/startMaxl.sh ...more code here...)

Here, we move on to actually invoke MaxL, using command substitution. Command substitution effectively replaces a command with its own output, so this construct places the output of the MaxL session into the MAXLOUT variable.

startMaxl.sh  2>> ${LOGFILE} << EOF

Streaming commands into MaxL using a “Here” document (aka inline redirection) is another valuable technique. << EOF means ‘pass all text after the current command as input to the process on the left, until reaching the token EOF (‘EOF’ is used by convention although another value could be chosen). Inline redirection means that we don’t need to create, maintain or migrate a separate file containing MaxL commands. 2 >> sends STDERR (i.e. any error output) to my log file – in this case, I specifically didn’t want to log all MaxL output, as the variable contains a password.

| grep 'MyVariable'

The output from MaxL – literally, the data that would be displayed on the screen if we ran these commands in an interactive session – is then piped to grep, which searches each line for the name MyVariable. Grep strips all the MaxL noise, and returns the single line containing the name and value of my substitution variable. You may have noticed that for this to behave as intended, the substitution variable name must not be a string that appears elsewhere in the MaxL output – for example, it would not behave correctly if the variable was named ‘logout’. There is scope for improvement here.

set column_width 1028;
login ${USER} identified by ${PASS} on ${SERVER};
display variable MyVariable;
logout;

The fairly self-explanatory MaxL commands follow. Set column width to 1028 (I do not know why — particularly since substitution variable names are limited to 320 characters, and values to 256 — but this length is the minimum required to avoid the familiar ‘truncation’ warning), login, extract the substitution variable, and logout.

MYVAR=$(echo ${MAXLOUT} | awk '{print $2}')

Finally, the content of MAXLOUT is piped to AWK which extracts the second token from the MaxL output line containing the variable, the first token being the name of the variable and the second being the variable value itself. AWK is used rather than, say, cut, only because it trims whitespace by default. Command substitution makes an encore to drop the result into the MYVAR variable.

And that’s it. Aside from being more elegant than writing out to a file, parsing that file and then cleaning it up, my favorite feature of this approach is that the script is entirely self-contained. No separate MaxL script is required, and no external output file is created at any point. This can be a worthwhile security benefit if (as in my use-case) the output itself contains sensitive information.

7 thoughts on “Use an Essbase Substitution Variable in a Shell Script

  1. Very nice. Isn’t Unix scripting wonderful? Imagine all of the extra maintenance involved in trying to do this with the JAPI.

    It’s been a decade since I’ve use the Essbase Perl Module (because it was such a pain to install, on many platforms you had to actually compile Perl yourself before it would work correctly) but that was the greatest scripting tool Essbase has ever known because it would give you stuff like sub var values back right into a variable — no awk or here documents necessary. Then you could make code branching decisions based upon their output all without ever logging out of Essbase.

    TimF

    • Thanks Tim. “Isn’t Unix scripting wonderful?” – yes, I was amazed that when I finished it this was all accomplished (outside of my parameterizing the login and the MaxL commands themselves) with two or three lines of code, using universally available techniques.

      I haven’t used the Perl module because I’ve been afraid of Perl, but I know the pain point only too well! ‘Simple’ tasks such as “shut down applications with no active sessions” require heavy duty parsing of output outside MaxL, dynamic creation of scripts etc. It’s not so much the fact that MaxL isn’t a full-featured scripting language (I can’t really expect Oracle to write that) as that there’s no good way to integrate MaxL into one (Perl excepted).

  2. Have you tried PowerShell on Windows? PS seems to have some better built in functions like shell scripting. They have similar command aliases between shell scripting and PowerShell, so your code might actually work on Windows as well.

  3. I accomplished something similar in Windows using report scripts. I needed to capture current year based on data found in a data table. Then armed with that value I set the year value. Then! with that value set as a substitution variable I was able to run a report script to generate a list of months for the current year where there is data. The last month, depending on outline order, would be the first or last record in the report script. A quick ‘tail’ or ‘head’ command would get the result for my next maxl command. I think it takes more steps in Windows but is possible to do without VBA.

    • Hi Evgeniy, thanks for the comment. I’m grabbing the subvar (which is primarily used in calc scripts) to use in the shell script, not the MaxL script – but yes, good point! Another option would be to hold this value be in a system environment variable which can also be referenced in both shell scripts and calc script, rather than holding the value in a subvar and then extracting it for use in a shell script. I believe the original justification for using a subvar was security-related.

      Obviously, the Unix techniques are well worth knowing regardless.

Leave a Reply

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