Automating Essbase member deletion using API


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
Advertisements

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

0 thoughts on “Automating Essbase member deletion using API