Использование технологий ETL и OLAP в анализе финансовых организаций

Опубликовано: 
Проблеми програмування, 2008. – №1

ВВЕДЕНИЕ

Потребность в постоянном совершенствовании бизнес-процессов, повышения прибыльности, снижение себестоимости, расширение рынков сбыта предприятий невозможно без анализа. Как правило, бизнес анализ необходим на всех стадиях жизненного цикла продукта и во всех подразделениях предприятия. Что в свою очередь привод к потребности обработки громадных объемов информации, ее структурирование, написание интерфейсов доступа к ней, обеспечения безопасности, целостности и других проблем связанных с анализом. Все это требует высокопрофессиональных сотрудников отдела IT, что в свою очередь является дорогим «удовольствием».

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

1.      Клиентская база, которая в свою очередь делится на

a.      Связанных лиц

b.      Несвязанных лиц

2.      Баланс банка в разрезе балансовых счетов

3.      Кредитный портфель

4.      Депозитный портфель

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

Но OLAPявляется законченной формой хранения данных, которые извлекаются из OLTPБД. Для преобразования данных в нужные структуры используются, так называемые ETLинструменты.

Также для доступа к данным OLAPнужны «красивые» средства, нежели APIили MDX. Такими средствами являются или программы обработки электронных таблиц, которые обычно включают клиента OLAPБД или широко используются системы построения отчетов и клиенты pivot-таблиц.

Три технологии ETL, OLAPи генераторы отчетов совместно работают на BI.

В этом докладе, приведена концепция построения типовых OLAPрешения и пример предоставления базового отчета для анализа баланса банка.

ГЛОССАРИЙ

БД – База данных

OLAP(Onlineanalysisprocessing) –Аналитическая обработка в реальном времени

OLTP(Onlinetransactionprocessing) – Обработка транзакций в реальному времени

ETL(ExtractTransformLoad) – Извлечь Преобразовать Загрузить

MDX (Multidimensional Expressions) – Многомерныйязыкзапросов

BI (Business Intelligent) – Бизнесанализ

VSFSS – Visual studio for sql server

ПО – Программное обеспечение

SQL(Structuralquerylanguage) – Структурированный язык запросов

DDL(Datadefinitionlanguage) – Язык описания структуры данных

НБУ – Национальный банк Украины

ОДБ – Операционный день банка

1 ПОСТАНОВКА ЗАДАЧИ

               В любом банке более 80% отчетов и нормативов строится на основе бухгалтерского баланса, соответственно при проверке нормативов и отчетности аналитику и управленцу критично необходимо «видеть» баланс в удобной структурированной форме.

Использования готовых западных OLAP-решений сомнительна и даже невозможна. Это связанно с громадным количеством отчетности, требуемых контролирующими органами в Украине, как государственными так и частными. В связи с этим, отечественные разработчики идут по пути наименьшего сопротивления и создают продукты, которые учитывают множество избыточной информации, необходимой для построения отчетности. Поэтому множество тонких моментов, важных для функционировании банка, теряются в громадных объемах информации.

Выходом из такой ситуации является ведения двух БД, одна содержит всю необходимую информацию для построения отчетности, как правило, это OLTPБД, вторая содержит «чистые данные», позволяющие адекватно оценивать качество функционирования банка, как правило, это OLAPБД. Так что приходиться заново преодолевать путь построения OLAPсистем.

Краткое изложения основных этапов построения OLAPрешений продемонстрировано на задаче построения пользовательского разреза баланса. Эта задача поставлена в отделе аналитики одного крупного банка. Также продемонстрированы результаты прогнозирования состояния банка.

               Детали задачи. Количество балансовых счетов бухгалтерского анализа состоит из порядка 1000 счетов, которые характеризируются так называемыми активностью и пассивностью. Эти счета сгурпирированы, но каждый банк имеет практику выстраивать свою иерархию для отображения «видения» управляющего персонала. Каждый балансовый счет ниже по иерархии содержит лицевые счета, которые составляют сумму балансового счета.

               Задача стоит так: построить разрез структурированного баланса, по дням с отображением остатков по категориям.

Эта задача демонстрирует основных подход и предположения используемые при построении системы BIна предприятии.

2 ЭТАПЫ ПОСТРОЕНИЯ OLAP РЕШЕНИЯ

               Этап построения законченного аналитического приложения можно разбить на 3 базовых этапа:

1.      Создание схемы OLAPБД

2.      Загрузка и преобразование (опционально) данных из OLTPБД в OLAPБД

3.      Построение необходимого куба(кубов), метрик и измерений.

4.      Построение отображения данных пользователю

Этап 1. Создание схемы OLAPБД. На этом этапе строиться реляционная схема OLAPБД. Как правило, она состоит из:

1.      Таблиц фактов – содержит в себе факты, например остаток на счете, объем продаж, число привлеченных клиентов.

2.      Таблиц измерений – содержит в себе возможные разрезы таблицы фактов, например дата, менеджеры, типы продукции.

3.      Отношений между таблицами

Более детальную информацию о возможных схема OLAPБД можно найти в [1,2]

Этап 2. Загрузка и преобразование данных из OLTPБД в OLAP. Основное различи OLAPи OLTP– это степень нормализации данных в БД. Как правило, OLTPБД имеют высокую степень нормализации, а OLAPглубоко денормализированы для повышения быстродействия операций выборки. Ввиду этого различия, чаще всего необходимы преобразования данных из OLTPв OLAP.

В реальных системах введется, как минимум, ежедневное обновление аналитических БД. Для автоматизации актуализации данных в OLAPБД используется комплекс программ под общим названием ETL, он в буквальном смысле означает:

1.      Извлечь, т.е. получить данные из указанных таблиц OLTPБД.

2.      Преобразовать, т.е. преобразовать схемы OLTPБД в схему OLAPБД

3.      Загрузить, т.е. непосредственно загрузить данные в OLAPБД

Этап 3. Построение куба, метрик и измерений. Над реляционной схемой OLAPБД строятся куб(ы).

Куб – концепт, состоящий из множества метрик (measures) (таблиц фактов) и измерений (dimensions) (таблиц измерений).

Ключевыми составляющими являются

1.      Метрики – состоять из данных содержавшихся в таблице фактов

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

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

Этап 4. Построение отображения данных пользователю. Этот этап отвечает за создание удобного и функционального интерфейса для пользователя. Интерфейс должен быть в состоянии:

1.      Подключиться к соответствующей службе OLAP

2.      Иметь понятный и удобный интерфейс

3.      Обеспечить пользователю выбирать измерения по рядкам и столбцам, а также метрики, которые ему нужны для анализа.

4.      Возможность экспортировать данные в нужные пользователю форматы.

5.      Опционально, фильтровать данные

3 ПРАКТИЧЕСКАЯ РЕАЛИЗАЦИЯ OLAP-РЕШЕНИЯ

Для конкретной реализации выбран BI-инструментарий MSSQLServer2005, как один из удобных и гибких для построения решений такого типа. Наличие утилит и инструментов разработчика позволяет в полной степени покрыть процесс построения аналитического решения, концепция которого раскрыта в разделе 2.

Краткий обзор утилит и инструментов:

1.      Для создания реляционной схемы OLAPБД можно использовать любой инструмент, который генерирует на выходе SQLDDL. Специально для целей MSSQLServer2005 использует расширение Visio2003 (2007)

2.      Типы проектов в приложении к VisualStudio2005, которое идет вместе с поставкой MSSQLServer2005 (Этапы 2,3,4)

a.      Проект службы интеграции (Integrationservicesproject). Этот проект отвечает за выгрузку, преобразование и загрузку данных в OLAPБД (и не только), т.е. это ETL.

b.      Проект аналитической службы (Analysisservicesproject). В этом проекте создается куб с измерениями и мерами. Концепция построения состоит в выборе источника данных, определения отношений между таблицами,  создание дополнительных вычислимых полей, создания базового куба непосредственно, создание/редактирование/удаления измерений/мер и множественного другого функционала, который не является стандартным, и который мы не будем трогать в данном приложении.

c.      Проект службы отчетов (Reportserverproject(Reportserverprojectwizard)). В этом проекте создаются базовые виды отчетов для отображения конечному пользователю, включая pivot-таблицы, отчеты, графики, диаграммы и т.п.

На этап 1 создается схемы OLAPБД

               Схема БД отображена на рис. 1

                

Рис. 1 Схема БД

Где:

1.      DimBalance – Таблица, содержащая стандартную разбивку НБУ баланса.

2.      DimDayDate – Таблица, содержащая даты. Выделена отдельная таблица для возможностей поделить даты на месяцы, годы, дни недели и т.п.

3.      DimAlgArticle – Иерархия групп счетов, может быть уникальной для каждого банка, департамента и т.п.

4.      DimAlgItem – Балансовые счета групп

5.      FactAmounts – Таблица, содержащая факты, как остатки на счетах

Этап 2 для загрузки и преобразования данных из OLTPБД в OLAP. Этот этап опущен из соображений высокой сложности и необходимости привязки к конкретным ОДБ. 2.3

Этап 3 для построение необходимого куба(кубов), метрик и измерений

Общая схема нашего куба, отображена на рис. 2.

Рис. 2 Схема OLAP БД                Рис 3. Меры OLAPкуба

Где:

1.      Источник данных является БД (также возможны множественные источники данных). Здесь указана БД, именуемая Bank Analit.ds

2.      Отображение источника данных(Data Sources Views), является схема БД указанная на этапе, описанном в подразделе 3.1. Здесь именуемая, как Bank Analit.dsv

3.      Куб. Именуемый BankAnalit.cube. Куб содержит группу мер, см. рис 3:

a.      Сальдо – Остаток на балансовом счету в гривне

b.      Сальдо у номіналі – Остаток на балансовом счету в валюте счета

4.      Измерения, соответствуют назначению таблиц, см. подраздел 3.1:

a.      DimBalance.dim

b.      DimDayDate.dim

c.      DimAlgItem.dim

d.      DimAlgArticle.dim

Підпис:<br />
РИС 3. Меры OLAP куба

Этап 4. Построение отображения данных пользователя. Для отображения пользователю конечных результатов буду использованы два интерфейса:

1.      MSExcel2003, см. рис. 4

2.      MSSQLServer2005 Reports Services, браузерныйсм. Рис. 5

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

Для построения браузерных отчетов, приложения VSFSсодержат удобные и понятные визарды для быстрого построения и установки отчетов на сервер отчетов.

РИС. 4 Пример отображения данных пользователю в MSExcel2003

 

РИС. 5 Пример отображения данных пользователя в браузере

4 ПРОГНОЗИРОВАНИЕ СОСТОЯНИЯ БАНКА

               Результаты построения, изложенные выше, были использованы в работе [4] для построения прогнозов данных, основанных на макроекномических инвариантов выбранных показателей банка. Отметим, что построения прогноза и поиск инвариантов был бы чрезвычайно сложен или невозможен, без построения аналитических разрезов баланса банка.

               Основная идея построения прогноза заключалась в:

1.      Выборе показателя функционирования банка, состоящего из определенного аналитиком набора балансовых счетов.

2.      Построение динамики этого показателя

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

4.      Построении или выборе прогноза макроэкономического показателей

5.      Построения прогноза показателя банка

В работе [4] продемонстрированы, практические результаты этого подхода. Некоторые из них представлены ниже.

Для проверки практичности методологии проведен эксперимент на примере крупного Украинского банка. Как генеральные совокупности выбраны «средства на текущих счетах в долларах США» и «средства на текущих и депозитных счетах в гривне», а инвариантами служили такие макропоказатели НБУ, как «курс доллара США»  и «Параметр М0» соответственно. Генеральная выборка состояла из усредненных данных поквартально с 2001 года по 2006.  «Средства на текущих и депозитных счетах в гривне» и «Параметр М0» скорректированы на инфляцию за период анализая.

Корреляция составила (см. рис. 1 и 2):

1.    «Средства на текущих счетах в долларах США» и «курс доллара США» –  -0,774

2.    «Средства на текущих и депозитных счетах в гривне» и «Параметр М0» –  0,947

             

РИС. 1 - Анализ тренда для «Средства на текущих счетах в долларах США» и «курс доллара США»

 

РИС. 2 - Анализ тренда для «Средства на текущих и депозитных счетах в гривне» и «Параметр М0»

Исходя из  анализа тренда видно, что при отрицательной корреляции имеем зеркально отображенный тренд на графике «курс доллара США». Также тренды генеральных совокупностей и инвариантов повторяют тенденции один другого, что позволяет сделать вывод о правильности выбора инвариантов.

Результаты автокорреляционного анализа, приведенные на рис. 3 и 4, показывает соответствие генеральных  выборок и инвариантов.

Наличие инвариантов позволяет прогнозировать состояние макропоказателей (или использовать уже готовые прогнозы). Поскольку макропоказатели агрегируют большие объемы данных, таким образом сглаживая аномалии, прогнозы построенные на их базе являются более точными. В табл. 2. приведены результаты прогнозирования для 4-х выше указанных выборок.

       

РИС. 3 - Автокорреляционный анализ для «Средства на текущих счетах в долларах США» и «курс доллара США»

        

РИС. 4 - Автокорреляционный анализ для «Средства на текущих и депозитных счетах в гривне» и «Параметр М0»

                              ТАБЛИЦА 2. Прогнозные значения

Год

Квартал

Курс доллара США

Средства на текущих счетах в долларах США

Параметр М0

Средства на текущих и депозитных счетах в гривне

2007

I

505

10593418

28813,82289

398274185,2

 

 

 

 

 

 

Прогнозное

 

504,05

13875012

29592

443301392

MAPE

 

0,4501

31,54

6

10,85

MAD

 

2,3556

2 375 730

1514

32 158 800

 

Как видно из результатов прогнозирования, MAPE как минимум в 1,8 раза меньше для инвариантов, чем для генеральных совокупностей.

Эксперимент поставлен на обычном персональном компьютере, что обусловлено отсутствием ресурсоемкости задач. При анализе больших объемов данных можно воспользоваться распараллеливанием задач.

 

ВЫВОДЫ

               Построение OLAPрешений на базе MSSQLServer2005 достаточно хорошо структурировано, практично и востребовано. На практике такие решения создаются в тесном сотрудничестве со специалистами конкретной предметной области. Отметим что, такая деталь, как именование метрик и измерений играет важную роль в совершенствовании продукта и снижает усилия для перехода на новый продукт.

               В настоящее время решения на базе Web-технологий еще не совсем привычны, несмотря на их множественные преимущества как для разработчиков, так и для конечных пользователей. В этом случае рекомендуется использовать двойной интерфейс, к примеру, один – в MSExcel2003 (2007), второй – Web.

               Изначально корректно построенная схема OLAPБД экономит время используя VSFSS, так как в этот продукт включено множество визардов и интеллектуальных систем, позволяющих построить 80% OLAP-куба, основываясь на схеме БД.

               В докладе освещены актуальные проблемы финансового сектора Украины. Приведено решения одной из таких проблем, на примере задача прогнозирования данных бухгалтерского баланса. Показана практичность используемых методов и инструментов.

ЛИТЕРАТУРА

1.       http://www.appdev.com/prodfamily.asp?catalog_name=AppDevCatalog&category_name=SQASProduct&MSCSProfile=E643FF728F825C96AE54FD4E74A9047A9651E2D9FB6FE2649138B7244C446CBF4CCBE3E9A63026ECA5BC42F66CDA19BFBD88BDB7F89F403B08324B2A9269A89A5B048B0B634BC2522F873F74224AB1A057E4B0E37BC4C1D4814FAA5B160F815013A80BD0992FD38D0940319C4D4D1E24F936EA07B6611D9BCF8D09956F4820E0CA6D0A34C85323B5

2.       Erik Thomsen. OLAP Solutions Building Multidimensional Information Systems Second Edition.– Toronto, Canada: 2002

3.       http://www.osp.ru/win2000/2005/08/380209/

4.       Мелащенко А.О. Метод инвариантов для прогнозирования // Компьютерная математика. – Вып 1, 2008. – с. 15-23.

 

Аннотация: 

В докладе рассматриваются вопросы использования решений BIв финансовых организациях Украины. Приводятся практические рекомендации по построению OLAPрешений и их применению для прогнозирования бухгалтерского баланса.