| Oracle® Business Intelligence Applications Installation and Configuration Guide Version 7.9.4 E10742-01 |
|
 Previous |
 Next |
| Oracle® Business Intelligence Applications Installation and Configuration Guide Version 7.9.4 E10742-01 |
|
Previous |
Next |
This chapter describes how to configure the Oracle Contact Center Telephony Analytics with Universal adapter to meet your business needs.
It contains the following topics:
Section 14.1, "Overview of Oracle Contact Center Telephony Analytics"
Section 14.2.1, "About the Dimension Key Resolution Process for Universal Source"
|
Note: For configuration steps that apply to all analytics modules (for example, Oracle Financial Analytics, Oracle HR Analytics, Oracle Sales Analytics, see Chapter 8, "Configuring Common Areas and Dimensions"). |
Oracle Contact Center Telephony Analytics tracks incoming and outgoing contacts and their resulting activities, and the agents performing contact center tasks. The Oracle Contact Center Telephony Analytics application is made up of these functional areas:
Representative Activities
Contact Center and Agent Performance
Contact Center Benchmark and Targets
Interactive Voice Response History
This chapter describes how to configure Contact Center Telephony Analytics for particular sources to meet your business needs, and contains the following topics:
Section 14.1, "Overview of Oracle Contact Center Telephony Analytics"
Section 14.2.1, "About the Dimension Key Resolution Process for Universal Source"
Section 14.2.4, "Setting Up The DATASOURCE_NUM_ID Parameter For The Universal Adapter"
Section 14.2.7, "About Configuring Contact Status Type Column"
Section 14.2.8, "Setting Up the Representative Activities Table"
Section 14.2.9, "Setting Up the Contact Center Performance Table"
Section 14.2.10, "Setting Up the Benchmarks and Targets Table"
Section 14.2.13, "Configuring Flags for Oracle Contact Center Telephony Analytics"
Section 14.2.14, "How to Exclude Representative Data for Post-Load Processing"
Dimension keys uniquely identify each record in a dimension table. The purpose of dimension keys is to relate a record in the dimension table to a record in the fact table. Therefore, the dimension key must be stored in both the dimension table and the fact table and resolved using the dimension table's INTEGRATION_ID and DATASOURCE_NUM_ID column values.
For universal business adapters, users supply the dimension INTEGRATION_ID and DATASOURCE_NUM_ID column values through a flat file interface. The same values for INTEGRATION_ID and DATASOURCE_NUM_ID are expected in both the dimension and fact business adapters so that the correct dimension key is resolved and loaded into the fact table.
The dimension key resolution process entails two steps:
Run the dimension table workflows to extract and load dimension records.
The dimension load mapping automatically creates a surrogate key for each record in the dimension table. This surrogate key value populates the dimension table's primary key column, which is referred to as the dimension WID. Similar to the INTEGRATION_ID column, which uniquely identifies the record within the source system, the dimension key uniquely identifies the record in the data warehouse dimension table.
Run the fact table workflows to extract and load fact records.
Records must contain the dimension ID column values for each fact record; these values must be the same values as the INTEGRATION_ID in the corresponding dimension tables.
The following sections describe these two steps in more detail by taking the example of one fact table (W_REP_ACTIVITY_F) and one dimension table (W_EVENT_TYPE_D). However, this process applies to all fact and dimension tables joined by a dimension key.
This task is explained by taking the example of the W_EVENT_TYPE_D table.
Loading the W_EVENT_TYPE_D table requires the following ETL processes:
The SDE_Universal_EventTypeDimension mapping extracts the data from file_event_type.csv and populates the W_EVENT_TYPE_DS staging table.
The SIL_EventTypeDimension mapping sources data from the staging table and passes it over to the Source Independent Load (SIL) mapping. The SIL mapping generates the surrogate key for each record in the staging table, then inserts it into W_EVENT_TYPE_D target table.
This task is explained by taking the example of the W_REP_ACTIVITY_F fact table.
Loading the W_REP_ACTIVITY_F fact table requires the following ETL processes:
The SDE_Universal_Rep_Activity mapping extracts the data from file_rep_activity.csv and populates the W_REP_ACTIVITY_FS staging table.
The SIL_Rep_ActivityFact mapping sources the data from the staging table, and the fact SIL mapplet resolves the dimension key by doing a lookup on W_EVENT_TYPE_D using the values supplied in the ACTIVITY_TYPE_ID column and the DATASOURCE_NUM_ID column. Then, the SIL populates the W_REP_ACTIVITY_F fact table.
Since the dimension *_ID values are supplied through the Universal Interface flat file, it is critical that you supply the same value for the INTEGRATION_ID in the dimension table and the corresponding *_ID field in the joined fact table. In addition, you must verify that the DATASOURCE_NUM_ID column values match (for Universal Sources, the value that is defined out-of-the-box for the DATASOURCE_NUM_ID column is 3). If you supply different values for the two tables, the fact table load mapping is not able to resolve the dimension key. As a result, you cannot perform queries on the fact table using that dimension.
All the Source Dependent Extract mappings (SDE) for the Universal Adapter are designed to accept the value for the DATASOURCE_NUM_ID column, either from the input data file or from a predefined parameter. If the value for a record is not supplied in a file, then the value assigned to the parameter is picked up.
The DATASOURCE_NUM_ID parameter is defined in the Data Warehouse Administration Console (DAC) Universal container and is set to the internal DAC system variable '@DAC_DATASOURCE_NUM_ID'. The value for this variable is dynamically set by DAC depending on the physical data source that is being used.
The W_EVENT_TYPE_D table is a dimension class table which tracks various types of events. This dimension table plays multiple roles in the Oracle Contact Center Telephony Analytics application. The various roles are described in the following section.
Contact center representatives engage in various activities, such as logging into the Automated Call Distributor (ACD) system to handle customer calls, taking a scheduled break, taking an unscheduled break, and so on. All such activities that you want to load into the warehouse should be provided in the file_event_type.csv source file to be stored in the W_EVENT_TYPE_D table with the W_EVENT_CLASS column set to the REPACTIVITY domain value.
The ACTIVITY_TYPE_WID dimension key in the W_REP_ACTIVITY_F fact table identifies the nature of the activity. This key is resolved using the W_EVENT_TYPE_D table. To resolve the ACTIVITY_TYPE_WID dimension key in the W_REP_ACTIVITY_F table, the W_REP_ACTIVITY_F and W_EVENT_TYPE_D tables are joined through the ACTIVITY_TYPE_ID column and the DATASOURCE_NUM_ID column. For the ACTIVITY_TYPE_WID dimension key to resolve properly in the W_REP_ACTIVITY_F fact table, you must verify that the ACTIVITY_TYPE_ID column and the DATASOURCE_NUM_ID column values in file_rep_activity.csv file match with the INTEGRATION_ID column and the DATASOURCE_NUM_ID column values in the file_event_type.csv file. If the two columns do not match for a particular record, the fact load mapping cannot resolve the dimension key for that fact record.
In a contact center there are various reasons for which customers contact an organization and organizations contact a customer. The contact can happen through various channels, such as phone, online chat, email, fax, and so on.
Some of the reasons for a customer to contact your organization include:
Placing an order for a product or service.
Lodging a complaint.
Inquiring about a product offering.
Some of the reasons for your organization to contact a customer include:
Performing a customer satisfaction survey.
Following up on an inquiry.
The call types that you want to load into the Oracle Business Analytics Warehouse are provided in the file_event_type.csv source file to be stored in the W_EVENT_TYPE_D table with the W_EVENT_CLASS column set to the CONTACT domain value.
The CALL_TYPE_WID dimension key in W_ACD_EVENT_F fact table identifies the type of call. This key is resolved using the W_EVENT_TYPE_D table. To resolve the CALL_TYPE_WID dimension key in W_ACD_EVENT_F fact table, the W_ACD_EVENT_F and W_EVENT_TYPE_D tables are joined through the CALL_TYPE_WID column and the DATASOURCE_NUM_ID column. For the CALL_TYPE_WID dimension key to resolve properly in the W_ACD_EVENT_F fact table, you must verify that the CALL_TYPE_WID column and the DATASOURCE_NUM_ID column values in file_acd_event.csv file match with the INTEGRATION_ID column and the DATASOURCE_NUM_ID column values in the file_event_type.csv file. If the two columns do not match for a particular record, the fact load mapping cannot resolve the dimension key for that fact record.
The CNTCT_TYPE_WID dimension key in the W_CNTCT_CNTR_PERF_F fact table identifies the same information and it is resolved in a similar process. It requires the CNTCT_TYPE_ID column and the DATASOURCE_NUM_ID column values in the file_cntct_cntr_perf.csv file to match with the INTEGRATION_ID column and the DATASOURCE_NUM_ID column values in the file_event_type.csv file.
In a call center, the Computer Telephony Integration (CTI) system tracks each activity associated with a call, including:
Call placed in queue to be answered
Call answered by a contact representative
Call placed on hold by a contact representative
Call transferred from one contact representative to another
Call hung up by the customer
The call events that you want to load into the Oracle Business Analytics Warehouse are provided in the file_event_type.csv source file and stored in the W_EVENT_TYPE_D table with the W_EVENT_CLASS column set to INTRA_CALL.
To resolve the CALL_EVENT_TYPE_WID dimension key in W_ACD_EVENT_F fact table, the W_ACD_EVENT_F and W_EVENT_TYPE_D tables are joined through the CALL_EVNT_TYPE_ID column and the DATASOURCE_NUM_ID column. For the CALL_EVENT_TYPE_WID dimension key to resolve properly in the W_ACD_EVENT_F fact table, you must verify that the CALL_EVNT_TYPE_ID column and the DATASOURCE_NUM_ID column values in file_acd_events.csv file match with the INTEGRATION_ID column and the DATASOURCE_NUM_ID column values in the file_event_type.csv file. If the two columns do not match for a particular record, the fact load mapping cannot resolve the dimension key for that fact record.
On many occasions, contact representative are required to perform certain tasks after the initial contact. These tasks might include creating a follow-up action item list, dispatching the case from the contact to a particular group, and so on. These activities are known as after-call work (ACW) activities. The call events that you want to load into the Oracle Business Analytics Warehouse are provided in the file_event_type.csv source file and stored in the W_EVENT_TYPE_D table with the W_EVENT_CLASS column set to the ACWACTIVITY domain value.
To resolve the ACW_ACT_TYPE_WID dimension key in W_CNTCT_CNTR_PERF_F fact table, the W_CNTCT_CNTR_PERF_F table is joined with W_EVENT_TYPE_D table through the ACW_ACT_TYPE_ID column and the DATASOURCE_NUM_ID column. For the ACW_ACT_TYPE_WID dimension key to resolve properly in the W_CNTCT_CNTR_PERF_F fact table, you must verify that the ACW_ACT_TYPE_ID column and the DATASOURCE_NUM_ID column values in the file_cntct_cntr_perf.csv file match with the INTEGRATION_ID column and the DATASOURCE_NUM_ID column values in the file_event_type.csv file. If the two columns do not match for a particular record, the fact load mapping cannot resolve the dimension key for that fact record.
The W_REASON_D table is a class dimension table that tracks various classes of reason such as those associated with the opportunity, opportunity status, quota, contact, contact transfer and so on. These classes are differentiated by the W_REASON_CLASS column.
For Oracle Contact Center Telephony Analytics, Contact Reason is an important reason class. All contacts made either by the customer to your organization, or by your organization to a customer, need to have a reason (for example, an inquiry or complaint).
The Contact Reasons that you want to load into the Oracle Business Analytics Warehouse are provided in the file_reason.csv source file to be stored in the W_REASON_D table with the W_REASON_CLASS column set to the domain value CONTACT. The Contact Transfer Reasons are provided with the W_REASON_CLASS set to CONTACT_TRANSFER.
To resolve the CNTCT_REASON_WID dimension key in W_CNTCT_CNTR_PERF_F fact table, the W_CNTCT_CNTR_PERF_F table is joined with W_REASON_D through the CNTCT_REASON_ID column and the DATASOURCE_NUM_ID column. For the CNTCT_REASON_WID dimension key to resolve properly in the W_CNTCT_CNTR_PERF_F fact table, you must verify that the CNTCT_REASON_ID column and the DATASOURCE_NUM_ID column values in file_cntct_cntr_perf.csv match with the INTEGRATION_ID column and the DATASOURCE_NUM_ID column values in the file_reason.csv file. If the two columns do not match for a particular record, the fact load mapping cannot resolve the dimension key for that fact record.
The W_STATUS_D is a dimension class table that tracks various classes of status such as those associated with the purchase orders, requisitions, shipments, inventory, customer cases, contact statuses, and so on. These classes are differentiated by the W_STATUS_CLASS column.
For the Oracle Contact Center Telephony Analytics application, Contact Status is an important status class. All contacts made either by the customer to your organization, or by your organization to a customer, are assigned a status. Examples include:
Customer abandoned call before contact
Customer abandoned call during contact
Contact completed
The contact statuses that you want to load into the Oracle Business Analytics Warehouse are provided in the file_status.csv source file to be stored in the W_STATUS_D table with the W_STATUS_CLASS column set to the CONTACT domain value.
The W_STATUS_CODE column in the W_STATUS_D table also contains domain values. The four domain values ABANDONED, RELEASE, DISCONNECTED, and HANGUP, are used in the computation of Contact Center Performance metrics. Therefore, it is critical that while you load all your Contact Statuses through the source file, the records are mapped into the appropriate W_STATUS_CODE domain value.
To resolve the CNTCT_STATUS_WID dimension key in W_CNTCT_CNTR_PERF_F fact table, the W_CNTCT_CNTR_PERF_F table is joined with W_STATUS_D through the CNTCT_STATUS_ID column and the DATASOURCE_NUM_ID column. For the CNTCT_STATUS_WID dimension key to resolve properly in the W_CNTCT_CNTR_PERF_F fact table, you must verify that the CNTCT_STATUS_ID column and the DATASOURCE_NUM_ID column values in file_cntct_cntr_perf.csv match with the INTEGRATION_ID column and the DATASOURCE_NUM_ID column values in the file_status.csv file. If the two columns do not match for a particular record, the fact load mapping cannot resolve the dimension key for that fact record.
When setting up the Representative Activities table you must consider the following:
All events in the Representative Activities table are time span events. The events are not point in time events.
The calculation of the Actual, Scheduled, Login, and Break durations are based on the event durations in the source-system data. To avoid duplication in a representative's time, the representative activity records must not overlap in time. For example, if the Login and Break activities overlap in time in the source-system data, then the time durations are counted towards both categories.
The hourly aggregate is the lowest level of aggregation provided. Representatives are counted as present for an hourly bucket if they are present for any part of that hour. For example, if a representative activity starts at 9.45 A.M. and ends at 10.45 A.M., the representative is counted as present for 9-10 A.M. and 10-11 A.M. time buckets. No weight factor is used to indicate the representative is available for part of the hour. However, the duration of activities are apportioned into the two hourly buckets. If the Local Time Zone is different from the warehouse time zone, the duration of activities will be apportioned in all the local and warehouse hours that had part of that activity.
The number of breaks a representative takes is calculated by counting the number of break records. There is one break record for each representative for each break (scheduled or actual). If a break is split into multiple records in the source system, then it is counted as multiple breaks in the Oracle Business Analytics Warehouse.
If a representative's activity spans across the date boundary, then you must provide two different records for that representative for the same activity, with different activity start dates and times. The same applies to the local date boundary as well. Hence, if an activity crosses the day boundary in warehouse time and local time at different points in time (due to time-zone difference between local and warehouse), there should be three records for that particular activity, each having an Activity start day that is the same as the Activity end day and the Activity start local day the same as the Activity end local day. For example, if a representative logs on to the Automatic Call Distributor (ACD) system at 23:30 on January 4, 2006 (23:00 on January 4, 2006 in local time) and logs off from the ACD system at 01:30 on January 5, 2006 (01:00 on January 5, 2006 in local time) then create three records in the file_rep_activity.csv flat file interface, as shown in the following table.
Table 14-1 Setting up activities and date boundaries
| Rep | Activity Type | Activity Start Date | Activity Start Time | Activity End Date | Activity End Time | Activity Start Local Date | Activity Start Local Time | Activity End Local Date | Activity End Local Time |
|---|---|---|---|---|---|---|---|---|---|
|
Rep1 |
LOGIN |
01/04/2006 |
23:30:00 |
01/04/2006 |
23:59:59 |
01/04/2006 |
23:00:00 |
01/04/2006 |
23:29:59 |
|
Rep1 |
LOGIN |
01/05/2006 |
00:00:00 |
01/05/2006 |
00:29:59 |
01/04/2006 |
23:29:59 |
01/04/2006 |
23:59:59 |
|
Rep1 |
LOGIN |
01/05/2006 |
00:30:00 |
01/05/2006 |
01:30:00 |
01/05/2006 |
00:00:00 |
01/05/2006 |
01:00:00 |
To set up the Representative Activities table
Open the file_rep_activity.csv file using a text editor in the $PMRoot\SrcFiles folder.
Type in your data to load the Representative Activities table.
Save and close the file.
The Contact Center Performance table stores information at a contact and representative grain. For example, if a call is handled by two representatives, there are two records for that contact in the Contact Center Performance table.
When setting up the Contact Center Performance table you must consider the following:
The Abandoned Contact Count, Answered Contact Count, Hangup Contact Count, and Released Contact Count metrics are counts of contacts based on the Contact Status. The Contact Center Performance table is preconfigured to expect the Contact Status in the file_cntct_cntr_perf.csv file is to be at a Contact level. If you configure the Contact Status at the contact and representative level, you need to make sure that these aggregate metrics are defined at the contact and representative level in the appropriate workflows. You need to make any changes in the Select clause of the Source Qualifier SQL statement in the PLP_ContactCenterPerformanceRepHourAggregate_Load mapping. You also need to configure the metadata in the repository file. You need to change the definitions of these metrics in the Logical Table Source that maps to the W_CNTCT_CNTR_PERF_F fact table.
Answered contacts are defined as the contacts whose status is not marked as ABANDONED. The Answered Contact Count metric is calculated as follows:
COUNT(DISTINCT (CASE WHEN W_STATUS_D.W_STATUS_CODE != 'ABANDONED' THEN W_CNTCT_CNTR_PERF_F.CNTCT_NUM END)).
You can choose not to count calls which are completed in the Interactive Voice Response (IVR) as an answered call. You can exclude these contacts from the Answered Contact Count by assigning them a different or new Contact Status.
As contacts are associated with a representative, a Contact Representative Identifier needs to be provided for each fact record. The IVR can be modeled as a virtual representative and assigned a Contact Representative Identifier. Calls waiting in a general queue, and not assigned to a representative, also need to be given their own Contact Representative Identifier.
The majority of the data for the Contact Center Performance table is sourced from the data in the file_acd_event.csv file. You must make sure that the source data is consistent across the file_acd_event.csv and file_cntct_cntr_perf.csv files.
To set up the Contact Center Performance table
Open the file_cntct_cntr_perf.csv file using a text editor in the $PMRootDir\SrcFiles folder.
Type in your data to load the Contact Center Performance table.
Save and close the file.
The benchmarks and targets subject area is used to establish targets for various metrics for your Contact Center and also capture industry benchmarks for the same metrics. The values of these metrics are calculated using the Contact Center Performance subject area and are compared to the Benchmarks and Targets table to evaluate the performance of your Contact Center.
When setting up the Benchmarks and Targets table you must consider the following:
The file_cntct_cntr_bnchmrk_tgt.csv file must supply the effective date range for each benchmark record. The date range is used to identify the appropriate benchmark to compare with the actuals and the determination of other metrics such as the Service Level. Actuals refers to the actual value of the metric (during the period) as opposed to the planned or targeted value of the metric.
You need to supply an appropriate date range for the benchmark records. For example, if the benchmark records do not vary over time, a large date range can be used. An example is shown below:
PERIOD_START_DT 01/01/1899
PERIOD_END_DT 01/01/3714
The Benchmarks and Targets table is preconfigured at the contact level. You can define other benchmarks and targets, for example, an Hourly-Total-Hold-Duration benchmark, and these can be added using the extension columns in the data warehouse. For more information on the methodology for storing additional data in the data warehouse, see Chapter 6, "Customizing the Oracle Business Analytics Warehouse".
For each dimension in the Benchmark and Targets fact table, you can decide if a benchmark or target varies by that dimension or not. If you choose to keep a benchmark or target constant over a dimension, you need to supply a question mark (?) as the value for the dimension ID. In addition, the metric needs to be leveled in the repository (RPD) at the grand-total level of that dimension. This dimension ID also needs to be removed from the join in the SQL statement in the PLP_ContactCenterPerformanceServiceLevelAggregate mapping. If you choose to vary a benchmark or target by a dimension, you need to provide benchmark or target for each value of the dimension.
The FORECAST_CNTCT_CNT table in the source file is preconfigured to forecast the number of calls for a day for a combination of dimensions.
The Benchmarks and Targets table is preconfigured with the smallest effective date range of a day. To changing the grain to be hourly, perform the following procedure.
To configure the grain of the Benchmarks and Targets table as an hourly level
Add the Hour WID to the W_CNTCT_CNTR_BNCHMRK_TGT_F fact table.
Modify the SDE_Universal_ContactCenterBenchmarkTargetFact and SIL_ ContactCenterBenchmarkTargetFact mappings to populate the Hour WID based on the Period Start Date and Period End Date.
These dates need to fall on the hour boundaries and not in the middle of an hourly interval.
Modify the PLP_ContactCenterPerformanceRepHourAggregate_Load mapping SQL statement to now join also on Hour WID to the Benchmarks and Targets table.
Modify the metadata in the repository to include the new physical and logical joins to the W_HOUR_OF_DAY_D dimension.
Set the content pane settings on the fact table to the newly added Hour (Time) dimension in the RPD.
To set up the Benchmarks and Targets table
Open the file_cntct_cntr_bnchmrk_tgt.csv file using a text editor in the $PMRootDir\SrcFiles folder.
Type in your data to load the Benchmarks and Targets.
Save and close the file.
If a fact record is deleted physically in the telephony data source, you can logically delete that record from the data warehouse. To do this, a target staging table called <FACT table>_PE needs to created for each fact table and a mapping needs to be created that can load this table.
For example in order to delete records from W_ACD_EVENT_F, a new staging table needs to be created as W_ACD_EVENT_F_PE where PE stands for primary extract. The structure of this table will be same for any fact table as it will have only two columns: INTEGRATION_ID and DATASOURCE_NUM_ID. A new mapping SDE_Universal_<Fact>_Primary_Extract needs to be created to load this the primary extract staging table with all the INTEGRATION_ID and DATASOURCE_NUM_ID records that have not been deleted and are currently present in OLTP system.
The following example illustrates this process. In this example, there are five records in the telephony data source and in the data warehouse table W_ACD_EVENT_F, as follows:
Table 14-2 Example records in the W_ACD_EVENT_F table
| INTEGRATION_ID | DATASOURCE_NUM_ID | CALLID_NUM | DELETE_FLG |
|---|---|---|---|
|
1 |
0 |
20060101_C1 |
N |
|
2 |
0 |
20060101_C2 |
N |
|
3 |
0 |
20060101_C3 |
N |
|
4 |
0 |
20060101_C4 |
N |
|
5 |
0 |
20060101_C5 |
N |
For any reason, records with an Integration_Id = 3 and a Datasource_num_Id = 0 are deleted from the source system. Then Our primary extract mapping should load the remaining four records in the W_ACD_EVENT_PE table, as follows:
Table 14-3 Example records in the W_ACD_EVENT_PE table
| INTEGRATION_ID | DATASOURCE_NUM_ID |
|---|---|
|
1 |
0 |
|
2 |
0 |
|
4 |
0 |
|
5 |
0 |
This will be followed by the SIL_IdentifyDelete and the SIL_*_SoftDelete mappings, which are already available in the data warehouse repository, as installed out-of-the-box. The SIL_ACDEventFact_IdentifyDelete mapping will do a left outer join on W_ACD_EVENT_F and W_ACD_EVENT_F_PE to find out the missing records in W_ACD_EVENT_F_PE, and to check whether the missing record is not already deleted (DELETE_FLG='Y') in the W_ACD_EVENT_F. If not, it will load those records in the staging table W_ACD_EVENT_F_DEL.
The SIL_ACDEventFact_SoftDelete mapping will read records from the W_ACD_EVENT_F_DEL and update the DELETE_FLG of those records in W_ACD_EVENT_F to 'Y', as follows:
The Contact Center Telephony Analytics application supports analysis by both the local date/time and the Warehouse date/time. On the fact tables, the _LDT columns refer to the local date and time while the _DT fields refer to the warehouse date and time. If you want to capture the local date and time on a transaction, you will need to provide that data in the _LDT columns through the flat file interface. You will also have to apply the appropriate transformation to compute the warehouse date/time and supply this in the _DT columns through the flat file interface. Even if you choose not to support analysis based on local time, you will need to supply data for the _DT (warehouse date/time) columns. The table below provides a list of the applicable date and local date columns for each fact table.
Table 14-6 Date Columns and Flat Files
| Flat file | Applicable Date Column | Applicable Local Date Column | Table Using the Local Date |
|---|---|---|---|
|
file_rep_activity.csv |
ACTIVITY_START_DT, ACTIVITY_END_DT |
ACTIVITY_START_LDT, ACTIVITY_END_LDT |
W_REP_ACTIVITY_F |
|
file_acd_event.csv |
EVENT_START_DT, EVENT_END_DT |
EVENT_START_LDT, EVENT_END_LDT |
W_ACD_EVENT_F |
|
file_cntct_cntr_perf.csv |
CNTCT_START_DT, CNTCT_END_DT |
CNTCT_START_LDT, CNTCT_END_LDT |
W_CNTCT_CNTR_PERF_F |
|
file_rep_activity_hour.csv (Alternate Load) |
ACTIVITY_DT |
ACTIVITY_LDT |
W_REP_ACTIVITY_HOUR_A |
|
file_cntct_cntr_perf_rep_hour.csv (Alternate Load) |
CNTCT_DT |
CNTCT_LDT |
W_CNTCT_CNTR_PERF_REP_HOUR_A |
To provide local dates in the flat file interface
Open the applicable flat file interface.
In the flat file interface, input the new dates in the *_DT fields.
Input the corresponding local dates in the *LDT fields.
Save your changes to the flat file interface.
Run a test load for 10 records to verify that your new dates are loaded into the applicable table.
Many of the fact and dimension tables within the Oracle Contact Center Telephony Analytics application use flag fields to provide value-added information pertaining to a contact or contact representative. These flag fields are configurable and include the following:
CONSULT_ FLG
CONFERENCE_FLG
PERTINENT_ INFO_FLG
CNTCT_MTCH_FLG
IVR_FLG
The possible values for these flag fields in the data warehouse tables are Y or N. The following table shows Configurable Flag Values and Descriptions.
Table 14-7 Configurable Flag Values and Descriptions
| Flag | Flag Value | Description |
|---|---|---|
|
CONSULT_FLG |
Y |
Indicates that the contact representative consulted with other contact representative during the course of the call or contact. |
|
N |
Indicates that the contact representative did not consult with other contact representative during the course of the call or contact. |
|
|
CONFERENCE_FLG |
Y |
Indicates that the contact representative conferenced with other contact representatives during the course of the call or contact. |
|
N |
Indicates that the contact representative did not conference with other contact representative during the course of the call or contact. |
|
|
PERTINENT_INFO_FLG |
Y |
Indicates that the pertinent information was available for the contact. |
|
N |
Indicates that the pertinent information was not available for the contact. |
|
|
CNTCT_MTCH_FLG |
Y |
Indicates that the contact was matched with the existing customer data using Customer Entered Digits (CED) such as PIN Numbers, Account Numbers, or Social Security Number. |
|
N |
Indicates that the contact could not be matched with the existing customer data using Customer Entered Digits (CED) such as PIN Numbers, Account Numbers, or Social Security Number. |
|
|
IVR_FLG |
Y |
Indicates that the call associated with the call was recorded in the Interactive Voice Response (IVR) system |
|
N |
Indicates that the call associated with the call was not recorded in the Interactive Voice Response (IVR) system |
This section contains information about excluding representative data from contact representative and organization performance aggregate tables for post-load processing.
You may want to exclude certain contact representatives from the aggregation process for various reasons. For example, you may want to exclude representatives who are on vacation, or who are working on special projects.
The default configuration calculates contact-related information for all contact representatives in the enterprise. There are five aggregate tables supplied with the Oracle Contact Center Telephony Analytics application for improving the performance of the dashboards and reports:
W_CNTCT_CNTRC_PERF_REP_HOUR_A
W_CNTCT_CNTR_PERF_REP_DAY_A
W_CNTCT_CNTR_PERF_REP_MONTH_A
W_CNTCT_CNTR_PERF_ORG_HOUR_A
W_CNTCT_CNTR_PERF_ORG_DAY_A
To exclude data about specific representatives from the aggregation calculation
In PowerCenter Designer, open the Configuration for Post Load Processing folder.
Open the PLP_ContactCenterPerformanceRepAggregate_Extract mapping.
Double-click the EXP_CNTCT_CNTR_PERF transformation.
Locate the EXCLUSION_IND_FLG port.
This port is preconfigured with a value of N indicating that all rows are included in the aggregates. Change this logic to include your logic to determine which groups of records you want to exclude.
Validate and save the mapping to the repository.
|
Note: If you exclude data from an aggregate table, you also need to apply the same filter to the Logical Table Source corresponding to the W_CNTCT_CNTR_PERF_F base fact table in the repository metadata (Fact Service Contact Center Performance logical table). The metrics computed from the base fact tables are now consistent with those computed from the aggregate tables. |
The Representative Activity fact table stores information at an individual representative activity grain. In case the data is not available at that grain, you can alternately choose to provide summarized data at the hourly level and load the Rep Activity hour level aggregate table W_REP_ACTIVITY_HOUR_A directly.
This data will be used by the post-load process to load the following higher level aggregates:
W_REP_ACTIVITY_DAY_A
W_REP_ACTIVITY_MONTH_A
To configure the alternate load plan to directly load aggregate table
Login to the DAC repository
Select the Universal container
Choose Design, then Subject Area and query for the "Service – Rep Activity" subject area.
With this subject area selected, in the detail pane, click on Configuration Tags.
Uncheck the configuration point "ECC- Load into base fact and then aggregates".
Check the configuration point "ECC – Load directly into aggregate facts".
Redesign the subject area "Service – Rep Activity" and save.
Redesign the execution plan "Universal Contact Center Telephony Analytics".
To provide hour level data
Open the file rep_activity_hour.csv using a text editor from $PMRootDir\SrcFiles folder.
Enter the data for Representative activities at the hourly level.
Save and close the file.
|
Note: If you directly load the entry-level aggregate table, the metrics in the Fact - Service - Rep Activities logical fact table that are mapped only to the base W_REP_ACTIVITY_F LTS will not be populated. |
The Contact Center Performance table stores information at a contact and representative grain. In case the data is not available at a contact grain, you can alternately choose to provide summarized data at the hourly level and load the Contact Center Performance hourly aggregate table, W_CNTCT_CNTR_PERF_REP_HOUR_A, directly.
This data will be used by the post-load process to load the following higher level aggregates.
W_CNTCT_CNTR_PERF_REP_DAY_A
W_CNTCT_CNTR_PERF_REP_MONTH_A
W_CNTCT_CNTR_PERF_ORG_HOUR_A
W_CNTCT_CNTR_PERF_ORG_DAY_A
To configure the alternate load plan to directly load aggregate
Login to the DAC repository
Select the Universal container
Go to Design, then Subject Area and query for the "Service - Contact Center Performance" subject area
With this subject area selected, click on Configuration Tags
Uncheck the configuration point "ECC- Load into base fact and then aggregates"
Check the configuration point "ECC – Load directly into aggregate facts"
Redesign the subject area "Service - Contact Center Performance and save.
Redesign the execution plan "Universal Contact Center Telephony Analytics".
To provide hour level data
Open the file_rep_activity_hour.csv using a text editor from $PMRootDir\SrcFiles folder.
Enter the data for Contact Center Performance at the hourly level.
Save and close the file.
|
Note: The Configuration Tag for the Direct Load to both Rep Activity and Contact Center Performance Hourly Aggregates is the same and hence either both can be loaded at hourly level or both can be loaded at point-in-time grain. If you directly load the entry-level aggregate table, the metrics in the Fact - Service - Contact Center Performance logical fact table that are mapped only to the base W_CNTCT_CNTR_PERF_F LTS will not be populated. |
The INTEGRATION_ID by default is provided in the SrcFiles and they are unique for a DATASOURCE_NUM_ID in a fact. Alternately, they can also be formed in the warehouse in the SDE adapter mappings using a set of Dimension_Ids and other columns of each record. The combination of Dimension Ids and other columns used for forming Integration_id is unique within a DATASOURCE_NUM_ID and identifies the grain of the fact. For example, in the W_ACD_EVENT_F fact table, INTEGRATION_ID is formed as CALLID_NUM||'~'||EVENT_SEQUENCE_NUM.
To set up formation of Integration_Id in SDE adapter
Login to the DAC repository and select the Universal container.
Go to the SDE_Universal task and click on the parameter tab.
Change the value of $$READ_INTEGRATION_ID_FROM_FILE parameter to FALSE.
The following table lists the expression of INTEGRATION_ID for each fact and the SDE task that creates it.
Table 14-8 Expressions and Task Names
| Table Name | INTEGRATION_ID Expression | Task Name |
|---|---|---|
|
W_ACD_EVENT_F |
CALLID_NUM||'~'||EVENT_SEQUENCE_NUM |
SDE_Universal_ACDEventFact |
|
W_CNTCT_CNTR_PERF_F |
CNTCT_NUM||'~'||CNTCT_REP_ID||'~'||CNTCT_START_DT |
SDE_Universal_ContactCenterPerformance Fact |
|
W_CNTCT_CNTR_BNCHMRK_TGT_F |
PERIOD_START_DT||'~'||COMPANY_ORG_ID||'~'||CNTCT_REP_ID||'~'||REP_HR_ORG_ID||'~'||CNTCT_REP_ORG_ID||'~'||CNTCT_CNTR_LOC_ID||'~'||CHNL_TYPE_ID||'~'||CUSTOMER_ID||'~'||PRODUCT_ID |
SDE_Universal_ContactCenterBenchmark TargetFact |
|
W_CNTCT_CNTR_PERF_REP_HOUR_A |
CNTCT_CNTR_LOC_ID||'~'||CNTCT_REP_ID||'~'||CNTCT_DT |
SDE_Universal_ContactCenterPerformance RepHourAggregate |
|
W_REP_ACTIVITY_F |
CNTCT_REP_ID||'~'||ACTIVITY_TYPE_ID||'~'||ACTIVITY_START_DT||'~'||ACTIVITY_START_LDT |
SDE_Universal_RepActivityFact |
|
W_REP_ACTIVITY_HOUR_A |
CNTCT_REP_ID||'~'||ACTIVITY_TYPE_ID||'~'||ACTIVITY_DT |
SDE_Universal_RepActivityHourAggregate |
The IVR Menu Dimension table W_IVR_MENU_D stores information about each menu point in the Interactive Voice Response System. The Integration_id in this table uniquely identifies a IVR MENU and is used as the dimension identifier IVR_MENU_ID in the ACD Event Fact fact table, W_ACD_EVENT_F.
If a call has n number of consecutive IVR events without any transfer to a contact representative, the NAV_PATH_ID for that call is identified as MENU1-MENU2-MENU3-……..-MENUn where MENUm is the IVR_MENU_ID of the mth event in that call. The NAV_PATH_ID is used to determine the navigation profile dimension key for a call.
It is recommended that the Integration_Id of the IVR MENU dimension W_IVR_MENU_D does not exceed 10 characters in order to keep the NAV_PATH_ID within the defined size limit of 255 characters.
If a call has multiple transfer from IVR to Rep or REP to IVR, a Navigation Profile is determined for each segment of the call that was completely within the IVR system. For example, suppose a call had eight events as given below.
Table 14-9 Example call events in a navigation profile
| CallId | Event Type | Event Sequence Number | Menu Id |
|---|---|---|---|
|
20060104-C1 |
IVR |
1 |
MENU1 |
|
20060104-C1 |
IVR |
2 |
MENU2 |
|
20060104-C1 |
IVR |
3 |
MENU3 |
|
20060104-C1 |
REP |
4 |
|
|
20060104-C1 |
IVR |
5 |
MENU4 |
|
20060104-C1 |
IVR |
6 |
MENU5 |
|
20060104-C1 |
IVR |
7 |
MENU6 |
|
20060104-C1 |
IVR |
8 |
MENU7 |
There will be two NAV_PATH_ID for both IVR segment of the call. Transfer Flag will be 'Y' for the first segment because the call was transferred to REP after this segment and it will be 'N' for the next segment where it completed in IVR itself.
Table 14-10
| CallId | NAV PATH ID | Transfer Flag |
|---|---|---|
|
20060104-C1 |
MENU1-MENU2-MENU3 |
Y |
|
20060104-C1 |
MENU4-MENU5-MENU6 |
N |
The mapping PLP_IVRNavigation Profile Dimension will load two records in the IVR Navigation Profile Dimension table W_IVR_NAV_PROFL_D, one for NAV_PATH_ID "MENU1-MENU2-MENU3" and "MENU4-MENU5-MENU6" and create unique surrogate key for each of them. If any NAV_PATH_ID already exists in the table W_IVR_NAV_PROFL_D and is Effective on the day when the call was made, that record will not be inserted.
IVR Menu dimension is configured as a slowly changing dimension. The history of any change in the following attributes will be maintained in the IVR Menu Dimension.
PROMPT_MSG
PROMPT_MESG_DURN
RSPNS_ERROR_MSG
MENU_BROADCAST_MSG
TIMEOUT_DURN
TIMEOUT_MSG
MENU_ACTIVE_FLG
When one or more of the above listed attributes change, the new record is inserted in W_IVR_MENU_D and a new dimension key (ROW_WID) is assigned to it. This change, in turn, result into a change in all the Navigation Profiles which has this MENU_WID at any position from 1 to 15th. The history of this change is maintained in the IVR Navigation profile dimension table, W_IVR_NAV_PROFL_D.
The Oracle Business Intelligence Applications analytic repository (.rpd) has several duration metric in the "Fact – Service - Contact Center Performance" and "Fact – Service – Rep Activities" subject areas. Most of these duration metrics also have a version that is a pre-formatted number to be displayed in the hh:mm:ss format. In order to view these duration metrics in hh:mi:ss format in an Oracle BI Answers report, do the following:
Select the duration in hh:mi:ss metric using the column selectors in the "Criteria" tab.
Click on "Format Column".
The Column Properties window will open. Go to Data format.
Check "Override Default data format".
Select Treat Number as "Custom" and give Custom Numeric Format as "0#:##:##".
