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!

NCOAUG Winter Training Day 2013 Presentation

---

Great News Everyone!  I will be presenting at the North Central Oracle Application User Group Winter Training Day.  I will be discussing Managed Services and how to best utilize a Managed Services Partner.  I am very excited.

Event Info:
2013 Winter NCOAUG Training Day
Friday, February 22, 2013 8:00 AM - 4:00 PM (Central Time)
Drury Lane
100 Drury Lane
Oakbrook Terrace, Illinois 60181
(630) 530-8300

Registration:
http://www.regonline.com/Register/Checkin.aspx?EventID=1179884
Registration deadline is Wednesday, February 13, 2013.

More info can be found at their website: http://www.ncoaug.org/

Hope to see you there!