Saturday, September 17, 2011

Essbase 11.x Workshop – Backup and Restore

Purpose: This workshop is meant to test the “transaction logging and replay” feature can be used as an ideal Backup and Restore mechanism for Essbase BSO cubes or not.

Introduction: Once we enable the transaction logging, Outline changes, data loading, calculations and lock and send activities will be logged.

This feature  is applicable for BSO only.

We can expect some impact in the performance.

Test Plan:

1) Enabling Transaction logging on the cube.

2) Take the full data backup from the cube.

3) Do some transactions later.

4) Do retrieve and save. Consider this as a failure point

5) Clear the cube

6) Load the data from the backup.

7) Replay the transactions to arrive at the failure point.

8) Do the retrieval and compare with that of 4.

Use Sample.Basic cube for the testing

Enable Transaction logging:

To enable transaction logging in the essbase.cfg file:

1. To access essbase.cfg from EAS, right-click on the Essbase server, then select Edit-> Properties.

2. Select the Environment tab.

3. To enable logging for the Sample.Basic database, insert the following line in the file:

TransactionLogLocation Sample Basic C:\Hyperion\products\Essbase\EssbaseServer\app\Sample\Basic\trstore NATIVE ENABLE

esb11_backup1

This will create the logs in the C:\Hyperion\products\Essbase\EssbaseServer\app\Sample\Basic\trstore directory.

Be sure to create the directory, and then restart the Essbase service.

4. Load the calcdata.txt into the sample.basic database

esb11_backup1

5. Execute default calculation to get the data aggregated to high level.

6. Do the high level retrieval

esb11_backup3

7. Export all data from the cube.

esb11_backup4

8. Clear the HY2 data using the below script

FIX(@Relative("Qtr3",0), @Relative("Qtr4",0))
CLEARBLOCK ALL;
ENDFIX

esb11_backup5

9. Do some manual adjustments into HY2(Jul to Dec)

10. Do the high level retrieval

esb11_backup6

11. Let us assume at this point cube got corrupted and we have all data backup available at step 7.

12. Clear all data.

13. Do the high level retrieval and see to confirm that all data is wiped off.

esb11_backup7

Restore:

14. Load the all data backup available at step 7.

esb11_backup9

15. Right click on database. Select display transactions

esb11_backup10

16. See the transaction list in the below table. We need to execute the transactions #2 and #3 to restore the cube to the failure point.

esb11_backup11

17. Replay the transactions 2 and 3 and do the high level retrieval.

esb11_backup13

18. Compare this with that of step 10 and validated successfully.

19. I tried to execute the transaction #2 again, got the below error message

Replay Transaction
September 18, 2011 11:07:21 PM IST
Error : message from server [Replaying a request that has an older start time [Sun Sep 18 22:55:58 2011], than the latest of last replayed request or the last restored backup's time [Sun Sep 18 22:55:59 2011], is not allowed. You may only skip requests]

Findings/ Recommendations

1. For large volume databases, it will have considerable impact on the performance.

2. With Display Transactions, we can get the list of older transactions but we can not replay the transactions older than the latest replay. So we need to be careful while restoring.

3. This might be very useful feature for the small databases with business critical data.