Softdial Publisher™

The SQL schema provides what Sytel believes is a best-fit normalisation of call center event data relating to:

• media sessions (typically phone calls)
• agent sessions (login / availability)
• agent activity
• queue activity
• campaign activity

The tables (see SQL Table Definitions) are updated on completion of an event cycle, e.g:

• the agent_states table is updated each time the agent state changes, resulting in an Agent Data [AZ] message
• the callcycles table is updated after the call session is ended and a Session Data [IZ] message has been issued (by Softdial CallGem™).

Each tenant requires their own set of tables. This means that either a separate database instance must be created per tenant, or each tenant needs to have their own schema within a database instance.

Schema-per-tenant is the model most common with users. This minimises DBMS licensing costs and it makes for a simpler model for database replication as there is only 1 instance to replicate for multiple tenants.

The default DSN is SccReporter. On installation, this is configured to point to the pre-configured mySQL database (accessible from http://localhost/apanel - Tools - phpMyAdmin).

The mySQL based reporting database is intended for demo and training purposes only and should not be used in production.

After setting up the production database and configuring the DSN, Publisher must be set up to use the new DSN, as follows:

1. Navigate to the Softdial/PUB/Primary/xml folder
2. Open the Publisher Primary configuration.xml file in an XML editor
3. Search for <module name="ODBC Reporter"...
4. Change the dsn, user and pwd parameters as required
   Show available parameters

   <!-- The ODBC DSN name -->
   <param name="dsn" value="SccReporter"/>
   
   <!-- The ODBC DSN username -->
   <param name="user" value="root"/>
   
   <!-- The ODBC DSN password -->
   <param name="psw" value="root"/>
   
   <!-- -->
   <param name="transaction" value="true"/>
   
   <!-- -->
   <param name="document expiry days" value="30"/>
   
   <!-- The database type e.g. MySQL, MSSQL -->
   <param name="dbtype" value="MYSQL"/>

5. Save the file.
6. Restart the Publisher Primary service.

The transformation files use rules and parameters that are defined in the rules.xml file (also located in Softdial/PUB/Primary/xml).

The schema described is as per the default implementation. It is possible to modify every detail of this implementation.

Common possibilities are:

• keying all tables by tenant and having a single output database across all tenants
• making additional permuted data to optimise MIS reporting
• extracting line-of-business information from the session data (assuming consistent format across campaigns) to add to the reporting model

Date & time values can be extracted from the SDMP messages to populate fields in the way appropriate for your needs.

This is done by including Sytel code via an XSLT Extension, declared in the stylesheet as follows:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:sytel="sytel:Extension">

This gives access to the following methods:

Method		Output
Universal (UTC)	Local	Type	Example
yearfromoautc	GetLocalYearFromOAUTC	int	2021
monthfromoautc	GetLocalMonthFromOAUTC	int	11
dayfromoautc	GetLocalDayFromOAUTC	int	30
hourfromoautc	GetLocalHourFromOAUTC	int	17
minutefromoautc	GetLocalMinuteFromOAUTC	int	26
secondfromoautc	GetLocalSecondFromOAUTC	int	36
-	GetOALocalFromOAUTC	double	44530.726805556

Date/ time options above are offered in 2 categories:

1. Universal - standardised world time (UTC), including a time-zone offset
2. Local - the time-zone offset applied to the values themselves

Local here refers to the location of the Reporting server.

For more on translating UTC to local time zone, see http://earthsky.org/astronomy-essentials/universal-time.

Usage can then follow this pattern:

<xsl:param name="Z0"/>
<xsl:value-of select="sytel:GetLocalDateAndTime($Z0)"/>