Jump to: navigation, search

Fact tables

This topic describes the Fact 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 a 24-hour day aggregate of agent adherence information.

Column Data type P M F DV
WFM_ADH_AGENT_DAY_KEY Numeric(19)
WFM_AGENT_KEY int
WFM_SITE_KEY int
WFM_TEAM_KEY int
WFM_DATE date
WFM_NON_ADHERENCE_DURATION int
WFM_OUT_SCH_NON_ADH_DURATION int
WFM_SCHEDULE_DURATION int
WFM_ACTUAL_WORK_DURATION int
WFM_ADHERENCE_PERC float
WFM_CONFORMANCE_PERC float
WFM_TIMESTAMP numeric(19)
DATE_TIME_DAY_KEY int
TENANT_KEY int
CREATE_AUDIT_KEY numeric(19)
UPDATE_AUDIT_KEY numeric(19)
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)
WFM_TIME_STEP datetime
WFM_NON_ADHERENCE_DURATION int
WFM_OUT_SCH_NON_ADH_DURATION int
WFM_SCHEDULE_DURATION int
WFM_ACTUAL_WORK_DURATION int
DATE_TIME_KEY int
TENANT_KEY int
CREATE_AUDIT_KEY numeric(19)
UPDATE_AUDIT_KEY numeric(19)
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)
WFM_AGENT_KEY int
WFM_SITE_KEY int
WFM_TEAM_KEY int
WFM_DATE date
WFM_DAY_START datetime
WFM_DAY_END datetime
WFM_STATE_KEY numeric(19)
WFM_FULL_DAY int
WFM_SCHEDULE_DURATION float
WFM_WORK_DURATION float
WFM_PAID_DURATION float
WFM_OVERTIME_DURATION float
WFM_TIMESTAMP numeric(19)
START_DATE_TIME_KEY int
END_DATE_TIME_KEY int
START_TS int
END_TS int
TENANT_KEY int
CREATE_AUDIT_KEY numeric(19)
UPDATE_AUDIT_KEY numeric(19)
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)
WFM_TIME_STEP datetime
WFM_SCHEDULE_DURATION float
WFM_WORK_DURATION float
WFM_PAID_DURATION float
WFM_OVERTIME_DURATION float
DATE_TIME_KEY int
TENANT_KEY int
CREATE_AUDIT_KEY numeric(19)
UPDATE_AUDIT_KEY numeric(19)
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)
WFM_STATE_KEY numeric(19)
WFM_STATE_START datetime
WFM_STATE_END datetime
WFM_STATE_DURATION float
WFM_PAID_DURATION float
WFM_FULL_DAY int
START_DATE_TIME_KEY int
END_DATE_TIME_KEY int
START_TS int
END_TS int
TENANT_KEY int
CREATE_AUDIT_KEY numeric(19)
UPDATE_AUDIT_KEY numeric(19)
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)
WFM_STATE_KEY numeric(19)
WFM_TIME_STEP datetime
WFM_STATE_DURATION float
DATE_TIME_KEY int
TENANT_KEY int
CREATE_AUDIT_KEY numeric(19)
UPDATE_AUDIT_KEY numeric(19)
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)
WFM_ACTIVITY_KEY int
WFM_SITE_KEY int
WFM_DATE date
WFM_PERF_ITEM_KEY int
WFM_PERF_ITEM_VALUE float
WFM_TIMESTAMP numeric(19)
DATE_TIME_DAY_KEY int
TENANT_KEY int
CREATE_AUDIT_KEY numeric(19)
UPDATE_AUDIT_KEY numeric(19)
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)
WFM_TIME_STEP datetime
WFM_PERF_ITEM_VALUE float
DATE_TIME_KEY int
TENANT_KEY int
CREATE_AUDIT_KEY numeric(19)
UPDATE_AUDIT_KEY numeric(19)
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.

Feedback

Comment on this article:

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