...
Broadsoft CC Dimension Tables | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
BroadsoftCallCenterDimension | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Column Name | Type | Description | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| bccd_id | int | Primary key | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| bccd_key | varchar(36) | Future use | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| bccd_userId | varchar(161) | Call center fully qualified user ID | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| bccd_name | varchar(30) | Call center name | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| bccd_type | varchar(10) | Call center type, which is one of: ▪ Premium ▪ Standard | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| bccd_eipId | varchar(16) | The account identifier | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| bccd_broadsoftEnterpriseId | varchar(64) | The VoIP platform identifier | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| bccd_parentEipId | varchar(16) | The parent account identifier | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
BroadsoftCCCallStatusDimension | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Column Name | Type | Description | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| bcsd_id | int | Primary key | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| bcsd_key | varchar(32) | Call status unique identifier
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| bcsd_description | varchar(64) | Call status description | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| bcsd_type | varchar(8) | Call status type | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| bcsd_isHandled | bit (1=true/0=false) | Flag to indicated if a call is handled | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
BroadsoftCCDNISDimension | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Column Name | Type | Description | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| bcdd_id | int | Primary key | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| bcdd_key | varchar(36) | DNIS unique identifier | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| bcdd_name | varchar(40) | DNIS name | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| bcdd_number | varchar(162) | DNIS directory number, in E.164 format | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| bcdd_extension | varchar(20) | DNIS phone extension | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| bcdd_eipId | varchar(16) | The account identifier | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| bcdd_broadsoftEnterpriseId | varchar(64) | The VoIP platform identifier | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| bcdd_broadsoftCallCenterId | varchar(161) | The call center user ID | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| bcdd_parentEipId | varchar(16) | The parent account identifier | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
...
Broadsoft CC Fact Tables | |||||
BroadsoftCCQueueCallFact | |||||
| Column Name | Type | Description | References | Indexed | |
|---|---|---|---|---|---|
| qcf_id | int | Primary key | |||
| qcf_agentSkillFirst | int | First agent skill associated with the call (if any) | |||
| qcf_agentSkillLast | int | Last agent skill associated with the call (if any) | |||
| qcf_preservedOfferedCount | int | Preserved offered count from previous queue (if any) | |||
| qcf_preservedBouncedCount | int | Preserved bounced count from previous queue (if any) | |||
| qcf_preservedWaitTime | int | Preserved wait time from previous queue (if any) | |||
| qcf_agentUserFk | int | Foreign key to the call agent user | BroadsoftUserDimension | Yes | |
| qcf_dnisFk | int | Foreign key to the call DNIS | BroadsoftCCDNISDimension. | Yes | |
| qcf_callCenterFk | int | Foreign key to the Call Center. | BroadsoftCallCenterDimension | Yes | |
| qcf_callReleasedDuration | int | Future use | |||
| qcf_callReleasedDateUTC | int | Foreign key to the call released date in UTC | DateDimension | Yes | |
| qcf_callReleasedTimeUTC | int | Foreign key to the call released time of day in UTC | TimeOfDayDimension | Yes | |
| qcf_callReleasedDateLocal | int | Foreign key to the call released date in local time | DateDimension | Yes | |
| qcf_callReleasedTimeLocal | int | Foreign key to the call released time of day in local time | TimeOfDayDimension | Yes | |
| qcf_callOfferedCount | int | The number of times the call was offered to an agent from this queue | |||
| qcf_callBouncedCount | int | This is the number of calls that bounced and remained in the queue, which were presented to agents | |||
| qcf_callQueueDuration | int | The total call wait time in seconds in this queue | |||
| qcf_callCreationDateUTC | int | Foreign key to the call creation date in UTC | DateDimension | Yes | |
| qcf_callCreationTimeUTC | int | Foreign key to the call creation time of day in UTC | TimeOfDayDimension | Yes | |
| qcf_callCreationDateLocal | int | Foreign key to the call creation date in local time | DateDimension | Yes | |
| qcf_callCreationTimeLocal | int | Foreign key to the call creation time of day in local time | TimeOfDayDimension | Yes | |
| qcf_status | varchar(32) | Call status. Possible values:
| |||
| qcf_key | varchar(162) | Future use | |||
| qcf_accountId | int | Foreign key to the account associated with this queue call | AccountDimension | Yes | |
| qcf_callId | varchar(162) | The unique call identifier from the VoIP platform | Yes | ||
| qcf_networkCallId | varchar(162) | Network call identifier from the VoIP platform | |||
| qcf_remoteNumber | varchar(162) | The calling / called number (depending on whether the call was inbound or outbound respectively) | |||
| qcf_numberCalled | varchar(162) | The dialed number | |||
| qcf_creationTimestamp | datetime2(7) | The call creation timestamp in UTC | Yes | ||
| qcf_lastUpdateTimestamp | datetime2(7) | Future use (currently equals to creation timestamp) | |||
| qcf_callAddedTimestamp | datetime2(7) | The timestamp in which the call was added to the queue in UTC | |||
| qcf_callOfferedFirstTimestamp | datetime2(7) | The timestamp in which the call was first offered in UTC | |||
| qcf_callOfferedLastTimestamp | datetime2(7) | The timestamp in which the call was last offered in UTC | |||
| qcf_callAnsweredTimestamp | datetime2(7) | The answered timestamp in UTC | |||
| qcf_callBouncedFirstTimestamp | datetime2(7) | The timestamp in which the call was first bounced in UTC | |||
| qcf_callBouncedLastTimestamp | datetime2(7) | The timestamp in which the call was last bounced in UTC | |||
| qcf_callBounceTransferredTimestamp | datetime2(7) | The timestamp (in UTC) in which the call was transferred automatically because the caller was placed on hold for too long by an agent. | |||
| qcf_callAbandonedTimestamp | datetime2(7) | The timestamp (in UTC) in which the call was removed from the queue as a result of the caller hanging up | |||
| qcf_callEscapedTimestamp | datetime2(7) | The timestamp (in UTC) in which the call was removed from the queue because the caller chose the voice mail option. | |||
| qcf_callOverflowedByTimeTimestamp | datetime2(7) | The timestamp (in UTC) in which the call was transferred to an alternative destination because the callers waited too long in queue | |||
| qcf_callReleasedTimestamp | datetime2(7) | Call was released timestamp in UTC | |||
| qcf_callStrandedTimestamp | datetime2(7) | The time (in UTC) that the call was transferred to an alternative destination because no agents were logged into the queue | |||
| qcf_callStrandedUnavailableTimestamp | datetime2(7) | If the call stranded policy is applied, this timestamp will be populated (time in UTC). | |||
| qcf_callTransferredTimestamp | datetime2(7) | If the call has been transferred out of the queue by a supervisor, this timestamp will be populated (time in UTC) | |||
| qcf_callOverflowedBySizeTimestamp | datetime2(7) | If the call overflowed from the queue due to too many calls in the queue, this timestamp will be populated (time in UTC) | |||
| qcf_eipId | varchar(16) | The account identifier for this queue call | Yes | ||
| qcf_deflected | bit (1=true/0=false) | Indicates whether the call is deflected or not. When a pre-queue policy is applied to a call instead of queueing it, the call is said to be “deflected”. | |||
| qcf_callNightServiceAppliedTimestamp | datetime2(7) | If the night service policy is applied, this timestamp will be populated (time in UTC) | |||
| qcf_deflectedCallReason | varchar(32) | The reason that the call was deflected. Possible values are:
| |||
| qfc_redirectAddress | varchar(64) | The redirected address if the call redirected to another party | |||
| qcf_agentCallId | varchar(162) | If an agent answer the call, this field will be populated with the agent call identifier. | Yes | ||
| qcf_parentEipId | varchar(16) | The parent account identifier | |||
| qcf_handledAgentSkill | int | If an agent answered the call and an agent skill was applied, this field will be populated with the agent skill level | |||
| qcf_handledCallHeldDuration | int | If an agent answered the call and the call was placed on hold, this field will be populated with the total hold duration (in seconds) | |||
| qcf_handledCallRingDuration | int | If an agent answered the call, this field will be populated with the total ring duration (in seconds) | |||
| qcf_handledCallTalkDuration | int | If an agent answered the call, this field will be populated with the total talk duration (in seconds) | |||
| qcf_handledWrapUpDuration | int | If an agent answered the call and went in to wrap-up state after the call ended, this field will be populated with the total wrap-up duration (in seconds) | |||
| qcf_handledDispositionCode | varchar(256) | If an agent answered the call and enter disposition code, this field will be populated | |||
| qcf_handledCallTotalDuration | int | If an agent answered the call, this field will be populated with the total call duration (in seconds) | |||
| qcf_userId | varchar(161) | The broadsoft user fully qualified identifier | |||
| qcf_lastUpdatedTimestamp | datetime2(7) | The record updated timestamp | |||
| qcf_updated | bit | Indicates whether the record was updated | |||
BroadsoftCCAgentCallFact | |||||
| Column Name | Type | Description | References | Indexed | |
| acf_id | int | Primary Key | |||
| acf_agentSkill | int | The agent skill level for an answered ACD inbound call | |||
| acf_agentUserFk | int | Foreign key to the agent user | BroadsoftUserDimension | Yes | |
| acf_dnisFk | int | Foreign key to the DNIS | BroadsoftCCDNISDimension | Yes | |
| acf_callCenterFk | int | Foreign key to the Call Center | BroadsoftCallCenterDimension | Yes | |
| acf_callHeldCount | int | The total amount of times the call was placed on hold by the agent | |||
| acf_callHeldDuration | int | The total amount of time the call spent in a on hold state (in seconds) | |||
| acf_callTotalDuration | int | The total amount of time since the call was created until the call was release (in seconds) | |||
| acf_callRingDuration | int | The total amount of time this call was ringing for the handling agent (in seconds) | |||
| acf_callTalkDuration | int | The duration of the call in seconds from the answer event to the release event | |||
| acf_callStartedDateUTC | int | Foreign key to the call started date in UTC | DateDimension | Yes | |
| acf_callStartedTimeUTC | int | Foreign key to the call started time of day in UTC | TimeOfDayDimension | Yes | |
| acf_callStartedDateLocal | int | Foreign key to the call started date in local time | DateDimension | Yes | |
| acf_callStartedTimeLocal | int | Foreign key to the call started time of day in local time | TimeOfDayDimension | Yes | |
| acf_callReleasedDateUTC | int | Foreign key to the call released date in UTC | DateDimension | Yes | |
| acf_callReleasedTimeUTC | int | Foreign key to the call released time of day in UTC | TimeOfDayDimension | Yes | |
| acf_callReleasedDateLocal | int | Foreign key to the call released date in local time | DateDimension | Yes | |
| acf_callReleasedTimeLocal | int | Foreign key to the call released time of day in local time | TimeOfDayDimension | Yes | |
| acf_status | varchar(16) | The status of the call. The only possible status is 'Completed' | |||
| acf_key | varchar(162) | Future use | |||
| acf_accountId | int | Foreign key to the account associated with this agent call | AccountDimension | Yes | |
| acf_callId | varchar(162) | The unique call identifier from the VoIP platform | Yes | ||
| acf_networkCallId | varchar(162) | Network call identifier from the VoIP platform | |||
| acf_remoteNumber | varchar(162) | The calling / called number (depending on whether the call was inbound or outbound respectively) | |||
| acf_numberCalled | varchar(162) | The dialed number | |||
| acf_creationTimestamp | datetime2(7) | The call creation timestamp in UTC | Yes | ||
| acf_lastUpdateTimestamp | datetime2(7) | Future use (currently equals to creation timestamp) | |||
| acf_callAnsweredTimestamp | datetime2(7) | The timestamp representing the call answer event (in UTC) | |||
| acf_callStartedTimestamp | datetime2(7) | The timestamp representing the call started event (in UTC) | Yes | ||
| acf_callHeldTimestamp | datetime2(7) | Represents the timestamp the call was first placed on hold by the agent (in UTC) | |||
| acf_callResumedTimestamp | datetime2(7) | Represents the timestamp the call was first resumed after call was placed on hold by the agent (in UTC) | |||
| acf_callReleasedTimestamp | datetime2(7) | The timestamp representing the call release event by the agent (in UTC) | |||
| acf_eipId | varchar(16) | The account identifier | Yes | ||
| acf_nonCallCenterCall | bit (1=true/0=false) | If the agent call was not associated with the call center, this field will be set to 1. | |||
| acf_direction | varchar(12) | The call direction. Possible values are:
| |||
| acf_callType | varchar(20) | This is the call type. Possible values:
| |||
| acf_redirectLocation | varchar(30) | Redirect location if the call was redirected to another party. Possible values:
| |||
| acf_redirectNumber | varchar(164) | The redirect number if the call was redirected to another party | |||
| acf_wrapUpDuration | int | Total time the agent spent in the Wrap-Up state in seconds | |||
| acf_wrapUpExitTimestamp | datetime2(7) | The timestamp in which the call was Wrapped-Up by the agent in UTC | |||
| acf_parentEipId | varchar(16) | The parent account identifier | |||
| acf_bounced | bit (1=true/0=false) | Indicates whether the call was bounced (1) or not (0) | |||
| acf_userId | varchar(161) | The broadsoft user fully qualified identifier | |||
| acf_lastUpdatedTimestamp | datetime2(7) | The record updated timestamp | |||
| acf_updated | bit | Indicates whether the record was updated | |||
BroadsoftCCAgentCallDispositionFact | |||||
| Column Name | Type | Description | References | Indexed | |
| acdf_id | int | Primary key | |||
| acdf_agentUserFk | int | Foreign key to the agent user | BroadsoftUserDimension | Yes | |
| acdf_dnisFk | int | Foreign key to the DNIS | BroadsoftCCDNISDimension | ||
| acdf_callCenterFk | int | Foreign key to the Call Center | BroadsoftCallCenterDimension | ||
| acdf_dispositionEntryDateUTC | int | Foreign key to the the disposition entry date in UTC | DateDimension | Yes | |
| acdf_dispositionEntryTimeUTC | int | Foreign key to the disposition entry time of day in UTC | TimeOfDayDimension | Yes | |
| acdf_dispositionEntryDateLocal | int | Foreign key to the disposition entry date in local time | DateDimension | Yes | |
| acdf_dispositionEntryTimeLocal | int | Foreign key to the disposition entry time of day in local time | TimeOfDayDimension | Yes | |
| acdf_status | varchar(16) | The status of the call. The only possible status is 'Completed' | |||
| acdf_key | varchar(162) | Future use | |||
| acdf_accountId | int | Foreign key to the account associated with this call | AccountDimension | Yes | |
| acdf_callId | varchar(162) | The unique call identifier from the VoIP platform | Yes | ||
| acdf_code | varchar(256) | The disposition code for this call | |||
| acdf_creationTimestamp | datetime2(7) | The call creation timestamp in UTC | |||
| acdf_lastUpdateTimestamp | datetime2(7) | Future use (currently equals to creation timestamp) | |||
| acdf_dispositionEntryTimestamp | datetime2(7) | The disposition entry timestamp in UTC | |||
| acdf_eipId | varchar(16) | The account identifier for this call | Yes | ||
| acdf_parentEipId | varchar(16) | The parent account identifier | |||
| acdf_userId | varchar(161) | The broadsoft user fully qualified identifier associated with this agent | |||
BroadsoftCCAgentStaffingFact | |||||
| acdf_lastUpdatedTimestamp | datetime2(7) | The record updated timestamp | |||
| acdf_updated | bit | Indicates whether the record was updated | |||
BroadsoftCCAgentStaffingFact | |||||
| Column Name | Column Name | Type | Description | References | Indexed |
| astf_id | int | Primary key | |||
| astf_agentUserFk | int | Foreign key to the agent user | BroadsoftUserDimension | Yes | |
| astf_callCenterFk | int | Foreign key to the Call Center | BroadsoftCCDNISDimension | ||
| astf_agentStaffedDuration | int | The duration between the agent transition from one staffed state to unstaffed state and vise versa | |||
| astf_agentStaffedStartedDateUTC | int | Foreign key to the agent staffed/unstaffed start date in UTC | DateDimension | Yes | |
| astf_agentStaffedStartedTimeUTC | int | Foreign key to the agent staffed/unstaffed start time of day in UTC | TimeOfDayDimension | Yes | |
| astf_agentStaffedStartedDateLocal | int | Foreign key to the agent staffed/unstaffed start date in local time | DateDimension | Yes | |
| astf_agentStaffedStartedTimeLocal | int | Foreign key to the agent staffed/unstaffed start time of day in local time | TimeOfDayDimension | Yes | |
| astf_agentStaffedEndedDateUTC | int | Foreign key to the agent staffed/unstaffed end date in UTC | DateDimension | Yes | |
| astf_agentStaffedEndedTimeUTC | int | Foreign key to the agent staffed/unstaffed end time of day in UTC | TimeOfDayDimension | Yes | |
| astf_agentStaffedEndedDateLocal | int | Foreign key to the agent staffed/unstaffed end date in local time | DateDimension | Yes | |
| astf_agentStaffedEndedTimeLocal | int | Foreign key to the agent staffed/unstaffed end time of day in local time | TimeOfDayDimension | Yes | |
| astf_status | varchar(16) | The status of the staffed/unstaffed activity. The only possible status is 'Completed' | |||
| astf_state | varchar(32) | Agent staffed states. Possible values:
| |||
| astf_skill | int | The agent's skill level | |||
| astf_key | varchar(162) | Future use | |||
| astf_accountId | int | Foreign key to the account | AccountDimension | Yes | |
| astf_lastUpdateTimestamp | datetime2(7) | Future use | |||
| astf_agentStaffedStartedTimestamp | datetime2(7) | The timestamp of the staffed/unstaffed start activity (in UTC) | Yes | ||
| astf_agentStaffedEndedTimestamp | datetime2(7) | The timestamp of the staffed/unstaffed end activity (in UTC) | |||
| astf_eipId | varchar(16) | The account identifier | Yes | ||
| astf_parentEipId | varchar(16) | The parent account identifier | |||
| astf_userId | varchar(161) | The broadsoft user fully qualified identifier associated with this agent | |||
| astf_lastUpdatedTimestamp | datetime2(7) | The record updated timestamp | |||
| astf_updated | bit | Indicates whether the record was updated | |||
BroadsoftCCAgentStateFact | |||||
| Column Name | Type | Description | References | Indexed | |
| asf_id | int | Primary key | |||
| asf_agentUserFk | int | Foreign key to the agent user | BroadsoftUserDimension | Yes | |
| asf_agentStateDuration | int | The duration between the agent transition from one state to another | |||
| asf_agentStateStartedDateUTC | int | Foreign key to the agent state start date in UTC | DateDimension | Yes | |
| asf_agentStateStartedTimeUTC | int | Foreign key to the agent state start time of day in UTC | TimeOfDayDimension | Yes | |
| asf_agentStateStartedDateLocal | int | Foreign key to the agent state start date in local time | DateDimension | Yes | |
| asf_agentStateStartedTimeLocal | int | Foreign key to the agent state start time of day in local time | TimeOfDayDimension | Yes | |
| asf_agentStateEndedDateUTC | int | Foreign key to the agent state end date in UTC | DateDimension | Yes | |
| asf_agentStateEndedTimeUTC | int | Foreign key to the agent state end time of day in UTC | TimeOfDayDimension | Yes | |
| asf_agentStateEndedDateLocal | int | Foreign key to the agent state end date in local time | DateDimension | Yes | |
| asf_agentStateEndedTimeLocal | int | Foreign key to the agent state end time of day in local time | TimeOfDayDimension | Yes | |
| asf_status | varchar(16) | The status of the agent state activity. The only possible status is 'Completed' | |||
| asf_state | varchar(32) | The agent's state. Possible values:
| |||
| asf_code | varchar(256) | The agent state code if applicable | |||
| asf_key | varchar(162) | Future use | |||
| asf_accountId | int | Foreign key to the account | AccountDimension | Yes | |
| asf_lastUpdateTimestamp | datetime2(7) | Future use | |||
| asf_agentStateStartedTimestamp | datetime2(7) | The timestamp in which the agent enters into one of the states (in UTC) | Yes | ||
| asf_agentStateEndedTimestamp | datetime2(7) | The timestamp in which the agent switch to another state (exit from the previous state) in UTC | |||
| asf_eipId | varchar(16) | The account identifier | Yes | ||
| asf_parentEipId | varchar(16) | The parent account identifier | |||
| asf_userId | varchar(161) | The broadsoft user fully qualified identifier associated with this agent | |||
| asf_lastUpdatedTimestamp | datetime2(7) | The record updated timestamp | |||
| asf_updated | bit | Indicates whether the record was updated | |||