Wednesday, September 25, 2013

Increasing the text capacity in a Hyperion Planning Application

Author: - Sanjay Purohit

Audience:  Hyperion Administrators, Solution Architects and Hyperion Developers
The purpose of this document is to demonstrate the use of the “MAX_CELL_TEXT_SIZE” Property of the Oracle Hyperion Planning application.  The growing number of Hyperion Public Sector Planning users/clients along with the growing need for Budget Book publication is driving Hyperion to handle more text than ever.
We receive many requirements from our users and clients to enter the larger amount of text to Planning Data Forms. Some clients use the Data Forms to enter goals/mission/vision/project description and objective. I have also have seen the use of textual information to report KPIs. The good news is that Hyperion Planning can handle a large amount of text data and users can print them as needed for reporting purpose.  HFR is the preferred tool to generate reports.  
 If you want to really dive into the Oracle Hyperion Planning Admin Guide, you would find a heading with “Setting the Maximum Length for Text Values and Comments in Cells”.  Users can add cell text values and comments to form cells as described in the Oracle Hyperion Planning User's Guide.  By default, the maximum number of single-byte characters allowed for text in each cell is 255, and the maximum number for comments is 1,500.  Note that, in the database, the column data type is set to varchar(255) and varchar(2,000) by default. If you need to display additional characters in cell text or comments, you can set this Planning Application property to the maximum length required by your application:

MAX_CELL_TEXT_SIZE: Text values entered in cells whose data type is set to text. Updating these settings to more than 2,000 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.” (Source- Oracle Hyperion Planning Admin Guide)
Example:-

The goal of the paper is not to reiterate what is already provided in the Oracle Hyperion Planning Admin Guide but rather, the goal is to go one step beyond the Admin Guide and increase the Planning Data Form Text limit.  A few of our clients are already taking advantage of this functionality and entering a larger number of text characters (256-10000 characters) in Planning Data Forms to report the KPIs and Goals.  I have used the non ADF enabled Planning Application for demonstration purposes.  This trick also applies to ADF enabled Planning Application.

The Oracle recommended practice:   Go to Workspace->Administration->Application->Properties->

Add the following property

A change in Planning properties requires the server to be bounced/restarted.  After restarting the server, the change should take place.  Sometimes the changes do not take place and users are unable to enter the large amount of data. To solve this issue, you need to log into the Planning Schema and look for HSP_TEXT_CELL_VALUE table and increase the size.    Personally, I prefer to log into the schema and make the change.

 The value column is modified from 255 to 2000 as given below.


Before Update

SQL> desc HSP_TEXT_CELL_VALUE
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TEXT_ID                                   NOT NULL NUMBER(38)
VALUE                                              VARCHAR2(256)

After Update

SQL> desc HSP_TEXT_CELL_VALUE
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TEXT_ID                                   NOT NULL NUMBER(38)
VALUE                                              VARCHAR2(2000)

SQL>

Note- We have increased the size from 256 to 2000 characters.  In a Production environment, the size can be increased up to 10000 characters or more to meet the requirement.  However, there may be some performance issues associated with this change.  We have done this for a few of our clients with optimal Outline Design and Application Settings.  Design considerations for  the Outline, Member Properties, and Data Forms are out of the scope of this document.  I will try to publish those design considerations in next paper. 

Tuesday, July 9, 2013

Use of Alias in Dataform and Hyperion Financial Report

Author: - Sanjay Purohit

Audience:  Hyperion Administrators, Solution Architects and Hyperion Developers

The purpose of this document is to demonstrate the use of multiple alias tables in Hyperion Financial Reports. From time to time, I have received calls from previous client who would like to take advantage of this feature. Though a minor detail, indeed a powerful feature. This feature provides the ability to associate multiple descriptions for a member name. The usage or definition of the alias table are purely implementation specific. For example-

Dimension Name
Member Name
Default Alias
HYPERIONIZED No 1
HYPERIONIZED Alias No 2
Account
51
Sales Account
Manooj Account
Sanjay Account

Note: This is standard feature of Hyperion. No modification is required.
This example demonstrates a member name being associated with multiple description using Alias Table.  The following steps are covered:
·         Creating of new Alias table
·         Using Alias tables in Hyperion Financial Reports
·         Using Alias tables in Data form/Setting up Alias Table Default

To create a new Alias Table = Go to Administration>Alias Tables 


Click on Add, Provide the Alias table name



The alias table “HYPERIONIZED No 1” has been created successfully. Similarly, another alias table HYPERIONIZED No 2 can be created.




The next step is to provide names to members in the Alias Table.
To add the name to member go to Dimension->Edit->
The default alias for A51 is Sales Account

From the drop down- add the corresponding alias name to account


And



As mentioned earlier, the goal is to set alias table in reports and planning web forms.

To demonstrate this feature, we have created a sample report. The report will show both the alias table i.e. a report can be developed to be alias specific. To select the alias table. User needs to login to Hyperion Financial Reporting Studio.

User Case No 1 – using default Alias table
Select the grid from Hyperion Financial Report Studio and chose the alias table

The output would be 

User Case No 2 – Using HYPERIONIZED No 1 Alias Table
Use case No 3 – Using HYPERIONIZED No 2 Alias Table




·         Using Alias tables in Data form/Setting up Alias Table Default – The custom alias tables can be used with planning data form. To setup the custom alias table. Users need to go to Administration>Application>Setting


You  should see the below given screen

Custom developed alias tables can be selected from above given console