Facts - Columns and Data Types

A numerical piece of data used to measure a business process

A fact is a numerical piece of data, which in a business environment is used to measure a business process. Values can be arbitrary.

Service Sessions

Service Session facts are created when conversation stays in one context of the system. The Service Session is terminated when ... :

  • ... the Customer hangs up a call.
  • ... the Service terminates the task or transfers call to another service.

Fact table grains below are considered finalized when the session ended. 

Attribute Data Type Category Description
Id  guid Primary Key Primary key 
ServiceId  guid Reference Reference to Service 
UnifiedConversationId guid Reference Reference to Unified session. Unified session can contain several Service sessions.
ServiceOrganizationUnitId guid Reference Organization Unit ID of the service (TeamId)
OrganizationUnitId guid Reference Parent Organization Unit ID of the service (ParentId)
OutcomeId  integer References Reference to Service Session Outcomes 
StartedAt  datetime   Session start timestamp 
TotalQueueTime  integer measure Queue time summary 
TotalIvrTime  integer measure IVR time summary 
TotalConnectedTime  integer measure Connected with user time summary 
HoldTime integer measure Hold time summary per session. Time when a customer was "on hold" during consult sessions. Park attempts are not included.
HoldCount integer measure Hold count summary per session. How often a customer was put "on hold" during consult sessions. Park attempts are not included.
IsUnderSla  boolean Degenerate dimension If Time in queue is less SLA time of this service 
DistributionPolicyLevel integer Degenerate dimension Profile level. In which distribution profile the user was selected (1, 2, 3... )
DistributionAlgorithm integer References Reference to Distribution Algorithm
AcwTime integer measure Duration of after conversation work
CallerId guid Reference Reference to Callers
TenantId guid Degenerate dimension Unique Tenant identifier
FirstPrimaryCodeId  guid References Reference to Primary Code
FirstSecondaryCodeId guid References Reference to Secondary Code
DistributionPriority integer Reference Reference to DistributionPriority
TaskTypeId integer Reference Reference to task type
LastTransferDestination string Reference UPN or PSTN of transfer destination, which leads to the termination of the Service session (Successful or Failed)
LastTransferTypeId guid Reference Transfer type id of the transfer, which leads to the termination of the Service session (Successful or Failed)
IsShortConversation boolean Degenerate Dimension Flag (True/False) indicating whether the call is within the Short Abandons Threshold as set for the service in the Nimbus General Service Settings  > Reporting section > Short Abandons Threshold. 
🔍 Fields below are not provided by OData feed, but calculated in Power BI template
InitialCallerId text Calculated Column Renamed column CallerId
O365Id guid Degenerate dimension Lookup value from Callers
Upn text Degenerate dimension Lookup value from Callers
TelNumber text Degenerate dimension Lookup value from Callers

TotalTime 

integer Calculated Column

Calculated: TotalQueueTime + TotalIvrTime + TotalConnectedTime

LocalTime 

integer Calculated Column

StartedAt shifted to local timezone of the system

Date 

date Calculated Column

Date part of LocalTime

Time 

time Calculated Column

Time part of LocalTime

Hours 

integer Calculated Column

Hours extracted from Time

Minutes 

integer Calculated Column

Minutes extracted from Time

Fixed15Min 

text Calculated Column

Time rounded to the 15 minutes timeslot e.g. 11:15:00, 11:30:00

OriginType text Calculated Column PSTN/INTL
Tags text Calculated Column Comma separated string of tags
 
 

Service Session Tags

Used to connect the service sessions table to their tags.

Attribute

Data Type

Description

ServiceSessionId guid Service session primary key
TagId guid Tag primary key
 
 

Service Session Tag Strings

Service Session Tag Strings enables users to view all the tags associated to a single Service Session ID. When multiple tags apply, the tags are shown in a single string, separated by comma (e.g. Tag1, Tag2, Tag3, ...). 

💡 Note that this entity starts getting populated with data from the moment of the release and it is not backfilled.

 
 

Transfer Sessions

Attribute 

Data Type
Category

Description 

Id  guid Primary Key Primary key 
ServiceSessionIDFrom guid Reference Id to the related Service Session from which the transfer was started
UserSessionIdFrom guid Reference null
ServiceFromOrganizationUnitId guid Reference Contains parent → OrganizationUnitIdof the service, from which transfer was started
ServiceToOrganizationUnitId guid Reference Contains parent → OrganizationUnitIdof the service, to which transfer was started
UserFromOrganizationUnitId guid Reference Contains source organization unit of → user which started the transfer.
UserToOrganizationUnitId  guid Reference Contains destination organization unit of → user which started the transfer.
StartedAt datetime measure transfer started at timestamp
EndedAt datetime measure transfer ended at timestamp
ServiceIdFrom guid Reference if the transfer was initiated by a Nimbus Service, the ID will be added here    
💡 will always be not null for transfer by Workflows.
UserIdFrom guid Reference null
ServiceIdTo guid Reference Destination Service Id
UserIdTo guid Reference null
Destination text Reference transfer destination (PSTN number or UPN)
DestinationName text Reference

transfer destination name in clear text.

💡 The destination name is

  • the service display name in case the destination is a Nimbus service.
  • the display name of the user in case destination is a Nimbus user.
  • empty, if no destination name is available.
TransferSessionOutcomeId integer Reference Filled accoring to TransferSessionOutcome    
🔍 see → Static Dimensions > Transfer Session tab
TransferTypeId integer Reference Filled accoring to TransferSessionType    
(info) see → Static Dimensions > Transfer Session tab
UserTransferActionTypeId guid Reference null
TenantId guid Reference Tenant Id
 
 

User Sessions

User Sessions are used to assess user performance. User Sessions are a sub process of Service Sessions and are always related to only one  User.

  • A session is created for accepted, missed and declined user conversations. 
  • With several users in a conversation, User Sessions are created for each User. 

Multiple user sessions may be created (e.g. when a user transfers a call to another user in the same service).

Attribute  Data Type Category Description 
Id  guid Primary Key Primary key 
UserId  guid Reference Reference to user slowly changing dimension 
UnifiedConversationId guid Reference Reference to Unified session. Unified session can contain several Service sessions.
TenantId guid Reference Unique Tenant identifier.
OrganizationUnitId guid Reference Organization Unit ID of the user.
OutcomeId  integer Reference Reference to User Session Outcomes static dimension 
UserSelectedAt  datetime measure User selected at timestamp 
RingTime  integer measure Ring time summary. The time invitation card was shown to this Nimbus user for the Handled attempt (i.e. Declined and RONA attempts should be ignored) for the selected period. Average time customer is connected this Nimbus user (applicable to Accepted calls excluding PickupThroughTransfer) for the selected period 
ConnectedTime  integer measure Connected time summary 
ConferencedTime integer measure Total time user spend in conference with customer and consultant
ConsultationTime integer measure Total time user spend in conference with consultant only (customer on hold)
ConsultationCount integer measure Count of consultations during the user session
ServiceSessionId  guid Reference Reference to Service Sessions fact 
AcwTime integer measure Duration of after conversation work
DistirbutionAlgorithm integer Reference Reference to Distribution Algorithm
DistributionPolicyLevel integer Reference Profile level. In which distribution profile the user was selected (1, 2, 3... )
DistributionTypeId integer Reference In which way the task was sent to the user. Broadcast, DirectConference etc.
DistributionPriority integer Reference Reference to UserSessionDistributionPriority
TaskTypeId integer Reference Reference to task type
LastTransferDestination string Reference UPN or PSTN of transfer destination, which leads to the termination of the User session (Successful or Failed)
LastTransferTypeId guid Reference Transfer type id of the transfer, which leads to the termination of the User session (Successful or Failed)
Fields below are not provided by OData feed, but calculated in Power BI template

LocalTime 

datetime Calculated column

UserSelectedAt shifted to local time zone, defined by parameter

Date 

date Calculated column

Date part of LocalTime

Time 

time Calculated column

Time part of LocalTime

Hours 

integer Calculated column

Hours extracted from Time

Minutes 

integer Calculated column

Minutes extracted from Time

Fixed15Min 

text Calculated column

Time rounded to the 15 minutes timeslot e.g. 11:15:00, 11:30:00 

 
 

User States

User states is a list of periods in time showing durations and user responsibility profile during this period.

Attribute  Data Type Category Description 
Id  guid Primary Key Primary key 
TenantId guid Reference Reference to Tenant table
UserO365Id  guid Reference Reference to user slowly changing dimension 
ResponsibilityProfileId  guid Reference Reference to Responsibility Profile dimension 
NotAvailableReasonId guid Reference Reference to NotAvailableReasons dimension
NotAvailableReasonComment text Reference Comment which is added by User for NAR
Start  datetime measure Period start time
End datetime measure Period end time
UserStateTypeId guid measure Reference to User State Type dimension
Fields below are not provided by OData feed, but calculated in Power BI template

PeriodLength 

text Calculated column

Represent Period Length as dd.HH:MM:SS.XXXXXXX formated string

🔍 Also refer to User States.

 
 

UnifiedSessions

UnifiedSessions - available through the OData interface, provides insights into the end-2-end concept of a call as opposed to single sessions. A Unified Session can include one or more ServiceSessions and/or UserSessions for which the common denominator is the UnifiedConversationId. 

  • Unified Sessions visibility depends on the permissions assigned to the role used to access the data. If the role is allowed to see the Incoming Service AND First Queued Service AND Last Queued Service, then the associated unified session will also be visible.
  • Service Sessions are ordered by StartedAt .
  • The customer path is combined from Service names based on ordered sessions.
  • Where multiple sessions belong to a Unified Session the dimensional attributes are mostly taken from the first service session ( Caller, Date, Time). The outcome is taken from the last session.
  • If there was only one session per call, then UnifiedConversationId is equal to Service Session Id.
  • HighestDistributionPriority represents distribution priority among Service sessions with same UnifiedConversationId. The lower the DistributionPriorityId the higher the priority. 

☝Performance Impact: Please note that at the moment Power BI using its own logic in the Nimbus Power BI Template to generate its own UnifiedSessions table and this can be very resource and time consuming even on high-end PCs. A toggle in the template has been provided to disable the loading of this table and help reduce the size of the model if size limits are reached.

 
Column Data Type Category Description
UnifiedConversationId Guid Primary Key Primary key 
TenantId Guid Reference Unique Tenant identifier
InitialCallerId Guid Reference CallerId of the FIRST service session
StartedAt DateTime   StartedAt value of the FIRST service session
TaskTypeId integer Reference TaskTypeId value of the FIRST service session
InitialModality integer Reference Modality value of the FIRST service session
IncomingServiceId Guid Reference Service Id of the FIRST service session
IncomingOrganizationUnitId Guid Reference OrganizationUnitId of the FIRST service session
IncomingServiceOrganizationUnitId Guid Reference ServiceOrganizationUnitId of the FIRST service session
OutcomeId integer Reference OutcomeId defined by the outcome of the last session
IsLastHandled bool   IsHandled flag associated with the last service session
FirstPrimaryCodeId Guid Reference PrimaryCodeId of the first connected Service
FirstSecondaryCodeId Guid Reference SecondaryCodeId of the first connected Service
FirstAcceptedUserId Guid Reference ID of the user who accepted on FirstQueuedService (if applicable otherwise null)
FirstQueuedDistributionPolicyLevel int Reference DistributionPolicyLevelId of the Service Session where the Conversation was FIRST queued
FirstQueuedDistributionAlgorithm int Reference Distribution Algorithm id of the Service Session where the Conversation was FIRST Queued
FirstQueuedServiceId Guid Reference ServiceId of the Service Session where the call was FIRST queued
FirstQueuedOrganizationUnitId Guid Reference OrganizationUnitId of the Service Session where the call was FIRST queued
FirstQueuedServiceOrganizationUnitId Guid Reference OrganizationUnitId of the Service Session where the call was FIRST queued
LastQueuedDistributionPolicyLevel int Reference DistributionPolicyLevelId of the Service Session where the Conversation was LAST queued
LastQueuedDistributionAlgorithm int Reference Distribution Algorithm id of the Service Session where the Conversation was LAST Queued
LastQueuedServiceId Guid Reference ServiceId of the Service Session where the call was LAST queued
LastQueuedOrganizationUnitId Guid Reference OrganizationUnitId of the Service Session where the call was LAST queued
LastQueuedServiceOrganizationUnitId Guid Reference ServiceOrganizationUnitId of the Service Session where the call was LAST queued
LastPrimaryCodeId Guid Reference PrimaryCodeId of the last connected Service
LastSecondaryCodeId Guid Reference SecondaryCodeId of the last connected Service
LastAcceptedUserId Guid Reference ID of the user who accepted on the LastQueuedService (if applicable otherwise null)
IvrTime integer measure Sum of all IvrTime of related Service Sessions in seconds
QueueTime integer measure Sum of all QueueTime of related ServiceSessions in seconds
ConnectedTime integer measure Sum of all ConnectedTime of related ServiceSessions in seconds
HoldTime integer measure Sum of all HoldTime of related ServiceSessions in seconds
AcwTime integer measure Sum of all AcwTime from the related User Sessions included in the unified session.
AcwExtendedTime integer measure Sum of all AcwExtendedTime of related User Sessions included in the unified session.
DialoutTime integer measure Sum of DialoutTime related to the User Sessions included in the unified session. The dial out time is the time spent dialing out to the destination. This attribute is populated only in case of outbound calls.
HoldCount int measure Sum of all HoldCounts occurred in the unified session
RONACount int measure Count of RONA (UserSession = Declined or Missed)
OutboundAttempts int measure Count of all OutboundAttempts for all service sessions included in the unified session.
ContainsFailedSession bool Degenerate dimension True/False flag indicating whether the unified session contains any instance of Handling of "Failed": ServiceSession/UserSession/TransferSession.
VoiceMailRecordedOnServiceId Guid Reference Service ID of Service where Outcome=11 (Workflow Converstation Recorded)
WorkflowTransferCount int measure Count of successful Service Session transfers with Outcome: - Workflow Internal Transfer Successful 12, - Workflow External Transfer Successful 14
ServiceTransferCount int measure Count of transfers to a service occurred in relation to the unified session
UserTransferCount int measure Count of successful transfers where initiated by the users. Count of Service Session with Outcome (User Internal Transfer Successful 6, User External Transfer Successful 7)
IsUnderSLA bool Degenerate dimension

Set to true if “IsUnderSLA” for the first OR last queued Service Sessions is set to true 

(IsUnderSLA is true for a Service Session if the time spent in queue for that Service Session is below the SLA time set for the service).

TopDistributionPriority int Reference Lowest DistributionPriority id for the first OR last queued Service Session (whichever is lowest).
IsAnyHandled bool Degenerate dimension

Set to true, if any related service session is handled.

Accepted by user, service transfer, marked as handled in WF (email), ...) 

LastTransferDestination string Degenerate dimension Destination UPN of the last successful transfer session if applicable (otherwise it is null).
LastTransferType integer Reference Transfer type ID of the last transfer session if successful (otherwise it is null).
IsLastTransferExternal bool Degenerate dimension Set to true if last service session was successfully transferred to external destination (Workflow External Transfer Successful 14, User External Transfer Successful 7)
 
 

Table of Contents