How to Enable Consolidation Rules in Hyperion Financial Management (HFM) Application


A standard consolidation is a type of consolidation driven by the application, which uses the default consolidation and eliminations. A non-standard one is driven by the Sub Consolidate() routine in the rule file. A non-standard consolidation is commonly used by statutory applications and is written by a qualified HFM consultant.

The steps to enable Consolidation Rules in classic HFM application:

1. Extract and save application metadata.
2. Open Windows HFM Client .
3. Click on the Manage Metadata option and open the metadata file.
4. From the Metadata Item drop down field, choose Appsettings.
5. From Members section, make sure that ConsolidationRules setting is set to Y.
6. Save the file.
7. Load the modified metadata file back to the application.

Note that the setting ConsolidationRules specifies whether consolidation rules are supported. Specify one of the following values:

Y to use the rules written in the Sub Consolidate() routine in a user-defined rule.
R to derive the proportional value in the Value dimension. Note that the proportional data is not stored.
N to use the default consolidation and eliminations.

Thanks,
~KKT~

Planning and Budgeting Cloud Service (PBCS): How to Kill Calculation Process?


1. Access Oracle Planning and Budgeting Cloud Service as Service Administrator
2. Launch Calculation Manager.
3. Select Tools > Database Properties > Expand Planning.
4. Right-click the Planning application and then select Sessions.
5. Select Session Management > Action > Kill.
6. From Entity, select “selected request”. You can kill a specific request, or requests by a specific user.
7. If you selected to end a specific request, from the list of existing sessions, select the session to kill the request.
8. Click the Apply icon.
9. Click Yes to confirm your action.

Thanks,
~KKT~

Steps to Install the Essbase Integration Services (EIS) Console 11.1.2.3.000


Steps to Install the Essbase Integration Services (EIS) Console 11.1.2.3.000

Below are the instructions for copying the Essbase Integration Services (EIS) console from a full EIS installation to an EIS Console only client machine.

The identical folder structure will need to be created on the Target Machine.

NOTE: Ensure that the file “startup.bat” located under “C:\Oracle\Middleware\user_projects\epmsystem1\EIS\bin” is pointing to the olapbldr.exe, as shown below:

cd “%ISHOME%\bin”
“%ISHOME%\bin\olapbldr.exe”
endlocal

Now, copy the following directories, including their Subdirectories, from the Source Machine to the Target Machine, creating the directories as needed.

1. C:\Oracle\Middleware\EPMSystem11R1\bin-32 and bin directories.
2. C:\Oracle\Middleware\EPMSystem11R1\common\EssbaseRTC\11.1.2.0 directory
3. C:\Oracle\Middleware\jdk160_35 directory
4. C:\Oracle\Middleware\EPMSystem11R1\common\jlib\11.1.2.0\epm_j2se.jar
5. C:\Oracle\Middleware\EPMSystem11R1\ccr directory
6. C:\Oracle\Middleware\EPMSystem11R1\products\Essbase\eis\console directory
7. C:\Oracle\Middleware\user_projects\epmsystem1\EIS directory
8. C:\Oracle\Middleware\user_projects\epmsystem1\bin\startOlapbldr.bat file and setEnv.bat file

You should now be able to start the EIS console using the startOlapbldr.bat file in the C:\Oracle\Middleware\user_projects\epmsystem1\bin directory.

Thanks,
~KKT~

How To Use String CDF Functions in Essbase 11.1.2.x


Below mentioned are the steps to follow-

1. Copy the JAR file and other files extracted from String.zip downloaded from http://www.oracle.com/technetwork/indexes/samplecode/essbase-sample-522117.html to the following directory: (if directory is not present create it)
C:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseServer\java\udf

2 To grant access to the JAR file, add the following statement to the end of the udf.policy file, which
is located in the C:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseServer\java directory:
grant codeBase “file:${essbase.java.home}/../java/udf/ CompareCDF.jar” {permission java.security.AllPermission;};
3. Modify the C:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseServer\java\udf\CDFRegisterAll.cmd as below and then run it:

Ensure RegisterStringFunctions.msh has the correct information in the first 3 lines:
set ServerName = ‘localhost’;
set User = ‘admin’;
set Password = ‘password’;

C:\Oracle\Middleware\user_projects\epmsystem1\EssbaseServer\essbaseserver1\bin\startMaxL.bat RegisterStringFunctions.msh

pause

Output of above MaxL script:

MAXL> LOGIN admin password ON localhost;

OK/INFO – 1051034 – Logging in user [admin@Native Directory].
OK/INFO – 1241001 – Logged in to Essbase.

MAXL> CREATE OR REPLACE FUNCTION ‘@JconcatStrings’
2> AS ‘com.oracle.essbase.cdf.StringFunctions.ConcatStrings(String[])’
3> SPEC ‘@JConcatStrings(strs)’
4> COMMENT ‘concatenates an array of strings’
5> with property runtime;

OK/INFO – 1056101 – Function @JconcatStrings created.

MAXL> CREATE OR REPLACE FUNCTION ‘@JconcatStringDouble’
2> AS ‘com.oracle.essbase.cdf.StringFunctions.ConcatStringDouble(String,double,boolean,boolean)’
3> SPEC ‘@JConcatStringDouble(str,dbl,strFirst,withDecimal)’
4> COMMENT ‘concatenates a string to a double with control over the order’
5> with property runtime;

OK/INFO – 1056101 – Function @JconcatStringDouble created.

MAXL> CREATE OR REPLACE FUNCTION ‘@Jequals’
2> AS ‘com.oracle.essbase.cdf.StringFunctions.equals(String,String)’
3> SPEC ‘@Jequals(str,str)’
4> COMMENT ‘compares two strings case sensitive’
5> with property runtime;

OK/INFO – 1056101 – Function @Jequals created.

MAXL> CREATE OR REPLACE FUNCTION ‘@JequalsIgnoreCase’
2> AS ‘com.oracle.essbase.cdf.StringFunctions.equalsIgnoreCase(String,String)’
3> SPEC ‘@JequalsIgnoreCase(str,str)’
4> COMMENT ‘compares two strings case insensitive’
5> with property runtime;

OK/INFO – 1056101 – Function @JequalsIgnoreCase created.

MAXL> CREATE OR REPLACE FUNCTION ‘@JcompareStringToDouble’
2> AS ‘com.oracle.essbase.cdf.StringFunctions.bcompareStringToDouble(String,double)’
3> SPEC ‘@JcompareStringToDouble(str,dbl)’
4> COMMENT ‘compares a string to a double’
5> with property runtime;

OK/INFO – 1056101 – Function @JcompareStringToDouble created.

MAXL> CREATE OR REPLACE FUNCTION ‘@JLCase’
2> AS ‘com.oracle.essbase.cdf.StringFunctions.LCase(String)’
3> SPEC ‘@JLCase(str)’
4> COMMENT ‘returns lower case’
5> with property runtime;

OK/INFO – 1056101 – Function @JLCase created.

MAXL> CREATE OR REPLACE FUNCTION ‘@JUCase’
2> AS ‘com.oracle.essbase.cdf.StringFunctions.UCase(String)’
3> SPEC ‘@JUCase(str)’
4> COMMENT ‘returns upper case’
5> with property runtime;

OK/INFO – 1056101 – Function @JUCase created.

MAXL> CREATE OR REPLACE FUNCTION ‘@JgetStringFromDouble’
2> AS ‘com.oracle.essbase.cdf.StringFunctions.getStringFromDouble(double,boolean,boolean)’
3> SPEC ‘@JgetStringFromDouble(dbl,withQuotes,withDecimal)’
4> COMMENT ‘returns a string’
5> with property runtime;

OK/INFO – 1056101 – Function @JgetStringFromDouble created.

MAXL> CREATE OR REPLACE FUNCTION ‘@JgetDoubleFromString’
2> AS ‘java.lang.Double.parseDouble(String)’
3> SPEC ‘@JgetDoubleFromString(str)’
4> COMMENT ‘converts a string to a double’
5> with property runtime;

OK/INFO – 1056101 – Function @JgetDoubleFromString created.

MAXL> CREATE OR REPLACE FUNCTION ‘@JechoBoth’
2> AS ‘com.oracle.essbase.cdf.StringFunctions.echoBoth(String[],double[])’
3> SPEC ‘@JechoBoth(strArray,dblArray)’
4> COMMENT ‘Echoes back all arguments passed to the function. To pass an array of arguments use @List(comma delimited list)’
5> with property runtime;

OK/INFO – 1056101 – Function @JechoBoth created.

MAXL> CREATE OR REPLACE FUNCTION ‘@JechoString’
2> AS ‘com.oracle.essbase.cdf.StringFunctions.echoString(String[])’
3> SPEC ‘@JechoString(strArray)’
4> COMMENT ‘Echoes back all arguments passed to the function. To pass an array of arguments use @List(comma delimited list)’;

OK/INFO – 1056101 – Function @JechoString created.

MAXL> CREATE OR REPLACE FUNCTION ‘@JechoDouble’
2> AS ‘com.oracle.essbase.cdf.StringFunctions.echoDouble(double[])’
3> SPEC ‘@JechoDouble(dblArray)’
4> COMMENT ‘Echoes back all arguments passed to the function. To pass an array of arguments use @List(comma delimited list)’
5> with property runtime;

OK/INFO – 1056101 – Function @JechoDouble created.

MAXL> CREATE OR REPLACE FUNCTION ‘@JgetDoubleQuote’
2> AS ‘com.oracle.essbase.cdf.StringFunctions.getDoubleQuote(String)’
3> SPEC ‘@JgetDoubleQuote(any string)’
4> COMMENT ‘Returns a string enclosed in double quotes’
5> WITH PROPERTY RUNTIME;

OK/INFO – 1056101 – Function @JgetDoubleQuote created.

MAXL> logout;

User admin is logged out

4. Restart Essbase

5. Created calc script under sample.basic as below

Set Updatecalc Off;

Fix (@CHILD(“Product”),”New York”,”Jan”,”Sales”)
“Actual” (
If (@JcompareStringToDouble(@NAME(@CURRMBR(“Product”)),100))
@JechoString(@LIST(“Level 1: Equal “,@NAME(@CURRMBR(“Product”))));
Else
@JechoString(@LIST(“Level 2: Not Equal “,@NAME(@CURRMBR(“Product”))));
EndIf
)

“Actual” (
If (@JequalsIgnoreCase(@ALIAS(@CURRMBR(“Product”)),@SUBSTRING(“goodcolas”,4)))
@JechoString(@LIST(“Equal: “,@ALIAS(@CURRMBR(“Product”)),” | “,@SUBSTRING (“goodcolas”,4)));
Else
@JechoString(@LIST(“Not Equal: “,@ALIAS(@CURRMBR(“Product”))));
EndIf
)

“Actual” (
If (@Jequals(@NAME(@CURRMBR(“Product”)),@JgetStringFromDouble(“100”,@_True,@_False)))
@JechoString(@LIST(“Equal: “,@NAME(@CURRMBR(“Product”)),” | “,@JgetStringFromDouble(“100”,@_True,@_False)));
@JechoString(@JconcatStrings(@LIST(“More”,”-“,”than”,”-“,”two”,”-“,”strings.”)));
Else
@JechoString(@LIST(“Not Equal: “,@NAME(@CURRMBR(“Product”))));
EndIf
)

EndFix

6. Verified and executed – success

EssbaseCluster-1.Sample.Basic.test Verify calculation script September 18, 2014 6:00:23 AM BST Succeeded
EssbaseCluster-1.Sample.Basic.test Execute calculation script September 18, 2014 6:00:35 AM BST Succeeded

Thanks,
~KKT~

FDMEE Loading Data To Different Planning Plan Types Does Not Work


FDMEE export to Hyperion Planning is failing due to different dimensions in each Plan Type. FDMEE attempts to load data to Planning dimensions that do not exist in some Plan Types.

To Fix-

Use the Refresh Metadata option to update the target application in FDMEE and set “valid for” flags correctly. If this does not resolve the issue update manually the aif_target_appl_dimensions table to set the flags correctly.

Thanks,
~KKT~

Explanation of Consolidations, Calculations and Translations in Hyperion Financial Management (HFM)


The HFM operations, Consolidate, Calculate and Translate will only be available for Impacted members, i.e. those with a status of other than OK or OK SC. There may be situations where the user wants to re-execute one or more of these operations based on some change that has occurred in the application. For example, suppose there is logic that pulls a data value from another scenario or year within the current scenario. If this data value changes, one or more values may no longer be correct in the current year and scenario, yet the status remains OK. The user can perform a Force Calculate to re-execute the logic for the base entity of interest or force a re-consolidation to re-execute logic for all child members of the selected parent and consolidate.

Also, a change may have occurred to the application itself such as changes to the logic or metadata. This will set an OK status to OK SC, indicating that some system change has occurred since the last successful calculation, translation or consolidation and the data may no longer be correct. Knowing how the system change affected the data in the application, the user can selectively force a re-execution of logic through a Force Calculate or force a re-consolidation.

Force Consolidation can be initiated in one of two ways: Consolidate All with Data and Consolidate All. Consolidate All with Data will re-execute logic, translate and re-consolidate all entities below the selected parent with a status of OK and OK SC, ignoring entities with status of NODATA. Consolidate All will re-execute logic, translate and re-consolidate all entities below the selected parent, including entities with status of NODATA. Users should be careful when using Consolidate All because, in applications with large, extensive organizations, the consolidation time may be unnecessarily long.

A change in translation rate data is considered a system change and will set all OK statuses to OK SC. Knowing which currency was affected, the user can re-execute a translation using Force Translation. However, where the translation is part of the value dimension roll-up, translation will occur automatically during a forced re-consolidation and the user need not be concerned with Force Translate. In practice, users will utilize Force Translate for “translation-on-the-fly”, i.e. for currencies as they appear in the value dimension.

Finally, the Calculate Contribution operation can be thought of as a consolidation for a single entity, where the system rolls the data up to the Contribution Total of the value dimension for the selected entity, stopping short of consolidating the data to the parent. This also has a “force” counterpart called Force Calculate Contribution, where logic and translation are re-executed for the current entity and the data re-rolled up to the current entity’s Contribution Total.

Thanks,
~KKT~

Data Relationship Management Best Practices


Properties

Use default values for properties. When a value is used often, defining it as the property default helps reduce database size.

Use inheritance for the same reason. (Local inheriting means from the hierarchy you are looking at; Global means from the node’s position in a specific hierarchy, the Controlling Hierarchy.)

Use Global Properties unless

– the value must be different for the same node in different hierarchies or

– the value for shared nodes must be different from the primary node in the same hierarchy

Consider the performance implications of derived properties. Functions that are recursive, such as NumDescendantsWith, are much slower than simple functions. The order of items in, say, IF functions can affect performance too.

Do not delete properties once the system is live. You will lose historical data and it may have unexpected effects if anything else uses the property, such as exports, queries and derived properties. To deprecate a property, hide it instead.

Versions

Define a naming standard for versions so that it is easy to determine what is what in the future.

Baseline versions, together with the Transaction History, are used to create As-Of versions. If you do not require this functionality, you can turn off Baseline versions using the System Preference AllowAsOf. After setting this to false and restarting the DRM application, you will find they are no longer created when you create a new version.

Copy versions on a periodic basis. For example, start each month with copies of the previous months’. If you do not, the Transaction History for the version will eventually become unmanageable. Employing DRM’s versioning functionality gives the following benefits:

– allows the use of version status to lock older versions

– enables you to delete older versions and their transaction history

– permits easy comparisons with prior business states

– ensures the As-Of capability performs well.

Hierarchies

It is best practice to use separate hierarchies rather than shared nodes if possible. For example, instead of having one Organization hierarchy that contains Legal, Managerial and Line of Business, separate them into three separate hierarchies and put them back together when exporting to downstream systems.

Validations

Creating properties with the validation logic (i.e. placing the logic inside a single derived boolean property rather than having complex tests in the validation itself) permits re-use of the properties in Queries, Exports and other Validations.

Real time validations have significant performance cost, especially complicated ones, because all of them are run on each change, including every line of Action Scripts. Use them wisely!

Note that Real Time usage does not protect against changes in values from inheritance or derivation. Consider whether certain validations should also be run as batch validations before performing exports etc.

Node Types

Once Node Types are in use, any new validations or properties must be added to the appropriate node types or they will not visible to users.

Browser

For best performance use a recent version of a supported browser. At the time of writing the supported browsers are FireFox 17 ESR and Internet Explorer 7.0, 8.0 and 9.0, but we recommend Firefox or IE 9.0 for the best UI performance. “JavaScript performance has been much improved over older versions of Internet Explorer.

Thanks,
~KKT~

How to Increase Cell Text or Comments Field Size for Planning Web Forms


Setting the Maximum Length for Text Values and Comments in Cells

Users can add cell text values and comments to data form cells as described in the Oracle Hyperion Planning User’s Online Help. By default, the maximum number of single-byte characters allowed for text in each cell is 255, and the maximum number for comments is 1500. Note that, in the database, the column data type is set to varchar(255) and varchar(2000) by default.

If you need to display additional characters in cell text or comments, you can set these Planning application properties to the maximum length required by your application:

– MAX_CELL_TEXT_SIZE: Text values entered in cells whose data type is set to text

– MAX_CELL_NOTE_SIZE: Comments added to cells
To set the maximum length for text values and comments in cells:

1. Select Administration, then Application, and then Properties.

2. Select Application Properties.

3. Add the property by clicking Add, and then enter one of these properties in the blank row:

– MAX_CELL_TEXT_SIZE

– MAX_CELL_NOTE_SIZE

4. Enter a value in Property Value to represent the maximum number of single-byte characters allowed for text values or comments in each cell.

5. Optional: If you are updating both of these properties, repeat step 3 and step 4 for the other property.

6. Click Save and confirm your changes.

7. Stop, and then restart the Planning server.

8. Back up the database, and then update the database column size or type to support the changed size specified in this property. For additional information, see the documentation for your database.

Updating these settings to more than 2000 requires that you make a corresponding change to the database. If you increase the maximum number of characters allowed for cell text, you must alter the database column size or type to support the changed size. (Changing the column type to CLOB, NCLOB, TEXT, or NTEXT to accommodate large cell text size can affect performance. Do so only if your application requires large cell text entries.) For additional information, see the documentation for your database.
The table affected for cell text is HSP_TEXT_CELL_VALUE, and the column in VALUE

For e.g

You would need to update the database to support 2000 character limit on the table. You can use the following to make the change
ALTER TABLE hsp_text_cell_value MODIFY VALUE NVARCHAR2(2000)

Thanks,
~KKT~