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.
Very nice. Thanks Celvin, and congrats again on being an Oracle ACE
I was thinking about this a week ago and here it is. It will be very useful. Thanks for sharing.
Great work Celvin!!!!
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.
Yes Glenn, that's the first reason I was trying to make it using Java API(to avoid client)
Glenn, I found two links
http://www.odtug.com/p/cm/ld/fid=65&tid=33&sid=179
http://www.odtug.com/p/cm/ld/fid=65&tid=33&sid=178
But if it is using VB APIs then don't they need runtime clients or is it picking it from the Add-inbin?
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
@Achan Chopra
I've not specifically checked the restructure timings (API vs Native), but I've a feel that there won't be a difference. Who knows, I could be wrong
Thanks for tool Celvin.
Is it possible to pass on multiple member names as argument to .exe file?
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
Did you every get past the ‘Member “ggg” not found in outline’ error? we are experiencing the same.
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
Try Essbase member operation utility, that is based on JAVA API. You’ll be able to use that on a Linux machine.