Hyperion Planning + OBIEE v/s Jedox EPM/BI suite

Hyperion Planning + OBIEE  v/s Jedox  EPM/BI suite

   

After working in Hyperion suite of products for more than 9 years I got an opportunity to work on another EPM/BI suite of products called Jedox (http://www.jedox.com/en/) . To be honest after being in a relationship for 9 years with Hyperion it was a tough situation to try something new  J .  In past 9 years I rarely got a chance to work on something other than HYPERION ( Worked on MS BI and few other BI tools like Tableau for a small POC and evaluation), so was pretty excited and tensed when I got into Jedox .. So, below are few things on which I can compare Hyperion suite of products (Planning,Essbase, FDM and OBIEE) v/s Jedox .  As I don’t have much exposure to some other Hyperion products like HFM, ODI, don’t think I am a correct person to compare it with any other product. Also, as stated prior about 9 years of love affair with Hyperion I will still try to be as neutral as possible ( That would be tough!!)

 1. Ease of Application Building/ Dimension building :

Hyperion Essbase :  There are multiple ways to build your dimensions in essbase like manually adding members in EAS, using rule files or using some other ETL tools like ODI.

Hyperion Planning:   Again same as Essbase there are multiple ways like using outline load utility, EPM , ODI etc..

Jedox:   Dimension building is pretty simple in Jedox. Similar to Essbase, you can add members manually or using flat files or from RDBMS using in-built ETL. One more flexible option in Jedox is that you can build dimension just by copy+pasting data from excel/text file into Jedox Excel Ad-in.

Overall, both products are pretty developer friendly when it comes to dimension building. One advantage of Jedox over Hyperion is that as it is in-memory product you don’t have to go through painful dense restructures after adding members in your Account/Period dimension. Hyperion gives much more properties tagged to members (Like Skip missing while taking average or Balance account etc) which are missing in Jedox and can be achieved only by using database formula.

 2.Calculation capabilities:

 Hyperion Essbase/Planning: Hyperion has very powerful calculation engine. There are multiple ways to perform complex calculations required for planning/budgeting. With extensive list of functions available you can fulfil almost all kind of business requirements.

Jedox: In Jedox you can perform simple calculations using DB formula (Similar to essbase member formula) for complex calculations you need to use ETL transformations

Hyperion is a clear winner when it comes to complex calculations and ease of doing it. Jedox calculations will be faster, but it has limitations. It is extremely difficult ( sometimes impossible) to perform some of the complex calculations in Jedox ETL which are pretty simple to do in Hyperion.

1. Customization of Look-n-Feel :

Jedox clearly has an upper hand, when it comes to customization. In Hyperion there is very limited opportunity to customize artifacts like user webform or user task lists.  Jedox interface in simple words is like an enhanced excel which you can publish, so possibilities are limitless.

1. ETL capabilities:

Hyperion Essbase/Planning adapters are available in various ETL tools like FDM, ODI, ERPi, EIS (No longer there) . Jedox has its own ETL module which can perform all kind of ETL operations.

No one is a clear winner here. If you want to integrate your EPM with Oracle GL then definitely Hyperion has more options and more in-built capabilities. Jedox ETL is comprehensive as well and can perform most of the ETL tasks.  From licencing standpoint, for Jedox you don’t need to buy one more ETL , whereas in Hyperion in most of the cases you do need to pay for ETL separately.

1. In-Built planning/Budgeting features:

Hyperion Planning gives many out of the box features like tasks lists or workflow which are not available readily in Jedox. But, you can build them as per your requirements with some efforts and knowledge of PHP.

1. BI capabilities:

 Jedox is much better in terms of quick turn-around time and performance when it comes to building BI dashboards and reports. I found building a dashboards and interactive reports more convenient in Jedox as compared to OBIEE.  For basic Financial Reports (generally achieved using HFR in Hyperion ) will take similar efforts in both products. May be in Jedox you can customize them more as it is basically excel report published as Web report.

1. Server Maintenance/Installation :

Jedox has very straight forward installation and it’s pretty simple in terms of server maintenance as well. You won’t require a full time server admin in case of Jedox. Again, with the evolution of oracle cloud and PBCS this might be an invalid point in future. Jedox also has its own cloud offering.   

1. Application Maintenance/Support :

I personally think that supporting/ maintaining  Hyperion application would be simpler as compared to Jedox (Again this might be because of 9 years of in-relationship  status with Hyperion :P ). In a complex Jedox application you certainly need to use custom PHP widgets which can be painful to maintain and support in a long run, whereas in Hyperion customized codes are limited and can be easily handover to support team.

1. Cost :

Jedox licences are much cheaper as compared to Oracle Hyperion licences. Overall Jedox product cost would be half as compared to Hyperion.

 So, overall it’s not really possible to say one product is a clear winner. Both have pros and cons, both have strong points and weakness.  Hyperion is much more matured and robust as compared to Jedox. Jedox is much faster and cheaper than Hyperion. Calculation capabilities of Hyperion are unparalleled; customization capabilities of Jedox are limitless. Depending upon requirements and situation any one of them can be a winner..

Entering TEXT data in Essbase using Text List

Hi All,

  In essbase 11 onwards there is an option to have planning smartlist like functionality in Essbase cube .. This new functionality is called Text List .. Just like planning smartlist, using essbase text list you can enter Text data in essbase also, you can restrict entries as in smartlist. Following is the process for having textlists in Essbase.

1. Enable type measures for essbase database : Go to Outline Properties and enable Typed measures.

Add caption

2. Creating Text List : Just like planning smartlist , create Essbase text list by going into Text List Manager Tab. In below screenshot I have created a text list for inserting ratings.

3. Apply data type as Text and assign TextList : In below example I have assigned Rating Textlist to Rating account.

Now your Essbase tasklist is ready for use .. You can use it just like Smartlist from smartview. Also, using excel ad-in you can simply lock n send text data into Essbase. For calculation purpose just like Smartlist you can use id value ..

Cheerz!
-Chinmay Joshi

Application Error in FDM , after loading maps having "&" or "@" in it ..

Hi,

  This is a strange and known bug in FDM ( All versions from 8 to 11) , if you load any map with having  & in any source member name it just corrupts FDM location ... Root cause of this issue is all FDM maps are stored in RDBMS and for any RDBMS & or @  are  special characters so, it creates problems !!!... This is very irritating thing especially when  you have given access to business users to load maps ..

Solution
1) Open the FDM Workbench Client and login to the application
2) Choose Tools/Table Editor
3) From the drop down list select the tDataMap table
4) In the "Find" box enter the location id for the location where the
mapping template was loaded. Scroll down to the entries in the table with
the DimName 'Account'. In the SrcKey field you will need to locate any
entries that contain reserved database characters (&,@, etc.). You can
delete these rows by highlighting them and clicking the X icon.
5) Click the Diskette Icon to save the changes.

  -Chinmay

Block creation Issue

Hi All,

  Many times while writing Essbase calc scripts you must have encountered an issue of block creation i.e. you are not able to see any results for your simple calculations !...

 Essbase normally create blocks when data is loaded, during aggregation of sparse dimensions
and as a result of a DATACOPY command.

Essbase does not create blocks as a result of most dense member assignment calculations. For
example based on the Sample.Basic outline, assume that “Sales” is a dense dimension member
and actual is a sparse dimension member. The following formula will fail because it will not
create new blocks for Budget.
FIX (budget)
"Sales"="Sales"->"Actual"*1.1;
ENDFIX

 So, in short if you are performing some basic arithmatic calculations, essbase won't create new block for sparse dimensions.. This is done by essbase to improve it's calculation performance but  it is a headache for developer..

 Ways to tackle this issue are:

1.DATACOPY into Sparse Member:
You can use the data copy command to create blocks in the same pattern as the actual
blocks. In above example you can do DATACOPY ACTUAL TO BUDGET prior to writing calc. command... It will first create a block for Budget and then will overwrite a calculated value into it.. You can also fix some combination and always enter some dummy data into it as a part of your essbase process and can use this combination for block creation.

e.g. You can always enter '1' in Actual->Test_Acc and can use it for all block creation datacopy commands..



2. CALCULATION COMMANDS TO ENABLE BLOCK CREATION
You can also use  SET CREATEBLOCKONEQ and SET CREATENONMISSINGBLK commands for block creation. But these commands are bit heavy on your calculation and degrades calc script performance so for big calc scripts it is always advisable to use DATACOPY approach...

 Cheerz!
--CJ

Hyperion HBR Run time Prompt (RTP) issue.

Hi,

  Issue:

In Planning webform HBR is attached , RTP are used and are set as "Run on Save" , "Use Member on form" and "Hide Prompt", but still when we hit save button a pop-up appears asking for RTP members.

Solution:

1. Make sure that RTP type is set as 'Member' and not 'Members' ... RTP with type as 'Members' will always give a prompt  as it is used to pass more than one member of a dimension.

2. If still problem exists...

 2.1.  Delete the RTP from BR

 2.2.  Create Local Variable RTP

 2.3.  Remove security and save BR.

 2.4.  Assign security and Save BR again.

 

Cheerz! :)

-Chinmay-

Design Essbase ASO aggregation

Hi,

ASO cubes are by default dynamic in nature i.e. in ASO all aggregations happen on the fly .. So data is stored only at Lev 0 .. For small ASO cubes it gives pretty good performance in reporting, but for huge ASO cubes ( Generally which is the case) report performance is a concern ..

'Design Aggregation' feature of ASO is a good option provided by Essbase .. In aggregation data is calculated at upper level and stored, so data retrieval becomes much faster.. Following are the steps for designing aggregation for ASO cubes

1. Enable 'Query Tracking' :  Query tracking will track data  combinations which have more data values and which requires Agg to improve performance..
e.g. If you have 2 parents with 100 children each, then Query tracking will keep track of which all children have data for some combinations and will suggest in future required aggregate views .

2. Go to 'Design Aggregation'

3.  First select  Use recommended views to materialize aggregation .. It will show all the views selected by system .. It is a good practice to include them first in Agg view definition ... Select all these views and save and materialize them .

 

 

4. Go back to 'Design Aggregation' and now select second option .

5. Select  'Select all recommended aggregate views' .. You can specify total storage space in this screen

 

 

6. Select 'Use query Tracking data during view selection' (Option present at bottom) and click on Start .. It will show all views tracked by system using Query tracking ..

7. Save and materialize views..

 

 

 

8. Repeat these steps once you load any new data and save new aggregate views in existing saved aggregation..

If you change any dimension name or change property of member like if stored becomes Label Only or calculated one , then entire Aggregation becomes invalid and you need to re-do this process..

-Cheerz -- :)

Error while connecting to essbase from FR

Hi,

This is the error that me and and some of my friends faced while connecting to Essbase from Financial Reporting Studio, while creating grid... Logging into FR studio is working fine that means HSS is up and running but while creating a report in FR when we drag a grid and try to connect to some essbase server it is throwing an Error: " Can't connect to server" so, new server connection is not getting established. Here is the solution :

1. Check if you can connect to any other Essbase server from Studio. If yes, then check if you are using correct login credentials to connect to essbase. Also, try to connect to that essbase server using your login credentials from Excel Add-in or SmartView or AAS.

2. Check if all BI+ Services are up and running on Server.

3. Stop and Start BI+ services in the following order:
1. BI+ Core Services
2. Workspace
3. Financial Reporting Java RMI
4. Financial Reporting Print Server
5. Financial Reporting Report Server
6. Financial Reporting Scheduler Server
7. Financial Reporting Web Server

5. If still problem exists, run config utility for BI+, and re-configure Database with Drop all tables option.

Cheerz!
-CJ

Book on Essbase Implementation

Hey all,

Check this : New book for essbase implementation:




For more info..Check this link :

http://www.packtpub.com/oracle-essbase-9-implementation-guide

A great new book by Joseph Sydney Gomez, Sarma Anantapantula on Oracle Essbase 9 Implementation .. Looks impressive.. I think it is first of it's kind implementation guide on Hyperion Essbase ... Must buy for Hyperion practitioners and people who wants to jump into Essbase Space ! :)

Cheerz!
-CJ-

User/Groups are not visible in Planning Webform and Dimension while assigning access.

Hi All,

This problem has been faced by many folks..Many times when you click on Assign Access for webforms/Dimension Members. There can be many reasons for this.

1. Check all the services are running fine.. Hyperion services create problems most of the times.. So, just restart services in order, it works in most of the cases.

2. Some issue with RDBMS, so just catch your DBA to get work done.

3. Some mis-sync between LDAP - SS . So, Try going to http://localhost:58080/interop/framework/configLogin
Login
Configuration > Sync OpenLdap

4. Just check that users have access to planning app and users are planners and not Admins, as admins won't appear in the list (I know this reason sounds funny but one of my friend made this mistake and we tried a lot of things).

Cheerz!

--Chinmay

Update on Hyperion Planning dim deletion

Hey!

One of my friend tried deleting Planning Dim using procedure given below on 9.3.1 , and it worked!!! :)

--Chinmay

How to delete dimension from Planning Application

This procedure should only be performed by your SQL or Oracle DBA.

** THIS PROCEDURE IS TRIED AND TESTED ONLY ON 9.2 VERSION

BE SURE YOU HAVE A BACKUP OF YOUR APPLICATION BEFOREATTEMPTING THIS PROCEDURE.

You will need to delete all the children first, then the dimension.

If you have the dimension in forms, you may also need to (after step 5) go into the hsp_formobj_def_mbr table and delete all references of that object ID. Then delete the dim id in thehsp_form_layout table. Recycle services of Planning.
Procedure: If you create a custom dimension in planning and wish to delete it, here is how you might remove it from SQL manually. We recommend that only someone familiar with relational databases attempt this.
The following assumptions are being made about the dimension: No forms have any references to thedimension. Any and all attribute dimensions have been removed from the dimension. Any and all members have been removed from the dimension. All security access has been removed from dimension. Any Alias associated with the dimension has been removed.

In this example we have created a dimension called DummyDimension.

1. Open the Enterprise Manager for SQL server
2. Open the database that has your planning application
3. Open the table HSP_OBJECT
4. You need to find the row that has the dimension name you want to delete:
5. Take note of the OBJECT_ID in my example it is50051, don't delete it yet.
6. Open the table HSP_MEMBER
7. Find and delete any rows that have a MEMBER_ID equal to the OBJECT_ID from step 5.
8. Close the Table HSP_MEMBER
9. Open the table HSP_MRU_MEMBERS.
10. Find and delete any rows that have a DIM_ID equal to the OBJECT_ID from step 5.
11. Close the table HSP_MRU_MEMBERS.
12. Open the table HSP_DIMENSION
13. Find and delete any rows that have a DIM_ID equal to the OBJECT_ID from step 5.
14. Close the table HSP_DIMENSION
15. Also delete it's reference from the HSP_UNIQUE_NAMES table.
16. Delete the row in the HSP_OBJECT table that you found in step 4.
17. Close the HSP_OBJECT table.
18. Open Essbase Application Manager for the application and remove the dimension from the necessary Essbase outlines.
19. Restart the application server.

Now when you open the application in Planning the dimension will be removed. The next step is to perform a refresh to make sure planning and Essbase are in sync.

Committed access in Essbase

Hi,

Committed access option provided by Hyperion essbase proves very helpful in many cases.First letz understand what is committed and uncommitted access... Committed access means while loading data into essbase , it first stores data in memory and once wait period is over it loads data into physical disk. It is synonymous to Commit access in SQL.Essbase works in similar fashion..You can set committed/uncommitted access in DB properties under transactions.



There are some real scenarios when data load if interrupted between needs to restart from beginning, in essbase we can achieve this by setting committed access and then click on "Abort on error" while data loading.

Restoring Data from Backups

Restoring Data from Backups


To restore data from backup:
1.Stop OpenLDAP service or process.

2.Using a command prompt window, navigate to /server/scripts and execute the following command.

Windows:
recover.bat

UNIX:
recover.sh

In this command,
Path_backup_directory indicates the path of the directory where the backup is stored.

3.Monitor the utility to ensure that it runs successfully.

4.Start OpenLDAP.

5.Restart Shared Services.

Backing Up OpenLDAP Database

Backing Up OpenLDAP Database

The OpenLDAP database must be backed up periodically to recover from loss of provisioning data due to media failures, user errors, and unforeseen circumstances.
There are two types of backups we can perform : Hot Backup and Cold Backup

Hot Backup
Regular incremental backups of OpenLDAP database can be performed without shutting down
OpenLDAP. Known as hot backups, they do not interfere with the availability of Shared Services.
Use
backup.bat
(Windows) or
backup.sh
(UNIX) to schedule daily hot backups. This
Hyperion-supplied backup file is stored in
/server/scripts
; for example
C:\Hyperion\SharedServices9\9.2\server\scripts
(Windows) or
/apps/Hyperion/
SharedServices9/9.2/server/scripts
(UNIX).
This procedure backs up Shared Services configuration files and OpenLDAP.

To run a hot backup:
1.Using a command prompt window, navigate to /server/scripts.
2.Execute the following command.

Windows:
backup.bat

UNIX:
backup.sh

where
backup_directory
indicates the path of the directory where the backup is to be stored.

3.Monitor the backup process to ensure that it runs successfully.



Cold Backup
Cold backups are taken after shutting down OpenLDAP.
Note:
Data in the OpenLDAP database is synchronized with the data available in the Shared Services
repository. Hyperion recommends that you back up the Shared Services repository along with the OpenLDAP database.

To back up OpenLDAP database:
1.Stop OpenLDAP service or process. 
2.Copy into a secure location.

Synchronizing OpenLDAP Database with Shared Services Repository

Synchronizing OpenLDAP Database with Shared Services Repository

The database configured with Shared Services stores product registration information, and the
OpenLDAP database contains provisioning data for Hyperion System 9 products. These
databases work in tandem to support Hyperion System 9 products.
Data inconsistencies between the databases impact normal operations. Inconsistencies could occur during manual database update, database upgrades, or in replicated OpenLDAP environments in which the OpenLDAP slave has taken over for a failed OpenLDAP master. To remove inconsistencies, the OpenLDAP database must be synchronized with Shared Services database. The synchronization process uses the Shared Services database as the master database to resolve data inconsistencies.
Use the OpenLDAP Sync utility in the following situations:

Product registration fails after the application was deleted from Shared Services
Shared Services database is recreated manually without recreating OpenLDAP, and Shared Services backup is not available
OpenLDAP database is recreated manually without recreating Shared Services database, and Shared Services backup is not available


The Sync OpenLDAP utility does not synchronize user and group provisioning data on Hyperion System 9 products.

To synchronize OpenLDAP database with Shared Services repository:
1.Log on to External Authentication Configuration Console as Shared Services Administrator.
2.Select Configuration > Sync OpenLDAP.
3.Optional: Click Refresh to update the status.
4.Optional: Click View Log to display Sync OpenLDAP Logthat details the operations that were performed
during the synchronization process.
5.Click Close.