MaxL Quick Tip - Loading Multiple Files with Wildcards

Hey Everyone,

Call me "late to the party" if needed, but I recently came across a bit of functionality that I had never used before and felt the need to compile a bit of research on the functionality.  Introduced in Essbase v11.1.2.2, there was a new MaxL feature that allows you to use wildcard characters in flat file names when loading a database.

Below is an example of how to import multiple files, in parallel, using these wildcards:

import database SCOTTAPP.SCOTTDB data from data_file "D:\\Data\\ThisIsData*.txt" using SERVER rules_file 'LoadItUp' on error append to "D:\\ErrorFiles\\ThisIsData.err";

This code above will load any file in the D:\Data directory that the name starts with "ThisIsData".  

For example the following files would be loaded:

• ThisIsData.txt
• ThisIsData_1.txt
• ThisIsData_2.txt
• ThisIsData_ButNotGoodData.txt

The following example files wound NOT be loaded:

• ThisIsGoodData.txt
• ThisIsData.csv

Below is an excerpt from the v11.1.2.2 user guide in regards to using wildcards in this scenario.

To import from multiple files in parallel, use the wildcard characters * and/or ? in the IMP-FILE name so that all intended import files are matched.

• * substitutes any number of characters, and can be used anywhere in the pattern. For example, day*.txt matches an entire set of import files ranging from day1.txt - day9.txt.
• ? substitutes one occurrence of any character, and can be used anywhere in the pattern. For example, 0?-*-2011.txt matches data source files named by date, for the single-digit months (Jan to Sept).

Hope this helps!  If you have any questions, please post them below in the comments section and I will answer as quickly as I can.

Mysterious Essbase Error When Loading Data With a Flat File

Hey Everyone,
I know it has been a while since I last posted, but I promise I am still here hard at work.  :)

I recently came across an error message from the output of one of my MaxL scripts that I have never seen before.  So my first thought is, "Hey Scott, why not Google that error message to see what is going on?".  This is usually a pretty safe attack on these types of errors, but in this case, I was mistaken.  At the time of searching for this error, there was nothing on Google about it.  Really, I am serious.  Nothing.

So here it is:
MAXL> import database PINBALL.PARTY_DB data from server data_file "some_great_text_file.txt" using server rules_file "LoadUp" on error write to "D:\EssbaseErr\dataload_some_great_text_file.err";

OK/INFO - 1019061 - Reading Rule SQL Information For Database [PARTY_DB].
OK/INFO - 1019025 - Reading Rules From Rule Object For Database [PARTY_DB].
   ERROR - 1013295 - Server Request Fails with error code [1003086].
   ERROR - 1241101 - Unexpected Essbase error 1013295.

The line I am referring to is the "ERROR - 1013295 - Server Request Fails with error code [1003086]" message.  What is that?

So, since Google failed in allowing me to be lazy, I am going to have to dig down a bit deeper and find out what is going on.  I grab a cup of coffee and prepare myself for a fun little research activity.

First thing I check?  Is the file physically in the app folder...  It is NOT.  So basically, this error message is only trying to tell me that the file I was trying to load into Essbase does not exist.  I didn't even get to take the first sip of my coffee.

Now you may be asking why would I post a problem with such a ridiculously easy solution...  Simple answer.  I did it because no information (at the time of posting this) exists on the internet for this error message.

You are very welcome!

Hope you enjoyed!

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!

Enabling / Disabling Connects in Essbase During Separate App Copy (v11.1.1.3)

---

I found something interesting recently in our EPM 11.1.1.3 environment regarding app copies affecting unrelated processes on the same Essbase server.  For this example lets assume I have an Essbase server with 3 applications on it; App1, App2, and App3.

When running an app copy from App1 to App2 using the syntax below, you cannot enable or disable connects in App3.

create or replace application App2 as App1;

When executing the following MaxL to disable connects, the system will allow me to log into the Server and log out any active users in the App, but will stall on the line that disables the connects until the app copy has completed.  This is very strange behavior since App3s commands should have nothing to do with the app copy going on between App1 and App2.

login Scott MyPassword on MyServer;

/* Logout current users */
alter system logout session on application App3 force;

alter application App3 disable connects;
alter application App3 disable commands;

logout;
exit;

This is also the same for enabling connections as well.  I have not yet came across another MaxL function that is affected by this app copy other than the 2 mentioned above.  In Essbase v6.5, there was an issue with app copies that froze up all users on the server.  I am wondering if this is a leftover bug from back in those days.

Currently, I have an Oracle SR open that will hopefully shine some light on the subject.  So far, the response I am getting is that I should time my batch scripts differently...   I will keep this post updated as I find out more information about this and whether or not it affects the latest version of the EPM suite.

Thanks for reading!

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.

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.