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. 

No comments:

Post a Comment