Hyperion planning error "Network error[%s] cannot receive the data"

I have chosen this topic as this is very common error but confusing for users


Issue: 
While refreshing application from Hyperion planning to Essbase we receive error "Network error[%s] cannot receive the data"
Essbase application is stopped and an exception *.xcp log file is generated under the application's directory ...essbase0\app\<APPLICATION_NAME>

Cause:

Even though error message shows as network issue it is not related to network but Essbase application db is corrupted.


Resolution:

1. Take application data backup to make sure data is not lost in application recovery process.
2. Stop application.
3. Navigate to Essbase application database folder (D:\Oracle\Middleware\user_projects\epmsystem1\EssbaseServer\essbaseserver1\app\<app_name>\<db_name>) and locate <db_name>.db (database file) and  <db_name>.dbb (database backup) file.
4. Take the backup of  <db_name>.db file and delete it.
5. Rename  <db_name>.dbb file to  <db_name>.db file.
6. Start Application
7. Now try refreshing application again from Hyperion Planning.

Import security certificates to java key store and Oracle e wallet

I have been involved a lot in Hyperion admin activities from last 2 years and today I am writing a small note on following topics:

•  How to create keystore to use ssl certificates for secure connections 

•  How to request certificates from verified providers

•  How to import certificates in keystore

•  Converting Keystore to Wallet

How to create keystore:

In simple words keystore is like a key repository which is use to store public and private keys. In order to request for a authorized trusted certificated for your application you need to first create a keystore. 

Consider server name where application is hosted is test_server.net then you can use below command to create keystore:

D:\<JAVA installation path>\jdk1.7.0_181\bin\keytool -genkeypair -alias <alias_name> -keyalg RSA -keysize 2048 -sigalg SHA256withRSA -validity 365 -keypass <password> -keystore <name_of_keystore> -storepass password -dname "CN=test_server.net,C=<country_detail>,O=<Organization_detail>,OU=<organzation_unit>,L=<location>,ST=<state_code>"

In the above command replace the values as per your environment and it will generate a keystore in current directory.

Note: Keep this keystore safe as the requested certificate can only be imported in the keystore from which CSR will be generated.

How to request for Certificate:

After creating keystore now its time to generate a CSR (certificate sign request) to request signed certificate from certificate trust authorities like symantec, godady, rapidssl etc.

Please run below command to generate CSR:

D:\<JAVA installation path>\jdk1.7.0_181\bin\keytool -certreq -alias <alias you mentioned in previous step>-sigalg SHA256withRSA -keystore <keystore name> -file <any_name>.csr

Above command will generate a .csr file which you can share with certificate sign authority to get a certificate issue for your application/server.

How to import certificate in keystore:

Once you receive signed certificate from certificate sign authority now its time to import it in the keystore generated by you in the first step:

Keep certificate file in the same directory as keystore and use below command to generate import certificate:

D:\<JAVA installation path>\jdk1.7.0_181\bin\keytool -import -alias <alias you mentioned in first step> -file <signed certificate file name>.cer -keystore <keystore name>

System will ask you for the keystore password so provide the password which you use while creating keystore

NOTE: You may need to import chain and root level certificates in the keystore as per your company security policies to authenticate at different levels in network and you can do the same by following above process.

Converting Keystore to Wallet:

Many applications like some oracle application use wallets to authenticate client request.

To generate wallet from jave key store use following commands:

Command to generate wallet after setting JAVA_HOME

> set JAVA_HOME=D:\Oracle\Middleware\jdk1.7.0_181

> D:\Oracle\Middleware\oracle_common\bin\orapki wallet create -wallet D:\Oracle\SSL\ -pwd <wallet password>

Importing java key store content to wallet:

>D:\Oracle\Middleware\oracle_common\bin\orapki wallet jks_to_pkcs12 -wallet D:\Oracle\SSL\ -pwd <wallet password> -keystore D:\Oracle\SSL\<java keystore name> -jkspwd <java keystore password>

Generate wallet sso file for automatic authentication:

>D:\Oracle\Middleware\oracle_common\bin\orapki wallet create -wallet D:\Oracle\SSL\ -auto_login

Enter wallet password when system will prompt for password and above command will generate a .sso file.

Now you are all set to copy java keystore and wallet wherever require to configure application

Please share your valuable feedback as it will help me improving my future posts.

Deploying Weblogic Admin service as a Windows service

Best way to keep the Weblogic admin services up and running on windows server is by deploying it as a windows service. 

Need: If Weblogic is installed on windows server (in my case it is under D:\Oracle\Middleware\user_projects\domains\EPMSystem\bin for Hyperion installation) Admin services needs to be started using startWebLogic.cmd located under "bin" folder. To keep the services up and running command prompt which is used to start the service should be always remain open on the server which is not practically always possible. If the same service is deployed as windows service it can be well managed through windows service manager.


Steps to be perform:

• Navigate to path D:\Oracle\Middleware\wlserver_10.3\server\bin
• Create a file InstallAdminService.cmd and copy below content in the file (Update values as per environment):

***************************************

SETLOCAL

SET DOMAIN_NAME=EPMSystem

SET USERDOMAIN_HOME=D:\Oracle\Middleware\user_projects\domains\EPMSystem

SET SERVER_NAME=AdminServer

set WLS_USER=EPM_ADMIN

set WLS_PW=*******

SET PRODUCTION_MODE=true

SET JAVA_OPTIONS=-Dweblogic.Stdout="D:\Oracle\Middleware\user_projects\domains\EPMSystem\stdout.txt" -Dweblogic.Stderr="D:\Oracle\Middleware\user_projects\domains\EPMSystem\stderr.txt"

SET MEM_ARGS=-Xms1024m -Xmx1024m

CALL "D:\Oracle\Middleware\wlserver_10.3\server\bin\installSvc.cmd"

ENDLOCAL

***************************************

• Navigate to D:\Oracle\Middleware\user_projects\domains\EPMSystem\bin and create a copy of setDomainEnv.cmd, rename the copied file as setSvcDomainEnv.cmd

• Open setSvcDomainEnv.cmd in a text editor Search for PROTOCOL_HANDLERS and comment the 3 lines with PROTOCOL_HANDLERS 

Before modification: 

set PROTOCOL_HANDLERS=oracle.mds.net.protocol

set JAVA_PROPERTIES=%JAVA_PROPERTIES% -DEPM_ORACLE_HOME=%EPM_ORACLE_HOME% -DEPM_ORACLE_INSTANCE=%EPM_ORACLE_INSTANCE%

set PROTOCOL_HANDLERS=%PROTOCOL_HANDLERS%;oracle.fabric.common.classloaderurl.handler;oracle.fabric.common.uddiurl.handler;oracle.bpm.io.fs.protocol

set PROTOCOL_HANDLERS=%PROTOCOL_HANDLERS:;="|"%

            After modification:

@REM set PROTOCOL_HANDLERS=oracle.mds.net.protocol

set JAVA_PROPERTIES=%JAVA_PROPERTIES% -DEPM_ORACLE_HOME=%EPM_ORACLE_HOME% -DEPM_ORACLE_INSTANCE=%EPM_ORACLE_INSTANCE%

@REM set PROTOCOL_HANDLERS=%PROTOCOL_HANDLERS%;oracle.fabric.common.classloaderurl.handler;oracle.fabric.common.uddiurl.handler;oracle.bpm.io.fs.protocol

@REM set PROTOCOL_HANDLERS=%PROTOCOL_HANDLERS:;="|"%

• Remove the following settings in the last ‘set EXTRA_JAVA_PROPERTIES’ in bold below -Djava.protocol.handler.pkgs=%PROTOCOL_HANDLERS% 

Before: 

set EXTRA_JAVA_PROPERTIES=-Dcommon.components.home=%COMMON_COMPONENTS_HOME% -Djrf.version=11.1.1 -Dorg.apache.commons.logging.Log=org.apache.commons.logging.impl.Jdk14Logger -Ddomain.home=%DOMAIN_HOME% -Djrockit.optfile=%COMMON_COMPONENTS_HOME%\modules\oracle.jrf_11.1.1\jrocket_optfile.txt -Doracle.server.config.dir=%ORACLE_DOMAIN_CONFIG_DIR%\servers\%SERVER_NAME% -Doracle.domain.config.dir=%ORACLE_DOMAIN_CONFIG_DIR%  -Digf.arisidbeans.carmlloc=%ORACLE_DOMAIN_CONFIG_DIR%\carml  -Digf.arisidstack.home=%ORACLE_DOMAIN_CONFIG_DIR%\arisidprovider -Doracle.security.jps.config=%DOMAIN_HOME%\config\fmwconfig\jps-config.xml -Doracle.deployed.app.dir=%DOMAIN_HOME%\servers\%SERVER_NAME%\tmp\_WL_user -Doracle.deployed.app.ext=\- -Dweblogic.alternateTypesDirectory=%ALT_TYPES_DIR% -Djava.protocol.handler.pkgs=%PROTOCOL_HANDLERS%  %WLS_JDBC_REMOTE_ENABLED% %EXTRA_JAVA_PROPERTIES%

After:

set EXTRA_JAVA_PROPERTIES=-Dcommon.components.home=%COMMON_COMPONENTS_HOME% -Djrf.version=11.1.1 -Dorg.apache.commons.logging.Log=org.apache.commons.logging.impl.Jdk14Logger -Ddomain.home=%DOMAIN_HOME% -Djrockit.optfile=%COMMON_COMPONENTS_HOME%\modules\oracle.jrf_11.1.1\jrocket_optfile.txt -Doracle.server.config.dir=%ORACLE_DOMAIN_CONFIG_DIR%\servers\%SERVER_NAME% -Doracle.domain.config.dir=%ORACLE_DOMAIN_CONFIG_DIR%  -Digf.arisidbeans.carmlloc=%ORACLE_DOMAIN_CONFIG_DIR%\carml  -Digf.arisidstack.home=%ORACLE_DOMAIN_CONFIG_DIR%\arisidprovider -Doracle.security.jps.config=%DOMAIN_HOME%\config\fmwconfig\jps-config.xml -Doracle.deployed.app.dir=%DOMAIN_HOME%\servers\%SERVER_NAME%\tmp\_WL_user -Doracle.deployed.app.ext=\- -Dweblogic.alternateTypesDirectory=%ALT_TYPES_DIR%  %WLS_JDBC_REMOTE_ENABLED% %EXTRA_JAVA_PROPERTIES%

• Save the changes to setSvcDomainEnv.cmd file
• Run InstallAdminService.cmd to create the windows service.

Error Connecting to Planning Application from Smartview After security changes

During my work for recent client I came across below issue multiple times so I thought of documenting the solution.

Issue Description : 

If you update a planning security for a user like adding additional role, removing a role, Adding or removing a group etc than user can face below issue while connecting to planning application through Smartview.

"Cannot open cube view. Essbase Error(1054060): Essbase failed to select application <Application Name> because <username@Directory> is not completely provisioned by Planning."

Issue Cause: Aforementioned issue caused because Essbase security is no longer in sync with Planning (Shared Services) after security changes in Shared Services for specific planning application have been made. (This is a product bug)

Issue Resolution:

To Resolve this issue you have to delete the user from Essbase and refresh the security from Planning to Essbase.

Please find detail steps below:

• Login to EAS console and open MAXL Script Editor

• Query user using below command

display user <username>;

• Delete user from Essbase using below command

drop user <username@provider> from security_file;

• Refresh security by logging into Planning application through Workspace

• Login to Smartview to confirm issue is resolved

Hyperion metadata backup automation

In my last post Hyperion Data Backup Automation I explained a method to perform data backup automation for Essbase\Planning applications.

In this post I am going to explain how to automate metadata backup of all Hyperion components (Example: Rule Files, Essbase Outline, Planning components like forms , business rules ,custom menus, Planning outline, calculation scripts etc ) other than data.

There can be multiple ways based on the component you want to take backup like planning dimension hierarchies can be backup using Outline Load utility. Similarly for forms you can use FormDefUtil and for security you can use Import &Export Security utility but the method I am demonstrating can be used to take backup of all or selective components using LCM command line utility.

LCM command line utility can be find under your epm instance bin folder like below:
D:\Oracle\Middleware\user_projects\epmsystem1\bin\Utility.bat

Before start with automation lets understand how manual LCM works by logging into Shared services. For demonstration I am taking an example of one Planning application as it also cover all Essbase components:

To export Planning\Essbase artifacts for backup or for migration purposes you need to login to Hyperion Shared services. Under Application group expand the Planning folder and click on application under it which you want to export.After selecting the application you can see multiple checkbox at right to select the artifacts you want to export. You can expand each category and select artifacts you want to export leaving data option unchecked as we don't want to export data using LCM.

After you make the selections you need to click on Export and provide the export folder name to begin exporting artifacts. All artifacts will be exported in the folder name you have provided under below path which is import_export folder you configured at the time of application configuration :

D:\Oracle\Middleware\user_projects\epmsystem1\import_export

Navigate to above path and open the application export folder. You will see two xml files under that folder named as Export.xml and Import.xml each file is having the list of components you have exported and respective path in the exported folder.

Automating above LCM export:

Setup a folder named as Automation where you will be writing a script to automate export. You need to perform a first time manual setup to perform that please follow below steps.

• Open a command prompt and execute below command

D:\Oracle\Middleware\user_projects\epmsystem1\bin\Utility.bat D:\Oracle\Middleware\user_projects\epmsystem1\import_export\<Exported folder>\Export.xml

Note: Modify the directory path in the above command as per your environment and replace <Exported Folder> with the folder name you have exported with LCM in the previous step.

• After entering above command system will ask you to provide username and password to perform the LCM export. In my case I have provided credentials for Admin user.

• As soon as you hit enter username and password provided by you will be embedded into below file. You have to react very quickly here and as soon as you hit enter you need to copy below Export.xml file from below path to Automation folder as once the export is completed by above script Export.xml will again be overwritten and you will loose encrypted credentials in the xml file.

D:\Oracle\Middleware\user_projects\epmsystem1\import_export\<Exported folder>\Export.xml

• The above export will export all artifacts same as manual LCM export through shared services and exported artifacts will be present in below folder which you can later use to import back through LCM.

D:\Oracle\Middleware\user_projects\epmsystem1\import_export\<Exported folder>\

• To double check you can now compare the two files to make sure credentials are present in the Export.xml which is copied over to Automation folder

Export.xml present in Automation folder which contains username and encrypted password:

Export.xml present in (D:\Oracle\Middleware\user_projects\epmsystem1\import_export\<Exported folder>\Export.xml

) after export is completed which does not contain credentials:

• Now rename the xml file to append application name in front of it in Automation folder which contains credentials and in the next step you will create a batch script to automate the export which you just performed manually. Renaming of file is not a mandate but if you are automating backup for multiple application in your environment you will be require to repeat all the above steps for each application and all the xml files will be residing in Automation folder so you need to give unique application based name to each file. 

• Next create a batch file and embedded below command in the batch.

copy D:\Automation\XML\Export_<application_name>.xml D:\Oracle\Middleware\user_projects\epmsystem1\import_export\<application folder>\Export.xml

D:\Oracle\Middleware\user_projects\epmsystem1\bin\Utility.bat D:\Oracle\Middleware\user_projects\epmsystem1\import_export\<application folder>\Export.xml

Note: In the above script you are copying xml file from Automation folder to application folder under import_export directory and then calling LCM utility to launch the export. By running this script you don't need to provide credentials while running the utility as credentials are embedded in the xml file which you are copying to application folder.

• Now just schedule the above batch using task scheduler as per your backup frequency and you are all set.

Hyperion application data backup automation

After thinking for many months finally I am writing my first Blog.

Blogs return by technology consultants had helped me a lot in past couple of years during my work and I always feel that I am only taking benefit of this vast technical community without contributing my bit hence I wanted to contribute by helping others with my gained knowledge while working for my clients.


In my case Essbase is installed in windows server so I have used batch (.bat) file and task scheduler for automation but you can replicate the same steps in shell script (.sh)  and using Unix cron job if Essbase is installed on Linux/Unix server in your case.

Please find below steps I performed to automate the daily data backup for Hyperion applications (Essbase/Planning) in one of my client environment.

• Create below folder structure to keep a backup history for one week. In my case I am taking data backup on daily basis and maintaining it for one week. You can decide your own data backup strategy and accordingly setup folder structure.

• If you want to take a full data backup you can simply use below maxl command to extract the entire application data or level0 data as per your need:

********************************************************

          /* Exporting Level 0 application data  */

login '<username>' '<password>' on '<server name>'; 

alter application <application name> load database <db name>;

export database sample.basic level0 data to data_file 'D:\<data_file_path>\<data_file_name>';

alter application <application name> unload database <db name>;

*********************************************************

*********************************************************

 /* Exporting all application data  */

login '<username>' '<password>' on '<server name>'; 

alter application <application name> load database <db name>;

export database sample.basic all data to data_file 'D:\<data_file_path>\<data_file_name>';

alter application <application name> unload database <db name>;

*********************************************************

• In my case I only need current year data backup for which I have setup below calculation script:

*********************************************************

SET DATAEXPORTOPTIONS

{

DataExportLevel "ALL";

DataExportDynamicCalc OFF;

/*DataExportDecimal n;

DataExportPrecision n; 

DataExportColFormat ON;*/

DataExportColHeader "Period";  

DataExportDimHeader OFF;

DataExportOverwriteFile ON;

DataExportRelationalFile ON;

};

FIX ("<current year>");

DATAEXPORT "File" "|" "D:\<exported data file path>\<exported_data_file_name>.txt" "NULL";

ENDFIX;

*********************************************************

• Next you need to create a small maxl script to call above calculation script for exporting a portion of data as per your need:

*********************************************************

/* Exporting current year application data  */

spool on to 'F:\<log path>\<log_name>.txt';

login '<username>' '<password>' on '<server name>'; 

alter application <application name> load database <db name>;

execute calculation <application name>.<db name>.<calculation script>;

alter application <application name> unload database <db name>;

spool off;

logout;

*********************************************************

• Now its time for fun part lets automate daily backups. Create a batch file named ApplicationDataBkp.bat in the folder where you have setup the folder structure for each day of week and above maxl to extract the data.

• Open ApplicationDataBkp.bat in text editor and embedded below code I am using "Sample" as application name and "Basic" as db name in the below example:

******************************************************************************

SET Drive=D:

REM SET Gen=%2

SET HWeek=%DATE:~0,3%

if %HWeek% == Sun Set Gen=Sun

if %HWeek% == Mon Set Gen=Mon

if %HWeek% == Tue Set Gen=Tue

if %HWeek% == Wed Set Gen=Wed

if %HWeek% == Thu Set Gen=Thu

if %HWeek% == Fri Set Gen=Fri

if %HWeek% == Sat Set Gen=Sat

REM Calling MXL to export the data from Sample application

call D:\Oracle\Middleware\user_projects\epmsystem1\EssbaseServer\essbaseserver1\bin\StartMaxl.bat D:\<backup folder path>\Export.mxl <username> <password> <server_name>;

REM Zipping IBT Data

@ECHO ON

CD /D "<path for 7 zip client installed>"

7z.exe a "D:\<backup folder path>\Processing\<Application Name>\<Backup_zip_file_name>.zip" "D:\<backup folder path>\Processing\<application name>\*.txt"

REM Copying file into respective day folder

move D:\<backup folder path>\Processing\<application name>\*.zip D:\<backup folder path>\%Gen%\<application name>\

REM removing text file after zip file transfer

del D:\<backup folder path>\Processing\<application name>\*.txt

*********************************************************************************

In the above example I have consider Storage drive name as "D" . You need to replace backup folder path , application name and db name as per your environment.


Lets understand what I am trying to achieve in the above script:

1. First of all I am defining variables based on the day when script run to select the correct folder name during file copy.
2. Then I am calling the maxl script which I have created in the prior step to export the data.
3. Data exported by the maxl will be initially stored in the "processing" folder under specific "application name" folder which will be huge in size (in my case 17 files each of 2 GB so total 34 GB data for current year)
4. Next for space management I am zipping the data files before storing them to respective day folder.
5. Once files are zipped I am copying them into respective day folder and clearing the processing folder by deleting the unzipped files.

• At last you can create a task scheduler job and call below script to schedule for daily backup. 

"D:\<backup folder path>\ApplicationDataBkp.bat" >D:\<backup folder path>\logs\Backup.log



Please do share your feedback about this post that will help me in improving my upcoming posts