Jump to: navigation, search

TASK_FACT Tables

A task describes a definite piece of work from the perspective of the customer. Each iWD task record results in a single fact being written to the iWD Data Mart. You can access a task fact through the following database objects:

  • I_TASK_FACT—Intraday data table
  • H_TASK_FACT—Historical data table
  • TASK_FACT—Blended view of historical and intraday data

The dimensions that support the iWD task fact tables and view are shown in the TASK_FACT star schema below. The fields, data types, and descriptions of each column are provided in the table that follows.

TASK_FACT Star Schema
The I_/H_TASK_FACT Tables
Field Data Type Description
INTERACTION_ID nvarchar(64) The Interaction ID, unique within a single Interaction Server database. Together with SOLUTION_KEY, this field serves as the primary key of this table.
SOLUTION_KEY int Key to the SOLUTION dimension, describing the solution instance of the task (as configured in iWD GAX Plug-in)—for example, Production versus Test. A solution is assigned as soon as a task is created in the Interaction Server database. A tenant can have more than one solution instance. Together with INTERACTION_ID, this field serves as the primary key of this table.
LAST_TASK_EVENT_ID bigint Unique identifier for the last event that is associated with the task.
CAPTURE_ID nvarchar(64) ID of the task capture, as stored or referenced in the source system—for example, work item ID. This field is a core task attribute.
TENANT_KEY int Key to the TENANT dimension, describing the tenant of the task (as configured in iWD GAX Plug-in). A tenant is assigned as soon as a task is created in the Interaction Server database.
DEPARTMENT_KEY int Key to the DEPARTMENT dimension, identifying the department that is associated with the task.
PROCESS_KEY int Key to the PROCESS dimension, identifying the parent iWD business process of the task.
CAPTURE_POINT_KEY int Key to the CAPTURE_POINT dimension, identifying the capture point that captured the task.
CURRENT_QUEUE_KEY int Key to the QUEUE dimension, identifying the queue in which the task resides and queue type.
CURRENT_QUEUE_TARGET_KEY int Key to the QUEUE_TARGET dimension, identifying the agent, agent group, place, or place group to which the task was assigned.
SOURCE_FIRST_CREATED_DATE_KEY int Key to the EVENT_DATE dimension. This field is reserved for the DTM (Driver Test Manager) from the first system that captured the task.

Note: iWD provides for task-flow scenarios that involve two source DTMs, where two systems were involved in the origination of a task&mbsp;for example, fax server and workflow.

SOURCE_FIRST_CREATED_TIME_KEY int Key to the EVENT_TIME dimension, identifying the time at which the first source system captured the task.
SOURCE_CREATED_DATE_KEY int Key to the EVENT_DATE dimension, identifying the date on which the second source system captured the task. The second source system is the DTM that submitted the task to iWD.
SOURCE_CREATED_TIME_KEY int Key to the EVENT_TIME dimension, identifying the time at which the second source system captured the task.
SOURCE_DUE_DATE_KEY int Key to the EVENT_DATE dimension, identifying the date on which the task is due in source system.
SOURCE_DUE_TIME_KEY int Key to the EVENT_TIME dimension, identifying the time at which the task is due in source system.
CREATED_DATE_KEY int Key to the EVENT_DATE dimension, describing the date on which the iWD task was created.

Additional created date and time stamps are provided for in the extended attributes to report not only on iWD capture date and time, but also on the source system—for example, workflow capture date and time. Refer to the SOURCE_CREATED and SOURCE_FIRST_CREATED date and time keys.

CREATED_TIME_KEY int Key to the EVENT_TIME dimension, describing the time at which the iWD task was created.
ACTIVATION_DATE_KEY int Key to the EVENT_DATE dimension, describing the iWD task activation date. This is the date on which the task becomes active; before this date, the task remains in the iWD_Captured queue and will not be prioritized or delivered to agents.
ACTIVATION_TIME_KEY int Key to the EVENT_TIME dimension, describing the activation time for the task. Activation time is the moment at which the task becomes active. Before this time, the task remains in the iWD_Captured queue and will not be prioritized or delivered to agents.
DUE_DATE_KEY int Key to the EVENT_DATE dimension, describing the date on which the task is due, as set by either iWD rules or the source system.
DUE_TIME_KEY int Key to the EVENT_TIME dimension, describing the time at which the task is due, as set by either iWD rules or the source system.
COMPLETED_DATE_KEY int Key to the EVENT_DATE dimension, describing the task completion date.
COMPLETED_TIME_KEY int Key to the EVENT_TIME dimension, describing the task completion time.
STOPPED_DATE_KEY int Key to the EVENT_DATE dimension, describing the date on which the task was stopped.
ASSIGNED_DATE_KEY int Key to the EVENT_DATE dimension, describing the date on which the task was assigned to an agent
ASSIGNED_TIME_KEY int Key to the EVENT_TIME dimension, describing the time at which the task was assigned to an agent.
MEDIA_CHANNEL_KEY int Key to the MEDIA_CHANNEL dimension, describing the channel through which the task was received—for example, fax. This value can be set in iWD rules or by the source system that submitted the task.
CATEGORY_KEY int Key to CATEGORY dimension, further describing the task, such as a follow-up.
BUSINESS_VALUE_KEY int Key to the BUSINESS_VALUE dimension. Business value is assigned by using iWD rules during the classification phase of the task.
CURRENT_PRIORITY_KEY int Key to the PRIORITY dimension. As with business value, initial priorities should be assigned during classification. The priority of a task can change over time. For example, as the task gets closer to its due date, rules can be configured to reprioritize the task proactively. The value that is stored in this field represents the current priority of the task. Historical priority values are stored in the H_TASK_EVENT_FACT table.
CURRENT_STATUS_KEY int Key to the STATUS dimension, describing the current status of the task
LAST_ASSIGNED_AGENT_KEY int Key to the AGENT dimension, identifying the last agent who was assigned to the task.
LAST_RESULT_CODE_KEY int Key to the RESULT_CODE dimension. This value often represents the wrap code from a soft phone, the result code from a routing strategy, or the result code from the source system.
CUSTOMER_KEY int Key to the CUSTOMER dimension. Often used as the customer ID from the source system. This ID can be utilized to retrieve further customer details from a Customer Relationship Management (CRM) data warehouse or other customer data repository.
CUSTOMER_SEGMENT_KEY int Key to the CUSTOMER_SEGMENT dimension, describing the customer to whom the task is associated. The customer segment is received from the source system as an extended iWD task attribute—for example, gold, silver, or bronze.
PRODUCT_KEY int Key to the PRODUCT dimension, describing the product to which the task is related—for example, a product name or product type, such as a loan or Internet Digital Subscriber Line (DSL). The product can be further defined by using product subtypes, such as residential loan or home DSL.
SOURCE_TENANT_KEY int Key to the SOURCE_TENANT dimension, describing the tenant who submitted the task. It can be important in a multi-tenant or service-bureau environment.
SOURCE_PROCESS_KEY int Key to the SOURCE_PROCESS dimension. Source process includes the type and subtype that describe the source process that is associated with the task — for example, Order and DSL Order.
REQUESTED_SKILL_KEY int Key to the SKILL dimension, identifying the agent skill that was requested by the iWD rule.
REQUESTED_AGENT_KEY int Key to the AGENT dimension, identifying the agent who was requested by the iWD rule.
CUSTOM_DIM_KEY int Key to the CUSTOM_DIM dimension containing five additional attributes (beyond those that are listed below) that can be used to dimension a task.
CUSTOM_ATTRIBUTE1 nvarchar(255) Custom attributes describe a task.

A total of 10 custom attributes can be mapped to the task, with an additional 5 attributes in the CUSTOM_DIM dimension. If more than 10 task attributes exist, only the first 10 are mapped; the ones that remain are not mapped.

CUSTOM_ATTRIBUTE2
CUSTOM_ATTRIBUTE3
CUSTOM_ATTRIBUTE4
CUSTOM_ATTRIBUTE5
CUSTOM_ATTRIBUTE6
CUSTOM_ATTRIBUTE7
CUSTOM_ATTRIBUTE8
CUSTOM_ATTRIBUTE9
CUSTOM_ATTRIBUTE10
SRC_CRT_TIME_FR_FIRST_CRTD_SEC int Calculated time value, in seconds, that counts the time that has elapsed from task capture from the first system to the source system—for example, fax server to workflow system.
CRT_TIME_FR_SRC_CRTD_SEC int Calculated time value, in seconds, from the time at which the task was created in the source system—for example, workflow—to the time at which it was created in iWD.
ACTIVATE_TIME_FROM_CREATED_SEC int Calculated value, in seconds, that counts the time that has elapsed from the time at which the task was submitted to iWD to the time at which it was activated.
ASSIGN_TIME_FROM_CREATED_SEC int Calculated value, in seconds, that counts the time that has elapsed from the time at which the task was created in iWD to the time at which it was assigned to an agent.
COMPLETE_TIME_FROM_CREATED_SEC int Calculated value, in seconds, that counts the time that has elapsed from the time at which the task was created in iWD to the time at which it was completed by the agent.
TOTAL_HELD_TIME_SEC int Calculated value, in seconds, that counts the total time that a task was held in iWD. This is an iWD “hold” action via an iWD capture point or through iWD Manager user interface and not a hold event from a soft phone or desktop application.
TOTAL_WORK_TIME_SEC int Calculated value, in seconds, that counts the time that has elapsed from the time at which a task was assigned to an agent to the time at which it was completed by the agent. A task may have multiple work times, as noted in TASK_WORK_FACT. This is the total sum.
CREATED_INTERVAL int Time interval that is derived from the CREATED_DATE_KEY and CREATED_TIME_KEY fields. Used for ETL scripts.
COMPLETED_INTERVAL int Time interval that is derived from the COMPLETED_DATE_KEY and COMPLETED_TIME_KEY fields. Used for ETL scripts.
DUE_TS int Timestamp for the iWD task’s due date and time. Used for ETL scripts.
COMPLETED_TS int Timestamp for the iWD task’s completed date and time. Used for ETL scripts.
ACTIVATION_INTERVAL int Time interval that is derived from the ACTIVATION_DATE_KEY and ACTIVATION_TIME_KEY fields. Used for ETL scripts.
ASSIGNED_INTERVAL int Time interval that is derived from the ASSIGNED_DATE_KEY and ASSIGN_TIME_KEY fields. Used for ETL scripts.
DUE_INTERVAL int Time interval that is derived from the DUE_DATE_KEY and DUE_TIME_KEY fields. Used for ETL scripts.
SOURCE_CREATED_INTERVAL int Time interval that is derived from the SOURCE_CREATED_DATE_KEY and SOURCE_CREATED_TIME_KEY fields. Used for ETL scripts.
SOURCE_FIRST_CREATED_INTERVAL int Time interval that is derived from the SOURCE_FIRST_CREATED_DATE_KEY and SOURCE_FIRST_CREATED_TIME_KEY fields. Used for ETL scripts.
CREATED_ETL_AUDIT_KEY int Key to the ETL_AUDIT dimension, identifying the ETL job that created this task fact.
UPDATED_ETL_AUDIT_KEY int Key to the ETL_AUDIT dimension, identifying the ETL job that last updated this task fact.
TIMEZONE_KEY int Key to the TIMEZONE dimension, identifying the time zone of the timestamp at which the task was created.
START_DATE_TIME_KEY int Key to the DATE_TIME table, identifying the 15-minute interval during which this record was created.

Feedback

Comment on this article:

blog comments powered by Disqus
This page was last modified on December 19, 2017, at 02:26.