Where is my import/export security utility???

When I first started working on Hyperion Planning, clients and consultants would call the hotline and ask how they could see their existing security because back in the 1.0 days it was not available. So I dove into the database and wrote a Microsoft Access database that enabled consultants and clients the ability to see their security. We distributed the database and instructions if people asked nicely or loudly until the feature was written into the tool.

The Oracle EPBCS/FCCS platform does not come with a nice way of exporting and importing object security the way the on-prem version of Hyperion Planning had with its command line utilities. I decided to take a stab at hopefully making it easier.

EXPORTING SECURITY

This entry will focus on exporting the current security of an application and formatting it into a csv file so you can easily edit the security or view its contents.

I am going to focus on group security since it is a best practice to apply security at a group level. You can follow this process for users as well.

The first step is to extract the current application security using the migration component of EPBCS. You can use a complete application extract or just extract the security. Below I am just extracting the group security.

Once the migration is executed download it to your machine. Unzip the file and you will find your currently assigned security in super fun XML files for each group.

Here is what it looks like in XML.

Its pretty simple XML but not great to edit. Now that you have the files I have written a Powershell script that will convert all of the files in a directory to a combined csv file. By no means am I a Powershell master but I like it because its extremely quick, and handles XML, JSON, and CSV formats extremely well. Generally you will find it installed on most new servers or clients. Here is some documentation to enable it on windows.

It can also run on your mac or linux environment.

You can download the script here. Once you download it place it in a directory where you plan to execute it from.

The script accepts two parameters. The first is the directory where the XML files are stored and the second is the name of the file you want to create. If you don’t put a path the file will be saved in the same directory as where you execute the script. The parameters should be separated with a space.

Once you launch the shell navigate to the directory you have saved the script in.

Here is the command :

./sec2csv.ps1 “V:\POWERSHELL\BLOG\HP-TOYS\resource\Security\Access Permissions\Groups\” security.csv

Place quotes around the directory if you have spaces in it.

It will look like this in the shell.

Once the script runs it will generate your export.

It has now combined all of your group files into one csv file that will look like this.

The information in this file will be very similar to what was required for the import and export utility in planning. The definition of these fields can be found here. The only one that is really different is the IsUser field. For group security this will always be N.

CONCLUSION

I hope you find this first part of this conversion helpful. I am testing the CSV to XML script now, it has been working great and I should have that up soon. I hope this helps you manage security in the cloud!!

EPBCS – Improved Smart List Lookup in Calculations

Over the last month I had to customize almost every feature in the EPBCS workforce planning application for a client. I have much to write about that experience but this one is my favorite. Do you want your Smart Lists to be dynamic but do not want to write long if/then statements for every possible value in the lists? Well read on…..

When I first saw the feature to build a Smart List from metadata I started to get excited about the potential to use that in a calculation. After going through the Workforce app in detail I have seen the light. If you are not familiar with that module yet it basically has three plan types. The first two BSO cubes are for the plan and another one is for the rates. When you create a TBH it performs a lookup in the rate cube to get default values. Here is a simplified example on how the functionality works so you can use it in your applications as well.

I built a 2 database application one called FINPLAN and the other called RATES.

Here is the dimensionality for the two plan types.

FINPLAN

RATES

 

 

 

The idea is that a user can enter in information in the Finplan cube and the system will do a look up in the rate cube based on a region they have selected in the Smart List. Here is a quick preview of the form.

 

Creating the Smart List

Once the dimensionality is set up correctly you need to set up a Smart List that is based on your application metadata. I created a Smart List named REGIONS and configured it to point to the level 0 members of the regions dimension.

This creates a dynamic list based on the application metadata. Notice on the Entries tab the id for the members in the list. If you know the backend of planning this is the OBJECT_ID from the HSP_OBJECT table, planning is going to use this identifier in your calc scripts to identify the member in Essbase.

After the list is created I assign it to the account Opportunity_Region in the FINPLAN Cube.

RATES

I then built a webform against the RATES database to store the rates an the No Entity member and at the Regions.

I am just storing a monthly rate by region in this cube.

IN ACTION

Here is a quick view of it working:

CALCULATION

This is where the magic happens. Basically we want to pull a value from the rate cube using the value assigned to the Smart List in the planning cube.

Here is the calculation:

Not as pretty version :

FIX (FY18,Plan,Working,USD,”No Entity”,@RELATIVE(“OPP”,0))
/*Define a variable to assign the Smart List value to*/
VAR SLVal;

/*Opportunity Overhead cost, lookup in Rate cube*/
“OVERHEAD_COST”(
/*Assign Smart List Value*/
SLVal=”OPP_REGION”->”BegBalance”;
IF( NOT (@ISMBR (“BegBalance”) ) ) /*Only Run on Months*/
IF( (SLVAL <> #MISSING) AND (“VOLUME”->”BegBalance” <>#MISSING)) /*Check if there is a Smart List Value and a volume*/
@XREF(_RATES_,”OVERHEAD_RATE”,”No Entity”,@CONCATENATE(“HSP_ID_”, @Name(@hspnumtostring(SLVal)))); /*apply Rate cube value*/
ENDIF
ENDIF

)

ENDFIX

THE MAGIC

If you look closer at the Xref formula you will notice@CONCATENATE(“HSP_ID_”, @Name(@hspnumtostring(SLVal))))

Lets break this down.

  • The SLVal was assigned from the Smart List value and it is populated with the OBJECT_ID of the member in PBCS.
  • The CDF hspnumtostring converts the numeric id to a string
  • The @Name function really makes sure its a string . Probably redundant.
  • Then its concatenated with “HSP_ID_”

Using the NY member as a value it would create a member that looks like this.

HSP_ID_50097

Behind the scenes Planning then can use that definition to translate it into a member that Essbase can read. I think with an alias table in Essbase.

Pretty cool!!

NOTES

The Xref formula uses txt values but if you need to convert this into an Essbase member then wrap the formula around an @MEMBER like this:

@MEMBER(@CONCATENATE(“HSP_ID_”, @Name(@hspnumtostring(SLVal)))))

 

You will only be able to use this in calculations and not in fix statements since you need the data value of the Smart List to drive the formula. I tried to get it to work in a member formula and was not successful, but I only spent a few minutes on it.

CLOSING

This is a pretty nice enhancement that is a key to understand if you need to troubleshoot or enhance Workforce in EPBCS. Plus you can utilize it in your own applications to make your scripts and applications more dynamic and focused.

Here is the LCM if you need it.