Skip to main content

Update Substitution Variable


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.

 We can create substitution variables using MAXL Script but for updating them automatically here we are using batch script and retrieving the values of Current Year, Current Month from the local machine time and then subsequently updating those values to get Next Month, Previous Month as well as Next Year and Previous Year.
Maxl Script:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
login 'admin' 'password' on '<servername>';
alter database 'App'.'Plan1' set variable 'CurrMonth' $CurrMonth;
alter database 'App'.'Plan1' set variable 'PriorMonth' $PriorMonth;
alter database 'App'.'Plan1' set variable 'NextMonth' $NextMonth;
alter database 'App'.'Plan1' set variable 'CurrYear' $CurrYear;
alter database 'App'.'Plan1' set variable 'PriorYear' $PriorYear;
alter database 'App'.'Plan1' set variable 'NextYear' $NextYear;
logout;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Batch script:
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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 <location of the maxl file> %CurrMonth%


Thanks!!


Popular posts from this blog

Planning Admin Extension

Planning 11.1.2.3 – Smart View metadata management One of the new features in planning 11.1.2.3 is the ability to manage dimensional metadata through Smart View  • The new Hyperion Planning Admin Extension for Smart View allows Oracle Hyperion Planning administrator users to import and work with metadata in Smart View. • With this extension, Planning administrators can use Oracle Hyperion Smart View for Office applications to perform these tasks: 1. Add, edit and import existing Oracle Hyperion Planning dimensions and members, including moving members and designating members as shared in Smart View 2. Create and refresh cubes • The extension can be downloaded from the Tools menu in workspace. • This will download a file called “ PlanningSVExtension.msi ” which is 1.4mb in size and can be easily installed by running the msi . Once you Install Planning Admin Extension A Smart View Set up Wizard opens up for the extension installation. Click on Next : ...
Home

Shared Connection and Private Connection

You connect to data sources through shared or private connections. Shared Connections •Shared connections are stored in a central location and are available to multiple users through the Smart View Panel. You cannot add, edit, or rename shared connections, but you can save them as private connections, which you can edit and rename. Private Connections •Private connections are those that you create by saving a shared connection to your local computer or by entering a URL to a provider that is not configured for shared connections. When you create a private connection, it becomes the active connection. Shared Connections : 1. From the Smart View ribbon, click  Panel . 2. From Smart View Home or from the menu displayed when you click the arrow next to Home button, do one of the following: • Click a connection name under  Recently U sed . You can click    to pin items to this list. • Select  Shared Connections  to open th...
Home

Essbase for Beginners: Data Block concepts Part A

An Essbase outline has a number of dimensions.  The number of dimensions can range in quantity and size, but each dimension is identified as a dense or sparse dimension.  As we understood the concept of Dense and Sparse in our last discussion, let’s begin with the cocept of data blocks now. To understand this, let’s carry forward our hierarchy of 5 dimensions in a test application in my previous post.. We have 3 dense and 2 sparse dimensions each with 3 stored members. So let’s begin: Number of Existing Blocks = (potential number of blocks) * estimated density= 9 Block Size = Dense Stored * Dense Stored * Dense Stored * 8 bytes = 3*3*3 *8 = 216 Potential number of blocks = Sparse Stored * Sparse Stored = 9 If all dimensions are assigned as Dense, Number of Existing Blocks = (potential number of blocks) * estimated density= 1 Block Size = Dense Stored * Dense Stored * Dense Stored * 8 bytes = 3*3*3*3*3 *8 = 1944 Potential number of bloc...
Home