Размещено на http://www.allbest.ru/

Размещено на http://www.allbest.ru/

Разработка хранилища данных для микрофинансовой организации в Индии



Введение

микрофинансовый бизнес фреймворк

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

Цель работы

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

Объект исследования

Исследуемый объект - хранилище данных микрофинансовой организации Homecredit India.

Предмет исследования

Предмет исследования - подходы к разработке хранилища данных.

Задачи проекта

Для достижения целей работы, необходимо выполнить следующие задачи:

1. Собрать бизнес требования к витринам данных

2. Собрать функциональные требования к витринам данных

3. Согласовать с банком модель данных

4. Написать прототипы витрин данных

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

6. Провести UAT

Постановка проблемы

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

Актуальность работы

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

Структура работы

Основная часть работы состоит из трех глав.

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

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

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

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

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

Основные источники информации

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

Первая область - описание банковских процессов: как формируется ставка по кредиту, какие проблемы были у продуктов в банковской отрасли больше десяти лет назад, какие решения виделись тогда и что по факту помогло решить те проблемы. Например, это работы Boyd, J.H., De Nicolo, G., Никитина (сотрудник ВШЭ), Rajan, R, Dhal, S.C.

Следующая область - статьи, имеющие отношение к микрофинансовому бизнесу в Индии, описывающие клиентов, проблемы, распространение микрокредитов и так далее. Авторы основных статей - Orazio Attanasio, Britta Augsburg, Ralph De Haas, Emla Fitzsimons, Heike Harmgart, Costas Meghir, Jacob Poushter, Craig Churchill и другие.

Далее, учебники и статьи по хранилищам данных. Здесь можно выделить работу Kimball, R. и Caserta, J. У. По оптимизации запросов - Richard A. Ganski.



1. Особенности микрофинансовой организации в Индии

Основной доход банков - доходы по процентам от выдаваемых кредитов. У кредита есть несколько показателей, его характеризующих, такие как: тело кредита, срок, ставка и сопряженный со ставкой показатель - риски. Микрофинансовая ораганизация, для которой происходит разработка, предполагает маленькое тело кредита. Срок выдачи кредитов - несколько месяцев, чаще всего меньше года. Риски же, сопряженные с выдачей кредитов большие. Об этом можно судить, например, по ставке кредитов. Если средний показатель ставки по ипотечному кредитованию порядка 8.8%, ставка по автокредитам 9.25%, то для потребительских кредитов ставки начинаются от 10.5% [37]. Это связано с тем фактом, что при ипотечном кредитовании недвижимость остается на балансе банка, до тех пор, пока заемщик не выполнит обязательства по кредиту. Так, в случае, если заемщик откажется платить, банк может компенсировать недостачу за счет недвижимого имущества. Считается, что недвижимое имущество имеет стабильную цену, следовательно убытки могут быть покрыты в полном объеме. С автокредитами все аналогично, разница в том, что автомобиль морально устаревает, что сказывается на его цене. Следовательно, у банка есть риск невозможности полной компенсации. Минимизация риска позволит предлагать клиентам продукты дешевле чем у конкурентов (Boyd & De Nicolo, 2005), которые не внедрят такое решение. [4] [8] [27]

Исходя из сказанного, микрофинансовой организации, для роста требуется постоянное привлечение низкорисковых клиентов (Rajan & Dhal, 2003). Предлагаемые продукты - кредитные карты с маленьким лимитом, кредиты наличными и кредиты в точках продаж, это кредиты выдаваемые, например, в магазинах электроники на покупку продукции из этого магазина.



1.1 Клиенты микрофинансовой организации

Так как экономика Индии одна из наиболее быстро развивающихся, покупательская способность населения тоже растет. Этот тренд подтверждает уменьшение числа людей, находящихся за чертой бедности. Если в 2011-12 годах данный показатель был на уровне 12.4%, то к 2016 году опустился до 3%. [11]

Рисунок 1. ВВП на душу населения

Несмотря на рост ВВП (рисунок 1) на душу населения, клиентами микрофинансовых организаций все еще остаются люди с маленьким доходом [1]. Так, исследование, проведенное в 2016 году подтверждает, что примерно 2/3 клиентов микрофинансовых организаций - малоимущие, или участники так называемых self-help groups. Это группа людей, формальная или неформальная, решившая объединить свои финансы для получения некоторой стабильности. [5] [6]

1.2 Продукты микрофинансовой организации

Банковские продукты бывают двух видов: персонализированные и, напротив, стандартизированные. У стандартизированных есть свои плюсы: во-первых, позволяют оптимально управлять кредитами, во-вторых, полевым сотрудникам легче принимать решения о выдаче или об отказе в кредите, в-третьих, позволяют принимать решение на основе конкретного перечня документов от клиента. Кроме того, учитывая, что уровень экономического образования в Индии мал, заемщику может быть сложно объяснить его обязанности, однако, при малом числе кредитных продуктов, обязанности объяснять становится легче. [20]

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

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

Первая группа - кредитные карты. Она характеризуется кредитным лимитом, это максимальная сумма кредита, которая может быть у клиента единовременно. То есть погасив кредит, клиент вправе воспользоваться им снова. Другая характеристика - грейс период, это период, после взятия кредита, когда проценты не начисляются или начисляются по пониженной ставке. Как и у любого вида кредита, кредитные карты характеризуются ставкой кредита.

Вторая группа кредитов это, так называемые, product loans. Это могут быть кредиты, выданные под конкретный продукт. Он характеризуется целью кредита или каналом продажи. В данном случае каналом может быть point of sales - представитель микрофинансовой организации, например, в магазине электроники.

Последняя группа кредитов - кредиты наличными. Особых характеристик у подобного кредита нет.

Помимо перечисленных есть продукты top-up или consolidation. Эти продукты не увеличивают прибыль банка напрямую, а призваны упростить контроль за выполнением условий операций как для банка так и для клиента. Например, если у клиента несколько кредитов, их можно собрать в один, и выплачивать ежемесячно один платеж.

1.3 Каналы связи

Ниже перечислены каналы связи со старыми клиентами и потенциальными клиентами.

Push уведомления в мобильном приложении. Целевая аудитория такого канала - люди, имеющие работающее мобильное приложение и пользующиеся им с какой-то частотой. От частоты пользования зависит содержание всплывающих уведомлений. Например, если клиент начал заполнять заявку на кредит, но в какой-то момент остановился, ему придет уведомление с предложением продолжить заполнение заявки. Если клиент не пользовался приложением несколько месяцев, ему придет уведомление о возможности получения кредита. Преимущество Push-уведомлений в возможности получения отклика. К примеру, если клиент отреагировал на push, и продолжил заполнения кредита, значит коммуникация прошла успешно. Push уведомления позволяют быстро перейти к заполнению заявки на кредит, причем это происходит интерактивно и моментально.

Связь посредством SMS сообщений предназначена для клиентов, обладающих подтвержденным телефонным номером. Недостаток такого канала связи - невозможность мониторинга отклика: нет механизма, позволяющего отследить эффективность получения сообщения. Единственное, что известно от отправляемом сообщении это его статус: было сообщение доставлено или нет.

Следующий канал связи, о котором стоит упомянуть - телефонные звонки. В Индии телефонная связь очень дешевая и доступная, причем число обладателей смартфонов с каждым годом растет (рисунок 2). В том числе поэтому, и потому что телефонная связь в индии дешевая, продажи по телефону считаются эффективным способом привлечения новых клиентов. Многие компании этим пользовались, для продажи своей продукции, некоторые использовали неэтичные манеры продаж, в том числе, могли звонить по ночам. С целью защиты личного пространства, в Индии был предложен и разработан National Customer Preference Register (NCPR) [33]. Это список, в котором хранится информация об отказе клиентом в любой коммерческой коммуникации. Таким образом, любая организация, желающая продать свои услуги по телефону, прежде чем звонить, должна проверить, нет ли потенциального клиента в этом реестре, и, если есть, коммуникация не должна производиться иначе, звонок будет считаться незаконным и компанию оштрафуют. [3]

Рисунок 2. Доля населения Индии, владеющая смартфонами

Помимо перечисленных выше каналов связи используется коммуникация посредством Viber, WhatsApp. Эти каналы характеризуются возможностью узнать реакцию клиента, в отличие от СМС сообщений. Связь также возможна посредством контекстной рекламы в Facebook (рисунок 3).

Рисунок 3. Каналы коммуникаций и ответы

1.4 Каналы продаж

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

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

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

1.5 Алгоритм получения кредита

Если связь с клиентом установлена и клиент выразил желание взять кредит, он создает заявку.

При создании заявки, в системе должна быть базовая информация о клиенте. Если ее нет, клиент должен пройти небольшой опрос, так называемый zero block of data (0BOD), в котором он должен указать паспортные данные, номер телефона и другую необходимую информацию.

Как показано на рисунке 4, при попытке создать заявку, будь то в телефоне или вместе с представителем организации, клиент должен пройти first block of data. Это более подробный перечень вопросов, требуется документальное подтверждение ответов из 0BOD. На этом этапе клиенту могут отказать в выдаче кредита, кредиты не выдается людям, находящимся в государственном черном списке - террористам, преступникам определенного рода. Кроме того, банк может отказать в кредите на основе кредитной истории клиента, на основе своего взаимодействия с ним. Решение об успешном прохождении 1BOD происходит не сразу. Одна из целей банка - чтобы время, потраченное на решение по кредиту, не превышало нескольких минут.

В случае успешного прохождения 1BOD, клиенту предлагается пройти 2BOD. На этом этапе клиент может либо отказаться от получения кредита, что скажется на вероятности получения другого кредита с этой организацией. При успешном прохождении 2BOD, и в случае подписания клиентов договора, кредит считается выданным.



Рисунок 4. Процесс получения кредита

1.6 Требования к хранилищу данных

Так как все решения основаны на данных, даже осуществление коммуникации происходит на основе данных о клиенте, для осуществления проекта требуется доработка хранилища данных.

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

Рисунок 5. Потоки данных

После того как все данные собраны в стейдж-слой, они грузятся в основное хранилище.

На основе данных в хранилище строятся витрины данных.

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

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

Рисунок 6. Ограничения проекта



На рис. 6 показаны потоки данных после того, как те попали в хранилище: ежедневный ETL процесс грузит данные в слой витрин данных, который находится в периметре имеющегося хранилища данных. Далее витрины грузятся в приложения для аналитики, запуска маркетинговых кампаний. Также, на основе витрин строятся отчеты.

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

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

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

Помимо описанных требований, существует требование к производительности базы данных. Учитывая, что объем данных огромный, это может мешать производительности запросов. Для ускорения производительности базы, во-первых, оптимизируют запросы к ней. Оптимизация запросов может вестись как с точки зрения бизнеса, так и с технической точки зрения. Например, эффективнее использовать inner join вместо left join. Однако, это не всегда возможно, так как часть данных может потеряться.

Технически, запрос можно ускорить, если правильно использовать индексы на таблицах, чаще всего пользуются B-tree индексами (рисунок 7). Индексы занимают отдельное физическое место в памяти и ускоряют запрос, если данные хорошо распределены и их много. Информация, в таком случае, хранится блоками, при этом эти блоки составляют дерево.

Рисунок 7. Сбалансированное дерево

На рисунке показан пример, когда данные хранятся в блоках (нижний ряд). Так, если в запросе потребуется найти информацию, соответствующую числу 29, системе не придется обращаться к каждому объекту таблицы (их может быть много, в данном примере больше 15) и проверять каждую запись на равенство, а всего лишь надо будет сравнить с 50, потом с 20, 30. И в итоге придется пройтись только по данным из второго блока, в котором всего два значения.

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



2. Описание источников данных

2.1 Описание базовых таблиц

В результате сбора требований, о котором рассказано в главе 3, было решено разработать 7 сущностей в витрине данных.

Сущность Client - базовая. Строится на основе таблиц, описанных ниже.

Зерно сущности - таблица OWNER_DWH.DCT_CLIENT. В ней содержится базовая информация обо всех клиентах. Их паспортные данные, место жительства. Ключ таблицы - уникальный кодификатор клиента.

Таблица, в которой содержится информация о каналах связи с клиентом - OWNER_DWH.FT_CLIENT_CONTACT_TT (рисунок 8).

Рисунок 8. Описание таблицы с каналами связи

В этой таблице по идентификатору клиента (SKP_CLIENT) и по типу контакта (SKP_CONTACT_TYPE), можно найти нужный канал связи (TEXT_CONTACT), в зависимости от типа, это будет либо номер телефона, либо адрес электронной почты, либо другое.

Для оптимизации запросов, с данной таблицей, на таблице были поставлены индексы (рисунок 9).

Рисунок 9. Индексы на таблице с контактной информацией

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

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

Рисунок 10. Данные по коммуникациям



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

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

Для удобства запросов, данная таблица тоже проиндексирована, как показано на рисунке 11.

Рисунок 11. Индексы на таблице с данными по кредитным картам

Дополнительно, в сущности клиент требуются агрегированные данные по суммам, ставкам, количеству заявок клиента и контрактам. Источники упомянутых агрегатов будут описаны ниже, так как они совпадают с теми, что будут использованы в сущностях заявка (Application) и контракт (Contract). Данные по числу полученных контрактов хранятся в в таблице OWNER_DWH.FT_CLIENT_AD.

Данная таблица тоже проиндексирована, для удобства поиска данных по конкретному клиенту, как показано на рисунке 12.

Рисунок 12. Индекс на таблице FT_CLIENT_AD

Естественно, что для принятия решения о кредите необходимы данные от департамента рисков. Эти данные хранятся в таблице OWNER_DWH.XSELL_CAMPAIGN_SPLIT. В этой таблице для всех клиентов хранится информация о рисковой группе, риске нереализации положительной стоимости дериватива, нужной для расчета резерва.

Сущность Client_tmp, как это было упомянуто, обирается на основе данных из текущего приложения, использующегося для маркетинговых кампаний - Genesys. Таблица, в которую грузятся «старые» коммуникации - OWNER_DWH.GEN_OPS_CAF_BY_CUID. В ней содержится вся коммуникация по клиентам: время звонка/смс (то есть время коммуникации), ответ клиента (в случае смс - статус, в случае звонка - результат). Push и коммуникация через социальные сети еще не были внедрены. Так как в витрину нельзя грузить все детальные данные, ввиду необходимости их обработки в сжатые сроки, было решено грузить агрегаты.

Некоторые атрибуты не нужно агрегировать, так как они доступны в таблице AP_CRM. CLX_OFFER_ARCHIVE_NEW. Например, в ней содержится информация о числе звонков, смс за последний день, два дня, три дня, 15 дней, 30 дней и 90. По сути, в этой таблице агрегированы данные, которые используются в настоящий момент в отчетах и для связи. Поэтому, эти атрибуты можно использовать напрямую, что сэкономит время для агрегирования базовой таблицы их Genesys. Однако, ввиду того, что эта таблица находится в пользовательской схеме AP_CRM, нет гарантий, что данные в этой таблице будут обновляться ежедневно. Поэтому было решено пользоваться базовой таблицей.

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

Рисунок 13. Основные внешние ключи зерна сущности Application

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

Так как статус заявки не постоянный, история хранится в таблице OWNER_DWH.FT_CREDIT_STATUS_TT. В этой таблице помимо кода кредитного дела (skp_credit_case) есть код статуса (skp_credit_status), даты начала действия статуса и конца (dtime_valid_from и dtime_valid_to соответственно). Таким образом, на любой момент времени date можно узнать статус заявки, если использовать условие dtime_valid_from < date < <dtime_valid_to (рисунок 14).

Рисунок 14. Некоторые атрибуты таблицы FT_CREDIT_STATUS_TT

skp_credit_case - это общий идентификатор для кредитного дела, например, если заявка была подписана и превратилась в контракт, идентификатор credit case не меняется, по нему можно для любого контракта отследить соответствующую заявку и наоборот.

Код статуса заявки (skp_credit_status) представляет из себя число. Чтобы пользователю узнать какому статусу соответствует код, нужно обратиться к таблице OWNER_DWH.CLT_CREDIT_STATUS_TT. Ниже перечислены возможные статусы по заявкам. Так как в стаблице со статусами хранятся статусы не только заявок но и кредитов (то есть хранятся статусы кредитных дел), в справочнике с типами хранятся статусы, релевантные именно для контрактов (рисунок 15).

Рисунок 15. Справочник с расшифровкой кода статуса

Сущность Contract в качестве зерна имеет таблицу OWNER_DWH.DCT_CONTRACT. В ней содержится информация о контрактах клиента. Соответственно, в ней есть идентификатор клиента, идентификатор продукта, который получил клиент.

Данные о платежах по контрактам хранятся в таблице OWNER_DWH. FT_INSTALMENT_HEAD_AD. В этой таблице хранятся плановые выплаты по графикам. При подписании контракта клиент соглашается с условиями, которые включают, в том числе, срок выплат, обычно это несколько месяцев, размер выплаты. Выплаты производятся ежемесячно. Соответственно, после подписания контракта в таблице появляется несколько записей с NUM_VERSION=1, то есть первая версия графика выплат. Количество записей соответствует числу плановых выплат.

В случае, если клиент совершил частичное досрочное погашение долга, генерируется новый график и появляются новые записи в таблице, с NUM_VERSION=2. Число записей по второй версии графика платежей соответствует числу оставшихся платежей, с учетом досрочного погашения. Все платежи по первой версии графика, с датой >= датам платежей из второй версии считаются неактуальными.

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

Фактические платежи хранятся в таблице OWNER_DWH.FT_OUTGOING_PAYMENT_TT. В этой таблице содержится вся информация о датах, статусах выплат по обязательствам.

Информация по датам прохождения контрактом своих статусов хранится в OWNER_DWH.FT_CONTRACT_BASE_AD. Дата активации, дата списания и так далее. Есть и другая таблица с информацией по статусам, FT_CREDIT_STATUS_TT, но она более подробная и зачастую требует агрегации, для выяснения даты изменения статуса. Этот алгоритм описан в главе 3 в разделе прототипирования.

Таблица AP_UWI.PORTFOLIO_PRODUCT хранит данные о взятии кредитных каникул клиентом.

Все перечисленные таблицы используются для создания прототипов.

Сущность Client Contact. Несмотря на тот факт, что некоторая информация о возможных каналах связи с клиентом хранится в сущности Client, для коммуникаций могут понадобиться, в том числе, новые каналы, которые еще не разработаны. Поэтому, для сохранения нормальности было решено вынести контактную информацию клиента в отдельную сущность, которая строится на основе OWNER_DWH.FT_CLIENT_CONTACT_TT, в которой хранится вся контактная информация.

Сущность Product представляет из себя справочник со всеми актуальными продуктами. Хоть продукты разбиты в 3 группы, в базовой таблице OWNER_DWH.DCT_PRODUCT их больше 5 тысяч (как показано на рисунке 15 а, наименований больше 1200, при этом некоторые продукты делятся на подгруппы), и продукты еще появляются. Это связано с тем, что продукты разделяются не только по трем группам, но и по каналам продаж, то есть кредитные карты, выданные по заявкам в приложении и в офисе банка, представляют из себя разные продукты. Кредиты, выданные на конкретную цель, тоже могут относится к отдельному продукту. Целью кредита может быть, например, покупка телефона, планшета, мопеда конкретной модели.

Рис. 15 (а). Количество банковских продуктов



Сущность Contract Documents строится на основе таблиц OWNER_DWH.DCT_CONTRACT, уже описанной, и OWNER_DWH.FT_REGISTRATION_DOC_AD. В последней перечислены документы, которые клиент приложил к заявке.

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



3. Описание выполненных работ

Для осуществления проекта по разработке хранилища данных требуется выполнить следующие задачи [2] [9]:

1. Собрать бизнес требования

2. Собрать функциональные требования

3. Согласовать с банком модель данных

4. Написать прототипы витрин данных

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

6. Провести UAT

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

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

Работа по внедрению приложения выполнялась в компании GlowByte consulting.

3.1 Характеристика компании

В профессиональные задачи линейного бизнес-аналитика на проекте с микрофинансовой организацией в Индии в GlowByte consulting входят следующие задачи:

· Сбор требований по разработке хранилища

· Прототипирование витрин

· Постановка задач разработчикам

· Участие в UAT

GlowByte consulting - консалтинговая компания, специализирующаяся на IT консалтинге. Предлагает следующие решения:

· Корпоративные хранилища данных

· Big Data

· Финансовая аналитика

· Клиентская аналитика. Автоматизация маркетинга

· Управление рисками организации

· Углубленная аналитика

Основные преимущества компании, следующие:

· Одна из лучших на рынке экспертиза в области хранилищ данных и аналитических приложений;

· Работа с современными технологиями, ведущими поставщиками ПО;

· Внедрение продуктов бизнес-аналитики в компаниях самых разных отраслей;

· Быстрые результаты, поэтапная реализация;

· Безукоризненная репутация, доверие заказчиков и партнеров.

Клиенты компании - большинство больших государственных и частных банков, телеком, х5 retail group и другие.

Партнерами выступают Oracle, SAS, SAP, IBM и другие вендоры, занимающиеся разработкой продуктов.

3.2 Сбор бизнес-требований

Было решено, что решение о коммуникации с клиентом, а именно необходимость коммуникации, канал коммуникации, шаблон коммуникации формируется на основе следующих данных о клиенте (таблица 1):

Его персональные данные:

Таблица 1. Клиент

Сущность	Группа атрибутов
Client	Info
Client	Contact info
Client	Contracts & Applications
Client	Communication info
Client	Credit Card
Client	Risk

Client info: персональная информация о клиенте, такая как имя, возраст, место рождения, место работы, зарплата и так далее.

Contact info: данные о возможности коммуникации. Тут содержится информация давал ли клиент согласие в NCPR реестре или нет, есть ли у него действующий мобильный номер, почта, аккаунты в мессенджерах.

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

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

Risk: так как у банка есть департамент рисков, данные от него необходимы в витрине. Например, есть черный список клиентов, который постоянно пополняется.

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

Рисунок 16. Схема данных

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

Например, коммуникация по телефону не происходит, если с клиентом уже была коммуникация накануне, причем не важно, звонил ли клиент сам или звонили ли ему. Это отражается в данных на рисунках 17 и 18.



Рисунок 17. Коммуникация

Рисунок 18. Входящая коммуникация

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

Рисунок 19. Число звонков

Так как подобных бизнес стратегий много, в хранилище инкорпорированы аналогичные данные по всевозможным видам коммуникаций. Например, не рекомендуется писать клиенту слишком часто. Поэтому, есть атрибут, показывающий частоту писем за 12 месяцев, полгода, 3 месяца и так далее (рисунок 20).



Рисунок 20. СМС

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

Рисунок 21. Результат коммуникации по смс

Соответственно, подобные атрибуты есть и для звонков. Как видно из структуры данных, различаются попытки соединения и фактическое соединения (рисунок 22).



Рисунок 22. Число звонков и число соединений

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

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

Рисунок 23. Число заявок и кредитов

Число заявок клиента, получивших отказ, также влияет на решение по кредиту. В общей сложности в сущности клиент 22 атрибута, которые считаются на основе заявок и контрактов.

Для генерации кредитного предложения, которое может превратиться в контракт, в случае согласия клиента, помимо агрегированной информации по заявкам и договорам, используется информация о кредитной истории клиента. Так, наличие просрочек по договору может негативно сказаться на условиях кредита или же может быть одним из факторов, повлекшим отказ в кредите. Естественно, в структуре данных это отражено, как показано на рисунке 24

Рисунок 24. Просрочка по кредиту

Есть случаи, когда клиенту отказывают в предложении независимо от его кредитной истории, эта ответственность лежит на департаменте рисков. В сущности клиент есть несколько атрибутов, которые считаются на основе данных, получаемых от департамента рисков. Примеры таких атрибутов указаны на рисунке 25.

Рисунок 25. Данные от департамента рисков

Как известно, кредитное предложение зависит от уровня риска. В начале коммуникации клиенту присваивается так называемый offline score. Это начальный скоринговый балл, который грузится в витрину ежедневно. Естественно, во время коммуникации может оказаться, что клиент принадлежит более рисковой категории или менее рисковой. Поэтому, уже на стороне приложения генерируется online score, который может пересчитываться в реальном времени. Online score имеет преимущественный вес, при расчете кредитного предложения для клиента.

Дополнительно, клиент проверяется департаментом рисков на предмет принадлежности к преступным, террористическим группировкам. В случае, если работодатель клиента вызывает подозрения, это уменьшает шансы в одобрении заявки.

Ниже приведено наполнение таблицы application, как одной из самых основных (таблица 2).

Таблица 2. Application

Entity	Group of attributes
Application	Main
Application	Dates
Application	Details
Application	Amounts
Application	Term
Application	Rates
Application	Scoring
Application	Calculated

Сущность application состоит из следующих параметров:

· Main: основные данные по заявке. Это данные о клиенте, номер заявки.

· Dates: Даты взятия в обработку, дата подписания, дата подачи и так далее.

· Details: Детали из разряда было ли заявление заполнено очно или заочно, тип продукта (наличные, кредитная карта или кредит на товар в кредит).

· Amounts: первоначальная сумма заявки, изменившаяся сумма заявки и т.д., сумма выплат

· Terms: информация по выплатам, их число.

· Rates: три варианта ставки по кредиту.

· Scoring: рисковая группа, показатель скоринга.

· Calculated: Порядок заявления, данные о предыдущем заявлении.

Ниже приведено наполнение таблицы Contract (таблица 3).

Таблица 3. Контракт

Entity	Group of attributes
Contract	Contract. Amounts
Contract	Contract. Dates
Contract	Contract. Term
Contract	Contract. Rate
Contract	Contract. Calculated

Как видно, структура таблицы Contract схожа со структурой таблицы Application.

3.3 Сбор функциональных требований

Предполагает выяснение требований к загрузке витрин: в какой момент они должны начинать грузиться, как быстро и так далее. Важная часть сбора функциональных требований - выяснение источников данных для всех атрибутов, согласуемых в ходе сбора бизнес-требований. Так как витрина содержит данные о клиентах, на основе которой будет происходить коммуникация, витрина должна загрузиться до начала кампаний.

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

Дополнительно, данная задача решает вопрос с источниками для наполнения витрины данными. То есть происходит регулярная коммуникация с банком для выяснения структуры их хранилища, таблиц, описания атрибутов и так далее. По каждому атрибуту в витрине должна иметься информация об источнике и техническом алгоритме, чтобы была возможность приступить к прототипированию, предварительно согласовав результат проделанных работ с банком.

Сбор функциональных и бизнес требований ведется в рамках GAP анализа. [17]

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

Это повлекло проблемы. Так, например, витрина application строится в основном на источнике owner_dwh.ft_application_base_tt. Доступ дали к реплике таблицы: owner_dwh#.f_application_base_tt. Эта таблица считается базовой, то есть все заявления должны храниться в ней.

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

Рисунок 26. Запрос



Результат отработанного запроса показывает, что дельта есть:

Рисунок 27. Результат запроса

Оказалось, что проблема в хэшировании данных банком, когда данные расхэшировали по нашей просьбе, в пересечении таблиц строчек было столько же, сколько в V_SAS_XSELL_APPLICATION_TYPE.

Будет добавлено описание полученных результатов. Главным образом, это источники для атрибутов. Можно описать для каких атрибутов источники найдены не были.

3.4 Согласование модели данных

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

В зависимости от имеющегося договора, банк либо проводит тендер, либо компания продолжает проводить работу, иногда работы уходят на in-house разработку.

Модель данных выглядит следующим образом: это эксель файл. В шапке файла следующие названия колонок:

Рисунок 28. Модель данных для согласования



In scope означает будет ли атрибут представлен в модели данных. Так как атрибуты могу добавляться, убираться, быть в скоупе их могут предлагать убрать, соответственно статусы New, No, Yes, Suggest to exclude.

Далее идет колонка Entity - описывающая сущность, которую нужно разработать.

Group of attributes - логическое деление атрибутов

Attribute - название самого атрибута

Далее идут описание атрибута, и его возможные значения (если они ограничены)

Рисунок 29. Модель данных

Далее - название системы, в которой ведется атрибут на стороне DWH

Тип, показывает нужно ли менять атрибут относительно его источника или можно грузить as is.

Далее - GAP анализ, здесь указывается источник для атрибута: название таблицы и название атрибута, желательно добавлять технический алгоритм.

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

Рисунок 30. Описание шапки документа



3.5 Прототипы витрин данных

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

Прототипирование ведется посредством языка SQL, в среде разработки Oracle SQL server. SQL - декларативный язык, хоть и прост в изучении, он обходит стороной некоторые вопросы связанные с эффективностью обработки запросов. В Oracle встроены процессы, позволяющие оптимизировать запросы автоматически [13]. Для автоматической эффективной обработки запросов необходимо, чтобы статистика по используемым таблицам была собрана. [25]

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

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

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

Решением было создать подзапрос, который бы содержал все актуальные и нужные данные по каждому заявлению (skp_credit_case - идентификатор заявления, кредитного договора).

Рисунок 31. Подзапрос

Одно из требований к витринам - она должна успеть загрузиться к 7 утра. По результату выполнения прототипа возникла проблема, что она грузится полтора часа, тогда как на всю витрину есть два часа.

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

Рисунок 32. Оконная функция

Рисунок 33. Оконная функция, упразднена



Рисунок 34. Оконная функция, упразднена

И другие. Самый простой вариант - избавиться. Так как часть атрибутов, считается только для заявок, у которых есть filled date, это дата, когда заявка была в статусе approved или rejected, было решено разбить запрос на две части, в одной работать с заявками с датой заполнения, в другой без. Далее - объединить массивы данных с помощью union all.

Дополнительно, большинство оконных функций нужны для атрибутов, которые представляют описание на текущей заявке некоторых данных о предыдущей заявке, например, актуальный статус предыдущей заявки. Так как для каждого подобного атрибута не оптимально искать предыдущую заявку с помощью оконной функции, было решено в одном из подзапросов единожды найти предыдущую заявку и для каждой заявки, и подтягивать требуемые атрибуты с помощью left join.

Рисунок 35. Оконная функция

Другая проблема, возникшая при прототипировании витрин, что некоторые заявки и кредиты по какой-то причине не обладают ссылкой на клиента. Вместо значения null, которое бы показывало, что у заявления или контракта нет соответствующего клиента, по правилам написания кода банка, значение атрибута должно быть -1. Получается, что оконные функции, которые ранжируют заявки или контракты в зависимости от даты, скажем, создания заявления в рамках одного клиента, отрабатывают для строчек со значением skp_client=-1 заведомо ложно. Подобная проблема с качеством данных может возникать, в данном случае решено было отказаться от загрузки в витрины подобных заявок и контрактов.

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

В случае с витриной application, была договоренность не грузить заявки, у которых skp_client =-1. При сдаче прототипа пришло сообщения от ответственного за приемо-сдаточные испытания прототипов, что часть заявок не прогружена. Причиной могут быть следующие факторы:

...