Planning Client Utilities 1


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
From

To

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

java -jar PlanningClientUtils.jar -encrypt="-U=admin,-P=password,-S=Svartalfheim,-A=Vision,-SEC_FILE="""C:TempExpSecFile.txt""",-DELIM=|,-importsec

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.

ACCESS_USER=Fr*,ACCESS_GROUP=Visi*

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

java -jar PlanningClientUtils.jar -encrypt="-U=admin,-P=password,-S=Svartalfheim,-A=Vision,-SEC_FILE="""C:TempExpSecFile.txt""",-DELIM=|,-exportsec,-ACCESS_GROUP="""Vision Planner"""#ID"

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:TempExpSecFile.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


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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

One thought on “Planning Client Utilities

  • Leo Herbert

    Hi Celvin,

    Have you tried to create the security filters for a single user using an utility. I have developed a utility using a curl type in Excel VBA, which does the same task of planning web create security filters for one single user. But in the 11.1.2.4 Oracle is using different architecture and my utility no longer works.

    Thanks
    Leo