Guest

Cisco Unified Contact Center Express

Export Cisco CRS 3.x Data to a Customer Data Warehouse

Cisco - Export Cisco CRS 3.x Data to a Customer Data Warehouse

Document ID: 65193

Updated: Jun 22, 2005

   Print

Introduction

This document describes how you can manually export historical reporting data from Cisco Customer Response Solutions (CRS) 3.x to a customer data warehouse in a Cisco IP Contact Center (IPCC) Express Edition environment.

Note: The data is stored on a remote database (RDB) if you have configured an RDB. However, if you do not use an RDB, the historical reporting data is available on the Cisco CRS server.

Prerequisites

Requirements

Cisco recommends that you have knowledge of these topics:

  • Cisco IPCC

  • Microsoft SQL Server

Components Used

The information in this document is based on these software and hardware versions:

  • Cisco IPCC Express 3.x

  • Microsoft SQL Server 2000

The information in this document was created from the devices in a specific lab environment. All of the devices used in this document started with a cleared (default) configuration. If your network is live, make sure that you understand the potential impact of any command.

Conventions

Refer to Cisco Technical Tips Conventions for more information on document conventions.

Basic Scenario: Microsoft SQL Server 2000 Database

This section explains the procedure to export data when both, the Cisco CRS database, and the customer data warehouse use Microsoft SQL Server 2000.

Note: Before you attempt this procedure, ensure that the customer data warehouse does not contain any Cisco CRS data. If the customer data warehouse contains any Cisco CRS data, see the procedure in the Variation 3: Customer Data Warehouse Already Contains CRS Data section.

Complete these steps:

  1. Select Start > Programs > Microsoft SQL Server > Enterprise Manager on the CRS server.

    The SQL Server Enterprise Manager window appears:

    Figure 1 – SQL Server Enterprise Manager

    CRS_data1.gif

  2. Expand Microsoft SQL Servers > SQL Server Group > CRS Server > Databases on the left pane.

  3. You need to export the db_cra and db_cra_ccdr databases.

  4. Right-click db_cra, and select All Tasks > Export Data.

    Figure 2 – Select All Tasks > Export Data for the db_cra Database

    CRS_data2.gif

    The Data Transformation Services (DTS) Import/Export Wizard appears:

    Figure 3 – The DTS Import/Export Wizard

    CRS_data3.gif

  5. Click Next.

    The Choose a Data Source page of the wizard appears:

    Figure 4 – Choose a Data Source

    CRS_data4.gif

  6. In the Choose a Data Source page, complete these steps:

    1. Type the IP Address or the host name of the customer data warehouse in the Server field (see arrow A in Figure 4).

    2. Select the Use SQL Server Authentication option (see arrow B in Figure 4).

    3. Type the user ID and password of the Cisco CRS database in the Username and Password fields (see arrow C in Figure 4).

    4. Select the destination database from the Database drop-down list.

  7. Click Next twice.

    The list of tables in db_cra appears:

    Figure 5 – List of Tables in db_cra

    CRS_data5.gif

  8. Check the check boxes next to these nine table names:

    • AgentConnectionDetail

    • AgentStateDetail

    • ContactRoutingDetail

    • ContactServiceQueue

    • Resource

    • ResourceGroup

    • ResourceSkillMapping

    • Skill

    • SkillGroup

  9. Click Next twice.

  10. Click Finish to start the data transfer process.

  11. When the process completes, a confirmation message appears:

    Figure 6 – Confirmation Message

    CRS_data6.gif

  12. Click OK.

  13. Right-click db_cra_ccdr in the SQL Server Enterprise Manager, and select All Tasks > Export Data.

  14. Repeat steps 5 through 12 to export the db_cra_ccdr database. Note these points:

    • In Step 6d, you can select the same destination database name that you select previously to export the db_cra database. Cisco CRS distributes data in two databases for design purposes only. You can combine the data from db_cra and db_cra_ccdr into one destination database, for example, "gloriatemp".

    • In Step 8, select only the ContactCallDetail table for db_cra_ccdr (see Figure 7).

      Figure 7 – Select the ContactCallDetail Table for db_cra_ccdr

      CRS_data7.gif

Variation 1: CRS Database is MSDE

This section explains the procedure to export data when the Cisco CRS database uses Microsoft SQL Desktop Edition (MSDE). MSDE does not support the SQL Server Enterprise Manager tool for data import and export. In this case, you must register the Cisco CRS database on the Cisco CallManager server.

Complete these steps:

  1. Select Start > Programs > Microsoft SQL Server > Enterprise Manager on the Cisco CallManager server.

    The SQL Server Enterprise Manager window appears:

    Figure 8 – The SQL Server Enterprise Manager on the Cisco CallManager Server

    CRS_data8.gif

  2. Right-click SQL Server Group, and select New SQL Server Registration from the shortcut menu.

    The Register SQL Server Wizard appears:

    Figure 9 – The Register SQL Server Wizard

    CRS_data9.gif

  3. Check the From now on, I want to perform this task without using a wizard check box.

  4. Click Next.

    The Registered SQL Server Properties dialog box appears:

    Figure 10 – The Registered SQL Server Properties Dialog Box

    CRS_data10.gif

  5. In the Registered SQL Server Properties dialog box, complete these steps:

    1. Type the IP address or the host name of the Cisco CRS database server in the Server field (see arrow A in Figure 10).

    2. Select the Use SQL Server authentication option (see arrow B in Figure 10).

    3. Type the user name and password of the Cisco CRS database in the Login Name and Password fields (see arrow C in Figure 10).

    4. Click OK (see arrow D in Figure 10).

    The Cisco CRS server now appears in the list under SQL Server Group (see Figure 11).

    Figure 11 – The Cisco CRS Server is Listed Under SQL Server Group

    CRS_data11.gif

  6. Perform the steps in the Basic Scenario: Microsoft SQL Server 2000 Database section to export Cisco CRS data.

Variation 2: Customer Data Warehouse is not Microsoft SQL 2000

This section explains the variation in the procedure to export data when the customer data warehouse does not use Microsoft SQL 2000.

Complete these steps:

  1. Perform the steps listed in the Basic Scenario: Microsoft SQL Server 2000 Database section until step 5.

  2. In step 6, select the appropriate destination server name from the Destination list in the Choose a destination screen of the wizard (see Figure 12).

    Figure 12 – Choose the Appropriate Destination

    CRS_data12.gif

  3. Continue with the rest of the procedure. Similarly, ensure that you select the appropriate destination server for the second database.

Variation 3: Customer Data Warehouse Already Contains CRS Data

Occasionally, some CRS data already exists in the customer data warehouse. When you export all the data again from the Cisco CRS server, data collision can occur. You can use one of these two options to handle such a situation:

  1. Export only the new data generated after a certain date.

  2. Alternatively, you can delete all the existing Cisco CRS data from the data warehouse before you export data to the data warehouse.

Incremental Export for Call Details Tables

This section describes how to export only new data. This procedure is applicable to all the call details tables, which include:

  • AgentConnectionDetail

  • AgentStateDetail

  • ContactCallDetail (db_cra_ccdr)

  • ContactRoutingDetail

Complete these steps:

  1. Perform steps 1 through 6 in the Basic Scenario: Microsoft SQL Server 2000 Database section.

  2. In step 7, click Next only once.

    The Specify Table Copy or Query screen of the wizard appears:

    Figure 13 – Specify Table Copy or Query

    CRS_data13.gif

  3. Select the Use a query to specify the data to transfer option (see arrow A in Figure 14).

    Figure 14 – Use a Query to Specify the Data to Transfer

    CRS_data14.gif

  4. Click Next.

    The Type SQL Statement page of the wizard appears:

    Figure 15 – Type the SQL Statement

    CRS_data15.gif

  5. Type the SQL query to export a specific portion of data from a specific table (see Figure 15). You can export data from only one table at a time.

    For example, in order to export data later than March 1st, 2004, from the ContactRoutingDetail table, type this query:

    select * from ContactRoutingDetail where startdatetime >= '03/01/2004 12am';

    Here is another example to query export data later than March 1, 2004, for the four call detail tables:

    select * from AgentConnectionDetail where startdatetime >= ‘03/01/2004 12am’; 
    select * from AgentStateDetail where eventdatetime >= ‘03/01/2004 12am’; 
    select * from ContactCallDetail where startdatetime >= ‘03/01/2004 12am’; 
    select * from ContactRoutingDetail where startdatetime >= ‘03/01/2004 12am’;
  6. Click Next.

    The Select Source Tables and Views page of the wizard appears:

    Figure 16 – Select Source Tables and Views

    CRS_data16.gif

  7. Select the corresponding destination table from the Results drop-down list in the Destination column (see Figure 16).

    Note: The destination table refers to the same table that you specified in the SQL query.

  8. Click Next twice.

  9. Click Finish in order to complete the process.

  10. Repeat steps 1 through 9 for each call details table.

Data Export for Configuration Tables

In the configuration tables, existing data is modified and new data is added frequently. You cannot identify the data that has changed. Therefore, Cisco recommends that you delete all the configuration data from the data warehouse, and export the data again from the Cisco CRS database.

The procedure in this section describes the process of data export for configuration tables, which include:

  • ContactServiceQueue

  • Resource

  • ResourceGroup

  • ResourceSkillMapping

  • Skill

  • SkillGroup

Complete these steps:

  1. Delete these six tables from the data warehouse:

    • ContactServiceQueue

    • Resource

    • ResourceGroup

    • ResourceSkillMapping

    • Skill

    • SkillGroup

  2. Perform steps 1 through 7 in the Basic Scenario: Microsoft SQL Server 2000 Database section.

  3. In step 8, select only the six configuration tables that you deleted (see Figure 17).

    Figure 17 – Select the Six Configuration Tables

    CRS_data17.gif

  4. Complete the rest of the procedure.

Note: This document does not provide any procedures to merge multiple Cisco CRS databases into a single data warehouse. Each Cisco CRS database generates unique IDs for database records. When you merge independent Cisco CRS databases, data conflict can occur.

Related Information

Updated: Jun 22, 2005
Document ID: 65193