Business Intelligence – Oracle

Archive for February 18th, 2009

Hyperion Essbase – Database Archive and Replay in BSO Cubes

Posted by Venkatakrishnan J on February 18, 2009

One of the new features that was introduced in the EPM release is the ability to restore an Essbase database back to its original state after a database failure. Typically, restoring a database from a backup always restores the database back to the point in time when the backup was taken. But there could have been transactions/dataloads that could have happened after that backup. Since these new transactions/data loads are not part of the back up, till the previous release(9.3.1), we could restore the database only to the point of time of the backup. But now in the current release, it is possible to restore the new transactions/data loads related data as well using the Database Replay feature. Before we see how this replay feature works, lets quickly understand the basics of database backup in Essbase.

BSO Cubes can be backed up in multiple ways. The most commonly used feature is the Database Archiving feature. This is supported only for the BSO cubes in the version. In 9.3.1, archive works(only the ESSCMD version works. The maxL version would not work. Before using ESSCMD on ASO cubes in 9.3.1, better get a confirmation from Oracle Support as the documentation clearly says it is not supported) even in ASO databases. The archive works in 3 steps. They are

1. Put the database in read-only mode by using the begin archive command.
2. Manually backup all the files listed in the archive file generated as part of the begin archive command.
3. Revert the database to the normal mode by using the end archive mode.

Basically the database begin archive command does the following

1. All the modified data which have not been committed so far onto the disk would be committed.
2. Puts the database in Readonly mode
3. Creates a file which will basically list down all the Essbase specific files that need to be backed up

As you see above, archive command only facilitates easy backup. It itself does not do the backup though which was the case till the last release. But now the backup can be done as well using the Archive and Restore feature in the current release. Lets first use the traditional approach as shown above using MaxL

alter system logout session on application 'Demo';
alter application 'Demo' disable connects;
alter database 'Demo'.'Basic' begin archive to file 'D:\DemoBasicArchive.lst';

Now the above maxL would generate the archive list file shown below.

Once this file has been generated, these files would have to be backed up. Or the entire app folder can be backed up using external softwares. In the current release even that backup has been made simple by the maxL grammar shown below

alter database 'Demo'.'Basic' archive to file 'D:\DemoBasicArchive.arcfile';

The above command can also be done from Administration Console itself as shown below

And the same database can be restored again using

alter database 'Demo'.'Basic' restore from file 'D:\DemoBasicArchive.arcfile';

Now that we have seen how to backup/restore an Essbase database, lets look at the Database Replay option. The database replay option works on the premise that Transaction logging has been enabled on the database. This is done through a couple of settings on the Essbase.cfg file. The settings are given below


The above setting in the Essbase configuration file basically lets Essbase know about the directory where all the transactions after a backup are stored. The important aspect of this feature is this can be used like the Flashback option in Oracle database. To test out the Replay lets follow a simple sequence

1. Archive the database
2. Update the data in Essbase using Excel Add-in
3. Restore the database
4. Enable Replay to see whether we are getting the updated data from Excel Add-in

Lets take a very simple Excel add-in report as shown below. This is the data that we have before doing an archive.

Now lets go ahead and archive the cube.

After the archive, lets go ahead and update the data in Excel as shown below. After changing it update the cube using the Lock and Send mechanism.

Now, lets restore the cube back using the Restore option. Before restoring, we would have to stop/unload the database.

If we go back to Excel, we would get the data that we had before the Lock and Send transaction.

Now, lets go and apply the Replay Transactions to restore the transactions that happened after the backup.

This should revert back the data that we had updated from Excel.

This replay option can be used for rule files and also the data files.

Posted in All Posts, EPM, Hyperion Essbase | 9 Comments »