Hi,
I have a data model which I am trying to use to calculate KPI's based on Service Level Agreement (IT outsourcing stuff).
The data model contains the following tables:
Ticket History - describes all states in which all tickets were in with all relevant information: state, state start datetime, state end datetime
- Ticket ID
- Service Group
- Assignee
- Ticket State
- Event Start
- Event End
Example:
Ticket ID - Service Group - Assignee - Ticket State - Event Start - Event End
1 - Service Desk - Mark - Assigned - 01.01.2018 08:00:00 - 01.01.2018 09:00:00
1 - Service Desk - Mark - Work in Progress - 01.01.2018 09:00:00 - 09.01.2018 10:00:00
1 - Service Desk - Mark - Solved - 09.01.2018 10:00:00 - 09.01.2018 11:00:00
2 - Datacenter Support - John - Assigned - 01.01.2018 08:00:00 - 01.01.2018 08:30:00
2 - Datacenter Support - Mark - Suspended - 01.01.2018 08:30:00 - 09.01.2018 09:00:00
2 - Datacenter Support - Mark - Work in Progress - 09.01.2018 09:00:00 - 09.01.2018 20:00:00
Calendar - standart date table with calulated columns for year, month, etc.
- date
- Year
- Week day
- Day type : working / non-working based on local calendar
Example:
date - Year - Week Day - Day type
01.01.2018 - 2018 - Monday - Non working day
02.01.2018 - 2018 - Tuesday - Non working day
03.01.2018 - 2018 - Wednesday - Non working day
04.01.2018 - 2018 - Thursday - Non working day
05.01.2018 - 2018 - Friday - Non working day
06.01.2018 - 2018 - Saturday - Non working day
07.01.2018 - 2018 - Sunday - Non working day
08.01.2018 - 2018 - Monday - Non working day
09.01.2018 - 2018 - Tuesday - Working day
10.01.2018 - 2018 - Wednesday - Working day
Teams - dimension table grouping Service groups by services.
Example:
Team - Service
Service Desk - 1st line support
Datacenter Support - Datacenter Support
Storage Support - Datacenter Support
Database Support - Datacenter Support
Service Time - table specifies service times for each service.
- Service
- Day type
- Service Start
- Service End
Example:
Service - Day type - Service Start - Service End
1st Line Support - Working day - 08:00:00 - 20:00:00
1st line Support - non-working day - N/A - N/A
Datacenter Support - Working day - 00:00:00 - 23:59:59
Datacenter Support - non-Working day - 00:00:00 - 23:59:59
So I need to create a measure (I do not like calculated columns) that will calculate duration of an event in the table Ticket History considering the service time, let's call it duration and the result of it is in minutes:
Ticket ID - Service Group - Assignee - Ticket State - Event Start - Event End - Duration
1 - Service Desk - Mark - Assigned - 01.01.2018 08:00:00 - 01.01.2018 09:00:00 - 0
Comment: as service time for 1st line support service Service Desk service group is a part of is 8:00 to 20:00 for all working days and 01.01.2018 - is not a working day, there are no service hours so the duration is 0
1 - Service Desk - Mark - Work in Progress - 01.01.2018 09:00:00 - 09.01.2018 10:00:00 - 120
Comment: as service time for 1st line support service Service Desk service group is a part of is 8:00 to 20:00 for all working days and 01-08.01.2018 - are not a working days, the only overlapping of service hours and the event period is on 09.01.2018 starting 08:00:00 (service time start for 1st line support) till 10:00:00 (the end of the event) and it is 120 minutes
1 - Service Desk - Mark - Solved - 09.01.2018 10:00:00 - 09.01.2018 11:00:00 - 60
2 - Datacenter Support - John - Assigned - 01.01.2018 08:00:00 - 01.01.2018 08:30:00 - 30
2 - Datacenter Support - Mark - Suspended - 01.01.2018 08:30:00 - 09.01.2018 09:00:00 - 11550
Comment: for Datacenter Support service hours are basically 24х7 so the duration is simply the number of minutes between 01.01.2018 08:30:00 and 09.01.2018 09:00:00 whish is 11550
2 - Datacenter Support - Mark - Work in Progress - 09.01.2018 09:00:00 - 09.01.2018 20:00:00 - 660
I just can't wrap my head around this. Please help!
