DW structure changes
       

• added new column to F_Interactions table:
  • Survey

New Report 9.01 PCS Detail: 

• Dataset Report_PCSDetail, fields:
  • InteractionID
  • StartTime
  • EndTime
  • Origin
  • BPName
  • AgentName
  • HoldTime
  • QueueTime
  • TalkTime
  • CrmFirstName
  • CrmLastName
  • surveyName
  • answers_shortName
  • answers_type
  • answers_text
  • answers_selection
  • answers_file
  • answers_endingReason
  • answers_index
       
• Dataset Report_PCSDetail, parameters:
  • @DW_DBName
  • @StartTime
  • @EndTime
  • @Offset
  • @Agents
  • @BusinessProcessName
  • @OriginName
  • @Current_User
  • @TimeZone
  • @IncludeCRM
           
• CSV Export, fields:
  • Origin
  • InteractionID
  • StartTime
  • EndTime
  • BPName
  • surveyName
  • AgentName
  • CrmFirstName
  • CrmLastName
  • QueueTime
  • TalkTime
  • HoldTime
  • Question
  • Answer
           

New Report 9.02 PCS Summary Agent:

• Dataset Report_PCSSummaryAgent, fields:
  • BPName
  • AgentName
  • surveyName
  • AverageScore
• Dataset Report_PCSSummaryAgent, parameters:
  • @DW_DBName
  • @StartTime
  • @EndTime
  • @Offset
  • @Agents
  • @BusinessProcessName
  • @Current_User
  • @TimeZone
            
• CSV Export, fields:
  • OptInBusinessProcess
  • AgentName
  • AverageScore
            

New Report 9.03 PCS Summary BP:

• Dataset Report_PCSSummaryBP, fields:
  • BPName
  • surveyName
  • AverageScore    
• Dataset Report_PCSSummaryBP, parameters:
  • @DW_DBName
  • @StartTime
  • @EndTime
  • @Offset
  • @Agents
  • @BusinessProcessName
  • @Current_User
  • @TimeZone
• CSV Export, fields:
  • OptInBusinessProcess
  • SurveyName
  • AverageScore

LOGIC CHANGES:

• ETL db changes:
  •  changes procedures:
    • optimization:
      • dbo.Upload_F_Attempts
      • dbo.Upload_F_Callbacks
                     
    • new field load:
      •  dbo.Upload_F_Interactions(add Survey load)
• Users db procedures changes:
  • dbo.Prepare_For_Deploy
  • dbo.Prepare_For_Export
  • dbo.spRpt_GetInteractionsDataset
• Users db new procedures:
  • DW.spRpt_CampaignTimeFrameSpecificStatistics.sql
  • DW.spRpt_CampaignTimeFrameSpecificStatistics_SubDs1.sql
  • DW.spRpt_CampaignInspection.sql
  • DW.spRpt_CampaignInspection_SubDs1.sql
  • DW.spRpt_BusinessProcessAgentPerf.sql
  • DW.spRpt_PCSSummaryAgent.sql
  • DW.spRpt_PCSSummaryBP.sql
  • DW.spRpt_PCSDetail.sql
• Users db delete procedures:
  • dbo.spRpt_CampaignInspection_SubDs1.sql
  • dbo.spRpt_CampaignInspection.sql
  • dbo.spRpt_CampaignTimeFrameSpecificStatistics_SubDs1.sql
  • dbo.spRpt_CampaignTimeFrameSpecificStatistics.sql
  • dbo.spRpt_BusinessProcessAgentPerf.sql
        

    1.06 Staffing:

• • procedures changed:
    • dbo.spRpt_APCStaffing
  • Total Online:
    • Previous:
      • Count(distinct F_AgentLife.AgentID)
        where (dbo.F_AgentLife.StartTime > = D_TimeSlice.BeginTime and dbo.F_AgentLife.StartTime < D_TimeSlice.EndTime
        or dbo.F_AgentLife.EndTime > = D_TimeSlice.BeginTime and dbo.F_AgentLife.EndTime < D_TimeSlice.EndTime
        or D_TimeSlice.BeginTime between dbo.F_AgentLife.StartTime and dbo.F_AgentLife.EndTime)
    • Current:
      • Count(distinct F_AgentLife.AgentID)
        where (dbo.F_AgentLife.StartTime > = D_TimeSlice.BeginTime and dbo.F_AgentLife.StartTime < D_TimeSlice.EndTime
        or dbo.F_AgentLife.EndTime > = D_TimeSlice.BeginTime and dbo.F_AgentLife.EndTime < D_TimeSlice.EndTime
        or D_TimeSlice.BeginTime between dbo.F_AgentLife.StartTime and dbo.F_AgentLife.EndTime)
        and 
        (
        @BusinessProcessName = '- ALL -' 
         OR
        (@BusinessProcessName = F_AgentBpTime.BusinessProcessName and F_AgentBpTime.isAssigned = 1)
        )
                       
  • Total Available:
    • Previous:
      • Count(distinct F_AgentLife.AgentID)
        where D_AgentStatus.AgentStatusName in ('Busy','Dialing','WaitingForOffline','WaitingForBreak',
        'WaitingForBackofficeAdministrative',
        'WaitingForHandlingOutgoingInteractions','Ready','Offered')
         and (dbo.F_AgentLife.StartTime > = D_TimeSlice.BeginTime and dbo.F_AgentLife.StartTime < D_TimeSlice.EndTime
         or dbo.F_AgentLife.EndTime > = D_TimeSlice.BeginTime and dbo.F_AgentLife.EndTime < D_TimeSlice.EndTime
         or D_TimeSlice.BeginTime between dbo.F_AgentLife.StartTime and dbo.F_AgentLife.EndTime)
             
    •  Current:
      • Count(distinct F_AgentLife.AgentID)
        where D_AgentStatus.AgentStatusName in ('Busy','Dialing','WaitingForOffline','WaitingForBreak',
        'WaitingForBackofficeAdministrative',
        'WaitingForHandlingOutgoingInteractions','Ready','Offered')
         and (dbo.F_AgentLife.StartTime > = D_TimeSlice.BeginTime and dbo.F_AgentLife.StartTime < D_TimeSlice.EndTime
         or dbo.F_AgentLife.EndTime > = D_TimeSlice.BeginTime and dbo.F_AgentLife.EndTime < D_TimeSlice.EndTime
         or D_TimeSlice.BeginTime between dbo.F_AgentLife.StartTime and dbo.F_AgentLife.EndTime)
         and 
         (
         @BusinessProcessName = '- ALL -' 
         OR
         (@BusinessProcessName = F_AgentBpTime.BusinessProcessName and F_AgentBpTime.isAssigned = 1)
         )
                   

2.04 Agent Interaction Summary:

• • procedures changed:
    • DW.spRpt_AgentInteractionSummaryReport    
           
  • Ready Idle:
    • Previous:
      • F_AgentLife.EndTime - F_AgentLife.StartTime where AgentState in ('Ready', 'Offered')
             
    • Current:
      • F_AgentBpTime.Ready + AgentBpTime.Offered
  • Break:
    • Previous:
      • F_AgentLife.EndTime - F_AgentLife.StartTimewhere AgentState in ('Break')
             
    • Current:
      • F_AgentBpTime.Break
  • Busy:    
    • Previous:
      • F_AgentLife.EndTime - F_AgentLife.StartTime
        F_AgentLife.EndTime - F_AgentLife.StartTime
        where AgentState in ('Busy', 'Dialing', 'WaitingFor%')
        or (AgentState in ('Backoffice - Administrative') and F_AgentLife.InteractionId is not null)
        or (AgentState in ('Handling - Outgoing Interactions') and F_AgentLife.InteractionId is not null)
             
    • Current:
      • F_AgentBpTime.Busy + F_AgentBpTime.Dialing + F_AgentBpTime.WaitingFor + F_AgentBpTime.BackOfficeBusy +  F_AgentBpTime.HandlingOutgoingIntBusy
  • Staffed:
    • Previous:
      • max(F_AgentLife.EndTime) - min(F_AgentLife.StartTime) within a shift
             
    • Current:
      • F_AgentBpTime.LoginTime
             
  • No Answer:
    • Previous:
      • F_AgentLife.EndTime - F_AgentLife.StartTime
        where AgentState in ('No Answer')
    • Current:
      • F_AgentBpTime.NoAnswer
  • BackOffice Idle:
    • Previous:
      • F_AgentLife.EndTime - F_AgentLife.StartTime
        where AgentState in ('Backoffice - Administrative')
        and F_AgentLife.InteractionId is null
             
    • Current:
      • F_AgentBpTime.BackOfficeIdle
         
  • Handle Outgoing Idle:
    • Previous:
      • F_AgentLife.EndTime - F_AgentLife.StartTimewhere AgentState in (if there was a state 'Handling - Outgoing Interactions')
        and F_AgentLife.InteractionId is null
               
    • Current:
      • F_AgentBpTime.HandlingOutgoingIntIdle
                       

5.05 Queue Interval Summary:

• procedures changed:
  • DW.spRpt_APCSummary
• Agent Staffed:
  • Previous:
    • Count(distinct F_AgentLife.AgentID)
      F_AgentLife.Status <> 'Offline'
      F_AgentLife.AgentLifeTypeID = 1
  • Current:
    • Count(distinct F_AgentBpTime.AgentID)
• Agent Available:
  • Previous:
    • Count(distinct F_AgentLife.AgentID)
      F_AgentLife.Status in ('Ready', 'Busy')
      F_AgentLife.AgentLifeTypeID = 1
  • Current:
    • Count(distinct F_AgentBpTime.AgentID)
      F_AgentBpTime.Ready + F_AgentBpTime.Busy > 0

   7.01 Customer Experience:

• • procedures changed:
    • dbo.spRpt_CustomerExperience
    • dbo.spRpt_CustomerExperience_Total
  • Staffed Agents:
    • Previous:
      • Count(distinct F_AgentLife.AgentID)
        F_AgentLife.Status <> 'Offline' 
        and F_AgentLife.AgentLifeTypeID = 1
    • Current:
      • Count(distinct F_AgentBpTime.AgentID)
        @BusinessProcessName = '- ALL -' 
         OR
         (@BusinessProcessName = F_AgentBpTime.BusinessProcessName and F_AgentBpTime.isAssigned = 1)
                        

7.03 Contact Center Performance:

• procedures changed:
  • dbo.spRpt_ContactCenterPerformance
  • dbo.spRpt_ContactCenterPerformance_Total
• Staffed Agents:
  • Previous:
    • Count(distinct F_AgentLife.AgentID)
      F_AgentLife.Status <> 'Offline' 
      and F_AgentLife.AgentLifeTypeID = 1
  • Current:
    • Count(distinct F_AgentBpTime.AgentID)
      @BusinessProcessName = '- ALL -' 
       OR
      (@BusinessProcessName = F_AgentBpTime.BusinessProcessName and F_AgentBpTime.isAssigned = 1)
                      
         

7.05 Contact Center Performance By Demands:

• procedures changed:
  • DW.spRpt_ContactCenterPerformanceByDemand
• Staffed Agents:
  • Previous:
    • Count(distinct F_AgentLife.AgentID)
      F_AgentLife.Status <> 'Offline' 
      and F_AgentLife.AgentLifeTypeID = 1
           
  • Current:
    • Count(distinct F_AgentBpTime.AgentID)
      @BusinessProcessName = '- ALL -' 
      OR
      (@BusinessProcessName = F_AgentBpTime.BusinessProcessName and F_AgentBpTime.isAssigned = 1)