Dimension tables

From Genesys Documentation
Jump to: navigation, search
This topic is part of the manual Workforce Management ETL Database Reference for version Current of Workforce Management.

Use these tables to define descriptive information for WFM objects in the WFM ETL database schema.

Related documentation:

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.

In certain configurations, 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. However, if you are using WFM's latest Forecast UI, it requires IANA time zones.

Tip
Some configuration is necessary to support this feature. Contact your Genesys Professional Services representative to implement this feature

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. 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 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 updates. 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
Retrieved from "https://all.docs.genesys.com/PEC-WFM/Current/ETLRef/DimTbls (2024-03-19 09:17:55)"
Comments or questions about this documentation? Contact us for support!