.NET Essbase MaxL Wrapper

• Download EssbaseWrappers Sample - 9.5 MB

Introduction 

Dealing with the Essbase API from .NET apps is a headache due interop, so I managed to wrap the Essbase MaxL API in a C++/CLI project and simplify my life developing Essbase admin features.

Background

A working Essbase client or server is required. The wrapper depends on unmanaged DLLs. I have used Visual Studio 2010 and Essbase 11.

Building the Wrapper

Hands on in simple steps:

1. In VS Create a new C++ CLR Class library project. Name it Essbase.MaxL.
2. Locate the following files in the Essbase api folder (see Points of Interest below):
   • essapi.h
   • essmaxl.h
   • essotl.h
   • esstsa.h
   • esstypes.h
   • maxldefs.h
   • essmaxlu.lib
3. Copy/Paste these files to the VC++ project folder.
4. In VS show all project files and include the pasted files.
5. Add a new class (default settings) named MaxLTypes.
6. Paste the code below this instructions in the specified files.
7. For x64 platform change it in Debug/Configuration Manager.
8. Build and happy coding!!!

Essbase.MaxL.h

#pragma once
#include "essmaxl.h"
#include "MaxLTypes.h"

namespace Essbase {

 using namespace System;
 using namespace System::Data;
 using namespace System::Runtime::InteropServices;

 public ref class MaxL
 {
  private:
   //Init Structure   
   MAXL_INSTINIT_T* pInstInit;

   //Session Structure   
   MAXL_SSNINIT_T* pSsnInit;

   //Session Id   
   MAXL_SSNID_T* pSsnId;

   //Last command status   
   MAXL_MSGLVL_T status;

   //Returns MessageLevel from Status Code
   static MessageLevel GetMessageLevel(MAXL_MSGLVL_T code);

   //Fetches data for 'display' command
   static bool FetchRow(MAXL_SSNID_T* pSsnId, MAXL_SSNINIT_T* pSsnInit);

  public:
   //Methods definitions
   MaxL(String^ server, String^ user, String^ password);
   ~MaxL();
   !MaxL();
   void Exec(String^ statement);
   void Exec(String^ statement, [Out]DataTable^% data);
   bool TryParse(String^ statement);
   bool TryParse(String^ statement, [Out]String^% message);
 };
}

Essbase.MaxL.cpp

#include "stdafx.h"
#include "essmaxl.h"
#include "Essbase.MaxL.h"
#include "MaxLTypes.h"
#include <msclr/marshal.h>
 
using namespace System;
using namespace System::Data;
using namespace System::Runtime::InteropServices; 
using namespace msclr::interop;
 
//I don't trust unmanaged code, so this method is only in case of unexpected status code from api
Essbase::MessageLevel
Essbase::MaxL::
GetMessageLevel(MAXL_MSGLVL_T code)
{
 MessageLevel level;
 switch (code) 
 {
  case MAXL_MSGLVL_SUCCESS:
  case MAXL_MSGLVL_WARNING:
  case MAXL_MSGLVL_ERROR:
  case MAXL_MSGLVL_SESSION:
  case MAXL_MSGLVL_FATAL:
  case MAXL_MSGLVL_OUT_OF_SESSIONS:
  case MAXL_MSGLVL_BAD_INIT:
  case MAXL_MSGLVL_ESSAPI_ERROR:
  case MAXL_MSGLVL_BAD_PASSWORD:
  case MAXL_MSGLVL_BAD_HOSTNAME:
  case MAXL_MSGLVL_ACCOUNT_LOCKEDOUT:
  case MAXL_MSGLVL_ACCOUNT_EXPIRED:
  case MAXL_MSGLVL_END_OF_DATA:
  case MAXL_MSGLVL_BAD_INST:
  case MAXL_MSGLVL_BAD_SSNID:
  case MAXL_MSGLVL_BAD_CONNECT:
  case MAXL_MSGLVL_LNX_62:
  case MAXL_MSGLVL_ERR_SESS_ENCODE:
   level = (MessageLevel)code;
   break;
  default:
   level = (MessageLevel)-1;
   break;
 }
 return level;
};
 
//Helper for data looping purposes
bool
Essbase::MaxL::
FetchRow(MAXL_SSNID_T* pSsnId, MAXL_SSNINIT_T* pSsnInit)
{
 //Fetch a new row into buffers and throw if not suceeded
 MAXL_MSGLVL_T code = MaxLOutputFetch((const MAXL_SSNID_T)*pSsnId, 0);
 if(code != MAXL_MSGLVL_SUCCESS && code != MAXL_MSGLVL_WARNING && code != MAXL_MSGLVL_END_OF_DATA)
  throw gcnew MaxLException(gcnew String(pSsnInit->MsgText), GetMessageLevel(code), (Int32)pSsnInit->MsgNumber);
 
 return code != MAXL_MSGLVL_END_OF_DATA;
};
 
//Constructor
Essbase::MaxL::
MaxL(String^ server, String^ user, String^ password)
{
 marshal_context context;
 
 //Init instance and two connections max
 pInstInit = new MAXL_INSTINIT_T();
 pInstInit->CtxListLen = 2;
 
 //Init and throw if not succeeded
 status = MaxLInit(pInstInit);
 if(status != MAXL_MSGLVL_SUCCESS)
  throw gcnew MaxLException(gcnew String(pInstInit->MsgText), GetMessageLevel(status), -1);
 
 //Instance session objects
 pSsnInit = new MAXL_SSNINIT_T();
 pSsnId = new MAXL_SSNID_T();
 
 //Cast parameters to unmanaged types
 const char* u_server = context.marshal_as<const char*>(server);
 const char* u_user = context.marshal_as<const char*>(user);
 const char* u_password = context.marshal_as<const char*>(password);
 
 //Create session and throw if not succeeded
 status = MaxLSessionCreate((char*)u_server, (char*)u_user, (char*)u_password, pSsnInit, pSsnId);
 if(status != MAXL_MSGLVL_SUCCESS)
  throw gcnew MaxLException(gcnew String(pSsnInit->MsgText), GetMessageLevel(status), (Int32)pSsnInit->MsgNumber);
};
 
//Simple Exec
void
Essbase::MaxL::
Exec(String^ statement)
{
 DataTable^ fooTable;
 Exec(statement, fooTable); //Overload call
};
 
//Exec with returning data
void
Essbase::MaxL::
Exec(String^ statement, [Out]DataTable^% data)
{
 marshal_context context;
 
 //Cast statement to unmanaged type
 const char* u_statement = context.marshal_as<const char*>(statement);
 
 //Perform and throw if not succeeded
 status = MaxLExec((const MAXL_SSNID_T)*pSsnId, u_statement, MAXL_OPMODE_DEFAULT);
 if(status != MAXL_MSGLVL_SUCCESS)
  throw gcnew MaxLException(gcnew String(pSsnInit->MsgText), GetMessageLevel(status), (Int32)pSsnInit->MsgNumber);
 
 //If command is 'select' throw exception
 if(pSsnInit->ExecVerb == MAXL_SELECT)
    throw gcnew InvalidOperationException("MDX queries are not supported");
 
 //If command is 'display' or 'query' start retrieving data
 if(pSsnInit->ExecVerb == MAXL_DISPLAY || pSsnInit->ExecVerb == MAXL_QUERY)
 {
  //Get column count (API formula is irrelevant here, but just in case)
  int size = (pSsnInit->ExecArity - 1) + 1;
 
  //Columns info array instance
  MAXL_COLUMN_DESCR_T* columns = new MAXL_COLUMN_DESCR_T[size];
 
  //Get columns and throw if not succeeded
  status = MaxLOutputDescribe((const MAXL_SSNID_T)*pSsnId, 1, pSsnInit->ExecArity, columns);
  if(status != MAXL_MSGLVL_SUCCESS)
   throw gcnew MaxLException(gcnew String(pSsnInit->MsgText), GetMessageLevel(status), (Int32)pSsnInit->MsgNumber);
 
  //DataTable instance for results
  data = gcnew DataTable();
 
  //Untyped array instance to receive rows data
  void** buffers = (void**)calloc(size, sizeof(*buffers));
  
  //Columns loop
  for(int i = 0; i < size; i++)
  {
   //Managed column type
   Type^ t;
 
   //Unmanaged column type
   MAXL_DTEXT_T e = MAXL_DTEXT_ULONG;
 
   //Evaluate column type and prepare needed objects
   switch (columns[i].IntType)
   {
    case 1:
     t = Boolean::typeid;
     buffers[i] = new short;
     break;
    case 2:
    case 4:
     t = Int32::typeid;
     buffers[i] = new long;
     break;
    default:
     t = String::typeid;
     e = MAXL_DTEXT_STRING;
     buffers[i] = malloc(columns[i].IntLen + 1);
     break;
   }
 
   //Define column to retrieve and throw if not succeeded
   status = MaxLColumnDefine((const MAXL_SSNID_T)*pSsnId, i + 1, buffers[i], columns[i].IntLen + 1, e, 0, NULL, NULL);
   if(status != MAXL_MSGLVL_SUCCESS && status != MAXL_MSGLVL_WARNING)
    throw gcnew MaxLException(gcnew String(pSsnInit->MsgText), GetMessageLevel(status), (Int32)pSsnInit->MsgNumber);
 
   //Add new column to DataTable
   data->Columns->Add(gcnew String(columns[i].Name), t);
  }
 
  //Loop every row to retrieve data and populate table
  while(FetchRow(pSsnId, pSsnInit))
  {
   //Get new row defined by DataTable
   DataRow^ row = data->NewRow();
 
   //Loop every field and fill DataRow
   for(int i = 0; i < size; i++)
   {
    //Evaluate column type again and get managed value from buffers
    switch (columns[i].IntType)
    {
     case 1:
      row[i] = gcnew Boolean(*(reinterpret_cast<const bool *>(buffers[i])));
      break;
     case 2:
     case 4:
      row[i] = gcnew Int32(*(reinterpret_cast<const long *>(buffers[i])));
      break;
     default:
      row[i] = gcnew String(reinterpret_cast<const char *>(buffers[i]));
      break;
    }
   }
   
   //Add new row to table
   data->Rows->Add(row);
  }
 
  //Destroy objects, important!!!
  free(buffers);
  delete [] columns;
 }
};
 
//Simple TryParse
bool
Essbase::MaxL::
TryParse(String^ statement)
{
 String^ foo;
 return TryParse(statement, foo); //Overload call
};
 
//TryParse with output message
bool
Essbase::MaxL::
TryParse(String^ statement, [Out]String^% message)
{
 marshal_context context;
 
 //Cast statement to unmanaged type
 const char* u_statement = context.marshal_as<const char*>(statement);
 
 //Perform command
 status = MaxLExec((const MAXL_SSNID_T)*pSsnId, u_statement, MAXL_OPMODE_PARSE_ONLY);
 
 //Evaluate status code, set output message and return
 if(status != MAXL_MSGLVL_SUCCESS)
 {
  message = String::Format(gcnew String(pSsnInit->MsgText));
  return false;
 }
 else
 {
  message = String::Empty;
  return true;
 }
};
 
//Managed destructor
Essbase::MaxL::
~MaxL()
{
 this->!MaxL();
};
 
//Unmanaged destructor
Essbase::MaxL::
!MaxL()
{
 //Destroy Session if needed
 if(pSsnId != 0)
  MaxLSessionDestroy((MAXL_SSNID_T)pSsnId);
 
 //Destroy API and release objects, important!!!
 MaxLTerm();
 delete pSsnId;
 delete pSsnInit;
 delete pInstInit;
};

MaxlTypes.h

#pragma once
#include "maxldefs.h"

namespace Essbase {

 using namespace System;

 //Status codes translation
 public enum class MessageLevel : int
 {
  SUCCESS = MAXL_MSGLVL_SUCCESS,
  WARNING = MAXL_MSGLVL_WARNING,
  ERROR = MAXL_MSGLVL_ERROR,
  SESSION = MAXL_MSGLVL_SESSION,
  FATAL = MAXL_MSGLVL_FATAL,
  OUT_OF_SESSIONS = MAXL_MSGLVL_OUT_OF_SESSIONS,
  BAD_INIT = MAXL_MSGLVL_BAD_INIT,
  ESSAPI_ERROR = MAXL_MSGLVL_ESSAPI_ERROR,
  BAD_PASSWORD = MAXL_MSGLVL_BAD_PASSWORD,
  BAD_HOSTNAME = MAXL_MSGLVL_BAD_HOSTNAME,
  ACCOUNT_LOCKEDOUT = MAXL_MSGLVL_ACCOUNT_LOCKEDOUT,
  ACCOUNT_EXPIRED = MAXL_MSGLVL_ACCOUNT_EXPIRED,
  END_OF_DATA = MAXL_MSGLVL_END_OF_DATA,
  BAD_INST = MAXL_MSGLVL_BAD_INST,
  BAD_SSNID = MAXL_MSGLVL_BAD_SSNID,
  BAD_CONNECT = MAXL_MSGLVL_BAD_CONNECT,
  BAD_LINUX_VERSION = MAXL_MSGLVL_LNX_62,
  ERR_SESS_ENCODE = MAXL_MSGLVL_ERR_SESS_ENCODE,
  UNKNOWN = -1 //Added for unexpected responses
 };

 //Exception definition, Level and Code members added
 public ref class MaxLException : public Exception
 {
 private:
  //Backing fields
  MessageLevel messageLevel;
  Int32 messageCode;

 public:
  MaxLException();
  MaxLException(String^ message);
  MaxLException(String^ message, MessageLevel level, long code);
  MaxLException(String^ message, Exception^ inner);
  MaxLException(String^ message, MessageLevel level, long code, Exception^ inner);
  property Essbase::MessageLevel Level
  { 
   public: Essbase::MessageLevel get();
  }
  property Int32 Code
  { 
   public: Int32 get();
  }
 };
}

MaxLTypes.cpp

#include "stdafx.h"
#include "MaxLTypes.h"

using namespace System;

//MaxLException constructors setting Level and Code if needed
Essbase::MaxLException::
MaxLException() { };

Essbase::MaxLException::
MaxLException(String^ message) : Exception (message) { };

Essbase::MaxLException::
MaxLException(String^ message, MessageLevel level, long code) : Exception (message) 
{ 
 messageLevel = level;
 messageCode = code;
};

Essbase::MaxLException::
MaxLException(String^ message, Exception^ inner) : Exception (message, inner) { };

Essbase::MaxLException::
MaxLException(String^ message, MessageLevel level, long code, Exception^ inner) : Exception (message, inner) 
{ 
 messageLevel = level;
 messageCode = code;
};

//Readonly property Level
Essbase::MessageLevel
Essbase::MaxLException::Level::
get()
{
 return messageLevel;
}

//Readonly property Code
Int32
Essbase::MaxLException::Code::
get()
{
 return messageCode;
}

Using the Wrapper

• MaxL class: This is the main class. The constructor needs the server, user and password.
• MaxL.Exec method: Executes a MaxL statement. Optional returned values in a DataTable object.
• MaxL.TryParse method: Evaluates a MaxL statement with no changes, returns true if succeeded. Optional return message if it fails.
• MaxL.Dispose method: Don't forget to call it afer no further use.
• MaxLException class: Exception. Message, Level and Code properties returns the data received from API.
• MessageLevel enum: Status codes translated from API.

Sample C# Code

using(var testMaxL = new Essbase.MaxL("server", "admin", "password"))
{
   testMaxL.Exec("create group 'FooGroup'");

   string reason;
   bool result = testMaxL.TryParse("create or replace user 'fooUser' type external", out reason);

   System.Data.DataTable table;
   testMaxL.Exec("display user all", out table);
}

Points of Interest

The default Essbase api folder is C:\Oracle\EPMSystem11R1\products\Essbase\EssbaseXXXXXX\api where XXXXXX is Server or Client. Also, Essbase installation creates a EssbaseClient-32 or EssbaseServer-32 folder inside the Essbase folder in case of x64 platform supported.

This Wrapper is not intended for MDX queries, InvalidOperationException will be thrown in such attempt.

Included API header files contains detailed information about methods and types

A complete MaxL technical reference can be found at Oracle's website.

This is my first contribution and my first C++ approach (I'm a C# guy) so, any improvements and suggestions are appreciated.