Genesys: Why Get Duplicate Agent/Queue in Custom Reports?

Product: Genesys
Module: DMA, CCA, Brio, Crystal Report, Hyperion

Many report designers do not understand the reason of primary key index, which is not limited to Genesys product.

As such, when the reports designed using other fields which could have duplicate, then it is possible to get duplicate username, queue, route point, agent group, etc.

In this example, I will use agent report. Many call centers use other fields for agent, such as Genesys login ID, phone login ID, Genesys Employee ID, etc. In the reporting, especially for CCA (DMA), the primary key is Object_ID, which shows agent's name, and Genesys login ID (username).

In directly, many designers use the human name, and username as filtering criteria, which will trigger duplicate records in following situation:
1. Agent re-created. For example seasonal worker, and people re-join the company
2. Agent change its name, may be typo in the name, or title
3. Agent Genesys login ID changed

Solution
1. Join the report between O_, V_, T_, S_ using column OBJECT_ID
2. Speak to user/manager if they are asking something which can't meet with standard CCA tables, such as join with cfg_ tables in CONFIG database
3. Confirmed with user whether want to display DELETED user. If they don't want to display DELETED user, then this is another way to quickly fix the duplicate record without joining with OBJECT_ID. The solution is to add additional filter (WHERE) to say "...and delete_ts is null"

Genesys Report Administration - Handling of Deleted Agent

Product: Genesys
Component: Configuration Manager, Stat Server, DMA, CCA

Call center is one of the industry with high turnaround in HR in term of agents. Therefore, it is crucial to design a business process to handle this situation, and will not degrade the Genesys system performance

Facts to consider
1. If agent user account is left in CME, and unused, Stat Server and DataSourcer will work actively to gather its statistics. Server utilization impacted, and waste of space in DATAMART database
2. If agent user account is disabled in CME, the situation still same. Stat Server and DataSourcer are still gathering its stat. Same result as above, with the extra security control see by supervisor
3. If agent user account is disabled and set to no privilege (regardless enable and disable), then Stat Server will get privilege error, and DataSource will still populate a value into database. Although Stat Server save time in gathering statistics, but overhead in error handling
4. If agent user account deleted from CME, custom reports which designed to pick custom property from CME will not able to display historical data for that agent. This does reduce the load in Stat server, DataSourcer, and save storage in DATAMART
5. If agent user account re-created many times in CME, CCA reports will display the agent name multiple times. Custom reports must be designed to handle this situation. This does reduce load in Stat Server and DataSourcer. Moreover, save space in DATAMART

So the recommended setup is as follow
1. In CME, create a folder to keep old agent login for 1 year. The PBX agent login will be removed, but account can be reused if he returned
2. In CME, create a folder to hold agents pending for deletion. Once the agent deleted, Stat Server, and DataSourcer will not gather its statistics
3. In database, create a custom table to hold custom properties of agent, such as PBX login, e-mail address, external login
4. Ensure custom report does not select from CME tables (config database cfg_person, cfg_agent_login), such as employee_id, login_code, address_line1. These information are not store in ODS, or DATAMART (in CCA module)
5. If custom report needed, create a custom table to store these columns
5.1. Create a process to sync the data daily (or hourly) from cfg_person, cfg_agent_login
5.2. The custom table must have TS_CREATE, TS_MODIFY, TS_DELETE, dbid, tenant_dbid (if multiple PBX)
5.3. Gets agent TS_CREATE and TS_DELETE from ODS database, and refers to table OBJECT, column ADD_TIME, DELETE_TIME
5.4. Sync program will populate TS_MODIFY with local time
5.5. Create primary key suitable primary key according to custom report. My recommendation is dbid, username, and add_time
6. The sync program can be written as shell script, database store procedure (and database job), or regular programming (C#, Java)
7. In the custom reporting, join with this custom table and always ensure TIME_KEY or BEGIN_TIME is within ADD_TIME and DELETE_TIME (if not null), or larger than ADD_TIME (if DELETE_TIME is null)

E.g.
BEGIN_TIME between ADD_TIME and DELETE_TIME
OR BEGIN_TIME >= ADD_TIME and DELETE_TIME is null

8. In DMA, click on Object tab, and double click on any object to bring up object selection screen
9. Click on the checkbox to remove the checkbox on agents which have been deleted
10. This will eliminate DataSourcer from gathering its statistics, and reduces its load
11. Nonetheless, Stat Server will still monitor the agent, so there is no improvement in reducing Stat server's load
12. Not recommend to store the historical value for the extra fields (address_line1). Only store the latest value. This is to prevent duplicate record when joining with other tables
13. If end user decided to display only active agent, create a new report to use custom view which points to O_AGENT_NO_AGG, O_AGENT_DAY, O_AGENT_WEEK, O_AGENT_MONTH, O_AGENT_QUARTER, O_AGENT_YEAR, with following criteria


CREATE VIEW Z_O_AGENT_NO_AGG
SELECT * FROM O_AGENT_NO_AGG where DELETE_TIME is null

14. If end user decided to display agents deleted in last 31 days, and active agents, then create the view with a little longer in the WHERE clause


CREATE VIEW Z_O_AGENT_NO_AGG
SELECT * FROM O_AGENT_NO_AGG where DELETE_TIME is null or DELETE_TIME >= sysdate - 31

Note: Above is for Oracle database ver 7.3 onward

15. Standard report does filter number of days in interval report. It is a hidden text box which can easily modify. If I'm not mistaken, it is 90 days

16. This view filter applies to Queue, Route Point, Calling List, Campaign, etc. Point all the report to these custom view instead of standard views


Benefits:
1. DATAMART size will not store the new statistics for deleted agent

2. Supervisor has the luxury to re-use existing ID if agent return within 1 year. The DATAMART only contains extra 0 value data for these old agents
3. Parameter screen will show up faster due to filtering of unused data, especially for agent listing
4. End user can keep track of additional columns without linking the tables to CONFIG database, which only contain real time info

Other Info:
1. It is common to have business requirement to show additional agent info configured in CME to show up in custom reports. These additional information are not store in ODS, nor DATAMART, but in CONFIG database, e.g. tables cfg_person, cfg_login_info, cfg_agent_login. Above custom table can be used to copy the columns from these 3 tables so that historical report not affected by object deletion or permission change in CME

Genesys ETL Run-time contains inappropriate object or statistic

Product: Genesys CTI
Module: ETL Runtime Service

Encounter following error in log ETL_Service.20121213_214131_050.log?

15:39:30.593 Src.ChunkData.1 Chunk#1_2075 contains inappropriate object or statistic


Following is the step to troubleshoot

Connects to ODS database

Find out the schedule # from chunk #
select  * from ol_chunk_log where log_id = 2075


LOG_ID SCHEDULE_ID BEGIN_TIME              UPDATE_TIME             CHUNK_TYPE TABLE_NAME IS_LAST_CHUNK RECEIVE_TIME            TRANSFERRED_TIME        DELETE_TIME IS_NULL_CHUNK DATA_RECORDS_NUM
------ ----------- ----------------------- ----------------------- ---------- ---------- ------------- ----------------------- ----------------------- ----------- ------------- ----------------
2075   9           2010-09-26 20:00:00.000 2010-09-26 20:15:00.000 NULL       OL_DATA3   0             2010-09-26 20:15:02.000 2010-09-26 16:19:06.560 NULL        0             227

Its value is 9

Use schedule # to find layout #
select * from ol_schedule where schedule_id = 9


SCHEDULE_ID LAYOUT_ID TIME_PROFILE_ID START_TIME              STOP_TIME REFRESH_RATE IS_NEEDS_TRANSFER FREEZE_TIME
----------- --------- --------------- ----------------------- --------- ------------ ----------------- -----------------------
9           9         1               2010-06-07 13:36:26.670 NULL      NULL         1                 2010-06-07 13:48:07.297

Its value is 9. This value is very often same as schedule #

select * from ol_report_layout where layout_id = 9


LAYOUT_ID OBJECT_TYPE_ID TENANT_ID LAYOUT_NAME    METAGROUP_CLASS METAGROUP_DB_ID ADD_TIME                DELETE_TIME             LAYOUT_DESCRIPTION           UPDATE_TIME TEMPLATE_NAME IS_BRKN
--------- -------------- --------- -------------- --------------- --------------- ----------------------- ----------------------- ---------------------------- ----------- ------------- -------
9         0              101       Agent_Layout_2 100             0               2010-06-10 11:42:40.147 2010-10-13 16:59:08.983 Agent Report Layout with AHT NULL        AGENT_AHT1    NULL

This means the chunk belongs to Report Layout Agent_Layout_2 (see DMA), and time of the record is 2010-06-07 13:36:36

Normally this error appear if report layout is deleted from DMA before ETL service able to pull the data into DATAMART/REPORT database. If you confirmed these can be deleted, then take a backup of the ODS database and execute following statement to delete all the entries belongs to "Agent_Layout_2"

detete from ol_report_layout where layout_id = 0

This is no way to undo from DMA, or ETL service. The only way to undo is to restore from database backup, or flashback (Oracle) or similar technology offer by database server