Get Text IDs from PBCS

I always had issue with the way OutLineLoad extracts data, it can called as a sort of one dimensional (or two) export.

Only one dimension in row and one dimension in column. Now you might say why use that when you've DATAEXPORT. I agree and at the same time disagree with you.

I agree DATAEXPORT can work if you are extracting numbers
I disagree when you are using DATAEXPORT for text and Smart List, because that is just going to give you numbers.

Well, now if you are the smart one (which I know you are and that's why you asked me to use DATAEXPORT) you can load the extracted data to a table, perform a lookup against HSP_TEXT_CELL_VALUE table (ID column) and return the text.

Yes that is perfectly possible in an On-Prem world, what about PBCS? :)

You cannot see the backend table, so there is no possibility of looking at HSP_TEXT_CELL_VALUE table.

Back to the Future (hmm no, Forward to the Future)

Let's take a look at the Migration options, shall we.

Oh, what do we see here, oooh Text Values, Cell Texts (lot of other stuff, which is not important right now)

Perform a "Migration" of the artifact and let's look at the file.

It is an XML file (pity!!!) with the text value and id, this is the same id that gets stored in Essbase.

Now let's create a DATAEXPORT script (now you are going to ask me what file path will I give in PBCS, hold on, dear reader :))

What do you see?!!!! (It took a long time to know that I could use /u03/lcm (I was using /u03/lcm/import/<LCMName>, thanks to Chris Rothermel and my older brother from completely different set of parents, Cameron Lackpour for sharing this wisdom on a N54 post)

Well now I know and you know that
/u03/lcm is the PBCS Inbox/Outbox location
/u03/lcm/import is the Migration aka LCM location
/u03/inbox/data is the Integrations aka Data Management aka FDMEE lite Inbox location

There we are I got my extract in PBCS inbox location.

File before conversion

Steps that you should perform
EPMAutomate to run the rule
EPMAutomate to exportsnapshot
EPMAutomate to download both files.
Extract the XML file from
Use the below given groovy script (Well I'm still learning, so you might find more faster, less number of line code to do the same job) to convert Essbase output to Planning Text output.

File after conversion

Cameron is going to kill me for teasing him, with all these new stuff that I'm talking about. Older brother forgive me ;)

There you go a faster way to get Text data from Planning. Now I'm lazy enough to write the entire code, back to sleep now.

Configuring PBCS to Federate with Microsoft Azure Active Directory Base version

Some you might know that Oracle PBCS now supports Single Sign-On.

If not here you go,
Oracle Planning and Budgeting Cloud November Update RCD
Administering Oracle Cloud Identity Management
Configuring Active Directory Federation Services 3.0 as an Identity Provider with Oracle Cloud as Service Provider

I was at a customer who has Office 365 and with that they get Azure Active Directory and I had to configure Single Sign using Azure Base version.

I wrote an article on that for OTN and here is the link

Configuring Azure AD Base Version as an Identity Provider with Oracle Planning and Budgeting Cloud Service

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

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.