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.
Table MEDIATION_SEGMENT_FACT
Description
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.
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.
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
| 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 | ||
| PRODUCER_BATCH_ID | numeric(19) | ||||
| ORSSESSIONID | varchar(128) |
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 Options Reference.
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 primary 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 primary 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.
PRODUCER_BATCH_ID
Introduced: Release 8.5.015.19
Reserved for internal use.
ORSSESSIONID
Introduced: Release 8.5.116.12
Modified: 8.5.116.45 (size of the column increased)
Reserved for internal use.
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.