Dimension Tables

This topic describes the Dimension tables in the Workforce Management (WFM) ETL Database schema. To view the details in each table click the table name (for example, WFM_BU or WFM_SITE) in the TOC on the right side of this page.

For a description of the abbreviations used in these tables, see Abbreviation of Database Terms.

When to use IANA time zones

The IANA_TIMEZONE column in the WFM_BU and WFM_SITE tables supports Internet Assigned Numbers Authority (IANA) time zones in WFM business units and sites.

When the [auth] provider configuration option is set to wfm in the WFM Server Application, WFM switches from using proprietary time zones (synchronized with Genesys Configuration Server) to standard IANA time zones ( see Internet Assigned Numbers Authority (IANA)). Also, the ETL column TIME_ZONE_KEY associated with BUs and sites becomes NULL as this key represents Configuration Server’s time zone assignment, which WFM no longer uses.

If you are using WFM's classic Forecast UI, you can disable IANA time zones in WFM and restore the previous time zone functionality by setting the [Server] tzdb option value to 0 in the WFM Server Application. However, if you are using WFM's latest Forecast UI, it requires IANA time zones.

WFM_BU

This table contains business unit descriptive information.

Column	Data type	P	M	F	DV
WFM_BU_KEY	int	✔	✔
WFM_BU_NAME	varchar(255)		✔
WFM_TIMESTAMP	numeric(19)		✔
TIME_ZONE_KEY	int			✔
IANA_TIMEZONE	varchar(255)
ACTIVE_FLAG	int		✔
TENANT_KEY	int			✔
CREATE_AUDIT_KEY	numeric(19)			✔
UPDATE_AUDIT_KEY	numeric(19)			✔
PURGE_FLAG	int

Description of Columns

• WFM_BU_KEY—The primary key for this table.
• WFM_BU_NAME—The name of Business Unit (BU).
• WFM_TIMESTAMP—An internal timestamp value.
• TIME_ZONE_KEY—The surrogate key used to join the TIME_ZONE dimension to the fact tables. It specifies the time zone of the Business Unit.
• IANA_TIMEZONE—The name of the standard IANA time zone. It specifies the time zone of the Business Unit. See more information about IANA time zones in When to use IANA time zones.
• ACTIVE_FLAG—Indicates whether the Business Unit is currently active and the corresponding record exists in operational WFM Database: 0 = No, 1 = Yes. See more about ACTIVE_FLAG in Purging terminated agents.
• TENANT_KEY—The surrogate key used to join the TENANT dimension to the fact tables.
• CREATE_AUDIT_KEY—The surrogate key used to join to the CTL_AUDIT_LOG control table. The key specifies the lineage for data creation. This value is useful for aggregation, Enterprise Application Integration (EAI), and ETL tools (that is, applications that need to identify newly added data).
• UPDATE_AUDIT_KEY—The surrogate key used to join to the CTL_AUDIT_LOG control table. The key specifies the lineage for data update. This value is useful for aggregation, Enterprise Application Integration (EAI), and ETL tools (that is, applications that need to identify recently modified data).
• PURGE_FLAG—This field is reserved (used internally by the purging script to mark records for purging).

WFM_SITE

This table contains site descriptive information.

Column	Data type	P	M	F	DV
WFM_SITE_KEY	int		✔
WFM_BU_KEY	int		✔	✔
WFM_SITE_NAME	varchar(255)		✔
WFM_TIMESTAMP	numeric(19)		✔
SWITCH_KEY	int			✔
TIME_ZONE_KEY	int			✔
IANA_TIMEZONE	varchar(255)
ACTIVE_FLAG	int		✔
TENANT_KEY	int			✔
CREATE_AUDIT_KEY	numeric(19)			✔
UPDATE_AUDIT_KEY	numeric(19)			✔
PURGE_FLAG	int

Description of Columns

• WFM_SITE_KEY—The primary key for this table.
• WFM_BU_KEY—The surrogate key used to join the WFM_BU dimension to the fact tables. It specifies the Business Unit of the Site.
• WFM_SITE_NAME—The name of the Site.
• WFM_TIMESTAMP—An internal timestamp value.
• SWITCH_KEY—The surrogate key used to join the GIDB_GC_SWITCH dimension to the fact tables. It specifies the switch associated with the Site.
• TIME_ZONE_KEY—The surrogate key used to join the TIME_ZONE dimension to the fact tables. It specifies the time zone of the Site.
• IANA_TIMEZONE—The name of the standard IANA time zone (www.iana.org/time-zones). It specifies the time zone of the Site. See more information about IANA time zones in When to use IANA time zones.
• ACTIVE_FLAG—Indicates whether the Site is currently active and the corresponding record exists in operational WFM Database: 0 = No, 1 = Yes. See more about ACTIVE_FLAG in Purging terminated agents.
• TENANT_KEY—The surrogate key used to join the TENANT dimension to the fact tables.
• CREATE_AUDIT_KEY—The surrogate key used to join to the CTL_AUDIT_LOG control table. The key specifies the lineage for data creation. This value is useful for aggregation, Enterprise Application Integration (EAI), and ETL tools (that is, applications that need to identify newly added data).
• UPDATE_AUDIT_KEY—The surrogate key used to join to the CTL_AUDIT_LOG control table. The key specifies the lineage for data update. This value is useful for aggregation, Enterprise Application Integration (EAI), and ETL tools (that is, applications that need to identify recently modified data).
• PURGE_FLAG—This field is reserved (used internally by the purging script to mark records for purging).

WFM_TEAM

This table contains team descriptive information.

Column	Data type	P	M	F	DV
WFM_TEAM_KEY	int	✔	✔
WFM_TEAM_NAME	varchar(255)		✔
WFM_SITE_KEY	int		✔	✔
WFM_TIMESTAMP	numeric(19)		✔
ACTIVE_FLAG	int		✔
TENANT_KEY	int			✔
CREATE_AUDIT_KEY	numeric(19)			✔
UPDATE_AUDIT_KEY	numeric(19)			✔
PURGE_FLAG	int

Description of Columns

• WFM_TEAM_KEY—The primary key for this table.
• WFM_TEAM_NAME—The name of the Team.
• WFM_SITE_KEY—The surrogate key used to join the WFM_SITE dimension to the fact tables. It specifies the Site of the Team.
• WFM_TIMESTAMP—An internal timestamp value.
• ACTIVE_FLAG—Indicates whether the Team is currently active and the corresponding record exists in operational WFM Database: 0 = No, 1 = Yes. See more about ACTIVE_FLAG in Purging terminated agents.
• TENANT_KEY—The surrogate key used to join the TENANT dimension to the fact tables.
• CREATE_AUDIT_KEY—The surrogate key used to join to the CTL_AUDIT_LOG control table. The key specifies the lineage for data creation. This value is useful for aggregation, Enterprise Application Integration (EAI), and ETL tools (that is, applications that need to identify newly added data).
• UPDATE_AUDIT_KEY—The surrogate key used to join to the CTL_AUDIT_LOG control table. The key specifies the lineage for data update. This value is useful for aggregation, Enterprise Application Integration (EAI), and ETL tools (that is, applications that need to identify recently modified data).
• PURGE_FLAG—This field is reserved (used internally by the purging script to mark records for purging).

WFM_AGENT

This table contains agent descriptive information.

Column	Data type	P	M	F	DV
WFM_AGENT_KEY	int	✔	✔
WFM_BU_KEY	int			✔
WFM_SITE_KEY	int			✔
WFM_TEAM_KEY	int			✔
EMPLOYEE_ID	varchar(64)		✔
FIRST_NAME	varchar(64)		✔
LAST_NAME	varchar(64)		✔
HIRE_DATE	date		✔
TERMINATION_DATE	date
WFM_TIMESTAMP	numeric(19)		✔
WM_HOURLY_WAGE	int
WM_SENIORITY	int
AGENT_KEY	int			✔
ACTIVE_FLAG	int		✔
TENANT_KEY	int			✔
CREATE_AUDIT_KEY	numeric(19)			✔
UPDATE_AUDIT_KEY	numeric(19)			✔
PURGE_FLAG	int

Description of Columns

• WFM_AGENT_KEY—The primary key for this table.
• WFM_BU_KEY—The surrogate key used to join the WFM_BU dimension to the fact tables. It specifies the the Agent's business unit.
• WFM_SITE_KEY—The surrogate key used to join the WFM_SITE dimension to the fact tables. It specifies the Agent's site.
• WFM_TEAM_KEY—The surrogate key used to join the WFM_TEAM dimension to the fact tables. It specifies the Agent's team. It is NULL if Agent does not belong to any team.
• EMPLOYEE_ID—The Agent's employee ID.
• FIRST_NAME—The Agent's first name.
• LAST_NAME—The Agent's last name.
• HIRE_DATE—The Agent's hire date.
• TERMINATION_DATE—The Agent's termination date.
• WFM_TIMESTAMP—An internal timestamp value.
• WM_HOURLY_WAGE—The agent's hourly wage.
• WM_SENIORITY—The user-defined rank field.
• AGENT_KEY—The surrogate key used to join the GIDB_GC_AGENT dimension to the fact tables.
• ACTIVE_FLAG—Indicates whether the agent is currently active and the corresponding record exists in operational WFM Database: 0 = No, 1 = Yes. See more about ACTIVE_FLAG in Purging terminated agents.
• TENANT_KEY—The surrogate key used to join the TENANT dimension to the fact tables.
• CREATE_AUDIT_KEY—The surrogate key used to join to the CTL_AUDIT_LOG control table. The key specifies the lineage for data creation. This value is useful for aggregation, Enterprise Application Integration (EAI), and ETL tools (that is, applications that need to identify newly added data).
• UPDATE_AUDIT_KEY—The surrogate key used to join to the CTL_AUDIT_LOG control table. The key specifies the lineage for data update. This value is useful for aggregation, Enterprise Application Integration (EAI), and ETL tools (that is, applications that need to identify recently modified data).
• PURGE_FLAG—This field is reserved (used internally by the purging script to mark records for purging).

WFM_ACTIVITY_TYPE

This table contains activity types and descriptions.

Column	Data type	P	M	F	DV
WFM_ACTIVITY_TYPE_KEY	int	✔	✔
WFM_ACTIVITY_TYPE_NAME	varchar(64)		✔

Description of Columns

• WFM_ACTIVITY_TYPE_KEY—The Activity type ID.
• WFM_ACTIVITY_TYPE_NAME—The Activity type name. The table below contains valid values.

ID	Name
0	'Immediate'
2	'Fixed Staffing'
4	'Deferred'
10	'Activity Group'

WFM_ACTIVITY

This table contains activity descriptive information.

Column	Data type	P	M	F	DV
WFM_ACTIVITY_KEY	int	✔	✔
WFM_BU_KEY	int		✔	✔
WFM_SITE_KEY	int			✔
WFM_MSA_KEY	int			✔
WFM_ACTIVITY_NAME	varchar(255)		✔
WFM_ACTIVITY_SHORT_NAME	varchar(6)		✔
WFM_ACTIVITY_TYPE_KEY	int		✔	✔
WFM_TIMESTAMP	numeric(19)		✔
ACTIVE_FLAG	int		✔
TENANT_KEY	int			✔
CREATE_AUDIT_KEY	numeric(19)			✔
UPDATE_AUDIT_KEY	numeric(19)			✔
PURGE_FLAG	int

Description of Columns

• WFM_ACTIVITY_KEY—The primary key for this table.
• WFM_BU_KEY—The surrogate key used to join the WFM_BU dimension to the fact tables. It specifies the Business Unit of the Activity.
• WFM_SITE_KEY—The surrogate key used to join the WFM_SITE dimension to the fact tables. It specifies the Site of the Activity. It is NULL if Activity is Multi-Site Activity (MSA) or Activity Group (AG).
• WFM_MSA_KEY—The surrogate key used to join the parent Multi-Site Activity to the child Activity. It is NULL for Multi-Site Activity and Activity Group or if Activity does not belong to any Multi-Site Activity.
• WFM_ACTIVITY_NAME—The name of the Activity.
• WFM_ACTIVITY_SHORT_NAME—The short name of the Activity.
• WFM_ACTIVITY_TYPE_KEY—The surrogate key used to join the WFM_ACTIVITY_TYPE dimension. It specifies the type of the Activity.
• WFM_TIMESTAMP—An internal timestamp value.
• ACTIVE_FLAG—Indicates whether the Activity is currently active and the corresponding record exists in operational WFM Database: 0 = No, 1 = Yes. See more about ACTIVE_FLAG in Purging terminated agents.
• TENANT_KEY—The surrogate key used to join the TENANT dimension to the fact tables.
• CREATE_AUDIT_KEY—The surrogate key used to join to the CTL_AUDIT_LOG control table. The key specifies the lineage for data creation. This value is useful for aggregation, Enterprise Application Integration (EAI), and ETL tools (that is, applications that need to identify newly added data).
• UPDATE_AUDIT_KEY—The surrogate key used to join to the CTL_AUDIT_LOG control table. The key specifies the lineage for data update. This value is useful for aggregation, Enterprise Application Integration (EAI), and ETL tools (that is, applications that need to identify recently modified data).
• PURGE_FLAG—This field is reserved (used internally by the purging script to mark records for purging).

WFM_SSG_TYPE

This table contains schedule state group types and descriptions.

Column	Data type	P	M	F	DV
WFM_SSG_TYPE_KEY	int	✔	✔
WFM_SSG_TYPE_NAME	varchar(64)		✔

Description of Columns

• WFM_SSG_TYPE_KEY—The Schedule State Group type ID.
• WFM_SSG_TYPE_NAME—The Schedule State Group type name. The table below contains valid values.

ID	Name
1	'Working Overhead'
2	'Non-Working Overhead'
3	'Actual Work'

WFM_SSG

This table contains schedule state group descriptive information.

Column	Data type	P	M	F	DV
WFM_SSG_KEY	int	✔	✔
WFM_SITE_KEY	int		✔	✔
WFM_SSG_NAME	varchar(255)		✔
WFM_SSG_TYPE_KEY	int		✔	✔
WFM_SSG_WEIGHT	int		✔
WFM_TIMESTAMP	numeric(19)		✔
ACTIVE_FLAG	int		✔
TENANT_KEY	int			✔
CREATE_AUDIT_KEY	numeric(19)			✔
UPDATE_AUDIT_KEY	numeric(19)
PURGE_FLAG	int

Description of Columns

• WFM_SSG_KEY—The primary key for this table.
• WFM_SITE_KEY—*The surrogate key used to join the WFM_SITE dimension to the fact tables. It specifies the site of the Schedule State Group (SSG).
• WFM_SSG_NAME—The name of the Schedule State Group.
• WFM_SSG_TYPE_KEY—The surrogate key used to join the WFM_SSG_TYPE dimension. It specifies the type of the Schedule State Group.
• WFM_SSG_WEIGHT—The superficial weight value of Schedule State Group used for grouping.
• WFM_TIMESTAMP—An internal timestamp value.
• ACTIVE_FLAG—Indicates whether the Schedule State Group is currently active and the corresponding record exists in operational WFM Database: 0 = No, 1 = Yes. See more about ACTIVE_FLAG in Purging terminated agents.
• TENANT_KEY—The surrogate key used to join the TENANT dimension to the fact tables.
• CREATE_AUDIT_KEY—The surrogate key used to join to the CTL_AUDIT_LOG control table. The key specifies the lineage for data creation. This value is useful for aggregation, enterprise application integration (EAI), and ETL tools (that is, applications that need to identify newly added data).
• UPDATE_AUDIT_KEY—The surrogate key used to join to the CTL_AUDIT_LOG control table. The key specifies the lineage for data update. This value is useful for aggregation, Enterprise Application Integration (EAI), and ETL tools (that is, applications that need to identify recently modified data).
• PURGE_FLAG—This field is reserved (used internally by the purging script to mark records for purging).

WFM_STATE_TYPE

This table contains schedule state types and descriptions.

Column	Data type	P	M	F	DV
WFM_STATE_TYPE_KEY	int	✔	✔
WFM_STATE_TYPE_NAME	varchar(64)		✔

Description of Columns

• WFM_STATE_TYPE_KEY—The State type ID.
• WFM_STATE_TYPE_NAME—The State type name. The table below contains valid values.

ID	Name
0	'None'
1	'Day Off'
2	'Time Off'
3	'Exception'
4	'Break'
5	'Meal'
6	'Activity'
7	'Activity Set'
8	'Shift'
9	'Marked Time'

WFM_STATE

This table contains schedule state descriptive information.

Column	Data type	P	M	F	DV
WFM_STATE_KEY	int	✔	✔
WFM_SITE_KEY	int		✔	✔
WFM_SSG_KEY	int			✔
WFM_STATE_TYPE_KEY	int		✔	✔
WFM_STATE_ID	int		✔
WFM_STATE_NAME	varchar(255)		✔
WFM_STATE_SHORT_NAME	varchar(6)		✔
TENANT_KEY	int			✔
CREATE_AUDIT_KEY	numeric (19)			✔
UPDATE_AUDIT_KEY	numeric(19)			✔
PURGE_FLAG	int

Description of Columns

• WFM_STATE_KEY—The primary key for this table.
• WFM_SITE_KEY—The surrogate key used to join the WFM_SITE dimension to the fact tables. It specifies the Site of the Schedule State.
• WFM_SSG_KEY—The surrogate key used to join the WFM_SSG dimension to the fact tables. It specifies the SSG of the Schedule State.
• WFM_STATE_TYPE_KEY—The surrogate key used to join the WFM_STATE_TYPE dimension. It specifies the type of the Schedule State.
• WFM_STATE_ID—The ID of Schedule State corresponding to the type of Schedule State. The ID is unique within the context of Schedule State type.
• WFM_STATE_NAME—The name of the Schedule State.
• WFM_STATE_SHORT_NAME—The short name of the Schedule State.
• TENANT_KEY—The surrogate key used to join the TENANT dimension to the fact tables.
• CREATE_AUDIT_KEY—The surrogate key used to join to the CTL_AUDIT_LOG control table. The key specifies the lineage for data creation. This value is useful for aggregation, Enterprise Application Integration (EAI), and ETL tools (that is, applications that need to identify newly added data).
• UPDATE_AUDIT_KEY—The surrogate key used to join to the CTL_AUDIT_LOG control table. The key specifies the lineage for data update. This value is useful for aggregation, Enterprise Application Integration (EAI), and ETL tools (that is, applications that need to identify recently modified data).
• PURGE_FLAG—This field is reserved (used internally by the purging script to mark records for purging).

WFM_PERF_ITEM

This table contains performance items (statistics) and descriptions.

Column	Data type	P	M	F	DV
WFM_PERF_ITEM_KEY	int	✔	✔
WFM_PERF_ITEM_CODE	varchar(64)		✔
WFM_PERF_ITEM_DESCRIPTION	varchar(256)		✔

Description of Columns

• WFM_PERF_ITEM_KEY—The Performance item ID.
• WFM_PERF_ITEM_CODE—The code of Performance item (statistic). The table below contains WFM Performance statistics.
• WFM_PERF_ITEM_DESCRIPTION—The description of the Performance item.

Performance Statistics

ID	Code	Description
12	FRC_CALC_STAFFING	Total Calculated Staffing Difference (between Optimal number of agents for Forecast workload and Calculated Staffing)
16	FRC_REQ_STAFFING	Total Required Staffing
38	FRC_CALC_SERVICE_PCT	Weighted average of (Achieved) Calculated Service Level % (weighted on Forecast Interaction Volume)
15	FRC_REQ_SERVICE_PCT	Weighted average of (Achieved) Required Service Level % (weighted on Forecast Interaction Volume) for Activity of type Deferred
40	FRC_CALC_ASA	Weighted average of (Achieved) Calculated Average Speed of Answer (weighted on Forecast Interaction Volume)
14	FRC_REQ_ASA	Weighted average of Required Average Speed of Answer (weighted on Forecast Interaction Volume)
39	FRC_CALC_ABANDONED_IV_PCT	Weighted average of (Achieved) Calculated Abandoned Interaction Volume % (weighted on Forecast Interaction Volume)
18	FRC_REQ_ABANDONED_IV_PCT	Weighted average of Required Abandoned Interaction Volume % (weighted on Forecast Interaction Volume)
41	FRC_CALC_MAX_OCCUPANCY_PCT	Weighted average of (Achieved) Calculated Maximum Occupancy % (weighted on Forecast Interaction Volume)
77	FRC_REQ_MAX_OCCUPANCY_PCT	Weighted average of Required Maximum Occupancy % (weighted on Forecast Interaction Volume)
10	FRC_IV	Total of Forecast Interaction Volume
50	FRC_CALC_FTE	Total of Calculated Full-time Equivalent
51	FRC_REQ_FTE	Total of Required Full-time Equivalent
56	FRC_CALC_MAN_HOURS	Total of Calculated Man Hours
57	FRC_REQ_MAN_HOURS	Total of Required Man Hours
21	SCH_COVERAGE	Total of Scheduled Coverage
24	SCH_SERVICE_PCT	Weighted average of Scheduled Service Level % (weighted on Forecast Interaction Volume)
19	SCH_ASA	Weighted average of Scheduled Average Speed of Answer (weighted on Forecast Interaction Volume)
22	SCH_ABANDONED_IV_PCT	Weighted average of Scheduled Abandoned Interaction Volume % (weighted on Forecast Interaction Volume)
23	SCH_MAX_OCCUPANCY_PCT	Weighted average of Scheduled Maximum Occupancy % (weighted on Forecast Interaction Volume)
49	SCH_FTE	Total of Scheduled Full-time Equivalent
55	SCH_MAN_HOURS	Total of Scheduled Man Hours
6	ACT_STAFFING	Total of Actual Staffing Difference (between Optimal number of agents for Actual workload and Scheduled Coverage)
59	ACT_COVERAGE	Total of Actual Coverage (agent minutes divided by timestep)
3	ACT_SERVICE_PCT	Weighted average of Actual Service Level % (weighted on Actual Distributed Interaction Volume) for Activity of type Deferred
5	ACT_ASA	Weighted average of Actual Average Speed of Answer (weighted on Actual Interaction Volume)
4	ACT_ABANDONED_IV_PCT	Total of Actual Abandoned Interaction Volume %
1	ACT_IV	Total of Actual Interaction Volume
62	ACT_ABANDONED_IV	Total of Actual Abandoned Interaction Volume %
8	ACT_DISTRIBUTED_IV	Total of Actual Distributed Interaction Volume
9	ACT_HANDLED_IV	Total of Actual Handled Interaction Volume
60	ACT_FTE	Total of Actual Full-time Equivalent
61	ACT_MAN_HOURS	Total of Actual Man Hours
20	SCH_HEADCOUNT	Total of Scheduled Headcount
2	ACT_AHT	Weighted average of Actual Handle Time (weighted on Actual Handled Interaction Volume)
78	ACT_SIMPLE_AHT	Simple average of Actual Handle Time
11	FRC_AHT	Weighted average of Forecast Handle Time (weighted on Forecast Interaction Volume)
58	FRC_SIMPLE_AHT	Simple average of Forecast Average Handle Time
70	SCH_AHT	Weighted average of Scheduled Average Handle Time (weighted on Forecast Interaction Volume)
69	SCH_IV	Total of Scheduled Interaction Volume