Hyperion Planning application can get unlocked at times. If, for example, users abnormally exit planning application, or if planning dump import is done without caution, etc, Planning application can be locked.
We can use the UNLOCK APPLICATION UTILITY (HspUnlockApp.cmd) to unlock the application in such situations. The HspUnlockApp.cmd utility is available in the Planning bin, which is usually the "\\PlanningServer\d$\oracle\Middleware\user_projects\epmsystem1\Planning\planning1" directory, and the utility clears all records in the HSP_LOCK table.
These are the steps that need to be taken to run this utility to unlock Planning Application.
Before running the utility, make sure:
1. The users are no users connected to Planning application.
2. Planning services are not running
Process to run the script:
1. Open a command prompt.
2. Change directory to the Planning1 directory, say for example, "\d$\oracle\Middleware\user_projects\epmsystem1\Planning\planning1" where I have the HspUnlockApp.cmd file.
3. Type in HspUnlockApp.cmd servername username password application_name.
4. If asked for password, type it in and ENTER.
5. Check for logs to see if the unlock process was successful or if it failed.
In case the utility does not work, we can clear out the HSP_LOCK table manually and then restart Planning.
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.
Account - Dense
Period - Dense
Entity - Sparse
Year - Sparse
Version - Sparse
Scenario - Sparse
Currency (multi-currency)
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.
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.
@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
User Authentication - asks for username and password
Task Security - users can do only things that they are assigned to
Data Security - not all users can modify data
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)
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.
