A nice Perl script that I built in order to manage and size databases caches every night. Not sure that it is perfect but we run it in production since a few months and it seems to work fine up to now. By the way we only have buffered I/O cubes.

The following Perl script takes the following arguments : application and database name and cache type.

 How to call : perl.exe confcaches.pl "application" "database" "buffered"

#!/usr/bin/perl

#----------------------------------------------------------------------------------
# This script calculates the following cache settings for Essbase databases:
# - index cache
# - data cache (Buffered IO or Direct IO mode)
# - data file cache (Direct IO mode only)
#
# The script takes the following arguments:
# - $ESS_APPLI - application name
# - $ESS_DB - database name
# - $cache - cache type (buffered or direct)
#
# Author: S.Roux - 19/10/2009
#
# Note : cache limits are limited in the script to 1 GB (see limit constants)
#
# Licence : feel free to use, reuse and modify the following script in any script or program
#
# Disclaimer : use at your own risk.
#----------------------------------------------------------------------------------

# For log output
print "\n\n----------------------\n";
print "DYNAMIC CACHES SETTING\n";
print "----------------------\n\n";

#----------------------------------------------------------------------------------
# Variables and constants declaration

# Min cache allocated (lowest cache as specified by DBAG)
$INDEXCACHEMINLIMIT = 1048576; # (= 1 MB)
$DATACACHEMINLIMIT = 3145728; # (= 3 MB)
$DATAFILECACHEMINLIMIT = 10485760; # (= 10 MB)

# Max cache allocated
$INDEXCACHEMAXLIMIT = 1073741824; # (= 1GB)
$DATACACHEMAXLIMIT = 1073741824; # (= 1GB)
$DATAFILECACHEMAXLIMIT = 1073741824; # (= 1GB)

# Init
$indtotalsize = 0;
$pagtotalsize = 0;

# Sizing coeff for datacache sizing - DBAG recommends 0.125
$coeffdatacache=0.125;

# Environment variable toward Essbase application path (data disk)
$ESS_APP=$ENV{'ESS_DATA'};
#----------------------------------------------------------------------------------

#----------------------------------------------------------------------------------
# Script arguments testing

# Application
if ($ARGV[0] eq "") {
    print "Application argument not specified in parameters\n";
    print "---------------\n";
    exit;
}
else {$ESS_APPLI=$ARGV[0];}

# Database
if ($ARGV[1] eq "") {
    print "Database argument not specified in parameters\n";
    print "---------------\n";
    exit;
}
else {$ESS_DB=$ARGV[1];}

# Cache type (buffered or direct)
if ($ARGV[2] eq "") {
    print "Cache type (buffered or direct) not specified in parameters\n";
    print "---------------\n";
    exit;
}
elsif ($ARGV[2] ne "buffered" && $ARGV[2] ne "direct") {
    print "Cache type not not specified in parameters (buffered or direct)\n";
    print "-> $ARGV[2]\n";
    print "---------------\n";
    exit;
}
else {$cache=$ARGV[2];}

# Full path toward database (data disk)
my $dir = $ESS_APP."\\".$ESS_APPLI."\\".$ESS_DB;
#----------------------------------------------------------------------------------

#----------------------------------------------------------------------------------
# Gathering of total size for index and data

# Getting files in array (index files .IND)
@files = <$dir\\*.ind>;
if (scalar @files eq 0) {
    print "No index file was found in $dir\n";
    print "---------------\n";
    exit;
}

# Cumulating files size
foreach $file (@files) {
    $size = -s $file;
    $indtotalsize = $indtotalsize + $size;
}

# For log output
$indtotalsizekb = $indtotalsize/1024;
$indtotalsizemb = $indtotalsize/1024/1024;
print "-----Stats-----\n";
print "Total size for ind files: ".$indtotalsize." O (".sprintf("%.0f", $indtotalsizekb)." KO, ".sprintf("%.0f", $indtotalsizemb)." MO)\n";

# Getting files in array (data files .PAG)
@files = <$dir\\*.pag>;
if (scalar @files eq 0) {
    print "No data file was found in $dir\n";
    print "---------------\n";
    exit;
}

# Cumulating files size
foreach $file (@files) {
    $size = -s $file;
    $pagtotalsize = $pagtotalsize + $size;
}

# For log output
$pagtotalsizekb = $pagtotalsize/1024;
$pagtotalsizemb = $pagtotalsize/1024/1024;
print "Total size for pag files: ".$pagtotalsize." O (".sprintf("%.0f", $pagtotalsizekb)." KO, ".sprintf("%.0f", $pagtotalsizemb)." MO)\n\n";
#----------------------------------------------------------------------------------

#----------------------------------------------------------------------------------
# Estimating index cache size
# Index cache contains index pages

# If size > to max limit then we set size to max constant
if ($indtotalsize > $INDEXCACHEMAXLIMIT) {
    $indexcache = $INDEXCACHEMAXLIMIT;
}
# If size < to min limit then we set size to min constant
elsif ($indtotalsize < $INDEXCACHEMINLIMIT) {
    $indexcache = $INDEXCACHEMINLIMIT;
}
# Within limits
else {
    $indexcache = $indtotalsize;
}
#----------------------------------------------------------------------------------

#----------------------------------------------------------------------------------
# Estimating datacache size (BUFFERED IO and DIRECT IO)
# Datacache contains uncompressed data
# cache used for dataload, calcs and retrieve
# datacache = sum of pag file * sizing coeff

# BUFFERED IO
if ($cache eq 'buffered') {
    # Datacache
    # If > to max limit then we set size to max constant
    if ($pagtotalsize > $DATACACHEMAXLIMIT) {
        $datacache = $DATACACHEMAXLIMIT * $coeffdatacache;
        # Rounding
        $datacache = sprintf("%.0f", $datacache);
    }
    # If < to min limit then we set size to min constant
    elsif ($pagtotalsize < $DATACACHEMINLIMIT) {
        $datacache = $DATACACHEMINLIMIT;
    }
    else {
        $datacache = $pagtotalsize * $coeffdatacache;
        # If < to min limit then we set size to min constant
        if ($datacache < $DATACACHEMINLIMIT) {
            $datacache = $DATACACHEMINLIMIT;
        }
        # Within limits
        else {
            $datacache = $pagtotalsize * $coeffdatacache;
            # Rounding
            $datacache = sprintf("%.0f", $datacache);
        }
    }

    # For log output
    $indexcachekb = $indexcache/1024;
    $indexcachemb = $indexcache/1024/1024;
    $datacacheko = $datacache/1024;
    $datacachemb = $datacache/1024/1024;
    print "indexcache to allocate: ".$indexcache." O (".sprintf("%.0f", $indexcachekb)." KB, ".sprintf("%.0f", $indexcachemb)." MB)\n";
    print "datacache to allocate: ".$datacache." O (".sprintf("%.0f", $datacacheko)." KB, ".sprintf("%.0f", $datacachemb)." MB)\n";
    print "---------------\n";

    # Calling maxl script for BUFFERED IO mode cache setting
    system "essmsh.exe %PF_SCRIPT%\\setcaches_buffered.mxl $ESS_APPLI $ESS_DB $indexcache $datacache";
}
#----------------------------------------------------------------------------------

#----------------------------------------------------------------------------------
# Estimating datacache size (BUFFERED IO and DIRECT IO)
# Data cache contains uncompressed data
# cache used for dataload, calcs and retrieve
# datacache =  * sizing coeff
#
# Estimating datafilecache size (DIRECT IO)
# Data file cache contains compressed data blocks
# cache used for dataload, calcs and retrieve
# datafilecache = sum of pag file

# DIRECT IO
elsif ($cache eq 'direct') {
    # Datacache
    # If > to max limit then we set size to max constant
    if ($pagtotalsize > $DATACACHEMAXLIMIT) {
        $datacache = $DATACACHEMAXLIMIT * $coeffdatacache;
        # Rouding
        $datacache = sprintf("%.0f", $datacache);
    }
    # If < to min limit then we set size to min constant
    elsif ($pagtotalsize < $DATACACHEMINLIMIT) {
        $datacache = $DATACACHEMINLIMIT;
    }
    else {
        $datacache = $pagtotalsize * $coeffdatacache;
        # Si le résultat du calcul est inférieur au min on applique le min
        if ($datacache < $DATACACHEMINLIMIT) {
            $datacache = $DATACACHEMINLIMIT;
        }
        # Within limits
        else {
            $datacache = $pagtotalsize * $coeffdatacache;
            # Rounding
            $datacache = sprintf("%.0f", $datacache);
        }
    }

    #Datafilecache
    # If > to max limit then we set size to max constant
    if ($pagtotalsize > $DATAFILECACHEMAXLIMIT) {
        $datafilecache = $DATACACHEMAXLIMIT;
    }
    # If < to min limit then we set size to min constant
    elsif ($pagtotalsize < $DATAFILECACHEMAXLIMIT){
        $datafilecache = $DATAFILECACHEMINLIMIT;
    }
    else {
        $datafilecache = $pagtotalsize;
    }

    # For log output
    $indexcachekb = $indexcache/1024;
    $indexcachemb = $indexcache/1024/1024;
    $datacacheko = $datacache/1024;
    $datacachemb = $datacache/1024/1024;
    $datafilecacheko = $datafilecache/1024;
    $datafilecachemo = $datafilecache/1024/1024;
    print "indexcache to allocate: ".$indexcache." O (".sprintf("%.0f", $indexcachekb)." KB, ".sprintf("%.0f", $indexcachemb)." MB)\n";
    print "datacache to allocate: ".$datacache." O (".sprintf("%.0f", $datacachekb)." KB, ".sprintf("%.0f", $datacachemb)." MB)\n";
    print "datafilecache to allocate: ".$datafilecache." O (".sprintf("%.0f", $datafilecachekb)." KB, ".sprintf("%.0f", $datafilecachemb)." MB)\n";
    print "---------------\n";

    # Calling maxl script for DIRECT IO mode cache setting
    system "essmsh.exe %PF_SCRIPT%\\setcaches_direct.mxl $ESS_APPLI $ESS_DB $indexcache $datacache $datafilecache";
}
#----------------------------------------------------------------------------------
else {exit;}