Jump to: navigation, search

Managing the WFM Database

Use Workforce Management (WFM) Backup-Restore Utility (BRU) to configure, update, maintain, backup, restore, and if required, migrate your database. Using a command line console, the Backup-Restore Utility provides a number of functions, enabling you to perform the tasks described in this topic.

Important
Genesys does not support direct access to the WFM Database to create custom reports. The WFM Database structure often changes and any custom reports created, by directly accessing the database could stop working after any update. Use the supported methods described in Using ETL Database Schema and Integration API Developer's Guide to generate custom reports.

Overview

Use the BRU to:

  • Create and configure a new database.
  • Update your database to that latest release.
  • Perform other database updates as needed.
  • Perform regular maintenance, such as cleanup of obsolete data.
  • Migrate data from a previous WFM release to a WFM 8.5 database.
Warning
To ensure the WFM Web Application to work properly, you must set Microsoft SQL and Oracle database management systems to be case-insensitive.

New Database Configuration

The WFM Backup-Restore Utility populates and configures the new database for you, setting up the necessary tables, views, indexes, and so on. For instructions, see Using the Backup-Restore Utility.

If you are already using WFM 7.x, its not necessary to install a new database. Simply perform a database update to transition your database to release 8.5.

Updating Your WFM Database

From time to time, Genesys issues Maintenance Releases (MR) of its products. Some of the Workforce Management updates require database updates. If so, you can perform them using the WFM BRU.

Performing a Database Update

Execute WFM BRU by using two options:

  • -DB <database connection information>
  • -UPDATE
Example:
WFMBRU.EXE -DB "mssql;MSSQL2012_SERVER;WFM_DB;sa;password" -UPDATE
Important
If you are migrating from WFM 7.6 to 8.5, all you need to do to your database is to update it. You do not need to create a new database.

Using Stored Procedures for Database Maintenance

To properly maintain your WFM Database, you might need to periodically clean up or purge obsolete data. The procedures Performing Database Cleanup (Oracle) and Performing Database Cleanup (MSSQL) describe how to cleanup the database using the stored procedures. For the access privileges required to perform maintenance and other tasks on the database, see Database Access Privileges.

The WFM Database schema has 15 stored procedures, each having only one date parameter. Each procedure deletes specific data up to (but not including) this date. There are 6 distinct groups of stored procedures:

1. Calendar

  • WMP_DEL_CAL_ITEMS—Purges Calendar Items of all types for dates earlier than the provided date. Also purges Meetings that end earlier than the provided date.

2. Configuration

  • WMP_DEL_CONF_T_AG—Purges agents whose termination date is earlier than the provided date and who do not have any Schedules, Calendar Items, Agent Real Time State Changes, are not included in any Schedule Scenarios, and are not assigned to any Meetings.
    Before Terminated Agents can be purged, any related data can be deleted using other procedures. For example

3. Schedule

  • WMP_DEL_SCH_MASTE—Purges Master Schedules for dates earlier than the provided date.
  • WMP_DEL_SCH_SCENA—Purges Schedule Scenarios that ended earlier than the provided date.
  • WMP_DEL_SCH_TRADE—Purges Schedule Trade Proposals that expired earlier than provided date together with the related Responses and Trades.

4. Forecast

  • WMP_DEL_FOR_MASTER—Purges the Master Forecasts for dates earlier than the provided date.
  • WMP_DEL_FOR_SCENAR—Purges the Forecast Scenarios that ended earlier than the provided date.

5. History

  • WMP_DEL_H_AGENT_ST—Purges the history of Agent Real Time State changes that occurred earlier than the provided date.
  • WMP_DEL_H_ACT_PERF—Purges the historical Performance information for all Activities in all Sites and BUs for dates earlier than the provided date.
  • WMP_DEL_H_OV_TEMPL—Purges the Overlap Templates that ended earlier than the provided date.
  • WMP_DEL_H_NOTIFICA—Purges Calendar, Schedule, and Schedule Trade notification acknowledgments generated earlier than the provided date.

6. Audit

  • WMP_DEL_CAL_AUDIT—Purges all Calendar Audit records generated earlier than the provided date.
  • WMP_DEL_CONF_AUDIT—Purges all Configuration Audit records generated earlier than the provided date.
  • WMP_DEL_SCH_AUDIT—Purges Schedule Audit records generated earlier than the provided date.
  • WMP_DEL_FOR_AUDIT—Purges Forecast Audit records generated earlier than the provided date.

Backing Up and Restoring Your Database

The BRU provides an improved method of backing up and restoring the WFM Database. Use the following procedures to backup and restore your database:

Backing Up the Database

Execute WFM BRU by using three options:

  • -DB <database connection information>
  • -BACKUP
  • -FILE <backup file name>
Example:
WFMBRU.EXE -DB "mssql;MSSQL2012_SERVER;WFM_DB;sa;password" -BACKUP -FILE "WFM_BACKUP.db"

Restoring the Database

Execute WFM BRU by using three options:

  • -DB <database connection information>
  • -RESTORE
  • -FILE <backup file name>
Example:
WFMBRU.EXE -DB "mssql;MSSQL2012_SERVER;WFM_DB;sa;password" -RESTORE -FILE "WFM_BACKUP.db"

For more information about BRU, see Using the Backup-Restore Utility.

Purging Procedures

Use the procedures below to perform cleanup on Oracle or MSSQL databases. Although these procedures describe how to use the Oracle and MSSQL utilities, you can use other utilities to execute stored procedures, if you wish.

Performing Database Cleanup (Oracle)

Purpose: To remove obsolete data from your Oracle database.
Prerequisite: Your database is up-to-date and the database tool used to execute SQL statements is available. The sqlplus.exe utility is available in Oracle Client installation.

Start of Procedure

  1. Add statements to the SQL script file using the required date.
    For example, Calendar Audit data up to (but not including) 01/01/2015 could be purged by adding the following text lines:
    EXEC WMP_DEL_CAL_AUDIT(TO_DATE('01-01-2015 0:0:0', 'DD-MM-YYYY HH24:MI:SS'));
    EXIT
  2. Obtain the Oracle username, password, and other database connection information for WFM Server, and use this information in the next step.
  3. Execute the command sqlplus.exe <user name>/<password>@<Oracle database server alias> @<input SQL script file name from step 1>.
    Warning
    Double-check your choices before you execute the commands. You cannot retrieve deleted data.

End of Procedure

Performing Database Cleanup (MSSQL)

Purpose: To remove obsolete data from your MSSQL database.
Prerequisite: Your database is up-to-date and the database tool used to execute SQL statements is available. The sqlcmd.exe utility is available in MSSQL Client installation.

Start of Procedure

  1. Add statements to the SQL script file using the required date.
    For example, Calendar Audit data up to (but not including) 01/01/2015 could be purged by adding the following text lines:
    EXEC WMP_DEL_CAL_AUDIT '2015-01-01 00:00:00'
    GO
  2. Obtain the MSSQL username, password, and other database connection information for WFM Server and use this information in the next step.
  3. Execute the command sqlcmd.exe -U <user name> -P <password> -S <MSSQL database server name> -i <input SQL script file name from step 1> -d <MSSQL database name>
    Warning
    Double-check your choices before you execute the commands. You cannot retrieve deleted data.

End of Procedure

Database Migration

For migration instructions, see the "Workforce Management Migration Procedures” chapter in the Workforce Management Migration.

ETL Database

You can set up an WFM ETL (Extract, Transform and Load) database schema to enable third-party reporting applications to easily create reports, by incorporating WFM data. Previously, the only way to build customer reports was to use the WFM API.

ETL functionality obtains Schedule, Adherence and Performance information from WFM and stores it into a documented relational database schema. For more information about this functionality or to set up an ETL database schema, see Setting up an ETL Database.

Feedback

Comment on this article:

blog comments powered by Disqus
This page was last modified on September 27, 2017, at 09:15.