Key-Value Pair
Also known as a KVP. A data structure that is used to communicate or store a piece of information. A KVP consists of a key, whose value is a string, and a value, which may be any of a variety of data types, including a key-value set (thus, making the structure recursive). The key identifies the meaning of the data that is contained in the value.
Glossary
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
Genesys Info Mart Database Schema
The Genesys Info Mart database schema contains the dimensions and facts that the extract, transform, and load (ETL) loads. The schema also includes five categories of internal tables that ETL jobs use for data processing.
Genesys Info Mart Database Schema Tables
Specifically, this database schema contains the following tables:
- Dimension tables
- Fact tables
- Control tables
- GIDB tables
- Merge tables
- Temporary tables
- Staging tables
Many fact tables and the aggregate tables that come with either the Genesys historical reporting presentation layer (Genesys CX Insights [GCXI]) or the Reporting and Analytics Aggregates (RAA) package share the same dimension tables. The Genesys Info Mart ETL frequently loads the dimension and fact tables throughout the day to enable reporting on both recent and historical contact center activity. For more information, see Fact Tables and Dimension Tables.
Whereas most control (service) tables are intended for internal purposes, certain CTL_* tables contain operational data that is helpful to system integrators and system administrators in their data validation and troubleshooting tasks. For more information, see Info Mart Service and Control Tables.
GIDB stands for Global Interaction Database. This part of the Info Mart database is designed to keep all records that are extracted from various IDBs and subsequently merged, so that coherent reporting data at the lowest level of detail is gathered from the entire contact center and stored within a single data warehouse for as long as customers require detailed data. Genesys Info Mart further processes (transforms) GIDB data to create data representations useful for end-user reports. For more information, see GIDB Tables.
Merge tables within the Info Mart database are intended for internal purposes only. They provide temporary storage for those interaction records that may be subject to the merge process. For more information, see Merge Tables.
Most staging (STG_*) tables are intended for internal purposes only, with the exception of two tables that are useful for troubleshooting errors in the source data that cause ETL jobs to either generate exceptions or fail. For more information, see Info Mart Service and Control Tables.
All temporary (TMP_*) tables are intended for internal purposes only. For more information, see Temporary Tables.
The fact and dimension tables are depicted in the “Info Mart Database Owner/Schema” portion of the following diagram.
Dimension Views
The Genesys Info Mart database contains read-only views to present certain configuration details, based on data in GIDB tables. These views provide configuration data that is not present in any tables in the dimensional model, but that Genesys Info Mart extracts to GIDB and uses for transformation of other data. Downstream reporting applications should query configuration data in Genesys Info Mart by using these views. In essence, these views are dimensions that serve the same purpose as dimension tables: to describe facts with attributes of a contact center environment.
The Genesys Info Mart database schema contains the following predefined dimension views:
The Genesys Info Mart Data Organization and Tenant Views diagram shows dimension views along with dimension tables.
User Data Tables
Genesys Info Mart provides both predefined and custom tables, to store user data supplied with interactions. This data allows interaction resource facts (IRFs) and, starting with release 8.1.2, mediation segment facts (MSFs) to be described by deployment-specific business attributes that characterize the interaction, such as service type and customer segment. A unified processing mechanism extracts deployment-specific business attributes from both call-based TEvents or Multimedia reporting protocol events (data that is attached by T-Server or Interaction Server, respectively) and EventUserEvents or EventCustomReporting events (data that is attached by other Genesys applications). Because the same logic is used to process these two types of data, they are collectively referred to as user data.
A customizable database schema enables you to treat each key-value pair (KVP) field as either a fact or a dimension and to store user-data KVPs in fact and dimension tables.
The following tables facilitate user-data processing:
The target table for storage of user data depends on whether the user-data key name is predefined or custom, and whether the value is of high or low cardinality.
- High-cardinality user data refers to data for which there can be a very large number of possible values. A Customer ID number is an example of high-cardinality user data.
- Low-cardinality user data refers to data that has a limited range of possible values; there may be multiple values of a specific type for a single interaction. Customer segment, service type, and service subtype are good examples of low-cardinality user data.
The following dimension, fact, and fact extension tables store user data:
- INTERACTION_DESCRIPTOR — This table is provided with the default schema to store Genesys-defined, low-cardinality KVPs, such as service type and customer segment. This table requires no customization.
- IRF_USER_DATA_GEN_1 — This table is provided with the default schema to store Genesys-defined, high-cardinality KVPs, such as case ID and customer ID. This table requires no customization.
- IRF_USER_DATA_CUST_* — Any number of IRF_USER_DATA_CUST_* fact extension tables can be added to the Info Mart schema to store high-cardinality user data. Genesys provides a template script for table creation. Use database performance considerations as your major guidance in determining the number of user-data tables that you deploy in your environment.
- USER_DATA_CUST_DIM_* — Up to 800 USER_DATA_CUST_DIM_* tables can be added to the Info Mart schema to store low-cardinality user data. Genesys provides a template script for table creation. The IRF_USER_DATA_KEYS table has to be expanded accordingly to facilitate processing of low-cardinality user data.
For information about the template script and instructions on how to add custom user-data tables to the schema, refer to Preparing Custom User-Data Storage in the Deployment Guide.
The Deployment Guide also provides information about the CTL_UD_TO_UDE_MAPPING and CTL_UDE_KEYS_TO_DIM_MAPPING service tables that are used for configuring user-data processing and storage.
Time-Related Fields
The Genesys Info Mart model allows for uniform treatment of time references. The start and end timestamps in most fact tables represent the number of seconds that have elapsed since midnight of January 1, 1970. The start and end date and time in most tables are also stored as dimension references to the DATE_TIME dimension.
The following four columns are standard in most of the fact tables:
- START_DATE_TIME_KEY — Identifies the start of a 15-minute interval in which the fact began. 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 fact ended. 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.
- START_TS — The date and time at which the fact began, as a Coordinated Universal Time (UTC) value. The UTC value is the number of seconds that have elapsed since midnight on January 1, 1970, not counting leap seconds (also known as UNIX time).
- END_TS — The date and time at which the fact ended, as a Coordinated Universal Time (UTC) value. The UTC value is the number of seconds that have elapsed since midnight on January 1, 1970, not counting leap seconds (also known as UNIX time).