Hyperion On-Premise, Cloud and OneStream: November 2013

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

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)

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:

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

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:

Hyperion On-Premise, Cloud and OneStream

Followers

Thursday, November 21, 2013

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

Monday, November 18, 2013

Update Substitution variables automatically using batch script

Report Abuse