Jump to: navigation, search

Extract, Transform, And Load

Also known as ETL. The ETL processes extract data from various data sources; transform the data into a format and structure that is suitable for subsequent business purposes; and load the data into a target data store (other database, data mart, or data warehouse).



Glossary

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Universal Routing Server

Also known as URS. The server that is used by Universal Routing that automatically executes routing-strategy instructions and distributes incoming customer interactions among contact-center agents. Previously known as Interaction Router.



Glossary

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Solution Control Interface

Also known as SCI. A Genesys Framework component that is used to administer Genesys solutions—for example, to start or stop the solution, view logs, configure event-triggered alarms, and provide real-time status information for all Genesys applications.



Glossary

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

High Availability

Also known as HA. The use of Redundancy to enable contact centers to minimize interruptions that are due to hardware, software, or network connectivity issues.



Glossary

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Extract, Transform, And Load

Also known as ETL. The ETL processes extract data from various data sources; transform the data into a format and structure that is suitable for subsequent business purposes; and load the data into a target data store (other database, data mart, or data warehouse).



Glossary

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Outbound Contact Server

Also known as OCS. The core component of the Outbound Contact Solution that provides automated dialing and call progress detection, so that an agent is required only when a customer is connected. OCS also intelligently uses customer data to ensure that campaigns are contacting the right customers, not just a large number of customers.



Glossary

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Interaction Database

Also known as IDB. The database that stores data about contact-center interactions and resources at a granular level of detail.
See also Interaction Concentrator.



Glossary

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Interaction Concentrator

Also known as ICON. A Genesys product that collects and stores detailed data from various sources in a contact center that is empowered by using Genesys software. Downstream reporting systems can access Interaction Concentrator data in near–real time.
Operating on top of Genesys Framework, the product consists of a server application that is called ICON and a database that is called Interaction Database (IDB). The server receives data from data sources such as Configuration Server, T-Server, or particular Genesys solutions; it then stores this data in IDB by using Genesys DB Server.



Glossary

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Managing ICON and data sources

This page provides information about managing the Interaction Concentrator (ICON) instances and the data source applications that provide data to Genesys Info Mart. The immediate sources of data for Genesys Info Mart are Interaction Database (IDB), which are populated by ICON applications. Depending on their configured role, the ICON instances get their data from Configuration Server, T-Server, Interaction Server, or Outbound Contact Server (OCS). In this chapter, the term data source refers to the upstream data provider—the source of data for ICON.

For additional information about managing ICON and data sources to work around extraction or transformation problems caused by data-source unavailability, see Recovering from data-source unavailability.

Restarting ICON

This section describes data-quality considerations that relate to interrupted ICON processing.

Special considerations for Multimedia

Genesys Info Mart requires that the ICON calls-in-the-past and om-force-adata options be set to true. This means that, if multimedia interactions begin while ICON is down or has no connection to Interaction Server, ICON reconstructs operational data and stores a user data snapshot for multimedia interactions that are already in progress, beginning with the next party that ICON sees being added to the interaction. The ETL process extracts the reconstructed data in the usual way, based on a comparison of the extraction high-water mark against the record-creation timestamp. However, be aware that, in these situations, information about previous parties and first values of user data keys might be missing or inaccurate.

The consequences may include:

  • If Interaction Server continues to run, multimedia interactions that end while the Multimedia details ICON is down are forever reported as active. Since ICON did not see these interactions end, Info Mart cannot report them as ended.
  • ICON records interaction-related data (IR, CALL, PARTY) for a never-before-seen multimedia interaction beginning when ICON sees a party being added to the interaction.
  • End times are shown for a party only if the party was created and ended within the same ICON session.
  • If ICON sees an interaction end, but the interaction is neither currently known nor known from a previous ICON session, then ICON does not record anything about this interaction.

Avoiding data quality issues

As noted above, some data quality issues regarding multimedia interactions can occur when the interaction data was recorded during different ICON sessions, and the high availability (HA) architecture is not in use. Some information can be lost between the sessions. However, sometimes it is necessary to stop an ICON to apply upgrades, or so that it can become aware of configuration changes when it restarts. It is possible to avoid nearly all of these data quality issues across a planned ICON outage by using the following procedure.

Procedure: Restarting a Multimedia details ICON

Purpose: To minimize data quality issues following a planned outage and restart of a Multimedia details ICON.

Genesys recommends that you perform this procedure during a period of agent inactivity (for example, at the end of one shift and before the next shift starts) to minimize disruption to the agents. When Interaction Server is stopped, all agents are logged out of Interaction Server, and any interactions that were actively on their desktops are returned to Interaction Queues or Workbins, so it is best to follow the procedure when agents are not in the middle of handling interactions.

Steps

  1. Use the Solution Control Interface (SCI) to stop Interaction Server.

Stopping Interaction Server moves active interactions to a “home” state, returning them to Interaction Queues or Workbins, and ICON will be aware of this.

  1. Stop the Multimedia details ICON.
  2. When you have completed the ICON maintenance, restart the Multimedia details ICON.
  3. Use SCI to restart Interaction Server.

Residual Virtual Queue Considerations—Following the procedure Restarting a Multimedia details ICON eliminates nearly all data quality issues that can occur when a multimedia interaction spans more than one ICON session. However, there are still some issues related to reporting on virtual queues:

  • It is possible that some virtual queue activity that occurs during the time frame of the procedure will be lost, for the following reason: Interactions that were in virtual queues when Interaction Server was stopped are returned to Interaction Queues. The events that Universal Routing Server (URS) sends to indicate that the interactions were cleared from virtual queues may not reach Interaction Server before Interaction Server stops its connection to URS. Therefore, ICON data may not show that the interaction left the virtual queue.
  • When Interaction Server is restarted, some interactions may be placed in virtual queues before ICON has successfully re-established its connection to Interaction Server. However, ICON will have established its connection to Interaction Server by the time agents begin logging in, so no agent activity is lost, not even from agents who receive interactions from those virtual queues.

High Availability Recommendation—To avoid data quality issues if a scheduled restart of ICON cannot be performed without affecting multimedia interaction-handling, or if ICON stops unexpectedly, Genesys recommends that you use an HA architecture for multimedia. For more information about HA in Genesys Info Mart, see the chapter about HA in the Genesys Info Mart Deployment Guide.

Purging IDB

The size of IDB is one of the important factors that affects ICON and Genesys Info Mart operational performance. You should periodically purge old data from the IDBs that Genesys Info Mart uses as sources of data.

This section provides recommendations and considerations relevant to Genesys Info Mart data source requirements, to prevent accidental purging of IDB data before Genesys Info Mart has an opportunity to extract it. In particular, this appendix provides guidance for selecting the smallest safe value to specify for retaining IDB data.

For detailed information about purging IDB, see Purge Procedures (in the Interaction Concentrator User's Guide), which describes using special stored procedures. For more information about purging the Info Mart database, see Purging the Info Mart database in the Genesys Info Mart Operations Guide..

Interaction Concentrator purge procedures

Genesys Info Mart does not provide automated purging of old IDB data. However, various releases of Interaction Concentrator provide stored procedures that purge old IDB data. You can use your RDBMS utility, or write your own program or stored procedure, to invoke the Interaction Concentrator functionality to purge IDB data in a way that:

  • Avoids deleting data that Genesys Info Mart has not yet extracted.
  • Retains enough historical data to allow for error recovery and problem determination.

IDB purge frequency

Genesys Info Mart recommends that you run the Interaction Concentrator stored procedure(s) to purge old IDB data once a day, during off-peak hours, when contact center activity is low, and when Genesys Info Mart is not accessing IDB. This means you should run the stored procedures at the same time that the Genesys Info Mart runs its daily maintenance job, Job_MaintainGIM. Interaction Concentrator stored procedures may take some time to finish, so run them as early as possible to allow them to complete before Genesys Info Mart starts the next extract, transform, and load (ETL) cycle.

IDB data retention

The amount of historical data you are able to retain in IDB depends on the database server’s hardware resources, such as memory and disk space, and disk subsystem performance. Because Genesys Info Mart initially copies almost all data from IDB into Global Interaction Database (GIDB) tables in the Info Mart schema, it is not necessary to retain IDB data for long periods. Nevertheless, for prudence, you should retain as much IDB data as possible, without impacting either ICON’s operational performance or Genesys Info Mart’s data extraction performance.

The following scenarios require special consideration when determining the number of days to retain IDB data:

  • A new Genesys Info Mart deployment—In a new Genesys Info Mart deployment, where ICON stores data prior to Genesys Info Mart’s first ETL cycle, there is a backlog of IDB data waiting to be processed. By default, Genesys Info Mart limits the amount of data extracted during each ETL cycle. While Genesys Info Mart works through a backlog of data, you might need to temporarily increase the IDB data retention period to take this backlog into account. You may also choose to suspend purging IDB data until Genesys Info Mart has had an opportunity to extract the backlog of data.
  • ETL failure—If some network, hardware, or software outage occurs that prevents Genesys Info Mart from maintaining its regular ETL schedule, consider temporarily increasing the IDB data retention period to account for the time that Genesys Info Mart has not been able to extract IDB data. You may also choose to suspend purging IDB data until Genesys Info Mart has had an opportunity to extract the backlog of data.
  • IDB data archiving—If your environment requires long-term storage of IDB data (longer than ICON’s operational performance or Genesys Info Mart’s data extraction performance permits), consider archiving IDB data. This allows the operational data store used by ICON and Genesys Info Mart to be small enough to allow acceptable and predictable performance, while providing an alternate data store for long-term archiving of IDB data. Work with your Database Administrator to determine an appropriate archival strategy.

Determining the retention period

The sample procedure documented below gives an estimate for the data retention threshold for IDB data. This procedure takes into account the last time that Genesys Info Mart successfully extracted all of the data for an ETL cycle, and whether Genesys Info Mart is limiting the amount of data it extracts while it processes a backlog of data. This procedure also includes a safety buffer of seven days.

In an environment where Genesys Info Mart is maintaining a regular schedule of ETL cycles that run every 60 minutes or less, Genesys recommends that the number of days to retain IDB data is:

  • For Voice and Outbound Contact details, between 7 and 30 days.
  • For Multimedia details, the maximum number of days for which Genesys Info Mart maintains memory of inactive interaction threads. This value is specified by the max-thread-duration-after-inactive-in-days configuration option, which has a default value of 31 days.

The procedure documented below will never return fewer than 7 days. If you are able to store more than 7 days of IDB data, you may choose to use a value larger than what is returned.

Important
If the Genesys Info Mart ETL cycles have not yet begun, this procedure returns a large value to prevent the accidental purging of data that has not yet been extracted.

Run an RDBMS-specific SQL statement against your Info Mart database to return the minimum value for the data retention threshold you will provide as an input parameter for the Interaction Concentrator purge procedure. To ensure an accurate calculation, issue the SQL statements prior to running each purge procedure. Also, make sure to log in using the Genesys Info Mart database Owner account before issuing the statements.

Example SQL Query—The following SQL query shows how to run a query against a Microsoft SQL Server–based Info Mart database. If you are using a different RDBMS, convert this query as appropriate.

Important
If you have performed only one ETL cycle, this query returns the result 999. However, if you have run more than one ETL cycle, it functions correctly.
SELECT cast(max(x) AS decimal) AS MINIMUM_DAYS FROM (
/* number of days since 1st extract preceding last completed transformation */
/* (and following the prior completed transformation) or 999, if none */
   SELECT coalesce((
      SELECT round(datediff(second, min(start_time), 
                   CURRENT_TIMESTAMP)/86400.0, 0) + 7
         FROM admin_etl_job_history
         WHERE job_name LIKE '%Extract%'
         AND start_time < 
             (SELECT max(start_time)
                 FROM admin_etl_job_history 
                 WHERE job_name = 'Job_TransformGIM' AND STATUS = 'COMPLETE')
         AND start_time > 
             (SELECT max(start_time) 
                 FROM admin_etl_job_history 
                 WHERE job_name = 'Job_TransformGIM' AND STATUS = 'COMPLETE'
                 AND start_time < 
                     (SELECT max(start_time)
                         FROM admin_etl_job_history 
                         WHERE job_name = 'Job_TransformGIM'
                          AND STATUS = 'COMPLETE'))
      ), 999) AS x
 
) z;

Feedback

Comment on this article:

blog comments powered by Disqus
This page was last modified on October 10, 2018, at 08:25.