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 Core 6.3 or higher |
Sipuri | Text | Related sip uri based on the sipmap id. |
Time | Time | The time value when the message was sent |
PowerBI_CallFlow
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 queue positions, 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 UCC | 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 after transfer till finish |
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
Sorry about that
Why wasn't this helpful? (check all that apply)
Thank you for your feedback.
Want to tell us more?
Email your feedback to our documentation team.
Great!
Thanks for taking the time to give us some feedback.