Hyperion Planning Input Forms

Hyperion Planning Input Forms

You’re Doing it All Wrong

 16+ Years Hyperion Implementation Experience  Certified in both Planning and Essbase

 Prior Practice Lead at Hyperion Partner Firm  Co-Editor of the book “Developing Essbase

Applications: Advanced Techniques for Finance and IT Professionals”

Developing Essbase Applications

● Over 50 years of expert experience between the covers.

● Advanced content written so that developers at all levels will gain assistance and insight.

● Chock full of experience proven best practices.

● Collaboratively written by a team of respected and sought after Essbase developers.

● Source code at

www.developingessbasebook.com

● Includes all download access to all source code & scripts.

● Purchase your copy today and energize your Essbase career!

US-Analytics is an industry leading professional services firm focused

on helping clients successfully establish and maintain long term Business Intelligence (BI) and Enterprise Performance Management (EPM)

applications and programs.

• BI and EPM Strategy and Processes

• Custom and packaged BI and EPM Applications • Oracle Infrastructure

• Managed Services and Hosting

For over a decade, market leading companies have trusted US-Analytics to solve complex business problems, drive managerial excellence, and deliver operational agility.

Learn more at Booth #107 or

 Planning 11.1.2.2 (ADF Interface)

 Major League Baseball Statistics (Pitching & Hitting)  50+ Stat Accounts

 FY10 – FY13

 Covers All Teams, Players & Games

 Attendees will Receive Exported Demo Application  Data Available from Windows Azure Marketplace

What’s in the Demo?

 The Planning and Essbase objects used in this demo

are available for free. They are unsupported.

 They are available for learning purposes only and

may include incomplete and/or inaccurate data.

 Difficult to Interpret  Perform Poorly

 Allow Bad Data

 Lack Interconnectivity

 Let the Database Do All the Work  Include Only “Lifeless” Grids

 Don’t Predict the Future

 Organizing Data  Limiting Data

 Creating Interconnected Forms  Creating Data Validations

 Creating Row & Column Formulas  Creating Embedded Charts

 Predictive Planning

Agenda

 Good Layout = Easy Consumption  Forms vs. Bulk Data Interfaces

 White Space  Borders

 Composite Forms

Organizing Data

 Don’t present users with a massive “wall” of cells.  Hide irrelevant rows and columns.

 Use the “Show Separator” feature to give users context

regarding different subject areas.

 Use blank rows to introduce spacing into forms.

 Use Composite Forms to present different (but related) subject

areas on the same form.

 Use Data Validation Rules that always evaluate to “True” for

additional formatting options.

Introducing Blank Spaces

To introduce a blank line into a form add a Formula Row with no formula or label.

Certain older versions of Planning require a label, otherwise the dimension name will be displayed. In these instances, use “.” as a label.

Using Blank Spaces

Good Things

Using Borders

“Show Separator” does not work with certain unpatched versions of ADF.

Composite Forms

● Under the right circumstances, composite forms bring together

multiple subject areas into a single view.

 Con’s

● Formatting and space allocation can be unpredictable, especially

when users have different display resolutions.

Forms vs. Bulk Data Interfaces

Using Validation Rules for Formatting

validating data!

 Developers can create validations that ALWAYS

 Suppress Missing Data (Rows & Columns)

● Set in the Grid Properties, Row Properties or Column Properties. ● Prevents blank rows or columns from being displayed.

 Suppress Missing Blocks

● Set in the Grid Properties.

● Does nothing, unless used with “Suppress Missing Data”. ● Typically good for forms where most rows will have no data. ● Typically bad for forms where most rows have data.

● Can cause issues displaying certain dynamic members and attributes.

Demo / Examples

 Pro’s

● Can keep irrelevant data off the form.

● Can make forms faster (when used correctly). ● Can make forms easier to consume.

 Con’s

● Planning does not support “Conditional” suppression in forms. ● What happens when users want to plan for a member combination

that currently has no data? (Hint . . . Take the Form Ad-Hoc)

 One aspect of building a “good” form is providing

multiple navigation routes to and from that form.

 Create right-click menus that take users from one

form to another, while passing “context”.

 This is especially useful when moving from a

summary form to a detail form.

 Menus can be used to do much more than navigate to

another form. They can:

● Take Users to a URL.

● Launch a Business Rule (Now with confirmation messages!) ● Take Users to Manage Approvals

● Take Users to the Previous Form

 Create a menu using the menu options

Administration, Manage, Menus.

 Add menu items that open forms, launch business

rules, manage approvals, open URL’s, etc.

 Assign the menu to a Required Parameter. This

simply tells Planning where the user must right-click in order to open the menu.

 Add the menu to a form in the form’s Other Options.

Creating Menus

Demo / Examples

 Users can set User Variables that limit the data

presented on a form.

 Forms must be built to take advantage of variables.  Variables can be placed in the POV, Page, Rows or

Columns.

 Variables can be placed within functions.

 Open the Planning application.

 Select the menu options Administration, Manage, User Variables. Click Add.

 In the form layout, select the variable from the Variables tab.

 A typical User Variable is set by the end-user in their Preferences.

 Users open the Planning application and select the

menu options File, Preferences. **

 Users must select the Planning icon, then the User Variable Options tab.

 The documentation is not clear.  Use Context is a variable setting.

 Enable Dynamic Variables is a form setting.

 Both options allow users to change variable values on

the fly, but in different ways.

 Both options can be used at the same time.

 There are two main differences:

● Use Context variables are set when the user right-clicks on

one form to navigate to another form (and passes context).

● Use Context variables can not be set in the user preferences.

● Enable Dynamic variables can be modified directly in a form.

● Enable Dynamic variables can be set in the user preferences.

● A variable can leverage both settings.

Demo / Examples

 If a user does not have read or write access to a

member, it will be suppressed on the form.

 Usefulness of this feature is limited, because users

often have read access to more members than those to which they can write.

 There is no easy way to limit members in a Page list

box, row or column based only on write access. **

 Data validations allow developers to alert users to

bad data.

 Developers can create “soft” validations that do

nothing but present a message.

 Developers can create “hard” validations that prevent

users from promoting their data or alter the promotion path.

 Developers can create data validations that ALWAYS

evaluate to “true” simply to introduce some formatting into their forms.

 Evaluate conditions in an individual cell or range of cells.

 Evaluate conditions in a “design-time” cell, for example a cell

defined in the form with a function that returns multiple individual cells.

 Evaluate conditions in a column or row.

 Evaluate cells specific to a member’s data value.  Evaluate cells specific to a member name itself.  Evaluate the cells of a specific account type.

 Evaluate cells with a specific version type.

 Evaluate cells with a specific variance reporting type.

 Evaluate cells that reference members of a dimension with a

specific user defined attribute.

 Evaluate cells that reference members with a specific attribute

member association.

 . . . . format cells that meet these criteria.  . . . . display a validation message.

 . . . . alter or stop the Approvals process.

Demo / Examples

Data Validations

● They’re very flexible and support many conditions. ● They integrate with Approvals.

● Unlike custom JavaScript, they work in Smart View.

● Unlike custom JavaScript, they don’t require programming skills. ● Unlike custom JavaScript, they don’t break every time you upgrade.

Data Validations

 Con’s

● Watch out for floating point decimal errors. Reference a range of

numbers rather than an explicit number.

● Cannot easily exclude non-editable cells.

● They don’t actually prevent data from being saved (yet).

● A data validation in one form cannot reference another form.

● Be careful using red cells with data validations if your users format

 In the Form Layout, right-click a row or column and

select Insert Formula Row (or Column).

 Give the row or column a name in the Formula Label.  Select the formula row or column heading (not the

cell) to display the formula properties.

 Indicate the Formula Data Type for each dimension.  Enter and validate a formula.

 Abs  Average  AverageA  Count  CountA  Difference  Eval

Creating Formula Rows & Columns

 IfThen  Max  Min  Mod  PercentofTotal  Pi  Product  Random  Rank  Round  Sqrt  Sum  Truncate  Variance  VariancePercent

 Sum(row[2])  row[2].Sum

 Sum(column[A], column[C])

 IfThen((IsMissing([A]) AND [B] > 0), 0, Eval([A] / [B]))

 IfThen(Not(IsNN([B])), Eval(PercentofTotal([B], [B,3]) / 100),[B])

Demo / Examples

 Pro’s

● Can help with database retrieval performance.

● Can help resolve database calculation order issues.

● Can save having to run a business rule with the save of a form. ● Good for one-off calcs that don’t require database development. ● The function “Pi” is available for anyone who wants to forecast the

circumference of a circle or is to lazy to type some numbers into a formula . . .

 Con’s

● Does not handle divide-by-zero issues gracefully.

● No explicit function to return #Missing. This is a challenge when

suppressing missing rows and you have a column formula.

● Formula columns are suppressed entirely if a label does not exist in

the first row heading.

● Limited control over in-sheet calc order when mixing Row AND

Column formulas in the same form.

● The “Pi” function is irrational . . . what if I need 16 digits???

 Users can add charts in Composite forms. ● Bar ● Horizontal Bar ● Line ● Area ● Pie ● Scatter

 Step 1 – Create Composite Form  Step 2 – Edit Form Layout

 Step 3 – Right-Click One of the Forms  Step 4 – Select “Display as Chart”

 Step 5 – Select “Options” Tab  Step 6 – Select Legend Position  Step 7 – Select Label Position

Demo / Examples

Embedding Charts in Forms

● Very quick and easy.

● Charts are attractive out-of-the-box.

 Con’s

● Chart options are very limited.

● Cannot place labels on chart axes. ● Not without bugs . . .

 To monitor the performance of forms, Planning includes

Grid Diagnostics. (as of version 11.1.2.2)

 Select the menu options Tools, Diagnostics, Grids.  Select the Run Diagnostics button.

 Select the forms to review.

 Press the Run Diagnostics button.

 Click the chart for individual form stats.  Experiment with different chart options.

Demo / Examples

 By default, Planning users may open one form at a

time.

 To change this behavior, select the menu options

Administration, Application, Properties.

 Add the setting DATA_GRID_CACHE_SIZE and give it

a value.

 Save changes and restart the Planning server.

Opening Multiple Forms at Once

 Place the property in the Application Properties to

only affect a single application.

 Place the property in the System Properties to affect

all applications.

 WARNING – This will increase memory consumption.

 When using the ADF interface, Planning will return

the form in “chunks” of 25 rows and 17 columns.

 When the user scrolls beyond this initial “chunk” the

application will fetch 25 more rows and 17 more columns.

 Administrators can change this threshold by

adjusting an Application Property called “GRID_PARTIAL_FETCH_SIZE”.

Grid Fetch Properties

Property requires two values (row & column setting) separated by a comma.

 Affects all forms and all users in a given application.  Only available starting with 11.1.2.2 patch 303.

 Patch readme file makes no mention of a related

System Property.

Turbocharge Your Input Forms with Predictive Planning

Wednesday, June 26 11:15 AM – 12:15 PM