Power BI Technical Overview

DateTable

The datetable is created to have a centralized table for time intelligence. The table is build by creating a list of dates and add useful time intelligence based columns, such as the day of the week, month, year or week of the year. This makes it easier to filter graphs and reports based on the date range that user prefers.

Fields

Field

Type

Description
DateKey Date Field to create relations between multiple tables. Can be used a filter on its own to filter data from multiple tables on date.
Year Integer The year of the Datekey field as number
Month Integer The month of the Datekey field as number
Month Name Text The month name of the Datekey field as a string, can be used for filters in a more user friendly way
Quarter Integer The quarter of the year the Datekey field is in
Week of Year Integer The week of the year of the Datekey field
Week of Month Integer The week of the month of the Datekey field
Day Integer The day of the month of the Datekey field as a number
Day of Week Integer The day of the week of the Datekey field as a number
Day of Year Integer The day of the year of the Datekey field as a number
Day Name Text The day name in text of the Datekey field
Week Number Integer The week number of the Datekey field
Date for Filter Text A combination of the date and the day name for easy filtering
Day Name Abbreviation Text The abbreviation of the day name to make it possible to create smaller filter cards or slicers

 

Elements

The elements table is a hidden table in the online environment and by default only visible in the tables or data model tab in Power BI Desktop. This table is only used to retrieve the latest icon set for report navigation and was created as Power BI by default does not support custom icons that switch whenever the user changes themes.

Fields

Field

Type

Description
Element Text Name of the element/icon that is used as display in the
Link Text The url where the image is stored and retrieved from
Theme Text A field that is used as a filter to switch between dark and light theme

 

PowerBI_ChatTranscript

This table stores the chat transcript history, if there is any. Which makes it possible to read a transcript of the chat, by drilling down on a specific chat to the dialogue detail report and then drilling down on the chat correlation. If you don’t have chats, you could remove this table and the chat transcript report.

Calculations /Formulas

Field

Type

Description
Agent Column: Text A column that simply generates the name of the agent for a chat message.
ColorCoding Column: Text A column that is used to store the color code of the background. Power BI allows the color of a text field to be determined by a calculation.
ColorCodingText Column: Text A column that is used to store the color code of the text colorcoding. Power BI allows the color of a text field to be determined by a calculation.
Customer Column: Text A column that simply generates the name of the customer for a chat message.
Duration Response Column: Integer A calculation for the time between the message and the last message from the other party. Used for average response time in chat transcript reporting.
Message Count Agent Measure: Integer The total amount of messages the agent sent for the selected chat(s). Filters out system message.
Message Count Customer Measure: Integer The total amount of messages the customer sent for the selected chat(s). Filters out system message.
Response Duration Agent Measure: Integer The average in seconds of the Duration Response column, filters only on Agent messages.
Response Duration Contact Measure: Integer The average in seconds of the Duration Response column, filters only on Customer messages.

Fields

Field

Type

Description
chatConversationID Integer The ID of the chat
CorrelationID Text The ID of the conversation, in order for it to link to PowerBI_Dialogue
ID Integer The unique ID of the row
IsCustomer Boolean A Boolean field that tells you if the message was sent by the customer or not. Is used to split the customer and agent from the participant column
Message Text The text message that was sent by the participant during the chat. Also contains the IDRInteractive Dialogue Response (IDR) is an application to take orders via keypad or button through a computer. By choosing menu options the contact receives information, without the intervention of a human operator, or will be forwarded to the appropriate Agent. questions that the customer answers before the start of the chat, and the browser information
Participant Text The name of the participant who sent the chat message
SipmapID Integer The unique id of the participant, can be blank if the chat was created before an update to CoreThe Core is the center of Anywhere365. It manages all the Dialogues. 6.3 or higher
Sipuri Text Related 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. uri based on the sipmap id.
Time Time The time value when the message was sent

 

PowerBI_CallDetails

A table that contains all the call details of the conversation. It is used for one report, the Dialogue Detail, which reports on the steps that a dialogue took within the Anywhere365 environment. Report on a specific call or chat and gain insight in the exact route through the contact center; which agents where hunted, which queuepositions, skill and time intelligence. Calculations in this table focus on concatenating strings with other calculations / fields.

Calculations / Formulas

Calculation

Type

Description
Conversation Contact Meassure: Text Concatenates the string “Dialogue with” and the related caller from PowerBI_Dialogue
Conversation Recording URL Meassure: Text Checks if PowerBI_Dialogue has a related Recording URL. And generates a string related to the outcome. It does not use the related URL, but rather generates a string based on the outcome
Conversation Survey URL Meassure: Text Checks if the column Survey URL is filled and generates a string based on the outcome.
Conversation Time Meassure: Text Generates a string that tells you the conversation time based on the first related time value and the last related time value from PowerBI_CallDetails
Conversation 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. Meassure: Text Looks up the value UCC Name from the UCC table. It searches the UCC table with the LOOKUPVALUE, where it searches the table based on the ucc_id value from PowerBI_CallDetails
Survey URL Column: Text Creates a new column in which the survey URL is placed. It is extracted from PowerBI_CallDetails message column and only filled if the type matches the input.

Fields

Field

Type

Description
Correlationid Text The ID of the conversation. In powerBI_Calldetails this ID is not unique, as a conversation can and will contain multiple steps.
Date time Date time The date and time the step of the conversation took place
ID Integer Unique ID of the message
ImageURL Text The url that retrieves the image that is linked to the step. This image is displayed in the Dialogue Detail report and needs to be dynamic as not all conversations will be the same
Message Text A field that contains information about the step type, such as the sip address of the agent or queueposition
Time Time The time the step was taken in the UCC
Type Text A preset of step types that define the step.
UCC_ID Integer The unique ID of the UCC, is used to link the table to the UCC_Name table and retrieve the name of the UCC

 

PowerBI_Classifications

The classifications table contains all the classified calls. Whenever an agent classifies the call, the summary will be stored in this table. Is used only for the classification report. This table is limited to the first five levels of classification.

Calculations / Formulas

Field

Type

Description
Percentage of Total Classifications Measure: Decimal The percentage of calls classified within this group compared to the total classified calls
Total Classification Rate Measure: Decimal The percentage of calls classified compared to the total amount of calls that are accepted. Does not allow drilldown as the measure above, but does offer a percentage if used in a total perspective rather than a specific perspective

Fields

Field

Type

Description
Class1 Text The first level of classification
Class2 Text The second level of classification
Class3 Text The third level of classification
Class4 Text The fourth level of classification
Class5 Text The fifth level of classification
ClassCombined Text A text field in which all classes above are combined. Is the default value for the reporting, but can be replaced with any of the above classes if the intention is to create a report on a specific level
CorrelationID Text The unique ID of the conversation. Is used to link this table to PowerBI_Dialogue
Date Date The date field on which the classification was created. Used for time intelligence
Name Text The name of the related UCC
Time Time The time when the classification was created, can be used for time intelligence.
UCC_ID Integer The unique ID of the UCC in which the classification was made

 

PowerBI_Dialogue

This is the main table of the reporting template. In this table all the conversations are stored with their summary. It contains information concerning the queuetimes, talktimes, skillnames, start and end times, and summaries on times, transferees and contact. Most of the reports in the template contain information from this table and some calculations in other tables refer back to this one

Calculations / Formulas

Field

Type

Description
Average Initial TalkTime Formatted Measure: Text Formats the average of the Initial TalkTime to a HH:MM:SS format, which is more readable for the end user.
Average QueueTime Formatted Measure: Text Formats the average of the QueueTime to a HH:MM:SS format, which is more readable for the end user.
Average SessionTime Formatted Measure: Text Formats the average of the SessionTime to a HH:MM:SS format, which is more readable for the end user.
Average TalkTime Formatted Measure: Text Formats the average of the TalkTime to a HH:MM:SS format, which is more readable for the end user.
Average TransferTime Formatted Measure: Text Formats the average of the TransferTime to a HH:MM:SS format, which is more readable for the end user.
Classification Column: Text The related total classification of the PowerBI_Classifications table
Conversation SkillChanged Column: Text Checks if the skillchanged field is empty and generates a string of the outcome
DialogueVolume Measure: Integer Counts the distinct (unique) conversations in the table
DialogueVolume HeatMap Measure: Integer Counts the distinct (unique) conversations in the table and replaces the value if its empty to 0.
First Time Right Measure: Decimal The percentage of all accepted dialogue that did not change to a different skill. The calculation is based in the skillchanged column and determines if a conversation is first time right if this value is empty
Frequent Callers Measure: Integer Uses another calculation as bases: Parameter Frequent Caller. Calculates the total amount of callers that has contacted your contact center more times than the parameter provided. By default this parameter is set to 10, but can be increased or decreased to any number.
Frequent Transferees Measure: Integer Uses another calculation as bases: Parameter Frequent Caller. Calculates the total amount of calls per transferee with more calls than the parameter provided. By default this parameter is set to 10, but can be increased or decreased to any number.
Handle Rate Measure: Decimal The total number of calls accepted divided by the total number of calls accepted + missed
InitialAgent Column: Text Matches the agent sipmap uri with the related Agent Table. Instead of using the default initialagent column you can use this column if you decide to create custom reports that combines multiple tables with different agent columns
No Skill Rate Measure: Decimal The percentage of conversations that leave the 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. before choosing a skill. Based on the actiontype column, which is precalculated in the SQL View
Overflow Measure: Integer The total amount of conversations with the actiontype overflow.
Parameter Frequent Caller Measure: Integer Used as bases for frequent caller and transferee calculations. Functions as a gateway function between the parameter value and the functions.
Quality Monitoring Value Formatted Column: Text Formats the Q.M. value to a general number or places a string if the conversation did not receive a Q.M. value.
Rated Dialogue Measure: Decimal Calculates the amount of dialogue that was rated (Q.M.)
SLA Measure: Integer The percentage of calls that are accepted by an agent before it reaches a specific time limit. This time limited is changeable by replacing the SLASeconds Variable from 20 to any desired number (in seconds).
Transfercode Column: Integer Translates the transfer string column to a number based on for further calculations.
Transferrate Measure: Decimal The percentage of conversations that have an initialtransferee linked to it.
Transfers:external Measure: Integer The amount of conversations that received an external type transfer
Transfers:internal Measure: Integer The amount of conversations that received an internal type transfer
Transfers:otherdomain Measure: Integer The amount of conversations that received an Other domain type transfer
Transfers:ucc Measure: Integer The amount of conversations that received an UCC type transfer
Unrated Dialogue Measure: Integer The total amount of conversations that where not rated (Q.M.)

Fields

Field

Type

Description
Accepted Integer A field that is either empty or 1 based on if a conversation was accepted
Acceptedtime Date Time The date time stamp a conversation was accepted by an agent.
Actiontype Text A text field that tells you what happened with the conversation.
Caller Text The phonenumber or sip address of the caller
Callersipmapid Integer The unique sipmap id of the caller. Can be used to link the table to the sipmap table for a centralized filter option
Correlationid Text The unique ID of the conversation. Is used to link this table to powerBI_Hunt, Calldetail, Classification and Chattranscript
Date Date The date of the starttime of the conversation. Is used to link to the date table to perform time intelligence
Dialoguetype Text The type of dialogue the conversation is classified as.
Endpointuri Text The uri of the endpoint the conversation was received on
Endtime Date Time The date time stamp that the conversation was ended on
Forwarded Integer A field that is either empty or 1 based on if a conversation was forwarded
Index Integer A unique number that is used to sort correlationID based on start time in table where you do not want the start time to be displayed
Initialagensipmapid Integer The unique ID of the agent that accepted the conversation, which is used to retrieve the agent name from the Sipmap table
Initialtalktime Integer The duration of the first conversation with an agent. In case the call is forwarded, the talktime that took place after the forward is not included
Initialtransferee Text The sipaddress or the phone number of the agent that received the first transfer
Initialtransfereesipmapid Integer The unique id of the transferee
Inqueuetime Date Time The date time stamp when a conversation entered the queue
Ivrtime Integer An integer of the duration the conversation spent in the IVR, in seconds
Missed Integer A field that is either empty or 1 based on if a conversation was Missed
Modality Text The modality type of the conversation
NoContact Integer A field that is either empty or 1 based on if a conversation was outbound and was not picked up by the contact
NoSkill Integer A field that is either empty or 1 based on if a conversation was ended before the contact chose a Skill
Qoah Integer Integer of the quarter of hour the conversation started in
Qualitymonitoringvalue Integer The value the contact gave to the conversation after filling in the Q.M. at the end of a conversation
Queuetime Integer The time in seconds that the conversation was in the queue
Quickdrop_10 Integer A field that is either empty or 1 based on if a conversation was abandoned by the caller within 10 seconds of entering the queue
Quickdrop_5 Integer A field that is either empty or 1 based on if a conversation was abandoned by the caller within 5 seconds of entering the queue
Recordinglocation Text The url to the location where the recording is stored. If there is no recording present, the field will be empty
Skillchanged Text The name of the skill that the conversation was transferred to
Skillchosen Text The name of the skill that was chosen in the IVR
Skilltype Text A combination of the dialoguetype and the modality type
SLA1 Integer This column contains a 1 if the conversation was answered within the SLA limit (default in the SQL-views is 20 seconds)
SLA2 Integer This column contains a 1 if the conversation was answered outside the SLA limit (default in the SQL-views is 20 seconds), but within 120 seconds
SLA3 Integer If the conversation was accepted outside both SLA1 and SLA2 limits
StartingTime Time The time stamp of the start of the conversation
Starttime Date Time The date time stamp of the start of the conversation
Supervisor Text The sip address of the supervisor of the agent that accepted the conversation
Supervisorsipmapid Integer The unique ID of the sip address of the supervisor
Talktime Integer The total talk time in seconds, including talk time after transfers
Time: Initial talk Time Formatted initial talk time to HH:MM:SS
Time: OnHold Time Formatted On hold time to HH:MM:SS
Time: Queue Time Formatted queue time to HH:MM:SS
Time: Talktime Time Formatted talk time to HH:MM:SS
Time: TotalSession Time Formatted Total session time to HH:MM:SS
Time: Transfer Time Formatted transfer time to HH:MM:SS
Totalholdtime Integer The total time the conversation was put on hold in seconds
Totalsessiontime Integer The total time in seconds the conversation lasted from start till finish
Transfertimespan Integer The total time in seconds that the conversation spent in transfer
Transfertype Text The type of transfer of the conversation (UCC, internal, external or other)
UCC_ID Integer The unique ID of the UCC that the conversation took place in
UCC_Name Text The name of the UCC the conversation took place in
Voicemail Integer A field that is either empty or 1 based on if a conversation was pickup by a voicemail
Voicemaillocation Text The location of the voicemail

 

PowerBI_Hunts

This table contains all information concerning the hunts. Please note that a call can have multiple hunts, to multiple agents, before it is accepted or missed. Therefor the total amount of hunts can differentiate from the total amount of conversations in PowerBI_Dialogue.

Calculations / Formulas

Field

Type

Description
Above Average Hunttimes Measure: Integer Returns the number of agents that have above average hunttime before they accept a conversation
Average Hunttime Measure: Integer Formats the hunttime in seconds to HH:MM:SS for a more readable number
Average Initial Talktime Measure: Integer Formats the initialtalktime in seconds to HH:MM:SS for a more readable number
Hunted Agent Column: Text The related hunted agent from the Sipmaptable
Hunt/Accept Rate Measure: Decimal The ratio of accepted hunts
Missed Measure; Integer The total of missed hunts
SLA Hunt Measure: Decimal The percentage of hunts that are accepted by an agent before it reaches a specific time limit. This time limited is changeable by replacing the SLASeconds Variable from 20 to any desired number (in seconds).

Fields

Field

Type

Description
Accepted Integer Contains a 1 if the hunt was accepted and is empty it is missed
Actiontype Text Either Accepted, Missed or Quickdrop depending on result
Agentsipmapid Integer The unique ID of the agent that was hunted
CorrelationID Text The unique ID of the conversation, not the ID of the hunt
Date Date The date stamp when the hunt took place
Hunttime Time The total hunttime formatted to HH:MM:SS
ID Integer The unique ID of the hunt
Initial Talktime Time The initial talktime formatted to HH:MM:SS
Initialtalktime Integer The initial talk time of the conversation after it was accepted, in seconds
Ringtime Integer The time the hunt took before begin accepted or missed
Skill Text The name of the skill the hunt took place in
SLA1Hunt Integer This column contains a 1 if the hunt was answered within the SLA limit (default in the SQL-views is 20 seconds)
SLA2Hunt Integer This column contains a 1 if the hunt was answered outside the SLA limit (default in the SQL-views is 20 seconds), but within 120 seconds
SLA3Hunt Integer If the hunt was accepted outside both SLA1 and SLA2 limits
Time Time The time stamp of the start of the hunt
UCC Text The name of the UCC the conversation took place in
UCC_ID Integer The unique ID of the UCC that the conversation took place in

 

PowerBI_Presence

The PowerBI_Presence table is currently not being used in reports anymore but is being used as basis for the new PresenceGrouping Table. The table is hidden from the end user and only visible in the desktop client. Please note that this table determines the content of the new Presence table and should not be deleted if you want to report on presence information.

 

PresenceGrouping

The PresenceGrouping is the new presence table of the template. It groups the time per hour to get a better understanding of the time spent in a presence state during the day. The table is created in DAX rather in the Power Query editor, and therefor it is not a SQL-view. It is based on the old table and cannot exist without it. The new table combined with the old table is faster and has less impact on the database than creating the formula in SQL.

Calculations / Formulas

Field

Type

Description
Agent Column: Text Related agent from the Sipmap table
Percentage% Measure: Decimal The percentage of time spent in a specific presence state. Used in Status Overview, as a value under a column hierarchy.
Statuscode Column: Integer Translation from text to integer for use of color coding rules in the status overview report
Time Formatted Measure: Text Formatted the time in seconds to HH:MM:SS format
Total Time Measure: Integer The total time in seconds spent in a selected presence state

Fields

Field

Type

Description
End Date Time The date time stamp of the ending hour of the status. Used to group and count the seconds of the presence per hour
Formal Text The type of signin the agent was at that moment. Can be one of three options: Formal – Not Signed in, Formal – Signed and informal
ID Integer The ID of the presence change. Is unique for a presence change, but as the data is grouped in hour slots, presence rows can be duplicated if the start or end time fall outside the hour group
PresenceEnd Date Time The actual date time stamp of the end time of the presence
PresenceStart Date Time The actual date time stamp of the start time of the presence
ReasonCode Text The reasoncode that was connected to the presence change
Seconds Integer The seconds that the presence was active in the start to end slot
SipmapID Integer The unique ID of the agent
Start Date Time The date time stamp of the starting hour of the status. Used to group and count the seconds of the presence per hour
StartDate Date The date stamp of the presence change
StartHour Integer The hour that is connected to the start of the presence change
Starttime Link Time The Time stamp that is used to connect to the time table
Status Text The presence state, based on Skype/Teams status
Status Hierarchy Hierarchy A hierarchy between status and reasoncodes, is used in the table of the Status Overview report
UCCID Integer The unique ID of the UCC that the conversation took place in
UCCName Text The name of the UCC the conversation took place in

 

SipMap

The Sipmap table contains all the sip addresses of customers, agents, transferees and supervisors. This table if usually referenced when retrieving the related address with a sipmap ID.

Fields

Field

Type

Description
ID Integer The unique ID of a contact, agent, transferee or supervisor
LineURI Text The phone number related to the ID
URI Text The sip address of the agent, contact, transferee or supervisor

 

UCC_Name

The UCC_Name table contains all the names and IDs of the UCCs in the database

Fields

Field

Type

Description
ID Integer The unique ID of the UCC and is used to link to multiple tables
Name Text The name of the UCC

 

SQL Views Entity Relation Diagram

Below the ERD of the SQL views is displayed. Please note that this ERD only contains the SQL-views (blue) and two optional tables (red). The content of the tables represent the content of the SQL-views rather than the Power BI Tables. For the datamodel behind the Power BI template, please use the Power BI Template under the data model tab.