Essbase ASO Outline Grows Too Big – Solution to Fragmentation Using Compact

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.

EASCompact

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 ##################################################################################
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%
Please also note that ASO with Unicode Outline has the following problem:
ESSCMDQ ASO Unicode Outline
That’s all!
Advertisements

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

11 thoughts on “Essbase ASO Outline Grows Too Big – Solution to Fragmentation Using Compact”

  1. I like the valuable info you provide for your articles.
    I will bookmark your blog and test once more here
    frequently. I’m quite certain I will be informed plenty of new stuff proper right here!
    Best of luck for the following!

    Like

      1. the problem here is silmpe: the link is to the wrong file.the -tables- one is a) not packed (so no, its not misssing a suffix) and b) only contains the schema/DDL part, but no data rows at all.the one with mysql5 but without the -tables- in the name is a) packed, b) has filename ending in .bz2, c) actualy contains the data.

        Like

      2. Hi Steve. Thanks for your blog. I tried Maxl approach to compact the outline every day after the cube load. It worked successfully, but OTL file access permissions is getting changed(Non Executable) due to which next time run is getting failed while connecting to cube.

        Fyi..i am using essbat credentials for executing the cube load Maxl script.

        Any suggestions are much appreciated on this!!!

        Like

      3. Thanks for the update Steve. I would like to perform the outline compact process after everyday cube load, so i hope Esscmdq approach won’t meet my requirement as it is a one time job.

        Please correct me if my assumption is wrong

        Like

      4. Hi Steve,

        As per the requirement, i have set up Dimension Build Setting update as “Remove Unspecified” instead of “Merge”, due to which after every day’s cube build, the outline size is increased(As Removed Members are logically clearing but not physical). This is proportionally affecting my cube build time as well.

        Now, when i try to optimize it, i luckily saw your blog and implemented Maxl Approach for compacting the outline file size. But after i did it, my .OTL file access permissions got changed and i am unable to rerun the cube build job as it is failing due to .OTL permission issue.

        Hope i explained you the details. Any thoughts are much appreciated!!!

        Like

      5. I never actually came across the scenario having permissions getting changed. Moreover, I don’t exactly like how the compact works with EAS / MaxL as it doesn’t do the job perfectly. What does the error say though? How do you know it’s a permission issue?

        Like

  2. Hi Steve. Thanks for your blog. I tried Maxl approach to compact the outline every day after the cube load. It worked successfully, but OTL file access permissions is getting changed(Non Executable) due to which next time run is getting failed while connecting to cube.

    Fyi..i am using essbat credentials for executing the cube load Maxl script.

    Any suggestions are much appreciated on this!!!

    Like

  3. Hi Steve,

    As per the requirement, i have set up Dimension Build Setting update as “Remove Unspecified” instead of “Merge”, due to which after every day’s cube build, the outline size is increased(As Removed Members are logically clearing but not physical). This is proportionally affecting my cube build time as well.

    Now, when i try to optimize it, i luckily saw your blog and implemented Maxl Approach for compacting the outline file size. But after i did it, my .OTL file access permissions got changed and i am unable to rerun the cube build job as it is failing due to .OTL permission issue.

    Hope i explained you the details. Any thoughts are much appreciated!!!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s