Unable to connect to database. Please check database information and verify database is accessible - in Hyperion FDM 11.1.2.3

Unable to connect to database. Please check database information and verify database is accessible - in Hyperion FDM 11.1.2.3

To resolve the above isssue, we need to follow some steps when we use Oracle as RDBMS.

1. Create user with the following Roles and Privileges

Roles: DBA, Resource, Connect

Privileges: CREATE ANY SYNONYM,CREATE CLUSTER,CREATE INDEXTYPE,CREATE PROCEDURE,CREATE SEQUENCE,CREATE SESSION,CREATE TABLE,CREATE TRIGGER,CREATE TYPE,CREATE VIEW,DROP ANY SYNONYM

2. Copy the tnsnames.ora file info from 

C:\app\hypadmin\product\11.2.0\dbhome_1\NETWORK\ADMIN (tnsnames.ora)
to
C:\Oracle\Middleware\user_projects\config\dbclient (tnsnames.ora)

C:\Oracle\Middleware\user_projects\config\dbclient:

 C:\app\hypadmin\product\11.2.0\dbhome_1\NETWORK\ADMIN:

Now try to Create a FDM application again

3. Upto 11.1.2.2 version, we need to install Oracle Client 32 bit first, then 64 bit Client(full clients) for Oracle Database(RDBMS).But for 11.1.2.3 version no need to install clients separately, hyperion will install clients automatically.

Thanks,

Mady

Update Substitution variables automatically using batch script

Update Substitution variables automatically using batch script

   Description: Using single batch script, we need to update

• If we enter Current Month(CurrMonth), then automatically Previous month(PriorMonth), Next Month(NextMonth) will update automatically.
• If we enter Current Year(CurrYear), then automatically Previous Year(PriorYear), Next Year (NextYear) will update automatically.
• Need Prompt to enter Month and Year
• Need Output: for example if we enter month and year,

Ex: Enter Month: 01             Enter Year: 10

      Current Month: jan         Current Year: FY10

      Previous Month: dec      Previous Month: FY09

      Next month: feb             Next Year: FY 11

                                Variables in Essbase, which we need to update.

                                 
                                          Preparing Encrypted Username and Password: 
                                                     Generate Public and Private keys using essmsh -gk

                             Note: Public key is for Encryption, Private key is for Decryption

                       Encryption:

                       Create one text or maxl script with username and password (login credentials of the Essbase)

1                                     

                              Encrypt Username and password:  (Using Encryption Code)

                        essmsh -E C:\Madhu\maxl\login.txt 32171,1911896561

                       After executing this, essmsh will create one file (login.txts) with encrypted username and                                password as shown below

                       Use this encrypted Username and Password in the max script as shown below:

Maxl Script:

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

login 'admin' 'password' on 'hypserver';

alter database 'App1'.'Plan1' set variable 'CurrMonth' $CurrMonth;

alter database 'App1'.'Plan1' set variable 'PriorMonth' $PriorMonth;

alter database 'App1'.'Plan1' set variable 'NextMonth' $NextMonth;

alter database 'App1'.'Plan1' set variable 'CurrYear' $CurrYear;

alter database 'App1'.'Plan1' set variable 'PriorYear' $PriorYear;

alter database 'App1'.'Plan1' set variable 'NextYear' $NextYear;

logout;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Batch script:

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

SET /p MN=Enter Month:

SET /p YY=Enter Year:

echo %MN%

echo %YY%

SET /A PM=%MN%-1

SET /A NM=%MN%+1

SET /A PY=%YY%-1

SET /A NY=%YY%+1

echo %PM%

echo %NM%

echo %YY%

echo %NY%

IF %MN% ==01 (SET CurrMonth="Jan") & (SET PriorMonth="Dec") & (SET NextMonth="Feb")

IF %MN% ==02 (SET CurrMonth="Feb") & (SET PriorMonth="Jan") & (SET NextMonth="Mar")

IF %MN% ==03 (SET CurrMonth="Mar") & (SET PriorMonth="Feb") & (SET NextMonth="Apr")

IF %MN% ==04 (SET CurrMonth="Apr") & (SET PriorMonth="Mar") & (SET NextMonth="May")

IF %MN% ==05 (SET CurrMonth="May") & (SET PriorMonth="Apr") & (SET NextMonth="Jun")

IF %MN% ==06 (SET CurrMonth="Jun") & (SET PriorMonth="May") & (SET NextMonth="Jul")

IF %MN% ==07 (SET CurrMonth="Jul") & (SET PriorMonth="Jun") & (SET NextMonth="Aug")

IF %MN% ==08 (SET CurrMonth="Aug") & (SET PriorMonth="Jul") & (SET NextMonth="Sep")

IF %MN% ==09 (SET CurrMonth="Sep") & (SET PriorMonth="Aug") & (SET NextMonth="Oct")

IF %MN% ==10 (SET CurrMonth="Oct") & (SET PriorMonth="Sep") & (SET NextMonth="Nov")

IF %MN% ==11 (SET CurrMonth="Nov") & (SET PriorMonth="Oct") & (SET NextMonth="Dec")

IF %MN% ==12 (SET CurrMonth="Dec") & (SET PriorMonth="Nov") & (SET NextMonth="Jan")

echo %CurrMonth%

echo %PriorMonth%

echo %NextMonth%

IF %YY% ==09 (SET CurrYear="FY09") & (SET PriorYear="FY08") & (SET NextYear="FY10")

IF %YY% ==10 (SET CurrYear="FY10") & (SET PriorYear="FY09") & (SET NextYear="FY11")

IF %YY% ==11 (SET CurrYear="FY11") & (SET PriorYear="FY10")& (SET NextYear="FY12")

IF %YY% ==12 (SET CurrYear="FY12") & (SET PriorYear="FY11")& (SET NextYear="FY13")

IF %YY% ==13 (SET CurrYear="FY13") & (SET PriorYear="FY12")& (SET NextYear="FY14")

IF %YY% ==14 (SET CurrYear="FY14") & (SET PriorYear="FY13")& (SET NextYear="FY15")

IF %YY% ==15 (SET CurrYear="FY15") & (SET PriorYear="FY14")& (SET NextYear="FY16")

IF %YY% ==16 (SET CurrYear="FY16") & (SET PriorYear="FY15")& (SET NextYear="FY17")

IF %YY% ==17 (SET CurrYear="FY17") & (SET PriorYear="FY16")& (SET NextYear="FY18")

IF %YY% ==18 (SET CurrYear="FY18") & (SET PriorYear="FY17")& (SET NextYear="FY19")

IF %YY% ==19 (SET CurrYear="FY19") & (SET PriorYear="FY18")& (SET NextYear="FY20")

IF %YY% ==20 (SET CurrYear="FY20") & (SET PriorYear="FY19")& (SET NextYear="FY21")

echo %CurrYear%

echo %PriorYear%

echo %NextYear%

Call C:\Hyperion\products\Essbase\EssbaseServer\bin\essmsh.exe -D C:\Mady\maxl\curr.msh 1291455731,1911896561 %CurrMonth%

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

                                     Output of the batch script:

                                  

                                   Updated Variables in Essbase Server:

Oracle Data Integrator installation and Configuration 11.1.1.6 version

Oracle Data Integrator installation and Configuration: Connecting Master and Work Repositories

Extract the ODI Zip files, then open Disk1

 Open Command prompt, change the path to setup.exe folder, execute the setup with jreLoc as shown below(Notw: If setup not recognise jre in the located place install JRE then do the same again)

                                                           
                                                                   Installation started

               
                      Skip Repository Configuration in the below step, we can confiure Repositories later

             
                    JDBC URL: SQL Server name, port number and Database details as shown below

                                 JDBC URL: SQL Server name, port number and Database details

Click on Test Connection

                             Open ODI studio from Satrt --> Programs --> Oracle --> ODI Studio
                                                           Create Login Name to login

  First time Click on " + " button to create. 

(Note: To Edit the login, click on pencil button after the "+" button)

                          Select the Drivers as SQL server Datadirect Driver, then enter URL
                Click on Search button at the end of the text box, it will give sample format of URL
                             (General format: SQL Server name, Prot number, and Database)

In the Work Repository, Select Work Repository tab and click on seach button it will shows work repositories to connect to the Master Repository

                                                                                                                  Cheers,

                                                                                                                  mady

Installation and Configuration of Data Relationship Management 11.x

Configuration:

Once installation completes, In the last step we get an option to Launch Console.

Otherwise we can from Start --> Programs --> Oracle EPM System --> Data Relationship Management -->Web Console

Go to Repository --> Select Repository Configuration

 Here i am using SQL Server as RDBMS, So i am selecting SQL Server. If you are using Oracle Select and give Server details as shown below.

In the User Settings, Console automatically mention user name just we need to give password.

In the Reposiory Settings, Console mention Database Name and Data and log paths also.

Note: we can change User Id and Database name in the following step. Whatever name we are giving here, Console will create that User id and Database in the SQL Server. No need to create a Database manully.

Application Administrator Creation, Console give User name as ADMIN, we need to give password.
Note: In the password, we need to mention Caps, numbers..etc (Strong Password then Console will aceept the password)

 Confiramation Wizard, Click next to create Repository.

 Create Repository Done, We can check in the SQL Server.

 Click Finish to complete Repository Configuration.

 Application Cretaion will come, to Create application. Mention Server Details then Save Configuration.

                                                                                                                                                                                                                                                                                                                      Cheers,

                                                                                                                                                  mady

How to open easconsole.jnlp file for EAS Console in Hyperion 11.x

How to open easconsole.jnlp file for EAS Console in Hyperion 11.x

Once Hyperion Installation and Configuration done, first when we try to easconsole, easconsole.jnlp file will be open. 

Go to this path, find java.ws (java web starter)

C:\Oracle\Middleware\EPMSystem11R1\common\JRE\Sun\1.6.0\bin

Click Run and install java on the Machine.

you will get easconsole as shown below.

Another Method:
To open easconsole, we can use workspace also. Open Web Analysis from workspace, it will automatically install java on the machine.

Thanks,
Mady