There’s an automation trick I’ve been using for awhile that I like.  Having talked about automation and such with other people at ODTUG this year, it seems that several people are using this technique or a variant of it on their own systems.

Basically, the idea is that you want to be able to sync your production automation scripts from your test server as easily as possible.  The number one cause of scripts not working after copying them from test to production is because they have some sort of hard-coded path, filename, server name, user id, password, or other value that simply doesn’t work on the server you are dropping your scripts onto.

Therefore, you want to try and write your automation scripts as generically as possible, and use variables to handle anything that is different between test and production.  As an added bonus for making the sync from test to prod just a little bit easier, why not dynamically choose the proper configuration file?

Assuming you are running on Windows (the same concept will work on other platforms with some tweaks for your local scripting environment), one way to handle it is like this: your main script is main.bat (or whatever).  One of the environment variables on a windows server is the COMPUTERNAME variable.  Let’s say that your test server is essbase30 and your production server is essbase10.  On the production server, COMPUTERNAME is essbase10.

Knowing that we can set environment variables in a batch file that will be available in MaxL, we could setup a file called essbase30.bat that has all of our settings for when the script runs on that server.  For example, the contents of essbase30.bat might be this:

SET ESSUSR=admin
SET ESSPW=password
SET ESSSERVER=essbase30

From main.bat, we could then do this:

cd /d %~dp0
call %COMPUTERNAME%.bat
essmsh cleardb.msh

Assuming that the two batch files and the cleardb.msh are in the same folder, cleardb.msh could contain the following MaxL:

login $ESSUSR identified by $ESSPW on $ESSSERVER;
alter database Sample.Basic reset data;
logout;
exit;

Now for a little explanation.  Note that in essbase30.bat I am explicitly setting the name of the server.  We could assume that this is localhost or the COMPUTERNAME but why not set it here so that if we want to run the script against a remote server, we could do that as well (note that if we did run it remotely, we’d have to change the name of our batch file to match the name of the server running the script).  In general, more flexibility is a good thing (don’t go overboard though).  The first line of main.bat (the cd command) is simply a command to change the current directory to the directory containing the script.  This is handy if our script is launched from some other location — and using this technique we don’t have to hard-code a particular path.

Then we use a call command in the batch file to run the batch file named %COMPUTERNAME%.bat, where %COMPUTERNAME% will be replaced with the name of the computer running the automation, which is in this case essbase30.  The batch file will run, all of the SET commands inside it will associate values to those environment variables, and control flow will return to the calling batch file, which then calls essmsh to run cleardb.msh (note that essmsh should be in your current PATH for this to work).  The file cleardb.msh then runs and can “see” the environment variables that we set in essbase30.bat.

If we want to, we can set variables for folder names, login names, SQL connection names/passwords, application names, and database names.  Using this technique can make your MaxL scripts fairly portable and more easily reusable.

In order to get this to work on the production server, we could just create another batch file called essbase10.bat that has the same contents as essbase30.bat but with different user names and passwords or values that are necessary for that server.

For all you advanced batch file scripters out there, it may be necessary to use a setlocal command so the variables in the batch file don’t stomp on something you need that is already an environment variable.  As you can see, I’m a big fan of the %COMPUTERNAME% technique, however, there are a few things to watch out for:

• You might be putting passwords in clear text in a batch file.  You can alleviate some of this by using MaxL encryption, although I haven’t actually done this myself.  The folder with these files on my servers already have filesystem level security that prevent access, and for the time being, this has been deemed good enough.
• It’s difficult with this technique to run automation for test and prod from the same server (say, some central scheduling server).  It’s not too difficult to address this, though.
• If you run the automation from a remote server instead of the Essbase server itself, you may end up with lots of different config files — in which case, you might want to adjust this technique a bit.

As for deploying your test files to your production server, if you’ve set everything up in a generic way, then simply changing the variables in the configuration file should allow you to run the exact same script on each server.  Therefore, your deploy method could literally be as simple as copying the folder from the test server to the production server.  I’m actually using Subversion to version control the automation systems now, but a simple file copy also works just as well