Automating Essbase member deletion using API 13


Why we don’t have a MaxL or MDX statement to delete a member from Essbase database.

If you look at Planning, OutlineLoad and ODI supports deleting members from Planning.

(Yes I hear what you are saying) You can use load rule and use remove unspecified to delete the unwanted members, but isn’t it risky?

I always thought about automating Essbase member deletion process. But if you look at this there is no way to perform it other than using an API.

I was planning to create a command line interface for renaming and deleting Essbase members using Java, but couldn’t start working on those for a long time.

On a fine Wednesday morning a friend of mine reminded me about it and I went back and looked at an year and half old script which uses the API EsbOtlDeleteMember. Polished it little bit and here we are 🙂

Usage

Delete_Essbase_Member.exe /S:ServerName /U:username /P:password /A:App /D:Db /M:”Member to Delete” /R:[All/Level0/Input/Discard] /L:Logfile

Execution status from logs

[Mon Oct 14 06:10:40 2013] The Essbase RTC version is 11.1.2.2
[Mon Oct 14 06:10:41 2013] User admin logged into EssbaseServer
[Mon Oct 14 06:10:41 2013] Sample.Basic set active for user admin
[Mon Oct 14 06:10:41 2013] Sample.Basic outline open for edit
[Mon Oct 14 06:10:41 2013] Found member “Sales” in outline
[Mon Oct 14 06:10:41 2013] Member “Sales” deleted from outline
[Mon Oct 14 06:10:41 2013] Outline saved
[Mon Oct 14 06:10:41 2013] Outline restructured with “All Data”
[Mon Oct 14 06:10:41 2013] User admin logged out from EssbaseServer
[Mon Oct 14 06:10:41 2013] API session ended

Drawbacks

The status of execution is not updated on screen. However you have logs 🙂 and a little bit of scripting will let you know whether it failed or deleted the member.
You need Essbase client to execute it.
It will not verify the outline (All member formulas referencing this member(deleted member) will fail)

Note: Make sure to backup your application and data before using it. I’m not responsible for any damages caused to your system/application/database.

This was tested on 11.1.2.2 Essbase version and found to be working.

You can download the file from here

Download Now

About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.


Leave a Reply

13 thoughts on “Automating Essbase member deletion using API

  • GlennS

    This is useful, but don't you have to have the runtime client installed on the client in order to run this? I guess you could run it from the server. Also, you have to have admin privledges in order to use it. There is a more complete example of this on the odtug papers site from Kaleidascope 2008 where I demo'ed this from Excel allowing you to delete a list of members defined in an excel worksheet.

  • Achan Chopra

    Hey Celvin,

    Thanks for the post…i have been working with VB API mainly and it requires the runtime client to initialize the API…..one question i would be more interested in is how much does it take to restructure using Java API ? Is it faster then normal essbase restructure….please share if you have any valuable experience with this

  • Anonymous

    I tried using this but it gives me error saying that member not found.We are on Essbase:11.1.2.3.5.Any tips how we can fix this.

    [Fri Mar 20 12:40:46 2015] The Essbase RTC version is 11.1.2.3
    [Fri Mar 20 12:40:46 2015] User admin logged into dev-hyperion-db1
    [Fri Mar 20 12:40:46 2015] AAA.AAA set active for user admin
    [Fri Mar 20 12:40:46 2015] AAA.AAA outline open for edit
    [Fri Mar 20 12:40:46 2015] Member "ggg" not found in outline
    [Fri Mar 20 12:40:46 2015] User admin logged out from dev-hyperion-db1
    [Fri Mar 20 12:40:46 2015] API session ended

  • Lubna

    Hi Celvin,
    I have not worked on APIs before , but have a requirement for which I have to use this API function -EsbOtlDeleteMember
    I cannot run your Delete_Essbase_Member.exe on my machine due to security reasons .
    Can you share if you have used VB script or C for calling this function ?
    Hyperion 11.1.2.4 is installed on Linux so trying to understand how to call this function .
    Please guide

    • Celvin Kattookaran Post author

      Try Essbase member operation utility, that is based on JAVA API. You’ll be able to use that on a Linux machine.