Hyperion Planning quick reference

Hyperion Planning Application Framework:
Planning: - A web-based budgeting and planning solution
Planning components:

• Planning
• Essbase
• Administration Services (EAS)
• Workspace
• Shared Services
• EPMA
• Smart View
• Calculation Manager
• Financial Reporting

Planning Architecture - Multi-tier. Uses Essbase as well as relational databases.

• Client - Offline client and Smart View Client
• Web Application - Planning, Financial Reporting, EPMA, Shared Services, EAS
• Database -  Essbase, Planning relational database, PMA relational database

Essbase vs Planning Repository (what is stored where)

Offer: Save $100 on this HP Desktop, refurbished by the Geek Squad

Planning Repository                                     Essbase

Security                                                           Security

Metadata                                                         Metadata

Foreign Exhange Rates                                     Foreign Exhange Rates

Smart Lists                                                       Smart Lists (numeric values after data save)

Process Management Details                            Data

Annotations/Cell text/ Support details               Business Rules

Task Lists                                                        Rules Files

Preferences                                                      Currency Calculation scripts

Application Setup                                             Substitution Variables

Data Forms

User Variables

• Application Name
• Number and names of plan types (essbase databases)
• Calendar Structure
• Multicurrency or not
• Default Currency

Method of creating Application:

• EPMA
• Application creation through wizard
• Drag and drop application modification
• Design application graphically in central library
• Duplicate application for testing and what-if analysis
• User dimensions and attributes across multiple applications
• Manage data flows graphically
• Graphical business rule design in Calculation manager
• Classic
• Application creation using wizard
• Modify single application using Dimension editor
• Set up graphical business rules for complex calculations

Plan Types: These are the Essbase databases where outline (apart from relational database) and data are stored. Plan comes withe three Plan Types that can be customized (Plan1, Plan2, and Plan3) and two others for Workfoce planning and Capex.

Dimensions: Structural elements of an application that describe and hold data.

• 6 required dimensions (8 if it is a multi-currency) application. Can add 14 (12 in case of multi-currency) dimensions to make a total of 20.
• Alias, Smart List, UDA, and Attribute not included in the twenty.

1. Account - Dense
2. Period -  Dense
3. Entity - Sparse
4. Year -  Sparse
5. Version - Sparse
6. Scenario - Sparse
7. Currency (multi-currency)
8. HSP_Rates (multi-currency)

Dimension Properties:

• Data Storage Properties
• Store - Stores data values of members
• Dynamic Calc - Calculates data value each time members is retrieved. Does not store
• Dynamic Calc and store - Calculates first time and then stores
• Shared (Share data)- Allows two or more members to share
• Never Share - Prohibits sharing member values
• Label Only - No data associated with member. Navigation and Reporting purpose.

Dynamic Calc - 

• Planning calculates data values of members, and disregards these values.
• Data values are not saved
• No Dynamic Calc for base-level members for which users enter data
• No Dynamic Calc for a parent member if you enter data for that member in a target version

Dynamic Calc and Store - Use Dynamic Calc and Store for members of sparse dimensions with complex formulas

Never Share - Default. If a parent has only one child.

Label Only - 

• To display value
• cannot assign label only to level 0 members.
• Decrease block size
• No attribute to label only member
• If parent is label only, child is never share by default

• Formula
• No member formulas for members that are Shared or Label Only
• Smart List
• Only one Smart List to be associated with a member. 
• UDA
• Specifies the value of the user-defined attributes to bind to the member
• Data Type (determine how values are stored in account members)
• Currency
• Non currency
• Percentage
• Text
• Date
• Smart List
• Unspecified
• With Currency data type, available exchange rate types are:
• Average - Average exchange rate
• Ending - Ending exchange rate
• Historical - Exchange rate in effect when, for example, earnings for a Retained Earnings account were earned or assets for a Fixed Assets account were purchased
• Aggregation
• Plus
• Minus
• Multiplication
• Division
• Percentage
• Ignore ~
• Never (do not aggregate)

Essbase Data Calculation Order -

• Account Dimension
• Period Dimension
• Other Dense Dimensions top-down fashion
• Other Sparse Dimensions top-down fashion
• Members tagged two pass calculation

Account Dimension Properties:

Account Type: Account type defines accounts’ time balance (how values flow over time) and determines

accounts’ sign behavior for variance reporting with Essbase member formulas. The account types are:

• Account Type                           Time Balance                               Variance Reporting
• Expense                                      Flow                                               Expense
• Revenue                                      Flow                                               Non-expense
• Asset                                          Balance                                           Non-expense
• Liability                                       Balance                                           Non-expense
• Equity                                         Balance                                           Non-expense
• Saved Assumption                      User-defined                                   User-defined

Variance Reporting

• Expense (The actual value is subtracted from the budgeted value to determine the variance)
• Non-expense (budgeted value subtracted from the actual value to determine the variance)

Time Balance:

• First               Beginning value in a summary time period as the period total
• Flow              Aggregate of all values for a summary time period as a period total. 
• Balance          Ending value in a summary time period as the period total
• Fill
• Average         Average for all the child values in a summary time period as the period total
• Weighted Average Actual_Actual
• Weighted Average Actual_365

Period Dimension Properties:

• Can update BegBalance and base time periods
• Cannot add or modify YearTotal time period.

Creating and Refreshing Applications: Planning creates the following things:

An Essbase application

      Multiple Essbase databases (one per plan type)

      Essbase access permissions filters

      Essbase outlines (all metadata):

            Members

            Shared members

            User-defined attributes and attribute values

Weekly Distribution: 4-4-5, 5-4-4, 4-5-4, or Even

Implied Share: Some members are shared even when we do not explicitly set them as shared.
The following situations apply:

• Parent with only one child.
• Parent has only one child that consolidates to the parent.
• Parent data type is label only.

Time Balance default settings: Ending for Balance and None for Flow.

Workspace URL: http://web server:port/workspace (http://myserver:19000/Workspace)
Planning URL: http://web server:port/HyperionPlanning (http://myserver:8300/HyperionPlanning)

Grid Spread: A way of spreading data. If your administrator has enabled Grid Spread, you can specify an amount or percentage to increase or decrease values across multiple dimensions on the grid, based on the existing values in the target cells. When calculating the spread data, read-only and locked cells and cells having supporting detail are ignored. Data integrity is ensured because values can be spread only to cells
to which you have access.

Mass Allocation: Using mass allocation, you can spread data to all descendents of a source cell and across all dimensions. Spreading by mass allocation spreads data to cells not displayed on the grid, and
does not require that you have access to the target cells.

Relationships:
Member, Descendants, Descendants (INC), Ancestors, Ancestors (INC), Siblings, Siblings (INC), Parents, Parents (INC), Children, Children (INC), Level 0 Descendants.
SmartView
     Connection Types:

•      Private Conenction
•      Shared Connection

     Adding connection using URLs

• Planning: http(s)://servername:port/HyperionPlanning/SmartView
• Essbase: http(s)://servername:port/aps/SmartView

SmartView Data Options

• Suppress Rows
• No Data/Missing
• Zero
• No Access
• Invalid
• Underscore Characters
• Repeat Members
• Suppress Columns
• No Data/Missing
• Zero
• No Access
• Replacement
• #NoData/Missing Label
• #NoAccess Label
• #Invalid/Meaningless
• Submit Zero
• Display Invalid Data
• Enable Essbase Format String

@XREF function looks up a data value from another database to calculate a value from the

current database.

Hyperion Planing Access Permission

4 levels of security

1. User Authentication - asks for username and password
2. Task Security - users can do only things that they are assigned to
3. Data Security - not all users can modify data
4. Object Security - not all users can view/modify dataforms

NOTE: Account, Version, Scenario, and Entity dimensions (by default) are enabled for access permission.

Planning elements that can be assigned access:

• Scenario members
• Version members
• Account members
• Entity members
• User-defined custom dimension members
• Launch privileges to Calculation Manager business rules
• Data Forms
• Data Form folders and Calculation Manager BR folders
• Task lists

Types of Access

• Read
• Write
• None

Business Rules Access types

• Launch
• No Launch

Task List Access types

• Assigned
• Manage
• Manage & Assign
• None

Inheritance of Permission

• Member
• Children
• iChildren
• Descendants
• iDescendants

Folder Access: Folder access takes precedence to its parent folder. 

No direct access assignment to Shared Member. Inherits from the base member. 

ImportSecurity/ExportSecurity Planning utilities to import-export Planning access

ImportSecurity utility loads access permissions for users or groups from a text file into

Planning. Importing overwrites existing access assignments only for imported members, data forms, data form folders, task lists, Calculation Manager business rules, and Calculation Manager business rule folders. All other existing access permissions remain intact. SL_CLEARALL parameter clears all existing access permissions.

ExportSecurity utility can be used to export Planning security which can be imported by using the ImportSecurity utility. ExportSecurity utility creates a SECFILE.TXT file on the Planning bin folder (\\Planning_Server\d$\oracle\Middleware\user_projects\epmsystem1\Planning\planning1).

SECFILE.TXT should have the following items (if creating manually)

1. username/group name
2. artifact name
3. access permission    (READ, READWRITE, NONE)
4. Essbase access flags (@CHILDREN, @ICHILDREN, @DESCENDANTS, etc)
5. artifact type (SL_FORM, SL_CALCRULE, etc)

Delimiters that can be used in SECFILE.TXT:

• comma (default)      admin,my_member201,READ,MEMBER
• semi-colon               Group/user_name,DataForm Name,READWRITE,MEMBER,SL_FORM
• colon
• space
• tab
• pipe

ImportSecurity Syntax: - ImportSecurity “app_name,user”

ExportSecurity Syntax: - ExportSecurity /A=app_name,/U=username,/S=Sales

We can specify a path and file name during ExportSecurity.

Audit Trial to track changes

• ADMINISTRATION -> Application -> Reports
• Select Auditing tab.                                                          Approvals
• Dimension Admin                                                   Copy Version
• Alias Table Admin                                                  Security
• Data                                                                       User Admin
• Launch Business Rules                                            Group Admin
• Data Form Definition                                               Offline
• Data Form Folder Admin                                        Task List
•                                                                                Copy Data
•                                                                                Clear Cell Details

HSP_AUDIT_RECORDS: - the table where audit results are recorded. 

Update/Create Security filter

• Update security for all users:
• Admin > Application > Refresh/Create Database > Security Filter.
• Update security for individual users:
• Admin > Manager > Security Filter. Select the ones to be created. Create.

UpdateUsers and UndateNativeDir utilities - to sync users/groups

Used in following situations:

• You change authentication providers.
• Users, groups, or organizational units (OUs) are moved in an external provider.
• You migrate your application from one environment to another (for example, from Dev to
  Prod) and plan to change Shared Services repositories.

UpdateUsers utility - to update SIDs in Planning with changes in Shared Services.

Syntax: updateusers.cmd Servername admin_username App_name

ProvisionUsers utility - synchronizes Planning users, groups, and roles in Shared Services Console with a Planning application and with Essbase

Syntax: ProvisionUsers /ADMIN:admin_username /A:appname

Application Refresh - The following things happen

• Essbase security filters are updated.
• Currency conversion calc scripts are updated.
• Members and associated properties are propagated from the relational database to the Essbase database.
• Custom attributes are added, modified, or deleted in the Essbase database.
• Exchange rate values are repopulated in the Essbase outline.
• Member formulas for certain accounts are dynamically generated or updated.
• Additions or changes to alias tables and their associations to dimensions or members are updated.
• The Essbase database is restructured.
• UDAs are added to the Essbase database.

Refresh/Create Options

• Database - create or refresh
• Update custom-defined functions
• Security Filters
• Shared Members
• Validate Limit

Multi-currency

• HSP_Rates dimension
• HSP_InputValue - stores data value
• HSP_InputCurrency - stores currency types for data values

Performance Optimization

1. Reordering dimensions
1. Dense first (most dense to least, eg, Period - Account), then Sparse. 
2. Sparse - place aggregating dimensions before non-agging.
3. NOTE: Dense calculate faster than sparse.
2. Allocating memory for supporting details cache
1. SUPPORTING_DETAIL_CACHE_SIZE - increase. Default is 20
2. Oracle recommends cache size of not more than 60%
3. Data and Index cache size
1. Index Cache - more than 256 BM
2. Data Cache - at least 2 GB
4. Other tips
1. Dense dimension upper member - dynamic calc
2. Clear/move historical or unnecessary data.
3. Defragment Essbase database routinely.
4. Split large data forms.
5. Not too much account annotation.

OutlineLoad Utility - To load metadata or data.

SYNTAX: -             ( I is used for Import and E for export)

D:\EPM_ORACLE_INSTANCE\Planning\planning1>OutlineLoad /A:application_name /

U:admin_username /M /I:c:\file_name.csv /D:Entity /L:c:/file_name.log /X:c:/exception_file.exc

Metadata load: 

• Must have header record
• Headers are case sensitive
• OutlineLoad script itself is case sensitive.
• Default STORE is NEVER SHARE.

Data Form - 

• It is associated with Plan Type.
• Users can edit data forms only if they have access to at least one member of each secured dimension.
• Users should have WRITE access to be able to change data. 
• Buttom-up versions - rows and columns with level 0 members allow data entry.
• Target version - data entry in parent or child member.
• Data validation - color/message to implement business practices.

Importing Data Form

• FormDefUtil utility/ ImportFormDefinition
• Import -  formdefutil [-f:passwordFile]import|exportfilename|formname|-all server name user name application
• Export -  FormDefUtil.cmd export -all localhost admin APP1   (all forms)
• Exprot - FormDefUtil.cmd export Form1 localhost admin APP1  (one form)

Business Rules -

• Run on load - launch it automatically when the data form is opened
• Run on Save - launch it automatically when saving a data form
• Runtime Prompt - prompt users for such variables as members, text, dates, or numbers

Planning Units

• combinations of scenario, version, and entity or part of an entity. 
• Scenarios and versions are the basis of the review cycle. 
• Planning units submit planning data for a scenario and version.
• Planning unit owner - only 1. Reviewer - multiple

Planning Unit Hierarchies

• Entity is the primary dimension for each planning unit hierarchy.
• Buttom-up budgeting - data input in leaf level
• Distributed budgeting - data input at leaf. Ownership at top level. Distributed down, and then submitted to top level through approval process.
• Free form budgeting - data input at leaf. Planner can select owner from drop-down list.

Task/ Task List:

• Task lists guide users through the planning process by listing tasks, instructions, and due dates.
• Tasks can display instructions, due dates, completed dates, and alerts.
• Can include Web pages, data forms, approval, business rules, or descriptions.
• URL Task: Opens a specified URL
• Web Data Form: Opens a data form
• Business Rule: Launches a business rule that you specify
• Manage Approvals: Starts the review process with a specified scenario and version
• Descriptive: Descriptive information
• Import/export -TaskListDefUtil utility
• Import - TaskListDefUtil.cmd import c:\EPM_ORACLE_INSTANCE\Planning\planning1\TaskList1.xml localhost admin APP1
• Export - TaskListDefUtil.cmd export TaskList1 localhost admin APP1

Application use limit setting

ADMINISTRATION -> APPLICATION -> SETTING

ADVANCED SETTING

SYSTEM SETTING

APPLICATION MAINTENANCE MODE -> ENABLE USE OF APPLICATION FOR:

• All users
• Administrator
• Owner

Deleting supporting details 

• SDDELETE.SQL file in SQL directory. Need to be careful. Look at manual.

MENUS: - Enables users to click rows or columns in data forms and select menu items to: 

• Launch another application, URL, or business rule, with or without runtime prompts
• Move to another data form
• Move to Manage Approvals with a predefined scenario and version

Sparse vs Dense

• At least one dense dimension required for Essbase.
• Custom attributes cannot be assigned to Dense dimension.
• Account/Period dense; others sparse by default.

Currency

• Base Currency
• Reporting Currency

SKIP OPTIONS - Zeros and Missing

• None   Zeros and #Missing are considered during calculation

• Zero  Excludes 0s while calculating parent values

• Missing   Excludes #Missing during calculation

• Zeros and Missing  Excludes Zeros and #Missing during calculation

SAVED ASSUMPTIONS - to centralize planning assumptions. 

Deleting Members

• Before deleting member from dimension, we need to delete it throughout Planning, such as data forms, planning units, exchange rates, etc. 
• SHOW USAGE can be used to see usage. 

Shared Members

• For alternate roll up
• Base member must exist in Planning before we can create a shared member.
• Can create multiple shared members for a base member
• Member formulas, custom attribute, custom attribute values not allowed. 
• Renaming base member renames shared member as well
• Cannot have children.Should be base level.
• Can enter data in shared member. Values stored in base member.
• Shared member available for Account, Entity and user-defined custom dimensions

Attributes - can be

• TEXT
• NUMERIC
• DATE (month-day-year / day-month-year)
• BOOLEAN (base dimension can be associated with only one attribute dimension with Boolean data type)

• Group members with same criterion.
• Only can assign to sparse dimension.
• Cannot assign to Label-only member
• No aggregation properties
• If a sparse dimension is changed to dense, all attributes and attribute values are deleted.

To create Attribute, 

• Make sure to be on the top level of a Sparse dimension.
• Edit, and then CUSTOM Attributes. 
• Here we can click CREATE to create attribute.

Calendar Roll up

Base Time Period

• 12 Months: Four quarters per year; months roll up into parent quarters and quarters into years.
• l Quarters: Quarters roll up into years.
• l Custom: A custom time period, such as weeks or days. No default rollup structure.

Calendar Year and Time Periods

We cannot change base time period or reduce the number of years after the application calendar is created. Administrators can change the names, descriptions, aliases, and ranges of the summary time periods in the hierarchy.

• Up to 500 Time Periods and 100 Years.
• Oracle recommends
• 400 Time Periods and 27 Years or
• 360 Time Periods and 30 Years.

BEGBALANCE

• can edit the BegBalance member of the Period dimension. 
• can rename and describe BegBalance
• can have an alias.

Currency 

• Three-letter code
• Symbol
• Description up to 256 characters
• Triangulation currency
• Alias table
• Number formatting

Triangulation

• Planning supports currency conversion by triangulation through an interim currency called the triangulation currency. 
• If you modify a currency’s triangulation currency, you must re-enter exchange rates for the triangulation currency property and refresh the application to transfer and store the exchange rates. 
• You cannot select the application’s default currency as a triangulation currency.
• Calculation method is multiply or divide.

SCENARIO AND VERSION - Broadest category of data.
Scenario: - Type of data in a plan, eg., Budget, Actual

• Start Year, End Year, Start Period, End Period.
• When copying,only scenario properties are copied, not data values and access rights associated with the original
• scenario are not copied to the new scenario.
• When deleting scenario, first remove references to scenarios from data forms and assign different scenarios.

Version: - Allows flexibility and iterative planning cycles.

• Allow multiple iterations of a plan
• Model possible outcomes based on different assumptions
• Manage dissemination of plan data
• Facilitate target settings
• Buttom-up Version: 
• Enter data intobottom level members
• Parent level members are display-only and do not permit data entry.
• Parent member values are aggregated from bottom level members.
• Target Version: 
• Enter data for members at any level. 
• Can use business rules to distribute values from parent members to their descendants. 
• Use it to set high-level targets for your plan
• Top-down budgeting
• Members must be store
• Manage Approval Task not allowed.

Dynamic Time Series (DTS)

• Dynamic Time Series members to create reports that show period-to-date data, EG, quarter-to-date expenses. 
• DTS members created automatically during application creation
• Can be used with members of the Period dimension
• Eight pre-defined DTS members
• Y-T-D
• H-T-D
• S-T-D
• P-T-D
• Q-T-D
• M-T-D
• W-T-D
• D-T-D

Smart List

• Custom drop-down lists that users access from data form cells. 
• Cannot type in cells that contain Smart Lists.

UDA (User Defined Attribute)

• Descriptive words or phrases, within calc scripts, member formulas, and reports. 
• UDAs return lists of members associated with the UDA.

Shared Services Roles

• Administrator
• LCM Administrator
• Application Creator
• Dimension Editor
• Calculation Manager Administrator