Заголовок этой статьи — это краткое резюме того, что просил заказчик. Я в это время был в отпуске, а мой руководитель, не вдаваясь в детали, дал добро на реализацию. Хорошо отдохнув и выйдя на работу, я почувствовал себя в шкуре ёжика, который попал в туман в одном известном мультфильме. А ситуация требовала Волшебника изумрудного города.
Итак, всё по порядку:
Постановка
Заказчиком являлся отдел по борьбе с проблемной задолженностью. Для успешного решения своих задач им требовалось
- Отслеживать историю проблемного кредита: смотреть, когда он выходил на просрочку и как её гасил. Так же проверять, нет ли у заёмщика, вышедшего на просрочку, других кредитов.
- Получать информацию по кредитному портфелю, отслеживать тенденции и изменения портфеля за любой произвольный период.
- Возможность “проваливаться” от агрегированных показателей, таких как “Дата выдачи кредита”, “Регион выдачи”, “Просроченные кредиты” и т.п. к непосредственно к самим кредитным договорам.
- Отслеживать работу кредитных менеджеров: проверять, насколько успешные кредиты они выдают.
Заказчики видели решение непременно в виде Web-формы, это объяснялось тем, что отдел был головным, а его подотделы находились в различных городах нашей необъятной родины и все сотрудники должны были видеть одну и ту же картину, разграниченную по правам доступа.
Задача немного облегчалась тем, что каждодневный обзор кредитного портфеля нужен был только за последние три месяца. Более поздняя информация должна была быть на первое число каждого прошедшего месяца.
Помимо этого условия была еще одна проблема: под кредитом понимались все виды кредитования, в том числе и кредитные карты, которые хранились отдельно и отличались в разрезе измерений.
Решение
На момент реализации имелось хранилище на БД Oracle, в котором хранилась вся информация по картам и кредитам, в том виде, в котором она туда поступала из оперативных источников. Информация поступала с задержкой в 1 день. В среднем каждый день появлялось 3 миллиона новых записей, часть из которых были кредиты, часть карты.
Для решения данной задачи была выбрана следующий алгоритм:
- Строилась витрина данных, которая объединяла кредиты и карты. Витрина строилась по схеме “Звезда”.
- Ежедневно в неё с помощью планировщика заданий «подливался» вчерашний день.
- Бал создан OLAP куб, для расчета агрегатов.
- По окончанию обновления витрины запускалось обновление OLAP куба.
- OLAP выдавал данные в Web-форму через OLAP Viewer.
Помимо реализации алгоритма надо было свести к минимуму, либо устранить все риски и препятствия, которые могут свести на нет работу данной системы.
Подготовительная работа:
- В витрине для объединения кредитов и карт был создан общий ключ, который однозначно идентифицировал как кредит, так и карту. Ключ хранился в таблице, каждая строка которой однозначно идентифицировала либо кредит, либо карту.
- К ежедневной заливке был добавлен механизм отслеживания изменений в оперативных таблицах не только вчерашней даты, но и всех данных за произвольный интервал в прошлом, начиная от вчерашнего дня. Это было сделано на случай каких-либо изменений, либо ошибок в базе “Вчерашнего дня”. Ситуация, когда информация по кредитам и картам оказывается в базе с некоторой задержкой, имела место, и с этим приходилось бороться. Проверялись последние 15 дней от вчерашнего дня.
- Куб создавался под возможности Web приложения. Кредитов и карт в сумме было около 3 миллионов и отобразить их в браузере все разом было либо очень сложно, либо невозможно. Для решения этой проблемы, помимо стандартного разделения кредитов и карт по дате открытия, региону привязки, выдавшему менеджеру и т.п. были придуманы дополнительные измерения. В описываемом случае были добавлены градации кредитов по Сумме, по Сумме просрочки, по количеству дней просрочки, по признаку – был ли хоть раз на просрочке. Так же по дате открытия и закрытия кредита были построены иерархии типа год-квартал-месяц-день. В конечном результате все кредиты были объединены в группы, самая большая из которых была не более 10 000 строк. Заказчика интересовала только просрочка, поэтому для кредитов, которые погашались в срок, дополнительные измерения не придумывались. Доступ к ним ограничивался Viewer’ом, и мог быть в любое время открыт. В текущей версии пользователь видел по ним только агрегированную по стандартным измерениям информацию. Но часть из таких кредитов всё же можно было увидеть – это были кредиты должника, у которого просрочен всего один кредит, а взято несколько.
- Обновление куба должно было начаться сразу же после обновления витрины. Для этого создан сервис, который проверял статус планировщика задач Oracle. К нему так же была написана утилита, с помощью которой можно было экстренно стартовать/прерывать обновление куба или наблюдать за текущим статусом.
- Необходимо было найти OLAP Viewer, который бы смог обеспечить весь требуемый функционал, нормальный вид и гибкость настроек. Эта задача была не простой: т.к. многие из них отличаются крайней не дружелюбностью к пользователям, и работать с ними не очень удобно. Даже с тем, который был найден, пришлось помучиться.
Непосредственно реализация опиралась на знания и технологии, которыми я владел на тот момент и политику компании в области программного обеспечения. Поэтому, если вдруг в реализации вам покажется, что я надувал шарик в форме котенка, то прошу быть снисходительными, не всё зависело от меня.
Реализация:
Система была развернута на Windows Server 2008 64-бит, Web-форма – на IIS, технология ASP.NET.
- Витрина данных — БД Oracle
- Планировщик заданий – БД Oracle
- OLAP куб – Microsoft Analysis Services
- Отслеживание обновлений витрины и запуск обновления для куба — Windows Service
- OLAP Viewer – DevExpres AspxPivotGrid
Немного о технических тонкостях и хитростях, которые были применены при разработке этой системы.
Технические хитрости:
- 1. Для корректной работы 32 битного и 64 битного провайдеров Oracle под Windows:
- Скачать 32-битный и 64-битный драйвера Oracle c официального сайта.
- Поставить в отдельную папку Client32 Runtime.
- Перезагрузить компьютер.
- Поставить в отдельную папку Client64 Runtime.
- В реестре Windows в раздел KEY_LOCAL_MACHINESOFTWAREWow6432NodeORACLE добавить значение TNS_ADMIN и задать ему путь к папке для Clinet32 — …networkadmin
- Перезагрузить компьютер
- При разграничении доступа в Кубе Analysis Services на закладке Cubes надо выставить Read, далее выбрать значения измерения в кубе, выбрать среди них те, к которым роль имеет доступ, и, переключившись на закладку Advanced, включить галочку Enable Visual Totals, она по умолчанию отключена. Если её не включить, то в итогах по этому измерению отобразится итог не только по тем измерениям, к котором роль имела доступ, но так же и те значения измерений к которым доступа не было.
- Для больших измерений в ASPxPivotGrid надо отключить сортировку значений по умолчанию.
О важном!
В данной статье я хотел показать, как можно решать задачи, связанные большими объёмами данных, используя доступные и давно существующие технологии. До внедрения OLAP, моё руководство, в поисках “золотой пули” рассматривало множество коробочных решений, как от крупных производителей, так и от мелких компаний. На проверку часто оказывалось, что поддержка решений из коробок требовала очень много усилий и финансовых затрат. В некоторых случаях коробочный продукт просто не мог сделать то, что от него хотели в силу своей архитектуры. Возможно компания, где я работаю, искала не там и не то и есть такая “золотая” или хотя бы “серебряная” пули. Но что было, то было. С другой стороны данная статья, возможно, кого-то остановит от изобретения “велосипедов” в виде построения дополнительных агрегированных таблиц, каких-то сложных архитектур и громоздких сайтов.
Но сколько людей столько и мнений и на этот счет мне нравится выдуманная история с изобретением шариковой ручки:
NASA потратил миллионы долларов на изобретение ручки, которой можно писать в невесомости.
Русские просто писали карандашом.
Но где сейчас карандаш и где ручка. Поэтому, любое решение, даже очень сложное и громоздкое может найти своего пользователя, точно так же как и простое.
Спасибо всем, кто смог прочитать всё, о чём я писал выше!
Автор: alex_29
