Заголовок этой статьи — это краткое резюме того, что просил заказчик. Я в это время был в отпуске, а мой руководитель, не вдаваясь в детали, дал добро на реализацию. Хорошо отдохнув и выйдя на работу, я почувствовал себя в шкуре ёжика, который попал в туман в одном известном мультфильме. А ситуация требовала Волшебника изумрудного города.
Итак, всё по порядку:
Заказчиком являлся отдел по борьбе с проблемной задолженностью. Для успешного решения своих задач им требовалось
Заказчики видели решение непременно в виде Web-формы, это объяснялось тем, что отдел был головным, а его подотделы находились в различных городах нашей необъятной родины и все сотрудники должны были видеть одну и ту же картину, разграниченную по правам доступа.
Задача немного облегчалась тем, что каждо��невный обзор кредитного портфеля нужен был только за последние три месяца. Более поздняя информация должна была быть на первое число каждого прошедшего месяца.
Помимо этого условия была еще одна проблема: под кредитом понимались все виды кредитования, в том числе и кредитные карты, которые хранились отдельно и отличались в разрезе измерений.
На момент реализации имелось хранилище на БД Oracle, в котором хранилась вся информация по картам и кредитам, в том виде, в котором она туда поступала из оперативных источников. Информация поступала с задержкой в 1 день. В среднем каждый день появлялось 3 миллиона новых записей, часть из которых были кредиты, часть карты.
Для решения данной задачи была выбрана следующий алгоритм:
Помимо реализации алгоритма надо было свести к минимуму, либо устранить все риски и препятствия, которые могут свести на нет работу данной системы.
Непосредственно реализация опиралась на знания и технологии, которыми я владел на тот момент и политику компании в области программного обеспечения. Поэтому, если вдруг в реализации вам покажется, что я надувал шарик в форме котенка, то прошу быть снисходительными, не всё зависело от меня.
Система была развернута на Windows Server 2008 64-бит, Web-форма – на IIS, технология ASP.NET.
Немного о технических тонкостях и хитростях, которые были применены при разработке этой системы.
В данной статье я хотел показать, как можно решать задачи, связанные большими объёмами данных, используя доступные и давно существующие технологии. До внедрения OLAP, моё руководство, в поисках “золотой пули” рассматривало множество коробочных решений, как от крупных производителей, так и от мелких компаний. На проверку часто оказывалось, что поддержка решений из коробок требовала очень много усилий и финансовых затрат. В некоторых случаях коробочный продукт просто не мог сделать то, что от него хотели в силу своей архитектуры. Возможно компания, где я работаю, искала не там и не то и есть такая “золотая” или хотя бы “серебряная” пули. Но что было, то было. С другой стороны данная статья, возможно, кого-то остановит от изобретения “велосипедов” в виде построения дополнительных агрегированных таблиц, каких-то сложных архитектур и громоздких сайтов.
Но сколько людей столько и мнений и на этот счет мне нравится выдуманная история с изобретением шариковой ручки:
NASA потратил миллионы долларов на изобретение ручки, которой можно писать в невесомости.
Русские просто писали карандашом.
Но где сейчас карандаш и где ручка. Поэтому, любое решение, даже очень сложное и громоздкое может найти своего пользователя, точно так же как и простое.
Спасибо всем, кто смог прочитать всё, о чём я писал выше!
Итак, всё по порядку:
Постановка
Заказчиком являлся отдел по борьбе с проблемной задолженностью. Для успешного решения своих задач им требовалось
- Отслеживать историю проблемного кредита: смотреть, когда он выходил на просрочку и как её гасил. Так же проверять, нет ли у заёмщика, вышедшего на просрочку, других кредитов.
- Получать информацию по кредитному портфелю, отслеживать тенденции и изменения портфеля за любой произвольный период.
- Возможность “проваливаться” от агрегированных показателей, таких как “Дата выдачи кредита”, “Регион выдачи”, “Просроченные кредиты” и т.п. к непосредственно к самим кредитным договорам.
- Отслеживать работу кредитных менеджеров: проверять, насколько успешные кредиты они выдают.
Заказчики видели решение непременно в виде 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_MACHINE\SOFTWARE\Wow6432Node\ORACLE добавить значение TNS_ADMIN и задать ему путь к папке для Clinet32 — …\network\admin
- Перезагрузить компьютер
- При разграничении доступа в Кубе Analysis Services на закладке Cubes надо выставить Read, далее выбрать значения измерения в кубе, выбрать среди них те, к которым роль имеет доступ, и, переключившись на закладку Advanced, включить галочку Enable Visual Totals, она по умолчанию отключена. Если её не включить, то в итогах по этому измерению отобразится итог не только по тем измерениям, к котором роль имела доступ, но так же и те значения измерений к которым доступа не было.
- Для больших измерений в ASPxPivotGrid надо отключить сортировку значений по умолчанию.
О важном!
В данной статье я хотел показать, как можно решать задачи, связанные большими объёмами данных, используя доступные и давно существующие технологии. До внедрения OLAP, моё руководство, в поисках “золотой пули” рассматривало множество коробочных решений, как от крупных производителей, так и от мелких компаний. На проверку часто оказывалось, что поддержка решений из коробок требовала очень много усилий и финансовых затрат. В некоторых случаях коробочный продукт просто не мог сделать то, что от него хотели в силу своей архитектуры. Возможно компания, где я работаю, искала не там и не то и есть такая “золотая” или хотя бы “серебряная” пули. Но что было, то было. С другой стороны данная статья, возможно, кого-то остановит от изобретения “велосипедов” в виде построения дополнительных агрегированных таблиц, каких-то сложных архитектур и громоздких сайтов.
Но сколько людей столько и мнений и на этот счет мне нравится выдуманная история с изобретением шариковой ручки:
NASA потратил миллионы долларов на изобретение ручки, которой можно писать в невесомости.
Русские просто писали карандашом.
Но где сейчас карандаш и где ручка. Поэтому, любое решение, даже очень сложное и громоздкое может найти своего пользователя, точно так же как и простое.
Спасибо всем, кто смог прочитать всё, о чём я писал выше!
