Revision as of 16:59, June 7, 2019 by Jumunn (talk | contribs) (Update with the copy of version: Public)
Jump to: navigation, search

Workforce Management ETL Database Reference

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

The ETL schema can co-exist with the main operational WFM database, 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.

Related Topics

Intended audience

This reference guide is intended for:

  • Reporting and business analysts who want to leverage the data that is contained in Genesys WFM, Data Mart, Info Mart and other third party applications to produce reports for business users.
  • IT administrators who want to gain an understanding of the components that enable WFM.

This reference assumes that the reader understands of the following:

  • Relational database concepts.
  • Structured Query Language (SQL) for querying and mining data.
  • Genesys WFM configuration and its data sources.
  • Data warehouse concepts—including working with star schemas, dimensions, aggregates, and measures.
  • Extraction, Transformation, and Loading (ETL) concepts

Overview

The Workforce Management (WFM) ETL Database schema contains Dimension, Fact, Service and Control, and Referred Info Mart tables. Dimension tables 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.

This reference contains detailed descriptions of these tables (summarized in this topic), and examples of queries that can be run on the table data.

There are three types of Dimension tables.

  • General Dimension tables include:
    • WFM_BU—Business unit descriptive information.
    • WFM_SITE—Site descriptive information.
    • WFM_TEAM—Team descriptive information.
    • WFM_AGENT—Agent descriptive information.
    • WFM_ACTIVITY_TYPE—Activity types.
    • WFM_ACTIVITY—Activity descriptive information.
  • Schedule Dimension tables include:
    • WFM_SSG_TYPE—Schedule State Group types.
    • WFM_SSG—Schedule State Group descriptive information.
    • WFM_STATE_TYPE—Schedule state types.
    • WFM_STATE—Schedule state descriptive information.
  • Performance Dimension tables include:
    • WFM_PERF_ITEM—Performance statistics.
See all Dimension tables in detail.

The Fact tables provide the following data:

  • Agent/team/site adherence totals aggregates for the calendar day and for the 15-minute interval.
  • Agent/team/site schedule totals aggregates for the schedule day and for the 15-minute interval.
  • Agent schedule states.
  • Schedule state and Schedule State Group (SSG) duration aggregates for the 15-minute interval.
  • Numerous activity (single-site, multi-site, Activity Group) and site performance statistics aggregates for the calendar day and for the 15-minute interval.
There are three types of Fact tables.
  • Adherence Fact tables inlcude:
    • WFM_ADH_AGENT_DAY—Aggregate of the agent adherence information for 24-hour days.
    • WFM_ADH_AGENT_TIMESTEP—Aggregate of the agent adherence for 15-minute intervals.
  • Schedule Fact tables include:
    • WFM_SCH_AGENT_DAY—Agent schedule day information.
    • WFM_SCH_AGENT_TIMESTEP—Aggregate of agent’s schedule totals for 15-minute intervals.
    • WFM_SCH_AGENT_STATE—Agent Schedule state information.
    • WFM_SCH_AGENT_STATE_TIMESTEP—Aggregate of schedule state duration for 15-minute intervals.
  • Performance Fact tables include:
    • WFM_PERF_ITEM_DAY—Performance statistics in 24-hour day granularity
    • WFM_PERF_ITEM_TIMESTEP—Performance statistics in 15-minute granularity
See all Fact tables in detail.

There is one Service and Control table:

  • WM_DB_VERSION—Internal version table.

There are six referred Info Mart tables:

  • CTL_AUDIT_LOG
  • DATE_TIME
  • TIME_ZONE
  • GIDB_GC_TENANT
  • GIDB_GC_SWITCH
  • GIDB_GC_AGENT

There are three categories of ETL query examples:

  • Adherence queries
  • Schedule queries
  • Performance statistics queries
See all query examples in detail.

Abbreviations of database terms

The Workforce Management (WFM) ETL Database Reference uses abbreviations throughout all topics to provide detailed information about and within the tables, including a concise listing of primary and foreign keys, default field values, and mandatory fields for each table. The field and index abbreviations are described here:

Field characterizations

  • P—Primary key
  • M—Mandatory field
  • F—Foreign key
  • DV—Default value

Index characterizations

  • C—Cluster
  • U—Unique


Comments or questions about this documentation? Contact us for support!