3. МиТ ПрИС. лаб 3. Модель для оценки упр. инц.. Лабораторная работа Разработка моделей данных и запросов к бд для оценивания показателей эффективности процесса управления инцидентами
![]()
|
3.6 Разработка запросов к базе данных для оценки показателей эффективности процесса управления инцидентамиСледует разработать запросы для вычисления значений заданных показателей эффективности на языке SQL с использованием или без использования средств автоматизации. Можно использовать такие программы как MS Access, SQL Management Studio+MS SQL Server, Open ModelSphere и другие. Используя таблицу, подобную таблице «РегИнц», можно разработать запросы для вычисления количества зарегистрированных инцидентов по дням (рисунок 3.5): SELECT Year([ДатаРегИнц]) AS Год, РегИнц.ДатаРегИнц, Count(РегИнц!ДатаРегИнц) AS КоличИнц FROM РегИнц GROUP BY Year([ДатаРегИнц]), РегИнц.ДатаРегИнц; или по месяцам: SELECT Year(РегИнц!ДатаРегИнц) AS Год, Month(РегИнц!ДатаРегИнц) AS Месяц, Count(РегИнц!ДатаРегИнц) AS КоличИнц FROM РегИнц GROUP BY Year(РегИнц!ДатаРегИнц), Month(РегИнц!ДатаРегИнц); ![]() Рисунок 3.5 – Запрос (в режиме конструктора) для вычисления количества зарегистрированных инцидентов по дням На основе запроса о количестве ежедневно регистрируемых инцидентов («З_инц в день») можно составить запросы для вычисления: количества регистрируемых инцидентов в интервале дат (рисунок 3.6); средних значений показателя по дням (рисунок 3.7) и по месяцам (рисунок 3.8). ![]() Рисунок 3.6 – Запрос (в режиме конструктора) для вычисления количества регистрируемых инцидентов в интервале дат ![]() Рисунок 3.7 – Запрос (в режиме конструктора) для вычисления средних значений количества регистрируемых инцидентов по дням ![]() Рисунок 3.8 – Запрос (в режиме конструктора) для вычисления средних значений количества регистрируемых инцидентов по месяцам При вводе данных о статусах инцидентов рекомендуется для номера инцидента отображать ещё дату и время регистрации, а также, при необходимости, «Название предприятия-заказчика», «ФИО и должность сотрудника» и/или другие сведения для корректной идентификации инцидента сотрудником ИТ-компании. При использовании MS Access это назначение выполняется с помощью подстановки в режиме конструктора для таблицы, например, для поля «НомерИнц» в таблице «Статусы_инц» в качестве источника данных указывается таблица «РегИнц» (рисунок 3.9), из которой при выборе инцидента отображаются данные первых трёх столбцов (номер, дата и время регистрации инцидента, рисунок 3.10). ![]() Рисунок 3.9 – Назначение источника данных для поля «НомерИнц» в таблице «Статусы_инц» ![]() Рисунок 3.10 – Пример отображения данных в поле со списком для выбора номер инцидента Чтобы в поле «КодСтатуса» отображалось название статуса, следует в таблице «Статусы_инц» для этого поля назначить подстановку, в которой источником данных является таблица «Статусы» (рисунки 3.3, 3.11), при этом код статуса при выборе отображаться не будет, т.к. ширина столбца присоединяемого столбца (первого) задаётся равной нулю (рисунок 3.11). ![]() Рисунок 3.11 – Назначение источника данных для поля «КодСтатуса» в таблице «Статусы_инц» Для регистрации инцидентов с учетом информации о представителях предприятия-заказчика можно использовать модель данных, представленную на рисунке 3.12. ![]() Рисунок 3.12 - Модель данных для регистрации инцидентов с учетом информации о представителях предприятия-заказчика Для ввода данных рекомендуется в таблице «РегИнц_Заказ» для поля «Сотрудник_Заказ» назначить подстановку с источником данных в виде запроса (рисунок 3.13), включающего сведения о предприятии-заказчике, подразделении и сотруднике, который представляет информацию об инциденте (рисунок 3.14). ![]() Рисунок 3.13 – Назначение источника данных для поля «Сотрудник_Заказ» в таблице «РегИнц_Заказ» ![]() Рисунок 3.14 – Запрос (в режиме конструктора) для представления данных о предприятии-заказчике, подразделении и сотруднике Продолжительность работ по закрытию инцидента (в часах) при круглосуточном обслуживании (Тинц.) может быть вычислена на основе графической модели (рисунок 3.15) с помощью запроса, включающего следующее выражение (в MS Access): Hour([РегИнц]![ВремяЗакр]-[РегИнц]![ВремяРегИнц])+ 24*([РегИнц]![ДатаЗакр]-[РегИнц]![ДатаРегИнц]). ![]() Рисунок 3.15 – Модель для вычисления продолжительности работ по закрытию инцидента (в часах) при круглосуточном обслуживании В данном примере использованы обозначения: «РегИнц» - таблица для регистрации инцидентов; «ДатаРегИнц», «ДатаЗакр» - даты регистрации и закрытия инцидентов (атрибуты таблицы «РегИнц»); «ВремяРегИнц», «ВремяЗакр» - время регистрации (для соответствующего значения атрибута «ДатаРегИнц») и закрытия инцидентов (атрибуты таблицы «РегИнц»); «Hour» - функция, возвращающая число часов (от 0 до 23). В случае, если работа по разрешению инцидентов выполняется, например, в течение девяти часов (например, с 8.00 до 17.00 с нерегламентированным перерывом на обед), и следует учитывать интервалы времени в минутах, то продолжительность работы в минутах можно вычислить на основе графической модели (рисунок 3.16) в составе запроса с помощью следующего выражения: 60*Hour([Табл_Расч_Врем]![ВремяОк]-[Табл_Расч_Врем]![ВремяНач])+ Minute([Табл_Расч_Врем]![ВремяОк]-[Табл_Расч_Врем]![ВремяНач])+540*([Табл_Расч_Врем]![ДатаОк]-[Табл_Расч_Врем]![ДатаНач]). В данном примере использованы обозначения таблицы «Табл_Расч_Врем» (рисунок 3.17) ![]() Рисунок 3.16 – Модель для вычисления продолжительности работ по закрытию инцидента (в часах или минутах) при обслуживании с 8.00 до 17.00 без перерыва Вычисление финансовых затрат на разрешение инцидента на основе почасовых ставок сотрудников при участии специалистов двух категорий может выполняться по формуле: ![]() где C1, C2– почасовые расценки специалистов первой и второй категории, соответственно, участвующих в разрешении инцидента; ![]() ni– количество интервалов времени, использованных для работыспециалистом i-й категории. При выполнении работ специалистами nс категорий, для каждой из которых установлены расценки Ci (руб. час), финансовых затрат на разрешение инцидента можно оценить по формуле: ![]() Для вычисления значений ![]() ![]() ![]() Рисунок 3.17 – Структура дополнительной таблицы для расчета затрат времени и стоимости работ Таблица «Табл_Расч_Врем» (рисунок 3.17) содержит атрибуты: «НомерИнц» - номер инцидента, присвоенный при регистрации; «ДатаНач», «ВремяНач» - дата и время начала интервала времени, в течение которого инцидент имеет статус «СтатусНач»; «ДатаОк», «ВремяОк» - дата и время окончания интервала времени, после которого инциденту присвоен статус «СтатусОк»; «Сотр_ИТ» - номер (или код) сотрудника ИТ-компании, который выполнил работу в данном интервале времени. ![]() Рисунок 3.18 – Содержание таблицы «Статусы_инц» с записями о статусе работ по инциденту №1 Для выполнения записей в таблицу интервалов времени для расчетов продолжительности работ необходимо определить, какие статусы должны соответствовать началу и окончанию интервала времени; можно принять последовательность, указанную в таблице 3.2. Таблица 3.2 – Статусы работ над инцидентами
Запись в таблицу интервалов времени для расчетов продолжительности работ может выполняться с помощью пары запросов: запрос на добавление выполняет для интервала времени работы над каждым инцидентом запись в первые четыре столбца таблицы «Табл_Расч_Врем» (рисунок 3.19); запрос на обновление выполняет для интервала времени работы над каждым инцидентом запись в последние четыре столбца таблицы «Табл_Расч_Врем» (рисунок 3.20). В результате выполнения четырёх пар запросов для каждого инцидента таблица «Табл_Расч_Врем» будет заполнена данными, как показано в примере на рисунке 3.21. ![]() Рисунок 3.19 – Содержание таблицы «Табл_Расч_Врем» после добавления записи с помощью запроса на добавление ![]() Рисунок 3.20 – Содержание таблицы «Табл_Расч_Врем» после изменения записи с помощью запроса на обновление (заполнены значения четырёх последних атрибутов) ![]() Рисунок 3.21 – Содержание таблицы «Табл_Расч_Врем» после обработки всех записей о статусе работ по инцидентам №1 и №2 (выполнено преобразование записей таблицы «Статусы_инц» в записи таблицы для расчетов продолжительности и стоимости работ) Запрос на добавление в таблицу «Табл_Расч_Врем» в MS Access можно создать в режиме конструктора (рисунок 3.22) и/или с помощью языка запросов SQL: INSERT INTO Табл_Расч_Врем ( НомерИнц, ДатаНач, ВремяНач, СтатусНач ) SELECT Статусы_инц.НомерИнц, Статусы_инц.ДатаСтатуса, Статусы_инц.ВремяСтатуса, Статусы.НаименовСтатуса FROM Статусы INNER JOIN Статусы_инц ON Статусы.КодСтатуса = Статусы_инц.КодСтатуса WHERE (((Статусы.НаименовСтатуса)="новый")). ![]() Рисунок 3.22 - Запрос на добавление в таблицу «Табл_Расч_Врем» в MS Access в режиме конструктора Запрос на обновление записи в таблице «Табл_Расч_Врем» в MS Access можно создать в режиме конструктора (рисунок 3.23) и/или с помощью языка запросов SQL: UPDATE Табл_Расч_Врем INNER JOIN З_Инц_Принят ON Табл_Расч_Врем.НомерИнц = З_Инц_Принят.НомерИнц SET Табл_Расч_Врем.СтатусОк = [З_Инц_Принят]![НаименовСтатуса], Табл_Расч_Врем.ДатаОк = [З_Инц_Принят]![Дата1], Табл_Расч_Врем.ВремяОк = [З_Инц_Принят]![Время1], Табл_Расч_Врем.Сотр_ИТ = [З_Инц_Принят]![НомерСотр_ИТ] WHERE (((Табл_Расч_Врем.СтатусНач)="новый")). ![]() Рисунок 3.23 - Запрос на обновление записи в таблице «Табл_Расч_Врем» в MS Access в режиме конструктора Источником данных для запроса на обновление в данном примере является запрос на выборку «З_Инц_принят» (рисунок 3.24), который на языке SQL имеет вид: SELECT Статусы_инц.НомерИнц, Статусы.НаименовСтатуса, Статусы_инц.ДатаСтатуса AS Дата1, Статусы_инц.ВремяСтатуса AS Время1, Статусы_инц.НомерСотр_ИТ FROM Статусы INNER JOIN Статусы_инц ON Статусы.КодСтатуса = Статусы_инц.КодСтатуса WHERE (((Статусы.НаименовСтатуса)="принят")). ![]() Рисунок 3.24 - Запрос на выборку «З_Инц_принят» - источник данных для запроса на обновление записи в таблице «Табл_Расч_Врем» После заполнения таблицы «Табл_Расч_Врем» с помощью запроса можно вычислить продолжительность работ по интервалам (в минутах) и стоимость работ по каждому интервалу (рисунок 3.25): SELECT Табл_Расч_Врем.НомерИнц, Сотр_ИТ.НомерСотр_ИТ, Табл_Расч_Врем.ДатаНач, Табл_Расч_Врем.ВремяНач, Табл_Расч_Врем.СтатусНач, Табл_Расч_Врем.ДатаОк, Табл_Расч_Врем.ВремяОк, Табл_Расч_Врем.СтатусОк, Minute(Табл_Расч_Врем!ВремяОк-Табл_Расч_Врем!ВремяНач)+540*(Табл_Расч_Врем!ДатаОк-Табл_Расч_Врем!ДатаНач)+60*Hour(Табл_Расч_Врем!ВремяОк-Табл_Расч_Врем!ВремяНач) AS Продолжительность_работы, [Продолжительность_работы]*Сотр_ИТ!Почас_ставка/60 AS [Стоимость(руб)] FROM Сотр_ИТ INNER JOIN Табл_Расч_Врем ON Сотр_ИТ.НомерСотр_ИТ = Табл_Расч_Врем.Сотр_ИТ. ![]() Рисунок 3.25 – Пример результатов выполнения запроса на выборку данных о продолжительности и стоимости работы над инцидентами с группировкой по сотрудникам ИТ-компании Суммарная стоимость работ по каждому инциденту может быть вычислена с помощью запроса, который выполняет группировку стоимостных данных по каждому инциденту (рисунок 3.26): SELECT [З_Продолжит работ по сотр].НомерИнц, Sum([З_Продолжит работ по сотр].[Стоимость(руб)]) AS [Затраты на инцидент (руб)] FROM [З_Продолжит работ по сотр] GROUP BY [З_Продолжит работ по сотр].НомерИнц. ![]() Рисунок 3.26 – Пример результатов выполнения запроса для вычисления стоимости работ над инцидентами Таким образом, для вычисления финансовых затрат на выполнение работ по каждому инциденту с использованием дополнительной таблицы для расчета затрат времени («Табл_Расч_Врем», рисунок 3.17) следует выполнить последовательность запросов, указанную в таблице 3.3. Запрос п.9 должен формировать результат, подобный рисунку 3.25; запрос п.10 должен формировать результат, подобный рисунку 3.26. Запрос п.11 должен освобождать таблицу «Табл_Расч_Врем» для последующих вычислений. Таблица 3.3 - Последовательность и характеристики запросов для вычисления финансовых затрат на выполнение работ
Можно выполнить вычисления значений показателей эффективности процесса управления инцидентами с помощью программных процедур (разработать алгоритмы вычисления значений, составить программные процедуры для обработки данных из базы данных без дополнительных таблиц). |