Piecing Together EPMA Automation, Part III

Today concludes the final installment of my “Piecing Together EPMA Automation” series.  This post will be less of a how-to and more of a here’s-how-I-did-it.

In my “Automating EPMA” presentations over the past year, I have focused on EPMA and the EPMA Batch Script Utility, as well as ADS files, the EPMA File Generator, diagnostics, the different EPMA libraries, Calc Manager, automating security via MaxL for Essbase, etc.  That obviously has not been this series; this series can almost be treated as a real life application of the real world problems for EPM(&A) administrators.  The consultants/developers are gone…how do I make my life easier again?

I have been an in-house administrator a couple times, so I completely understand the industry administrator role.  One thing I try to keep in mind when developing a technical solution is how will maintenance be performed?  As with any new EPMA installation, there *many* new features (?), modules and technical solutions that are  new to the in-house administrator.  Keeping the lights on for the new system will not be the same as it was before…so how I can lessen the blow of learning new administrator tasks?  The main way I try to help is by setting up the automation.  If you think about it, if you have an automated process in place for the administrator, they can look at the code and find out the order of operations.  Also, it gives them a starting point for figuring out issues outside of the Administrator’s Guide and Documentation.

So…how do you do this in an environment where the Essbase cube is deployed from EPMA (as it is shared with, say, HFM and Planning as far as dimensionality goes) and all the other items are handled in EAS (calc scripts, data loads, data clears, etc)?

Recently I wrote a script to automate some of the administrator tasks that go back and forth between EAS and EPMA.  This also performs error trapping and stops whenever an error is encountered.  Here are some of the key points to the script:

• The script is written in DOS
• The administrator/user will see a menu list of options
• There is specific error handling for EPMA and EAS
• The script keeps the user apprised of the progress of the script
• If the steps keep passing, then the script keeps running.  The script will stop when an error has been encountered.
• More error handling can be added (using methods shown yesterday) or removed, depending on your goals.
• In this example, the components of the update were broken out into pieces into folders on the server.  These components are called through the script.
• The individual components each have their own log files (and error files, if necessary) and are combined into one, large log file at the end of the script for ease of viewing.

I will start by stepping through the different portions of the script, followed by a few examples.

DOS Layout
(Note that I am using UltraEdit as it makes DOS color coded…and is easier on the eyes.  But the code is the same in Notepad on the server.)
I feel the best place to start is with the construction of the DOS batch file.  There are 2 main parts:

• Menu section
• Selection process

The Menu sections lays out the options to the user and then tells the system where to go when a selection is entered:

You will notice that the top starts with “:Menu”.  This tells DOS to present a menu option to the user.  The second part to this section is reading what the user wrote for an option then telling the system where to go for the commands.

The second section, the Selection Process, is defined by you and is where you build out the process for running the steps needed.

For the first example, if we choose to update the “Sample” outline from EPMA, we will choose “1”.  This is what the user will see:

When “1” is chosen, the script will forward to where “:Sample” is in the script”:

Before I list out what we need to do, I want to go through the steps necessary (for the environment this is sampled from) to update the cube from EPMA:

1. Validate the outline in EPMA (why go through the remaining steps if the outline won’t deploy from EPMA later on?).
2. Export data from Sample.
3. Clear data from Sample.
4. Deploy Sample from EPMA.
5. Reload data to Sample.

Validate the Outline in EPMA

1. The very first thing I do (for each step) is to delete the previous log files.  MaxL is good about replacing old log files with only the current information.  EPMA will append the current log file to the old and can create an extremely large log file if you are not careful.
2. Run the EPMA script from the batch file
   
3. See what the %ERRORLEVEL% is
   1. If the %ERRORLEVEL% is greater than 0 (success) then stop the process
   2. Show the return code number
   3. Show the EPMAErrors.txt file
      1. Note here that you can do the EPMA batch error trapping here, if you would like.  This original script was around 3000 lines already, so I did not add the additional lines.
   4. Create the updated combined log file
   5. Exit the script
4. If the script returned a code of 0, then move onto the next section

Export the Data from Sample

1. Run the MaxL script to export the data from Sample
   1. Note that no MaxL error trapping was done in this example; rather, an error log file was created and that was used for error checking versus codes since the script is so basic.  If a longer MaxL script was used, performing multiple commands, it would be better to use error codes.
      
2. If an error log file was created (indicating an error), then
   1. Stop the process
   2. Open the error log file onto the screen
   3. Create the updated combined log file
   4. Exit the script
3. If the error log file is NULL (empty), then move onto the next section
   

Clear the Data from Sample

1. Run the MaxL script to clear the data from Sample.
   
2. If an error log file was created (indicating an error), then
   1. Stop the process
   2. Open the error log file onto the screen
   3. Create the updated combined log file
   4. Exit the script
3. If the error log file is NULL (empty), then move onto the next section
   

Deploy Sample from EPMA

1. Run the EPMA script from the batch file
   
2. See what the %ERRORLEVEL% is
   1. If the %ERRORLEVEL% is greater than 0 (success) then stop the process
   2. Show the return code number
   3. Show the EPMAErrors.txt file
      1. Note here that you can do the EPMA batch error trapping here, if you would like.  This original script was around 3000 lines already, so I did not add the additional lines.
   4. Create the updated combined log file
   5. Exit the script
3. If the script returned a code of 0, then move onto the next section
   

Reload Data to Sample
One note here before I start…this script is dynamic to the point that if the export got to 6 files, then they can all be loaded.  In reality, only 2 files are created, so there is room for “data growth”.  There are probably other ways of testing to see how many files are produced with a given naming convention and I encourage you to use what works best for you!

1. Run the MaxL to reload the data to Sample (note that I have 5 more of these to catch any additional files)
   
2. If an error log file was created (indicating an error), then
   1. Stop the process
   2. Open the error log file onto the screen
   3. Create the updated combined log file
   4. Exit the script
      

Finalize
I will finalize the process by combining all the log files into one and let the user know that everything was a success.

So there is MUCH more you can do to piece this all together.  I am putting my full code list in the “BOX” section of my blog for you to use, modify and make for your own.

Please give me feedback…I like this little tool and want to see it out in the wild!