Сетевой журнал: галерея ИТ-проектов


РДТеХ: хранилище данных на основе технологий Oracle



задача:
Хранилище для банковских данных
другие решения:
“БОРЛАС”: соединяя эффективность аналитической системы и удобства технологий





 

РДТеХ, первая российская компания, получившая статус официального дистрибьютора корпорации Oracle, работает на российском рынке информационных технологий с 1992 года. Среди ее заказчиков – государственные структуры, банки, нефтяные и газовые компании, промышленные и торговые предприятия, операторы связи, научные организации.

Главным направлением деятельности РДТеХ является разработка на заказ информационных систем на основе технологий Oracle. Компания предлагает заказчикам как создание систем ”под ключ”, так и помощь на любом из этапов разработки. Проект, о котором пойдет речь, полностью реализован специалистами РДТеХ. Его задачей было построение хранилища данных для формирования аналитической отчетности по кредитным операциям, а заказчиком выступал акционерный коммерческий банк РОСБАНК.

Проект, выполненный в 2001 году, стал первым для РДТеХ и, возможно, вообще первым в нашей стране проектом хранилища данных на основе новых технологий Oracle: Warehouse Builder и Discoverer. Сейчас на счету компании уже немало хранилищ данных, в том числе и для более крупных банков, но проект для РОСБАНКа занимает в этом ряду особое место как пионерная разработка. Материал по проекту подготовил Виктор Сусойкин, который был его руководителем.

Заказчик и его проблемы
РОСБАНК – один из десяти крупнейших банков России; он имеет разветвленную сеть отделений и филиалов: на момент подготовки этого материала действовали пять его отделений в Москве, филиалы в Дудинке, Красногорске Московской области, Краснодаре, Красноярске, Новосибирске, Норильске, Омске, Перми, Томске, Туле, Улан-Удэ, Хабаровске и Южно-Сахалинске, а также представительства в Пекине и Женеве.

Для работы с документами, связанными с оформлением кредитных договоров банка с физическими и юридическими лицами, в банке используется АБС собственной разработки на основе СУБД Btrieve (ныне Pervasive), работающей под управлением Novell Netware. Хотя это решение явным образом устарело, оно в достаточной мере удовлетворяет РОСБАНК. Можно заметить, что в начале 90-х годов, когда создавались многие отечественные банки, Btrieve была очень популярна и, в частности, широко применялась для создания АБС. Построенные в то время на ее базе банковские системы до сих пор работают примерно в 45% банков нашей страны.

Однако когда РОСБАНК поставил задачу анализа кредитных операций, обнаружилось, что для ее решения Pervasive абсолютно не приспособлена. Это файловая СУБД, т. е. база данных представляет собой совокупность независимых таблиц, хранящихся как файлы. Целостность и непротиворечивость данных в разных таблицах никак не контролируются, поэтому вполне возможна, например, та ситуация, что дополнение к кредитному договору ссылается на не существующий в соответствующем справочнике документ. Обращение к базе Pervasive с помощью языка SQL возможно, но только через интерфейс ODBC, то есть с определенными ограничениями.

Отдельную проблему представляла сложившаяся по историческим причинам разобщенность информации. Первоначально РОСБАНК работал только с юридическими лицами, позже начал обслуживать также физических лиц. Из-за этого в АБС существуют два отдельных модуля и, соответственно, две базы данных по юридическим и по физическим лицам. Кроме того, АБС банка охватывает не всю информацию, нужную аналитикам. Например, ставки казначейства хранятся не в общей базе, а как отдельный файл Excel; также в виде файлов Excel передается в центральный офис информация из филиалов, где используются свои банковские системы.

Чтобы сводить воедино и анализировать всю эту разнородную информацию, сотрудники Департамента кредитных операций банка применяли чрезвычайно сложную и трудоемкую процедуру. Сначала данные с помощью специальной программы, написанной на языке Clarion, вручную выгружались из Pervasive в Excel. Из Excel они переносились в Access; туда же передавались и данные, полученные непосредственно в виде файлов Excel. В Access производились необходимые вычисления, строились запросы, после чего полученные результаты копировались обратно в Excel, где формировались и печатались отчеты. Подготовка аналитического отчета по такой схеме занимала целый рабочий день, а иногда и больше, причем при условии, что все операции проходят без ошибок; в случае же сбоя операцию приходилось повторять сначала, так что процедура еще затягивалась. Отметим кроме того, что квалифицированные сотрудники банка (аналитики, методологи) тратили свое рабочее время на технические операции, которые вообще не должны были им поручаться. Понятно, что подобная ”система” подготовки отчетности нуждалась в радикальном усовершенствовании.

На входе и на выходе
Поскольку СУБД Pervasive вполне подходила банку в качестве основы для АБС, но абсолютно не годилась для аналитических задач, естественным решением стало построение отдельного хранилища данных, где информация представлена в удобном для анализа виде. Для такого хранилища, очевидно, необходимо обеспечить, во-первых, получение данных из различных источников – действующей АБС, отчетности филиалов и некоторых других – и преобразование их к нужному виду, а во-вторых, извлечение этих данных конечными пользователями.

Двум названным задачам соответствуют две основные функциональные подсистемы хранилища – подсистема загрузки и подсистема аналитической отчетности. В качестве инструментальных средств для их разработки были выбраны соответственно Oracle Warehouse Builder и Oracle Discoverer, которые в то время только что появились. Применение этих систем обеспечило ряд преимуществ, которых мы коснемся ниже.

Хранилище работает на платформе Oracle8i под управлением ОС Solaris.

Подсистема загрузки
Итак, источниками данных для разработанного хранилища служат кредитный модуль АБС (БД по юридическим лицам и БД по физическим лицам), реализованный на базе СУБД Pervasive (платформа Novell Netware), а также файлы Excel, поступающие из филиалов банка по электронной почте и из других источников (например, ставки казначейства).

Чтобы обеспечить заполнение хранилища информацией, пригодной для анализа, требовалось:
• обеспечить связь между СУБД Pervasive и Oracle через интерфейс ODBC;
• разработать для хранилища схему данных и процедуры загрузки и очистки данных из имеющихся источников;
• организовать запуск процедуры загрузки в автоматическом режиме.
Принципиальных проблем в том, чтобы технически наладить обмен данными между Pervasive и Oracle, не было, хотя потребовалось выполнить довольно трудоемкую рутинную процедуру. Для организации связи Oracle с файловой СУБД через интерфейс ODBC необходимо предварительно описать структуру всех ее файлов в так называемых файлах словаря, или файлах определения данных (ddf – data definition files). В АБС РОСБАНКа используется около сотни файлов данных, и для всех них нужно было вручную ввести описания в ddf-файлы.

По окончании этой работы специалисты РДТеХ сконфигурировали так называемые гетерогенные службы сервера Oracle, которые обеспечивают работу через интерфейс ODBC, позволяя связывать между собой совершенно разные СУБД и платформы (в данном случае соответственно Oracle и Pervasive, Solaris и Novell).

Для проектирования и генерации схемы данных и процессов загрузки информации в хранилище и ее очистки разработчики использовали Oracle Warehouse Builder – специализированный инструмент для построения хранилищ данных. В соответствии с требованиями заказчика схема хранилища была спроектирована так, чтобы обеспечивать накопление хронологических данных, – это необходимо для сопоставительного анализа банковских показателей во времени. Немаловажным достоинством Warehouse Builder является полное документирование разрабатываемой системы, что значительно упрощает ее сопровождение: вся необходимая информация записывается в репозитарий Warehouse Builder, и при необходимости ее можно представить в виде отчета.

Построение процедур загрузки – достаточно сложная задача. Поскольку многие необходимые банку аналитические отчеты должны строиться как по головному отделению, так и по филиалам (а также требуется консолидированный отчет), данные в хранилище должны быть очищены, согласованы и консолидированы. Эта задача, вообще говоря, стандартно присутствует в проектах, связанных с построением хранилищ данных, и ее целесообразно решать во время загрузки данных. Таким образом, при загрузке приходится заботиться о массе казалось бы мелких деталей, которые тем не менее необходимо учитывать. Например, в Pervasive, работающей под управлением Novell Netware, русские буквы представлены в кодировке CP866, в Excel – в кодировке Windows-1251, а в Oracle (точнее, в Solaris) используется кодировка ISO 8859-5, так что требуется предусмотреть конвертирование данных; в Pervasive дата и время – два разных поля, а в Oracle – одно, и при загрузке их следует объединять; в Pervasive нет понятия NULL (значение поля не определено), и если дата неизвестна, в поле заносят арифметический нуль, а Oracle воспринимает это значение как некорректное, поэтому нужно заменять нуль на NULL. Использование Warehouse Builder позволило существенно сократить время разработки за счет автоматизации многих рутинных процедур и унифицировать все процессы загрузки.

Для автоматизации загрузки данных из АБС в хранилище достаточно было задать ее расписание в среде Oracle Enterprise Manager (OEM), поскольку Oracle Warehouse Builder автоматически генерирует TCL-код и устанавливает его в библиотеке заданий OEM. Загрузка выполняется в ночное время по будним дням (в выходные необходимости в ней нет, поскольку новые данные в АБС не появляются). Загрузка Excel-файлов, которые приходят по электронной почте из филиалов, инициируется вручную при их получении. По результатам каждой загрузки формируется протокол, позволяющий администраторам контролировать процесс загрузки, отслеживать динамику использования дискового пространства и т.п.

Витрины и оптимизация
Информация, с которой работает аналитик, – это по большей части не первичные данные, содержащиеся в АБС и непосредственно переданные оттуда в хранилище данных, а результат определенных, часто достаточно сложных вычислений над ними. Поэтому стандартная методология предполагает, что конечные пользователи будут получать доступ не непосредственно к хранилищу, а к так называемым витринам данных, где информация представляется в виде, удобном для анализа (с точки зрения аналитических средств доступа, в данном случае Oracle Discoverer). Для рассматриваемого хранилища были построены витрины по кредитным договорам и письмам (заявкам). При формировании витрины на основе данных хранилища происходит их агрегирование, а также расчет дополнительных (производных) показателей кредитного договора.

Техническое задание ограничивало время формирования отчетов (включая и выполнение переходов по иерархии для просмотра исходных данных) 15–20 секундами. Это удалось обеспечить, во-первых, за счет тонкой настройки сервера Oracle и оптимизации генерируемых системой запросов, а во-вторых, за счет использования витрин данных.

Подсистема отчетности
Подсистема аналитической отчетности должна была включать прототипы стандартных отчетов и специализированную среду, в которой конечные пользователи могли бы самостоятельно строить нерегламентированные (ad hoc) запросы и отчеты, анализировать информацию в различных срезах и в динамике. В качестве инструмента для разработки и того и другого было использовано программное средство Oracle Discoverer.

Встроенные аналитические возможности Discoverer (анализ данных с переходами по иерархии, вращение элементов – ”осей куба”, постраничная развертка, итоги и подитоги, проценты, графика, Top-N анализ и др.) – позволили подготовить ряд достаточно сложных аналитических отчетов: ”Справка по крупным заемщикам”, ”Отчет о структуре портфеля по уровню риска вложений”, ”Отчет по срочности портфеля”, ”Отчет по отраслевой структуре портфеля”, ”Оборотно-сальдовая ведомость по портфелю”, ”Справка по заполнению лимитов” и др.

Так, например, в отчет ”Справка по крупным заемщикам” в качестве параметров можно передавать даты начала и конца временного периода и количество самых крупных (по величине задолженности) заемщиков, информация о которых выводится на экран в порядке убывания величины задолженности (т.н. ранжирование). Данные группируются отдельно по физическим и юридическим лицам. По столбцам выводится не только задолженность по клиенту, но и ее доля от портфеля, а также доля нарастающим итогом, и так по каждому месяцу из заданного временного периода. В крайнем столбце вычисляется подитог – среднее за полугодие. Предусмотрена возможность переходов по иерархии с последовательным агрегированием и детализацией данных. С уровня клиента можно ”спуститься” на уровень валюты договора (детализировать информацию), потом с уровня валюты – на уровень договора (еще ниже по иерархии) и т. д. Затем можно вновь ”свернуть иерархию” и подняться до уровня клиента. Таким образом, пользователь ”скользит” по данным, изучая нужные уровни и срезы.

Однако в качестве главной задачи при разработке системы отчетности рассматривалось не построение готовых прототипов отчетов, а создание удобного инструмента, с помощью которого сотрудники департамента кредитных операций (аналитики, методологи, кураторы кредитных клиентов и др.) могли бы самостоятельно, не прибегая к услугам программистов, формировать нужные им отчеты, а также выбирать и представлять данные в нужном им виде (табличном, кросс-табличном, графическом, с постраничной разверткой и пр.).

Система аудита хранилища данных обеспечивает протоколирование действий пользователей и сбор статистики использования отчетов. При этом преследуется двоякая цель – во-первых, осуществляется просто административный контроль, а во-вторых, выясняется, какого рода отчеты и запросы наиболее востребованы, для последующей оптимизации системы.

Результаты
Что же получили аналитики банка в результате внедрения хранилища данных? Во-первых, у них появилась возможность самостоятельно извлекать нужную им аналитическую информацию. Во-вторых, им стало значительно проще готовить отчеты уже за счет того, что отпала необходимость выполнять многочасовые и многошаговые процедуры передачи данных из Pervasive в Excel, из Excel в Access, из Access обратно в Excel – достаточно открыть нужный отчет в Oracle Discoverer. В-третьих, благодаря тому же Oracle Discoverer значительно расширился спектр возможностей, доступных при подготовке отчетов.

Благодаря хранилищу в отчетах теперь отражается текущее состояние бизнеса, в то время как при использовании старой системы добиться этого не удавалось. Кроме того, отчеты представляют информацию в том виде, в каком она нужна аналитику, и их можно модифицировать, создавать на их основе дополнительные запросы и т.д.

Администратор хранилища получил качественно документированную систему, а благодаря наличию в Oracle Warehouse Builder визуального языка проектирования он может даже без знания SQL и PL/SQL быстро и легко вносить в эту систему изменения. Так, после сдачи системы, когда она уже работала в режиме промышленной эксплуатации, у заказчика возникла необходимость внести изменения в процедуры загрузки и в структуру некоторых таблиц хранилища в связи с появлением второго куратора. Сотрудники ДИТ банка без труда проделали это самостоятельно (без обращения в РДТеХ), просто ”нарисовав” дополнительные стрелочки в визуальной среде проектирования Warehouse Builder.

 
сетевой форум
архив
подписка на журнал
о сетевом

подписка на новости

подписка на журнал

архив номеров

архив новостей