Jump to: navigation, search

Dimension tables

This topic describes the Dimension tables in the Workforce Management (WFM) ETL Database schema.

For a description of the abbreviations used in these tables, see Abbreviations for ETL Database Terms.

This table contains business unit descriptive information.

Column Data type P M F DV
WFM_BU_KEY int X X
WFM_BU_NAME varchar(255) X
WFM_TIMESTAMP numeric(19) X
TIME_ZONE_KEY int X
ACTIVE_FLAG int X
TENANT_KEY int X
CREATE_AUDIT_KEY numeric(19) X
UPDATE_AUDIT_KEY numeric(19) X
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.
  • ACTIVE_FLAG—Indicates whether the Business Unit is currently active: 0 = No, 1 = Yes.
  • 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.

This table contains site descriptive information.

Column Data type P M F DV
WFM_SITE_KEY int X
WFM_BU_KEY int X X
WFM_SITE_NAME varchar(255) X
WFM_TIMESTAMP numeric(19) X
SWITCH_KEY int X
TIME_ZONE_KEY int X
ACTIVE_FLAG int X
TENANT_KEY int X
CREATE_AUDIT_KEY numeric(19) X
UPDATE_AUDIT_KEY numeric(19) X
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.
  • ACTIVE_FLAG—Indicates whether the Site is currently active: 0 = No, 1 = Yes.
  • 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.

This table contains team descriptive information.

Column Data type P M F DV
WFM_TEAM_KEY int X X
WFM_TEAM_NAME varchar(255) X
WFM_SITE_KEY int X X
WFM_TIMESTAMP numeric(19) X
ACTIVE_FLAG int X
TENANT_KEY int X
CREATE_AUDIT_KEY numeric(19) X
UPDATE_AUDIT_KEY numeric(19) X
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: 0 = No, 1 = Yes.
  • 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.

This table contains agent descriptive information.

Column Data type P M F DV
WFM_AGENT_KEY int X X
WFM_BU_KEY int X
WFM_SITE_KEY int X
WFM_TEAM_KEY int X
EMPLOYEE_ID varchar(64) X
FIRST_NAME varchar(64) X
LAST_NAME varchar(64) X
HIRE_DATE date X
TERMINATION_DATE date
WFM_TIMESTAMP numeric(19) X
AGENT_KEY int X
ACTIVE_FLAG int X
TENANT_KEY int X
CREATE_AUDIT_KEY numeric(19) X
UPDATE_AUDIT_KEY numeric(19) X
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.
  • 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: 0 = No, 1 = Yes.
  • 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.

This table contains activity types and descriptions.

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

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'

This table contains activity descriptive information.

Column Data type P M F DV
WFM_ACTIVITY_KEY int X X
WFM_BU_KEY int X X
WFM_SITE_KEY int X
WFM_MSA_KEY int X
WFM_ACTIVITY_NAME varchar(255) X
WFM_ACTIVITY_SHORT_NAME varchar(6) X
WFM_ACTIVITY_TYPE_KEY int X X
WFM_TIMESTAMP numeric(19) X
ACTIVE_FLAG int X
TENANT_KEY int X
CREATE_AUDIT_KEY numeric(19) X
UPDATE_AUDIT_KEY numeric(19) X
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: 0 = No, 1 = Yes.
  • 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.

This table contains schedule state group types and descriptions.

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

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'

This table contains schedule state group descriptive information.

Column Data type P M F DV
WFM_SSG_KEY int X X
WFM_SITE_KEY int X X
WFM_SSG_NAME varchar(255) X
WFM_SSG_TYPE_KEY int X X
WFM_SSG_WEIGHT int X
WFM_TIMESTAMP numeric(19) X
ACTIVE_FLAG int X
TENANT_KEY int X
CREATE_AUDIT_KEY numeric(19) X
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: 0 = No, 1 = Yes.
  • 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.

This table contains schedule state types and descriptions.

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

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'


This table contains schedule state descriptive information.

Column Data type P M F DV
WFM_STATE_KEY int X X
WFM_SITE_KEY int X X
WFM_SSG_KEY int X
WFM_STATE_TYPE_KEY int X X
WFM_STATE_ID int X
WFM_STATE_NAME varchar(255) X
WFM_STATE_SHORT_NAME varchar(6) X
TENANT_KEY int X
CREATE_AUDIT_KEY numeric (19) X
UPDATE_AUDIT_KEY numeric(19) X
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.

This table contains performance items (statistics) and descriptions.

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

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

Feedback

Comment on this article:

blog comments powered by Disqus
This page was last modified on August 10, 2017, at 05:39.