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

Partitions

As I mentioned in my first post, I've recently begun using partitioning to move data between cubes. After getting this working I repeatedly smacked my forehead while moaning, "Why, oh, why haven't I tried this before?!". BP (Before Partitioning, not to be confused with the petroleum giant) I would use one of several options to move data: export all level 0 data (this was usually the fastest option), export the results of a report script to a file, or run a calc script with DATAEXPORT with FIXes. These are all still valid options AP (After Partitioning; no, not Advanced Placement, although you will likely get credit from management for using partitioning), but going forward I will be using them in more limited, specialized circumstances due to the awesome power of partitioning.

Partitioning comes in 3 flavors: replicated, transparent, and linked. While I'm sure they're all delicious, I must admit that I have really only sampled replicated partitions. This is mainly because I've never had a requirement that two cubes remain synchronized in real-time, which would require transparent partitions. The functionality provided by linked partitions can also pretty much be replicated (no pun intended) with judicious use of @XREFs. Therefore, from here on out when I refer to partitions, you'll know I mean the replicated kind.

It seems that most people create their partitions on the fly, replicate data, then delete the partition. I can think of two reasons for this. First, you can only have one partition between any given source-target database combination. So, you may be asking yourself, what if I need to move a different set of data to the target cube than is defined in the current partition definition? You can use substitution variables to some extent, but they won't get you the whole way there. Second, if left to themselves for too long, partition definitions seem to have a disturbing tendency to become corrupted.

Well, without further ado, here is the MaxL:

login user password on 'server';

shell "'date'";
drop replicated partition SrcApp.SrcDB to TarApp.TarDB;
shell "'date'";
create or replace replicated partition SrcApp.SrcDB
area '@RELATIVE("Comparability",0),"Units","Actual",@RELATIVE("Product",0),@RELATIVE("Accounts",0),@RELATIVE("Q1",0),&CurrYr,"USD",@RELATIVE("Entity", 0),@RELATIVE("Brand",0)'
to TarApp.TarDB at localhost
as user identified by password
area '@RELATIVE("Comparability",0),"Units","ActualTest",@RELATIVE("Product",0),@RELATIVE("Accounts",0),@RELATIVE("Q1",0),&CurrYr,"USD",@RELATIVE("Entity", 0),@RELATIVE("Brand",0)' foo
mapped globally ("Actual") to ("ActualTest")
update allow;
shell "'date'";
refresh replicated partition SrcApp.SrcDB to TarApp.TarDB at 'server' all data;
shell "'date'";
drop replicated partition SrcApp.SrcDB to TarApp.TarDB;
shell "'date'";
logout;
exit;

MDX - LinkMember

I've been working with MDX in ASO much more lately.  Databases just keep getting bigger and bigger and they no longer perform well in BSO.  Having worked with relational databases and sql for years before stumbling into Essbase, I am really enjoying working with MDX.  The similarities with sql make it a much better match for me than report scripts, which I generally avoid at all costs.

LinkMember is my MDX friend-of-the-day.  I was working with a database that has multiple-hierarchies enabled for the entity dimension and was struggling to get a currency conversion MDX formula to work with the shared members of the alternate hierarchies.  Turns out LinkMember was the solution.  Take a look at it in the 11.1.1.3 techref.

Introduction

A couple of months ago I moved to Atlanta. I’m no longer consulting, now working in-house at a large company in the area. So far I’m enjoying the change in perspective. While consulting I often didn’t get to see the results of projects. Now I will be around to see exactly how my cubes are used, or abused. This will hopefully give me the opportunity to get more involved in optimizing cubes. I’m thinking here about MDX member formulas in ASO replacing BSO calc scripts. ASO databases seem to be Oracle’s preferred solution going forward, so I might as well get used to transitioning BSO cubes to them. I think the limited ASO calc functionality introduced in 11.1.2 is just the tip of the iceberg.

I’ve already gotten the chance to work with some new-to-me areas of Essbase. For example, I’ve used replicated partitions to move data from a BSO to an ASO rather than exporting level 0 data from the BSO then importing it into the ASO. Partitioning is an order of magnitude faster and easier to maintain. I’ve heard that partitions have a tendency to become corrupted, so I’m keeping a close watch on the replication process. I've also used MDX formulas in ASO to replace currency conversion calc scripts in BSO.

As I’m no longer working crazy consulting hours, I’m hoping to have more time to update this blog with my Essbase escapades. We’ll see how that works out...