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!

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.