Jump to: navigation, search

INTERACTION_FACT

Important.png Please be aware that the formulas and recommendations have not been validated internally prior to the initial release of this document. They are to be treated as results of theoretical analysis and a good starting point for re-orienting the 7.6 database queries towards the Info Mart 8.1 database.
DevGuide2.png Purpose: To provide compatibility references between the columns in the 7.6 INTERACTION_FACT table and the fields in the Info Mart 8.x schema.

Table INTERACTION_FACT

This table has been redesigned significantly because of the changes in handling the lowest level of data details. In the Info Mart 7.6 schema, INTERACTION_FACT is modeled as a summary of the underlying INTERACTION_SEGMENT_FACT rows, rather than as a summary of the underlying INTERACTION_RESOURCE_FACT rows. Because of the removal of the INTERACTION_SEGMENT_FACT table in the Info Mart 8.0 schema, most of the INTERACTION_FACT columns have been retired as well. In addition to using the recommendations for particular fields below, consider summarizing the underlying INTERACTION_RESOURCE_FACT data to obtain the metrics similar to those in your 7.6 reports.

7.6 Column Name 8.x Changes
Type of Change
Description/Recommendation
New Table.Column
INTERACTION_ID No Change
GMT_ENTERPRISE_DATE_KEY Retired See Changes to Time-Related Tables and Fields.
GMT_TENANT_DATE_KEY Retired See Changes to Time-Related Tables and Fields.
GMT_TIME_OF_DAY_KEY Retired See Changes to Time-Related Tables and Fields.
STD_ENTERPRISE_DATE_KEY Retired See Changes to Time-Related Tables and Fields.
STD_TENANT_DATE_KEY Retired See Changes to Time-Related Tables and Fields.
STD_ENTERPRISE_TIME_OF_DAY_KEY Retired See Changes to Time-Related Tables and Fields.
STD_TENANT_TIME_OF_DAY_KEY Retired See Changes to Time-Related Tables and Fields.
LOCAL_ENTERPRISE_DATE_KEY Retired See Changes to Time-Related Tables and Fields.
LOCAL_TENANT_DATE_KEY Retired See Changes to Time-Related Tables and Fields.
LOCAL_TIME_OF_DAY_KEY Retired See Changes to Time-Related Tables and Fields.
TENANT_KEY No Change
INTERACTION_TYPE_KEY No Change
MEDIA_TYPE_KEY No Change
TECHNICAL_DESCRIPTOR_KEY Replaced INTERACTION_RESOURCE_FACT.TECHNICAL_DESCRIPTOR_KEY or MEDIATION_SEGMENT_FACT.TECHNICAL_DESCRIPTOR_KEY
MEDIA_RESOURCE_KEY Replaced Can be calculated from MEDIA_RESOURCE_KEY for child IRF records INTERACTION_RESOURCE_FACT.MEDIA_RESOURCE_KEY
RESOURCE_KEY Replaced Can be calculated from RESOURCE_KEY for child IRF records. INTERACTION_RESOURCE_FACT.RESOURCE_KEY or MEDIATION_SEGMENT_FACT.RESOURCE_KEY
PLACE_KEY Replaced Can be calculated from PLACE_KEY for child IRF records. INTERACTION_RESOURCE_FACT.PLACE_KEY
REQUESTED_SKILL_KEY Replaced Can be calculated from REQUESTED_SKILL_KEY for child IRF records. INTERACTION_RESOURCE_FACT.REQUESTED_SKILL_KEY
INTERACTION_DESCRIPTOR_KEY Replaced Can be calculated using IRF_USER_DATA_KEYS and child IRF records. IRF_USER_DATA_KEYS.INTERACTION_DESCRIPTOR_KEY
CUSTOMER_KEY Replaced Can be calculated from IRF_USER_DATA_GEN_1 and child IRF records.
CURRENCY_KEY Retired
USER_DATA_KEY Replaced See Changes to User Data Tables and Fields.
USER_DATA_2_KEY Replaced See Changes to User Data Tables and Fields.
CREATE_AUDIT_KEY Changed See Database Schema Changes Overview.
UPDATE_AUDIT_KEY Changed See Database Schema Changes Overview.
ROOT_INTERACTION_ID Retired
IXN_FACT_EXT_KEY Retired
GMT_START_TIME Retired See Changes to Time-Related Tables and Fields.
GMT_END_TIME Retired See Changes to Time-Related Tables and Fields.
STD_ENTERPRISE_START_TIME Retired See Changes to Time-Related Tables and Fields.
STD_ENTERPRISE_END_TIME Retired See Changes to Time-Related Tables and Fields.
STD_TENANT_START_TIME Retired See Changes to Time-Related Tables and Fields.
STD_TENANT_END_TIME Retired See Changes to Time-Related Tables and Fields.
LOCAL_START_TIME Retired See Changes to Time-Related Tables and Fields.
LOCAL_END_TIME Retired See Changes to Time-Related Tables and Fields.
MEDIA_SERVER_ROOT_IXN_ID No Change
MEDIA_SERVER_IXN_ID No Change
MEDIA_SERVER_ROOT_IXN_GUID No Change
MEDIA_SERVER_IXN_GUID No Change
INTERACTION_COUNT Retired Was always set to 1.
UNIQUE_INTERACTION_COUNT Retired Was always set to 1.
TOTAL_SEGMENT_COUNT Retired
TOTAL_DURATION Replaced Can be calculated as END_TS - START_TS (if END_TS > 0).
ROUTING_POINT_SEGMENT_COUNT Retired
ROUTING_POINT_SEGMENT_DURATION Retired
QUEUE_SEGMENT_COUNT Retired
QUEUE_SEGMENT_DURATION Retired
IVR_PORT_SEGMENT_COUNT Retired
IVR_PORT_SEGMENT_DURATION Retired
AGENT_SEGMENT_COUNT Retired
AGENT_SEGMENT_DURATION Retired
NETWORK_SEGMENT_COUNT Retired
NETWORK_SEGMENT_DURATION Retired
ALERT_COUNT Retired
ALERT_DURATION Retired
HANDLE_COUNT Retired
HANDLE_DURATION Retired
WRAP_COUNT Retired
WRAP_DURATION Retired
AGENT_HANDLE_DURATION Retired
CUSTOMER_HANDLE_COUNT Retired
CUSTOMER_HANDLE_DURATION Retired
CUSTOMER_WAIT_COUNT Retired
CUSTOMER_WAIT_DURATION Retired
BASELINE_SERVICE_OBJECTIVE Replaced Moved down to IRF level, available in IRF_USER_DATA_GEN_1. IRF_USER_DATA_GEN_1.SERVICE_OBJECTIVE
INITIAL_RESPONSE_DURATION Retired This metric depends on the value of voice-init-resp-duration. For TIME_TO_FIRST_SERVICE, use IRF.START_TS - IRF.RING_DURATION - IF.START_TS WHERE IRF.IRF_ACHNOR=1 AND IRF.TALK_COUNT>1. For TIME_TO_FIRST_AGENT, the calculations are similar, but instead of the IRF.IRF_ACHNOR=1 condition, find the first (min(IRF.START_TS) IRF pointing to an agent resource and with TALK_COUNT>1. No replacement is available for TIME_FROM_IVR_TO_AGENT.
MET_SERVICE_OBJECTIVE_FLAG Replaced Use IRF.MET_SERVICE_OBJECTIVE_FLAG WHERE IRF.IRF_ANCHOR=1.
REQUESTED_SKILL_COUNT Replaced Moved down to IRF level; use the value available in REQUESTED_SKILL_COMBINATION.SKILL COUNT. Find the first (min(start_ts)) IRF for which REQUESTED_SKILL_COMBINATION.SKILL COUNT > 0.
MATCHED_SKILL_COUNT Retired
ANSWERED_WITH_SKILL_MATCH_FLAG Retired
REVENUE_STD_CURRENCY Retired Was a reserved field in 7.6.
COST_STD_CURRENCY Retired Was a reserved field in 7.6.
REVENUE_LOCAL_CURRENCY Retired Was a reserved field in 7.6.
COST_LOCAL_CURRENCY Retired Was a reserved field in 7.6.
SOURCE_ADDRESS No Change
TARGET_ADDRESS No Change
CASE_ID Replaced Moved down to IRF level and is available in IRF_USER_DATA_GEN_1. IRF_USER_DATA_GEN_1.CASE_ID
USER_DATA_1 Replaced Can be can calculated from child IRF records, IRF_USER_DATA_GEN_1, and custom user data tables. See Changes to User Data Tables and Fields.
USER_DATA_2 Replaced Can be can calculated from child IRF records, IRF_USER_DATA_GEN_1, and custom user data tables. See Changes to User Data Tables and Fields.
USER_DATA_3 Replaced Can be can calculated from child IRF records, IRF_USER_DATA_GEN_1, and custom user data tables. See Changes to User Data Tables and Fields.
USER_DATA_4 Replaced Can be can calculated from child IRF records, IRF_USER_DATA_GEN_1, and custom user data tables. See Changes to User Data Tables and Fields.
USER_DATA_5 Replaced Can be can calculated from child IRF records, IRF_USER_DATA_GEN_1, and custom user data tables. See Changes to User Data Tables and Fields.
USER_DATA_6 Replaced Can be can calculated from child IRF records, IRF_USER_DATA_GEN_1, and custom user data tables. See Changes to User Data Tables and Fields.
USER_DATA_7 Replaced Can be can calculated from child IRF records, IRF_USER_DATA_GEN_1, and custom user data tables. See Changes to User Data Tables and Fields.
USER_DATA_8 Replaced Can be can calculated from child IRF records, IRF_USER_DATA_GEN_1, and custom user data tables. See Changes to User Data Tables and Fields.
USER_DATA_9 Replaced Can be can calculated from child IRF records, IRF_USER_DATA_GEN_1, and custom user data tables. See Changes to User Data Tables and Fields.
USER_DATA_10 Replaced Can be can calculated from child IRF records, IRF_USER_DATA_GEN_1, and custom user data tables. See Changes to User Data Tables and Fields.
USER_DATA_11 Replaced Can be can calculated from child IRF records, IRF_USER_DATA_GEN_1, and custom user data tables. See Changes to User Data Tables and Fields.
USER_DATA_12 Replaced Can be can calculated from child IRF records, IRF_USER_DATA_GEN_1, and custom user data tables. See Changes to User Data Tables and Fields.
USER_DATA_13 Replaced Can be can calculated from child IRF records, IRF_USER_DATA_GEN_1, and custom user data tables. See Changes to User Data Tables and Fields.
USER_DATA_14 Replaced Can be can calculated from child IRF records, IRF_USER_DATA_GEN_1, and custom user data tables. See Changes to User Data Tables and Fields.
USER_DATA_15 Replaced Can be can calculated from child IRF records, IRF_USER_DATA_GEN_1, and custom user data tables. See Changes to User Data Tables and Fields.
USER_DATA_16 Replaced Can be can calculated from child IRF records, IRF_USER_DATA_GEN_1, and custom user data tables. See Changes to User Data Tables and Fields.
USER_DATA_17 Replaced User data is stored in IRF_USER_DATA_GEN_1 and custom user data tables.
USER_DATA_18 Replaced Can be can calculated from child IRF records, IRF_USER_DATA_GEN_1, and custom user data tables. See Changes to User Data Tables and Fields.
USER_DATA_19 Replaced Can be can calculated from child IRF records, IRF_USER_DATA_GEN_1, and custom user data tables. See Changes to User Data Tables and Fields.
USER_DATA_20 Replaced Can be can calculated from child IRF records, IRF_USER_DATA_GEN_1, and custom user data tables. See Changes to User Data Tables and Fields.
ACTIVE_FLAG No Change
GMT_ROW_CREATED_TIME Retired See Changes to Time-Related Tables and Fields.
GMT_ROW_UPDATED_TIME Retired See Changes to Time-Related Tables and Fields.
PURGE_FLAG Changed See Database Schema Changes Overview.


This page was last edited on March 27, 2013, at 22:41.

Feedback

Comment on this article:

blog comments powered by Disqus