maxl utils

(1)

Using MaxL to Automate Essbase

Cameron Lackpour

interRel Consulting

(2)

Disclaimer

• These slides represent the work and opinions of the

presenter and do not constitute official positions of

Oracle.

• This material has not been peer reviewed and is

presented here with the permission of the presenter.

• This material should not should not be reproduced

without the written permission of the presenter and

interRel Consulting.

(3)

What this session isn’t

• Not an introduction to MaxL

• Great presentation at Kaleidoscope 2008

by Tracy McMullen

• Link:

http://www.odtug.com/apex/f?p=500:570:280884916307492::NO::P570_CONTENT_ID:3630

• Or just search the Tech Resources section

of

www.odtug.com

for “maxl”

(4)

So, what’s the content?

• Variables, variables, variables

• Making your auditors happy (encryption)

• Playing the Shell game

• Setting a trap for errors

• The triumvirate of NT cmd scripts,

VBScript, and MaxL

(5)

How does it come together?

• Fix what Oracle won’t

–

Make Essbase look like Planning

–

Change Read filters to Metaread

• Next session

(6)

What does MaxL stand for?

• (

M

)aximum (

a

)wfulness e(

x

)pressed in a

(

L

)anguage

• (

M

)ost (

a

)wesome e(

x

)cellent (

L

)anguage

• (

M

)ulti-dimensional database (

ax

)ccess

(

L

)anguage

–

Thanks to Tim Faitch

• (

M

)ulti-dimensional (

a

)utomation

E(x

)pression (

L

)anguage

(7)

Hardcoding gives me hives

• Especially when MaxL gives you three

options

–

Command line

–

Locally defined

(8)

Ugly but succinct positionals

/* Login to Essbase

*/

login $1 $2 on $3 ;

(9)

But the result is beautiful

• Type

C:\>essmsh sample1.msh essadmin essbase d630

• Get back

MAXL> login essadmin essbase on d630 ;

OK/INFO - 1051034 - Logging in user [essadmin].

OK/INFO - 1051035 - Last login on Friday, May 08, 2009 12:02:32 PM. OK/INFO - 1241001 - Logged in to Essbase.

(10)

Respect the environment

• OS defined

• Addressable by name

• Type (in Windows command shell)

C:\>set computername

• Get back

(11)

A little more readable

• Stick a “$” in the front and the name

login $1 $2 on $COMPUTERNAME ;

(12)

And the result is the same

• Type

C:\>essmsh sample2.msh essadmin essbase

• Response

MAXL> login essadmin essbase on D630 ;

(13)

Locally defined within code

/* Declare variables */

set varUserName = essadmin ; set varPassword = essbase ; set varServerName = d630 ;

set varSpoolFile = 'c:\tempdir\odtug_2009\local.log' ; /* Connect to server */

login $varUserName $varPassword on $varServerName; /* Write STDOUT to log file */

spool on to $varSpoolFile ;

(14)

The result doesn’t change, much

• Type

C:\>essmsh local.msh

• Response

essadmin

(15)

local.log

essadmin

(16)

What’s wrong with this picture?

• Admin username and password

• Auditors seem to dislike this

–

Why is that, exactly?

• And you can’t fix it with local variables

(17)

So which is best?

• Positional

–

Must be on command line

–

Most flexible

–

Cryptic

• Environment

–

Must be defined within OS

• Local

(18)

Encryption review

• Public key encryption

–

Asymmetric key algorithms

• Encrypt (public) and decrypt (private) keys are

different

• Generated through large random number and

black-box magic

• MaxL generates both keys for you

• Different every time it’s run

(19)

Generate those keys

• Type

C:\>essmsh –gk

• Response

Public Key for Encryption: 26429,694177571

Private Key for Decryption: 316108469,694177571

(20)

Simple code

login essadmin essbase on d630 ;

exit ;

(21)

Encrypt script with public key

• Type

C:\>essmsh -E tobeencrypted.msh 26429,694177571

• Response

(22)

tobeencrypted.mshs

login $key 994958605500155654401932116812299621 $key 080321016292835195975318016812299621 on d630;

(23)

It blew up real good

• Type

C:\>essmsh tobeencrypted.mshs

• Response

MAXL> login $key 994958605500155654401932116812299621 $key 080321016292835195975318016812299621 on d630;

essmsh error: Variable doesn't exist - $key essmsh error: Variable doesn't exist - $key

(24)

So decrypt with private key

• Type

• C:\>essmsh -D tobeencrypted.mshs 316108469,694177571

• Response

MAXL> login $key 994958605500155654401932116812299621 $key 080321016292835195975318016812299621 on d630;

(25)

Hardcoded username and password

• Parameters don’t make sense

• No joy for locally defined variables

(26)

Three shells and a pea

• No sleight of hand – you are not marks

• Sometimes the OS needs to help out

–

Creating/Deleting/Moving files

–

Kicking off other processes

(27)

Code still isn’t complicated

login essadmin essbase on d630 ;

/* Wait for 60 seconds

*/

(28)

Scripts within scripts

• Nesting is easy

• msh scriptname

• Login info passed

(29)

Useless by themselves

• properties.msh

/* Declare variables */

set varUserName = essadmin ; set varPassword = essbase ; set varServerName = d630 ;

set varSpoolFile = 'c:\tempdir\odtug_2009\nested.log' ;

• connect.msh

/* Connect to server */

login $varUserName $varPassword on $varServerName; /* Write STDOUT to log file */

spool on to $varSpoolFile ;

(30)

Sum is greater than its parts

• nested.msh

msh properties.msh ; msh connect.msh ; msh disconnect.msh ;

• Stole it from:

http://essbaselabs.blogspot.com/2009/04/organizing-maxl- scripts.html

(31)

Eeny, meeny, miny, moe

• We’re catching errors, not tigers

• iferror

• OS return codes

(32)

You’re doing this, right?

• What could possibly go wrong?

• Every action needs to be tested for failure

• That 3 am call

• Iferror and define label are your

BFFs

(33)

What do want to do?

• Login

• Clear out good old Sample Basic

• Load

(34)

Murphy is paying a visit

login essadmin essbase on d630;

alter database Sample.Basic1 reset data ;

import database Sample.Basic data from local text data_file 'C:\Hyperion\AnalyticServices\app\Sample\Basic\calcdat.txt' on error write to 'c:\tempdir\odtug_2009\calcdat.err' ;

execute calculation Sample.Basic.CalcAll ; exit ;

(35)

Whoops

• Type

C:\>essmsh hellomurphy.msh

• Response (trimmed)

MAXL> alter database Sample.Basic1 reset data ;

ERROR - 1056024 - Database Sample.Basic1 does not exist.

MAXL> import database Sample.Basic data from local text data_file 2> 'C:\Hyperion\AnalyticServices\app\Sample\Basic\calcdat.txt' 3> on error write to 'c:\tempdir\odtug_2009\calcdat.err' ;

(36)

Catch the error

login essadmin essbase on d630; iferror 'ErrorHandler' ;

alter database Sample.Basic1 reset data ;

iferror 'ErrorHandler' ;

import database Sample.Basic data from local text data_file 'C:\Hyperion\AnalyticServices\app\Sample\Basic\calcdat.txt' on error write to 'c:\tempdir\odtug_2009\calcdat.err' ;

iferror 'ErrorHandler' ;

execute calculation Sample.Basic.CalcAll ; iferror 'ErrorHandler' ;

define label 'ErrorHandler' ; exit ;

(37)

And now it gracefully(?) ends

• Type

C:\>essmsh catchthaterror.msh

• Response

MAXL> login essadmin essbase on d630;

(38)

The OS is your friend

• %ERRORLEVEL% is crude, yet effective

• 0 = successful MaxL script

• <> 0 = trouble

(39)

Good old %erorrlevel%

• Type

C:\>%errorlevel%

• Response

'2' is not recognized as an internal or external command,

operable program or batch file.

(40)

No errors = 0

• Type

C:\>essmsh parameters.msh essadmin essbase d630

• Response

MaxL Shell completed C:\>%errorlevel%

'0' is not recognized as an internal or external command, operable program or batch file.

(41)

DOS (not really) lives

@ECHO OFF

%hyperion_home%\analyticservices\bin\essmsh.exe catchthaterror.msh

IF NOT %ERRORLEVEL% == 0 (SET errormsg=Error! -

catchthaterror.msh ran with errors and return code %ERRORLEVEL% & GOTO ERROR)

REM No errors, so jump over the error logger GOTO END

:ERROR

(42)

Output = bad

(43)

Piping output to files

• spool

• Two choices

–

STDOUT

–

STDERR

• Or log both

(44)

Two of a kind

spool stdout on to 'c:\tempdir\odtug_2009\SpoolMe.log' ;

spool stderr on to 'c:\tempdir\odtug_2009\SpoolMe.err' ;

login essadmin essbase on d630; iferror 'ErrorHandler' ;

alter database Sample.Basic1 reset data ; iferror 'ErrorHandler' ;

etc. etc. etc.

(45)

• SpoolMe.log

MAXL> login essadmin essbase on d630;

OK/INFO - 1051035 - Last login on Friday, May 08, 2009 3:14:41 PM.

OK/INFO - 1241001 - Logged in to Essbase.

• SpoolMe.err

ERROR - 1056024 - Database Sample.Basic1 does not exist.

(46)

Okay, but so what?

• Test for the presence of SpoolMe.log

–

Tells you that the job ran

• Test for the presence of SpoolMe.err

–

Tells you if there was a failure

(47)

In the Windows world

REM Call and run SpoolMe.msh

%hyperion_home%\analyticservices\bin\essmsh.exe SpoolMe.msh REM If SpoolMe.log doesn't exist, error

IF NOT EXIST SpoolMe.log (SET errormsg=Error! - SpoolMe.msh failed to run & GOTO ERROR)

REM If SpoolMe.err does exist, error

IF EXIST SpoolMe.err (SET errormsg=Error! - SpoolMe.msh ran with errors & GOTO ERROR)

REM No errors, so jump over the error logger GOTO END

(48)

Nice n’ Easy

*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=* Load and calc of Sample.Basic

Current time at \\D630 is 5/8/2009 3:20 PM The command completed successfully.

ECHO is off.

-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-Error! - SpoolMe.msh ran with errors

-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-ECHO is off.

-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-Execution of SpoolMe.msh ended at :

Current time at \\D630 is 5/8/2009 3:20 PM The command completed successfully.

(49)

What do I do with this?

• Typical use

–

Loading and calculating data

–

Creating partitions

–

Applying security

• Atypical

(50)

Planning and filters

• Planning

–

Metadata filtering

–

Like METAREAD

• Essbase and Financial Reports

–

READ

(51)

Planning meta filtering

(52)

Not in reporting tools

(53)

What’s the problem?

Access

Member specification

Read "BalanceSheet",@IDES("IncomeStatement"),@IDES("E02"),"F orecast","Plan","Working","BU Version_1","Final"

None @IDES("Account"),@IDES("Scenario"),@IDES("Version"),@ID ES("Entity")

(54)

Easy fix in EAS

Access

Member specification

Read "BalanceSheet",@IDES("IncomeStatement"),@IDES("E02"),"F orecast","Plan","Working","BU Version_1","Final" None @IDES("Account"),@IDES("Scenario"),@IDES("Version"),@ID ES("Entity") Metaread "BalanceSheet",@IDES("IncomeStatement"),@IDES("E02"),"F orecast","Plan","Working","BU Version_1","Final"

(55)

(56)

Planning strikes again

Access

Member specification

Read "BalanceSheet",@IDES("IncomeStatement"),@IDES("E02"),"F orecast","Plan","Working","BU Version_1","Final"

None @IDES("Account"),@IDES("Scenario"),@IDES("Version"),@ID ES("Entity")

(57)

What do we want to happen?

• Refresh Planning

–

Dimensions

–

Security

(58)

How do we do this?

• Automate

–

CubeRefresh.cmd

–

NT CMD, VBScript, and MaxL script(s)

• Drive values through OS parameters

• Wrap the whole thing in error checking

–

ERRORLEVEL

(59)

Steps

1. Planning refresh with CubeRefresh.cmd

2. Write filters to disk

3. Read them into memory

4. Generate MaxL code to apply

METAREAD

5. Run the new nested script

(60)

Refresh_Planning.cmd command line

Parameter

Value

Planning encrypted password file

c:\tempdir\odtug_2009\password.txt

Planning application

plansamp

Planning admin username

admin

Refresh or create

/R

Filter switches

/FS

Essbase server

%computername%

First half of private key

316108469

Second half of private key

694177571

(61)

Looks like

refresh_planning.cmd c:\tempdir\odtug_2009\password.txt plansamp admin /R /FS %computername% 316108469,694177571

(62)

Automate Planning refresh

• Modified CubeRefresh.cmd

• Dumps to prompt

• Add EXIT, save to script dir

ModCubeRefresh.cmd @echo off set PLN_JAR_PATH=C:\Hyperion\products\Planning\bin call "%PLN_JAR_PATH%\setHPenv.bat" "%HS_JAVA_HOME%\bin\java" -Dhyperion.home=%HYPERION_HOME% com.hyperion.planning.HspCubeRefreshCmd %1 %2 %3 %4 %5 /COMMAND:CubeRefresh.cmd >>%6 EXIT

(63)

Run the refresh

REM start /wait /min modcuberefresh.cmd -

f:c:\tempdir\odtug_2009\password.txt /A:plansamp /U:admin /R /FS %log%

START /WAIT /MIN ModCubeRefresh.cmd -f:%1 /A:%2 /U:%3 %4 %5 %log% REM Test for errors from the Planning refresh process

IF %ERRORLEVEL% == 1 (SET errormsg=Error! - Planning refresh of %2 on %COMPUTERNAME% failed & GOTO ERROR)

(64)

Write all filters to disc

REM Write filters to disc

%hyperion_home%\products\Essbase\EssbaseClient\bin\essmsh.exe -D

write_filters_to_disc.mshs %7,%8

REM If error, go to end, else write

IF ERRORLEVEL == 1 (SET errormsg=Error! - Read of filters from Essbase failed & GOTO ERROR)

(65)

write_filters_to_disc.mshs

/* Create log file that will contain the filter contents */

spool on to 'Write_Filters_To_Disc.log';

/* Log on to the server using the: userid, password, and servername */

login $key 152862484425455861686132835 $key

420285733240440925357332674133471265 on $COMPUTERNAME ;

/* Set the column width to 1000 characters to allow the filter plenty of room. */

set column_width 1000 ;

/* Show those filters */

display filter row all ;

/* End the spooling. */

(66)

Create MaxL script

REM Use Windows Scripting Host to parse the filter file and write out MaxL code to add MetaRead filters

CSCRIPT Create_MetaRead_Filters.wsf /Application:%2 /FilterFile:Write_Filters_To_Disc.log

/OutputFile:Metaread_Add_Filters.msh

REM If the output file doesn't exist, an error has occurred

IF NOT EXIST MetaRead_Add_Filters.msh (SET errormsg=Error! - Creation of MaxL include file failed %2 & GOTO ERROR)

(67)

Metaread_Add_Filters.msh

ALTER FILTER 'PlanSamp'.'Consol'.'fplanuser' ADD META_READ ON

'"BalanceSheet",@IDES("IncomeStatement"),@IDES("E02"),"Forecast ","Plan","Working","Final","BU Version_1"' ;

(68)

Nest alter filter code

REM Execute add metaread MaxL script

%hyperion_home%\products\Essbase\EssbaseClient\bin\essmsh.exe -D call_metaread_add_filters.mshs %7,%8

REM If the log file doesn't exist, an error has occurred

IF EXIST call_metaread_add_filters.err (SET errormsg=Error! - Execution of MaxL include file for METAREAD ADD failed & GOTO ERROR)

(69)

call_metaread_add_filters.mshs

/* Write out only errors to the log file. Use this to determine error

status. */

spool stderr on to 'call_metaread_add_filters.err' ;

/* Log on to the server using the: userid, password, and servername*/ login $key 152862484425455861686132835 $key

420285733240440925357332674133471265 on $COMPUTERNAME ;

/* Include the VBScript-generated METAREAD ADD MaxL code. */

msh 'metaread_add_filters.msh';

/* End the spooling. */

(70)

Refresh_Planning.log

*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*

Planning refresh of plansamp on DEMO111 beginning at: Current time at \\DEMO111 is 5/10/2009 11:14 AM

The command completed successfully.

-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-Output from ModCubeRefresh.cmd

Cube refresh completed successfully.

-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-Planning refresh succeeded

Now getting filters from all Essbase databases

-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-Filter dump successful.

Parse all filters to create MaxL include for plansamp

-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-Creation of MaxL include code successful

Execute MaxL include code to create METAREAD filters

-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-Error! - Execution of MaxL include file for METAREAD ADD failed

-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-Planning refresh of plansamp on and application of METAREAD filters ended at: Current time at \\DEMO111 is 5/10/2009 11:14 AM