Jump to: navigation, search

View GROUP_TO_CAMPAIGN_FACT

Description

Each row describes the association of an agent or place group to an outbound campaign. The grain of the fact is an accumulating snapshot that represents the duration of the association between an agent or place group and a campaign.

Column List

Column Description
GROUP_TO_CAMPAIGN_FACT_KEY The primary key of this view.
GROUP_KEY The surrogate key that is used to join the GROUP_ dimension to the fact tables.
CAMPAIGN_KEY The surrogate key that is used to join the CAMPAIGN dimension to the fact tables.
TENANT_KEY The surrogate key that is used to join the TENANT dimension to the fact tables.
START_DATE_TIME_KEY Identifies the start of a 15-minute interval in which the agent group or place group was added to the campaign in the contact center configuration. 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 agent group or place group was removed from the campaign in the contact center configuration. 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.
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 used to join to the CTL_AUDIT_LOG dimension. 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.
START_TS The UTC-equivalent value of the date and time when the agent group or place group was added to the campaign in the contact center configuration.
END_TS The meaning depends on the value of ACTIVE_FLAG. For an inactive row, the UTC-equivalent value of the date and time when the agent group or place group was removed from the campaign in the contact center configuration. For an active row, this value represents a UTC-equivalent value of the date and time far in the future, so that applications do not have to test for null.
TOTAL_DURATION The meaning depends on the value of ACTIVE_FLAG. For an inactive row, this value represents the total duration, in seconds, that the agent group or place group was associated with the campaign. For an active row, this value represents the duration, in seconds, that the agent group or place group was associated with the campaign, from start time to the time that the ETL last executed.
ACTIVE_FLAG Indicates whether the association between the agent group or place group and the campaign is still active: 0 = No, 1 = Yes.
PURGE_FLAG This field is reserved.

SQL Query

select
  GROUP_TO_CAMPAIGN_FACT_KEY,
  GROUP_KEY,
  CAMPAIGN_KEY,
  TENANT_KEY,
  START_DATE_TIME_KEY,
  END_DATE_TIME_KEY,
  CREATE_AUDIT_KEY,
  UPDATE_AUDIT_KEY,
  START_TS,
  END_TS,
  (case when ACTIVE_FLAG
<> 0
  then (select (max(LAST_CFG_EXTRACT_TS) - START_TS) from CTL_EXTRACT_METRICS)
  else END_TS - START_TS end) as TOTAL_DURATION,
  ACTIVE_FLAG,
  PURGE_FLAG
  from GROUP_TO_CAMPAIGN_FACT_

Feedback

Comment on this article:

blog comments powered by Disqus
This page was last modified on 29 August 2016, at 14:54.