Piecing Together EPMA Automation, Part II

What’s my “personal” thing for today?  A statement I hope does not get rotten tomatoes thrown at me: I strongly dislike fall.  The only season that carries more disdain for me is winter.  I do not like the cold.  I do not like the briskness.  I love heat – triple digits and 100% humidity make me a happy, happy girl.  Lucky for you, today is Raleigh’s first real “cool” day of the fall – 61* and I still have a run to get in before it gets dark.  Why are you lucky for the weather and my anticipated run?  Because I will be more verbose in my writing today regarding part 2 of “Piecing Together EPMA Automation” to put off running in that cold outside air.

Frozen

(PS – Do you also find it ironic that SFO (Boston, second) is my favorite city yet I dislike cooler weather??)

If you recall from yesterday, I discussed how to automate EPMA’s Workspace functions.  The post wasn’t all that long because the guide Oracle created is pretty good.  It’s a rudimentary scripting language, so no need to go into depth about every option.  Find what you do manually in Workspace and look it up in the guide…it’s actually pretty much as easy as that.

Today I want to discuss error trapping for 3 areas:

  • DOS
  • EPMA
  • MaxL

Why DOS, you ask?  Well, you will need it to pull the pieces together (recall that I am basing this blog series on a Windows server).  But that is tomorrow’s topic, so let’s get started with today.

DOS Error Trapping
I am going to start with DOS error trapping.  Why?  Because you will likely use DOS to run the EPMA Batch Script Utility or MaxL and their associated scripts, so it only makes sense to start at the end.  🙂

Full disclosure – if you had asked me 6 months ago about DOS scripting, I would have sent you to someone else.  Heck, I am pretty sure I said that just 2 months ago.  I never took the time to truly dig into DOS because it had always accomplished what I wanted it to.  It wasn’t until I started putting all of the EPMA automation pieces together that I realized DOS could be my friend…or enemy.  It could be used to tell me about my scripts rather than just be used as a conduit to accomplish tasks in the Hyperion environment.

One thing I have noticed over the years in Hyperion environments is there are different ways of calling the MaxL shell script from a batch file.  The typical options are CALL, START and {nothing}.  The {nothing} just starts MaxL without START or CALL.  CALL is typically used and you can run other batch files with this command and return back to the current batch to carry on the script.  You can also use the batch script variables in the CALLed batch scripts.  The START command will start a new command prompt and variables cannot be shared.  Both of these have useful options…you just gotta know what you need.

An important part of the DOS error trapping method for Hyperion is the output from the script if there is an error.  Now, I struggled with this one for a bit.  There is ERRORLEVEL and there is %ERRORLEVEL% and they are not the same.  I was trying to catch the error from an EPMA script using just ERRORLEVEL and it would *never* return correct.  I always got success even when the error code was not 0 (signaling success).  The problem was that error trapping for 0 was first on my list of possible errors and I was not using the keyword correctly.  Plain ERRORLEVEL is treated as an environment variable, just as one named SARAH would be.  Nothing special about the variable name.  However, put it in percentage signs and suddenly the system looks for a previously defined variable named ERRORLEVEL and if it does not exist then the current internal error level number is substituted.  Another way of stating this is if you *do* have an environment variable called ERRORLEVEL and the value is 1, then if you checked to see if the value was 0, it would be false.  However, if you don’t specify a variable named ERRORLEVEL, then the system is going to force feed you what is leftover in its hand from your script – your return value.

Baby

Example: Error is 1, but I get an ERRORLEVEL return of 0.
IF ERRORLEVEL 0 GOTO SUCCESS
IF ERRORLEVEL 1 GOTO ERROR1
IF ERRORLEVEL -1 GOTO ERRORMINUS1

However, when I encompassed the ERRORLEVEL with percentage signs (indicating a variable in DOS), I get the result I was hoping for.

Example: Error is 1, and I get an ERRORLEVEL return of 1.
IF %ERRORLEVEL% 0 GOTO SUCCESS
IF %ERRORLEVEL% 1 GOTO ERROR1
IF %ERRORLEVEL% -1 GOTO ERRORMINUS1

Granted, sometimes we just want to see if the script passed or failed.  This is where our elementary school math learning of comparison math comes into play; sometimes, you just want to see if something is greater than 0 or not equal to zero (to catch the -1 error).  To accomplish this, you will perform the same type of error trapping:

Example: Error is 1, and I get an ERRORLEVEL return of 1
IF %ERRORLEVEL% NEQ 0 GOTO FAILED **This would be correct
or
IF %ERRORLEVEL% GTR 0 GOTO FAILED **This would also be correct

However, sometimes you have to consider the return codes available to you.  For example, Smart View has return codes that are both positive and negative, depending on if the error is server-based or local machine-based.  The options available to you in DOS are:

  • EQU – equal to
  • NEQ – not equal to
  • LSS – less than
  • LEQ – less than or equal to
  • GTR – greater than
  • GEQ – greater than or equal to

With these values, you can determine how to handle each return code in DOS from your MaxL or EPMA scripts.

EPMA Error Trapping
Anyone who has worked with EPMA knows that it can be…finicky.  There seem to be more errors than can be imagined and the slightest of errors can wreck a validation or deploy.  So how do you check for errors in your EPMA automation scripts?  Look to DOS and page 10 of the handy EPMA Batch User Guide (EBUG).  Now, here is where I blame Oracle for my confusion of implementing ERRORLEVEL versus %ERRORLEVEL% earlier – the EBUG lists the correct way to trap errors from EPMA is to use ERRORLEVEL.  However, if you do this, you will ALWAYS get success (if you have 0/success listed first in your return code check list).  {…Waaaaaaiiiiittttt…is this a ploy?!?}

Do you recall earlier in the post where I stated “the Oracle guide is pretty good”.  Well, it’s true.  It’s pretty good, but it is not perfect.  You will find typos in critical code keywords and you will find errors on how to trap errors (see what I did there?!).  The EBAG lists the following to test the errors…and for someone green, green, green, GREEN to DOS scripting, this is not enough.

EBAG
AngryCat

What the above *should* show is the following for when trying to trap errors coming from the EPM Batch Script Utility:

call epma-batch-client.bat .\scripts\MyScript.txt
IF %ERRORLEVEL% 0 GOTO ON_SUCCESS
IF %ERRORLEVEL% 100 GOTO PARSE_ERROR
IF %ERRORLEVEL% 101 GOTO APP_CREATE_FAILED

:ON_SUCCESS
ECHO Script was successful
PAUSE
EXIT

:PARSE_ERROR
ECHO Error: 100.  There was a parsing error.
PAUSE
EXIT

:APP_CREATE_FAILED
ECHO Error: 101. The application creation failed.
PAUSE
EXIT

Please note that you can do whatever you want in the sections once an error code is identified.  I like for the error to be displayed on screen (the ECHO and PAUSE) before the script exits.  You can create the full list from the EBAG, or use my code listed at the end of this post.  🙂  (You’re welcome).

MaxL Error Trapping
Full disclosure: I recently discovered this method of error trapping from MaxL and am very excited to continue implementing it in the future.  I started by using the DBAG and read further on Cameron Lackpour’s site as he did a great job of explaining the process.  He is very good about telling what he tried and tested, what worked and what didn’t.  I’m going to tell you what I have done so that tomorrow you have the FULL picture of how everything fits together.  I encourage you to visit his post on the topic listed here.

What I did not realize until recently is that you can pass exit codes (error codes, if you will) from MaxL and *they can be customized*.

Whaaa

To quote David After Dentist – Is this real life??

Here is example.  If I want to track my MaxL script and return an error code based on where it fails in the script, we can give each line its own error code…again…

Selfie

If we use the following as an example, we can see that we have applied “labels” to places in the script where exit codes can be applied:

MaxL

The “iferror” followed by a label name tells the script that if there is an error, then go to the appropriate label place in the script.  Under “define label” we see where we have created special exit numbers (1 through 4 here) and have attached an exit code of 0 (although likely repetitive) if the script runs successfully.

We can take these codes and use them in the DOS error trapping method listed above…just like the EPMA Batch Script Utility codes.

…Are you starting to see how this is all coming together???

Tomorrow I am excited to wrap up this 3 part series where I tie together EPMA and Essbase automation using DOS.  To conclude my most fun post to date (for me, at least), how I felt after I pieced together EPMA and Essbase automation via a quote from my favorite movie ever:

Willy Wonka: ..Charlie, don’t forget what happened to the man who suddenly got everything he always wanted.
Charlie Bucket: What happened?
Willy Wonka: He lived happily ever after.

Willy

EPMA Error Trapping in DOS Code:

IF %ERRORLEVEL% -1 GOTO ERRORMINUS1
IF %ERRORLEVEL% 1 GOTO ERROR1
IF %ERRORLEVEL% 100 GOTO ERROR100
IF %ERRORLEVEL% 4 GOTO ERROR4
IF %ERRORLEVEL% 1501 GOTO ERROR1501
IF %ERRORLEVEL% 1502 GOTO ERROR1502
IF %ERRORLEVEL% 101 GOTO ERROR101
IF %ERRORLEVEL% 102 GOTO ERROR102
IF %ERRORLEVEL% 103 GOTO ERROR103
IF %ERRORLEVEL% 110 GOTO ERROR110
IF %ERRORLEVEL% 2100 GOTO ERROR2100
IF %ERRORLEVEL% 201 GOTO ERROR201
IF %ERRORLEVEL% 202 GOTO ERROR202
IF %ERRORLEVEL% 203 GOTO ERROR203
IF %ERRORLEVEL% 210 GOTO ERROR210
IF %ERRORLEVEL% 1602 GOTO ERROR1602
IF %ERRORLEVEL% 303 GOTO ERROR303
IF %ERRORLEVEL% 404 GOTO ERROR404
IF %ERRORLEVEL% 405 GOTO ERROR405
IF %ERRORLEVEL% 406 GOTO ERROR406
IF %ERRORLEVEL% 407 GOTO ERROR407
IF %ERRORLEVEL% 409 GOTO ERROR409
IF %ERRORLEVEL% 412 GOTO ERROR412
IF %ERRORLEVEL% 500 GOTO ERROR500
IF %ERRORLEVEL% 602 GOTO ERROR602
IF %ERRORLEVEL% 603 GOTO ERROR603
IF %ERRORLEVEL% 703 GOTO ERROR703
IF %ERRORLEVEL% 800 GOTO ERROR800
IF %ERRORLEVEL% 900 GOTO ERROR900
IF %ERRORLEVEL% 1903 GOTO ERROR1903
IF %ERRORLEVEL% 2000 GOTO ERROR2000
IF %ERRORLEVEL% 1000 GOTO ERROR1000
IF %ERRORLEVEL% 1102 GOTO ERROR1102
IF %ERRORLEVEL% 1103 GOTO ERROR1103
IF %ERRORLEVEL% 1803 GOTO ERROR1803
IF %ERRORLEVEL% 1200 GOTO ERROR1200
IF %ERRORLEVEL% 1702 GOTO ERROR1702
IF %ERRORLEVEL% 1301 GOTO ERROR1301
IF %ERRORLEVEL% 1308 GOTO ERROR1308
IF %ERRORLEVEL% 1302 GOTO ERROR1302
IF %ERRORLEVEL% 1303 GOTO ERROR1303
IF %ERRORLEVEL% 1400 GOTO ERROR1400
IF %ERRORLEVEL% 0 GOTO CONTINUE

:CONTINUE
ECHO There were no errors.
PAUSE
EXIT

:ERROR1
ECHO There was an error: %ERRORLEVEL%.
ECHO A validation error occurred.
PAUSE
EXIT

:ERRORMINUS1
ECHO There was an error: %ERRORLEVEL%.
ECHO A general error occurred.
PAUSE
EXIT

:ERROR100
ECHO There was an error: %ERRORLEVEL%.
ECHO A parse error occurred.
PAUSE
EXIT

:ERROR4
ECHO There was an error: %ERRORLEVEL%.
ECHO Command line error.
PAUSE
EXIT

:ERROR1501
ECHO There was an error: %ERRORLEVEL%.
ECHO An application copy error occurred.
PAUSE
EXIT

:ERROR1502
ECHO There was an error: %ERRORLEVEL%.
ECHO A dimension copy error occurred.
PAUSE
EXIT

:ERROR101
ECHO There was an error: %ERRORLEVEL%.
ECHO Create application failed.
PAUSE
EXIT

:ERROR102
ECHO There was an error: %ERRORLEVEL%.
ECHO Create dimension failed.
PAUSE
EXIT

:ERROR103
ECHO There was an error: %ERRORLEVEL%.
ECHO Create member failed.
PAUSE
EXIT

:ERROR110
ECHO There was an error: %ERRORLEVEL%.
ECHO Create association failed.
PAUSE
EXIT

:ERROR2100
ECHO There was an error: %ERRORLEVEL%.
ECHO Unknown error occurred.
PAUSE
EXIT

:ERROR201
ECHO There was an error: %ERRORLEVEL%.
ECHO Delete application failed.
PAUSE
EXIT

:ERROR202
ECHO There was an error: %ERRORLEVEL%.
ECHO Delete dimension failed.
PAUSE
EXIT

:ERROR203
ECHO There was an error: %ERRORLEVEL%.
ECHO Delete member failed.
PAUSE
EXIT

:ERROR210
ECHO There was an error: %ERRORLEVEL%.
ECHO Delete association failed.
PAUSE
EXIT

:ERROR1602
ECHO There was an error: %ERRORLEVEL%.
ECHO Detach dimension failed.
PAUSE
EXIT

:ERROR303
ECHO There was an error: %ERRORLEVEL%.
ECHO Exclude member failed.
PAUSE
EXIT

:ERROR404
ECHO There was an error: %ERRORLEVEL%.
ECHO Execute data sync failed.
PAUSE
EXIT

:ERROR405
ECHO There was an error: %ERRORLEVEL%.
ECHO Execute deploy failed.
PAUSE
EXIT

:ERROR406
ECHO There was an error: %ERRORLEVEL%.
ECHO Execute dimension sync failed.
PAUSE
EXIT

:ERROR407
ECHO There was an error: %ERRORLEVEL%.
ECHO Execute import failed.
PAUSE
EXIT

:ERROR409
ECHO There was an error: %ERRORLEVEL%.
ECHO Execute redeploy failed.
PAUSE
EXIT

:ERROR412
ECHO There was an error: %ERRORLEVEL%.
ECHO Execute validation failed.
PAUSE
EXIT

:ERROR500
ECHO There was an error: %ERRORLEVEL%.
ECHO Exit failed.
PAUSE
EXIT

:ERROR602
ECHO There was an error: %ERRORLEVEL%.
ECHO Include dimension failed.
PAUSE
EXIT

:ERROR603
ECHO There was an error: %ERRORLEVEL%.
ECHO Include member failed.
PAUSE
EXIT

:ERROR703
ECHO There was an error: %ERRORLEVEL%.
ECHO Insert member failed.
PAUSE
EXIT

:ERROR800
ECHO There was an error: %ERRORLEVEL%.
ECHO Login failed.
PAUSE
EXIT

:ERROR900
ECHO There was an error: %ERRORLEVEL%.
ECHO Logout failed.
PAUSE
EXIT

:ERROR1903
ECHO There was an error: %ERRORLEVEL%.
ECHO Move member failed.
PAUSE
EXIT

:ERROR2000
ECHO There was an error: %ERRORLEVEL%.
ECHO Option set failed.
PAUSE
EXIT

:ERROR1000
ECHO There was an error: %ERRORLEVEL%.
ECHO Quit failed.
PAUSE
EXIT

:ERROR1102
ECHO There was an error: %ERRORLEVEL%.
ECHO Remove dimension failed.
PAUSE
EXIT

:ERROR1103
ECHO There was an error: %ERRORLEVEL%.
ECHO Remove member failed.
PAUSE
EXIT

:ERROR1803
ECHO There was an error: %ERRORLEVEL%.
ECHO Rename member failed.
PAUSE
EXIT

:ERROR1200
ECHO There was an error: %ERRORLEVEL%.
ECHO Set failed.
PAUSE
EXIT

:ERROR1702
ECHO There was an error: %ERRORLEVEL%.
ECHO Share dimension failed.
PAUSE
EXIT

:ERROR1301
ECHO There was an error: %ERRORLEVEL%.
ECHO Update application failed.
PAUSE
EXIT

:ERROR1308
ECHO There was an error: %ERRORLEVEL%.
ECHO Update dimension association failed.
PAUSE
EXIT

:ERROR1302
ECHO There was an error: %ERRORLEVEL%.
ECHO Update dimension failed.
PAUSE
EXIT

:ERROR1303
ECHO There was an error: %ERRORLEVEL%.
ECHO Update member failed.
PAUSE
EXIT

:ERROR1400
ECHO There was an error: %ERRORLEVEL%.
ECHO Variable failed.
PAUSE
EXIT

4 comments

  1. Like the way you describe it. Exit codes in MaxL came in recently and I still follow my earlier old code of error trapping MaxL (maybe I just like the way I wrote them as functions.)

    SET FILE=%MAXLLOG_DIR%\AppDb_Logout.log

    findstr /R “\ ESSErrCheck
    for /F %%A in (“ESSErrCheck”) do If %%~zA NEQ 0 (

    call :datestamp
    echo %DTSTMP% Error in Log Out Process……………………. >> %MAINLOG%
    copy %ESSLOG% %MONTH_ERR_DIR%\Essbase\%~n0.log
    copy %FILE% %MONTH_ERR_DIR%\Essbase\MaxL\Maxl_Error.log
    call :remove_file ESSErrCheck
    call :exit_process
    ) else (
    call :datestamp
    echo %DTSTMP% Log Out Process was successful……………………. >> %MAINLOG%
    call :remove_file ESSErrCheck

    )
    So this one check whether there was an ERROR during Logout process.
    FILE is the spool file from MaxL
    findstr check whether ERROR was captured and this gets routed to a dummy file.
    for command checks whether the file size was zero if it is not then you got an error else success 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s