You are here: Advanced > Database Schema

Introduction

This document outlines Clarity Connect’s data warehouse database architecture, which is used for Clarity Connect reports.

It is intended for experienced SQL administrators familiar with highly normalized relational databases, star schema, indexes, and general SQL functionality.

Data Warehouse Database

Architecture

The data warehouse database uses a star schema architecture. A fact table, which records event-based and time-based metrics, serves as the center of the star schema and is surrounded by dimension tables, which are companion tables to the fact tables.

While there are several different fact tables, Clarity Connect reports pull from the primary fact tables, FactSession and FactAgent.

When referencing the fact tables in this document, keep in mind the FactSession.CallDispositionId is the final state of the session, and the FactSession.DW_CallDispositionKey (and FactSession.DW_CallSubDispositionKey) is the agent-assigned session categorization.

FactSession contains one entry for every Clarity Connect session (referenced via SessionId). FactAgent contains one entry for every Clarity Connect agent per minute of each day.

Database Diagrams

Keep the following in mind when referencing the database diagrams included below:

  • There is one FactSession entry for each call, and it contains most metric information for that session.
  • FactCallFlowNode entries contain information about the session’s behavior in the call flow.
  • There is one FactAgent entry per agent per minute.
  • FactAgent entries contain information regarding away reason codes.
  • Durations are measured in ticks (10,000,000 per one second).
  • Tables ending in “archive” are direct copies from the runtime database. Data is moved from the runtime database to the data warehouse database every 15 minutes. Data is purged from the runtime database every four days. Agent status history data is purged from the runtime database every 90 days. Each of these values are default values and can be configured as desired.
  • The FactMessage table refers to email routing.
  • The FactSessionParticipation table keeps track of all actions that occurred during a given session.