# Genesys Quality Management Suite 8.1.520 : Integrating the Quality Manager Database with Excel

Analyze Quality Manager data on a Windows PC by connecting the Quality Manager database to Microsoft Excel. The procedure described below requires the following:

- Quality Manager is l.icensed, functional, and using the default PostgreSQL database for data storage.
- Administrator permissions to the Genesys Quality Management Suite installation including root SSH permissions.
- At least installation permissions on the Windows XP, Vista, or Windows 7 client PC running Microsoft Excel.
- The client PC is connected via an IP network to the Quality Manager database server, typically the Genesys Quality Management Suite server for standalone installations.
- Experience of Linux file editing commands, relational database structures, and SQL syntax.

## Setup Instructions

Setup consists of three stages:

- Create a read-only user on the Quality Manager database server.
- Set up the ODBC source on the client PC running Excel.
- Import the ODC query files for use with Excel.

## Create a Read-only Database User

To create a read only database user:

Connect to the main Genesys Quality Management Suite server via an SSH Client. Log in as `admin`. Enter `su -` to log in as the root user. Enter the password, the default is `zoomcallrec`.

1. Open the file at`/opt/callrec/data/psql/pg_hba.conf` and add a line like the following to enable access from the client PC, where the PC's IP address and range are very narrowly defined, ideally an individual static IP address. The following example allows connection from IP addresses in the range 192.168.10.0 - 192.168.10.255:

       host all all 192.168.10.0/24 md5

2. Save the file, then run the following command to apply this configuration change to the database:

       /etc/init.d/postgresql reload

3. Start up the PSQL tool, logging in to the database as the postgres user:

       psql -U postgres callrec

The following commands in this step are all database commands in SQL format. For best results, type or paste in each individual line, then press ENTER.

1. Create the database user. Replace the sample username: `excel` and password: `excel1234` with preferences, but ensure the remaining commands are updated appropriately:

       CREATE ROLE excel WITH PASSWORD 'excel1234';

2. Enable this user to log in:

       ALTER ROLE excel LOGIN;

3. Enable this user to view the callrec and wbsc schemas, for Call Recording and Quality Manager respectively:

       GRANT USAGE ON SCHEMA callrec TO excel;
       GRANT USAGE ON SCHEMA wbsc TO excel;

4. Grant select (read permission) on the tables from the schema:

       GRANT SELECT ON wbsc.answers TO excel;
       GRANT SELECT ON wbsc.companies TO excel;
       GRANT SELECT ON wbsc.criteria TO excel;
       GRANT SELECT ON wbsc.evalanswers TO excel;
       GRANT SELECT ON wbsc.evalcalls TO excel;
       GRANT SELECT ON wbsc.evaluations TO excel;
       GRANT SELECT ON wbsc.questforms TO excel;
       GRANT SELECT ON wbsc.questiongroups TO excel;
       GRANT SELECT ON wbsc.questions TO excel;
       GRANT SELECT ON wbsc.sc_users TO excel;
       GRANT SELECT ON wbsc.subevaluation TO excel;
       GRANT SELECT ON wbsc.user_belongsto_ccgroup TO excel;
       GRANT SELECT ON wbsc.ccgroups TO excel;
       GRANT SELECT ON wbsc.callwrapups TO excel;
       GRANT SELECT ON wbsc.interaction_types TO excel;
       GRANT SELECT ON wbsc.categories TO excel;
       GRANT SELECT ON wbsc.database TO excel;
       GRANT SELECT ON wbsc.languages TO excel;
       GRANT SELECT ON wbsc.user_role TO excel;
       GRANT SELECT ON wbsc.roles TO excel;

5. Exit the PSQL utility (type `\q` and press ENTER) and end the SSH session.

### Set up the ODBC Source

The following procedure is performed on a Windows PC with administrative permissions. Read the following information before starting:

- The type of Operating System (32-bit or 64-bit). This can be determined using the following Microsoft Support page:
  http://windows.microsoft.com/en-us/windows7/find-out-32-or-64-bit.
- The type of Microsoft Excel installation (32-bit or 64-bit). This can be seen in Excel 2007 by viewing the File >Help > About Microsoft Excel section.

Depending on the type of Excel installation, proceed as follows:

Excel 64-bit

1. Unzip and install the PostgreSQL ODBC driver after downloading the latest zipped MSI installation package from the following URL:http://www.postgresql.org/ftp/odbc/versions/msi/. The 64-bit drivers are named with the suffix `-x64.zip`.
2. Open the following Windows dialog panel: Administrative Tools > Set up data sources (ODBC), or paste the following at a Windows command prompt:
   `%systemdrive%\Windows\system32\odbcad32.exe`
3. On the Drivers tab, ensure that the PostgreSQL drivers are listed, then click Add on the User DSN tab.
4. Select the PostgreSQL Unicode(x64) driver.
5. Configure the database server access credentials for the database user created earlier.
   Use the following parameters, modifying the Server, Port, Username, and Password fields as required. The Data Source field must be set to the value shown to run the sample ODC database queries unmodified.
   - Data Source: `ZOOMQM-Quality Manager`
   - Description: leave blank.
   - Database: `callrec`
   - Server: (Genesys Quality Management Suite server IP address or fully qualified domain name).
   - Port: `5432`
   - Username: `excel`
   - Password: `excel1234`
6. Click Test to check the connection, then Save.

Excel 32-bit

Follow steps 1-6 above (the screens vary), with the following differences:

- Step 1: Download a 32-bit MSI installation file (without the`x-64.zip` suffix), then unzip and install it.
- Step 2: On a 64-bit Windows system, run the 32-bit ODBC Administrator dialog box to see the 32-bit PostgreSQL ODBC drivers. Paste the following at a Windows command prompt:
  `%systemdrive%\Windows\SysWoW64\odbcad32.exe`
- Step 4: Select the PostgreSQL Unicode driver.

### Import the ODC Files

Sample database queries have been provided in ODC (Office Database Connection) format. The samples can be imported into the Office Data Connections list to display data, such as the list of Quality Manager evaluations and details of individual questionnaires, evaluations and users.

To test the sample queries, download and unzip the ODC files to a temporary folder on the client PC. Then do the following:

1. Open Excel and click the Data menu.
2. Click Existing Connections.
3. In the Existing Connections dialog, click Browse for More....
4. Navigate to the location of the unzipped ODC files in the Select Data Source dialog and select a file.
5. Click Open. If the ODBC data connection, set up earlier is correctly configured, the Import Data dialog opens.
6. In the Import Data dialog, decide where and in what format to place the data and click OK.
   Note: Start with the Table format until familiar with the data structure.
7. The data is imported. Data is refreshed both when the saved workbook is re-opened and when clicking Refresh.
   There is no 'remove' option in the Excel Existing Connections dialog. However, to remove unnecessary external data connections from this dialog, simply delete the appropriate ODC files or their shortcuts in the `My Data Sources` directory. The following example opens this location on a Windows 7 PC: `%UserProfile%\Documents\My Data Sources`.

## Modifying ODC SQL Queries

Although SQL queries in individual ODC files can be edited in any text editor, there is the danger of errors creeping in due to the character-escaped SQL syntax that is used. A more robust method is to modify the SQL query in Excel after import. This does require that the ODC connection has been successfully imported into Excel using the setup procedure above:

1. In the Excel Data menu, click Connections to open the Workbook Connections dialog.
2. Ensure the ODC connection is displayed and select it.
3. Click Properties... to open the Connection Properties dialog.
4. Click the Definition tab.
   - EITHER: View and modify the SQL query directly.
   - OR: Edit the query in Microsoft Query. If an error states that: "This query cannot be edited in the Query Wizard", edit the SQL directly by clicking SQL in Microsoft Query after acknowledging the error. Close the Wizard to return to the Connection Properties dialog.
5. Click OK to commit the changes, then accept any ODC file modification requests, after which the data is refreshed from the database according to the updated SQL query.