Fact Tables

This topic describes the Fact tables in the Workforce Management (WFM) ETL Database schema. To view the details in each table click the table name in the first column. For example, WFM_ADH_AGENT_DAY or WFM_ADH_AGENT_TIMESTEP.

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

This table contains a 24-hour day aggregate of agent adherence information.

Column	Data type	P	M	F	DV
WFM_ADH_AGENT_DAY_KEY	Numeric(19)	X	X
WFM_AGENT_KEY	int		X	X
WFM_SITE_KEY	int		X	X
WFM_TEAM_KEY	int			X
WFM_DATE	date		X
WFM_NON_ADHERENCE_DURATION	int		X
WFM_OUT_SCH_NON_ADH_DURATION	int		X
WFM_SCHEDULE_DURATION	int		X
WFM_ACTUAL_WORK_DURATION	int		X
WFM_ADHERENCE_PERC	float		X
WFM_CONFORMANCE_PERC	float		X
WFM_TIMESTAMP	numeric(19)		X
DATE_TIME_DAY_KEY	int		X	X
TENANT_KEY	int			X
CREATE_AUDIT_KEY	numeric(19)			X
UPDATE_AUDIT_KEY	numeric(19)			X
PURGE_FLAG	int

Description of Columns

• WFM_ADH_AGENT_DAY_KEY—The primary key for this table.
• WFM_AGENT_KEY—The surrogate key used to join the WFM_AGENT dimension to the fact tables. It specifies the Agent of the Agent Adherence Day.
• WFM_SITE_KEY—The surrogate key used to join the WFM_SITE dimension to the fact tables. It specifies the Site of the Agent Adherence Day.
• WFM_TEAM_KEY—The surrogate key used to join the WFM_TEAM dimension to the fact tables. It specifies the historical Team of the Agent at the time of adherence date specified in WM_DATE column. It is NULL if the Agent was not under any team at that time.
• WFM_DATE—The date of the Agent Adherence Day in the Agent's Site time zone.
• WFM_NON_ADHERENCE_DURATION—The Agent’s total non-adherence time in seconds for the day.
• WFM_OUT_SCH_NON_ADH_DURATION—The Agent’s total out of schedule non-adherence time in seconds for the day.
• WFM_SCHEDULE_DURATION—The Agent’s total schedule time plus Agent’s total out of schedule non-adherence time for the day in seconds.
• WFM_ACTUAL_WORK_DURATION—The Agent’s total work time (logged in time) in seconds for the day.
• WFM_ADHERENCE_PERC—The Agent’s adherence percentage for the day. The adherence percentage is calculated using the following formula:
  

  WFM_ADHERENCE_PERC = 100.0 - (100.0 * WFM_NON_ADHERENCE_DURATION)
  / WFM_SCHEDULE_DURATION

• WFM_CONFORMANCE_PERC—The Agent’s conformance percentage for the day, calculated by using the following formula:
  

  WFM_CONFORMANCE_PERC = (100.0 * WFM_ACTUAL_WORK_DURATION)
  / WFM_SCHEDULE_DURATION

• WFM_TIMESTAMP—An internal timestamp value.
• DATE_TIME_DAY_KEY—Identifies the start of a day interval in which the fact began and is equal to the UTC-equivalent time value, at which the day interval started. The value is the number of seconds that have elapsed since midnight on January 1, 1970, not counting leap seconds (also known as UNIX time). Use this value as a key to join the Fact tables to any configured DATE_TIME dimension to group the facts that are related to the same interval and/or convert day interval start to an appropriate time zone.
• 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 a 24-hour day aggregate of agent adherence information.

Column	Data type	P	M	F	DV
WFM_ADH_AGENT_DAY_KEY	numeric(19)	X	X	X
WFM_TIME_STEP	datetime	X	X
WFM_NON_ADHERENCE_DURATION	int		X
WFM_OUT_SCH_NON_ADH_DURATION	int		X
WFM_SCHEDULE_DURATION	int		X
WFM_ACTUAL_WORK_DURATION	int		X
DATE_TIME_KEY	int		X	X
TENANT_KEY	int			X
CREATE_AUDIT_KEY	numeric(19)			X
UPDATE_AUDIT_KEY	numeric(19)			X
PURGE_FLAG	int

Description of Columns

• WFM_ADH_AGENT_DAY_KEY—The surrogate key used to join parent WFM_ADH_AGENT_DAY record containing the Agent, Site and Team, as well as the corresponding calendar day information.
• WFM_TIME_STEP—The start date/time of 15-minute interval in the Agent's Site time zone.
• WFM_NON_ADHERENCE_DURATION—The Agent's total non-adherence time in seconds for the 15-minute interval.
• WFM_OUT_SCH_NON_ADH_DURATION—The Agent's total out of schedule non-adherence time in seconds for the 15-minute interval.
• WFM_SCHEDULE_DURATION—The Agent's total schedule time in seconds for the 15-minute interval.
• WFM_ACTUAL_WORK_DURATION—The Agent's total work time (logged in time) in seconds for the 15-minute interval.
• DATE_TIME_KEY—Identifies the start of a 15-minute interval, in which the fact began and is equal to the UTC-equivalent time, at which the interval started. The value is the number of seconds that have elapsed since midnight on January 1, 1970, not counting leap seconds (also known as UNIX time). Use this value as a key to join the Fact tables to any configured DATE_TIME dimension to group the facts that are related to the same interval and/or convert interval start to an appropriate time zone.
• 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 the Agent’s shift-day schedule information. The shift-day starts anywhere within the corresponding 24-hour calendar day, but it can end on the next calendar day, if the scheduled shift is an overnight shift.

Column	Data type	P	M	F	DV
WFM_SCH_AGENT_DAY_KEY	numeric(19)	X	X
WFM_AGENT_KEY	int		X	X
WFM_SITE_KEY	int		X	X
WFM_TEAM_KEY	int			X
WFM_DATE	date		X
WFM_DAY_START	datetime		X
WFM_DAY_END	datetime		X
WFM_STATE_KEY	numeric(19)		X	X
WFM_FULL_DAY	int		X
WFM_SCHEDULE_DURATION	float		X
WFM_WORK_DURATION	float		X
WFM_PAID_DURATION	float		X
WFM_OVERTIME_DURATION	float		X
WFM_TIMESTAMP	numeric(19)		X
START_DATE_TIME_KEY	int		X	X
END_DATE_TIME_KEY	int		X	X
START_TS	int		X
END_TS	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_SCH_AGENT_DAY_KEY—The primary key for this table.
• WFM_AGENT_KEY—The surrogate key used to join the WFM_AGENT dimension to the fact tables. It specifies the Agent of the schedule day.
• WFM_SITE_KEY—The surrogate key used to join the WFM_SITE dimension to the fact tables. It specifies the Site of the schedule day.
• WFM_TEAM_KEY—The surrogate key used to join the WFM_TEAM dimension to the fact tables. It specifies the historical Team of the Agent at the time of schedule date specified in WM_DATE column. It is NULL if the Agent was not in any team at that time.
• WFM_DATE—The date of Agent Adherence Day in the Agent's Site time zone.
• WFM_DAY_START—The start date/time of schedule day in the Agent's Site time zone. It is a start time of the first (the earliest) schedule state within the schedule day.
• WFM_DAY_END—The end date/time of schedule day in the Agent's Site time zone. It is a end time of the last (the latest) schedule state within the schedule day.
• WFM_STATE_KEY—The surrogate key used to join the WFM_STATE dimension to the Fact tables. It specifies the full-day schedule state corresponding to the schedule day.
• WFM_FULL_DAY—Indicates whether the schedule is full-day or not: 0 = No, 1 = Yes. The full-day schedule day is one that has no specific start/end time defined (for example, Day-Off).
• WFM_SCHEDULE_DURATION—The total schedule time, in minutes, for the schedule day.
• WFM_WORK_DURATION—The total scheduled work on activities time, in minutes, for the schedule day.
• WFM_PAID_DURATION—The total scheduled paid time, in minutes, for the schedule day.
• WFM_OVERTIME_DURATION—The total scheduled overtime, in minutes, for the schedule day.
• WFM_TIMESTAMP—An internal timestamp value.
• START_DATE_TIME_KEY—Identifies the start of a 15-minute interval, in which the fact began. Use this value as a key to join the Fact tables to any configured DATE_TIME dimension to group the facts that are related to the same interval and/or convert the START_TS timestamp to an appropriate time zone.
• END_DATE_TIME_KEY—Identifies the start of a 15-minute interval, in which the fact ended. Use this value as a key to join the Fact tables to any configured DATE_TIME dimension to group the facts that are related to the same interval and/or convert the END_TS timestamp to an appropriate time zone.
• START_TS—The date and time, at which the fact began, as a Coordinated Universal Time (UTC) value—the number of seconds that have elapsed since midnight on January 1, 1970, not counting leap seconds (also known as UNIX time).
• END_TS—The date and time, at which the fact ended, as a Coordinated Universal Time (UTC) value—the number of seconds that have elapsed since midnight on January 1, 1970, not counting leap seconds (also known as UNIX time).
• 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 a 15-minute interval aggregate of agent’s schedule information.

Column	Data type	P	M	F	DV
WFM_SCH_AGENT_DAY_KEY	numeric(19)	X	X	X
WFM_TIME_STEP	datetime	X	X
WFM_SCHEDULE_DURATION	float		X
WFM_WORK_DURATION	float		X
WFM_PAID_DURATION	float		X
WFM_OVERTIME_DURATION	float		X
DATE_TIME_KEY	int		X	X
TENANT_KEY	int			X
CREATE_AUDIT_KEY	numeric(19)			X
UPDATE_AUDIT_KEY	numeric(19)			X
PURGE_FLAG	int

Description of Columns

• WFM_SCH_AGENT_DAY_KEY—The surrogate key used to join parent WFM_SCH_AGENT_DAY record containing the Agent, Site and Team, as well as corresponding schedule day information.
• WFM_TIME_STEP—The start date/time of the 15-minute interval in the Agent's Site time zone.
• WFM_SCHEDULE_DURATION—The total schedule time, in minutes, for the 15-minute interval.
• WFM_WORK_DURATION—The total scheduled work on activities time, in minutes, for the 15-minute interval.
• WFM_PAID_DURATION—The total scheduled paid time, in minutes, for the 15-minute interval.
• WFM_OVERTIME_DURATION—The total scheduled overtime, in minutes, for the 15-minute interval.
• DATE_TIME_KEY—Identifies the start of a 15-minute interval, in which the fact began and is equal to the UTC-equivalent time, at which the interval started. The value is the number of seconds that have elapsed since midnight on January 1, 1970, not counting leap seconds (also known as UNIX time). Use this value as a key to join the Fact tables to any configured DATE_TIME dimension to group the facts that are related to the same interval and/or convert interval start to an appropriate time zone.
• 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’s schedule state information.

Column	Data type	P	M	F	DV
WFM_SCH_AGENT_DAY_KEY	numeric(19)	X	X	X
WFM_STATE_KEY	numeric(19)	X	X	X
WFM_STATE_START	datetime	X	X
WFM_STATE_END	datetime		X
WFM_STATE_DURATION	float		X
WFM_PAID_DURATION	float		X
WFM_FULL_DAY	int		X
START_DATE_TIME_KEY	int		X	X
END_DATE_TIME_KEY	int		X	X
START_TS	int		X
END_TS	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_SCH_AGENT_DAY_KEY—The surrogate key used to join the parent WFM_SCH_AGENT_DAY record containing the Agent, Site and Team, as well as corresponding schedule day information.
• WFM_STATE_KEY—The surrogate key used to join the WFM_STATE dimension to the Fact tables. It specifies the schedule state of the agent schedule state.
• WFM_STATE_START—The start date/time of the Agent schedule state in the Agent's Site time zone.
• WFM_STATE_END—The end date/time of the Agent schedule state in the Agent's Site time zone.
• WFM_FULL_DAY—Indicates whether the Agent schedule state is full-day or not: 0 = No, 1 = Yes. The full-day schedule state is one that has no specific start/end time defined (for example, Day-Off).
• WFM_STATE_DURATION—The schedule state duration in minutes.
• WFM_PAID_DURATION—The schedule state paid duration in minutes.
• START_DATE_TIME_KEY—Identifies the start of a 15-minute interval, in which the fact began. Use this value as a key to join the Fact tables to any configured DATE_TIME dimension to group the facts that are related to the same interval and/or convert the START_TS timestamp to an appropriate time zone.
• END_DATE_TIME_KEY—Identifies the start of a 15-minute interval, in which the fact ended. Use this value as a key to join the Fact tables to any configured DATE_TIME dimension to group the facts that are related to the same interval and/or convert the END_TS timestamp to an appropriate time zone.
• START_TS—The date and time, at which the fact began, as a Coordinated Universal Time (UTC) value—the number of seconds that have elapsed since midnight on January 1, 1970, not counting leap seconds (also known as UNIX time).
• END_TS—The date and time, at which the fact ended, as a Coordinated Universal Time (UTC) value—the number of seconds that have elapsed since midnight on January 1, 1970, not counting leap seconds (also known as UNIX time).
• 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 a 15-minute interval aggregate of schedule state duration information.

Column	Data type	P	M	F	DV
WFM_SCH_AGENT_DAY_KEY	numeric(19)	X	X	X
WFM_STATE_KEY	numeric(19)	X	X	X
WFM_TIME_STEP	datetime	X	X
WFM_STATE_DURATION	float		X
DATE_TIME_KEY	int		X	X
TENANT_KEY	int			X
CREATE_AUDIT_KEY	numeric(19)			X
UPDATE_AUDIT_KEY	numeric(19)			X
PURGE_FLAG	int

Description of Columns

• WFM_SCH_AGENT_DAY_KEY—The surrogate key used to join the parent WFM_SCH_AGENT_DAY record containing Agent, Site and Team, as well as corresponding schedule day information.
• WFM_STATE_KEY—The surrogate key used to join the WFM_STATE dimension to the Fact tables. It specifies the schedule state of the 15-minute interval aggregate.
• WFM_TIME_STEP—The start date/time of the 15-minute interval in the Agent's Site time zone.
• WFM_STATE_DURATION—The total schedule state time in minutes for the 15-minute interval.
• DATE_TIME_KEY—Identifies the start of a 15-minute interval, in which the fact began and is equal to the UTC-equivalent time, at which the interval started. The value is the number of seconds that have elapsed since midnight on January 1, 1970, not counting leap seconds (also known as UNIX time). Use this value as a key to join the Fact tables to any configured DATE_TIME dimension to group the facts that are related to the same interval and/or convert interval start to an appropriate time zone.
• 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 a 24-hour calendar day aggregate of the activity and/or the site performance statistics.

Column	Data type	P	M	F	DV
WFM_PERF_ITEM_DAY_KEY	numeric(19)	X	X
WFM_ACTIVITY_KEY	int			X
WFM_SITE_KEY	int			X
WFM_DATE	date		X
WFM_PERF_ITEM_KEY	int		X	X
WFM_PERF_ITEM_VALUE	float		X
WFM_TIMESTAMP	numeric(19)		X
DATE_TIME_DAY_KEY	int		X	X
TENANT_KEY	int			X
CREATE_AUDIT_KEY	numeric(19)			X
UPDATE_AUDIT_KEY	numeric(19)			X
PURGE_FLAG	int

Description of Columns

• WFM_PERF_ITEM_DAY_KEY—The primary key for this table
• WFM_ACTIVITY_KEY—The surrogate key used to join the WFM_ACTIVITY dimension to the Fact tables. It specifies the Activity (Single-Site or Multi-Site or Activity Group) of the performance statistic aggregate for the day. It is NULL for the Site statistic aggregate.
• WFM_SITE_KEY—The surrogate key used to join the WFM_SITE dimension to the fact tables. It specifies the Site of the performance statistic aggregate for the day. It is NULL for the Activity statistic aggregate.
• WFM_DATE—The date of performance statistic day aggregate in time zone of the Activity or the Site. Single-Site Activity uses the Site time zone, while Multi-Site Activity and Activity Group use the Business Unit time zone.
• WFM_PERF_ITEM_KEY—The surrogate key used to join the WFM_PERF_ITEM dimension to the Fact tables. It specifies the performance statistic type of the day aggregate. See the list of available statistics in the description of the WFM_PERF_ITEM dimension.
• WFM_PERF_ITEM_VALUE—The value of the Activity or Site performance statistic aggregate for the day.
• WFM_TIMESTAMP—An internal timestamp value.
• DATE_TIME_DAY_KEY—Identifies the start of a day interval, in which the fact began and is equal to the UTC-equivalent time value, at which the day interval started. The value is the number of seconds that have elapsed since midnight on January 1, 1970, not counting leap seconds (also known as UNIX time). Use this value as a key to join the Fact tables to any configured DATE_TIME dimension to group the facts that are related to the same interval and/or convert day interval start to an appropriate time zone.
• 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 a 15-minute interval aggregate of the activity and/or the site performance statistics.

Column	Data type	P	M	F	DV
WFM_PERF_ITEM_DAY_KEY	numeric(19)	X	X	X
WFM_TIME_STEP	datetime	X	X
WFM_PERF_ITEM_VALUE	float		X
DATE_TIME_KEY	int		X	X
TENANT_KEY	int			X
CREATE_AUDIT_KEY	numeric(19)			X
UPDATE_AUDIT_KEY	numeric(19)			X
PURGE_FLAG	int

Description of Columns

• WFM_PERF_ITEM_DAY_KEY—The surrogate key used to join the parent WFM_PERF_ITEM_DAY record containing the Activity or Site Performance Statistic, as well as corresponding calendar day information.
• WFM_TIME_STEP—The start date/time of 15-minute interval in time zone of the Activity or Site. Single-Site Activity uses the Site time zone, while Multi-Site Activity and Activity Group use the Business Unit time zone.
• WFM_PERF_ITEM_VALUE—The value of the Activity or the Site performance statistic aggregate for the 15-minute interval.
• DATE_TIME_KEY—Identifies the start of a 15-minute interval, in which the fact began and is equal to the UTC-equivalent time, at which the interval started. The value is the number of seconds that have elapsed since midnight on January 1, 1970, not counting leap seconds (also known as UNIX time). Use this value as a key to join the Fact tables to any configured DATE_TIME dimension to group the facts that are related to the same interval and/or convert interval start to an appropriate time zone.
• 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.