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: