Planning Client Utilities 1

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

To

Update PlanningClientUtils.bat to reflect your environment details.

The first version of Client Utils allows you to

1. Import Security
2. Export Security

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