Power BI Technical Overview

Calculations / Formulas

PowerBI_Classifications

Calculation

Description

Percentage of Total Classifications

Calculates the percentage of classified dialogue per classification, in comparison with the total classified dialogue. Divides the distinct correlation id’s from PowerBI_Classifications for each unique value in the classification columns, with the total classified calls.

Total Classification Rate

Calculates the ratio of classified dialogue, in comparison with the total classifiable dialogue. Divides distinct correlation id’s from PowerBI_Classifications with the distinct correlation id’s from PowerBI_Dialogue that where accepted.

 

PowerBI_Dialogue

Calculation

Description

Average InitialTalkTime

Formats the average InitialTalkTime to string format “HH:MM:SS”

Average QueueTime

Formats the average QueueTime to string format “HH:MM:SS”

Average TalkTime

Formats the average TalkTime to string format “HH:MM:SS”

Average TransferTime

Formats the average TransferTime to string format “HH:MM:SS”

Callvolume

Volume of all dialogue. Counts all distinct correlation id’s. Whenever there are no correlation id’s available, value is 0 instead of Blank

CallvolumeCurrentWeek

Calculates the callvolume in the current week (column added with query)

CallvolumePrevWeek

Calculates the callvolume of the previous week (column added with query)

First Time Right

Calculates the percentage of conversations that were accepted and did not change skill during the conversation.

Frequent Callers

Count distinct callers that called more than the threshold. Creates a temporary table with unique callers that contacted more times than the threshold. The threshold is a measurement FrequentCallers.

Frequent Transferees

Count distinct transferees that received more conversations than the threshold. Creates a temporary table with unique transferees that received more conversations than the threshold. The threshold is a measurement FrequentCallers.

Frequent Callers

Used as a parameter/threshold for other measurement. Looks up a value from the ParameterFrequentCallers table. Can be used as a variable for a slicer.

Handle Rate

Calculates the handle rate of conversations.

No Skill Rate

The percentage that conversations ended with a contact not chosing a Skill. Creates a temporary table to filter out conversations other than actiontype “NoSkill”

Overflow

Calculates the conversations with the actiontype “Overflow”

Rated Dialogue

Calculates the total amount of conversations that received contact feedback in the form of Quality Monitoring

SkillchangedVolume

Calculates the amount of conversations that included a skillchange

SLA

The service level agreement calculation. Divides the sum of SLA1 conversations with the total conversations. If result is empty of blank, returns a string with “No SLA”.

Transferrate

The ratio of the transferred conversations compared to the total applicable conversations.

Transfers:external

Total transferred conversations that went to an external transferee

Transfers:internal

Total transferred conversations that went to an internal transferee

Transfers:otherdomain

Total transferred conversations that went to a sipThe Session Initiation Protocol (SIP) is a protocol to make multimedia communication (audio, video and other data communication) possible and it is also used for Voice over IP (VoIP). SIP has similarities with other Internet protocols such as HTTP and SMTP. address with a different domain

Transfers:UCCUCC stands for Unified Contact Center and consists of a queue that can be handled by Agents Each Contact Center has its own settings, interactive voice response questions and Agent with specific skills. Agents can be member of, or sign up to, one or more Contact Centers.

Total transferred conversations that went to another UCC

Transfertime format

Formats the average Transfertime to string format “HH:MM:SS”

Unrated Dialogue

Counts distinct conversations that did not receive any Quality monitoring value (equals Blank)

 

PowerBI_Hunts

Calculation

Description

Above Average Hunttimes

Creates temporary table with all agents and their average hunttime. Counts the distinct agents that have above average hunttimes.

Above Average Talktime

Creates temporary table with all agents and their average talktime. Counts the distinct agents that have above average talktimes.

Above Average TransferRate

Creates temporary table with all agents and their transferrate. The transferrate is only calculated with accepted dialogue. Counts the distinct agents that have above average transferrates.

Average Holdtime

Formats the average of Holdtime to string format “HH:MM:SS”

Average Hunttime

Formats the average of Hunttime to string format “HH:MM:SS”

Average InitialTalktime

Formats the average of InitialTalktime to string format “HH:MM:SS”

Below Average Accept Rate

Creates temporary table with all agents and their average Accept Rate. Counts the distinct agents that have below average Accept Rates

Hunt/Accept rate

Divides the Sum of all accepted calls with the total amount of hunts

Missed

Calculates the total missed hunts by subtracting the total Hunts with the total Accepted hunts

Missed Hunts By Top/Bottom 5 “AcceptRate/Hunttimes/TranferRate/TalkTime”

Calculates the missed hunts of the Top (or bottom) 5 agents per grouping (four different measures). Determines the Top/Bottom 5 agents by creating a temporary table with

SLA Without Top “AcceptRate/Hunttimes/TranferRate/TalkTime”

Calculates the SLA without the top or bottom 5 agents. First creates a variable with a temporary table with all distinct agent and their “AcceptRate/Hunttimes/TranferRate/TalkTime”. Then create a new variable which adds ranking to the first variable. Calculates the SLA with all agents outside of the top/bottom 5 of the second variable.

SLAHunt

Calculates the hunt SLA by diving the accepted hunts within SLA1 by the total of hunts. If value is empty, returns 0%

Transferred (Calculated column)

Column to use in calculations, generates a column with a value per row that is either 0 or 1. In case when a conversation was transferred, fill value with 1, otherwise fill value with 0.

 

PowerBI_Presence

Calculation

Description

% “Status”

Calculates the percentage of time spent in a specific Skype status. Calculation excludes time spent with status Offline. “Status” can be replaced with the basic Skype statuses for the calculation of that specific status. Per specific status a new measure was created.

Measure “Status”

Calculates the SUM of seconds per status. “status” can be replaced with the basic Skype statuses for the calculation of that specific status. Per specific status a new measure was created.

Time “Status”

Concatenation of the “Measure “Status”” calculation to change the integer format to a string format. This way a time-like string is created to enhance the readability of the time spent per status. Concatenates the calculation for hours with minutes and seconds to HH:MM:SS format. “Status” can be replaced with the basic Skype statuses for the calculation of that specific status. Per specific status a new measure was created.

 

View

PowerBI_Dialogue View

Description

This table show a summary of all conversations. Similar to the session summary table, which the reports in Dialogue intelligence use. Created with the thought to simplify the data and it’s interpretation.

Fields

Field

Type

Description

Date

Date

Date of the conversation

Hour

Int

Hour the conversation took place

Qoah

Int

Quarter of the hour the conversation took place

Dialoguetype

Varchar(255)

Type of dialogue: Inbound, Outbound, Direct

Modality

Varchar(255)

Call, Chat, IOT, Dialer, Email, CallMeCallMe adds a button on your website where the customer can enter his / her number to be called by the UCC, saving the customer in telephone costs., AutonomousDialer, CampaignDialer, DirectDialer, VoicemailDialer

Actiontype

Varchar(255)

None, Accepted, Missed, Forwarded, Voicemail, Noskill, Prompt, Overflow

Starttime

Datetime

Timestamp conversation started

Inqueuetime

Datetime

Timestamp conversation entered queue

Acceptedtime

Datetime

Timestamp conversation was accepted

Transfertime

Datetime

Timestamp conversation was transferred

Endtime

Datetime

Timestamp conversation was terminated

Ivrtime

Int

Time conversation spend in ivrInteractive Voice Response (IVR) is a telephone application to take orders via telephone keypad or voice through a computer. By choosing menu options the caller receives information, without the intervention of a human operator, or will be forwarded to the appropriate Agent.

Queuetime

Int

Time conversation spend in queue

Initialtalktime

Int

Time conversation spend with first agent

Talktime

Int

Time conversation spend with all agents

Transfertimespan

Int

Time conversation spend with transferee

Totalsessiontime

Int

Total conversation time

Totalholdtime

Int

Number of seconds the conversation was put on hold

Correlationid

Varchar(50)

Correlationid of conversation

Ucc_id

Int

UCC Id of conversation

Ucc_name

Varchar(50)

Name of the corresponding ucc

Endpointuri

Varchar(255)

Uri of the endpoint

Skillchosen

Varchar(255)

Title of first skill

Skillchanged

Varchar(255)

Title of second skill

Qualitymonitorvalue

Int

Feedback from the QM

Caller

Varchar(255)

Uri of caller

Initialagent

Varchar(255)

Uri of first agent

Initialtransferee

Varchar(255)

Uri of first transferee

Supervisor

Varchar(255)

Uri of supervisor

Recordinglocation

Varchar(1023)

Location of the recording

Voicemaillocation

Varchar(1023)

Location of the voicemail

SLA1

Bit

If conversation was within SLA1

SLA2

Bit

If conversation was within SLA2

SLA3

Bit

If conversation was SLA 3

Accepted

Bit

If conversation was accepted

Missed

Bit

If conversation was Missed

NoContact

Bit

If conversation did not have a contact

Quickdrop_5

Bit

If conversation was dropped within 5 seconds

QuickDrop_10

Bit

If conversation was dropped within 10 seconds

Forwarded

Bit

If conversation was forwarded

Voicemail

Bit

If conversation reached a voicemail

NoSkill

Bit

If conversation did not have a skill

 

PowerBI_Hunt View

Description

This table show a summary of all hunts.

 

Fields

Field

Type

Description

Id

bigint

Record ID

Correlationid

Varchar(50)

Correlationid of conversation

Ucc

Varchar(50)

Name of the corresponding ucc

Initialagent

Varchar(255)

Agent

ringtime

Int

Duration of hunt

initialtalktime

Int

Duration of the first talk

Accepted

Bit

If hunt was accepted

Hunted

Bit

If an agent was hunted

Skill

Varchar(255)

Title of Skill for this hunt

SLA1Hunt

Bit

If hunt was accepted within SLA1

SLA2Hunt

Bit

If hunt was accepted within SLA2

SLA3Hunt

Bit

If hunt was accepted within SLA3

Actiontype

Varchar(255)

Actiontype of the hunt: None, Accepted, Missed, Forwarded, Voicemail, Noskill, Prompt, Overflow

 

PowerBI_Presence View

Description

This table show all presence updates of your agents

 

Fields

Field

Type

Description

Id

bigint

Unique ID of the presence change

Ucc_name

Varchar(50)

Name of the corresponding ucc

Devicetype

Varchar(50)

Device type on which the presence change was made

Location

Varchar(50)

SameEnterprise, FederatedFederation makes it possible to communicate with customers, suppliers, partners and others, via Lync Server 2010 and Lync Server 2013, which are not in the same Lync environment., Unknown

Starttime

Date time

Starttime of the presence change

Endtime

Date time

Endtime of the presence change

Duration_in_micro_seconds

Bigint

Duration of the presence change in microseconds

Status

Varchar(50)

The presence the agent changed to

Formal

Varchar(50)

Formal or Informal

 

PowerBI_Classification View

Description

This table show all the conversations that have classifications

Fields

Field

Type

Description

Correlationid

Varchar(50)

Correlationid of conversation

ClassificationType

Varchar(255)

Layer of classification

ClassificationValue

Varchar(255)

The value of the classification

Ucc_name

Varchar(50)

Name of the corresponding ucc

 

SQL Views Entity Relation Diagram

The SQL view PBI_Presence is not directly connected to the other views. It does not share a common field and does not contain a foreign key from another view. It was created to deliver insight into the agent presence updates, which is not related to conversations.