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->
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.
