ASO Outline Compaction Using ESSCMDQ - The Good and Bad

---

ASO Essbase cubes have an amazing architecture overall.  Unfortunately, like every beautiful thing, there are some flaws up close.  Today, I am going to be talking about the issue of outline fragmentation and what to do about it.

The issue will arise when an ASO cube has been set up to automatically rebuilt it's dimensionality from an external source using a load rule.  Whether this be one or all dimensions in the outline, it does not matter.  This fragmentation also exists with ASO cubes built with EPMA Shared Libraries, but it is less of an issue as the fragmentation is less severe.  Just keep in mind the higher the frequency of dimensionality rebuilds, the higher fragmentation rate.  Eventually, without proper treatment, your ASO outline will expand in size and eventually you will not be able to open it in EAS.

Luckily, Oracle offers a free utility that will allow us to compact ASO outlines and return them to their normal size.  This utility is called ESSCMDQ.cmd.  No, that is not a typo, it is a utility similar to ESSCMD containing ASO outline compaction functions.  This utility is most likely not installed on your system and you will need to install it on the server you are running your batch scripts from.  You can download the utility from Oracle at the link below.

Please make sure that prior to installation, you read and understand the documentation accompanied with it.
http://www.oracle.com/technetwork/middleware/bi-foundation/esscmdq-sampleapps-093105.html

Installing ESSCMDQ

1. Download the documentation PDF file from the Oracle link
2. Download the ZIP file that corresponds to your operating system and version of Oracle Essbase. 
3. Unzip the ZIP file into a TEMP directory. 
4. Copy the ESSCMDG and ESSCMDQ files to your [arborpath]\bin directory. 
1. Typically: C:\Hyperion\products\Essbase\EssbaseClient\bin

Scott's Compaction Script

I created the windows batch script below that shows how to compact your ASO outlines.  This file as it sits is dynamic enough for you to call it from your batch scripts.

Please note: Do not run this utility on every dimension build.  (read why in the "ESSCMDQ Compaction Issues" section at the end of this post)

Batch Script Process Flow:

1. Creates MaxL script on the fly (since you cannot pass variables to the MaxL using the ESSCMDQ utility)
2. Launches the utility using the temporarily created MaxL
3. Outputs the utility results to a log file
4. Deletes the temporary MaxL script (Important because the MaxL will contain the password of the account used to compact the OTL)

Below is the code for the batch script. Please be aware that there are 2 environment variables that must be set for this script to function properly. I also recommend that you call the batch script CompactOTL.bat.

ECHO off

REM ###################### IMPORTANT NOTE ############################################
REM ## Before this batch can be run, the ESSCMDQ.exe 
REM ## application must be downloded and copied to the 
REM ## ESSCMDQpath below.
REM ##
REM ## You can get this utility and it's associated documentation from the URL below
REM ## http://www.oracle.com/technetwork/middleware/bi-foundation/esscmdq-sampleapps-093105.html
REM ##################################################################################

REM **********************Version*****************************************************
REM Version: 1.1
REM Created By Scott Danesi
REM **********************************************************************************

REM **********************PASSED PARAMETERS*******************************************
REM %1 = ServerName
REM %2 = AppName
REM %3 = DBName
REM %4 = UserName
REM %5 = Passord
REM **********************************************************************************

REM *************Environment Variable Declarations (Change These)*********************
SET ESSCMDQpath=C:\Hyperion\products\Essbase\EssbaseClient\bin
SET LocalPath=C:\Scripts\CompactOTL
REM **********************************************************************************

REM *************System Variable Declarations (DO NOT CHANGE)*************************
SET ServerName=%1
SET AppName=%2
SET DBName=%3
SET UserName=%4
SET Password=%5
set TimeStamp=%Date:~-4,4%%Date:~-10,2%%Date:~-7,2%_%Time:~0,2%%Time:~3,2%
SET LogFilePath=%LocalPath%\CompactOutline_%TimeStamp%_%AppName%-%DBName%.log
SET MaxLpath=%LocalPath%\CompactOutline.mxl
REM **********************************************************************************

ECHO on

del %MaxLpath%

REM ****************Create MXL File from variables************************************
ECHO Login "%ServerName%" "%UserName%" "%Password%" ; >> %MaxLpath%
ECHO Select "%AppName%" "%DBName%" ; >> %MaxLpath%
ECHO Openotl "2" "1" "%AppName%" "%DBName%" "%DBName%" "y" "y" "1"; >> %MaxLpath%
ECHO Writeotl "1" "2" "1" "%AppName%" "%DBName%" "%DBName%" ; >> %MaxLpath%
ECHO Restructotl "1"; >> %MaxLpath%
ECHO Closeotl "1"; >> %MaxLpath%
ECHO Unlockobj "1" "%AppName%" "%DBName%" "%DBName%" ; >> %MaxLpath%
ECHO Logout; >> %MaxLpath%
REM **********************************************************************************

REM ************Call ESSCMDQ for Restructure******************************************
call %ESSCMDQpath%\esscmdq.exe %MaxLpath% > "%LogFilePath%"
REM **********************************************************************************

del %MaxLpath%

Usage:

You can call the batch script from another batch script with the following syntax:

CompactOTL.bat EssbaseServerName AppName DBName UserName Password

ESSCMDQ Compaction Issues (Please Read Before Running)

Beautiful right?  Right, but again not without some minor flaws.  I have been using this script for many years without any issues until recently.  I have an environment that is running v11.1.1.3 and doing ASO dimension builds twice a day.  Out ASO outlines were starting to get out of hand in size.  I decided to put the compaction code in the twice daily batch scripts to compact after every rebuild.  This worked great for a little while, but eventually it corrupted one of the outlines so bad that it could not be recovered except from a backup.  This corruption was a result of the dimension build not making any changes to the outline.  Apparently compacting an outline that is 100% unfragmented will cause it to corrupt   I am still in the process of researching exactly why this is happening.  Please do not run this utility on every dimension build.  I will post back my findings as I get them.

Encrypting MaxL Passwords Using an Environment File

---

We all know the importance of using an environment file in our batch scripts right?  No?  Well I am not going to lecture you right now on that...  We are going to talk about how you can encrypt your MaxL passwords while still using an external environment file to store credentials and server information.

There is a ton of information out there on all these different Oracle blogs on how to properly encrypt your MaxL Scripts, but there is not too much out there on how to use this encryption while still utilizing an environment file.

For this example, we are going to walk through a series of files I created to demonstrate this functionality. Each file is prefixed with a step number and can be downloaded below if you would like to follow along.

Please note, in order to run these batch scripts included in this exercise, you need to have the Essbase Client installed on your local machine.  Some modifications to the batch scripts will need to be made for versions 11.1.2.2+.

http://www.scottdanesi.com/software/misc/ScottDanesi-PasswordEncryption.zip

Step 1: 01-CreateKeys.bat

This is a batch script that will automatically create a unique set encryption keys for your system.  Run this script as it is to create the file for the next step.

Example File Contents:

echo on
essmsh -gk >02-Keys.txt

Step 2: 02-Keys.txt

This text file is automatically generated by the batch file in the first step.  It will contain a unique set of keys to use for encryption and decryption of your passwords.

Example File Contents:

Essbase MaxL Shell - Release 11.1.1 (ESB11.1.1.3.0B126)
Copyright (c) 2000, 2009, Oracle and/or its affiliates.
All rights reserved.


Public Key for Encryption: 2257,1114744343
Private Key for Decryption: 52844593,1114744343

MaxL Shell completed

Step 3: 03-PasswordInfo.txt

This text file needs to be modified with the Public Key from the "02-Keys.txt" file generated in Step 2 along with the password that you want to encrypt.  This is technically a CSV file using the "pipe" as a delimiter.

Once you have modified this file, continue to the next step.

Example File Contents:

Password|18187,293621927

Step 4: 04-EncryptPassword.bat

For this step, you will run this batch script which will encrypt the password using the Public key that you entered into the file in Step 3.  This script will create a text file containing your freshly encrypted password.

Example File Contents:

FOR /F "tokens=1-2 delims=|" %%A IN (03-PasswordInfo.txt) DO (
SET NewPassword=%%A
SET PublicKey=%%B
)

essmsh -ep %NewPassword% %PublicKey% >05-EncryptedPassword.txt

Step 5: 05-EncryptedPassword.txt

This is the output file from Step 4 that contains your newly encrypted password.  You will need to reference this file when setting up the Environment File in the next step.

Example File Contents:

Essbase MaxL Shell - Release 11.1.1 (ESB11.1.1.3.0B126)

Copyright (c) 2000, 2009, Oracle and/or its affiliates.

All rights reserved.



Encrypted Data: 031017652655928821892075652309090891


MaxL Shell completed

Step 6: 06-EnvFile.txt

So here we are...  We now have everything that we need to populate the environment file.  This environment file is going to hold 4 peices of critical connection information for our test script.  For this example, the setting must be in the following order; username, encrypted password, server name, and private key from the 02-Keys.txt file.  Each of these values needs to be separated by the pipe | delimiter.  Take a look below for my example.

Example File Contents:

admin|031017652655928821892075652309090891|localhost|205053283,293621927

Step 7: 07-Test.bat

This is our main batch script for testing that our environment file is working properly.  It first reads from the environment file that we updated in Step 6 and sets the variables accordingly.  It will then run a MaxL script that I included that logs into Essbase and logs back out (08-Test.mxl).  This batch also creates the "09-TestMaxLOutput.txt" file so that you can verify that everything worked.

Example File Contents:

FOR /F "tokens=1-4 delims=|" %%A IN (06-EnvFile.txt) DO (
SET username=%%A
SET password=%%B
SET servername=%%C
SET key=%%D
)

echo on

essmsh -D 08-Test.mxl %key% %username% %password% %servername%

That is pretty much it!  So don't forget to download the zip file that contains all of this sample code and start experimenting for yourself.  Feel free to post any questions int he comments below and I will answer.

Thanks!

Properly Passing Commands With Your Batch and MaxL Scripts

---

I know that this may be a bit of a beginner topic for some of you, but I feel this is an essential part of creating the brotherly bond between your batch scripts and your MaxL scripts.  This post will also contain some of my best practices around this topic which will make your life much easier down the road.

Passing commands from one batch script to another is relatively simple, as long as you adhere to a standard when doing so.  For this example, we will pass the following information from one batch to another.

• Server Name
• Application Name
• Database Name
• Username
• Password

First we will create a "parent" batch script.  This script will use the call function to start up another "child" batch script within the same cmd window.  Below is an example of the "parent" script we will use.

Example 1 (Parent Batch Script):

REM *******************************************************
REM Script Name:   parent.bat
REM Purpose:       Example of how to pass commands
REM Process:       High level process list
REM *******************************************************

@ECHO ON

REM ****************** Call Child Batch *******************
CALL child.bat server1 application1 database1 scott password

This example above should be pretty self explanatory   The first thing we are doing is turning on the ECHO command to enable screen output and then we are calling the child.bat to execute.  You will notice in this example how we pass the connection information to the child.  

It is very important to note that the space represents a divider among multiple commands.  So if you are passing a value to the child script that has a space in it, you must put it in double quotes.  See the example line below where I have changed my password to one with a space in it. 

CALL child.bat server1 application1 database1 scott "pass word"

It also does not hurt to put the double quotes around commands that do not have a space either.  (see below)

CALL child.bat "server1" "application1" "database1" "scott" "pass word"

Now that we have successfully setup our Parent batch to pass commands to our child batch, we can now work on ensuring that the child batch script can accept these commands.  Below is an example of the child batch script and how to best manage these passed commands.

Example 2 (Child Batch Script):

REM *******************************************************
REM Script Name:      child.bat
REM Purpose:          Example of how to receive commands
REM                     and pass them to a MaxL Script
REM Process:          High level process list
REM Passed Commands:  1) Server Name
REM                   2) Application Name
REM                   3) Database Name
REM                   4) UserName
REM                   5) Password
REM *******************************************************

REM ****************** Set Variables **********************
SET ssd_server_name=$1
SET ssd_app_name=$2
SET ssd_db_name=$3
SET ssd_user_name=$4
SET ssd_password=$5
REM ****************** End Variables **********************

@ECHO ON

REM ****************** Call Child Maxl Script *************
CALL essmsh child_maxl.mxl %ssd_server_name% %ssd_app_name% %ssd_db_name% "%ssd_user_name%" "%ssd_password%"

You will notice a few things in this example. First, the way that the child batch script accepts the passed commands is with a dollar sign followed by a number.  This number represents the numeric position of the passed command.  Second, you will notice that I have assigned all of these passed commands to local variables.  This will help determine what the batch is expecting to receive from the parent batch script and also make referencing these command values much more user friendly.  Lastly, I have put quotes around some variables in the MaxL script call that could potentially have spaces in them.

MaxL scripts are a bit different in how they handle and accept these commands.  Below, I have posted the script of my test MaxL (child_maxl.mxl) that we called above.

Example 3 (Child MaxL Script):

/*******************************************************
Script Name:      child_maxl.mxl
Purpose:          Example of how to receive commands
                   in a MaxL script
Process:          High level process list
Passed Commands:  1) Server Name
                  2) Application Name
                  3) Database Name
                  4) UserName
                  5) Password
*******************************************************/

/****************** Set Variables **********************/
SET ssd_server_name=$1;
SET ssd_app_name=$2;
SET ssd_db_name=$3;
SET ssd_user_name=$4;
SET ssd_password=$5;
SET mxl_logfile_path="C:\Logs\child_maxl.log"
/****************** End Variables **********************/

spool on to $mxl_logfile_path;

login $ssd_user_name $ssd_password on $ssd_server_name;

logout;
spool off;
exit;

You can see that the MaxL script is similar to the batch syntax with some slight variations.  In this case, I passed 5 commands to the MaxL script, but only used 3 of them.

That's about it for a high level overview of how to pass commands from batch scripts to MaxL scripts.

As always, post your questions or comments in the comment section below and I will respond.

How Not to Structure Your Essbase Fix Statements

---

Fix statements...  The most essential concept to understand when writing calc scripts in Essbase.  I am going to be talking a little bit about best practices around fix statements and what not to do with them.  I will be referencing the Sample.Basic database that we all love so much just to keep the code simple.

Below is a high level screenshot of the Sample.Basic outline that we will reference for this discussion:

So, lets get right into why you came here...  ...the fun examples of what not to do.  Below is an example of what our fictional Essbase developer, "Jimmy", has done.  Even though the syntax validates and the calc functions, for the most part, as it should, lets discuss what is wrong with it.  I will show an example at the end describing how you should structure this clear script efficiently and effectively.

fix(jan,feb,mar);
     fix(actual);
          fix("100-10");
               fix("new york");
                    fix(sales,cogs);
                         clearblock all;
                    endfix;
               endfix;
          endfix;
     endfix;
endfix;

#1: "The Zipper" or "Pyramid"

I am sure the first thing you notice about this example is that Jimmy has nested all of his FIX statements separately, calling out a member from each dimension in order to clear a select set of blocks.

This method may look nice to some, but to an experienced Essbase developer, this is not nice.  In fact, this is actually a less efficient way of clearing these blocks.  By opening a new fix statement for every dimension he is actually slowing down the calculation unnecessarily.  You can simply put all of the members in one statement to make this much more efficient.

#2: "Manic Semicolons"

Jimmy has added more sloppiness to his calc script.  The Manic Semicolon happens more often than you would think.  Some developers, like Jimmy, do not realize that a FIX statement does not need a semicolon.  This really doesn't hurt performance, but it sure does make Jimmy look like he does not understand what he is doing.

#3: "Unrestrained Members"

The last thing you ever want to do is leave any member unrestrained like Jimmy has done here.  Members should always be contained within quotes.  This may just sound like a pet peeve, but it can save headaches down the road.  You will notice that "New York" and "100-10" are contained in quotes.  This is not by accident.  These members would cause the calc script to fail validation if they did not have these protective quotes around them.  The reason for this is because New York has a space in the member name and the system will try to resolve it as two separate members, "New" and "York".  The product 100-10 also would fail validation because the system assumes this is the number 100 minus 10 or 90.  No good...

#4: "Underutilized Functions"

Jimmy is what I would qualify as a caveman coder, similar to the "guy" that unplugs a lamp to turn it off when he could have just flipped the switch on the wall.  You will notice in his first FIX Statement, he is trying to include the months in the first quarter of the year in his script.  Lucky for him, there is an easy to use function called "@CHILDREN" that he can use instead of hard coding the first 3 months.  I realize that this is not an extreme case, but it is a good idea to get in the habit of thinking about your calc scripts in this dynamic way.  The syntax that should be used in this case is @CHILDREN("Qtr1") which will automatically grab Jan, Feb, and Mar.

#5: No Respect for Next Person

Clearly Jimmy has no respect for the next developer that will be modifying this script in the future.  You can tell this from 2 key facts, proper capitalization not used and the lack of comments.

Proper capitalization may not seem very important, but it really improves the overall readability of the script syntax.  It really lets the developer after you know that you took your time with the script.  Some guidelines for properly capitalizing items within year script are as follows.  Member names should always match the case of how they are stored in your outline.  Functions, like FIX and @CHILDREN should be in a caps to make them easier to read.

Comments, need I say more?  Commenting code is something that every programmer should have ingrained in their coding techniques.  This not only helps the developer after you figure out what the script is doing, but helps you debug as you are developing.  Another thing to add to the script is a header comment that describes which script you are looking at, what is is suppose to do, when it was written, who wrote it, and what the high level process is of the calc script.  This will save many headaches down the road.

#6: Overruled Outline Order

Looks like Jimmy has done it again...  His nested FIX statements are not in outline order.  This order does not affect the script from a technical standpoint, but it sure does make it easier to figure out which members are from each dimension (see #5 above).  What Jimmy should have done is put all of his members in one FIX statement and group them by dimension.  You will notice in the best practice example below, this has been organized properly.

How Should It Really Look?

After coaching Jimmy through my best practices, he re-wrote his script and gave it back to me.  Much better!  What do you think?

/************************************************
Script Name: CLR_EX.csc
Script Purpose: This script will clear Actual Sales and COGS for New York for the "100-10" product
Date Created: 2012-06-21
Created By: Jimmy Smith (ACME Consulting)
Process:
1.  Clear data
************************************************/
FIX(
     @CHILDREN("Qtr1"),  /* Year */
     "Sales", "COGS",  /* Measure */
     "100-10",  /* Product */
     "New York",  /* Market */
     "Actual"  /* Scenario */
)
     CLEARBLOCK ALL;
ENDFIX /* Year, Measure, Product, Market, Scenario */

Please note that the best practices discussed here are the ones that I have collected over the years that that I personally believe are the best ones. There is no law of the land in this space, but these are the best practices that I drive on all of my projects.

Innovative Way to Structure Essbase/Planning Account Dimension

---

Hey Everyone,
I wanted to share with you today a pretty innovative, and different  way to structure an account dimension in Essbase or Planning.  This method is not new and has been passed down from generation to generation from ancient times when planning could be installed from 3.5" floppy disks, slowly evolving into what I am going to describe below.  This will not be ideal to use in all situations, but when it fits, it fits very well.

I am going to cut to the chase and just get right into it.  Below you will find a sample structure of this method for use in a planning application and some notes below about why I personally like and dislike about this structure.

• Account
• Transferred_Accounts (~)
• Loaded_Sales (Stored) (~)
• Loaded_COGS (Stored) (~)
• Input_Accounts (~)
• Sales_Adjustment (Stored) (~)
• COGS_Adjustment (Stored) (~)
• Calculated_Accounts (~)
• Calculated_Sales (Stored) (~)
• Calculated_COGS (Stored) (~)
• Reporting_Accounts (~)
• Total_Earnings (Dynamic Calc) (+)
• Sales (Dynamic Calc) (+)
• Loaded_Sales (Shared) (+)
• Total_Adjusted_Sales (Dynamic Calc) (+)
• Calculated_Sales (Shared) (+)
• Sales_Adjustment (Shared) (+)
• COGS (Dynamic Calc) (-)
• Loaded_COGS (Shared) (+)
• Total_Adjusted_COGS (Dynamic Calc) (+)
• Calculated_COGS (Shared) (+)
• COGS_Adjustment (Shared) (+)

So obviously this example above is pretty simple, but what I am trying to show here is how you can bucketize most of the accounts in a flat list and use them as shared members in the Reporting Hierarchy.  With a flat list of Transferred, Input, and Calculated Accounts it makes processing and locating accounts much easier.

Something to note is that all members under the Reporting_Accounts parent, are either dynamic calc or shared.

I know this is going to get asked, so I will address it right now...  "But Scott, you are telling me that in the reporting account structure my Sales = Loaded_Sales + Calculated_Sales + Sales_Adjustment?  That is not right..."  The answer is like Schrödinger's cat, yes and no.  Yes, the outline physically aggregates these members.  No, because loaded data should be in the Actuals scenario and calculated/adjustment data should be in the Plan scenario.

Below is my list of Pro's and Con's for this method.

Pro's

• Simplifies calc scripts.  For example, if you had a calc script that cleared all of the input accounts for a specific period, you could use @CHILDREN("Input_Accounts") within the fix statement.  This will avoid having to maintain the calc script if a new input account is added to the structure.
• This works for data exports and transfers as well.  
• This method makes locating accounts much easier as you can sort the flat lists of accounts by name in Essbase.
• This method also simplifies security.  For example, you can apply security easily to all Input accounts in one place without having to pick through a large reporting hierarchy.  --Thanks MG!

Con's

• This adds complexity for end-users since they need to be trained to only pull from the Reporting hierarchy at the bottom of the dimension.
• Since this in a non-conventional way to structure your accounts, some administrators might think you are crazy.  There is a very small gap between crazy and innovative.

I would love to hear some feedback on this approach to see what some of you other experts think of this.  Also as always, let me know if you find any errors in what I am posting.  Thanks for reading!

Using Blat Statements in Your Windows Batch Scripts

---

Blat?

If you look up the word "Blat" on Wikipedia, the first definition that comes up is "a form of corruption in Russia and the Soviet Union".  Strange, but don't worry, that is not what I will be talking about today.

The Blat that I am going to be talking about today is a public domain windows command line utility that sends email using SMTP.  This tool is extremely handy when creating windows batch scripts because it allows you to easily send email updates during your batch process.  Blat must be installed on the server by an administrator and configured with an SMTP email account to work properly.

Where to Get Blat

Blat is available for free from http://www.blat.net.  Since the Blat application is in the public domain, there is no cost and very little limitations associated with it.  Be sure to check the license agreement on their website for more information.

Installation / Configuration (SMTP)

The installation process is pretty straight forward as long as you are comfortable running command line applications, which I am assuming you are, otherwise this blog would be pretty boring.  I am only going to talk about configuring SMTP for Blat at this time.  Blat is capable of POP3 and NNTP (posting to Usenet)

Installation Step 1: Unpacking the zip file

The first thing you need to do is extract the contents from the zip file.  Below I have outlined the folder structure in version 307 32bit.

• blat307
• full
• blat.dll
• blat.lib
• blat.exe
• blatdll.h
• docs
• ChangeLog.txt

Extract these files into a permanent directory on the machine in which you run your batch scripts.  I have seen some administrators use c:\windows\system32, but it is probably best to install it to its own directory in "Program Files" to keep the system32 directory clean.

Installation Step 2: SMTP Configuration

For this step you will need to have a dedicated email account set up and the following information on hand.

• Email address
• SMTP server name
• Email User ID
• Email Password

Open up a command window and navigate to the Blat directory (wherever you created it).  Once there, we need to configure the utility to store the default SMTP information for sending emails from the system.

Below is the syntax for the install switch.

-install[SMTP|NNTP|POP3] <server addr> <sender's email addr> [<try n times> [<port> [<profile> [<username> [<password>]]]]]

The <try n times> and <port> may be replaced by a '-' to use defaults.  The default number of tries is 1 and the default port for SMTP is 25.  The <profile> can also be replaced by a '-' if you plan on specifying a username and password.

Below is an example of the syntax I used to get Blat installed on my local machine.  You may have to modify this syntax unless your email address is me@myemailserver.com.
C:\Program Files\Blat\full>blat -installsmtp smtp.myemailserver.com me@myemailserver.com - - - MyUserName MyPassword

So go ahead and modify that string with your email connection information and execute it.  Once complete, you should get the message below stating a successful configuration.

SMTP server set to smtp.myemailserver.com on port 25 with user me@myemailserver.com, retry 1 time(s)


Please note that when setting up Blat with a default SMTP connection, this connection information is stored in the registry.  This includes the Username and Password.  Don't worry too much though, the password is stored as an encrypted string within a registry key.

Congratulations!  Your Blat utility is now should be configured!  Let's move to the next and final step...

Installation Step 3: Testing

Now that you have successfully configured your Blat utility, lets test it out.  You should still have your command window open to your Blat directory.  If not, do that now.

We are going to test sending an email by using the bare minimum amount of commands as possible.  

The ones we will use are:

• -to
• -subject
• -body

You can probably imagine what my example is going to look like, but I am going to post it anyways...  See below.

C:\Program Files\Blat\full>blat -to scott@whatever_his_email_is.com -subject "Great Job!" -body "Scott, you are doing a great job with your blog.  I will subscribe and read every post!"

After executing this command, you should get a confirmation message from the Blat utility stating the sender and receiver of the email.

Now with this power comes responsibility.  I do not recommend sending your co-workers fake lottery numbers from their "future self" by using the -f switch which will override the default sender's email address making it appear they sent a message to themselves.  That is not nice.

For a full list of switch commands please check out the syntax guide on the Blat website below.
http://www.blat.net/syntax/syntax.html

Using Blat Statements in Your Windows Batch Scripts

Now that you understand the technical aspects of how Blat statements function, lets talk a little about how to use these in your windows batch scripts.  I will write a little about a few different types of ways to use this to your advantage.

Simple Process Updates
Email communications are probably most commonly used as status updates in a process. Whether the process is is at the start, middle or end.  The key thing to having meaningful status updates is to be sure that the subject lines are consistent.  I usually start the subject line with the name of the process that is being run and following it up with a short description of the update.  For example: "AM Actuals Load: Process Started".  The consistency allows you to easily watch different processes that may be running in parallel.

Error Notifications

To me, this is the most important use of email notifications within my scripts.  I don't really have to go into detail why because it pretty much speaks for itself.  I posted some sample code about how to configure batch scripts for MaxL error checking previously.  This code had the following Blat statement in it.

findstr /m "ERROR" C:\LogFiles\log_file_name.log
if %errorlevel%==0 ( blat -t me@email.com -priority 1 -s "AM Backup: Level0 backup failed" -body "Level0 backup failed. Aborting Process." -attacht C:\LogFiles\log_file_name.log
exit )

In this example, I am sending a high priority message to myself with the log file attached to the email message.  I used the -priority switch to specify that the priority of the message will be 1 or High and I used the -attacht to attach the log file directly to the message.  Attaching the log file directly to the failure notification is priceless when trying to turn around a resolution quickly.

That's about it.  Happy emailing!

Error Checking MaxL From a Windows Batch Script

---

Hey Everyone,
I thought I would share a simple way to error check MaxL output using a windows batch.  I know there are many different methods out there, but this is the one that I like to use.

Please note:  I have removed all dynamic references from the code samples below and hard coded sample paths, server, and credentials to make them as generic and as easy to adapt as possible.  Also, I am making the assumption that Essmsh is in the server path so it can be called directly.  In versions 11.1.2.1 and up you will need to call the startmaxl.cmd instead of essmsh as Oracle has phased out essmsh.

Step 1: 

Ensure the MaxL Script is set to spool out to a log file by adding the following code to the beginning of you MaxL.  It is also a good idea to set the time-stamp functionality to "on".

/***************** Record session to a log file *********************/
spool on to C:\LogFiles\log_file_name.log;

/***************** Enable MaxL Timestamp ****************************/
set timestamp on;

Also make sure to include the "spool off" command at the end of your MaxL script to ensure that the log file gets closed by the operating system.  (see code below)

spool off;
exit;

Step 2:

Setup the batch script to call the MaxL and perform the error checking on the MaxL log file.

REM  ----------------------------------------------------------------------------
REM  ---------------- Execute MaxL script to extract level 0 data ---------------
REM  ----------------------------------------------------------------------------
call essmsh C:\MaxL\BackUp_level0.mxl [Here you would have dynamic references including server name, credentials, log file name, log file path, etc.]

findstr /m "ERROR" C:\LogFiles\log_file_name.log
if %errorlevel%==0 ( blat -t me@email.com -priority 1 -s "AM Backup: Level0 backup failed" -body "Level0 backup failed. Aborting Process." -attacht C:\LogFiles\log_file_name.log
exit )

Example of MaxL Log File Error:

Below is an example of a portion of a MaxL log file where we were encountering mysterious network errors.

MAXL> login Username Password on ServerName;

 OK/INFO - 1051034 - Logging in user [Username].
 OK/INFO - 1051035 - Last login on Thursday, June 21, 2012 10:08:07 AM.
 OK/INFO - 1241001 - Logged in to Essbase.

      essmsh timestamp: Thr Jun 21 10:08:07 2012

MAXL> import database APP.DB data from server data_file "DataFile.txt" using server rules_file 'SSD_RULE' on error write to "C:\ERR\dataload.err";

   ERROR - 1042013 - Network error [10054]: Cannot Receive Data.
   ERROR - 1042012 - Network error [10054]: Cannot Send Data.
   ERROR - 1241101 - Unexpected Essbase error 1042013.

So what is this batch script actually doing?

The first step in the batch is to call the MaxL that we setup in Step 1.  This will generate the log file specified in the MaxL script every time it runs (C:\LogFiles\log_file_name.log).  This log file will reside on the server that actually kicks off this script.  If you run it multiple times, it will overwrite the one that existed before.  Note that after this essmsh command, you would normally pass server names, credentials, paths, etc. to the MaxL script for further processing (which we are not going to discuss right now).

After the MaxL script has finished up the next thing the batch will do is look for the word "ERROR" anywhere within the log file.  The findstr function is case sensitive, so beware.

This next step is now testing to see if the line before found the word in the log file.  Now before you look at this next line and say "Hey Scott, your code is incorrect!  You are saying that if there is no error then exit!"  This appears so, but it is not the case.  The line "If %errorlevel%==0" is actually testing the "findstr" line.  If the findstr function does not find the word "ERROR" in our log file, it will throw an error itself.  This is a "good error"!  So if the errorlevel for the findstr function is 0, then we have a problem...

The nested steps within the if statement are pretty simple as well.  The first step is to send an email to me notifying me of the problem, and then the final step closes the batch script so that it does not continue.  More information on the Blat command can be found here.

You can also insert other lines of code within the IF statement.  I usually put in a call to a batch that will backup and archive the log files with an error time-stamp on them or create a flag file notifying other disconnected processes of the error.

That's about it.  As always if you have any questions or find any errors in my posts, please comment so I can respond and correct.