Configure Oracle Metrics Data Sources

This page describes how to configure a connection to your Oracle metrics data sources for Pulse Advisors.

Configure a Connection to AGA Metrics Schema on the Same Oracle Instance as the Platform Schema

Use the information in this section to configure connectivity to the AGA metrics schema where the AGA data source is on the same Oracle instance as the Platform schema.

1. Do one of the following: Connect as a privileged user (such as system) and grant the following select permissions to the Platform user-schema owner: grant select on <ADVISORS AGA METRICS SCHEMA>.QUEUE_SET1_REAL_TIME to <ADVISORS PLATFORM USER> WITH GRANT OPTION; grant select on <ADVISORS AGA METRICS SCHEMA>.QUEUE_SET2_REAL_TIME to <ADVISORS PLATFORM USER> WITH GRANT OPTION; grant select on <ADVISORS AGA METRICS SCHEMA>.SKILL_GROUP_REAL_TIME to <ADVISORS PLATFORM USER> WITH GRANT OPTION; grant select on <ADVISORS AGA METRICS SCHEMA>.SERVICE_REAL_TIME to <ADVISORS PLATFORM USER> WITH GRANT OPTION; grant select on <ADVISORS AGA METRICS SCHEMA>.SERVICE_MEMBER to <ADVISORS PLATFORM USER> WITH GRANT OPTION; grant select on <ADVISORS AGA METRICS SCHEMA>.PERIPHERAL_REAL_TIME to <ADVISORS PLATFORM USER> WITH GRANT OPTION; grant select on <ADVISORS AGA METRICS SCHEMA>.INTERACTION_QUEUE_REAL_TIME to <ADVISORS PLATFORM USER> WITH GRANT OPTION; grant select on <ADVISORS AGA METRICS SCHEMA>.CONTROLLER_TIME to <ADVISORS PLATFORM USER> WITH GRANT OPTION; grant select on <ADVISORS AGA METRICS SCHEMA>.CALL_TYPE_REAL_TIME to <ADVISORS PLATFORM USER> WITH GRANT OPTION; grant select on <ADVISORS AGA METRICS SCHEMA>.AGENT_SKILL_GROUP_REAL_TIME to <ADVISORS PLATFORM USER> WITH GRANT OPTION; Connect to the AGA metrics schema as its owner and execute the following statements: grant select on AGENT_SKILL_GROUP_REAL_TIME to <ADVISORS PLATFORM USER> WITH GRANT OPTION; grant select on CALL_TYPE_REAL_TIME to <ADVISORS PLATFORM USER> WITH GRANT OPTION; grant select on CONTROLLER_TIME to <ADVISORS PLATFORM USER> WITH GRANT OPTION; grant select on INTERACTION_QUEUE_REAL_TIME to <ADVISORS PLATFORM USER> WITH GRANT OPTION; grant select on PERIPHERAL_REAL_TIME to <ADVISORS PLATFORM USER> WITH GRANT OPTION; grant select on QUEUE_SET1_REAL_TIME to <ADVISORS PLATFORM USER> WITH GRANT OPTION; grant select on QUEUE_SET2_REAL_TIME to <ADVISORS PLATFORM USER> WITH GRANT OPTION; grant select on SERVICE_MEMBER to <ADVISORS PLATFORM USER> WITH GRANT OPTION; grant select on SERVICE_REAL_TIME to <ADVISORS PLATFORM USER> WITH GRANT OPTION; grant select on SKILL_GROUP_REAL_TIME to <ADVISORS PLATFORM USER> WITH GRANT OPTION;

2. Test the connectivity by verifying that the following select statements return 0 or more rows if executed by Platform user-schema owner: SELECT * FROM <aga metrics schema>.AGENT_SKILL_GROUP_REAL_TIME; SELECT * FROM <aga metrics schema>.CALL_TYPE_REAL_TIME; SELECT * FROM <aga metrics schema>.CONTROLLER_TIME; SELECT * FROM <aga metrics schema>.INTERACTION_QUEUE_REAL_TIME; SELECT * FROM <aga metrics schema>.PERIPHERAL_REAL_TIME; SELECT * FROM <aga metrics schema>.QUEUE_SET1_REAL_TIME; SELECT * FROM <aga metrics schema>.QUEUE_SET2_REAL_TIME; SELECT * FROM <aga metrics schema>.SERVICE_MEMBER; SELECT * FROM <aga metrics schema>.SERVICE_REAL_TIME; SELECT * FROM <aga metrics schema>.SKILL_GROUP_REAL_TIME;

Configure a Connection to AGA Metrics Schema on a Different Oracle Instance than the Platform Schema

Use the information on this tab to configure connectivity to the AGA metrics data source when it is installed on a different Oracle instance than the Platform schema.

Before you begin:

• The tnsnames.ora file, located on the Oracle instance where the Platform schema resides, must contain a SID entry for the Oracle instance where the AGA metrics schema is located.
  Example:
  

  atlanta12 =
     (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST =p3458atl12 .us.prod.company.com)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = orcl12. us.prod.company.com)))

  You can locate your tnsnames.ora file in the $ORACLE_HOME/network/admin directory.

• To ensure a database link can be created, the user who will perform this operation must be granted the following permission:
  GRANT CREATE DATABASE LINK TO <platform user>

1. Create a database link inside the Platform schema or a public database link. For example: CREATE DATABASE LINK atl12.gcmdb81 CONNECT TO "<aga metrics schema>” IDENTIFIED BY "<aga metrics schema owner pwd>” USING 'atlanta12';

2. Test the links from SqlDeveloper or run a select statement as Platform user. For example: SELECT * FROM Controller_Time@atl12.gcmdb81;

Configure a Connection to Cisco ICM Data Source from Platform Database on Oracle Instance

Use the information on this tab to configure connectivity to the Cisco ICM data source (ICM AWDB) when the Platform database is installed on an Oracle instance.

Before you begin:

• Identify all ICM AWDBs that must be accessed by CCAdv and WA, as well as the SQL Servers that host those databases.
• Ensure that SQL Server accounts exist on all SQL Servers that host the ICM AWDBs accessed by CCAdv and WA.
• Ensure that each MSSQL Server account (see preceding bullet) has the MSSQL master database as a default database.
• Ensure that each ICM AWDB that must be accessed by CCAdv and WA has a user mapped to the relevant SQL Server account (see preceding bullets). The minimum requirement is that this user has permissions to select the data from the following CISCO source AWDB views:

  Agent_Skill_Group_Real_Time

  Call_Type

  Call_Type_Real_Time

  Logical_Interface_Controller

  Peripheral

  Peripheral_Real_Time

  Service

  Service_Real_Time

  Skill_Group

  Skill_Group_Real_Time

  Service_Member

  and

  AWDB Controller_Time table

• Ensure the user has the preceding object-level permissions or this user is assigned to an equivalent user-defined database role. If it is allowed by your organization’s security policy, the user can be assigned to any database standard role that includes the above minimum permissions. As an example, the user can be assigned to the standard db_datareader role.
• Ensure the Oracle Database Gateway for SQL Server is installed.
• Ensure the Gateway Initialization parameter file(s) exists for each Cisco ICM data source used by CCAdv and WA.
• Ensure the Oracle Net Listener configuration file has an entry for every gateway instance that exists for Cisco ICM data sources.
• Ensure the Oracle database that hosts the Platform schema is configured for Gateway Access and its tnsnames.ora configuration file contains a separate entry for each gateway instance. The alias from each such entry is used as database link creation parameters.

For detailed information about SQL Server security configuration, see the online documentation for Microsoft SQL Server at http://msdn.microsoft.com.

For detailed information about Oracle Database Gateway for SQL Server installation and configuration, see http://docs.oracle.com/cd/E18283_01/gateways.112/e12061/sqlserver.htm.

1. Create – or have your DBA create – a separate database link for each ICM source using a corresponding gateway instance. The links can be created inside the Platform schema or they can be created as public database links. Create database links using the following pattern: CREATE [PUBLIC] DATABASE LINK <arbitrary mssql database link name> CONNECT TO "<MSSQL username created for you in ICM awdb>" IDENTIFIED BY "<MSSQL password created for you in ICM awdb>" USING '<gateway_sid>'; where gateway_sid is the entry of the corresponding gateway instance contained in the tnsnames.ora file. For example: CREATE PUBLIC DATABASE LINK "prod67543.icm1" CONNECT TO "user1" IDENTIFIED BY "password1" USING 'dg4msql2';

2. Test the links from SqlDeveloper or run a select statement against the whole set of views as Platform user. For example: SELECT * FROM “Controller_Time”@prod67543.icm1;

The configuration of ICM data sources is now complete.