Jump to: navigation, search

Exporting data from Genesys Info Mart

You can export data from Genesys Info Mart to use in your own data warehouse. Prior to exporting data from Genesys Info Mart, use the information on this page to ensure that facts are up-to-date (if you are exporting non-aggregated data) or that aggregation is complete (if you are exporting aggregated data).

In order to export data, you must have direct access to the Info Mart database. For more information about accessing the Info Mart database, contact your administrator. For information about the Info Mart database schema, see the Genesys Info Mart 8.5 Reference Manual for your RDBMS type, which is available from docs.genesys.com. For information about the organization of aggregate tables, see Reporting and Analytics Aggregates Reference Manual.

Warning
This page describes actions that, if improperly performed, could negatively impact the operation of your database. Consult your database administrator before making any changes.

Tips for extracting Genesys Info Mart data

To incrementally export data (export only newly inserted or updated rows), you can check the audit key fields in Info Mart fact and dimension tables (CREATE_AUDIT_KEY, UPDATE_AUDIT_KEY). For example, on MSSQL deployments, select only newly inserted or updated rows by using logic similar to the following:

declare @MAX_AUDIT_KEY      numeric(19);
declare @PREV_MAX_AUDIT_KEY numeric(19);
 
SET @PREV_MAX_AUDIT_KEY = (SELECT max(AUDIT_KEY) FROM 'a table with high water mark of extracted data'
WHERE TABLE_NAME='INTERACTION_FACT';
 
SET @MAX_AUDIT_KEY = (SELECT MAX(AUDIT_KEY) FROM CTL_AUDIT_LOG WITH (INDEX(PK_CTL_AUDIT_LOG))
WHERE JOB_ID NOT IN (
    SELECT JOB_ID FROM CTL_WORKFLOW_STATUS
    WHERE STATUS IN ('RUNNING'))
AND AUDIT_KEY > @PREV_MAX_AUDIT_KEY);
 
SELECT * INTO INTERACTION_FACT_NEW FROM INTERACTION_FACT 
WHERE  CREATE_AUDIT_KEY BETWEEN @PREV_MAX_AUDIT_KEY+1 AND @MAX_AUDIT_KEY
OR     UPDATE_AUDIT_KEY BETWEEN @PREV_MAX_AUDIT_KEY+1 AND @MAX_AUDIT_KEY;
 
UPDATE 'a table with high water mark of extracted data'
SET AUDIT_KEY=@MAX_AUDIT_KEY 
WHERE TABLE_NAME='INTERACTION_FACT';

Tips for extracting Reporting and Analytics Aggregates data

While facts are recorded promptly after calls terminate, aggregate tables are updated on a periodic basis. Before you export aggregated data, ensure that aggregation is complete before you select the data to export, using logic similar to the following:

    To verify that aggregation is complete:

  1. Execute queries with logic similar to one or more of the following examples:
    • For the AG2_AGENT_*, AG2_ID_*, and AG2_QUEUE_* aggregates:
      SELECT @start=min(start_date_time_key), @end=max(start_date_time_key) 
      FROM INTERACTION_FACT 
      WHERE CREATE_AUDIT_KEY BETWEEN @PREV_MAX_AUDIT_KEY+1 AND @MAX_AUDIT_KEY
      OR UPDATE_AUDIT_KEY BETWEEN @PREV_MAX_AUDIT_KEY+1 AND @MAX_AUDIT_KEY;
    • For the AG2_I_AGENT_* aggregates:
      SELECT @start=min(start_date_time_key), @end=max(end_date_time_key) 
      FROM INTERACTION_FACT 
      WHERE CREATE_AUDIT_KEY BETWEEN @PREV_MAX_AUDIT_KEY+1 AND @MAX_AUDIT_KEY
      OR  UPDATE_AUDIT_KEY BETWEEN @PREV_MAX_AUDIT_KEY+1 AND @MAX_AUDIT_KEY;
    • For the AG2_I_SESS_STATE_*, or AG2_I_STATE_RSN_* aggregates:
      SELECT @start=min(start_date_time_key), @end=max(end_date_time_key) 
      FROM SM_RES_STATE_FACT 
      WHERE CREATE_AUDIT_KEY BETWEEN @PREV_MAX_AUDIT_KEY+1 AND @MAX_AUDIT_KEY
      OR  UPDATE_AUDIT_KEY BETWEEN @PREV_MAX_AUDIT_KEY+1 AND @MAX_AUDIT_KEY;
    • For the AG2_CAMPAIGN_* aggregates:
      SELECT @start=min(start_date_time_key), @end=max(start_date_time_key) 
      FROM CAMPAIGN_FACT 
      WHERE CREATE_AUDIT_KEY BETWEEN @PREV_MAX_AUDIT_KEY+1 AND @MAX_AUDIT_KEY
      OR UPDATE_AUDIT_KEY BETWEEN @PREV_MAX_AUDIT_KEY+1 AND @MAX_AUDIT_KEY
    • For the AG2_CALLBACK_* aggregates:
      SELECT @start=min(start_date_time_key), @end=max(start_date_time_key) 
      FROM CALLBACK_FACT 
      WHERE CREATE_AUDIT_KEY BETWEEN @PREV_MAX_AUDIT_KEY+1 AND @MAX_AUDIT_KEY
      OR  UPDATE_AUDIT_KEY BETWEEN @PREV_MAX_AUDIT_KEY+1 AND @MAX_AUDIT_KEY;
  2. Execute:
    SELECT 1 FROM PENDING_AGR 
    WHERE START_DATE_TIME_KEY < @end AND END_DATE_TIME_KEY > @start
    If this step returns rows, then this AUDIT_KEY has not been processed by the aggregation engine, and you cannot pull data for it from aggregate tables.

  3. To select aggregated data for export:

  4. If step 2 returns no rows, then you can select data for all aggregates:
    SELECT * FROM AG2_<view> 
    WHERE DATE_TIME_KEY BETWEEN @start AND @end

Performance considerations

When creating queries to export data, Genesys recommends that you avoid full-table scans, as this can impact Genesys Info Mart performance.

Script Description
make_export_indexes_mssql.sql Example script to create indexes for Info Mart tables. Applies to MSSQL deployments.

Feedback

Comment on this article:

blog comments powered by Disqus
This page was last modified on May 25, 2016, at 15:06.