Compacting ASO Outline – Defragmenting

Why does it grow?

In BSO, defragmenting the cube is actually quite simple. One way is to do a full restructure and the other way is to export, clear all blocks then re-import the data. So you think ASO doesn’t need to defrag since it doesn’t contain any blocks? Too bad, you’re very wrong. ASO fragmentation happens when you add/delete/regroup members or delete/add dimensions. So to make it simple, any meta-data changes will cause fragmentation. In fact, even if you use EPMA or work direct from EAS or Essbase Studio and EVEN OBIEE AGGREGATE PERSISTENCE WIZARD, it will still cause fragmentation.

So how much would it grow?

To be honest, I do not have the algorithms on this. But if you update the cube on a daily basis, I should think it will double the size in a few months time.

What happens if I don’t do anything?

If you don’t do anything to it, and when .otl file grows to over 2gb in size, EAS won’t be able to open the outline. Would users still be able to access it? Yes they would. It’ll still work but it doesn’t really feel safe does it?

What are the solutions?

There are 3 ways to compact the outline which in the end is basically defragmenting the outline, each with pros and cons. They are explained as follows:

EAS Compact Function

How?

Using EAS, you will need to expand to the outline then right click and select Compact.

The Good & Bad?

This is the quickest and easiest approach. However, it requires you to do this manually through EAS. In addition, please have in mind that it will only defragment the updated meta-data. So it will still grow to a reasonable size as time goes by.

MaxL Approach

How?

After logging in Essbase using essmsh.exe, you can use the following command:

alter database appname.dbname compact outline;

For more information on MaxL, you can refer to technical reference 11.1.2.3 here.

The Good & Bad?

Well, with MaxL, you will need to write a batch file or enter command prompt to do the compact. There’s no UI for this hence it might not be too straightforward for admins who aren’t comfortable with command prompt. The good part is that you can schedule this job on a daily basis or part of an ETL after it completes.

ESSCMDQ Approach

How?

First step is to Download ESSCMDQ HERE and the PDF Guide HERE.

Of course download the version that matches your Essbase. The below is sourced from Oracle:

The ESSCMDQ and ESSCMDG utilities are used internally by the Quality Engineering team to test the Essbase API. These utilities consist of the same command set as ESSCMD, but also include the Outline API and additional Functional APIs (ESSCMDQ), and the Grid API (ESSCMDG), as commands.
In addition to the two utilities, the documentation describes the command syntax and the required parameters for each of the ESSCMDQ/G commands.

To Install:
1. Download the documentation PDF file ( aso_compact_outline.pdf)
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.

To write a compacted outline to an application:

Login “servername” “username” “password” ;
Select “appname” “dbname” ;
Openotl “2” 1 “appname” “dbname” “dbname” “y” “y” 1 ;
Writeotl 1 “2” 1 “appname” “dbname” “dbname” ;
Restructotl 1 ;
CloseOtl 1 ;
Unlockobj 1 “appname” “dbname” “dbname” ;
LogOut ;

It should complete very quickly and this time, it’s a full defragmentation. Please note that this utility is only for Oracle’s internal Quality Engineering team to test the Essbase API. They recommend that this utility is only used for compacting ASO outlines. Other included features should not be used. It’ll be explained below.

The Good & Bad?

While EAS and MaxL approach both compacts the outline for updated meta-data, the size of the outline would still grow significantly after some time. While, ESSCMDQ utility will fully defragment the outline, a 3GB outline can result in a few MEGABYTES! Yes, I’m not joking. So you might want to schedule this? But not, you should also consider that when you use the ESSCMDQ utility and perform compact on an outline that is fully defragmented, which means if you defrag it 2 times in a row, you will have a chance of corrupting the outline. This is also why it say’s it’s for Oracle’s Internal Quality Engineering team to test the Essbase API. Scott’s blog has also mentioned this issue before (here).

The good part is, you might want to schedule the job every month or few months. If you’d like to do this, please follow to Scott’s blog. He has already provided this. I’ve quoted the script in the following:

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%

Author: Steve Yeung

Being in the EPM & BI field for more than 8 years, it's about time I contribute to newcomers! As a founder of MondayBI.com I wish to give you all the help I can. Feel free to give any suggestions or questions. Hope you will all enjoy this blog! William Wong Essbase Certified Specialist OBIEE Certified Specialist View all posts by Steve Yeung