Reporting And Analytics Aggregates
Also known as RAA. An optional Genesys Info Mart process that creates and populates predefined aggregation tables and views within an Info Mart database. RAA aggregation tables and views provide the metrics that summarize contact center activity to facilitate reporting, and serve as the primary source of data for the GI2 and Genesys CX Insights reports. RAA is required for GI2 and Genesys CX Insights environments.
How Do I Customize Queries and Hierarchies?
This page demonstrates how to customize the existing Reporting and Analytics Aggregates (RAA) queries and hierarchies to:
- Replace the definitions of existing measures with custom definitions.
- Aggregate additional measures.
- Define other joins to Info Mart tables.
- Add a WHERE qualification to RAA queries.
- Rename the aggregation tables.
- Define the aggregation levels that RAA will aggregate.
- Define which aggregation levels should be tables and which should be views within Info Mart.
Building RAA Queries
The RAA aggregation archive (GIMAgg.jar) includes several data-definition files that create the aggregation queries—language files that define the identifiers and macros that are used, templates, and other supporting files—all of which have been created using the Scheme programming language (a high-level dialect of the Lisp programming language). To customize aggregation, you must have a clear understanding of Scheme:
Using Scheme to Build RAA Queries
Understand that editing the files in the aggregation archive is not a supported feature. Nonetheless, this page provides some fundamental information on how the RAA queries are written. From there, you can extrapolate how to tailor them—at your own discretion—to better meet a specific business need.
Out of the box, RAA includes the following query families:
These query families correspond directly to the hierarchies that are described in Disposition Measure Hierarchies, and share similar names. (Hierarchies, however, are all prefaced with “H_”.)
Internal coding for each query family further differentiates family members along the seven time intervals (see Aggregation Intervals).
To modify a query’s family, you should be familiar, at minimum, with the query’s top-level definition, which is stored in a select-*.ss Scheme file inside the aggregation Java archive. There is one such file for each query family; for example:
- select-AGENT.ss stores the top-level definition for the AGENT query.
- select-AGENT_CAMPAIGN.ss stores the top-level definition for the AGENT_CAMPAIGN query.
These files reference macros that are defined in other, lower levels of query definition that reside in language and other ss files. RAA queries are complex.
Customize Aggregation Using the Patch-Aggregation File
For aggregation that operates in integrated mode, you can customize aggregation by creating / editing a Patch-Aggregation file (patch-agg.ss), which you must place in the Genesys Info Mart folder in which the gim_etl_server batch script is located. For aggregation that operates in autonomous mode, place the patch-agg.ss file in the Genesys Info Mart working directory of the java process that runs standalone aggregation—the Genesys Info Mart root directory.
See the following sections for more information:
Loading the patch-agg.ss file
The following procedure describes the steps you must follow to load the patch-agg.ss file.
Editing the patch-agg.ss file
In the patch-agg.ss file, you specify the query modifications that will override or add to the existing Genesys-provided query definitions. Use the instructions in this patch file to add, customize, or correct metric definitions.
The following code in the patch-agg.ss file provides one example that updates the ID query.
This example relies on a contact-center configuration that attaches agent cost to each interaction and records this information in the AGENT_COST column of the IRF_USER_DATA_GEN_1 table. AGENT_COST is a custom attached-data measure that you must configure before you can add it to the aggregation query.
This code accomplishes the following:
- Adds the AGENT_COST metric to the ID query.
- Replaces the definition of the existing INVITE metric to a new definition that tallies ringing interactions only (instead of ringing and dialing interactions).
- Adds a join to a user-defined, attached-data Info Mart table that is named IRF_USER_DATA_GEN_1 (from which the AGENT_COST metric is sourced in this example).
When this file is loaded, the aggregation engine will follow these instructions and regularly populate the new and changed columns in the AGT_ID_* group of tables and views, as notifications of newly transformed data are sent to the RAA internal queue.
Special Runtime Parameters for Customization The parameters that are listed in the Table Special Runtime Parameters for Customization and Debugging are not described elsewhere within the RAA documentation. They are provided to aid in the construction and debugging of your customized aggregation queries.
Runtime Parameter Description Special Runtime Parameters for Customization and Debugging allowDestructiveDDL Specifies whether the aggregation engine can use destructive data-definition language (DDL)—such as drop table—to delete and recreate database objects as needed. This parameter uses a date range to limit RAA issuance of destructive operations to two days before and after the date that you specify with this parameter. You must specify the date value in YYYY-MM-DD format.
Dropping and recreating aggregation tables would be necessary, for instance, if you customized a hierarchy to add new dimensions in a database created with release 8.1.103 or earlier. Setting this option is unnecessary for standard aggregation operations.
checkQuery Validates the specified query against Info Mart. The query can be a Genesys-provided query or a custom query.
evaluateFile Evaluates the specified Scheme file within the context of aggregation, enabling you to perform debugging without the use of any tools and without connection to Info Mart.
For example:TipYou can also use Scheme printf and display statements to output results for debugging purposes.
Limitations of the patch-agg File
By following this format, you can add additional alter-query statements to the patch file. If you include more than one alter-query statement for the same query, make sure that their instructions do not conflict; otherwise, RAA will use the last statement’s definition. Also, you can include as many add and replace statements as needed within an alter-query statement to attain the desired level of query modification. Lastly, at this time, you cannot use this file to accomplish any of the following:
- Remove metrics from the query (although you can replace their definition with a constant, such as “0”, or build a new query altogether).
- Restrict changes only to a particular member of a query family. For instance, you cannot specify to update the query definition for the AGT_I_SESS_STATE_SUBHR table without also simultaneously affecting the query definition for all other family members (AGT_I_SESS_STATE_HOUR and AGT_I_SESS_STATE_DAY).
- Add another join to the DATE_TIME dimension; only one join is permitted and this one join is already used.
Format of the patch-agg.ss file
The commands that you write in the patch-agg.ss file must obey syntax rules and be written by using the identifiers that RAA recognizes. This section provides syntax for the following types of customizations, including examples for each type:
- Adding measures to query definitions — Syntax
- queryName is the name of any RAA-defined query.
- mNameX and mNameY are the names of measures that you want to add to the query definition.
- mDefX and mDefY (defined in the preceding syntax by the concatenation of three expressions: expr1, expr2, and expr3) are the measures’ definitions, written in the SQL format, and bound by double quotation marks or parentheses. In this example, expr2 is a call to a procedure that returns a string value.
- Joining other tables to those within the query definition — Syntax
- joinType describes the type of table join—either of the following:
- TableX and TableY are the names of the tables that are to be joined. These tables could be custom tables. For performance reasons, Genesys recommends that they exist within Info Mart.
- aliasX and aliasY are the aliases for TableX and TableY, respectively
TipYou must provide an alias. This alias must differ from the reserved aliases that are used within the select-*.ss files if you are adding a new table join to the query or defining a second (or third) join to a table that already exists within the query.
- joinCondition describes how two tables are joined, written in SQL format and bound by double quotation marks or parenthesis.
ExampleTipThe irf alias that is used in this example is already defined in the select-ID.ss file.
- joinType describes the type of table join—either of the following:
- Adding WHERE qualifications to queries — Syntax
- whereQualification is the expression to be added to the WHERE clause of the query. Place the expression in double quotation marks to code advanced SQL in free form.
You cannot delete or modify expressions in the WHERE clause using add-predicate.
- Altering the definitions of measures within queries — Syntax
- Editing joins within queries — Syntax
- Adding dimensions to queries — Syntax
Exercise caution when you add new dimensions to an existing aggregation hierarchy that is populated. To keep the hierarchy’s data homogeneous, you must delete and reaggregate all of the data for all aggregation levels of the hierarchy, thereby ensuring that existing records are dimensioned by the new addition.
RAA requires that you specify special-permission within the Scheme code to perform this destructive operation. In addition, consider setting the allow DestructiveDDL runtime parameter (described in Special Runtime Parameters for Customization) to true.
The syntax for adding dimensions to a query is:
- dimNameX and dimNameY are two dimensions that you want to add to the query definition.
- Tbl.Col are the table (or alias) and column, respectively, that hold the dimension. If the name of the dimension and its schema location are identical, you do not have to include Tbl.Col.
- TableX and TableY are the names of the tables that are to be joined. These can be custom tables. For performance reasons, Genesys recommends that you enter names of tables that exist within Info Mart.
- Modifying hierarchies — Syntax
The following syntax provides only some of the identifiers that are used with the alter-hierarchy command:
- hierName is the name of the aggregation hierarchy that you want to alter. For information about the Genesys-provided hierarchies, see What is an Aggregation Hierarchy?.
- AgLv1 AgLv2 .. AgLvX are the levels that you want RAA to aggregate. For example, HOUR, DAY, MONTH.
- AgTb1 AgTb2 .. AgTbX are those aggregation levels that RAA will materialize as table objects in Info Mart, instead of views. For example, (materialize: HOUR DAY) indicates that only the HOUR and DAY levels will exist as tables. All other levels will exist as views. You can also specify none.
- baseQuery defines what entity the hierarchy is based on—either data from an Info Mart FACT table (in which case you specify fact) or the name of a defined query. The H_AGENT_GRP hierarchy, for example, is based on the AGENT query. CustomText defines the template by which RAA names the aggregation tables and views. Genesys recommends that this template include %QUERY% and %LEVEL% somewhere within the custom text. For example: KJM_%QUERY%_%LEVEL%_72099
Not all combinations of views and tables will work within the materialize statement. Within a hierarchy, table creation cannot be dependent on a view; instead, table creation must be based directly from data that is pulled from FACT tables. Otherwise dependencies (not covered in this document) must also be updated. So, for instance, the following alter-hierarchy statement will not yield results, if this is the only change that is made to Scheme files:
For the interval-based hierarchies, hour results are derived from subhour results. In this example, the SUBHOUR aggregation level is defined to exist as a view to the detriment of this customization example.
Views typically have slower performance than tables. Perhaps you would prefer that the subhour views within Info Mart be tables. The following example makes the subhour entity for the H_AGENT hierarchy a table that contains subhour aggregations. This coding also changes the template by which aggregation entities are named and reduces the number of aggregation levels processed. The subhour table that RAA creates and populates, for example, is AG3_AGENT_SUBHR_TEST, instead of AG2_AGENT_SUBHR. Notice also that WEEK is omitted from agg-levels.
- Adding SQL strings to Scheme aggregate definition — Syntax
The following syntax examples illustrate how you can use the Scheme macro inline-sql to add, drop, or replace specific SQL statements in Scheme aggregate definitions.
- To specify an aggregator Scheme file description without alias:
(inline-sql <SQL statement>)
- To specify an aggregator Scheme file description with alias:
(inline-sql left-outer-join-alias <SQL statement>)
- To specify a drop statement in the patch-agg.ss Scheme patch file (if you've specified an alias):
(drop (inline-sql left-outer-join-alias))
- To specify a replace statement in the patch-agg.ss Scheme patch file (if you've specified an alias):
(replace (inline-sql left-outer-join-alias <SQL statement>))
- To specify an aggregator Scheme file description without alias:
- it.INTERACTION_SUBTYPE_CODE in "('INTERNALCOLLABORATIONREPLY','INBOUNDCOLLABORATIONREPLY')"
- it.INTERACTION_SUBTYPE_CODE in (\'INTERNALCOLLABORATIONREPLY\'\,\'INBOUNDCOLLABORATIONREPLY\')
- it.INTERACTION_SUBTYPE_CODE in ("'INTERNALCOLLABORATIONREPLY'"\,"'INBOUNDCOLLABORATIONREPLY'