Installing Hyperion EPM on a Linux box – Delving into X servers

In this blog, I will be talking about installing Hyperion EPM on a Linux blog. Since a Linux/Unix box may not always have a GUI associated with it, the installation of Hyperion EPM can be a bit more fun than the straight forward Windows install. In order to do an install on a Linux box, we would need the following components: -

• An X Window System. We would be using XMing as an X Window System. An X Server is a program in the X Window system that runs on local machines (i.e. a machine directly used by an end user) and handles access to the graphical cards, display screens and input devices. The X window system is a complete, cross-platform and free client-server system for managing GUI’s on single computers or networks of computers. Simply put, the X Window system will basically act as the endpoint for all GUI operations on a machine that does not have its own GUI. XMing is an X11 display server for the Windows OS.
• An SSH connection to the remote Linux machine on which the installation is being done. In order to connect to the Linux box, we would be using Putty which is a free and open source terminal emulator.

The next snapshots show me installing the XMing Display server on my local machine which is a Windows box. 

      

The Linux box on which I am trying to connect is a Red Hat Enterprise Linux machine with machine name RedHatLinux and having IP address 10.0.2.7. This is shown in the next snapshot. 

The next snapshot shows me connected to the remote machine and running a basic sanity check command on the Linux box. 

The next step is to configure the XLaunch on the local machine from which I would be running the install. The steps are shown in the next snapshot. 

Observe the display number in the above snapshot. This is the XServer that will be used by us for X11 forwarding configuration.    

On clicking the Finish button in the above snapshot, the XMing server is started as shown in the below snapshot.

Now, the next snapshot shows the login screen for Putty. 

Expand the SSH in the above snapshot to configure the X11 port forwarding as shown in the next snapshot.

The XDisplay location is set as <IP Address of the local machine>:<display number set in XLaunch>
Since the IP of my Windows box is 10.0.2.10, I set up the X display location as 10.0.2.10:0

(In layman term this means, any GUI component on 10.0.2.7 should be redirected to the X Display location machine 10.0.2.10 to the display server on that machine which is 0)
 

The next snapshot shows me running the EPM installer start shell script, installTool.sh.
 

Observe that the display is running on the local machine/Windows box.

The next snapshot shows the configuration of my environment.   

The next snapshot shows me running through the steps of the install. 

Observe that the installation is successful as shown in the above snapshot. Thus, we were able to successfully run an install on a Linux box using the X window system and Putty.

FDMEE Logic Accounts – Moving sum of bank balances to either Cash-in-hand or Overdraft in Bank using FDMEE

Well, in this blog, which is my second one covering logic accounts in FDMEE, I talk about implementing bank balance movement to either Assets or Liabilities in the balance sheet using FDMEE. I loved working on this requirement since it showed internally how powerful a simple tool like FDMEE is in terms of functional clarity that it brings about.

So, lets jump onto the requirement. The requirement is simple. Suppose I have a set of 100 accounts which represent my bank accounts for different entities. The requirement is that I need these 100 accounts to be parked in the correct place (Assets or Liabilities) based on whether the sum of these 100 accounts is positive or negative. If the summation of bank balances is positive, it moves as an Asset to “Cash in Hand” account whereas if the sum is negative, it should move to Liabilities under “Overdraft in Bank”

The next snapshot shows the sample file that I have for my Bank account balances.

The next couple of snapshots show the FDMEE data load rule and the workbench after the file has been imported successfully.

 
The next snapshot shows me creating a Logic account using a Complex logic.

I add an entry into the Logic group for the summation of the bank balance accounts as shown in the below snapshot.

The criteria for the logic accounts is as shown in the below snapshot.

The above criteria can be logically treated as two steps.

• Whenever the Account name begins with “BankAccount”, I replace it with SumCash-Logic. Whenever I have any Entity and Version, I replace them with Entity_NA and Final respectively.
• Once this mapping happens I aggregate the mapped data to get the final SumCash-Logic value.

The next snapshot shows me tagging the logic account group to a location. 

On running the import, observe that the Logic account data has come through as shown in the next snapshot. 

Now the next step is to ignore the individual Bank Balance accounts, which is do with a simple rule and map only the logic account for Cash. After applying the new mappings, the data load workbench is as shown in the next snapshot. 

In order to implement the movement to either Assets or Liabilities, I now create a SQL mapping as shown in the below snapshots. 

The snapshot of the data load workbench after importing the data once again is as shown in the below snapshot. Observe that the data has move to the “Cash In Bank” account after FDMEE mappings

Now, let us check if the ‘Overdraft in Bank’ is working as expected. I update the file to make most of the data negative as shown in the next snapshot. 

On running the import once again, observe that the data is now parked in ‘Overdraft in Bank’ which would be under Liabilities.

 

FDMEE - Logic accounts – Why you should love them

In this blog, I would be talking about logic accounts in FDMEE and why you should love them…FDMEE is designed as an ETL tool for Hyperion systems which is a known fact. However, what you should also remember is that FDMEE is a much more finance-friendly tool than we give credit to it… In a couple of blogs, I will be showing FDMEE being used to solve real world finance problems. So let us begin.

Recently, I had an Integration requirement which was as follows. When data comes in for specific accounts from the GL into the Planning application, they needed it to be loaded into two accounts, one as is and one of it as a negative offset of the same value. So suppose my source account is 1100 with a value of 100. This data from 1100 should go into an account PL1100 as is and into account PL1105 as negative offset which is -100.

Now, there are different ways to implement the same requirement within FDMEE, but I chose to implement the same using Logic accounts with simple logic. The logic that I used is as shown in the below snapshot.

Logic accounts are basically used to create accounts that are not generated by the source systems.

The logic account I have created is explained as follows. Whenever I have an account like ‘PL1510’, I should create a logic account called ‘PL1510-Offset’ and export it.

The next snapshot shows me tagging the logic account to a location.

When I now import the data, observe that a logic account for the PL1510 has come through as shown in the next snapshot.

I have not done any mappings for this member as of now.

The next snapshot shows me creating an explicit mapping for this member. Observe that the “Change Sign” is ticked for this account.

After recalculating the data, observe that the data mappings are reflected correctly as shown in the below snapshot.

If you observe the above snapshots, we did not do any fancy coding or such. It is all very basic and can be done by non-IT people with relative ease. This ease of maintenance is the power that FDMEE brings to the table. Tomorrow, if we need to add a new account or change the mapping for the existing one, it is very simple and does not take a lot of development effort.

Essbase Log File parser – Shell script

In this blog, I will be publishing the Essbase log file parser shell script that I have created. I had designed a JAR file for the same. But I know that many a times people have issues with using jar files in production environment that is downloaded online. So, open source is the way to go… I will be publishing the shell script structure which I used for designing the Essbase log file parser. This is one version of the script. There are other ways to do the same as well…

Now, in order for the shell script to work you would need sed which is a stream editor and tr utility. Most Unix/Linux environments come with these utilities so we are in the green here.

Now this is the structure of the Essbase log file.

The shell script that will parse the log file will basically send the output to a new log file called parsed_essbase_log,txt

The shell script to do the parsing is as shown in the below snapshot.

Logic of the script:

• Replace a line which is blank i.e. length is 0 with ###
• Convert new line characters into space so that the file loses individual line markers.
• Replace ### with a new line character.
• Replace the first instance of a closing bracket “)” with a “)”+TAB.

Usage:
To run the script, you need to give the name of the Essbase log file as a parameter while running the script.
 

sh -x <scriptname.sh> <full path of the log file>

The log file after being parsed is as shown in the below snapshot. 

This log file is generated in the directory where the shell script is placed.

Disclaimers:

• This is very basic operation of the shell script. It formats and gives you a readable output in seconds.
• The script is not in place since the existing log file is kept as is and a new log file is created. Thus is the size of the log file is n bytes, you need atleast 2n bytes for running this script.
• It does basic script formatting for you. More customizations can be done by adding additional fields using this script.

Script:

The shell script for formatting the logs is as follows:

# Script designed by Sibin Jose for Hyperion Essbase log file parsing

echo "Hyperion Essbase log parser"

if [ $# -eq 0 ]
then
 echo "Incorrect possitional parameters... Usage: log_parser.sh <PATH OF LOG FILE>"
exit 1
fi

export ESSBASE_LOG_FILE=$1
echo ${ESSBASE_LOG_FILE}

#export PARSED_ESSBASE_LOG_FILE=`echo ${ESSBASE_LOG_FILE} | sed 's/.log/_parsed.log/g'`
#echo ${PARSED_ESSBASE_LOG_FILE}
sed 's/^$/###/g' ${ESSBASE_LOG_FILE} | tr '\n' ' ' | sed 's/###/\n/g' | sed 's/)/)\t/1'  > parsed_essbase_log.txt
echo "THe script has finished executing...Please check the folder for parsed_essbase_log.txt file"

Hyperion Shared Services – Getting Provisioning Information for users and groups for a particular application

In this blog, I would be talking about how to the get the provisioning information for users and groups for a particular application by designing some queries that hit the backend relational tables of Hyperion Shared Services. 
I am not a fan of the provisioning report generated by Hyperion Shared Services so this is my stab at the report to generate some good formatted reports.

The below two snapshots show me provisioning two groups and giving them Planner and Mass-Allocation access to the respective groups.

Now, the provisioning information is stored in a table called as CSS_PROVISIONING_INFO which is as shown in the below snapshot.

The list of users provisioned to the Hyperion Planning REVPLAN application is as shown in the below snapshot.

The metadata structure for the CSS_PROVISIONING_INFO table is as shown in the below snapshot.

The data that is present in the CSS_PROVISONING_INFO table pivoted for reference is present in the below snapshot. 

Now, the MEMBER_IDENTITY in the above snapshot refers to the user or group provisioned for access. This information will come from either the CSS_USERS or CSS_GROUPS table.

The ROLE_IDENTITY table basically comes from the CSS_ROLES_LOCALE table.

Now that we have got the provisioning information, the real trick would be to get the list of users and groups who are provisioned for access to this application.

The below SQL query can be used for getting the list of users who are provisioned for access to the application. The query basically joins the CSS_USERS, CSS_ROLES_LOCALE and CSS_PROVISONING_INFO table to get the provisioning details is a nice format. 

The output on running the above SQL query is as shown in the below snapshot.

The next query shown in the below snapshot can be used for getting the lists of groups provisioned for access to the REVPLAN application.

The output of this query is as shown in the below snapshot.

Now, if we do a UNION of the above two queries, we can get a list of all the users and groups provisioned for access to REVPLAN application.

The output of the join query is as shown in the below snapshot.

SQL queries: -
 
To get a list of users provisioned for access to an application, use the following SQL: -

/* Users Provisioned for access to the application */
 
select CSS_USERS.NAME,CSS_ROLE_LOCALES.NAME,APPLICATION_ID, MEMBER_IDENTITY, ROLE_IDENTITY, MEMBER_TYPE  FROM CSS_PROVISIONING_INFO, CSS_ROLE_LOCALES,CSS_USERS
WHERE LOWER(CSS_ROLE_LOCALES.IDENTITY_ID)= LOWER(CSS_PROVISIONING_INFO.ROLE_IDENTITY)
and CSS_PROVISIONING_INFO.APPLICATION_ID='HP:REVPLAN'
and CSS_ROLE_LOCALES.locale='en'
AND CSS_PROVISIONING_INFO.MEMBER_TYPE=1
AND lower(CSS_USERS.IDENTITY_ID)=lower(CSS_PROVISIONING_INFO.MEMBER_IDENTITY)
To get a list groups provisioned for access to an application, use the following SQL: -

/* Groups Provisioned for access to the application */
 
select CSS_groups.NAME,CSS_ROLE_LOCALES.NAME,APPLICATION_ID, MEMBER_IDENTITY, ROLE_IDENTITY, MEMBER_TYPE  FROM CSS_PROVISIONING_INFO, CSS_ROLE_LOCALES,CSS_GROUPS
WHERE LOWER(CSS_ROLE_LOCALES.IDENTITY_ID)= LOWER(CSS_PROVISIONING_INFO.ROLE_IDENTITY)
and CSS_PROVISIONING_INFO.APPLICATION_ID='HP:REVPLAN'
and CSS_ROLE_LOCALES.locale='en'
AND CSS_PROVISIONING_INFO.MEMBER_TYPE=2
AND lower(CSS_GROUPS.IDENTITY_ID)=lower(CSS_PROVISIONING_INFO.MEMBER_IDENTITY)

The consolidate ed list of users and groups provisioned for access to an application is as shown in the below SQL: -

/* Consolidated list of users and groups for an application*/

(
select CSS_USERS.NAME,CSS_ROLE_LOCALES.NAME,APPLICATION_ID, MEMBER_IDENTITY, ROLE_IDENTITY, MEMBER_TYPE  FROM CSS_PROVISIONING_INFO, CSS_ROLE_LOCALES,CSS_USERS
WHERE LOWER(CSS_ROLE_LOCALES.IDENTITY_ID)= LOWER(CSS_PROVISIONING_INFO.ROLE_IDENTITY)
and CSS_PROVISIONING_INFO.APPLICATION_ID='HP:REVPLAN'
and CSS_ROLE_LOCALES.locale='en'
AND CSS_PROVISIONING_INFO.MEMBER_TYPE=1
AND lower(CSS_USERS.IDENTITY_ID)=lower(CSS_PROVISIONING_INFO.MEMBER_IDENTITY)
)
UNION
(
select CSS_groups.NAME,CSS_ROLE_LOCALES.NAME,APPLICATION_ID, MEMBER_IDENTITY, ROLE_IDENTITY, MEMBER_TYPE  FROM CSS_PROVISIONING_INFO, CSS_ROLE_LOCALES,CSS_GROUPS
WHERE LOWER(CSS_ROLE_LOCALES.IDENTITY_ID)= LOWER(CSS_PROVISIONING_INFO.ROLE_IDENTITY)
and CSS_PROVISIONING_INFO.APPLICATION_ID='HP:REVPLAN'
and CSS_ROLE_LOCALES.locale='en'
AND CSS_PROVISIONING_INFO.MEMBER_TYPE=2
AND lower(CSS_GROUPS.IDENTITY_ID)=lower(CSS_PROVISIONING_INFO.MEMBER_IDENTITY)
)
To get the list of all users and groups provisioned for access in the Hyperion environment, use the following SQL: -

/* Consolidated access list for users and groups */
(
select CSS_USERS.NAME,CSS_ROLE_LOCALES.NAME,APPLICATION_ID, MEMBER_IDENTITY, ROLE_IDENTITY, MEMBER_TYPE  FROM CSS_PROVISIONING_INFO, CSS_ROLE_LOCALES,CSS_USERS
WHERE LOWER(CSS_ROLE_LOCALES.IDENTITY_ID)= LOWER(CSS_PROVISIONING_INFO.ROLE_IDENTITY)
and CSS_ROLE_LOCALES.locale='en'
AND CSS_PROVISIONING_INFO.MEMBER_TYPE=1
AND lower(CSS_USERS.IDENTITY_ID)=lower(CSS_PROVISIONING_INFO.MEMBER_IDENTITY)
)
UNION
(
select CSS_groups.NAME,CSS_ROLE_LOCALES.NAME,APPLICATION_ID, MEMBER_IDENTITY, ROLE_IDENTITY, MEMBER_TYPE  FROM CSS_PROVISIONING_INFO, CSS_ROLE_LOCALES,CSS_GROUPS
WHERE LOWER(CSS_ROLE_LOCALES.IDENTITY_ID)= LOWER(CSS_PROVISIONING_INFO.ROLE_IDENTITY)
and CSS_ROLE_LOCALES.locale='en'
AND CSS_PROVISIONING_INFO.MEMBER_TYPE=2
AND lower(CSS_GROUPS.IDENTITY_ID)=lower(CSS_PROVISIONING_INFO.MEMBER_IDENTITY)
)

The consolidated lists of access for my environment is as shown in the below snapshot.