After discussing in
the first part of this article new options in Essbase for
the general backup and restore, this second part will deal with
the also rather new feature of Transaction Logging and Replay, which was released in version 11.1,
enhancing existing restore options.
Tip: Transaction logging and replay cannot be used for aggregate storage databases. Please refer to
the Oracle Hyperion Enterprise Performance Management System Backup and Recovery Guide (rel. 11.1.2.1).
Even if backups are done on a regular, frequent base, subsequent data entries, loads or calculations would not be reflected in a restored database. Activating Transaction Logging could fill that gap and provides you with an option to capture these post-backup transactions for later replay.
The following table shows, which are
the transactions that could be logged when Transaction Logging is enabled:
In order to activate its usage, corresponding statements could be added to
the Essbase.cfg file, using
the TRANSACTIONLOGLOCATION command.
The complete syntax reads:
TRANSACTIONLOGLOCATION [ appname [ dbname]] LOGLOCATION NATIVE ?ENABLE | DISABLE
Where appname and dbname are optional parameters giving you
the chance in combination with
the ENABLE or DISABLE command to set Transaction Logging for certain applications or databases or to exclude them from being logged. If only an appname is specified,
the setting applies to all databases in that particular application. If appname and dbname are not defined, all applications and databases would be covered. LOGLOCATION specifies
the directory to which
the log is written, e.g. D:\temp\trlogs. This directory must already exist or needs to be created before using it for log information being written to it. NATIVE is a reserved keyword that shouldn’t be changed.
The following example shows how to first enable logging on a more general level for all databases in
the application Sample, followed by a disabling statement on a more granular level for only
the Basic database in application Sample, hence excluding it from being logged.
TRANSACTIONLOGLOCATION Sample Hyperion/trlog/Sample NATIVE ENABLE
TRANSACTIONLOGLOCATION Sample Basic Hyperion/trlog/Sample NATIVE DISABLE
Tip: After applying changes to
the configuration file you must restart
the Essbase server in order to initialize
the settings.
A maybe required replay of logged transactions after restoring a database can be done only by administrators.
The following options are available:
In Administration Services selecting Replay Transactions on
the right-click menu on
the database:
Here you can select to replay transactions logged after
the last replay request was originally executed or after
the time of
the last restored backup (whichever occurred later) or transactions logged after a specified time.
Or you can replay transactions selectively based on a range of sequence IDs, which can be accessed using Display Transactions on
the right-click menu on
the database:
These sequence ID s (0, 1, 2 … 7 in
the screenshot below) are assigned to each logged transaction, indicating
the order in which
the transaction was performed.
This helps to ensure
the integrity of
the restored data after a replay, as
the replay of transactions is enforced in
the same order in which they were originally performed. So for example a calculation originally run after a data load cannot be replayed before having replayed
the data load first. After a transaction is replayed, you can replay only transactions with a greater sequence ID. For example, replaying
the transaction with sequence ID of 4 includes all preceding transactions, while afterwards you can only replay transactions with a sequence ID of 5 or greater.
Tip: After restoring a database from a backup you should always completely replay all logged transactions, which were executed after
the backup, before executing new transactions.
But not only
the transaction information itself needs to be logged and stored in a specified directory as described above. During transaction logging, Essbase also creates archive copies of data load and rules files in
the following default directory:
ARBORPATH/app/appname/dbname/Replay
These files are then used during
the replay of a logged transaction. By default Essbase archives only data load and rules files for client data loads, but in order to specify
the type of data to archive when logging transactions you can use
the command TRANSACTIONLOGDATALOADARCHIVE as an additional entry in
the Essbase.cfg file.
The syntax for
the statement is:
TRANSACTIONLOGDATALOADARCHIVE [appname [dbname]] [OPTION]
While to
the [appname [dbname]] argument
the same applies like before for TRANSACTIONLOGLOCATION,
the valid values for
the OPTION argument are
the following:
Make
the respective setting for which files copies should be logged, considering from which location transactions are usually taking place. Selecting
the NONE option prevents Essbase from saving
the respective files and
the data load cannot be replayed. In this case you must first manually load
the data before you can replay
the transactions.
Tip: If you use server or SQL data and
the data and rules files are not archived in
the Replay directory (for example, you did not use
the SERVER or SERVER_CLIENT option), Essbase replays
the data that is actually in
the data source at
the moment of
the replay, which may or may not be
the data that was originally loaded.
You can find more detailed information in
the following documents:
Oracle Hyperion Enterprise Performance Management System Backup and Recovery Guide (rel. 11.1.2.1)
Oracle Essbase Online Documentation (rel. 11.1.2.1))
Enterprise Performance Management System Documentation (including previous releases)
Or on
the Oracle Technology Network.
If you are also interested in other new features and smart enhancements in Essbase or Hyperion Planning stay tuned for coming articles or check our training courses and web presentations.
You can find general information about offerings for
the Essbase and Planning curriculum or other Oracle-Hyperion products here; (please make sure to select your country/region at
the top of this page) or in
the OU Learning paths section, where Planning, Essbase and other Hyperion products can be found under
the Fusion Middleware heading (again, please select
the right country/region). Or drop me a note directly:
[email protected].
About
the Author:
Bernhard Kinkel started working for Hyperion Solutions as a Presales Consultant and Consultant in 1998 and moved to Hyperion Education Services in 1999. He joined Oracle University in 2007 where he is a Principal Education Consultant. Based on these many years of working with Hyperion products he has detailed product knowledge across several versions. He delivers both classroom and live virtual courses. His areas of expertise are Oracle/Hyperion Essbase, Oracle Hyperion Planning and Hyperion Web Analysis.
Disclaimer:
All methods and features mentioned in this article must be considered and tested carefully related to your environment, processes and requirements. As guidance please always refer to
the available software documentation. This article does not recommend or advise any explicit action or change, hence
the author cannot be held responsible for any consequences due to
the use or implementation of these features.
