This discussion is archived
11 Replies Latest reply: May 16, 2012 8:21 AM by 934257 RSS

Harvesting member information for all dimensions via the C API - how?

934257 Newbie
Currently Being Moderated
Hi,

Sorry if this is a newbie question but I'm wondering how, using Essbase's Main C API, I can iterate over each dimension in a database and pull out the details for each member, including its parent/sibling/child info and ideally additional attributes.

I looked at EssGetMemberInfo() but that needs the member name to start with. If I could grab the "root" element in a dimension to start the process off I'd be OK because I could then walk the hierarchy, calling this function on each element and following children/siblings, etc., but I don't know it (do all dimensions have a standard root element name on every dimension?) [This procedure might also be rather inefficient, calling the function for every element, I'm not sure, so suggestions for a better way would also be greatly appreciated.]

Can anyone give me a pointer as to where to start? 8-S

Thanks.
  • 1. Re: Harvesting member information for all dimensions via the C API - how?
    Daylan Journeyer
    Currently Being Moderated
    Hello,

    Have a look at EsbOtlGetFirstMember, it may help.

    http://docs.oracle.com/cd/E17236_01/epm.1112/esb_api_ref/apvfgfir.html

    Regards,
    Ahmet
  • 2. Re: Harvesting member information for all dimensions via the C API - how?
    934257 Newbie
    Currently Being Moderated
    Thanks for the speedy response, Ahmet, but, like this other requester (using EssOtlOpenOutline with read only user ID I can't use the Outline API without DB/APP Designer access, it seems, so I'm trying to stick to the Main API or the Grid API. Even though the docs for EssOtlOpenOutline() (http://docs.oracle.com/cd/E12825_01/epm.111/esb_apiref/apcfopen.htm) say Designer access is required if you lock the Outline, it seems it is required even if you don't lock it!

    Do you know how I can enumerate the hierarchy/members with merely regular read permissions? The docs say that I can use EssGetMemberInfo(...) with just ESS_PRIV_READ permissions, so all I think I need is a way to find the first/root member in each dimension!

    Thanks again for your help!

    Edited by: user7677408 on 01-May-2012 06:42 - fixed typo
  • 3. Re: Harvesting member information for all dimensions via the C API - how?
    Daylan Journeyer
    Currently Being Moderated
    Hi Again,

    I don't suppose there will be a way to use outline functions with a mere read only user.

    This small piece is finding the first dimension and send a message box saying the name of the dimension. But I tried with an db admin user.

    obj.hCtx = lngCtxHndl
    obj.Type = ESB_OBJTYPE_OUTLINE
    obj.AppName = "Appname"
    obj.DbName = "Dbname"
    obj.FileName = "Dbname"
    openoutline = EsbOtlOpenOutline(lngCtxHndl, obj, 0, 0, hOutline)
    get1stmember = EsbOtlGetFirstMember(hOutline, hMemberFirst)
    getprop = EsbOtlGetMemberInfo(hOutline, hMemberFirst, fmbr)
    MsgBox "Top member is " & fmbr.szMember

    Cheers,
    Ahmet
  • 4. Re: Harvesting member information for all dimensions via the C API - how?
    934257 Newbie
    Currently Being Moderated
    Thanks again, Ahmet.

    I've been talking to a colleague here and he said that on each dimension in Essbase there is always a root element in the hierarchy with the name of the dimension. If this is true I imagine I could use EssGetDimensionInfo(NULL) to return a list of all dimensions, and then use EssGetMemberInfo() on each of those dimensions names to get the details of the root element in each dimension. This member info should include child and sibling information, so I should be able to make repeated calls to EssGetMemberInfo() on the child elements and siblings to work my way across all elements in the outline without calling any EssOtl functions, shouldn't I?

    Does this make sense do you think? Might this be my solution?

    Many thanks for all of your help on this.
  • 5. Re: Harvesting member information for all dimensions via the C API - how?
    Daylan Journeyer
    Currently Being Moderated
    Hi Zarquon,

    Once you get a reference to the first dimension's top member. you can use EsbOtlGetNextSibling() to grab next dimension's top member.
    This is because dimension top members are siblings of each other.
    I was thinking like that;

    get1stmember = EsbOtlGetFirstMember(hOutline, hMemberFirst)
    getprop = EsbOtlGetMemberInfo(hOutline, hMemberFirst, fmbr)
    MsgBox "Top member is " & fmbr.szMember
    get2stmember = EsbOtlGetNextSibling(hOutline, hMemberFirst, hMemberSecond)
    getprop2 = EsbOtlGetMemberInfo(hOutline, hMemberSecond, smbr)
    MsgBox "Next Dimension is " & smbr.szMember

    better to write within a loop to traverse all dimensions but this will give you an idea I hope.

    However, EssGetDimensionInfo() might be another approach to the problem, makes sense to me.

    Cheers,
    Ahmet
  • 6. Re: Harvesting member information for all dimensions via the C API - how?
    835737 Expert
    Currently Being Moderated
    Dylan,

    It looks like your trying to do a tree traversal without recursion. I have code posted here that will do this:
    http://codingwithhyperion.blogspot.com/2011_03_29_archive.html

    Regards,
    Robb Salzmann
  • 7. Re: Harvesting member information for all dimensions via the C API - how?
    934257 Newbie
    Currently Being Moderated
    Hi again Ahmet,

    As I said before I can't use any of the Outline APIs because they all assume you are a super-user and have DB/App designer/manager permissions.

    Instead I've been looking at the Main API (wherein I only require READ access) and it seems I'm pretty close but I'm getting a weird 1013003 error saying the outlines are empty, and I'm not sure what is going wrong. This is what I've got so far...

         ESS_INIT_T init_struct;
         init_struct.Version = ESS_API_VERSION;     // Api version
         init_struct.UserContext = 0;     // I'm not using this at the moment
         init_struct.MaxHandles = 0L;     // Max nos sim login context, rarely do we need > 1
         init_struct.MaxBuffer = 0L;
         init_struct.LocalPath = NULL;
         init_struct.MessageFile = NULL;

         init_struct.AllocFunc =          0;
         init_struct.ReallocFunc =     0;
         init_struct.FreeFunc =          0;

         init_struct.MessageFunc = (ESS_PFUNC_T) callback;
         init_struct.HelpFile = NULL;
         init_struct.Ess_System = 0L;

         // This works - returns 0
         s_sts = EssInit(&init_struct, &s_api_handle);

         // This works - returns 0
         s_sts = EssAutoLogin(s_api_handle, server, username, password, application, database, AUTO_NODIALOG | AUTO_NOSELECT, &app_access, &s_context_handle);

         // This works - returns 0
         s_sts = EssSetActive(s_context_handle, application, database, &app_access);

         unsigned long num_dims;
         ESS_DIMENSIONINFO_T *pDims = 0;
         char *dim_name = NULL;
         s_sts = EssGetDimensionInfo(s_context_handle, dim_name, &num_dims, &pDims);

    But at this stage my s_sts status always seems to be 1013003, which is "Outline is empty". But it seems to me that if this works I should be able to recurse the hierarchy by starting with the each dimension name in turn.

    Anyone know why I'm getting this odd error? Same user shows the full outline hierarchy via the Essbase GUI.
  • 8. Re: Harvesting member information for all dimensions via the C API - how?
    Daylan Journeyer
    Currently Being Moderated
    Hi Robb,

    Nice java example for traversing outline,

    Regards,
    Ahmet
  • 9. Re: Harvesting member information for all dimensions via the C API - how?
    Daylan Journeyer
    Currently Being Moderated
    Hi Zarquon,

    I am not sure of C syntax but this piece is working fine in VB. returning 0 for all.

    d = EsbGetDimensionInfo(lngCtxHndl, "", nDims)
    e = EsbGetNextItem(lngCtxHndl, ESB_DIMINFO_TYPE, DimInfo)
    MsgBox "error " & d & e & DimInfo.DimName

    can you try to assign something to variable dim_name variable ? like "Acount"

    Regards,
    Ahmet
  • 10. Re: Harvesting member information for all dimensions via the C API - how?
    934257 Newbie
    Currently Being Moderated
    Hi Ahmet,

    Thanks for all of your suggestions.

    I have tried assigning known names but that seems to return the same thing.

    The docs say passing in NULL should return details of all dimensions, passing in a dimension name should return info for just that dimension and passing in an invalid name should return an error, but I'm getting error 1013003 each time - "AD_MSGRM_DSNLOAD", which apparently means "Database Outline Is Empty". I was forgetting to call SetActive() initially (which gave me a different error) so I'm wondering if there's something else I'm forgetting to call before I call EssGetDimensionInfo().
  • 11. Re: Harvesting member information for all dimensions via the C API - how?
    934257 Newbie
    Currently Being Moderated
    OK, I got it working at long last. I wrote a version using the Outline APIs, which require very high-level access permissions even for read-only access to the metadata, as well as with the Main API EssGetMemberInfo function, etc. Both seem to work, although the Main API runs approximately 500 times slower than the Outline API for my outline - 4000 metadata elements and their attributes are loaded via the Outline API in around four seconds, whereas the Main API EssGetMemberInfo() function returns around two elements per second!

    One workaround I found was the EssQueryDatabaseMembers() function, which is much quicker than repeated calls to EssGetMemberInfo(), although that does entail building a textual report query and submitting it to EssQueryDatabaseMembers, and then repeatedly calling EssGetString() and parsing out the results from the text report returned.

    If only there was a more sensible and fast method of querying the metadata from Essbase programmatically rather than via text reports and parsing.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points