Повышение эффективности работы с просроченной дебиторской задолженностью становится одним из условий финансовой стабильности и приоритетных задач как для многих банков, так и компаний. Эффективная работа с просроченными долгами может внести весомый вклад в поддержку компании особенно в период кризиса.
Так, контроль над ситуацией с просроченной задолженностью в банковском секторе является инструментом мониторинга качества кредитного портфеля, который в свою очередь влияет на объем банковских резервов и сказывается на привлечении финансовых инвестиций. После событий 2008 года проблемные кредиты могут составлять от 10 до 20% общего кредитного портфеля российских банков, хотя истинный масштаб просроченной задолженности в банковском секторе определить практически невозможно, так как многие кредитные организации не спешат раскрыть её реальные значения.
Перейдем в практическую плоскость и рассмотрим (базовые принципы) как SQL Server Analysis Services (SSAS) помогает анализировать просроченную задолженность. В хранилище/витрине данных для каждого договора на каждую отчётную дату рассчитаны и хранятся остатки и количество дней просрочки по методу FIFO и методу LIFO, а дни укладываются в базовые группы просрочки "0", "1..7", "8..30", "31..60", "61..90", "91..120", "121..150", "151..180", "181..210", "211..360", "360+". Данные показатели являются базисными для Behavioral Scoring, Collection Scoring, Fraud Scoring.
По методу LIFO отсчёт количества дней просрочки основного долга (ОД) начинается с даты возникновения ненулевой суммы на счете просрочки ОД и до момента обнуления суммы на этом счете. По методу FIFO счётчик количества дней просрочки основного долга также начинает работать с даты возникновения ненулевой суммы на счете просрочки ОД, но может уменьшаться при погашении одного из просроченных платежей.
По методу FIFO договор может переходить из группы просрочки, как в следующую худшую группу просрочки, так и в любую предыдущую группу просрочки. По методу LIFO договор может переходить следующую худшую группу просрочки, либо в группу "0" или "1..30".
Таким образом, с точки зрения оценки качества кредитного портфеля наиболее жёстким вариантом отнесения в группу просрочки является вариант количества дней просрочки основного долга + процентов по методу LIFO.
В реляционном источнике сформируем таблицу базовых групп просрочек v_overdue_group, в которой для удобства использования также введем укрупненные группы "Группа 0-30-30плюс", "Группа 0-60-60плюс", "Группа 0-90-90плюс", "Группа 0-30-60-90-90плюс".
На базе этой таблицы создадим измерение "Группы просрочки", дополнив свойствами "Цвет фона", "Цвет шрифта", "Цвет фона 0-30-60-90-90плюс", "Цвет шрифта 0-30-60-90-90плюс". Эти свойства затем будут использованы для централизованной установки корпоративного цветового оформления в многомерной модели OLAP.
Корпоративные цвета повышают смысловую нагрузку отчётов, обеспечивают интуитивность восприятия и единые стандарты в рамках компании (могут быть подкреплены внутрифирменными регламентными документами).
--------------------------------------------- Группа Цвет Цвет просрочки фона фона описание --------------------------------------------- "1...7" 25600 RGB(0,100,0) "8...30" 7451452 RGB(60,179,113) "31...60" 5296274 RGB(146,208,80) "61...90" 65535 RGB(255,255,0) "91...120" 42495 RGB(255,165,0) "121...150" 4678655 RGB(255,99,71) "151...180" 255 RGB(255,0,0) "181...210" 2162853 RGB(165,0,33) "211...360" 13158 RGB(102,51,0) "360+" 2631720 RGB(0,0,0)
Поскольку таблица базовых групп просрочек постоянна, то связи между атрибутами измерения установим жесткими (Rigid):
Применим ещё один трюк для централизованного оформления OLAP-куба. В реляционном источнике создадим простую табличку v_display_rule, а на её базе - вспомогательное измерение "_Правила оформления", содержащее только ключевой атрибут.
----------------------------------------------------------------------------- Код Правило оформления правила ----------------------------------------------------------------------------- 0 без оформления 1 выделение цветом согласно группам просрочки ОД FIFO 2 выделение курсивом внебалансовых сумм 4 округление до тысяч сумм показателей 8 округление до миллионов сумм показателей 16 округление до тысяч сумм показателей, зависимых от параметров 32 округление до миллионов сумм показателей, зависимых от параметров 64 показывать аббревиатуры округлений сумм
Посредством выбора элементов, комбинации элементов данного измерения можно будет настроить формат отображения показателей в текущем отчете. Обратите внимание, что значение кода правила оформления есть нечто иное как 2 в степени n. Смысл этой особенности будет раскрыт позже. Вспомогательное измерение подлежит размещению только в области фильтров сводной таблицы.
Поясним определения правил оформления:
- без оформления - выключение, отмена всех правил форматирования (даже если выбраны другие правила); будет являться правилом по умолчанию;
- выделение курсивом внебалансовых сумм – суммы остатков на внебалансовых счетах выделяются курсивом;
- округление до тысяч сумм показателей – формат представления в виде арифметического округления значений базовых показателей до тысяч (только формат представления, но не округленное значение);
- округление до миллионов сумм показателей – формат представления в виде арифметического округления значений базовых показателей до миллионов (только формат представления, но не округленное значение);
- округление до тысяч сумм показателей, зависимых от параметров – формат представления в виде арифметического округления значений вычисляемых показателей до тысяч (только формат представления, но не округленное значение);
- округление до миллионов сумм показателей, зависимых от параметров – формат представления в виде арифметического округления значений вычисляемых показателей до миллионов (только формат представления, но не округленное значение);
- показывать аббревиатуры округлений сумм – аббревиатуры "тысяча", "миллион" с учетом языковой локализации; отключать аббревиатуры особенно удобно для шкал на графиках;
- выделение цветом согласно группам просрочки ОД FIFO – если опция включена и в области строк / столбцов сводной таблицы размещена иерархия "Группа 0-30-60-90-90плюс" измерения "Группы просрочки ОД FIFO" или эта иерархия помещена в область фильтров и выбран единственный элемент, то остатки балансовых сумм и количество договоров раскрашиваются предопределенными цветами, соответствующим качеству кредитного портфеля (от зеленого цвета для малой просрочки до чёрного цвета для безнадёжной просрочки).
Теперь свяжем наши измерения и факты:
Вернемся к таблице v_display_rule. На базе этой таблицы была создана размерная группа Display_rule, содержащая скрытую физическую меру id_display_rule с типом агрегирования AgregateFunction = Sum. Вспомогательное измерение "_Правила оформления" связано только с размерной группой Display_rule (фактовая связь). Таким образом, когда пользователь разместит вспомогательное измерение "_Правила оформления" в область фильтров сводной таблицы и выберет (отметит) несколько правил одновременно, мера id_display_rule будет просуммирована. Поскольку значения id_display_rule - это 2 в степени n, то результирующая сумма уникальна по составу выбранных элементов, например, значение 5 может быть получено только как 1 + 4 ("выделение цветом согласно группам просрочки ОД FIFO" и "округление до тысяч сумм показателей").
Для обратной декомпозиции нам потребуется написать и подключить внешнюю CLR функцию, которая бы проверяла и возвращала True или False в случае вхождения указанного значения в сумму значений: MY_ASSP.BitInBinaryMask(сумма_выбранных_элементов, проверяемое_значение) Функцию BitInBinaryMask предлагается Вам разработать самостоятельно.
Перейдем на вкладку вычислений куба (MDX-script) и пропишем нижеследующие выражения:
--------------------------------------------------------------------------------------------------------------------------------- -- для измерения "Отчетные даты" устанавливается выбранной -- по умолчанию дата равная дате наиболее поздней, на которую есть контракты ALTER CUBE CURRENTCUBE UPDATE DIMENSION [Отчетные даты], DEFAULT_MEMBER = 'TAIL(FILTER([Отчетные даты].[Год-квартал-месяц-дата].MEMBERS, [MEASURES].[MEASURES].[Количество_контрактов] !=NULL),1).ITEM(0)'; -- по умолчанию к мерам не будут применяться никакие правила оформления: ALTER CUBE CURRENTCUBE UPDATE DIMENSION [_Правила оформления], DEFAULT_MEMBER ='[_Правила оформления].[Правило оформления].&[0]' ; -- если для измерения определён элемент по умолчанию, то он работает даже тогда, -- когда измерение не размещено ни в одной из областей (осей) сводной таблицы / диаграммы -- в данной служебной мере запоминаем backColor для мер CREATE MEMBER CURRENTCUBE.[MEASURES].[backColor] AS IIF( MY_ASSP.BitInBinaryMask([MEASURES].[id_display_rule], 1) -- если пользователь выбрал "выделение цветом..." ,CASE [Группы просрочки ОД FIFO].[Группы].CURRENTMEMBER.Level.Name WHEN "Группа просрочки" THEN [Группы просрочки ОД FIFO].[Группы].CURRENTMEMBER.Properties("Цвет фона", typed) WHEN "Группа 0-30-60-90-90плюс" THEN [Группы просрочки ОД FIFO].[Группы].CURRENTMEMBER.Properties("Цвет фона 0-30-60-90-90плюс", typed) END ,NULL ) ,VISIBLE = 0; -- в данной служебной мере запоминаем foreColor для мер CREATE MEMBER CURRENTCUBE.[MEASURES].[foreColor] AS IIF( MY_ASSP.BitInBinaryMask([MEASURES].[id_display_rule], 1) -- если пользователь выбрал "выделение цветом..." ,CASE [Группы просрочки ОД FIFO].[Группы].CURRENTMEMBER.Level.Name WHEN "Группа просрочки" THEN [Группы просрочки ОД FIFO].[Группы].CURRENTMEMBER.Properties("Цвет шрифта", typed) WHEN "Группа 0-30-60-90-90плюс" THEN [Группы просрочки ОД FIFO].[Группы].CURRENTMEMBER.Properties("Цвет шрифта 0-30-60-90-90плюс", typed) END ,NULL ) ,VISIBLE = 0; -- в данной служебной мере запоминаем формат отображения для "денежных" мер. -- Формат отображения пользователь выбирает из вспомогательного измерения [_Правила оформления] CREATE MEMBER CURRENTCUBE.[MEASURES].[selected_format_string] AS CASE WHEN MY_ASSP.BitInBinaryMask([MEASURES].[id_display_rule], 8) = true -- если пользователь выбрал округление до млн. THEN "#,#0,," + IIF(MY_ASSP.BitInBinaryMask([MEASURES].[id_display_rule], 64) = false, "" ,[_Валюты конверсии].[Валюта].CURRENTMEMBER.Properties("Аббревиатура миллиона", typed)+ [_Валюты конверсии].[Валюта].CURRENTMEMBER.Properties("Знак валюты", typed) ) WHEN MY_ASSP.BitInBinaryMask([MEASURES].[id_display_rule], 4) = true -- если пользователь выбрал округление до тыс. THEN "#,#0," + IIF(MY_ASSP.BitInBinaryMask([MEASURES].[id_display_rule], 64) = false, "" ,[_Валюты конверсии].[Валюта].CURRENTMEMBER.Properties("Аббревиатура тысячи", typed)+ [_Валюты конверсии].[Валюта].CURRENTMEMBER.Properties("Знак валюты", typed) ) ELSE "" END ,VISIBLE = 0; /* -- вот так выглядела бы наша мера, если бы мы явно определяли её оформление: CREATE MEMBER CURRENTCUBE.[MEASURES].[Остаток просроченного ОД] AS [MEASURES].[Остаток_просроченного_ОД] / [MEASURES].[Курс_валюты] ,FORMAT_STRING = "Currency" ,ASSOCIATED_MEASURE_GROUP = 'Показатели' ,DISPLAY_FOLDER = 'Остатки\Балансовые' ,BACK_COLOR = CASE [Группы просрочки ОД FIFO].[Группы].CURRENTMEMBER WHEN [Группы просрочки ОД FIFO].[Группы].&[2] THEN RGB(0,100,0) -- "1...7" 25600 WHEN [Группы просрочки ОД FIFO].[Группы].&[3] THEN RGB(60,179,113) -- "8...30" 7451452 WHEN [Группы просрочки ОД FIFO].[Группы].&[4] THEN RGB(146,208,80) -- "31...60" 5296274 WHEN [Группы просрочки ОД FIFO].[Группы].&[5] THEN RGB(255,255,0) -- "61...90" 65535 WHEN [Группы просрочки ОД FIFO].[Группы].&[6] THEN RGB(255,165,0) -- "91...120" 42495 WHEN [Группы просрочки ОД FIFO].[Группы].&[7] THEN RGB(255,99,71) -- "121...150" 4678655 WHEN [Группы просрочки ОД FIFO].[Группы].&[8] THEN RGB(255,0,0) -- "151...180" 255 WHEN [Группы просрочки ОД FIFO].[Группы].&[9] THEN RGB(165,0,33) -- "181...210" 2162853 WHEN [Группы просрочки ОД FIFO].[Группы].&[10] THEN RGB(102,51,0) -- "211...360" 13158 WHEN [Группы просрочки ОД FIFO].[Группы].&[11] THEN RGB(0,0,0) -- "360+" 0 END ,VISIBLE = 1; */ -- но мы опубликуем нашу меру более компактно: CREATE MEMBER CURRENTCUBE.[MEASURES].[Остаток просроченного ОД] AS [MEASURES].[Остаток_просроченного_ОД] / [MEASURES].[Курс_валюты] ,FORMAT_STRING = IIF([MEASURES].[selected_format_string]="", "Currency", [MEASURES].[selected_format_string]) ,ASSOCIATED_MEASURE_GROUP = 'Показатели' ,DISPLAY_FOLDER = 'Остатки\Балансовые' ,FORE_COLOR = [MEASURES].[foreColor] ,BACK_COLOR = [MEASURES].[backColor] ,VISIBLE = 1; --..... -- правила оформления применяем ко всем подобным мерам CREATE MEMBER CURRENTCUBE.[MEASURES].[Общий остаток ОД] AS [MEASURES].[Общий_остаток_ОД] / [MEASURES].[Курс_валюты] ,FORMAT_STRING = IIF([MEASURES].[selected_format_string]="", "Currency", [MEASURES].[selected_format_string]) ,ASSOCIATED_MEASURE_GROUP = 'Показатели' ,DISPLAY_FOLDER = 'Остатки\Балансовые' ,FORE_COLOR = [MEASURES].[foreColor] ,BACK_COLOR = [MEASURES].[backColor] ,VISIBLE = 1; --------------------------------------------------------------------------------------------------------------------------------- Развернём проект на сервере SSAS, спроцессим куб и построим нижеследующий отчет в клиентском приложении, поддерживающем серверные свойства BACK_COLOR, FORE_COLOR, FORMAT_STRING, например, в MS Excel:
Кстати, в MS Excel возможно переопределить форматирование ячеек: щелкнуть правой кнопкой мыши на сводной таблице, выбрать пункт "Параметры сводной таблицы…", на вкладке "Разметка и формат" отметить "Сохранять форматирование ячеек при обновлении", щелкнуть правой кнопкой мыши на ячейках сводной таблицы, выбрать пункт "Формат ячеек…" и установить желаемый формат. Теперь наш куб приобрел дополнительную функциональность и стал еще более аналитическим. Еще пример отчетов по просроченной задолженности
Скорее ступайте и сделайте / дополните свой куб данными примерами, постройте красивые содержательные отчёты, и Вы будете бэтменами в глазах Бизнес-пользователей!