Oracle ACE Director

This was a dream that I didn't dare dreaming. I got a confirmation from Oracle ACE program that I'm that they did accept my nomination for elevating my ACE status to the next level as ACE Director.

Oracle ACE Director profile

My older smarter brother from completely different set of parents, Cameron Lackpour offered that he file my nomination for ACE Director level.

I was way too excited about that. I was in tears (of joy of course) reading through what he and other 6 people wrote about me. (even though I feel that Cameron had that brotherly affection and overstated some facts)

What I told him was "I'll be slightly disappointed if I don't make it, however this (nomination form) is the biggest recognition that I can ever get in my life." I even went on saying that I might frame that form. (I'll seriously do that :))

I would like to thank everyone who supported me, who believed in me. (I know it is just two words, but there are a lot of feelings behind that words which I cannot express right now)

Cameron Lackpour
Tim Tow
Glenn Schwartzberg
Steve Liebermensch
Prasad Kulkarni
Sreekumar Menon
and Shankar Viswanathan

I would also like to thank Oracle ACE Program for awarding the status to me.

Hyemi Han thank you for helping me start this journey, if it was not for you I would have never sent my ACE nomination (in August 2013, ok so now you all know whom to blame ;))

I would like to thank you gentle reader for following me, my blog posts. It's you who is giving me ideas and the energy to go forward.

Last but not the least my family.

- My lovely, beautiful wife, I wouldn't have achieved all this without your support and help. Thank you for listening to all my Hyperion talk and for all the inputs and ideas on the appearance of the tools that I create.

I was excited to hear the news about the promotion to the next level, however I was surprised to see her excitement. Boy!!!! I've never seen her this excited (not even on our wedding day, I think she was scared on that day ;))

- My Kids, Thank you kiddos for understanding this grumpy father ;) (I'm when I'm working on something).

I want to thank my family for letting me do what I do. (They know when I've something in my mind :))

Essbase Member Operation Utility - Encryption and find which members to tag as Never Share

I know many of you are using this utility and I had to use that myself at my project.

This was done to properly update a dimension. It had alternate hierarchy and there was member movements too. I had to delete the alternate hierarchy and then load the main hierarchy first and then the alternate.

I didn't like the idea of exposing the credentials on a production system and had to add encryption.

Here we are, you can now encrypt the options and pass that in the bat file. (Yeah I don't like just encrypting user name/password only). If you are choosing encryption then you've to encrypt the whole string.

You can encrypt the entire option by navigating to Essbase Member Operation lib directory (e.g. C:\Users\Administrator\Desktop\Essbase_Mbr_Operations_Util_1.4\lib)
Issue the following command

Update the bat file with the encrypted string

Find Implicit Shared Members

Implicit sharing can be a pain, I think it is high time to change the code and get rid of this. Yeah I know you can use IMPLIED_SHARE to change the behavior, however I don't like that setting. (I'm an old school guy, well maybe not "old")

I'm adding an option which will help you find out all potential Implicit Shared members in your cube.

You'll have to use export MaxL to export your outline as an XML file.

To export all dimensions use the below given MaxL
export outline 'Sample'.'Basic' all dimensions to xml_file 'C:\Temp\Sample.xml';

To export specific dimension use the below given MaxL
export outline 'Sample'.'Basic' list dimensions {"Market"} to xml_file 'C:\Temp\Sample.xml';

You can read more about export outline here.

Extract the outline, I used a modified version of Sample Basic for this.

Encrypt the options.

Update the bat file and there you go.

I did run this on a outline extract which has close to 1.4 Million members and it was done in 2 minutes, so it is fast!!!!

You can download the new version for here 
Documentation is available at

Planning Client Utilities

This is probably a work that I'm so proud of. I tried a lot to make it work and was about to give up and finally cracked it.

Cameron Lackpour and Sree Menon, I would like to thank you both for listening to me :).

Bit of a background, so as you know I'm working on this awesome security viewer (NUMSys) and was the next planned version will expand the capability to report on Planning security.

Till now Shared Services, Essbase and Workspace are all using their own API calls to create the security reports, however Planning is a different beast, it does not have an API. I was trying to see whether I can hack into the Planning API calls and somehow use that in NUMSys. However I soon realized that it is not going to be an easier task.

I was moving towards a SQL solution and talked to Cameron. Hw was generous enough to allow me to use some of his SQL code to pull some reports, along with some of the SQLs that I've written the job was done.

That was an easier way and you know, I don't like to give up that easily. Couple of late nights and I was able to crack the API code for Planning.

This was an annoying headache for most of us while doing a Planning implementation, if you've to import security you need to be on Planning server (or use PS.exe/RSH and so on). You don't need to do that anymore :)

Even though I named it was client utils it is not really a full fledged client (It uses Shared Services API, I'll discuss about this later), you need to run this on an EPM product installed machine. Hey it is still a client ;)

You need to copy some files to make this work from a client machine.

Copy Planning common folder from Planning server

Copy the following files from your Planning Server to the Essbase RTC folder
    • HspEssbaseEnv.dll
    • HspEssbaseGridAPI.dll
    • HspEssbaseMainAPI.dll
    • HspEssbaseMAXLAPI.dll
    • HspEssbaseOutlineAPI.dll


Update PlanningClientUtils.bat to reflect your environment details.

The first version of Client Utils allows you to
  1. Import Security
  2. Export Security
Import Security

It mimics the well established functionality of ImportSecurity.cmd. The difference being you can supply a security file and delimiter.

I've incorporated encryption (I'm not a believer of partial encryption, so if you are encrypting - encrypt all options)

You can encrypt options by issuing the following command under the lib folder

If you want to clear existing security, you can supply -CLEARALL option.

Update the bat file with the encrypted string and execute

It'll let you know how many lines goe updated successfully and which lines had issues.

Export Security

Similar to ExportSecurity.cmd, this however allows you to perform a wild card search for users/groups.

You can also mention user/group filter attributes while using Planning Client Utils.

User Filter Attributes

  • ID - User Name
  • Description - User description
  • FirstName - User first name
  • LastName - User Last Name
  • Email - User's Email Address

Group Filter Attributes

  • ID - Group Name
  • Description - Group description

Since this uses Shared Services API for filtering users, you need to run this command with an Admin User who has Shared Services Adminstrator and Planning Administrator privileges.

This is the main reason why it needs to be executed from an EPM product installed machine as Shared Services security jar file has lot of dependencies. (It also uses Calc Manager common jar files)

If you want to export all users and groups use it without mentioning ACCESS_USER/ACCESS_GROUP option.

ID is used as the default search attribute if filter attribute is not supplied.

You can also filter for USER and GROUP at same time.


For encrypting the parameters issue the following command while you are in lib folder.

It'll display the non encrypted string and encrypted string on screen, if there are space then use """ as shown above to escape them

-U=admin,-P=password,-S=Svartalfheim,-A=Vision,-SEC_FILE="C:\Temp\ExpSecFile.txt",-DELIM=|,-exportsec,-ACCESS_GROUP="Vision Planner"#ID

Encrypted Text : F8TwryJheHDjY5YRn/EX6rZFWpTyO9Hin1KWGIq1kxeWjHj2eTkaSp36yXSvzRC8v49N4QGzSVyzds4yqbx+Qt45wAK9H5nSgT09CXJV3l2MqZlr5jQxSbBkJBXE9xGeiy964VYi5RJy8uBiLjizzNx6mEyNi1BRNqKROfTsHeyvMqJFny51Bd+p8XTCEZhe

Update the encrypted string and execute

I'm hoping to expand this to cover all the existing utilities :)

You can download Planning Client Utils from here

You can access the documentation here

NUMSys Web Edition + Essbase Security Viewer + Workspace Access Viewer

NUMSys is getting closer to the idea of security viewer.

The latest version of NUMSys comes with two new components.
  • Tree Viewer for groups.
  • Workspace Reports
Tree Viewer

Mike Henderson pitched this to me in a Network54 forum post and I had to struggle a bit to get this one working. (Mainly because I was not looking at the right spot, I would like to thank my Wife for pointing me to the right direction)

You can expand to view the child users/groups of a parent, also you can view their provisioning information.

You can do that by selecting a user/group

I've also provided an option to view the Indirect Access.

In the below example "Carol" don't have a direct access given to her. She inherits that from a group.

It is an easier way to look at "nested groups"

Workspace Reports

I've struggled a lot while documenting Workspace objects, the options available to you are open each folder and then look at the permissions. Same applies in case of documenting object types in Workspace.

You can collapse unwanted columns. A starting folder location can be specified to run the reports.

NUMSys now allows you to generate reports

  1. Find out all Object Types in Workspace (like FR books, FR reports, FR text object, et al)
  2. Find out all Unknown Object Types
  3. Generate a report for all repository object starting from a folder location. This comes in handy if you need full path, created, modified date of objects under a folder

You can expand and collapse folder to view/hide underlying objects.

All Object Types Report

This report will provide you a list of all object types in workspace and their count. It is easier to now look at "How many FR reports I've have?"
Unknown Object Types Report

This report will provide you a list of all unknown objects, their path and count.
Repository Object Report

This report will provide you with a list of all repository object under a folder. You can also use filters on object types.

Access Control Reports

You can export the object access from Workspace.

The generated report will contain the type of the file, it's path and access information.

You can also use filters to restrict user/group pull.

The installer issue with Linux machines is also fixed with the new version.

You can download NUMSys from here

Documentation can be found here

Bulk upload valid intersections in PBCS

Well with all good things (and bad) there is a beginning and for this one I can point out where it started :)

There was this question on Network 54 which was a definite fit for Valid Intersections. However the unfortunate thing about it is "Valid Intersection is not available on on-prem", "No automated way to bulk import Valid Intersection".

1. Not being available - can't do anything about it.
2. Where it is available, there is no good way to bulk load them.

So how can I bulk load valid intersections in PBCS.

Maybe I play around with LCM a lot and I love doing what I do. (You need to be careful with what you are doing, all I'm saying is that).

Sharpen your command lines skills and get the below given free utilities, oh you are going to love them.

Bulk Rename Utility

This is a file renamer tool. Why you need a file renamer - good question. If you look at the LCM files, you'll see that

Valid intersections (not only those, all artifacts), when exported will carry the same file name (.xml) and you need a file renamer to rename and load the new artifact.

You can download Bulk Rename Utility from here. There is a command line version and a UI version, for this exorcise we are using the command line version.

The second one is Find and Replace tool (fnr)

Well, there are other tools out there which can do it (like Notepad++, open all artifacts and do a find and replace on all open documents), what I like about this one is it can do recursive find and replace, comes with a UI and command line. You can download fnr from here.

Step 2

Perform a LCM of one valid intersection, the one which you can perform find and replace and create a new one.

Step 3

I've created a bat file to perform, download of the snaphot, perform, find and replace, upload the file and do execute LCM.

This is all for Valid Intersections and PBCS, however can be replicated for any artifacts. Also same can be used in on-prem (You'll have to use Utility.bat for Export and Import).

Here is the bat file for doing all the above mentioned process.
I've added comments in the script itself to explain each step.

Run the batch and you'll see that it did replace the files.

There you go, bulk upload of Valid Intersections :)

Views discussed in this blog are my personal views.
They do not represent the view of organizations, businesses or institutions that I'm part of.