Jump to: navigation, search

In the Column List:

  • P = Primary key
  • M = Mandatory field
  • F = Foreign key (where the term is used loosely to indicate a surrogate key reference to a field in another table, not a formal constraint)
  • DV = Default value
  • In the Data Type column, varchar/nvarchar means that the data type is varchar except in multi-language databases that use Unicode, in which case the data type is nvarchar. (The Info Mart database in Cloud deployments is not multi-language.)

Table MEDIATION_SEGMENT_FACT

Description

Modified: 8.5.004 (USERDATA_FLAG added); 8.5.003 (in Oracle, fields with VARCHAR data types use explicit CHAR character-length semantics)

In partitioned databases, this table is partitioned.


This table describes interaction activity with respect to mediation DNs, including virtual and ACD queues, as well as Genesys eServices/Multimedia interaction queues and workbins. The grain of the fact spans the time from when the interaction enters the mediation DN to when the interaction leaves the mediation DN in one of the following three ways:

  • Abandoned in the mediation DN
  • Cleared from the mediation DN (for virtual queues only)
  • Distributed from the mediation DN, including the time that it takes the interaction to be answered by the target resource or to be abandoned while alerting at the target resource

For voice, only completed ACD queue and virtual queue activity is populated; for multimedia, both active and completed virtual queue activity is populated.

Important
Availability of active virtual queue data in Genesys Info Mart depends on the vq-write-mode configuration option in Interaction Concentrator.

In releases prior to 8.5.003, the populate-mm-ixnqueue-facts configuration option disables the population of eServices/Multimedia Interaction Queue activity to the MSF table. Starting with release 8.5.003, an MSF record is populated for the starting Interaction Queue of an Inbound Interaction, even if populate-mm-ixnqueue-facts is set to false.

The mediation segment start and end dates and times are stored as facts in the UTC format.

Tip
To assist you in preparing supplementary documentation, click the following link to download a comma-separated text file containing information such as the data types and descriptions for all columns in this table: Download a CSV file.

Hint: For easiest viewing, open the downloaded CSV file in Excel and adjust settings for column widths, text wrapping, and so on as desired. Depending on your browser and other system settings, you might need to save the file to your desktop first.

Column List

Legend

Column Data Type P M F DV

MEDIATION_SEGMENT_ID

numeric(19) X X

TENANT_KEY

int X X

START_DATE_TIME_KEY

int X X

END_DATE_TIME_KEY

int X X

INTERACTION_TYPE_KEY

int X X

MEDIA_TYPE_KEY

int X X

TECHNICAL_DESCRIPTOR_KEY

int X X

RESOURCE_KEY

int X X

RESOURCE_GROUP_COMBINATION_KEY

int X X

WORKBIN_KEY

int X -2

INTERACTION_SDT_KEY

int X

INTERACTION_ID

numeric(19) X

IXN_RESOURCE_SDT_KEY

int X

IXN_RESOURCE_ID

numeric(19) X

TARGET_IXN_RESOURCE_SDT_KEY

int X

TARGET_IXN_RESOURCE_ID

numeric(19) X

MEDIA_SERVER_IXN_GUID

varchar(50)

MEDIATION_GUID

varchar(50)

ENTRY_ORDINAL

int

MEDIATION_DURATION

int

ONLINE_DURATION

int

ANSWER_THRESHOLD

int

SHORT_ABANDONED_FLAG

numeric(1)

MET_THRESHOLD_FLAG

numeric(1)

ACTIVE_FLAG

numeric(1)

USERDATA_FLAG

numeric(1)

START_TS

int

END_TS

int

CREATE_AUDIT_KEY

numeric(19) X X

UPDATE_AUDIT_KEY

numeric(19) X X

MEDIATION_SEGMENT_ID

The primary key of this table.

TENANT_KEY

The surrogate key that is used to join the TENANT dimension to the fact tables, to indicate the tenant to which the mediation DN belongs.

START_DATE_TIME_KEY

Identifies the start of a 15-minute interval in which the interaction entered the mediation DN. Use this value as a key to join the fact tables to any configured DATE_TIME dimension, in order 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 interaction left the mediation DN. Use this value as a key to join the fact tables to any configured DATE_TIME dimension, in order to group the facts that are related to the same interval and/or convert the END_TS timestamp to an appropriate time zone. For an active row that represents a multimedia interaction that is currently at the mediation DN (where ACTIVE_FLAG=1), this field references the date and time far in the future, so that applications do not have to test for null.

INTERACTION_TYPE_KEY

The surrogate key that is used to join this table to the INTERACTION_TYPE dimension, to identify the interaction's type. For voice interactions, this value matches the related INTERACTION_FACT row. For multimedia interactions, this value reflects the interaction type/subtype of the Interaction Server interaction that is placed in the virtual queue, interaction queue, or workbin.

MEDIA_TYPE_KEY

The surrogate key that is used to join this table to the MEDIA_TYPE dimension, to identify the media type that is associated with this handling attempt. For voice interactions, this value matches the related INTERACTION_FACT row. For multimedia interactions, this value is derived from the Interaction Server interaction and can differ from the respective value in INTERACTION_FACT; for example, an inbound chat interaction may include an e-mail response.

TECHNICAL_DESCRIPTOR_KEY

The surrogate key that is used to join the TECHNICAL_DESCRIPTOR dimension to the fact tables, to indicate the result of the mediation segment, such as Abandoned, Cleared, or Diverted.

RESOURCE_KEY

The surrogate key that is used to join the RESOURCE_ dimension to the fact tables, to indicate the mediation DN resource.

RESOURCE_GROUP_COMBINATION_KEY

The surrogate key that is used to join records in this table to a specific combination of resource groups in the RESOURCE_GROUP_COMBINATION dimension. This field identifies the groups of which the mediation DN resource was a member when the interaction entered the mediation DN. This field references the default "No Group" (-2) value if the mediation DN does not belong to a group. This field references the "UNKNOWN" (-1) value for the records that are associated with a discarded group combination.

WORKBIN_KEY

In MSF records that are created as a result of workbin time that is considered to be mediation, this field is the surrogate key that is used to join this table to the WORKBIN dimension, to identify the type of resource that is associated with the workbin and the specific resource that is associated with the mediation. For MSF records that are not associated with workbin mediation, this field is populated with the specified default value (-2).

For a summary of the conditions under which workbin time is considered to be mediation, see the description of the populate-workbin-as-hold configuration option in the Genesys Info Mart Deployment Guide.

INTERACTION_SDT_KEY

The value of the START_DATE_TIME_KEY field of the record in the INTERACTION_FACT table. On a partitioned database, INTERACTION_SDT_KEY in combination with INTERACTION_ID forms a value of the composite primary key for the INTERACTION_FACT table.

INTERACTION_ID

The value of the interaction fact primary key.

IXN_RESOURCE_SDT_KEY

The value of the START_DATE_TIME_KEY field of the INTERACTION_RESOURCE_FACT record that is identified by the IXN_RESOURCE_ID field. On a partitioned database, IXN_RESOURCE_SDT_KEY in combination with IXN_RESOURCE_ID forms a value of the composite primary key for the INTERACTION_RESOURCE_FACT table.

IXN_RESOURCE_ID

The value of the primary key of the INTERACTION_RESOURCE_FACT table. In MSF records that are part of an attempt (successful or unsuccessful) to reach a handling resource, this field is the ID of the IRF that represents the attempt. This field can be used to join the MSF table to the IRF table. If the interaction passes through multiple mediation resources during the attempt to reach a handling resource, many MSF records will reference the same master IRF record. If the attempt is successful, the referenced IRF is the IRF for the handling resource that was reached. If the attempt is unsuccessful, the referenced IRF is the IRF for the last mediation resource (the resource in which the interaction ended).

This field is not populated if ICON has not been configured to populate the G_ROUTE_RES_VQ_HIST table (in other words, if route-res-vqid-hist-enabled in the ICON application is set to false).

TARGET_IXN_RESOURCE_SDT_KEY

The value of the START_DATE_TIME_KEY field of the INTERACTION_RESOURCE_FACT record that is identified by the TARGET_IXN_RESOURCE_ID field. On a partitioned database, TARGET_IXN_RESOURCE_SDT_KEY in combination with TARGET_IXN_RESOURCE_ID forms a value of the composite primary key for the INTERACTION_RESOURCE_FACT table.

TARGET_IXN_RESOURCE_ID

The value of the primary key of the INTERACTION_RESOURCE_FACT table. Identifies the target of the distribution from this mediation DN. This field can be used to join this table to the INTERACTION_RESOURCE_FACT table.

MEDIA_SERVER_IXN_GUID

The unique interaction ID, as reported by the interaction media server. In the case of voice T-Server, the GUID is the call’s UUID. In the case of multimedia, the GUID is either of the following:

  • The interaction ID from Interaction Server, in a record that is created for virtual queue
  • The call ID of the party that is associated with the mediation DN, in a record that is created for an interaction queue or workbin

MEDIATION_GUID

The unique ID that represents the interaction in the virtual queue, as reported by URS through ICON. URS uses this ID to resolve calls that are stuck in a virtual queue. For ACD queue activity (associated with voice interactions), this field contains the party GUID for the ACD queue party, as reported by ICON. For interaction queue or workbin activity (associated with multimedia interactions), this field contains the party GUID for the interaction queue or workbin party, as reported by ICON.

ENTRY_ORDINAL

Indicates the order of entrance of this mediation segment relative to other mediation segments of the same master IRF record. The other mediation segments are MSF records that have the same IXN_RESOURCE_ID.

This field is not populated if ICON has not been configured to populate the G_ROUTE_RES_VQ_HIST table (in other words, if route-res-vqid-hist-enabled in the ICON application is set to false).

MEDIATION_DURATION

The time, in seconds, from when the interaction enters the mediation DN to when the interaction is removed, for any reason.

For ACD queues, interaction queues, or interaction workbins, the mediation duration does not include any time spent in a strategy or a virtual queue, except for bounce-back scenarios (a subset of "runaway strategy" scenarios in which an interaction is bounced between the mediation resource and a strategy, as the strategy repeatedly retries busy agents). In bounce-back scenarios, all the time that the interaction spends in a particular mediation resource is combined into a single MSF record, and the mediation duration in the MSF for that resource includes all the interim strategy time.

For virtual queues, the adjust-vq-time-by-strategy-time configuration option controls whether the mediation duration includes or excludes the time that the interaction spent in the strategy but outside the virtual queue. For an active multimedia interaction that is currently at a mediation DN, this value is 0.

For multimedia interactions that involve very large numbers of parties or VQs, such that Genesys Info Mart abbreviates the representation of unsuccessful routing attempts ("runaway strategy" scenarios), population of this field changed between release 8.1.1 and release 8.1.2.

  • In release 8.1.1, a new MSF record is created every time an interaction enters a virtual queue. This field includes only the duration until the interaction leaves the virtual queue.
  • In release 8.1.2, a single MSF record is created for a particular virtual queue, regardless of the number of times that an interaction returns to this virtual queue. This field includes all the time that the interaction spends in a particular virtual queue during mediation. (Refer to the Genesys Info Mart 8.1 Deployment Guide for information about how the max-parties-per-call configuration option controls when excessive numbers of parties are skipped.)

ONLINE_DURATION

Part of the MEDIATION_DURATION before the interaction went offline, for Genesys eServices/Multimedia chat and online 3rd Party Media interactions. For voice calls, ONLINE_DURATION and MEDIATION_DURATION are equal. For e-mail messages and offline 3rd Party Media interactions, ONLINE_DURATION equals 0.

ANSWER_THRESHOLD

The number of seconds that establishes a threshold for an interaction to be both distributed from the mediation DN and accepted by the target resource. This value is derived from the value of the q-answer-threshold-voice configuration option for voice interactions or the media-specific q-answer-threshold configuration option for multimedia interactions.

SHORT_ABANDONED_FLAG

Indicates whether the interaction was abandoned in the mediation DN within the defined threshold, in which case the value is 1, or abandoned in the mediation DN outside this threshold, in which case the value is 0. The threshold is defined by the q-short-abandoned-threshold-voice configuration option for voice interactions or by the media-specific q-short-abandoned-threshold configuration option for multimedia interactions. If the interaction was not abandoned at all, this value is 0.

MET_THRESHOLD_FLAG

Indicates whether the interaction was distributed from the mediation DN and accepted by a resource within the defined threshold. If so, the value of this field is 1; otherwise, the value is 0. The threshold is defined by the q-answer-threshold-voice configuration option for voice interactions or by the media-specific q-answer-threshold configuration option for multimedia interactions.

ACTIVE_FLAG

Indicates whether the mediation DN segment is currently active: 0 = No, 1 = Yes.

USERDATA_FLAG

Introduced: Release 8.5.004
This flag facilitates an unambiguous join between the MSF and fact extension tables to retrieve correct user data that is attached during mediation. If user data is associated with this MSF record, the value of this field is 1; otherwise, the value is 0.

START_TS

The UTC-equivalent value of the date and time at which the interaction entered the mediation DN.

END_TS

The UTC-equivalent value of the date and time at which the interaction that left the mediation DN (was diverted, cleared, or abandoned while queued) reached the target resource or was abandoned. For multimedia, this value also depends on the value of the ACTIVE_FLAG field. For an active row (where ACTIVE_FLAG=1), this field instead represents a UTC-equivalent value of the date and time far in the future, so that applications do not have to test for null.

CREATE_AUDIT_KEY

The surrogate key that is used to join to the CTL_AUDIT_LOG control table. The key specifies the lineage for data creation. This value can be 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 that is used to join to the CTL_AUDIT_LOG control table. The key specifies the lineage for data update. This value can be useful for aggregation, enterprise application integration (EAI), and ETL tools—that is, applications that need to identify recently modified data.


Index List

CODE U C Description

I_MSF_SDT

Improves access time, based on the Start Date Time key.

I_MSF_IID

Improves access time, based on the INTERACTION ID.

Index I_MSF_SDT

Field Sort Comment
START_DATE_TIME_KEY Ascending

Index I_MSF_IID

Field Sort Comment
INTERACTION_ID Ascending

Subject Areas

  • Facts - Represents the relationships between subject area facts.
  • Mediation_Segment - Represents interaction activity from the perspective of contact center ACD queues, virtual queues, interaction queues, and interaction workbins, as well as groups thereof.

 

Feedback

Comment on this article:

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