# Administrator Guide

# Managing the Analytics Database

This chapter includes the following topics:

- Sizing the Analytics Database
- Archiving and Restoring Partitions

## Sizing the Analytics Database

This section provides information and recommendations that should help you manage the size and growth of your Analytics database to, which should increase performance. It includes the following topics:

- Overview of Analytics Database Growth
- Tuning the Analytics Database - Oracle

### Overview of Analytics Database Growth

The majority of growth in the Analytics database occurs in the fact tables that are delivered with Analytics. The rest of the delivered Analytics tables -- including dimension tables -- generate negligible growth in the Analytics database. This section provides details on the growth of fact and dimension tables.

#### Fact Table Growth

Fact tables capture event parameter data of types Date, Integer, and Float.

Both Oracle and SQL Server databases grow at approximately the same rate: every one million events that are stored in the database use approximately 150 megabytes of disk space. The following table lists numbers of events and their corresponding estimated database sizes.

Table 5-1 Numbers of Events and Corresponding Estimated Database Sizes
Number of Events | Estimated Database Size
--- | ---
1,000,000 | 150 megabytes
2,000,000 | 300 megabytes
5,000,000 | 750 megabytes
10,000,000 | 1.5 gigabytes

Use these size estimates to calculate your own database growth requirements. As the number of events in your system continues to grow, query performance ultimately starts to decline. For this reason, in high-volume environments you should monitor the growth of your database and take appropriate measures to prevent performance degradation.

#### Dimension Table Growth

Analytics uses dimensions to capture event parameter data of type String. Dimension tables do not grow as quickly as delivered fact tables because dimension data does not change at nearly the same rate as events occur.

Note that if an object is removed from the application on which Analytics is reporting, the record for that object's dimension data remains in the Analytics database. For this reason, Analytics continues to report the events that occurred on this object before it was removed from the application.

### Tuning the Analytics Database - Oracle

To obtain the best performance with Oracle, we recommend that you:

- Edit the create_analytics_tablespaces.sql script and include appropriate sizing information for your database. The create_analytics_tablespaces.sql script is located in a platform-specific subdirectory within the Analytics database's host computer.

Note: | Note: The default values in the create_analytics_tablespaces.sql script are acceptable for a development or staging database. You should, however, change these values accordingly in a production environment.

- Move the ANALYTICSTABLE tablespace to a different drive than the one used for the ANALYTICSINDEX tablespace.
- Configure the Oracle settings described in this table:

  Table 5-2 Oracle Settings and Configuration Recommendations
  Oracle Setting | Configuration
  --- | ---
  Buffer Cache | Increase to 250 megabytes
  PGA Aggregate Target | Increase to 150 megabytes
  Data Block Size | Increase to 16K

Note: | Note: The recommendations in this table are for use with the Analytics database only. You might want to change these configurations slightly to more appropriately suit your environment.

## Archiving and Restoring Partitions

This section provides guidelines for archiving and restoring the partitions within your Analytics database. For details on using Analytics Administration's Partition Settings page, see Configuring Partition Settings.

To maintain a steady size of your Analytics database and keep your queries performing quickly, we recommend archiving partitioned data that is greater than six months old. You can identify partitioned tables by their date/year suffix. For example: _08_2006.

After archiving or restoring partitions, you must refresh the database views by clicking Finish on the Partition Settings page. If you do not refresh the database views, Analytics reports will fail.

Caution: | Never remove the current fact table, which is not partitioned. Also, never remove database views. Instead, use the scrolling view window to set the number of partitions that are accessible to Analytics reports.