Managing Database Settings Skip Navigation
Essbase® Analytic Services Database Administrator's Guide | Update Contents | Previous | Next | Print | ? |
Information Map

Managing Database Settings


This chapter introduces you to the Analytic Server Kernel, explains how Analytic Services accesses and stores data, and provides an overview of Analytic Server database settings you can use to optimize performance at the Analytic Server level.

This chapter contains the following sections:

Note: For information about fatal errors in the Analytic Server Kernel, see Understanding Fatal Error Handling.

Understanding the Analytic Server Kernel

The kernel provides the foundation for a variety of functions of Analytic Server. These functions include data loading, calculations, spreadsheet lock&send, partitioning, and restructuring. The kernel reads, caches, and writes data; manages transactions; and enforces transaction semantics to ensure data consistency and data integrity.

The kernel has the following functions:

The rest of this section explains the two available access modes, and describes how to set the access modes:

Understanding Buffered I/O and Direct I/O

The Analytic Services Kernel uses buffered I/O (input/output) by default, but direct I/O is available on most of the operating systems and file systems that Analytic Services supports. For a list of the supported platforms, see the Essbase Analytic Services Installation Guide.

Buffered I/O uses the file system buffer cache.

Direct I/O bypasses the file system buffer cache, and is able to perform asynchronous, overlapped I/Os. The following benefits are provided:

If you set a database to use direct I/O, Analytic Services attempts to use direct I/O the next time the database is started. If direct I/O is not available on your platform at the time the database is started, Analytic Services uses buffered I/O, which is the default. However, Analytic Services will store the I/O access mode selection in the security file, and will attempt to use that I/O access mode each time the database is started.

Note: Cache memory locking can only be used if direct I/O is used. You also must use direct I/O if you want to use an operating system's no-wait (asynchronous) I/O.

Viewing the I/O Access Mode

Buffered I/O is the default for all databases.

To view which I/O access mode a database is currently using or is currently set to, use any of the following methods:


Tool
Topic
Location

Administration Services

Selecting an I/O Access Mode

Essbase Administration Services Online Help

MaxL

display database

Technical Reference

ESSCMD

GETDBINFO

Technical Reference



Setting the I/O Access Mode for Existing Databases

To use direct I/O instead of the default buffered I/O for any database, use any of the following methods:


Tool
Topic
Location

Administration Services

Selecting an I/O Access Mode

Essbase Administration Services Online Help

MaxL

alter database

Technical Reference

ESSCMD

SETDBSTATEITEM

Technical Reference



You may also need to increase the size of some caches. See Sizing Caches for instructions and recommendations.

Setting the I/O Access Mode for New and Migrated Databases

To use direct I/O instead of the default buffered I/O for all databases migrated from an earlier release and for newly created databases, follow these steps:

  1. Add the configuration setting DIRECTIO to the Analytic Server essbase.cfg file, and set the value to TRUE. See the Technical Reference for instructions.

  2. Consult the Essbase Analytic Services Installation Guide, in PDF format, in the /ARBORPATH/docs/pdf directory for information and instructions to help you make the transition. ARBORPATH is the Analytic Services install directory; by default this is /hyperion/essbase.

  3. Consult the cache sizing information in Sizing Caches.

Understanding Kernel Components

The kernel contains components that control all aspects of retrieving and storing data:

Index Manager

The Index Manager manages the database index and provides a fast way of looking up Analytic Services data blocks. The Index Manager determines which portions of the database index to cache in the index cache, and manages the index cache.

The Index Manager controls five components. The following table describes these components:


Component
Description

Index

The method that Analytic Services uses to locate and retrieve data. The term index also refers to the index file.

Index file

File that Analytic Services uses to store data retrieval information. It resides on disk and contains index pages. Analytic Services names index files incrementally on each disk volume, using the naming convention essxxxxx.ind, where xxxxx is a number. The first index file on each disk volume is named ess00001.ind.

Index page

A subdivision of an index file that contains index entries that point to data blocks.

Index entry

A pointer to a data block. An index entry exists for every intersection of sparse dimensions.

Index cache

A buffer in memory that holds index pages.



The term index refers to all index files for a single database. The index can span multiple volumes, and more than one index file can reside on a single volume. Use the disk volumes setting to specify disk spanning parameters. For information on setting the index cache size, see Sizing the Index Cache. For information about allocating storage space with the disk volumes setting, see Specifying Disk Volumes.

Allocation Manager

Allocation Manager, part of the Index Manager, performs these tasks:

When one of these tasks needs to be performed, the Allocation Manager uses this process to allocate space:

  1. It attempts to use free space in an existing file.

  2. If enough free space is not available, it attempts to expand an existing file.

  3. If enough free space is not available in existing files, it creates a new file on the current volume.

  4. If it cannot expand an existing file or create a new file on the specified volume, it attempts to use the next specified volume.

  5. If all specified volumes are full, an error message is displayed, and the transaction is aborted.

The Allocation Manager allocates space for index and data files based on the database settings for storage.

To check current values and set new values, use any of the following methods:


Tool
Topic
Location

Administration Services

Setting Database Properties

Essbase Administration Services Online Help

MaxL

alter database

Technical Reference

ESSCMD

SETDBSTATEITEM 23

Technical Reference



See Specifying Disk Volumes for a detailed discussion of how the disk volumes setting works.

For a comprehensive discussion of how Analytic Services stores data, see Storage Allocation.

Data Block Manager

The Data Block Manager brings data blocks into memory, writes them out to data files, handles data compression, and writes data files to disk. The Data Block Manager controls four components. The following table describes each component:


Component
Description

Data file

A file that contains data blocks. Analytic Services generates the data file upon data load and stores it on disk. Analytic Services names data files incrementally-essxxxxx.pag, where xxxxx is a number starting with 00001.

Data block

The primary storage unit within Analytic Services. A data block is a multidimensional array that represents cells of the dense dimensions for a given intersection of sparse dimensions.

Data cache

A buffer in memory that holds uncompressed data blocks.

Data file cache

A buffer in memory that holds compressed data files (.PAG).



The size of the data file cache determines how much of the data within the data files can fit into memory at one time. The data cache size and the data block size determine how many data blocks can fit into memory at one time. Data files for a single database can span multiple volumes; more than one database can reside on the same volume. For information on setting the data file cache size and data cache size, see Sizing the Data File Cache and Sizing the Data Cache. For information about allocating storage space with the disk volumes setting, see Specifying Disk Volumes.

LRO Manager

Linked reporting objects (LROs) enable you to associate objects, such as flat files, with data cells. Using the Spreadsheet Add-in, users can create and store LRO files, with an .lro extension.

LRO files are stored in the database directory (\ARBORPATH\appname\dbname, for example, \Essbase\Sample\Basic).

Analytic Services stores information about linked reporting objects in an LRO catalog. Each catalog resides in its own Analytic Services index page and coexists in an index file with other, non-LRO Analytic Services index pages.

For a comprehensive discussion of linked reporting objects, see Linking Objects to Analytic Services Data and the Essbase Spreadsheet Add-in User's Guide.

Lock Manager

The Lock Manager issues locks on data blocks, which in turn controls concurrent access to data.

The committed access and uncommitted access isolation levels use different locking schemes. For more information on isolation levels and locking, see Ensuring Data Integrity.

Transaction Manager

The Transaction Manager controls transactions and commit operations and manages database recovery.

Analytic Services commits data automatically. Commits are triggered by transactions that modify data-data loading, calculating, restructuring, and spreadsheet lock&send operations.

How Analytic Services commits data depends upon whether the transaction isolation level is set to committed or uncommitted access (the default). For detailed explanations of the two isolation levels, see Committed Access and Uncommitted Access.

The Transaction Manager maintains a transaction control table, database_name.tct, to track transactions.

For information about commit operations and recovery, see Recovering from a Crashed Database.

Understanding the Kernel Startup

This list is the sequence of events during an kernel start-up:

  1. After the Analytic Server starts, a user connects to it from a client.

  2. The user starts a database.

  3. Analytic Services loads the database.

  4. The Analytic Services Agent passes database settings to the server.

  5. The kernel begins its initialization process.

  6. The kernel starts its components-the Index Manager, Lock Manager, LRO Manager, Data Block Manager, and Transaction Manager.

If it encounters an error during start up, the Analytic Services Kernel shuts itself down.

Understanding the Precedence of Database Settings

Analytic Services provides default values for some database storage settings in the essbase.cfg file. You can leave the default settings, or change their values in two places:

Changes made for an individual database permanently override essbase.cfg settings and Analytic Services defaults for the relevant database until they are changed or withdrawn.

If you change settings at the database level, the changes become effective at different times, as shown in Table 56:


Table 56: Database-level storage settings effectiveness

Setting
When setting becomes effective

  • Index cache

  • Data file cache

  • Data cache

  • Cache memory locking

  • Disk volume

After you stop and restart a database.

Isolation level parameters Concurrency parameters

The first time after setting these values that there are no active transactions.

All other settings

Immediately.



Note: The size of index pages is fixed at 8 K to reduce input-output overhead, as well as to simplify database migration.

Understanding How Essbase Reads Settings

Analytic Services reads the essbase.cfg file when you start Analytic Server, and then applies settings to the appropriate databases that you have created using any of the methods described in Specifying and Changing Database Settings.

Database settings that you specify using Administration Services or ESSCMD/MaxL always override essbase.cfg file settings, even if you change a setting in the essbase.cfg file after you have applied a setting for a particular database. Only removing a setting triggers Analytic Services to use the essbase.cfg file, and then only after restarting Analytic Server.

Viewing Most Recently Entered Settings

To view the most recently entered settings, use any of the following methods:


Tool
Topic
Location

Administration Services

Setting Database Properties

Essbase Administration Services Online Help

MaxL

display database

Technical Reference

ESSCMD

GETDBSTATE

GETDBINFO

Technical Reference



For information on stopping and starting servers, applications, and databases, see Starting and Stopping.

Customizing Database Settings

You can customize different settings for each database on Analytic Server. The information in this section helps you understand what each setting controls, how to specify settings, and lists examples. For a table of performance-related settings, see Improving Analytic Services Performance.

Note: Configure settings that are applied to an entire Analytic Server with the essbase.cfg file. For information about how to create this file and about what settings are available, see the Technical Reference.

You can customize these major database settings:


Table 57: Major Kernel Settings

Setting
Where to Find More Information

Index cache size

Sizing the Index Cache

Data file cache size

Sizing the Data File Cache

Data cache size

Sizing the Data Cache

Cache memory locking

Deciding Whether to Use Cache Memory Locking

Disk volumes

Storage Allocation

Data compression

Data Compression

Isolation level

Understanding Isolation Levels



The following sections describe how to change kernel settings and list examples:

Specifying and Changing Database Settings

Before you change any database settings, be sure to review information about precedence of the settings as changed in different parts of Analytic Services, and how those settings are read by Analytic Services:

To specify most database settings, use any of the following methods:


Tool
Topic
Location

Administration Services

Setting Database Properties

Essbase Administration Services Online Help

MaxL

alter database

Technical Reference

ESSCMD

SETDBSTATEITEM

SETDBSTATE

Technical Reference



These different methods provide different ways to change the same database settings. In rare cases, you may want to use the essbase.cfg file to specify settings.

Caution: In previous versions of Analytic Services, you can specify many database settings in the essbase.cfg file on Analytic Server. In Version 5.x and higher, Analytic Services overrides most of the .cfg settings. For an explanation of how newer versions of Analytic Services handle settings, see Understanding the Precedence of Database Settings and Understanding How Essbase Reads Settings.

Using alter database in MaxL

Issue a separate alter database statement for each database setting you want to change. For example, the following MaxL script logs on to Analytic Services, changes three database settings, and logs off:

login admin identified by secretword;
alter database sample.basic enable committed_mode;
alter database sample.basic set lock_timeout immediate;
alter database sample.basic disable create_blocks;
logout; 
 

Note: Terminate each MaxL statement with a semicolon when issuing them using the MaxL Command Shell; however, if MaxL statements are embedded in Perl scripts, do not use the semicolon statement terminator.

You can use MaxL to write batch scripts that automate database setting changes. For detailed explanations of MaxL statements, see the MaxL Language Reference, located in the Technical Reference.

Using SETDBSTATEITEM in ESSCMD

For simple items, specify the command, item number representing the parameter, application, database, and value for the parameter:

SETDBSTATEITEM 2 "SAMPLE" "BASIC" "Y"; 
 

For parameters that require multiple values, such as Isolation Level (item 18), specify multiple values, in this case, all the values after "BASIC":

SETDBSTATEITEM 18 "SAMPLE" "BASIC" "1" "Y" "-1"; 
 

If you do not know the parameter number, omit it, and Analytic Services lists all parameters and their corresponding numbers. Analytic Services also prompts you for a database and an application name.

Use a separate SETDBSTATEITEM command for each parameter; you cannot string parameter numbers together on the same line.

See the Technical Reference for information about the parameters for the SETDBSTATE and SETDBSTATEITEM commands.

Note: SETDBSTATEITEM or SETDBSTATE affect only the specified database.

You can include SETDBSTATEITEM (or SETDBSTATE) in batch scripts. For a comprehensive discussion of batch processing, see Using Script and Batch Files for Batch Processing. For information on specific ESSCMD syntax, see the Technical Reference.



Hyperion Solutions Corporation link