Using an ETL Database

Using a WFM ETL (Extract, Transform and Load) database schema enables Genesys Infomart and other third-party reporting applications to easily create reports that incorporate WFM data. Once configured, this functionality can obtain Schedule, Adherence, and Performance information from WFM and store it into a documented relational database schema. (Prior to WFM 8.1.3, the only way to build customer reports was to use WFM API.)

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 Info Mart or third-party applications. The ETL schema can be part of, and co-exist with the main operational 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 is not executed automatically by the DBU. 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.

Database Tables and Categories

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

The ETL schema contains the following Dimension and Fact tables:

Dimension Tables	Schedule Fact Tables
WFM_BU	WFM_SCH_AGENT_DAY
WFM_SITE	WFM_SCH_AGENT_STATE
WFM_TEAM
WFM_SSG
WFM_STATE

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 the ETL database 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 ETL WFM Server 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 ETL WFM Server Application. When WFM operational and ETL schemas 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 to perform ETL functions only and the server accesses only the ETL database and not the operational WFM database, you must disable all cache preload functions, because the corresponding database tables are not available in the ETL database. The WFM Server IP contains the WFM Server Application template for a dedicated ETL Server. It will create the ETL options, set the default values, and disable cache preload functionality and wait list processes. The dedicated ETL WFM Server generates an error if a reporting client tries to obtain its data, by using the WFM API.

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 Application (see below).

Enabling ETL Functionality

You can configure the ETL database schema, by using the following options, which are configured in the WFM Server Application, in the ETL section under the Options tab:

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