Business Intelligence – Oracle

Hyperion Essbase 11.1.1.0 – 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 11.1.1.0 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 11.1.1.0 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

TRANSACTIONLOGDATALOADARCHIVE Demo Basic SERVER_CLIENT
TRANSACTIONLOGLOCATION Demo Basic D:\EssbaseReplay NATIVE ENABLE

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.

About these ads

9 Responses to “Hyperion Essbase 11.1.1.0 – Database Archive and Replay in BSO Cubes”

  1. Varma said

    Mr. Venkatakrishnan,

    Thank you very much, it is informative..!!

  2. […] Venkatarishan J has posted an extensive article on Hyperion. Data Archive and Replay in BSO Cubes. […]

  3. Claudio Tito said

    MR. Venkatakrishnan

    I’ am an oracle italian student, in your post i didn’t understand if i can apply the “archive” option also on ASO cubes (in the 11 version). you’ve written that i can’t, but the example use the ASO samp cube, so what can i do??

    please help me..

    I want also thank you for all the great informations written in this blog!!!

    Claudio Tito

  4. Claudio Tito said

    OH! Sorry for my mistake.. Now i’ve seen the picture properly..

    many thanks..

    Tito

  5. jack said

    very helpful,thanks so much!

  6. colin said

    Thanks!
    It is so helpful!
    I would like to translate your articles in Chinese and reprint them on my personal blog—http://hi.baidu.com/thirdline.
    It is just my personal ideas.
    may i get you permission?

  7. […] Hyperion Essbase 11.1.1.0 – Database Archive and Replay in BSO Cubes One of the new features that was introduced in the EPM 11.1.1.0 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… Hyperion Essbase 11.1.1.0 – Database Archive and Replay in BSO Cubes […]

  8. Rahaman said

    Hi Venkat,
    8+4 forecast process is erroring out in production
    We need to understand whether it is mandatory to log out all users at the time of data load because
    What we know that this is needed for outline restructure only, i.e. hierarchy refresh load.
    In the past we have done many loads (actual load) when users have been on the system and actual load has not error out.
    Please clarify the fact and confirm to us that no users should be on the system at the time of data load.

    Thanks,
    Rahaman

  9. Arun said

    Hello Venkatakrishnan,

    The thing you are doing is really helpful to new comers like me. I’ve a query, From the information you’ve provided above, i can understand that in 9.3.1 MaXL version doesn’t support for Archive. But, I’ve tested in BSO cube, its working fine. Please advise me , if am wrong.


    Thanks,
    Arun

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

 
Follow

Get every new post delivered to your Inbox.

Join 158 other followers

%d bloggers like this: