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.

---

Did you find this page helpful?

Yes No