Wednesday, June 25, 2014

How to Identify Employee and Positions with Missing Allocations with Public Sector Planning and Budgeting Application

Initial Draft:-Tejas Patel                                                                  Review & Updated By:-Sanjay Purohit

Audience: Hyperion Solution Architects, Functional Lead and Hyperion Business users
Hyperion Public Sector Planning and Budgeting (PSPB) application comes with three prebuilt configuration options.


Employee Only: - This model is used to track and budget/compensation by employees who are assigned to various jobs and benefits.

Position Only: - This modes is used to tack and budget/compensation by positions. The compensation amount will be based on overall FTE and benefits.

Employee and Position: - Most commonly used model by municipal and state governments. This model tracks budget by employee with assigned position and benefits.

Any configuration model can be selected based on organizations need.  Oracle has spent good amount of money and time to come up with PSPB design and options. All three modules are loaded with prebuilt business rules and data forms; and should meet most of the requirements out of box but we don’t live in perfect word and each client has specific needs which require customization. I personally don’t like to customize HCP at all.

Objective

The purpose of this article is to identity positions and employee in PSPB application with missing allocations. Hyperion Public Sector Planning calculates position and employee compensation on various factors such as salary grades, hours and FTEs.  The final compensation numbers are pushed to LineItem plan type. The assigned segments determine how the cost should be allocation across segments. The right amount should go to right bucket in General Ledger (GL).  Checking allocation of each position and employee is tedious process with current pre-built HCP functionality.  I am going to demonstrate; how to build a data form to identify the missing allocation by departments/entities. We will cover two modules “position” and “Employee and Position” module. I personally have expertise with both.

solution

Employee and Position Module/Configuration: - Design a data form with following member combinations. This should provide the position/employee with missing allocation.  This data form pulls all the position and employee associated with entity. Each position has a start date and should have allocation start date.  Missing start date denotes missing allocations.




Position Only Module/Configuration: - Design a data from with following member combinations. This should provide the position with missing allocations. I have placed entity on rows to see all the entities once. This could cause planning crash. You also can place entity on pages items to be on safe boat.  




Thursday, June 19, 2014

Hyperion Financial Management- Unable to create application due to “TNS: could not resolve the connect identifier specified”

Author: - Sanjay Purohit

Audience:  Hyperion Solution Architects and Hyperion Database Administrator.
I installed Hyperion Financial Management (HFM) on my server and was very excited to have my own dedicated HFM server. I really did not find any difficulty during installation and configuration.  The entire process went smooth and I was wondering why I did not encounter an error!! I should have got at least one. My installation was single server windows 2008(R2) installation. All the components were in same box. I also installed Oracle Database 11g on the same windows machine and did not realize the possible implication/setting with HFM. The HFM needs Oracle Database client to be installed on same server to connect to relation repositories but I had database on same server so thought not to install clients. I am not relation database person therefore It took me long time to figure out the exact relationship between Hyperion installed client and oracle home.  I did not realize that if I don’t install clients I have to use existing clients. After finishing up configuration I tried to create a new application using sample application and finally got an error!!!

Error
During the application creation you might encountered an error “TNS: could not resolve the connect identifier specified“. 

I goggled but could not find any specific to HFM since it’s an Oracle Database related error; most of the searches were database related. Then I finally decided to dig into Oracle installation and configuration guide to get the solution.  

REASON
Hyperion Financial Management requires database client to be installed on same server. EPM installation also installs Oracle client by default if you install HFM. However it‘s optional.  The existing installed client can be used.  Oracle HFM supports both the methods. The Oracle Database clients establish connection between HFM and Oracle Database. Please refer below URl for more details:-
 I will demonstrate the process of using existing client to create HFM application.

solution
HFM configuration process populates tnsnames.ora files file under the EPM_ORACLE_INSTANCE/user_projects/config/dbclient/tnsnames location. This file contains the HFM configuration TNS entries
The goal is to move the above TNS entries to existing database client so it can leverage both. We will copy above TNS entries to existing tnsname.ora files and the files should look like below.  The location of tnsname.ora can be different depending on version of make or version of database
After making the changes you will need to restart HFM services.  You should be able to create application with no error


Note:- The Location is

C:\Hyperiondb\product\11.2.0\dbhome_1\NETWORK\ADMIN
C:\OracleHyp\Middleware\user_projects\config\dbclient




Wednesday, June 11, 2014

Resetting Hyperion Admin Password if you forgot

Author: - Sanjay Purohit

Audience:  Hyperion Solution Architects and Hyperion Database Administrator.
I received a phone call from one of my coworker regarding resetting Hyperion admin password.  He installed Hyperion on a server.  He also prepared an installation document and handover to client for review.  The client was kind enough and did not user Hyperion for a while and also lost installation document.  After few months- my friend received a phone call from client asking Hyperion admin password and he also forgot. He needed to reset the Hyperion admin password. This posting is dedicated to my friend JP.  


objective
The goal of this posting is to “Reset Hyperion Work-space admin Password”.  Hyperion encrypts user friendly language and stores the password on shared services schema. All the native users can be seen on “CSS_USERS” table.  Resetting the admin password is an easy task and can be achieved little knowledge of relation database. 
Login to Shared Services Schema ( The user you used during the shares services configuration) with SQL developer or any other tools and look for “CSS_USERS” table.


 Run the following command



update CSS_USERS

set PASSWORD= '{SSH2}mpQoG4jU00otFocLZM6CWWUzpk9ZmSY5In3l3ZzFCH5byRhkG6ow7z4KxXmXWXSo'
where name='admin';

This will setup the admin users password to ‘password'

Note:-
To set the password to ‘welcome1’ =
‘{SSH2}FvSZVSvSmzvjT/02Nn1TrJyQk8SqNus4Fw4m2cNxa3zkSLe8HFiJy7cT8zUDGiWi’
To set the password to ‘password’ =
'{SSH2}mpQoG4jU00otFocLZM6CWWUzpk9ZmSY5In3l3ZzFCH5byRhkG6ow7z4KxXmXWXSo'