Hyperion Planning Error “The entered member formula is invalid. Click the Details button for details” When Validating a Member Formula


You can validate a member formula in Essbase and the same is not validating in Planning.

The entered member formula is invalid. Click the Details button for details.
Error compiling formula for [ ] (line 1): unknown member name [“A2”] in function [operator @X]

If you will try to refresh outline, you will get the below error:

com.hyperion.planning.olap.HspVerifyOutlineException: Verify Outline failed with the following errors:
Error [1200497] detected in member formula for member “E1000”.

This is due to The member which is throwing error and is used in the formula is not created for all plan types. For example, if below is the member formula for Entity member E1000, Member E1000 is enabled for Plan type 1,2,3 where as account member A2 is just enabled for Plan type 1. Here if you validate this formula in Essbase for Plan type 1, it will validate, where as in Planning it will give an error.

E1000= A2->E1000;

The following 2 workarounds can be used to resolve the problem

I. Enable the member for all Plan Types in Planning
1.Log into Hyperion Planning,
2.Search for the member which is giving error in the logs,
3.Select the member, edit it,
4.Enable it for the other plan types ( In our Ex. If E1000 is for Plan Type 1,2 and 3; enable A2 for the same Plan Types),
5.Save the Changes,
6.Go to Administration->Dimension,
7.Refresh the Database,
8.Try Validating the formula again.

II. Write a Business Rule instead of a member formula. In this example create a BR and associate it with Plan Type 1.

Thanks,
~KKT~

Advertisements

Essbase Server (Agent) Hangs on Unix Systems


The Essbase server (agent) periodically hangs requiring a restart of Essbase. All users, including the Essbase admin user, are unable to connect from any client tool, MaxL, ESSCMD, Excel Add-in, etc.

This is due to Port conflict with other applications/utilities. By default, Essbase uses Ports 32768-33767 to start each application.

To confirm a port conflict on the server, on the next occurrence of a hang of the Essbase agent, do the following:
1. Unload the Essbase agent (use kill -15 to kill all ESSSVR processes, then the ESSBASE process. If the -15 does not work, you will need to use kill -9)
2. Run command:
netstat -na > netstat.txt
3. Edit the netstat.txt file and look to see if there are ports ESTABLISHED or LISTENING in the 32768 through 33768 range. These are the default ports used by Essbase to run each Essbase application.
4. If any port in this range is ESTABLISHED or LISTENING:
Essbase v9.3.x/v11.1.1.x
Add the following 2 lines to the Essbase.cfg file, confirming the ports you assign are not used by another utility/application:
SERVERPORTBEGIN 42000
SERVERPORTEND 43000
Essbase v11.1.2.x
If using OPMN to start Essbase, re-run the configuration tool to set the Port Range for Start Port and End Port. The configuration tool will update the ports in the opmn.xml and essbase.cfg files.
5.Start Essbase.
Thanks,
~KKT~

Smartview With Excel 2010 Crashes When Trying To Save A File Without Doing a Refresh


Excel 2010 terminates abnormally when saving a workbook; however, Excel saves the workbook properly and does not crash if a Refresh is performed before saving.

The cause of this problem has been identified and verified in unpublished Bug 16998632 EXCEL CRASHING IF TRYING TO SAVE CERTAIN WORKBOOK

This issue is fixed in Smartview version 11.1.2.5.000, Patch Set Exception 17322826.

Download Information:
This patch is available via My Oracle Support.

1. Log into the My Oracle Support portal.
2. Choose the Patches & Updates tab.
3. Select Patch Name or Number from the drop-down box.
4. Perform a search for Patch:17322826 for your corresponding OS platform.
5. The patch file includes both installers and the readme.
Thanks,
~KKT~

How to Set Up Essbase Custom Defined Function (@JExtendedExportTo)


How to set up Essbase Custom Defined Function (@JExtendedExportTo).

1. Copy ExportCDF.jar file from old server to the location

MIDDLEWARE_HOME\user_projects\epmsystemPLAN11\EssbaseServer\essbaseserver1\java\udf

2. In the udf.policy file add below lines:

grant codeBase “file:..:/Oracle/Middleware/user_projects/epmsystemPLAN11/EssbaseServer/essbaseserver1/java/udf/ExportCDF.jar” {
permission java.security.AllPermission;};
3. Restart Essbase services.
Thanks,
~KKT~

Move a Relational Database for Hyperion Products from one Server to Another Server


How to move a Relational Database (SQL or Oracle) used by Hyperion Products, from one Server to another Server?

1. Export the tables to the new schema/rdbms.
2. Add the old user id/password to the new schema/rdbms.
3. Launch the configuration utility, choose the product you wish to move the RDBMS for, i.e. Shared Services or Essbase Administration Services, and then select the option to Configure Database. Enter the new server name, choose the ‘REUSE EXISTING DATABASE’, and then be sure to use the same user id and password that was used for the old db schema/rdbms.

* Migrate each rdbms at a time and test after each.
* You only have to reconfigure the db for that product. You DO NOT have to re-register the products back to Shared Services.
NOTE: If you are using Oracle RDBMS, make sure that the Oracle Database user IDs have the following roles and privileges:
– CREATE SESSION
– CREATE VIEW
– RESOURCE

Note: The Resource role should be granted as Default.
For example, ALTER USER oracle_schema_user DEFAULT ROLE “RESOURCE”.

Source: Oracle EPM 11.1.1.3.0 Installation Start Here, Page 48
http://download.oracle.com/docs/cd/E12825_01/epm.111/epm_install_start_here.pdf

Thanks,
~KKT~

Data Export From FDMEE to Excel Returns Blank Spreadsheet


Please note the below solution is applicable for version 11.1.2.3

When attempting to export data in the data load workbench to Excel within FDMEE the Excel is returned with a message saying the extension doesn’t match the file type and then then excel file was blank, except header.

This issue is caused by a software defect and it has been logged as bugs 18665000 and 18638754. ADF version that is installed with the EPM Patch 11.1.2.3.510 is not exporting the data to Excel correctly.

Workaround:
•Directly query the FDMEE Database table TDATASEG by LOADID. This is only available if direct access to the DB is available and should only be done by the DBA or FDMEE Admin.
•Run the Base Trial Balance Report in FDMEE to view the GL Data.

Patch 19586149 on My Oracle Support fixes this issue. To apply, please follow these steps:

1. copy p19586149_111171_Generic.zip to c:\Oracle\Middleware\oracle_common\OPatch

2. CD c:\Oracle\Middleware\oracle_common\OPatch

3. unzip p19586149_111171_Generic.zip

4. opatch apply c:\Oracle\Middleware\oracle_common\OPatch\19586149\oui -oh C:\Oracle\Middleware\oracle_common -jdk c:\Oracle\Middleware\jdk160_35

Thanks,
~KKT~

Login To Financial Reporting Studio When Using Planning and Budgeting Cloud Services (PBCS)


To access FR Studio the user name must be entered in IDENTITY_DOMAIN.USER_ID format, and Domains should be the Domain URL (without the port):

User name: IdentityDomian.UserID
Password:
Domain URL: https://DomainName.oraclecloud.com
This is different format than EPM Workspace login, which is:

Username:UserID
Password:
Domain:DomainName

Thanks,
~KKT~

How to automatically Housekeep or purge Informatica and DAC logs


Informatica:

After lot of hit and trails I discovered that if you want to create your own custom scheduling of this functionality then you can leverage the ‘pmrep truncatelog’ Command which can be used to truncate the session log entries from a command line interface as follows: –

Syntax:
Truncatelog uses the following command syntax:

truncatelog -t all | endtime -f folder name -w workflow name
Option Argument Name Argument Description

-t Required all | endtime Use “all” or enter a date/time. Use “all” to delete all the logs. Otherwise enter a date/time in this format: MM/DD/YYYY HH24:MI:SS pmrep deletes logs older than this time.

-f Optional folder name Specify a folder name to delete logs only for that folder. If you do not specify a folder, then you delete the logs in the repository.

-w Optional workflow name Specify a workflow name to delete logs for a workflow. If you specify a workflow name, you must specify the folder name. Otherwise pmrep generates an error.

To delete all the logs, use the “all” option. Otherwise specify an endtime in this format: -t MM/DD/YYYY HH24:MI:SS. When you specify the endtime, you delete all logs older than the date and time you specify.

Create your own Batch file with this command and your relevant arguments and this should give you the functionality that you require.

When the Tables have not been pruned for some time, issues can occur when the log data becomes too large. If you experience issues deleting this data, the following two suggestions might help: –

1) When communicating with a large repository the resilience time between Repository Service and Repository Database can be increased. Increase Repository Service Resilience Timeout, Heartbeat Interval, DatabaseConnectionTimeout and Limit on Resilience Timeout to 600. Allowing more time for the truncation to take place could help resolve your issue.

2) If the Repository is very large, try deleting the logs in stages (for a period of a few days or weeks).

Syntax:
truncatelog
-t <logs_truncated (all or up to end time in MM/DD/YYYY HH24:MI:SS format or as number of days before current date)> [-f <folder_name>] [-w <workflow_name>]

Example:

pmrep> truncatelog -t ‘3/20/2014 12:01:01’

DAC:

There is no documented ‘Command Line Command’ for the purging the DAC logs.

However, by editing the ‘StartClient.bat’ file in the ‘\\OracleBI\DAC’ directory and uncommenting the ‘JAVA’ line and commenting out the ‘JAVAW’ line, it is possible to see what commands are issued when the menu option in the DAC is executed.

I received this from my Test environment: –

Aug 15, 2014 2:35:08 PM com.siebel.etl.gui.data.StaticDatabaseCalls getDefnIds
INFO: SELECT
ROW_WID
FROM
W_ETL_DEFN_RUN
WHERE
STATUS = ‘Completed’
AND ROW_WID != ?
Aug 15, 2014 2:35:08 PM com.siebel.etl.gui.data.StaticDatabaseCalls getDefnIds
INFO: 8 run(s) found
Aug 15, 2014 2:35:08 PM com.siebel.etl.gui.data.StaticDatabaseCalls purgeRunDeta
ils
INFO: DELETE
FROM
W_ETL_RUN_SDTL
WHERE
RUN_STEP_WID IN
(SELECT ROW_WID
FROM W_ETL_RUN_STEP
WHERE RUN_WID IN (’85C0734149D33952665F29E26ABD0′, ‘DF645EF112E
8647C95A86018C97FE7C4′, ’54B048D2799F90DEBDD527D6BF651147’, ‘482A524D44211A1CF2E
C08573AFF7AB’, ‘D9CE165FE2AEA7AB13B586D11669F30’, ‘C55F996E574E2482CC72FBC6942BE
2E’, ‘F7EF2C3B3B47A476A69836E66A428957′, ’53FA118792AEC54AE78A47011F96F60′)
)
Aug 15, 2014 2:35:20 PM com.siebel.etl.gui.data.StaticDatabaseCalls purgeRunDetails
INFO: 29145 records deleted in W_ETL_RUN_SDTL
Aug 15, 2014 2:35:20 PM com.siebel.etl.gui.data.StaticDatabaseCalls purgeRunDetails
INFO: DELETE
FROM
W_ETL_RUN_AUDIT
WHERE
RUN_WID IN (’85C0734149D33952665F29E26ABD0’, ‘DF645EF112E8647C95A86018C9
7FE7C4′, ’54B048D2799F90DEBDD527D6BF651147’, ‘482A524D44211A1CF2EC08573AFF7AB’,
‘D9CE165FE2AEA7AB13B586D11669F30’, ‘C55F996E574E2482CC72FBC6942BE2E’, ‘F7EF2C3B3
B47A476A69836E66A428957′, ’53FA118792AEC54AE78A47011F96F60′)
OR RUN_STEP_WID IN
(SELECT ROW_WID
FROM W_ETL_RUN_STEP
WHERE RUN_WID IN (’85C0734149D33952665F29E26ABD0’, ‘DF645EF112E
8647C95A86018C97FE7C4′, ’54B048D2799F90DEBDD527D6BF651147’, ‘482A524D44211A1CF2E
C08573AFF7AB’, ‘D9CE165FE2AEA7AB13B586D11669F30’, ‘C55F996E574E2482CC72FBC6942BE
2E’, ‘F7EF2C3B3B47A476A69836E66A428957′, ’53FA118792AEC54AE78A47011F96F60′)
)
Aug 15, 2014 2:35:20 PM com.siebel.etl.gui.data.StaticDatabaseCalls purgeRunDetails
INFO: 2635 records deleted in W_ETL_RUN_AUDIT
Aug 15, 2014 2:35:20 PM com.siebel.etl.gui.data.StaticDatabaseCalls purgeRunDetails
INFO: DELETE
FROM
W_ETL_RUN_SUMM
WHERE
RUN_WID IN (’85C0734149D33952665F29E26ABD0’, ‘DF645EF112E8647C95A86018C9
7FE7C4′, ’54B048D2799F90DEBDD527D6BF651147’, ‘482A524D44211A1CF2EC08573AFF7AB’,
‘D9CE165FE2AEA7AB13B586D11669F30’, ‘C55F996E574E2482CC72FBC6942BE2E’, ‘F7EF2C3B3
B47A476A69836E66A428957′, ’53FA118792AEC54AE78A47011F96F60′)
Aug 15, 2014 2:35:20 PM com.siebel.etl.gui.data.StaticDatabaseCalls purgeRunDetails
INFO: 0 records deleted in W_ETL_RUN_SUMM
Aug 15, 2014 2:35:20 PM com.siebel.etl.gui.data.StaticDatabaseCalls purgeRunDetails
INFO: DELETE FROM W_ETL_RUN_STEP
WHERE
RUN_WID IN (’85C0734149D33952665F29E26ABD0’, ‘DF645EF112E8647C95A86018C9
7FE7C4′, ’54B048D2799F90DEBDD527D6BF651147’, ‘482A524D44211A1CF2EC08573AFF7AB’,
‘D9CE165FE2AEA7AB13B586D11669F30’, ‘C55F996E574E2482CC72FBC6942BE2E’, ‘F7EF2C3B3
B47A476A69836E66A428957′, ’53FA118792AEC54AE78A47011F96F60′)
Aug 15, 2014 2:35:27 PM com.siebel.etl.gui.data.StaticDatabaseCalls purgeRunDetails
INFO: 2000 records deleted in W_ETL_RUN_STEP
Aug 15, 2014 2:35:27 PM com.siebel.etl.gui.data.StaticDatabaseCalls purgeRunDetails
INFO: DELETE
FROM
W_ETL_DEFN_RUN
WHERE
ROW_WID IN (’85C0734149D33952665F29E26ABD0’, ‘DF645EF112E8647C95A86018C9
7FE7C4′, ’54B048D2799F90DEBDD527D6BF651147’, ‘482A524D44211A1CF2EC08573AFF7AB’,
‘D9CE165FE2AEA7AB13B586D11669F30’, ‘C55F996E574E2482CC72FBC6942BE2E’, ‘F7EF2C3B3
B47A476A69836E66A428957′, ’53FA118792AEC54AE78A47011F96F60’)
Aug 15, 2014 2:35:27 PM com.siebel.etl.gui.data.StaticDatabaseCalls purgeRunDetails
INFO: 8 records deleted in W_ETL_DEFN_RUN

As you can see the ‘Purge Run Details’ executes the above SQL Statements directly to the DAC Database Repository Tables. The logic to this deletion is more complex and covers a larger set of Log Tables. If you can find some logic to complete this yourself in a Batch File which meets your needs then you may be able to find an alternative solution, like you have for the Informatica Table..

Thanks,
~KKT~

What are the best practices to tune a Essbase BSO database?


Here are the best practices to tune a BSO database:

1. ACR (Average clustering Rate) should be as close to 1 as possible. If it is higher you can launch a dense restructure to de-fragment the database.
2. Block Size should be between 8 and 100KB (can be more on 64 bit systems).
3. Data cache should be 12.5% of the pag files.
4. Index cache should be the same size as the ind files.
5. You should have 1 Database per Application.

Thanks,
~KKT~

FDM Error “The file is corrupt and cannot be opened.” When Exporting a Report To Excel 2010″


When exporting a report as an excel file, the process completes without error on a Windows XP worksation with Office 2007 installed. The same process fails on a Windows 7 Workstation with Office 2010 installed.

The following error is returned:

The file is corrupt and cannot be opened.

To fix –

Open Excel 2010.
Click on File > Options.
Select Trust Center > Trust center settings.
Select Protected view.
Uncheck all the options under Protected View > OK.
Restart Excel 2010 and try to open Report via Excel.

Thanks,
~KKT~