Data Warehousing - System Managers


System management is mandatory for the successful implementation of a data warehouse. The most important system managers are:

  • System configuration manager
  • System scheduling manager
  • System event manager
  • System database manager
  • System backup recovery manager

System Configuration Manager

  • The system configuration manager is responsible for the management of the setup and configuration of data warehouse.

  • The structure of configuration manager varies from one operating system to another.

  • In Unix structure of configuration, the manager varies from vendor to vendor.

  • Configuration managers have single user interface.

  • The interface of configuration manager allows us to control all aspects of the system.

Note: The most important configuration tool is the I/O manager.

System Scheduling Manager

System Scheduling Manager is responsible for the successful implementation of the data warehouse. Its purpose is to schedule ad hoc queries. Every operating system has its own scheduler with some form of batch control mechanism. The list of features a system scheduling manager must have is as follows:

  • Work across cluster or MPP boundaries
  • Deal with international time differences
  • Handle job failure
  • Handle multiple queries
  • Support job priorities
  • Restart or re-queue the failed jobs
  • Notify the user or a process when job is completed
  • Maintain the job schedules across system outages
  • Re-queue jobs to other queues
  • Support the stopping and starting of queues
  • Log Queued jobs
  • Deal with inter-queue processing

Note: The above list can be used as evaluation parameters for the evaluation of a good scheduler.

Some important jobs that a scheduler must be able to handle are as follows:

  • Daily and ad hoc query scheduling
  • Execution of regular report requirements
  • Data load
  • Data processing
  • Index creation
  • Backup
  • Aggregation creation
  • Data transformation

Note: If the data warehouse is running on a cluster or MPP architecture, then the system scheduling manager must be capable of running across the architecture.

System Event Manager

The event manager is a kind of a software. The event manager manages the events that are defined on the data warehouse system. We cannot manage the data warehouse manually because the structure of data warehouse is very complex. Therefore we need a tool that automatically handles all the events without any intervention of the user.

Note: The Event manager monitors the events occurrences and deals with them. The event manager also tracks the myriad of things that can go wrong on this complex data warehouse system.


Events are the actions that are generated by the user or the system itself. It may be noted that the event is a measurable, observable, occurrence of a defined action.

Given below is a list of common events that are required to be tracked.

  • Hardware failure
  • Running out of space on certain key disks
  • A process dying
  • A process returning an error
  • CPU usage exceeding an 805 threshold
  • Internal contention on database serialization points
  • Buffer cache hit ratios exceeding or failure below threshold
  • A table reaching to maximum of its size
  • Excessive memory swapping
  • A table failing to extend due to lack of space
  • Disk exhibiting I/O bottlenecks
  • Usage of temporary or sort area reaching a certain thresholds
  • Any other database shared memory usage

The most important thing about events is that they should be capable of executing on their own. Event packages define the procedures for the predefined events. The code associated with each event is known as event handler. This code is executed whenever an event occurs.

System and Database Manager

System and database manager may be two separate pieces of software, but they do the same job. The objective of these tools is to automate certain processes and to simplify the execution of others. The criteria for choosing a system and the database manager are as follows:

  • increase user's quota.
  • assign and de-assign roles to the users
  • assign and de-assign the profiles to the users
  • perform database space management
  • monitor and report on space usage
  • tidy up fragmented and unused space
  • add and expand the space
  • add and remove users
  • manage user password
  • manage summary or temporary tables
  • assign or deassign temporary space to and from the user
  • reclaim the space form old or out-of-date temporary tables
  • manage error and trace logs
  • to browse log and trace files
  • redirect error or trace information
  • switch on and off error and trace logging
  • perform system space management
  • monitor and report on space usage
  • clean up old and unused file directories
  • add or expand space.

System Backup Recovery Manager

The backup and recovery tool makes it easy for operations and management staff to back-up the data. Note that the system backup manager must be integrated with the schedule manager software being used. The important features that are required for the management of backups are as follows:

  • Scheduling
  • Backup data tracking
  • Database awareness

Backups are taken only to protect against data loss. Following are the important points to remember.

  • The backup software will keep some form of database of where and when the piece of data was backed up.

  • The backup recovery manager must have a good front-end to that database.

  • The backup recovery software should be database aware.

  • Being aware of the database, the software then can be addressed in database terms, and will not perform backups that would not be viable.