DW structure changes

• added new F_AgentBpTime table with fields:
  
  • Id
  • TimeSliceId
  • AgentId
  • BusinessProcessId
  • Ready
  • Offered
  • Break
  • NoAnswer
  • Dialing
  • WaitingFor
  • Busy
  • BackOffice
  • HandlingOutgoingInt
  • LoginTime
  • HandlingTime
  • CreatedLogID
  • UpdatedLogID
  • HandlingOutgoingIntIdle
  • HandlingOutgoingIntBusy
  • BackofficeIdle
  • BackofficeBusy
  • InConference
  • Consulting
  • Handling
  • IsAssigned
  • OutgoingBreakExists
  • IsInternal 
• changes F_InteractionDelegated table:
  
  • InteractionGUIID -> InteractionGUIDID
• added new column to F_Interactions table:
  • CampaingInteractionLastStateId

• 1.01 Detailed Agent Activity:

  • Parameter: @ChartOptions
    
    • Busy States - The agent states pie chart while being busy (Ready, Handle Outgoing Busy, Backoffice Busy, Waiting For XXX)
    • Busy Time - The agent time pie chart spent while being busy in any state (Talk, Ringing, Dialing, Wrap-up, Hold)
    • Idle States - The agent states pie chart while being idle (Ready, Offered, Handle Outgoing Idle, Backoffice Idle, No Answer, Break)
  • Parameter: @DisplayOptions(Show Chart Only)
    • Previous:
      • Chart and Table - Default
      • Chart Only
    • Current:
      • Chart and Table - Default
      • Chart Only
      • Table Only
  • New fields:
    • Added "Interaction GUID"
    • Added "Remote Party"
  • Dataset Report_DetailedAgentActivity, new fields:
    • InteractionGUIDID
    • RemoteParty
    • InteractionId
    • IdleState
    • BusyState
    • BusyTime
  • CSV Export, new fields:
    • InteractionGUIDID1
    • RemoteParty1

• 2.02 Agent Interaction Summary:

  • CSV Export, changed fields:
    
    • TotalHandlingOutgoingInteractionsIdle -> TotalHandlingOutgoingInteractionsIdleTime
    • TotalBackOfficeTimeIdle -> TotalBackOfficeIdleTime

• 2.04 Agent Interaction Summary:

  • Parameter: @DisplayTimeInSeconds
    
    • default = false - if true all time fields will display in seconds

• 4.05 Delegated Interactions:

  • Dataset Report_DelegatedInteractions, new parameters (optional):
    • @DWDBName

• 5.01 Business Process Agent Performance:

  • Dataset Report_BusinessProcessAgentPerformance, new parameters (optional):
    
    • @DWDBName

• 6.01 Completed Campaign Interactions:

  • Dataset Report_BusinessProcessAgentPerformance, new parameters (optional):
    
    • @DWDBName

• LOGIC CHANGES:

  • ETL db changes:

    • new procedures:
      
      • fill new collected statistic table:
        • dbo.Upload_F_AgentBpTime

  • Users db new procedures:

    • DW.spRpt_BusinessProcessAgentPerf
    • DW.spRpt_CompletedCampaignInteraction

  • Users db delete procedures:

    • dbo.spRpt_BusinessProcessAgentPerf
    • dbo.spRpt_CompletedCampaignInteraction

  • 1.01 Detailed Agent Activity:

    • procedures changed:
      
      • DW.spRpt_DetailedAgentActivity

  • 5.01 Business Process Agent Performance:

    • procedures changed:
      
      • DW.spRpt_BusinessProcessAgentPerf

  • 6.01 Completed Campaign Interactions:

    • procedures changed:
      
      • DW.spRpt_CompletedCampaignInteraction

  • 6.04 Completed Callback Interactions:

    • Agent Name:
      • Previous:
        • F_Attempts.AgentID → F_Attempts.AgentName
      • Current:
        • last(F_InteractionSegments.AgentID)
          where InteractionState in ('Handling', 'Consulting', 'In Conference')
          if last agent not exists then
          F_Attempts.AgentID → F_Attempts.AgentName
    • procedures changed:
      • DW.spRpt_CompletedCallbackInteraction