Monday 6 February 2012

Using the Essbase C API with C# - Initialisation

Essbase C API and .Net does not appear to be the hottest topic on the net, probably for obvious reasons, but somebody might find this useful. Working with the Essbase C API is fairly intuitive (after a while) and if imagination helps great products can come out.

Here are the steps to get the Essbase C API working in C# :
1.Install the Essbase Client and set the ESSBASE_PATH environment variable to “X:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseClient”
2.Alternatively copy the Essbase API to a folder and set the ESSBASE_PATH to point to that folder. This makes for an easier deployment.
3.Create a new project in VS and add the following classes:

public class EssbaseTypes
{
[DllImport("essapinu.dll", CharSet = CharSet.Ansi, SetLastError = true, ExactSpelling = true)]
static internal extern uint EssInit(ref ESS_INIT_T EssInitStruct, ref uint EssHInst);

[DllImport("essapinu.dll", CharSet = CharSet.Ansi, SetLastError = true, ExactSpelling = true)]
static internal extern uint EssTerm(uint EssHInst);
}

public delegate uint FcnPtr();

[StructLayout(LayoutKind.Sequential, Pack = 1)]
public struct ESS_INIT_T
{
   public uint Version;
   public uint UserContext;
   public ushort MaxHandles;
   public uint MaxBuffer;
   public string LocalPath;
   public string MessageFile;
   public FcnPtr AllocFunc;
   public FcnPtr ReallocFunc;
   public FcnPtr FreeFunc;
   public FcnPtr MessageFunc;
   public string HelpFile;
   public uint Ess_System;
   public uint usApiType;
}



4.Using the code:

string essApiPath = @"C:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseClient\bin\";
           
// Create initialisation structure
ESS_INIT_T essInit = new ESS_INIT_T();
essInit.Version = 0x000B1200;
essInit.UserContext = 0;
essInit.MaxHandles = 0;
essInit.MaxBuffer = 0;
essInit.LocalPath = essApiPath;
essInit.MessageFile = essApiPath + "essbase.mdb";
essInit.AllocFunc = null;
essInit.ReallocFunc = null;
essInit.FreeFunc = null;
essInit.MessageFunc = null;
essInit.HelpFile = essApiPath + "essapiw.hlp";
essInit.Ess_System = 0;
essInit.usApiType = 0x0002;

// instance handle
uint essHInst = 0;

// The errNum is 0 when successful
uint errNum1 = EssbaseTypes.EssInit(ref essInit, ref essHInst);
if (errNum1 == 0)
{
// Code goes here

uint errNum2 = EssbaseTypes.EssTerm(essHInst);
}

5. The errNum should be 0 in case of a successful initialisation. Any other code indicates an error. Errors are usually related to the essbase api path and the ESSBASE_PATH variable.

The value for Version in ESS_INIT_T can be found in file essapi.h. For 11.1.2 the version is 0x000B1200. 

5 comments:

  1. Thanks Alex. Now I just need to come up with a reason to use it

    ReplyDelete
  2. Thanks Alex, I have some problems to get it to work, maybe caused by the fact that we're using the 11.1.2.2.1 (I changed the version in the code to 0x000B1221 for that).

    In the essapi.h I found the following structure:
    ESS_TSA_ELEMENT(ESS_ULONG_T, Version); /* This should be set to ESS_API_VERSION */
    ESS_TSA_ELEMENT(ESS_PVOID_T, UserContext); /* void pointer to user's message context */
    ESS_TSA_ELEMENT(ESS_USHORT_T, MaxHandles); /* max number of context handles required */
    ESS_TSA_ELEMENT(ESS_SIZE_T, MaxBuffer); /* max size of buffer that can be allocated */
    ESS_TSA_ELEMENT(ESS_STR_T, LocalPath); /* local path to use for file operations */
    ESS_TSA_ELEMENT(ESS_STR_T, MessageFile); /* full path name of message database file */
    ESS_TSA_ELEMENT(ESS_PFUNC_T, AllocFunc); /* user-defined memory allocation function */
    ESS_TSA_ELEMENT(ESS_PFUNC_T, ReallocFunc); /* user-defined memory reallocation function */
    ESS_TSA_ELEMENT(ESS_PFUNC_T, FreeFunc); /* user-defined memory free function */
    ESS_TSA_ELEMENT(ESS_PFUNC_T, MessageFunc); /* user-defined message callback function */
    ESS_TSA_ELEMENT(ESS_STR_T, HelpFile); /* user-defined help file path */
    ESS_TSA_ELEMENT(ESS_PVOID_T, Ess_System); /* reserved for internal use */
    ESS_TSA_ELEMENT(ESS_USHORT_T, usApiType);
    ESS_TSA_ELEMENT(ESS_PCATCHFUNC_T, CatchFunc); /* user-defined kill-own-request signal callback function */
    ESS_TSA_ELEMENT(ESS_PCATCH_INIT_FUNC_T, CatchInitFunc); /* user-defined kill-own-request signal initialization callback function */
    ESS_TSA_ELEMENT(ESS_PCATCH_TERM_FUNC_T, CatchTermFunc); /* user-defined kill-own-request signal termination callback function */
    ESS_TSA_ELEMENT(ESS_PCOOKIE_CREATE_FUNC_T, CookieCreateFunc); /* user-defined cookie creation function */
    ESS_TSA_ELEMENT(ESS_PCOOKIE_DELETE_FUNC_T, CookieDeleteFunc); /* user-defined cookie creation function */


    I always get the following error:
    PInvokeStackImbalance was detected
    Message: A call to PInvoke function 'Ess11CsConsole!EssbaseTypes::EssInit' has unbalanced the stack. This is likely because the managed PInvoke signature does not match the unmanaged target signature. Check that the calling convention and parameters of the PInvoke signature match the target unmanaged signature.

    Do you have any idea how to solve this?
    Regards (another ;-)) Alex


    ReplyDelete
  3. Hi Alex,

    the code only works with Essbase API 32 bit so make sure you're not running 64 bit. A few changes needs to be done for 64 bit to work.

    I'm using the following ESS_INIT and it is working for me in 11.1.2.2 if I remember correctly:

    [StructLayout(LayoutKind.Sequential, Pack = 1)]
    public class ESS_INIT_T
    {
    public uint Version = 0x92000;
    public uint UserContext = 0;
    public System.UInt16 MaxHandles = 0;
    public uint MaxBuffer = 0;
    public string LocalPath; // Local path
    public string MessageFile;// Path to essbase.mdb
    public FcnPtr AllocFunc = null;
    public FcnPtr ReallocFunc = null;
    public FcnPtr FreeFunc = null;
    public FcnPtr MessageFunc = null;
    public string HelpFile;// path to essapiw.hlp
    public uint Ess_System = 0;
    public System.UInt16 usApiType = 0x0003;
    }

    As you can see the version is 0x92000. This is because for no apparent reason it doesn't seem to work well with any of the other version number (version numbers are in "essapi.h").

    Regards,
    Alex

    ReplyDelete
  4. Thank you, Alex.
    I tried with your code but still I get that same error.
    I copied the following content of the directory c:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseClient-32 from a Win2008R2 machine to my local Win7 x64 machine into c:\EssbaseClient11-32 and set the ESSBASE_PATH variable right to that path. Do you have any further suggestions?
    Regards, (another) Alex

    ReplyDelete
  5. This thread helped me a lot and I am thankful to Alex for it.
    Unfortunately I run onto the same issue as Anonymous above (the PInvoke error).
    To fix the problem, use the explicit CallingConvention declaration in your DllImport statement. Working code:
    [DllImport(@"essapinu.dll", CharSet = CharSet.Ansi, SetLastError = true, ExactSpelling = true, CallingConvention = CallingConvention.Cdecl)]
    static internal extern uint EssInit(ref ESS_INIT_T EssInitStruct, ref uint EssHInst);

    [DllImport(@"essapinu.dll", CharSet = CharSet.Ansi, SetLastError = true, ExactSpelling = true, CallingConvention = CallingConvention.Cdecl)]
    static internal extern uint EssTerm(uint EssHInst);

    I hope this will save somebody's time.
    Best!
    Karol.

    ReplyDelete