Hyperion Planning - DB issue

2012-06-13T14:50:00.000-04:00

PLANNING APPLICATION CREATION - VERSION 9.3.1 - REVISITED

Today I happened to revisit on the procedure for creating a Planning application using version 9.3.1.
As you are aware of, I followed the regular text book procedure given by oracle for creating the application. Here are the steps which I followed while creating the new PLAN application called as "TESTAPP".

· I created a blank database on the SQL Server environment called "TESTDB"

· Then, I went ahead with the data source creation using the Hyperion Configuration utility. Refer screenshot below.

· The data source will be pointing TESTDB database (as mentioned in the configuration utility screenshot above).

· I reused an existing ID on the DB called as "TESTUSR" after provisioning the ID with the db_owner privilege

Once I happened to complete the initial setup, I went ahead to create the application using the regular planning AppWizard URL. I went ahead with the default options / selections as I was refreshing my memory on the application creation procedure (in order to help one of my friend). The moment I hit the "FINISH" button, I encountered an Error saying that “THE SQL OPERATION FAILED. CHECK LOG FOR DETAILS".

I started troubleshooting this issue and checked the SystemErr.logand SystemOut.Log. All that I could find is the snippet below at the end.

---- Begin backtrace for Nested Throwables
java.sql.SQLException: [Hyperion][SQLServer JDBC Driver][SQLServer]The statement has been terminated.

at hyperion.jdbc.base.BaseExceptions.createException(Unknown Source)
at hyperion.jdbc.base.BaseExceptions.getException(Unknown Source)
at hyperion.jdbc.sqlserver.tds.TDSRequest.processInfoToken(Unknown Source)
at hyperion.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)

The interesting thing (or the so called "WEIRD" thing) that happened as an additional impact of this is application creation is that it deleted all the records on another database called "SAMPLE" on the same SQL Server environment, which I created earlier for whole different purpose (an OLD planning application).
Interesting, huh................
My brain lightened up on finding a solution for this interesting issue and forced me to quit all the other work which I was supposed to do on the later part of the day.

I started analyzing from the DB point of view, since the planning was throwing an error that it could not perform the SQL operation. I double checked the privileges and everything looked appropriate while I noticed an interesting thing which I assumed could be the root cause for this issue.

The SQL Server ID which I used for creating the planning application (data source) is defaulting to the database called "SAMPLE" which got erased in the process of application creation, which I assumed to be the root cause of the error.

Since the application creation failed on the TESTDB database due to various unidentified reason (still under research), the DROP and CREATE statements (which is internally issued by the Planning application creation wizard) got executed on the default database (SAMPLE) assigned to the user id TESTUSR.

I'm not convinced till now on whether my assumption is right or wrong. I opened a discussion thread on the ORACLE FORUM in parallel to my on-going analysis. Eagerly looking forward on whether my brain is going to identify and prove my assumption right it before a Hyperion Expert says the other way around... Keeping my fingers crossed...:)

By the by, Lesson Learnt / Resolution to avoid this in future:
Creating an independent DB User ID and assigning it as an OWNER to the DB created (for planning application). In other words, the application needs to be created with a separate USER ID that references only one database at a time.
I've tried creating an application using the recommended approach and everything went fine..... Still in search of a solution for the issue I faced.

Please provide your valuable feedback or suggestion... Thanks....

EPM - HYPERION

Hyperion Planning - DB issue