Введение

Аббревиатура KPI означает Key Performance Indicator, что на русский обычно переводят как ключевые показатели эффективности.
Некоторое время назад я столкнулся с проблемой расчетов KPI в среде Microsoft SQL Server. В то время мы все отчеты с KPI делали вручную с помощью обычных SQL скриптов. Но через некоторое время возникли серьезные проблемы:

Разные аналитики считают по-разному

Изменение алгоритма обсчета хотя бы одного KPI требует "перелопачивания" большого количества отчетов

После внесения изменений тяжело делать откат назад или определить кто внес изменения

Цифры начинают плавать, т.к. каждый раз пишется новый SQL скрипт, в котором про некоторые детали могут забыть.

После внедрения OLAP кубов, я решил попытаться исправить ситуаций с KPIs. Для этого я воспользовался динамическим SQL, и выдвинул следующие требования к проекту:

Изменение кода и логики расчета KPI должно происходить в одном месте

Код должен быть параметрическим, т.е. на вход подается 2 параметра: дата начала и конца анализа. Каждый KPi - это одна цифра.

На основе этих KPI, можно строить отчеты с любой группировкой и жестко заданной сортировкой.

Описание и логика расчета привязываются к KPI

Т.к. эта система очень ответственная, было решено включить логгирование всех изменений через SQL триггеры.

Общий обзор

Все решение свелось к созданию 5 таблиц:

KPIitem - содержит название KPI и динамический SQL для его расчета (справочник)

KPIgroup - справочник названий групп

KPIreport - справочник отчетов

KPIlink - таблица сборки отчета

KPIitemAlterLog - таблица записи изменений всех KPI в таблице KPIitem

и двух хранимых процедур:

_KPIitem - процедура расчета одного KPI из таблицы KPIitem

_KPIreport - процедура генерации отчета KPI, эта процедура использует процедуру _KPIitem

Собственно задачу построения отчетов из KPI можно разбить на три части:

Создание списка и написание SQL скриптов для расчета KPI в таблице KPIitem

Создание групп (при необходимости) в таблице KPIgroup , заведение отчета в таблице KPIreport и объединение KPI в отчет и группы в таблице KPIlink

Генерация отчета процедурой _KPIreport

Скачать SQL скрипт-генератор этих таблиц и хранимых процедур можно по этой ссылке здесь: KPI.sql

Создание KPI

Показывать я буду сразу на примерах, так понятнее.

Пусть у нас имеется таблица Orders, в ней есть три поля ID (идентификатор заказа), AmountRUR (стоимость заказа в рублях) и Date (дата заказа). И нам нужно построить отчет с тремя показателями, коорые можно рассчитывать за любой период:

Кол-во заказов за период

Стоимость заказов за период

Средняя стоимость одного заказа за период

Для написания этих отчетов мы должны иметь ввиду два условия:

"Шапка", которая подставляется перед выполнением скрипта из таблицы KPIitem:

SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET NOCOUNT ON

DECLARE @StartDate datetime,
@EndDate datetime

select @StartDate=StartDate from #Date
select @EndDate=EndDate from #Date

эта шапка подставляется автоматически - и она предназначена для двух целей: для передачи входных параметров (переменные @StartDate и @EndDate) и для того чтобы предотвратить аварийное завершение скрипта при делении на ноль.

В конце работы скрипт должен выдать только одну цифру - результат, делается это с помощью этого куска кода:

insert #temptable(Value)
select …

Приступаем к делу, рассчитываем показатель "Кол-во заказов за период". Для KPI пишем следующий код:

insert #temptable(Value)
(select count(*) from Orders
where Date between @StartDate and @EndDate)

Этот рассчитывает кол-во заказов в периоде между датами @StartDate и @EndDate, особо заморачиваться не стал, поэтому результат сразу поместил в возвращаую таблицу.

Дальше, создаем запись в таблице KPIItem:
1) Заполняем поля ID, EngName - назв. показателя на английском, RusName - на русском
2) SQLText1 - вставляем SQL скрипт. Здесь есть ограничение в 8Кб на размер скрипта, если он больше, разбейте его на части по 8 Кб и последовательно вставьте в поля SQLText1, SQLText2, SQLText3, SQLText4. Кстати, об ограничениях скриптов, нельзя использовать конструкции nested join:

insert TableA(Column1, Column2)
exec _Procedure

3) AggFunction - агрегатная функция, вещь в принципе бесполезная, но пригодится, если вы захотите кешировать результаты расчетов KPI, а затем их объединять (sum, avg, и т.д.).

Тоже самое делаем и для показателя "Стоимость заказов за период". Третий показатель "Средняя стоимость одного заказа за период", можно подсчитать в лоб, а можно заметить, что это всего лишь отношение 2го показателя к первому. Для этого воспользуемся функцией KPIItem. Пишем следующие скрипт:

declare @@a float,
@@b float

EXEC _KPIitem 1, @StartDate, @EndDAte , @@a OUTPUT

EXEC _KPIitem 2, @StartDate, @EndDAte , @@a OUTPUT

insert #temptable(Value)
select @@b/@@a

пихаем этот скрипт как третий показатель, и все готово.

Продолжение следует...