Managing the WFM Database

Use Workforce Management (WFM) Backup-Restore Utility (BRU) to configure, update, maintain, backup, anonymize Personal Identifiable Information (PII) data, 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.

Tip

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.
• Anonymize PII (Personal Identifiable Information) data only or anonymize all objects names.

Important

To ensure the WFM Web Application works properly:

• Set the Microsoft SQL and Oracle database management systems (DBMS) to be case-insensitive.
• If your WFM database is deployed on the Microsoft SQL DBMS, the WFM schema requires you set READ_COMMITTED_SNAPSHOT to ON, by executing the following DB statements:

  ALTER DATABASE <WFM_DB> SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

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.

Anonymizing Data in WFM DB

The Personal Identifiable Information (PII) anonymization capability of the database backup has been added to the WFM Database Backup/Restore utility (WFMBRU). If the main WFM database needs to be shared (externally or internally, for some testing or another purposes) with sensitive data hidden, the WFM database anonymization feature can be used to make sensitive data impersonal and masked. Using this feature, customers can create WFM database backup with anonymized data, which can be restored in other environments later while having all sensitive data hidden.

WFM Database Utility supports backup procedure with two anonymization data modes:

• ANONYMIZE_PII — When this option is used, there will be nothing left in the database that can be used to identify any user or agent. For instance, first and last names, email, employee ID, any personal comments, messages, or memos.
• ANONYMIZE_FULL — When this option is used, there will be no PII in the database and also nothing that can potentially be used to identify the customer. That is, no customer created names like site, business unit, activity, shift names, etc.

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.

Using Stored Procedures to Purge Data

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 do this by using stored procedures. For the access privileges required to perform maintenance and other tasks on the database, see Database Access Privileges.

Starting in 8.5.214, the WFM Database schema has 2 sets of stored procedures in 6 distinct groups. The latest procedures are improved, cleaning up data much faster than the previous procedures. You can use the latest or previous purge procedures to cleanup your database. In the table below, the latest procedures are in bold.

Each of the previous procedures has only one date parameter and deletes specific data up to (but not including) that date.

Each of the latest procedures has three parameters, as follows:

1. The number of days back that the data should be cleaned up
2. The transaction size
3. The output parameter that returns the deleted records count

Here are the default values for these parameters:

• In MSSQL scripts:

  @DAYS_BACK INT = 90,

  @MAX_PURGE_CHUNK INT = 10000,

  @DEL_COUNTER INT OUTPUT

• In Oracle scripts:

  DAYS_BACK IN NUMBER DEFAULT 90,

  MAX_PURGE_CHUNK IN NUMBER DEFAULT 10000,

  DEL_COUNTER OUT NUMBER

Table: Purge Procedures

Group	Procedure (latest version in bold)	Description
Calendar	WMP_DEL_CAL_ITEMS2 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.
Configuration	WMP_DEL_CONF_T_AG2 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 purging Terminated Agents, you can delete any related data by using other procedures. For example: Schedules can be deleted using a separate WMP_DEL_SCH_MASTE procedure. Schedule Scenarios can be deleted using a separate WMP_DEL_SCH_SCENA procedure. Calendar items and Meetings can be deleted using a separate WMP_DEL_CAL_ITEMS procedure. Agent Real Time State changes can be deleted using a separate WMP_DEL_H_AGENT_ST procedure.
	WMP_DEL_CONF_EXCE2 WMP_DEL_CONF_EXCE	Purges Exception types previously deleted on dates earlier than the provided date.
	WMP_DEL_CONF_ACTI2 WMP_DEL_CONF_ACTI	Purges Activities previously deleted on dates earlier than the provided date.
	WMP_DEL_CONF_REPO2 WMP_DEL_CONF_REPO	Purges reports previously created on dates earlier than the provided date.
Schedule	WMP_DEL_SCH_MASTE2 WMP_DEL_SCH_MASTE	Purges Master Schedules for dates earlier than the provided date.
	WMP_DEL_SCH_SCENA2 WMP_DEL_SCH_SCENA	Purges Schedule Scenarios that ended earlier than the provided date.
	WMP_DEL_SCH_TRADE2 WMP_DEL_SCH_TRADE	Purges Schedule Trade Proposals that expired earlier than provided date together with the related responses and trades.
Forecast	WMP_DEL_FOR_MASTER2 WMP_DEL_FOR_MASTER	Purges the Master Forecasts and Master Forecast Comments that were created on dates earlier than the provided date.
	WMP_DEL_FOR_SCENAR2 WMP_DEL_FOR_SCENAR	Purges the Forecast Scenarios that ended earlier than the provided date, and Forecast Scenarios Comments created on dates earlier than the provided date. Comments are deleted, but not counted.
History	WMP_DEL_H_AGENT_ST2 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_PERF2 WMP_DEL_H_ACT_PERF	Purges the historical Performance information for all activities in all sites and business units for dates earlier than the provided date.
	WMP_DEL_H_OV_TEMPL2 WMP_DEL_H_OV_TEMPL	Purges Overlap templates that end earlier than the provided date.
	WMP_DEL_H_NOTIFICA2 WMP_DEL_H_NOTIFICA	Purges Calendar, Schedule, and Schedule Trade notification acknowledgments generated earlier than the provided date.
Audit	WMP_DEL_CAL_AUDIT2 WMP_DEL_CAL_AUDIT	Purges all Calendar audit records generated earlier than the provided date.
	WMP_DEL_CONF_AUDIT2 WMP_DEL_CONF_AUDIT	Purges all Configuration audit records generated earlier than the provided date.
	WMP_DEL_SCH_AUDIT2' WMP_DEL_SCH_AUDIT	Purges Schedule audit records generated earlier than the provided date.
	WMP_DEL_FOR_AUDIT2 WMP_DEL_FOR_AUDIT	Purges Forecast audit records generated earlier than the provided date.
WMP_DEL_ALL		This procedure is added for convenience only. It calls all of the latest procedures (in this table) one by one and returns the sum (count) of the deleted records. Tip WFM counts directly deleted records only. However, since records in related tables are also deleted by database triggers, the total number of deleted records in tables can be higher than reported.

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.

The latest WFM purging procedures (see table above) enable you to purge data faster than the previous procedures. You can still use the previous procedures to purging data, but cleanup will take longer to complete.

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.

Warning

Double-check your choices before you execute the commands. You cannot retrieve deleted data.

Start of Procedure

1. Add statements to the SQL script file using the required date. For example, to purge Calendar Audit data:
   • Using the latest procedure (fast cleanup) up to 100 days back from the current date:

     SET SERVEROUTPUT ON

     DECLARE

     AMOUNT NUMBER;

     BEGIN

     WMP_DEL_CAL_AUDIT2(100, 10000, AMOUNT);

     DBMS_OUTPUT.PUT_LINE('Finished DEL_CAL_AUDIT2. Deleted total records:' || AMOUNT);

     END;

     /

   • Using the previous procedure up to (but not including) 01/01/2015:

     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>.

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.

Warning

Double-check your choices before you execute the commands. You cannot retrieve deleted data.

Start of Procedure

1. Add statements to the SQL script file using the required date. For example, to purge Calendar Audit data:
   • Using the latest procedure (fast cleanup) up to 100 days back from the current date:

     DECLARE @amount INT

     EXEC WMP_DEL_ALL 100, 10000, @DEL_COUNTER = @amount OUTPUT

     PRINT 'Finished WMP_DEL_ALL. Deleted total records: ' + CAST(@amount as NVARCHAR(30))

     GO

   • Using the previous procedure up to (but not including) 01/01/2015:
     1. EXEC WMP_DEL_CAL_AUDIT '2015-01-01 00:00:00'
     2. 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>

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 Using ETL Database Schema.