Ensuring Data Integrity

This chapter tells you about how Hyperion Essbase handles transactions and locking and about other ways that Hyperion Essbase protects data.

This chapter includes the following sections:

If you are migrating your data from a previous release of Hyperion Essbase, see the Hyperion Essbase Start Here booklet.

About Transactions

When a database is in read/write mode, Hyperion Essbase considers every update request (such as a data load, a calculation, or a statement in a calc script) as a transaction. Hyperion Essbase tracks information about transactions in a transaction control table.

The transaction control table (TCT) contains an entry for each transaction and tracks the current state of each transaction.

About Isolation Levels

Hyperion Essbase offers two isolation levels for accessing data: committed access and uncommitted access (the default). You can optimize data integrity by using committed access.

Note:

Some of the behavior of the Hyperion Essbase kernel depends on which type of access is specified.

About Committed Access

Committed access provides a high level of data consistency. Only one transaction at a time can update data blocks. Under committed access, the Hyperion Essbase kernel issues short-term read locks and long-term write locks. Hyperion Essbase provides options that determine when Hyperion Essbase issues locks, as the following table shows.


Option	Description	Where Set
Wait or time-out	You decide whether (and how long) to wait for locked blocks to become available or you decide to perform the operation only if the required blocks are immediately available.	Hyperion Essbase Application Manager: Database > Settings, Transaction page MaxL: alter database statement ESSCMD: SETDBSTATEITEM 20
Pre-image access	You view data (in read-only mode) when blocks are locked by another transaction; you view data as it existed when it was last committed (before the other transaction started).	Hyperion Essbase Application Manager: Database > Settings, Transaction page MaxL: alter database statement ESSCMD: SETDBSTATEITEM 19

Note:

When you have pre-image access enabled, you are not limited to read-only access to data blocks; if you need write access to locked blocks, your transaction waits for write access or times out, depending on the wait or time-out setting. Your transaction gets immediate write access to data blocks that are not locked by another transaction.

If you do not have pre-image access enabled and if you need read or write access to locked blocks, your transaction waits for write access or times out, depending on the wait or time-out setting.

Under committed access, Hyperion Essbase retains some redundant data to ensure data consistency. To accommodate data redundancy, allow disk space for double the size of your entire database.

To enable committed access, change your access type, or change related options, select Database > Settings and click the Transaction page in Hyperion Essbase Application Manager. For more information, see Specifying Hyperion Essbase Kernel Settings.

About Uncommitted Access

Uncommitted access uses only short-term write locks, so the outcome of concurrent transactions is less predictable than with committed access. Uncommitted access is consistent with the concurrent activity under Hyperion Essbase Version 4. See Handling a Transaction for more information.

For information about storage settings used with uncommitted access (Commit Blocks and Commit Rows), see Specifying Isolation Level.

Handling a Transaction

Hyperion Essbase tracks transactions from start to finish, swapping data blocks in and out of memory as needed and committing data blocks when the transaction completes. The following list describes how Hyperion Essbase handles a transaction: all list items apply to both committed and uncommitted access.

A user or batch program begins an operation.
The OLAP engine notifies the Hyperion Essbase kernel that a transaction is to begin.
The Hyperion Essbase kernel begins the transaction.
The OLAP engine requests data from the Hyperion Essbase kernel.
The Hyperion Essbase kernel locates the requested data. It passes the data, and some associated control information, to the OLAP engine. If you are using Hyperion Essbase Spreadsheet Add-in, you see this data displayed on the sheet.
If you are using Hyperion Essbase Spreadsheet Add-in, when you modify data, you issue the Send command.
The Hyperion Essbase kernel associates the transaction with an entry in its transaction control table.
After the operation is complete on the OLAP engine side, the OLAP engine notifies the Hyperion Essbase kernel about the update, and the Hyperion Essbase kernel updates internal data structures accordingly.
Steps 4 through 8 repeat as often as necessary to complete the operation.
The transaction ends. If Hyperion Essbase encounters an error during transaction processing, it aborts the transaction. If no errors are encountered, Hyperion Essbase commits the transaction. See Committing Data for details on the differences in commit behavior under committed and uncommitted access.
Hyperion Essbase issues a message to notify the client that the transaction is complete; for example, "TOTAL CALC ELAPSED TIME..."

Under uncommitted access, it is possible to access uncommitted data as multiple transactions are active and are accessing the same data. Transaction results are unpredictable under uncommitted access.

Under uncommitted access, if you have defined a commit threshold, Hyperion Essbase may need to break down a single database operation into multiple synchronization points. See Committing Data with Uncommitted Access for information on commit thresholds.

Locking Data

Hyperion Essbase issues write (exclusive) locks for blocks that are created, updated, or deleted, and read (shared) locks for blocks that need to be accessed but not modified. By issuing the appropriate locks, Hyperion Essbase ensures that data changed by one operation cannot be corrupted by a concurrent update.

Note:

The Hyperion Essbase kernel locks data blocks, not objects. For information about locking and unlocking outlines and other objects, see Locking and Unlocking Objects.

*Table 44-1: Basic Lock Types*
Lock	Description
Write (exclusive) lock	Prevents any other transaction from accessing the locked data block. Used for all data block updates, including spreadsheet lock&send.
Read (shared) lock	Allows other transactions read-only access to the data block, but prevents other transactions from modifying the data block.

The following table shows the locks that Hyperion Essbase issues for various types of operations.

*Table 44-2: Locking by Higher-Level Functions*
Type of Operation	Lock Issued
Spreadsheet retrieve	Read (shared) lock on each data block.
Retrieve and lock	Write (exclusive) lock on all affected blocks. A subsequent send command commits the data.
Calculate derived block	Write lock on the block being calculated. As a block is calculated, all blocks containing the block's children acquire read locks.
Data load	Write lock.
Restructure	Write lock.

How Hyperion Essbase handles locking depends on whether committed or uncommitted access is enabled. For information on access type, see About Transactions.

Locking Under Uncommitted Access

Under uncommitted access, Hyperion Essbase issues short-term write locks; Hyperion Essbase locks blocks only as it is updating them (contrast with committed access, when Hyperion Essbase holds locks until a transaction completes).

Table 44-3 illustrates locking behavior under uncommitted areas when more than one transaction is contending for a lock on the same data. In the example in Table 44-3, transaction Tx1 is running, and transaction Tx2 is requesting access to the same data.

*Table 44-3: Locking Behavior with Uncommitted Access*
Request made by Tx2	If Tx1 holds read lock	If Tx1 holds write lock
Read lock	Tx2 gets read lock.	Tx2 gets read lock.
Write lock	Tx2 gets write lock.	Tx2 waits for Tx1 to release the lock.

Locking Under Committed Access

Under committed access, Hyperion Essbase issues short-term read locks and long-term write locks. A transaction locks and holds the lock on each block that it modifies until the transaction completes.

Table 44-4 illustrates locking behavior under committed access when more than one transaction is contending for a lock on the same data. In the example in Table 44-4, transaction Tx1 is running, and transaction Tx2 is requesting access to the same data.

Note that access to locked blocks depends on what options are enabled:

Pre-image access--You view data (in read-only mode) when blocks are locked by another transaction; you view data as it existed when it was last committed (before the other transaction started). Enabling pre-image access does not limit you to read access; if you need write access, or if you do not enable pre-image access, you can use the wait or time-out option. Your transaction can immediately get write access to data blocks that are not locked by another transaction.
Wait or time-out--You specify how long your transaction should wait for locked data blocks to become available. If you choose 0 seconds, Hyperion Essbase issues a time-out error if the blocks are not immediately available to your transaction and aborts your transaction.

For information on setting these options, see Specifying Hyperion Essbase Kernel Settings.

*Table 44-4: Locking Behavior Under Committed Access*
		Tx1 holds read lock; Tx2 requests read lock	Tx1 holds read lock; Tx2 requests write lock	Tx1 holds write lock; Tx2 requests read lock	Tx1 holds write lock; Tx2 requests write lock
Pre-image access enabled	Wait (time-out) period specified (indefinite wait or a number of seconds wait)	Tx2 gets read lock.	Tx2 waits for Tx1 to release read lock.	Tx2 gets pre-image access.	Tx2 waits for Tx1 to release write lock.
Pre-image access enabled	No wait (time-out) period specified (immediate time-out)	Tx2 gets read lock.	Hyperion Essbase issues time-out message and aborts the transaction.	Tx2 gets pre-image access.	Hyperion Essbase issues time-out message and aborts the Tx2 transaction.
No pre-image access	Wait (time-out) period specified (indefinite wait or a number of seconds wait)	Tx2 gets read lock.	Tx2 waits for Tx1 to release read lock.	Tx2 waits for Tx1 to release write lock.	Tx2 waits for Tx1 to release write lock.
No pre-image access	No wait (time-out) period specified (immediate time-out)	Tx2 gets read lock.	Hyperion Essbase issues time-out message and aborts the Tx2 transaction.	Hyperion Essbase issues time-out message and aborts the Tx2 transaction.	Hyperion Essbase issues time-out message and aborts the Tx2 transaction.

Concurrency Under Committed Access

Occasionally under committed access, a deadlock situation results when two transactions are locking or waiting for access to the same blocks. For example, if transaction Tx1 needs to update first data block B1 and then data block B2, it first locks B1 and then attempts to lock B2. Meanwhile, if transaction Tx2 needs to update first data block B2 and then block B1, Tx2 first locks B2 and then attempts to lock B1. Tx1 locked B1 and is waiting for B2, and Tx2 locked B2 and is waiting for B1. Neither transaction can complete under these conditions.

Hyperion Essbase transactions periodically perform deadlock detection. If a transaction is waiting to acquire a lock, it checks periodically. If the transaction detects a deadlock, Hyperion Essbase issues an error message, and the transaction aborts.

If you try to update a block that is locked to another user, Hyperion Essbase behaves as follows:

If wait is set to indefinite, your transaction waits to acquire the needed locks.
If wait is set to 0 (immediate) and if the required blocks are not immediately available, Hyperion Essbase displays an error message, and aborts the transaction.
If wait is set to a user-specified number of seconds and the time has expired, Hyperion Essbase displays an error message and aborts the transaction.
If your request times out, try the operation again.

For information on setting locking options, see Specifying Hyperion Essbase Kernel Settings.

Committing Data

Hyperion Essbase automatically commits any kind of update operation--data load, calculation, restructure, or spreadsheet operation--that modifies data blocks.

Hyperion Essbase handles commit operations differently under committed and uncommitted access.

You cannot explicitly commit data under either type of access; there is no COMMIT command.

Committing Data with Committed Access

Under committed access, as Hyperion Essbase completes an operation, it commits the transaction. There is no explicit commit operation for the user to perform; Hyperion Essbase handles the commit automatically.

Committing Data with Uncommitted Access

Under uncommitted access, the following conditions cause Hyperion Essbase to commit data during one or more synchronization points during a transaction:

An operation is completed.
The number of modified data blocks reaches a user-defined threshold. To set the threshold level, select Database > Settings in Hyperion Essbase Application Manager, click the Transaction tab, and use the Synchronization Point group. For more information, see Specifying Hyperion Essbase Kernel Settings.
The number of loaded data rows reaches a user-defined threshold. To set the threshold, select Database > Settings in Hyperion Essbase Application Manager, click the Transaction tab, and use the Synchronization Point group. For more information, see Specifying Hyperion Essbase Kernel Settings.
If a user-defined threshold is exceeded during an operation, Hyperion Essbase issues a synchronization point to commit the data processed to that point. Hyperion Essbase performs as many synchronization points as are necessary to complete the operation.

Accommodating Data Redundancy

To ensure data integrity, the Hyperion Essbase kernel temporarily retains redundant (duplicate) information. To accommodate redundant information, allow disk space for double the size of your database.

Hyperion Essbase maintains a file called dbname.ESM, in which it stores crucial control information.

CAUTION: The databasename.TCT file, databasename.ESM file, the index files, and the data files contain information crucial for data recovery. Never alter or delete these files.

Rolling Back Transactions

If a server crash or other server interruption occurs while there are active transactions running, the Hyperion Essbase kernel rolls back the transactions when the server is restarted.

Rollback with Committed Access

Under committed access, if the server crashes, Hyperion Essbase rolls back all database updates by transactions that were in progress when the server stopped. Thus, Hyperion Essbase ensures that changes made by the aborted transactions are undone.

If a transaction is aborted due to a non-fatal error, all changes made by the transaction are rolled back.

Rollback with Uncommitted Access

Under uncommitted access, if the server crashes, Hyperion Essbase rolls back all database updates from the point of the last successful commit. Some of the updates from an aborted transaction may have committed. Whether transactions committed their updates the way users expected depends on the order in which overlapping transactions updated and committed data.

If a transaction is aborted due to a non-fatal error, Hyperion Essbase commits only the data that the transaction finished processing prior to the abort of the transaction.

Actions to Take When a Transaction Does Not Complete

Table 44-5 shows what action to take if a transaction does not complete, because the transaction aborted or the server was interrupted.

*Table 44-5: Actions to Take if a Transaction Stops Before Completing*
Operation	Action to Take
Calculation	Start over.
Calc script	Check the server log and see how far the calc script got and decide whether to start over or continue.
Normal data load	Start over.
Arithmetic data load (loads that perform operations on values in the database, such as adding 10 to each value)	Start where the data load left off.
All other operations	Start over.

Checking Structural Integrity

The VALIDATE command performs several types of structural integrity checks:

Verifies the structural integrity of free space information in the index.
Compares the data block key in the index page with the data block key in the corresponding data block.
Restructures data blocks whose restructure was deferred with incremental restructuring. For more information, see Incremental Restructuring.
Checks every block in the database to make sure each value is a valid floating point number.
Verifies the structural integrity of the linked reporting objects (LROs) free space catalog.

As Hyperion Essbase encounters mismatches, it records error messages in the VALIDATE error log file. You can specify a file name for error logging; Hyperion Essbase prompts you for this information if you do not provide it. The VALIDATE utility continues running until it has checked the entire database.

You can use the VALIDATE command in ESSCMD to perform these structural integrity checks. See the online Technical Reference in the DOCS directory for information about the command. See Performing Interactive and Batch Operations Using ESSCMD for information about ESSCMD.

During index free space validation, the VALIDATE command verifies the structural integrity of free space information in the index. If integrity errors exist, Hyperion Essbase records them in the VALIDATE log file. The file that you specified on the VALIDATE command holds the error log.

If VALIDATE detects integrity errors regarding the index free space information, the database must be rebuilt. You can rebuild in any one of three ways:

Restoring the database from a recent system backup
Exporting data from the database; creating a new, empty database; and loading the exported data into the new database
Restructuring the database

VALIDATE also validates the consistency of data block keys. The Hyperion Essbase index contains a key for every data block. Every corresponding data block contains the same key. The VALIDATE utility uses this key as a basis for comparison. You may want to run VALIDATE periodically to ensure data consistency.

Even if you do not use VALIDATE, Hyperion Essbase automatically performs certain validity checking whenever a read operation is performed, to ensure that the index is properly synchronized with the data.

For every read operation, Hyperion Essbase compares the data block key in the index page with the data block key in the corresponding data block and checks other header information in the block.

If Hyperion Essbase encounters a mismatch, it displays an "Invalid block header" error message.