In this chapter we list facts available in the data warehouse. In the following chapters, we will specify the list of dimensions and measures applicable for these facts.

  • The fact table contains business process events with appropriate measures, and foreign keys, which refer to the dimension tables.
  • The grain of the fact table is the finest level of detail that is implied when the fact and dimension tables are joined. For example, the granularity of Task fact table is a finished Call, IM or Email. The call is referenced by started date and time and has its type dimensions and duration measures.
  • Measures are numerical values that mathematical functions work on. E.g. in Task fact table there is ConnectedTimeMs field, which is a measure because you can find out a total or average of its values.

The fact table contains business process events with appropriate measures, and foreign keys, which refer to the dimension tables.

Fact datasets

Task

A Task fact is a process during which a conversation stays in one context of the system.

The Task is finished in two cases:

  • when context is removed (when all participants are disconnected) or
  • when the task context is changed (when conversation is transferred to another Service Sip Uri).

A fact table grain is a finished process from start to end when conversation stays in one context.


Transfer

EXEC [Reporting].[usp_GetDataSet] @DataSet = 'Transfer', @Version = '3.2.1'
SQL

A Transfer is a business process which sends the conversation from one Sip URI to another Sip URI.

Fact table grain: completed transfer.

Transfer Types and Customer Tasks

Depending on transfer target (Agent/Service/External) a new Task or AgentTask may or may be not created after a transfer. In case of new Task being created after the transfer, both source and target Tasks are created within one Customer Task.

  1. Transfer to Service – new Task
  2. Transfer to Agent - new Agent Task
  3. Transfer to External – nothing

The table below lists all supported transfer options.

TransferTypeI - TransferType - Description

Transfer activity

Resulting count of Tasks/AgentTasks
within customer task

121TRF_SRV_AGT Service - AgentWorkflow Transfer

WFA Virtual TransferWFA Blind TransferWFA Language Transfer

1 Customer Task
1 Task (initial)
1 Agent Task (new after transfer to Agent)

122 TRF_SRV_SRV Service - Service Workflow Transfer

WFA Virtual TransferWFA Blind TransferWFA Language Transfer

1 Customer Task
2 Tasks (initial + new after transfer to service)

123TRF_SRV_EXT Service - ExternalWorkflow Transfer

WFA Virtual TransferWFA Blind TransferWFA Language Transfer

1 Customer Task
1 Task (initial)

221FWD_SRV_AGT Service - AgentWorkflow Transfer

WFA Forward

1 Customer Task
1 Task (initial)
1 Agent Task (new after forward to Agent)

222FWD_SRV_SRV Service - ServiceWorkflow Transfer

WFA Forward

1 Customer Task
2 Tasks (initial + new after forward to service)

223FWD_SRV_EXT Service - ExternalWorkflow Transfer

WFA Forward

1 Customer Task
1 Task (initial)

111 TRF_AGT_AGT Agent - Agent Agent Transfer

SfB Blind Transfer
Sfb Consultation Transfer

1 Customer Task
1 Task (initial)
2 Agent Task (initial + new after transfer to Agent)

112TRF_AGT_SRV Agent - ServiceAgent Transfer

SfB Blind Transfer

1 Customer Task
2 Tasks (initial + new after forward to service)
1 Agent Task (initial)

113TRF_AGT_EXT Agent - ExternalAgent Transfer

SfB Blind Transfer

1 Customer Task
1 Task (initial)
1 Agent Task (initial)

Survey Result

EXEC [Reporting].[usp_GetDataSet] @DataSet = 'TransferType', @Version = '3.2.1'
SQL

EXEC [Reporting].[usp_GetDataSet] @DataSet = 'SurveyResult', @Version = '3.2.1'

Survey result is a customer answer to one question.

Fact table grain – submitted answer to one question.

There may be 2 technical result values:

  • NoAnswer – this value is recorded if caller did not provided value before timeout, or value is of the unexpected type or outside range.
  • SkipQuestion – this value is recorded if customer forcibly skips the survey question, e.g. hang up.

Field

Field Type

Description/Related data set

SurveyQuestionResultId

Integer

Primary key

SurveyQuestionId

Reference

Survey Question

TaskId

Reference

Task

CustomerTaskId

Reference

CustomerTask

ResultText

String value

Actual value stored in database

ResultNumeric

Integer

Numeric value or NULL in case Result is not numeric/ NoAnswer or SkipQuestion

ResultWeighted

Integer

Weighted result is calculated only for Survey questions with Range. With provided result (NoAnswer and SkipQuestion values are excluded)

Percentage value is decimal within 0 and 1) calculated based on the predefined borders.

For example if range is 3 till 8, recorder result is 5 the

ResultWeighted = 5 -3 / (8-3) = 0.4

Task Completion Information

EXEC [Reporting].[usp_GetDataSet] @DataSet = 'TackCompletionInformation', @Version = '3.2.1'
SQL

Task completion information:

  • is only contained in service agent tasks.
  • is an selection information picked by the Agent, consiting of "Task Completion Code" and "Cross Selling Code"

Fact table grain: submitted selection of Task Completion Code and Cross Selling Code.

Agent State Period

EXEC [Reporting].[usp_GetDataSet] @DataSet = 'TransferType', @Version = '3.2.1'
SQL

Fact table contains periods defining when an Agent presence state is persistent. Once a state is changed a new period is started.

Agent State Hourly Snapshot

EXEC [Reporting].[usp_GetDataSet] @DataSet = 'AgentStateHourlySnapshot', @Version = '3.2.1'
SQL

This fact table contains 1-hour-snapshots of the Agents. A row per Agent per hour, one column per Agent State Type sum in milliseconds.

Agent Not Ready Reason

EXEC [Reporting].[usp_AgentNotReadyReasons_DM_Stable]  @Offset = '+01:00', @UseDaylightSavings = 1, @StartDate='20171007', @EndDate='20190127', @LanguageId = 'DEU'
SQL

This fact table contains a list of "Not Ready" periods of the Agents. Every time when an Agent changes state to "Not Ready" (Busy, DND, Be right back, etc.) it is excluded from the task distribution pool, until the state is changed back to "Available". The affected Agent is requested to select a Not-Ready-Reason from the predefined list and add a free-text comment.

Fact table grain: Period when agent is in one state.