7.5 Configuring the Service Warehouse

The Service Warehouse stores alarm history and comments as well as historical performance and service level data. For supported databases and specific product requirements, see the Operations Center 5.6 Getting Started Guide.

The Service Warehouse gathers the service level metrics required for determining whether service compliance is being met and service level health is acceptable, both of which are critical to managing service level agreements (SLAs). Management of SLAs requires the use of another Operations Center product called the Service Level Manager (SLM), which is licensed separately. For more information about SLAs and SLM, see the Operations Center 5.6 Service Level Agreement Guide.

Even if you are not using SLAs and SLM, you need to create and define a Service Warehouse in order to use alarm history data. For more information on alarms, see Section 11.0, Customizing Monitored Elements and Alarms.

The following sections cover various topics related to creating and maintaining a Service Warehouse:

7.5.1 Sizing the Service Warehouse

The Service Warehouse stores two basic types of data:

  • Alarm history

  • Service level agreement (SLA) performance and service level metric data

This data is described briefly here but for more detailed information, see the Service Warehouse Data Dictionary.

To assist in creating a Service Warehouse, Operations Center provides sample scripts. Prior to using these scripts, you need to determine the approximate size of the database to create. To calculate the approximate space needed by the database to accommodate the requirements of the Service Warehouse, you must closely examine your configuration and use simple arithmetic.

When estimating SLA performance and service level metric data storage requirements, it is necessary to consider:

  • The number and type of expressions and data retention settings

  • All historical alarm types and data retention settings

  • The frequency with which elements are shared across multiple services

[The following sections cover these various considerations when planning for your Service Warehouse:]

Historical Alarms

When estimating the number of alarms that will be stored, consider all types of alarms. Table 7-1 provides an overview of the five types of historical alarms data stored in the Service Warehouse.

Table 7-1 Historical Alarm Types

Alarm Type

Amount of Storage

Audit Alarms

Depends on specific audit settings.

Service Alarms

Varies by the number and type of adapters, and the associated number of elements generated in the element hierarchy.

Service Level Breach and Warning Alarms

Depends on the number of SLAs and objectives set per element * 2 (1 for warning and 1 for breach), the interval over which objectives are measured (hourly vs. monthly, aligned vs. rolling), and the anticipated failure frequency. It’s possible that a breach alarm is issued every hour for an extended period of time when an extended outage occurs.

Outages

Depends on the number of manual outages create or imported from external data sources.

Comments

Might not impact storage requirements. Every comment on an alarm is a new comment. Note that Comments are removed when their associated alarm history is removed.

In addition, the data retention period must be factored into the storage estimates. Set data retention independently for audit, service and service level breach, and warning alarms. Alarm history is stored in the BSAAlarmData table.

Another consideration is the frequency with which elements are shared across multiple services. Table size could increase significantly depending on the frequency of shared elements within the service hierarchy. The relationship between elements and alarms is stored in BSAAlarmElements table.

BSLM Performance and SLA Metric Data

All service level metric data is managed by the Service Levels profile, which uses a data retention default of 90 days. You can customize the data retention settings to suit specific requirements. For more information on updating profile data retention settings, see Setting Service Levels Profile Properties in the Operations Center 5.6 Service Level Agreement Guide.

For service level metric data, condition changes reside in the BSAFactSeriesData table. Estimate service level metric storage requirements using a single BSLM expression in the calculation in Basic Calculation.

Capture performance metrics using one or more profiles, each with its own data retention setting. Set this frequency for data capture for a profile by associating a schedule. If data is stored every 5 minutes, then the calculation should include a multiplier based on this value.

Basic Calculation

These calculations are approximate. All numbers should include a small error factor to account for different types of alarm data and historical performance expressions.

The basic formula for calculating space requirements is:

(((x * 2,280) + (y * 100) * 31) * 2) = ~MB of data storage per month

Where:

  • x = target number of alarms per day

  • y = target number of BSLM expressions per day

  • 31 = number of days in a month

  • 2 = number of tablespaces (one for data and the other for indexes)

For the sake of simplicity, the number 1,000 in decimals is considered to be equal to 1K.

Some sample scenarios:

Example #1, Calculation Scenario

For example, assume the target is to collect each day:

  • 10,000 alarms

  • An additional 100,000 performance and/or SLA metric records for other BSLM profiles and expressions

For alarm data, the breakdown calculation is outlined in Table 7-2.

Table 7-2 Alarm Data Approximations

Approximate Bytes

Per

~ 1600

Alarm record

~7 * 40

Alarm element mapping

~0.5 * 400

Element DName storage

~200

Miscellaneous series identifiers and timeband records

~2280

Total bytes per alarm stored

Therefore, the space required for alarm data is approximately:

10,000 * 2,280 = 23 MB per day

For the additional expressions for performance and SLA data, multiply each record by 100 bytes. Therefore the example calculation is:

100,000 * 100 average bytes/expression = 10 MB per day

Using the above calculations, the combined total is 33MB per day.

To accommodate one month of data at the rate defined above, the database tablespaces can be approximately 900MB – 1GB for each tablespace.

Since there are two tablespaces for BSLM (one for data, the other for indexes), then allocate approximately 2 GB per month when creating the database.

Example #2, Database Report Illustration

As an additional illustration, the following database report was generated from an Oracle database with Historical Performance data collected for T/EC and PATROL alarms after one week of activity. In this case, there were three profiles, each with data retention set for seven days:

PATROL element with:

  • 4 performance expressions

  • 1 alarm expression

  • 1 minute interval

Enterprise root:

  • matching on '.*'

  • expression on child condition count

  • expression on element condition

  • 10 minute interval

T/EC element with:

  • 4 performance expressions

  • 1 minute interval

Database Table Name

Number of Rows

Total Size (KB)

Total Size (MB)

Bytes per Row

BSAADAPTERS

1

8

.01

8192

BSAALARMDATA

41,801

53,376

52.13

1308

BSAALARMELEMENTS

220,341

7,312

7.14

34

BSADATATYPES

12

8

.01

683

BSAELEMENTS

149

24

.02

165

BSAFACTSERIESDATA *

1,575,557

142,376

139.04

93

BSASERIES

545

96

.09

180

BSATIMEBAND

39,385

2,816

2.75

73

BSAVERSIONWAREHOUSE

1

8

.01

8192

If storing Root Cause data, add an additional 1K per Root Cause entry to the BSAFACTSERIESDATA table.

Data Purging

An important factor in calculating database space requirements is the Service Warehouse data purging which is performed at scheduled intervals using the Operations Center Jobs function. Operations Center ships with a default job, the BSW Historical Data Purge job, which is designed to purge the Service Warehouse every morning at 3 AM (this can be modified).

This job purges all alarm history, historical performance data, and SLA data based on the Retain this data for x days data retention setting defined for each profile that is capturing data. As of version 4.7 purge dates are recorded at the time the data is written to the Service Warehouse, so changes to retention settings are applied going forward.

7.5.2 Configuring the Service Warehouse

To define and configure the Service Warehouse, you’ll need to create a database definition specifically for the Service Warehouse. Once this definition is created, any change to the Database Type requires a restart to the Operations Center server.

To configure and enable the Service Warehouse:

  1. In the Operations Center console Explorer pane, expand Administration > Database Definitions.

  2. Right-click Database Definitions, then select Create Database Definitions to open the Create Database Definition dialog.

  3. Enter a name for the definition in the Name field.

    It is best practice to use a name without spaces or special characters.

  4. In the Schema(s) section, make sure that Service Warehouse is selected.

  5. Click the Type drop-down list, then select a database type.

    If selecting (SQL Server (Domain Authentication), there are required configurations for Windows servers. For more information, see Section 7.1, Configuring Windows Servers for Single Sign On (SSO).

    NOTE:Once the Service Warehouse definition is created, any change to the Database Type requires a restart to the Operations Center server.

  6. Select the Enable check box to activate the database definition.

  7. Specify the Database Properties required to establish the database connection (the required properties vary depending on the database):

    Database Properties

    Description

    Hostname

    The name of the database server.

    Listener Port

    The port on which the database listens for communications.

    Server ID (SID)

    The name of the database (used when defining an Oracle database connection).

    Database

    The name of the database (used when defining a Microsoft SQL Server, Sybase, or DB2 database connection).

    Domain

    The domain to use for domain authentication with single sign on (used when defining a Microsoft SQL Server database with Domain Authentication).

    User Name

    The name of the user account. When multiple Operations Center schemas exist within the same database, a unique ID must be created for each database schema.

    If using Microsoft SQL Server with Domain Authentication, specify the username of the Windows user account, or leave blank (on Windows servers) to attempt to use the credentials of the currently active Windows user account.

    Password

    The password for the user account.

    If using Microsoft SQL Server with Domain Authentication, specify the password of the Windows user account, or leave blank (on Windows servers) to attempt to use the credentials of the currently active Windows user account.

    Initial Connections

    The number of connections established upon initial connection.

    Maximum Connections

    The maximum number of connections allowed.

    Additional Properties

    (Optional) Additional JDBC URL properties for Microsoft SQL Server and Microsoft SQL Server (Domain Authentication) databases. Prefix each parameter entry with a semi-colon. For example,

    ;parameter1=value1;parameter2=value2

    Database Class, URL, and Driver File

    Name and location of the IBM DB2 driver. If using an IBM DB2 database, it is necessary to upload the JDBC driver file supplied by IBM.

    When setting the number of database connections allowed, calculate the number of required database connections by analyzing your user base and other Operations Center settings. Identify the following:

    • The number of database connections required in part depends on how many users access reports.

    • The number of database connections used for storing alarm history has a one-to-one correspondence to the number of alarm threads configured for the Service Warehouse.

    • The number of database connections used for storing Performance Series data has a one-to-one correspondence to the number of performance threads configured for the Service Warehouse.

    Then, use the following calculation to estimate the number connections needed:

    (# users * 0.5) + (# of Alarm Threads * 1) + (# Series Threads * 1) +10

    The Service Warehouse uses a connection pool, therefore each connection is returned to the connection pool when the query is complete. If the number of used connections is always at the maximum, then increase the number of connections by 20 percent.

  8. Click the Install Driver button to browse, then select a driver file.

  9. Click the Test button to test and verify that the database settings are valid, prior to creating the definition.

    If the database connection is valid, the Create button activates.

IMPORTANT:If you are using Operations Center in a clustered environment, one Operations Center server must be designated as the primary server writing to the Service Warehouse. This is indicated in Configuration Manager on the Database pane on the Components tab. On one configuration, change the Primary Warehouse Writer option to True. By default all others are False.

For information on clustered environments, see Implementing a High Availability Solution in the Operations Center 5.6 Server Installation Guide.

7.5.3 Customizing Data Collection Settings for Alarms and Performance Metrics

The Service Warehouse is preconfigured with default settings that allow Operations Center software to capture and record alarm and performance data. These settings can be adjusted depending on the various needs of your system.

System performance can vary based on the combined settings specified for Performance Threads, Transactions Per Commit, and Purge Block Size. A higher transaction limit provides better performance. However, it can increase database recovery time, and the potential of locking out other threads trying to access the same tables. If set too high, the database can also run out of database cursors.

Table 7-3 shows the setting values that are likely to produce the best performance levels for most systems.

Table 7-3 Recommended Settings for Most Systems

Setting

Sybase

Oracle

MSSQL

Performance Threads

4

4

4

Transactions Per Commit

50

50

50

Purge Block Size

1,000

2,000

1,000

The Service Warehouse collects alarm and performance data using an element called Data Warehouse which appears in the hierarchy in the Operations Center console under Enterprise > Administration. For information about collecting data for service level agreements, see the Operations Center 5.6 Service Level Agreement Guide.

To edit or configure Data Collection settings:

  1. In the Explorer pane, expand the root Administration element.

  2. Right-click Data Warehouse, and select Properties.

  3. Select Data Warehouse in the left pane.

  4. (Data Warehouse tab) Configure the following options for the capturing, recording, and maintenance of alarm and performance data:

    • Performance Threads: The number of threads to handle historical performance data.

      The queue for Performance data can have a maximum of eight threads with each thread using one or two database connections.

    • Transactions Per Commit: The number of objects per commitment.

      A higher transaction limit provides better performance. However, it can increase database recovery time, and there is the potential of locking out other threads trying to access the same tables. If set too high, the database can also run out of database cursors. It is suggested to use a setting of 50.

    • Maximum Allowable Errors: The maximum allowable errors before the Service Warehouse shuts down.

      NOTE:When the threshold is exceeded (i.e. when the database runs out of disk space or the database is unavailable for any reason), the Service Warehouse automatically initiates the Backup Repository, if enabled. If the Backup Repository is not enabled, data processing is shutdown entirely.

      When the database becomes available again, it is necessary to disable and then reenable the Database Definition (created in Section 7.5.2, Configuring the Service Warehouse) which alerts the Service Warehouse to switch from backup mode and return to using the database.

    • Purge Block Size: The number of objects to clear from the database with each purge transaction.

    • Select the Start Data Warehouse Automatically check box to start the Data Warehouse when the Operations Center server is started.

  5. (Adapter Query Cache tab) Configure cache settings for responses from integration systems. The data returned from adapter queries can be cached to help speed response times.

    • Adapter Query Cache Size: The number of adapter responses to cache.

    • Adapter Query Cache TTL: The retention time (in seconds) to hold query responses in the cache.

7.5.4 Enabling the Service Warehouse Backup Repository

The Service Warehouse has a backup repository system which uses a file system that stores excess data until queues return to normal and data in the backup files is processed.

When enabled, Operations Center uses the in-memory queue and writes data to a backup repository in the event that:

  • The Service Warehouse database connection fails.

  • The database temporarily shuts down.

  • The in‑memory queue for writing historical data to the database exceeds the Queue Maximum setting, when Warehouse Write Mode is set to MEMORY-QUEUED in the Configuration Manager. For more information on the Queue Maximum database setting, see Step 5.

    For more information on Configuration Manger settings, see Section 2.2.11, Database Pane.

  • The Maximum Allowable Errors setting is reached and exceeded (for example, when the database runs out of disk space or the database is unavailable for any reason) and the Service Warehouse shuts down. For more information on the Maximum Allowable Errors database setting, see Step 4.

    In this case, it is necessary to re-enable the database definition after the problem is solved and the database is available. For more information see Section 7.3.5, Disabling and Deleting a Definition.

To enable or configure settings for the backup repository:

  1. In the Explorer pane, right-click the Data Warehouse root element, then select Properties to open the Status property page.

  2. In the left pane, click Data Warehouse to open the Data Warehouse property page, then select the Backup Repository tab:

  3. Select the Enable Backup Repository check box to activate the back repository process.

    If implementing Service Level Management (SLM), enabling the backup repository is required. For more information about SLM, see the Operations Center 5.6 Service Level Agreement Guide.

  4. (Optional) To specify the location of the backup repository, enter the directory for the backup repository files in the Storage Location field, or click Browse to navigate, then select a directory.

    The backup repository is configured and enabled by default to store data in the /OperationsCenter_install_path/database/eventQueue directory.

  5. (Optional) Specify the following settings when Warehouse Write Mode is set to MEMORY-QUEUED in the Configuration Manager:

    Maximum Disk Space: The maximum amount of disk space used for backup repository files.

    Maximum File Size: The maximum size of a generated backup file before an additional backup file is created.

    Queue Maximum: The maximum size for the database queue, above which additional collected data is written to backup repository files.

    Queue Minimum: The minimum size for the database queue, below which any data stored in backup repository files is reissued to the queue.

  6. (Optional) To process existing backup repository files when the Service Warehouse starts, select the Process Backup Files Automatically check box.

  7. (Optional) To delete all existing backup repository files, click the Delete Backup Files button.

  8. Click Apply to save the changes.

7.5.5 Enabling and Disabling the Service Warehouse

If the database definition for the Service Warehouse is disabled, data is collected, but it is stored in the backup repository only. During this time, reports and views cannot show the most recent data until the Service Warehouse database is back online and data in the backup repository is written to the database.

WARNING:If the maximum number of allowable errors is reached (i.e. when the database runs out of disk space or the database is unavailable for any reason), the Service Warehouse shuts down. After troubleshooting/resolving the issue and the database is available, you must disable and then reenable the Database Definition. By reenabling the database definition, the Service Warehouse is alerted that the database is available again.

For more information on the Maximum Allowable Errors database setting, see Step 4.

To manually enable or disable the Service Warehouse, right-click the Service Warehouse element, then select either Enable Database Definition or Disable Database Definition.

7.5.6 Auditing Service Warehouse Events

Updates to the Service Warehouse can be audited. The following events can be audited:

  • Service Warehouse settings updated

  • Start or stop the data warehouse engine

  • Clear the Service Warehouse backup queue

Audit events display in the Audit Event channel in the Alarms view on the Data Warehouse element. For information about audited events, see the Operations Center 5.6 Security Management Guide.

Auditing is set in the Explorer pane, under Administration. Right-click Data Warehouse, then select Properties. On the Audit tab, select the events to audit. Audit events display in the Alarms view. For more information on alarms, see Section 11.0, Customizing Monitored Elements and Alarms.

To set auditing:

  1. In the Explorer pane, expand Administration.

  2. Right-click Data Warehouse, then select Properties to open the Status property page.

  3. In the left pane, click Audit to update the Audit property page:

  4. Select the events to audit.

  5. Click the Apply button.

7.5.7 Viewing Service Warehouse Status & Statistics

Cache statistics are available for the data warehouse for the Service Warehouse.

To view the status and statistics for the Data Warehouse:

  1. In the Explorer pane, right-click the Date Warehouse root element, then select Properties.

    The Status property page opens and displays information about Repository Status, Queue Size, and Repository Details.

    HINT:To disable text wrapping for easier reading, right-click the Repository Details pane, then select Word Wrap.

  2. In the left pane, click Data Warehouse to open the Data Warehouse property page.

  3. To view warehouse settings and service level settings click the Data Warehouse, Backup Repository, System Settings, or Service Level Settings tabs.

  4. To view caching statistics for primary keys, alarm metadata, profiles, and time series information, click the Cache Statistics tab.

    HINT:To disable text wrapping, right-click the Cache Statistics pane, then select Word Wrap.