Using ETL Database Schema

Using a WFM ETL (Extract, Transform and Load) database schema enables Genesys Interactive Insights and other third-party reporting applications to easily create reports that incorporate Genesys Workforce Management (WFM) data. Once configured, this functionality can obtain Schedule, Adherence, and Performance information from WFM and store it into a documented relational database schema.

ETL Database Schema and Script

WFM itself does not use the data from ETL storage for any task. ETL stores the data into the designated database schema, for use only by Genesys Interactive Insights or third-party reporting applications. The ETL schema can be part of, and co-exist with the main operational Genesys WFM database. It can be a standalone database or part of any other database. WFM provides the SQL script to create the database schema, but does not specify which physical tablespace, user, or database on which to create it.

The script is included in WFM Database Utility (DBU) IP, but it is not executed automatically. The database administrator must execute the script, by using a third-party SQL interpreter. The script is found in the \Scripts folder in WFM Database Utility deployment folder.

For more information about the ETL DB schema, see the Workforce Management ETL Database Reference.

Database Tables and Categories

The ETL database contains various types of tables, including Fact tables, Dimension tables, a Service and Control table, and Referred Genesys Info Mart tables.

Dimension tables somewhat correspond to the WFM organization, configuration, and policy objects. The Dimension tables provide sorting, grouping, and filtering capabilities for reports. Fact tables contain adherence, performance, and schedule information and can be sorted, grouped, and filtered by dimensions.

For detailed descriptions of these tables, see the Workforce Management ETL Database Reference.

WFM Server's Role in the ETL Process

WFM Server has built-in ETL functionality. However, you must configure some WFM Server Application options to enable it (see Enabling ETL Functionality).

In the following two deployment options, you must also create a connection to WFM Server for ETL to function properly:

1. If the ETL schema is created in a database, other than the WFM database, two WFM Server instances are required—one that is connected to the operational WFM database and one connected to the ETL database. In this setup, the WFM Server for ETL instance (with the connection to the ETL database), also connects to the main WFM Server instance (with the connection to WFM database) and obtains data, by using the WFM binary API (also used to generate WFM internal reports). This means, a connection to the main WFM Server must be added to the WFM Server for ETL Application. When WFM operational and ETL share the same database, a single WFM Server instance is sufficient to perform both functions—serving WFM API requests and performing ETL data storage.
2. If you set up a dedicated WFM Server for ETL only and the server accesses only the ETL database and not the operational WFM database, you must disable all cache preloaded functions, because the corresponding database tables are not available in the ETL database. The WFM Server IP contains the WFM Server Application template WFM_ETL for a dedicated ETL Server. It will create the ETL options, set the default and proper values, and disable cache preloaded functionality and wait list processes. The dedicated WFM Server for ETL generates an error if a reporting client tries to obtain its data, by using the WFM API. To prevent that, do not change the values for the options not described in WFM Server ETL chapter.

To install WFM Server as a dedicated ETL Server, see the installation procedure in Installing and Uninstalling WFM Components.

ETL Process Flow

When the ETL process starts, it synchronizes the WFM operational database with the ETL database. During synchronization, the process first transfers all new Dimension information the WFM operational database to the ETL database. Then, updates all of the Dimension objects that were updated in operational database since the last run of the ETL process. After the Dimension information is synchronized, the process transfers newly updated or modified Fact information in the same way. However, the process does not try to synchronize all Fact information, but only a specified number of days in the past and future. The number of days is specified by setting the configuration options in the WFM Server for ETL Application (see below).

Enabling ETL Functionality

You can configure the ETL process by using the following options, which are configured in the WFM Server for ETL Application, Options tab in the [ETL] section:

• DaysAhead—The number of days (from the current day) to look ahead for Fact data.
• DaysBack—The number of days (from the current day) to look back (to the past) for Fact data.
• DayChunk—The number of days that will be processed at a time.
• ETLTimeout—A non-zero value that starts the ETL process within WFM Server. The number represents the timeout interval between executions of the ETL process.

For a detailed description of these configuration options, see the [ETL] section in WFM Server for ETL options.

Behavior of WFM Server for ETL at Startup

At startup, WFM Server for ETL waits for a period of time (determined by the value that is set in the ETLTimeout configuration option) before starting the first run of the ETL process.

Updating Your ETL Database

Updating your WFM ETL database is easy when you use the WFM Backup-Restore Utility (BRU). See Workforce Management Migration for information and procedures about updating or migrating your database.

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

Purging the ETL Database

To purge old data in the ETL Database, configure the following options in the [ETL] section of the WFM Server for ETL Application:

• PurgeDaysBack
• PurgeDate
• MaxPurgeChunk

[ETL] PurgeDaysBack is the main purge option that enables a rolling data purge. The [ETL] PurgeDate option is an optional override that can be used in special cases, when required.