There are many considerations that must be carefully planned
when addressing an upgrade to version 9 or 11, or creating a backup
strategy. Manually moving all the components involved can take days and
is extremely error prone. There is more to it than moving Essbase
databases. Essbase calc scripts, reports, and load rules have to be
considered. Server variables need to be moved. All the Maxl and
EssCmd scripts need to be copied and changed to reflect the new server and
security model. Security filters need to be copied and altered slightly
if moving to a new version of Essbase. All the security users and groups
need to be created. As with any endeavor of this capacity, it can be time
consuming. The benefits of the improved stability and features far outweigh the
efforts.
Completing this for one server is tough enough. Imagine
if corporate policy dictates that everything has to be done in a QA and/or test
environment before it is moved to the new production area. Now factor in
the number of Essbase servers and the fact that the security model might have
to be consolidated to one (this occurs when upgrading from anything before 9,
to version 9 or 11). Don’t forget that there is only a very small window
for the current production servers to be down. If 4 Essbase servers
exist, this effort might have to occur 12 times!
Doing the same work 3 times for every server is obviously
redundant. I developed a small .NET application that significantly
reduces the work involved. It virtually eliminates the need for any
manual or redundant effort. .NET was selected because it was the quickest
for me to develop the application, but JAVA, Perl, or any other similar
development language could be used. The .NET application accepted the
results of the following Maxl display commands.
display application all;
display database all;
display filter row all;
display variable all;
display privilege group all;
alter system load application all;
display partition all advanced;
Maxl scripts were created from the process to
-
create
all the applications and databases
-
assign all relevant application and
database settings
-
rebuild and
update security filters
-
replicate all
server variables
This Maxl can be executed on the destination server to setup
the new environment. Examples of the scripts generated from the .NET
application below.
/* Create
Application: BUDGET */
create or replace
application 'BUDGET' type nonunicode_mode;
alter application 'BUDGET'
set lock_timeout after 300;
alter application 'BUDGET'
set max_lro_file_size unlimited;
alter application 'BUDGET'
set minimum permission no_access;
alter application 'BUDGET'
enable startup;
alter application 'BUDGET'
disable autostartup;
alter application 'BUDGET'
enable commands;
alter application 'BUDGET'
enable updates;
alter application 'BUDGET'
enable connects;
alter application 'BUDGET'
enable security;
/* Create Database:
BUDGET */
create database
'BUDGET'.'Budget';
alter database
'BUDGET'.'Budget' set data_file_cache_size 1024000000;
alter database
'BUDGET'.'Budget' set index_cache_size 76800000;
alter database
'BUDGET'.'Budget' enable startup;
alter database
'BUDGET'.'Budget' enable autostartup;
alter database
'BUDGET'.'Budget' set minimum permission no_access;
alter database
'BUDGET'.'Budget' set retrieve_buffer_size 102400;
alter database
'BUDGET'.'Budget' enable two_pass_calc;
alter database
'BUDGET'.'Budget' enable aggregate_missing;
alter database
'BUDGET'.'Budget' enable compression;
alter database
'BUDGET'.'Budget' disable create_blocks;
alter database
'BUDGET'.'Budget' disable committed_mode;
alter database
'BUDGET'.'Budget' set implicit_commit after 10000 blocks;
alter database
'BUDGET'.'Budget' disable cache_pinning;
alter database
'BUDGET'.'Budget' set retrieve_buffer_size 102400;
alter database
'BUDGET'.'Budget' set compression bitmap;
alter database 'BUDGET'.'Budget'
set retrieve_buffer_size 102400;
alter database
'BUDGET'.'Budget' set retrieve_sort_buffer_size 102400;
alter database
'BUDGET'.'Budget' set data_cache_size 512000000;
alter database
'BUDGET'.'Budget' set io_access_mode buffered;
alter database
'BUDGET'.'Budget' set note '';
alter system unload
application 'BUDGET';
/* Create Filter:
MRP100206310000 */
create or replace filter
'BUDGET'.'Budget'.'Audit' write on
'@DESCENDANTS("Time"),@DESCENDANTS("Year"),"Input","Working
Budget",@DESCENDANTS("Product"),@DESCENDANTS("Total
Audit"),@DESCENDANTS("Expenses")';
DOS and UNIX scripts were generated to copy all of the
database objects, data files, and Maxl and EssCmd scripts from the source
server to the destination server. The program also created all the files
to import into Version 9 and System 11 to add users, groups, and replicate the
security model.
All the Maxl and EssCmd scripts (username, password, server
names, file paths, etc.) were updated so they could be executed on the new
servers.
This process makes it extremely simple to migrate, or move,
any Essbase application from one server to another. The entire process
could be completed in hours, rather than days, and eliminates the possibility
of human error. What would be budgeted to take weeks with several
resources can take less than a day.