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];
        }
    }
}