Essbase Quick Reference

Essbase: Extended Spreadsheet - a multi-dimensional OLAP system.

Essbase database structure:
   Application
       Database
           Outline
Outline is created when database is created. Includes dimensions, members, aliases, properties, types, consolidation, and mathematical operators.

Following files are created when a database is created.
.db, .dbb, .otl, .esm, .tct

Following files are created when an application is created.
.instance, .app, .log


Intelligent Calculation: With this, Essbas calculates only update members and their dependent parents (dirty blocks) making calculation efficient. After full calculation, all data blocks are marked clean. We can turn the Intelligent Calculation feature on or off through calculation script or essbase.cfg file.

Keyword - UPDATECALC

Syntax (calc script) - SET UPDATECALC ON/OFF;
Syntax (essbase.cfg file) - UPDATECALC TRUE/FALSE

SET UPDATECALC ON;      Calculates only dirty blocks
SET UPDATE CALC OFF;    Calculates all data blocks


Essbase Security File - essbase.sec
Location - Arborpath/bin directory - eg: (\\EssbaseServer\d$\Hyperion\Essbase\bin)

Information stored in secfile:

• Filter Access
• Application Access type
• Application and database properties, substitution variables
• CalcScript access

essbase.sec file contents are encrypted. We can export it to a .TXT file and view the contents.
MAXL to export "essbase.sec" file:

• export security_file to data_file "e:\khoonks\essbase_security_file.txt"

ExportSecurity ImportSecurity in the Hyperion Environment

Nikon Coolpix L810 16MP 26X Optical Zoom Black Digital Camera (Google Affiliate Ad)

In Hyperion Planning ExportSecurity and ImportSecurity are two very important utilities that can be used to give access to users.

They reside in the bin folder, therefore, in terms of Hyperion 11.1.2, it should be somewhere around D:\Oracle\Middleware\user_projects\epmsystem1\planning\planning1 (this is the regular path and one that we have in our environment, it may be a little different depending on how it is installed in your work environment). 

ExportSecurity exports the security features and creates a SECFILE.TXT file inside the BIN folder and ImportSecurity imports the security that is present in the SECFILE.TXT file to the specified application. For example, if you have a user called SAM whose access you want to export and import to user HARRY, then you need to use the ExportSecurity command with SAM and then use the ImportSecurity with user HARRY.

Please look at the syntax below. 

In order to run the command, please open the COMMAND PROMPT and go to the BIN folder.

ExportSecurity.cmd /A=applicationname, /U=adminusername, /S_USER=SAM

The above command creates a SECFILE.TXT inside the BIN folder. What you need to do here is open the file, change the username from SAM to HARRY, and save the file. 

If you want to export an application's overall security file, the syntax would be:

ExportSecurity.cmd /A=applicationname, /U=adminusername

This command exports the application overall security file and output a secfile.txt.

A batch script is of great significance if you have a lot of applications.
In my case, I have a batch file which I have named "KHOONKSSEC.BAT"
______________
REM Export security for the KHOONKS planning application


CALL D:\oracle\Middleware\user_projects\epmsystem1\Planning\planning1\ExportSecurity.cmd /A=KHOONKS,/U=ADMIN

We can specify the output file name/location by using the parameter "TO_FILE"
In the following example,
username is nirmal, application is khoonks, and we would like to export the resulting security file to a file named "mysec.txt" in E:\my_folder.
So the syntax would be something like:
ExportSecurity.cmd /A=khoonks,/U=nirmal,/TO_FILE=E:\\my_folder\\mysec

Moreover, we can also use the (-f) option to encrypt the password while doing the security export. In this case the syntax of a batch script to call the ExportSecurity.cmd would look something like:

call D:\oracle\Middleware\user_projects\epmsystem1\Planning\planning1\ExportSecurity.cmd -f:D:\oracle\Middleware\user_projects\epmsystem1\Planning\planning1\passwordFile /A=appname,/U=ADMIN,/TO_FILE=E:\\samplefolder\\subfolder\\mysec_secfile

The above script will create a file called mysec_secfile.txt inside the "subfolder" folder in the specified path. 

NOTE: In the above example, there is a space before "-f" and after "passwordFile"
The utility is very sensitive in terms of case and spaces. Therefore, make sure they are correct. 
_________________

Now that I have a batch file, I do not have to go to the BIN folder and type the syntax each time I want to export security, I can simply run the Batch file. Now you can enter the ImportSecurity command.

ImportSecurity.cmd “appname, username, password”

Security can be removed from Planning application by running the following ImportSecurity command -
ImportSecurity.cmd "MYAPP,ADMIN,,,,SL_CLEARALL"  - where MYAPP is the name of the application and ADMIN is the username. 
NOTE - make sure to have the four "commas"