Embedding MaxL in a here document

I am fortunate to be using Essbase in a UNIX/Linux environment.  I say this because I consider shell scripting, whether it be bash or korn, to be vastly superior to Windows DOS/batch files.  I've written hundreds of scripts in both environments and the bash/korn shell scripts are invariably easier to write, faster, and more maintainable.  The availability of here documents is just one reason why.  Let me explain.

UNIX/Linux shell scripts basically allow you to embed a string in a command.  In other words, you can call a command (essmsh in the example below) and pass a multi-line string to it.  Well, you may say, I can just have multiple calls to essmsh and achieve the same thing in my handy-dandy bat file.  Maybe, but it won't be as clean as using a here document.  Let me give a sample shell script to illustrate.

The below would reside in a shell script.  We'll call it 'sample.sh'.

#begin sample.sh

APPLICATION=Sample
DATABASE=Basic
DATA_FILE=dims.txt
RULES_FILE=dims
ERROR_FILE=dim_errors.txt

essmsh <<END
import database $APPLICATION.$DATABASE dimensions
from server text data_file $DATA_FILE
using server rules_file $RULES_FILE
on error append to $ERROR_FILE;
END

#end sample.sh

So you see, there's no need for passing parameters from a bat/cmd file to a mxl file.  You can set the variables at the beginning of your shell script and use them in the here document's string while passing them directly to the essmsh command.  You don't have to refer to another file with the MaxL to see what's going on (and maintain two files).  In addition, you don't have to contend with all the crazy double quotes versus single quotes nonsense in MaxL files.  Also, if you are making multiple calls to essmsh in your bat file, each instance is separate.  In other words, if you set a variable in your first essmsh call, it will not be available in subsequent calls.  Here documents neatly sidestep this problem, as you're only making one essmsh call.

To recap, a here document allows you to put both your variables and MaxL in a single file, making maintenance a breeze.  You'll also avoid most double quotes / single quotes issues in MaxL files.

What's not to like?

Essbase outline extraction using the Java API

We all wonder why Hyperion/Oracle have never provided a built-in utility to allow extraction of metadata from an Essbase outline. The AppliedOLAP extractor is great, as long as you're on Windows. If you're on Linux or UNIX, your only 2 choices are 1) use the AppliedOLAP outline extractor from a Windows client machine or 2) write your own extractor using the Essbase Java API. The first choice is not really viable if you want to automate the extraction process. In a production environment do you really want to have the process running from a separate Windows machine? Windows is not very reliable on its good days...

This post is about the second option - using the Essbase japi to write an outline extractor. The ViewOutlineTree.java file in the Samples folder provides a great start, but it only outputs the member names in a tree format. What about the member properties, like UDAs, aliases, etc.? Fortunately, it is very easy to add these properties to the output. Take a look at the modified ViewOutlineTree.java code below to give you an idea.


/*

    File: ViewOutlineTree.java 1.0, 2010-6-24

    Copyright (c) Harry Gates

 */



import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;

import com.essbase.api.base.*;
import com.essbase.api.session.*;
import com.essbase.api.datasource.*;
import com.essbase.api.domain.*;
import com.essbase.api.metadata.*;

/**
    A platform-independent Essbase outline extractor.

    @author Harry Gates
    @version 1.0, 24 Jun 10
 */

public class ViewOutlineTree {

    // NOTE: The following variables will be set from the runViewOutlineTree.sh script on the Essbase server.

    private static String s_userName;
    private static String s_password; // = "password";
    private static String s_olapSvrName; // = "localhost";
    private static String s_provider = "http://servername:13080/aps/JAPI"; // = "embedded"; // Default
    private static String s_application = "Sample"; // = "IhtGl_X";
    private static String s_database = "Basic"; // = "Main";
    private static String s_dimension = "Product";
    private static String s_delimiter = "?"; // = ",";
    private static String s_file; // = "test.txt";
    static long stoptime = 2000L; //2 Seconds
    private static IEssCube cube = null;
    private static IEssOlapServer olapSvr = null;
    private static IEssCubeOutline otl = null;
    private static IEssIterator associatedAttributes;
    private static BufferedWriter outputStream;
    private static int iAssociatedAttributes_count;
    private static int iUDA_count;
    private static final int FAILURE_CODE = 1;

    public static void main(String[] args) {
        int statusCode = 0;
        IEssbase ess = null;
        long startTime = System.currentTimeMillis();
  
        try {
//            acceptArgs(args);
            // Create JAPI instance.
            ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);

            // Sign On to the Provider
            IEssDomain dom = ess.signOn(s_userName, s_password, false, null, s_provider);

            // Open connection with olap server and get the cube.
            olapSvr = (IEssOlapServer)dom.getOlapServer(s_olapSvrName);
            olapSvr.connect();
            cube = olapSvr.getApplication(s_application).getCube(s_database);
       
            // get the outline members for the specified dimension
            //  builds the s_output string containing all members
            listOutlineMembers(cube);
         
         // close the outline
            otl.close();
            otl = null;
         
            // add the header to the s_output string containing the list of outline members
            writeFileHeader();
        } catch (EssException x) {
            System.err.println("Error: " + x.getMessage());
            statusCode = FAILURE_CODE;
        } catch (InterruptedException e) {
   e.printStackTrace();
  } catch (IOException e) {
   e.printStackTrace();
  } finally {
            try {
                if (olapSvr != null && olapSvr.isConnected() == true)
                    olapSvr.disconnect();
            } catch (EssException x) {
                System.err.println("Error: " + x.getMessage());
            }

            try {
                if (ess != null && ess.isSignedOn() == true)
                    ess.signOff();
            } catch (EssException x) {
                System.err.println("Error: " + x.getMessage());
            }
          
            long elapsedTimeMillis = System.currentTimeMillis()-startTime;
            float elapsedTimeMin = elapsedTimeMillis/(60*1000F);
            System.out.println("minutes to run: " + elapsedTimeMin);
            // Set status to failure only if exception occurs and do abnormal termination
            // otherwise, it will by default terminate normally
            if (statusCode == FAILURE_CODE) System.exit(FAILURE_CODE);
        }
    }

    static void listOutlineMembers(IEssCube cube) throws EssException, InterruptedException, IOException {
        otl = null;
        try {
            otl = cube.openOutline();
        
            IEssIterator dims = otl.getDimensions();
            for (int i = 0; i < dims.getCount(); i++) {
                IEssDimension dim = (IEssDimension)dims.getAt(i);
           
                if (s_dimension.equals(dim.getName())) {
                 outputStream = new BufferedWriter(new FileWriter(s_file + "/" + dim.getName() + ".txt"));
                 associatedAttributes = dim.getDimensionRootMember().getAssociatedAttributes();
                 iAssociatedAttributes_count = associatedAttributes.getCount();
                
                    // run the recursive method to walk the outline tree
                 listOutlineMembers_helper(dim.getDimensionRootMember());
               
                 outputStream.close();
                }
            }         
        } catch (EssException x) {
            System.err.println("Error: " + x.getMessage());
            if (otl != null) {
                try {
                    otl.close();
                } catch (EssException e) {
                    System.err.println("Error: " + e.getMessage());
                }
            }
        }
    }
 
    static void listOutlineMembers_helper(IEssMember mbr) throws EssException, InterruptedException, IOException {
     
        IEssIterator mbrs = mbr.getChildMembers(false);
        for (int i = 0; i < mbrs.getCount(); i++) {
         IEssMember mb = (IEssMember)mbrs.getAt(i);
         displayMemberProperties(mb);
            listOutlineMembers_helper(mb);
        }
    }
   
    private static void displayMemberProperties(IEssMember mbr) throws EssException, IOException {
     String s_output = "";
     IEssMemberSelection mbrSel = null;
   
     s_output += mbr.getParent().getName() + s_delimiter + mbr.getName() + s_delimiter;

//      Member alias
        String s_alias = mbr.getAlias("default");
        s_alias = s_alias == null ? "" : s_alias;
        s_output +=  s_alias + s_delimiter;
         
        String s_consType = mbr.getConsolidationType().toString();
        s_output += s_consType.replaceAll(" .*$", "") + s_delimiter;
     
//      Formulas: not needed for Organisation dimension
        String frm = mbr.getFormula();
        if (frm != null) {
         s_output = s_output + frm.replaceAll("\\r?\\n", " ") + s_delimiter;
        }
      
        s_output += s_delimiter;      

//      UDAs
        String[] s_UDAs = mbr.getUDAs();
        if (s_UDAs != null) {
         for (int i = 0; i < s_UDAs.length; i++) {
          s_output = s_output + s_UDAs[i] + s_delimiter;
         }

         if (s_UDAs.length < iUDA_count) s_output += repeat(s_delimiter, iUDA_count - s_UDAs.length);
         iUDA_count = s_UDAs.length > iUDA_count ? s_UDAs.length : iUDA_count;
        } else {
         s_output += repeat(s_delimiter, iUDA_count);
        }
    
        write2File(s_output, true);
    }

    private static String repeat(String src, int repeat) {
     StringBuffer buf=new StringBuffer();
     for (int i=0; i<repeat; i++) {
      buf.append(src);
     }
     return buf.toString();
    }
      
    private static void write2File(String msg, boolean bNewLine) throws IOException {
  outputStream.write(msg);
  if (bNewLine) outputStream.newLine();
    }
  
    private static void writeFileHeader() throws EssException, IOException {
     String s_header  = "PARENT0,Time" + s_delimiter + "CHILD0,Time" + s_delimiter + "ALIAS0,Time" + s_delimiter + "PROPERTY0,Time" + s_delimiter + "FORMULA0,Time" + s_delimiter;
     String sNewHeader = s_header.replaceAll("Time", s_dimension);
     outputStream = new BufferedWriter(new FileWriter(s_file + "/" + s_dimension + "_header.txt"));
   
     outputStream.write(sNewHeader);

//  add UDAs to header
  for (int j = 0; j < iUDA_count; j++) {
   outputStream.write("UDA0," + s_dimension + s_delimiter);
  }
 
  outputStream.close();
    }

    static void acceptArgs(String[] args) throws EssException {
        if (args.length >= 7) {
            s_userName = args[0];
            s_password = args[1];
            s_olapSvrName = args[2];
            s_provider = args[3];
            s_application = args[4];
            s_database = args[5];
            s_dimension = args[6];
            s_delimiter = args[7];
            s_file = args[8];
        }
    }
}