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

Оглавление

• Введение
• Глава 1. Микрофинансовый рынок в Российской Федерации
• 1.1 Современные задачи информационной поддержки управления в компаниях микрофинансирования
• 1.2 Анализ деятельности микрофинансового рынка в Российской Федерации
• 1.3 Описание бизнес-процесса «Оформление займа»
• 1.4 Существующие средства информационной поддержки деятельности компании микрофинансирования
• Глава 2. Проектирование информационно-аналитической системы на основе хранилища данных
• 2.1 Методология проектирования и построение хранилища данных
• 2.2 Определение источников данных для хранилища
• 2.3 Проектирование хранилища данных
• Глава 3. Создание информационно-аналитической системы на основе хранилища данных
• 3.1 Создание хранилища данных
• 3.2 Применение ETL-инструментов для загрузки данных в хранилище
• 3.3 Создание управленческих отчётов
• Заключение
• Список использованной литературы
• Приложение 1


Введение

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

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

Объектом исследования является компания, работающая в сфере микрофинансирования в Российской Федерации. Предметом исследования является информационная поддержка деятельности компании микрофинансирования с применением информационных систем.

Целью работы является проектирование и построение хранилища данных для мониторинга и анализа деятельности компании микрофинансирования.

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

1) Выявление особенностей деятельности в области микрофинансирования;

2) Формализация основного бизнес-процесса в области микрофинансирования: подачи онлайн-заявки на получение потребительского микрозайма;

3) Выявление применимости современных методов разработки и построения хранилищ данных, а также заполнение и визуализация данных для решения задач мониторинга и анализа деятельности компании микрофинансирования;

4) Систематизация особенностей принятия решений в сфере микрофинансирования;

5) Выявление существующих источников, среднестатистических данных по рынку микрофинансирования;

6) Проектирование и построение хранилища данных для выбранной предметной области в целях реализации поставленной цели;

7) Проектирование, разработка и реализация механизма ETL (аббревиатура от Extract, Transform, Load) для загрузки собранных данных из разных источников в хранилище данных;

8) Разработка системы построения отчётов и анализа данных на основе хранилища данных для принятия решений.

В данной работе в качестве среды для построения хранилища данных, а также создания необходимых отчётов выбрана платформа Microsoft SQL Server 2008 R2. Загрузка данных в хранилище данных производится с помощью SQL Server Business Intelligence Studio .

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

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



Глава 1. Микрофинансовый рынок в Российской Федерации

1.1 Современные задачи информационной поддержки управления в компаниях микрофинансирования

Как было отмечено во Введении рынок микрофинансирования начал своё существование относительно недавно. Микрофинансовые организации в Российской Федерации официально появились в 2010 году со вступлением в силу закона «О микрофинансовой деятельности и микрофинансовых организациях» [Электронный ресурс]: https://www.consultant.ru/document/cons_doc_LAW_102112/. Короткий срок существования микрофинансирования в России подразумевает наличие несбалансированного состояния рынка и, как следствие, наличие большого числа проблем. Из исследования состояния рынка выявлено, что одной из крупнейших проблем микрофинансового сектора считается низкое качество управления Шакер И.Е., Абрамова М.А., Мамута М., Тенетник О.С., Криворучко С.В. Микрофинансирование в России, М.: Центр Исследований Платежных Систем и Расчетов, 2013, 155 с.. Ежедневно в микрофинансовых компаниях принимаются различные решения по управлению и развитию организации. Данный бизнес-процесс принято назвать «процесс принятия решений». Подобные решения одной организации могут изменить развитие всего рынка, определяя будущее бизнеса. Как правило, данные решения выносятся на основании информации о текущем состоянии компании. Если для небольших компаний данный процесс не составляет особого труда и может быть реализован без внедрения дополнительных информационных систем, то для крупных компаний с большим потоком информации процесс хранение, обработка и анализа данных вручную невозможен. Так, например, основываясь на результатах исследования рейтингового агентства «Эксперт РА» (RAEX) [Электронный ресурс]: http://www.raexpert.ru/researches/mfo/itog_2015_pre/: «Рынок микрофинансирования по итогам 2015 года: жертвуя выдачей», можно констатировать, что ежегодный портфель заёмщиков лидирующих микрофинансовых организаций в среднем составляет 50 000 займов в год, что подразумевает порядка 150 транзакций в день. На сегодняшний день одним из основных элементов в целях автоматизации является система хранилищ данных, позволяющая осуществлять структурированное хранение большого числа информации, а также обрабатывать и анализировать данные.

Хранилище данных - сложная комплексная система хранения данных, включающая в себя информационные ресурсы, соответствующие определённой предметной областиАверченков В., Рощин С. Мониторинг и системный анализ информации в сети Интернет. М.: ФЛИНТА, 2011, 145 с.. Хранилище данных позволяет собрать в едином месте всю информацию, которая может понадобиться управляющему при принятии решений. Удобство хранилища данных также заключается в том, что данные, пройдя предварительную обработку, могут быть загружены с помощью ETL-приложений из самых разнообразных источников, включая корпоративные информационные системы, локальные файлы (таблицы Excel, Access), данные, предоставляемые или каким-то образом получаемые от контрагентов, данные по рынку и др.

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

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

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

1.2 Анализ деятельности микрофинансового рынка в Российской Федерации

С каждым годом объёмы оказания финансовых услуг институтов микрофинансирования в России увеличиваются в несколько раз, о чём свидетельствуют результаты исследования рейтингового агентства «Эксперт РА» (RAEX) [Электронный ресурс]: http://www.raexpert.ru/researches/mfo/itog_2015_pre/: «Рынок микрофинансирования по итогам 2015 года: жертвуя выдачей». На конец 2015 года зафиксировано, что общий объем микрозаймов вырос на 6,5% по сравнению с предыдущим годом и составил 139,9 млрд. руб., а количество заёмщиков в данном секторе увеличилось до 3 миллионов человек (+20%). На фоне активного ужесточения политики надзора и уменьшения количества кредитных организаций со стороны Центрального Банка РФ, которое ведется с 2014 года, данные показатели микрофинансирования демонстрирует свою силу и востребованность со стороны российских граждан.

Однако, наряду с ростом общего объёма рынка микрофинансрования, зафиксировано сокращение числа работающих на рынке микрофинансовых организаций. На рисунке 1 представлена динамика изменения показателей числа ликвидированных с рынка микрофинансирования организаций за период с ноября 2011г. по март 2016г⁵. В результате по итогам 2015 года реестр МФО включает 36882 организаций, что на 12,2% меньше значения показателя годом ранее. Данное сокращение связано с введением политики по защите интересов заёмщиков и инвесторов на рынке микрофинансирования со стороны Банка России в 2015 году Указание Банка России от 24.06.2015 № 3690-У «О порядке осуществления Банком России контроля за исполнением плана восстановления платежеспособности микрофинансовой организации»; Указание Банка России от 24.06.2015 № 3689-У «О временной администрации микрофинансовой организации»; Положение Банка России от 10.12.2015 № 517-П «О порядке осуществления временной администрацией микрофинансовой организации контроля за деятельностью ликвидационной комиссии (ликвидатора) в случае принятия решения о ликвидации микрофинансовой организации в период деятельности временной администрации»..



Рисунок 1 Количество ликвидированных МФО по месяцам ЦБ РФ, [Электронный ресурс]: http://www.cbr.ru/

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

Как правило, в микрокредитовании выделяется 3 типа услуг (таблица 1).



Таблица 1. Сегменты микрофинансового рынка

Тип займа	Цель займа	Сумма займа, руб.	Ставка	Срок
«Займы до зарплаты»	любая	2-30 тыс.	600-800%	2-30 дней
Потребительские	любая	10-50 тыс.	250-400%	6-12 месяцев
Юридические	любая	2000-1 млн.	70-650%	2-25 месяцев

Опираясь на данные, приведенные в информационно-аналитическом материале «Обзор ключевых показателей микрофинансовых организаций» ЦБ РФ, [Электронный ресурс]: http://www.cbr.ru/finmarkets/files/supervision/review_mfo_110516.pdf, сделанный Банком России делаем вывод о том, что на сегодняшний день наиболее популярной формой микрозаймов является сфера «займы до зарплаты» (рис.2). Наряду с другими формами микрокредитования, только сектор «займы до зарплаты» показал положительный прирост показателей в 2015г. Благодаря этому, сумма займов, выданных физическим лицам, за год выросла на 11,7% (до 117,5 млрд. руб.), количество договоров, заключенных с физическими лицами, увеличилось на 30,5% (до 11,27 млн. договоров). При этом объем выданных микрозаймов «до зарплаты» продемонстрировал значительный рост в 45,6% и достиг значения в 62,8 млрд. руб., средняя сумма микрозайма в данной категории выросла с 5,8 тыс. рублей до 6,7 тыс. рублей. Для сравнения средняя сумма потребительского микрозайма снизилась с 12,2 до 10,4 тыс. руб., а сумма юридического микрозайма - с 11,9 до 11,4 млрд. рублей.

Рисунок 2 Темпы прироста микрофинансовых организаций в сравнении со смежными секторами

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

Как говорилось ранее, ежегодно Банк России ужесточает условия существования участников на рынке микрофинансирования с целью сделать рынок более прозрачным и увеличить уровень защиты клиентов. Важно отметить, что 2016 год не стал исключением. 29 марта 2016 года вступили в силу поправки в федеральном законе «О микрофинансовой деятельности и микрофинансовых организациях» ЦБ РФ, [Электронный ресурс]: http://www.cbr.ru/. Суть данных поправок состоит в следующем:

a) Разделение микрофинансовых организаций на два типа - микрофинансовые (МФК) и микрокредитные (МКК).

Для получения статуса МФК размер собственных средств должен быть не менее 70 млн.руб. (при несоблюдении данного условия через 180 дней МФК будет исключена из реестра). Для получения статуса МКК разрешен размер собственных средств может быть менее 70 млн.руб.;

b) Ограничен и предельный размер долга по продуктам «займы до зарплаты»: совокупный размер начисленных процентов по таким займам не может превышать сумму основного долга более чем в 4 раза. Также сумма займа до зарплаты не должна превышать 30 000 рублей, а максимальный срок займа 1 месяц;

c) Установлен максимальный процент неустойки в размере 0.05% от суммы займа за каждый день просрочки.

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

На основе вышеизложенного анализа, можно сделать вывод о том, что, несмотря на рост портфеля, состояние рынка микрофинансирования нестабильно. Ввиду внесения изменений в законодательство о микрофинансовой деятельности со стороны Банка России, количество игроков на рынке с каждым годом становится меньше. Для удержания позиций микрофинансовым компаниям необходимо выстраивать чёткий план своей работы, соблюдать все необходимые правила рынка, а также вести постоянный учёт собственной деятельности. В целях повышения качества работы автоматизация основных бизнес-процессов компании является неотъемлемой частью успешной работы организации.

1.3 Описание бизнес-процесса «Оформление займа»

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

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

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

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

После подписания договора клиентом займ считается активным. Клиенту переводится денежная сумма, указанная в договоре займа. В результате данный процесс завершается статусом «займ выдан».



Рисунок 3 Бизнес-процесс получения микрозайма

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



1.4 Существующие средства информационной поддержки деятельности компании микрофинансирования

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

· СУБД (система управления базами данных для реализации хранилища данных)

· ETL (загрузка собранных данных)

· Инструменты анализа

За основу была выбрана концепция BI (Business Intelligence) Microsoft Business Intelligence - технические описания и презентационные материалы. © 2010 MICROSOFT CORP., предполагающая перевод необработанной информации в осмысленную, удобную форму. Данная концепция опирается на четыре компонента, которые используются в данной работе (рис.4):

· Внешние источники данных, а именно файлы с собранными данными различных форматов (.xlsx, .txt и т.д.), необходимые для загрузки в хранилище данных;

· ETL-инструменты, осуществляющие преобразование и выгрузку данных из внешних источников в хранилище данных;

· Хранилище данных: предметно-ориентированная, интегрированная, вариантная по времени, не разрушаемая совокупность данных, предназначенная для поддержки принятия управленческих решений (Уильям Г. Инмон)Reinschmidt J., Francoise A. Business Intelligence Certification Guide. IBM Red books, 2000, 166 p.;

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

Рисунок 4 Компоненты для построения хранилища данных

На сегодняшний день, ввиду технологического прогресса, развитие систем хранилища данных достигло высокого уровня. Существует большое количество СУБД, реализующих работу с хранилищем данных. Однако на протяжении многих лет лидирующими платформами остаются SQL Server, Oracle и MySQL [Электронный ресурс]: http://db-engines.com/en/ranking. Данные продукты имеют как преимущества, так и недостатки, которые проанализированы в данной работе.

MySQL

Плюсы: бесплатно; лёгкое администрирование MySQL с набором всего необходимого функционала; поддержка большинства платформ
Минусы: незащищённость от потери данных; поддержка только малых баз данных.

Oracle

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

Минусы: большая цена; мощное оборудование для оптимальной работы Oracle; высокоспециализированный персонал для поддержки базы данных; сложности в изучении функционала.

Microsoft SQL Server

Плюсы: большой пакет встроенных инструментов (DataTransformationServices, MicrosoftDecision-SupportServices и т.д.), которые просты в использовании; наибольшая надежность и безопасность в сравнении с другими продуктами; наименьшие затраты на администрирование sql server; наличие средств удаленного доступа.

Минусы: «привязка» к платформам Microsoft и компьютерам на базе Intel.

Проанализировав продукты, несмотря на недостатки, выбор пал именно на Microsoft SQL Server, поскольку данный продукт прост в установке и использовании и в нём предусмотрено огромное количество инструментов, функционал которых поможет достичь поставленной цели работы. Кроме того, данный продукт интегрируется с Microsoft Office, что делает работу с данным продуктом доступной для каждого пользователя платформы Windows. Еще одним немаловажным преимуществом является наличие в Microsoft SQL Server встроенной возможности для ETL-процессов, а также построения отчётов.

На основе выбранной СУБД несложно определиться с ETL-инструментом. Поскольку Microsoft SQL Server имеет встроенные компоненты, то загрузка данных из внешних источников в хранилище данных, а также преобразование данных реализовано с помощью средства SQL Server Business Intelligence Studio.

Инструментами интеллектуального анализа данных послужили SQL запросы, выполненные на основе хранилища данных в Microsoft SQL Server.

Итак, в качестве СУБД выбрана Microsoft SQL Server, а в качестве ETL системы- SQL Server Business Intelligence Studio.



Глава 2. Проектирование информационно-аналитической системы на основе хранилища данных

2.1 Методология проектирования и построение хранилища данных

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

· Схема «Звезда»;

· Схема «Снежинка»;

· Схема «Data Vault».

Прежде чем приступить к анализу возможных методов построения хранилища данных, необходимо ввести следующие определенияInmon W.H. Building the Data Warehouse. John Wiley & Sons Inc., 2002, 432 p.:

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

· Таблица измерений: таблица, которая содержит атрибуты (компоненты) событий, сохраненных в таблице фактов. Атрибуты (измерения) представляют собой текстовые или иные описания, логически объединенные в одно целое.

· Первичный ключ: это поле (или набор полей) со значениями (или комбинацией значений), которые являются уникальными для всей таблицы. Каждая таблица может содержать только один первичный ключ.

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

Теперь рассмотрим каждый метод подробно.

Схема «Звезда»Горбач И.В. Microsoft SQL Server 2005 Analysis Services. OLAP и многомерный анализ данных. М.: БХВ-Петербург, 2007, 928 с.

Схема «Звезда» (рис.5) состоит из одной таблицы фактов, окруженной таблицами измерений. Главной считается таблица фактов, которая содержит суммируемые или фактические данные и может включать в себя несколько миллионов строк. Что касается таблиц измерений, то они имеют меньшее количество строк, чем таблица фактов и содержат описательную информацию. При соединении таблиц фактов и измерений с помощью идентифицирующей связи первичный ключ таблицы измерений мигрирует в таблицу фактов в качестве внешнего ключа, прописанного в поле таблицы фактов как суррогатный ключ.

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

Рисунок 5 Схема «Звезда»



Схема «Снежинка»¹⁴

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

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

Рисунок 6 Схема "Снежинка"

Схема «Data Vault» (DV)Материалы Web-сервера [Электронный ресурс]: http://www.olap.ru/

В основу данной архитектуры также вошла схема «звезда». В отличие от предыдущих схем, DV (рис.7) может обрабатывать массивные наборы данных, занимая при этом значительно меньше физического места. В роли таблиц, хранящих уникальные ключи, выступают Хабы. Таблицы также содержат поля с дополнительными данными для отслеживания изменений: искусственный ID, дата загрузки в хранилище данных, источник данных и т.д. Отличительной особенностью Хабов является отсутствие поля внешних ключей. Все таблицы Хаб связаны между собой с помощью Линков, содержащих такие поля как искусственный ID, суррогатные ключи Хабов, дату загрузки и источник данных. В роли таблицы измерения здесь выступает компонент Сателлит, включающий в себя информацию о Хабах и Линках. Данная таблица состоит из искусственного ID Хаба или Линка, даты загрузки, дату окончания, источника данных. Аналогично Хабу, Сателлит не включает в себя внешних ключей.

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

Рисунок 7 Схема «Data Vault»

Итак, проанализированы три методологии построения хранилища данных. Возвращаясь к задачам данной работы, изложенных выше, отметим, что архитектура «Звезда» в полной мере не удовлетворяет заданным требованиям для выполнения поставленной задачи. Отсутствие агрегированных таблиц упрощает построение хранилища данных, сужая функционал задачи. Архитектура Data Vault, на первый взгляд, демонстрирует приемлемую гибкость своей модели, но существенен факт сложных и длительных ETL-процессов загрузки данных в хранилище данных, что также негативно сказывается на продуктивности выполнения поставленной цели. В конечном счете, несмотря на недостаток, выбрана схема «снежинка», чья архитектура соответствует критериям, необходимым для реализации поставленной задачи.

2.2 Определение источников данных для хранилища

Поскольку хранилище данных построено для конкретной микрофинансовой компании ООО МФО «Кредитех Рус», все данные взяты из аналитических отчётов данной организации.

Т.к. за базовый выбран бизнес-процесс «оформление и выдача займа», то важна такая информация, как:

· Количество произведенных услуг за определенный период времени;

· Количество клиентов за определённый период времени;

· Статистика личных данных клиентов (возраст, пол, адрес проживания);

· Статистика сезонности подачи заявок на займ.

Опираясь на вышепоставленные задачи, необходим сбор данных по следующим сущностям и их атрибутам (таблица 2).

Таблица 2. Сущности и атрибуты ХД

Имя сущности	Атрибуты
Клиент	ФИО, пол, другие особенности
Микрофинансовая Операция	Наименование, Сумма, Способ выплаты, Клиент операции, Период
Федеральный округ	Наименование, регион
Регион	Наименование, федеральные округа

Поскольку выбранная компания работает только на территории России, то сущность «Страны» не создана. В перспективе использования построенного хранилища данных для микрофинансового сектора в целом, данную сущность необходимо будет добавить.

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

Помимо данных, взятых из отчётности микрофинансовой компании, также собрана и контекстная информация по предлагаемому продукту микрокредитования, основываясь на данных портала https://www.kredito24.ru/. В таблице 4 приведен перечень собранных данных и наименование файлов, в которые эти данные сохранены.

Таблица 4. Итоговые файлы

Наименование блока данных	Имя файла
Данные по клиентам (2015-2016)	Clients.xlsx
Перечень регионов	Regions.xlsx
Перечень округов	Countries.xlsx
Данные об операциях	Operations. xlsx

2.3 Проектирование хранилища данных

За основу взят бизнес-процесс «оформление и выдача займа», описанный выше.

Задачей данного раздела является проектирование хранилища данных.

К проектированию хранилищ данных обычно предъявляются следующие требования, отличающих их от других систем хранения:

· Структура данных хранилища должна быть понятна пользователям;

· Должны быть выделены статические данные, которые регулярно модифицируются: ежемесячно, ежеквартально;

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

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

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

Роль каждой таблицы определяется префиксом в названии:

· Dim_название - Таблица измерений;

· Fact_название - Таблица фактов.

Прежде чем создать базу данных со схемой типа снежинка, необходимо проанализировать выбранный бизнес-процесс предметной области с целью выяснения центрального вопроса, ответ на который наиболее важен. Все прочие вопросы должны быть объединены вокруг этого основного вопроса и моделирование должно начинаться с этого основного вопроса. В бизнес-процессе «оформление и выдача займа» ключевым вопросом является факт оформления сделки займа (операции) между клиентом и микрофинансовой организацией. Для этого создаем центральную таблицу факта: Fact_Operations. Далее необходимо определить, какие данные нам необходимы для каждой успешной сделки. Создаем вокруг таблицы фактов таблицы измерения с информацией о клиенте (Dim_Client), способе выплаты займа (Dim_Payment), а также дате оформления сделки (Dim_Periods и Dim_Years). Используя схему снежинка, выделим также такие агрегированные объекты как округ проживания клиента (Dim_Country) и статус клиента (Dim_Status). Для полноты базы данный добавим ещё один агрегированный объект к таблице Dim_Country регион проживания клиента (Dim_Regions). Наличие информации из представленных выше таблиц, позволит полноценно видеть текущую ситуацию дел по портфелю клиентов и займов микрофинансовой организации.

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

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

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

Таблица 5. Поля таблиц измерений и фактов

Наименование таблицы	Поля
Dim_Client	- Идентификатор клиента (суррогатный ключ) (Client_id) - Фамилия (Surname) - Имя (Name) - Отчество (Midname) - Пол (Gender) - Внешний (суррогатный) ключ к измерению агрегации Dim_Status (Status_id)
Dim_Status	- Идентификатор статуса клиента (суррогатный ключ) (Status_id) - Наименование статуса клиента (Status_name)
Dim_Regions	- Идентификатор региона (суррогатный ключ) (Regions_id) - Наименование региона (Reg_name)
Dim_Country	- Идентификатор округа (суррогатный ключ) (Country_id) - Наименование федерального округа (Country_name) - Внешний (суррогатный) ключ к измерению агрегации Dim_Regions (Regions_id)
Dim_Payment	- Идентификатор способа выдачи (суррогатный ключ)(Payment_id) - Наименование способа выдачи (Name) - Подробное описание способа выплаты (Description)
Dim_Periods	- Идентификатор периода (суррогатный ключ) (Periods_id) - Наименование месяца (Month_name) - Внешний (суррогатный) ключ к измерению агрегации Dim_Quarters (Quarters_id)
Dim_Quarters	- Идентификатор квартала (суррогатный ключ) (Quarters_id) - Порядковый номер квартал (Quarter)
Dim_Years	- Идентификатор года (суррогатный ключ) (Years_id) - Порядковый номер года (Year_name)
Fact_Operations	- Идентификатор операции (Operation_id) - Сумма платежа (Amount) - Внешний (суррогатный) ключ к измерению Dim_Payment (Payment_id) - Внешний (суррогатный) ключ к измерению Dim_Client (Client_id) - Внешний (суррогатный) ключ к измерению Dim_Years (Years_id) - Внешний (суррогатный) ключ к измерению Dim_Periods (Periods_id)

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

· Для числовых значений с большим диапазоном используется тип int, который является целочисленным типом с диапазон от 0 до 4294967295. Такой большой диапазон данных необходим для создания полей уникальных идентификаторов, чьи значения велики;

· Для числовых значений с небольшим диапазоном используется тип smallint с диапазоном от 0 до 65535. Этого достаточно для создания полей Year_name и Amount;

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

· Для поля Quarter используется тип nchar, который позволяет использовать числовые и символьные значения.

На основе данной информации каждой строке был присвоен тип. Конечная информация о смоделированных таблицах представлена в
таблице 6.

На данном этапе моделирование бизнес-процесса «оформление и выдача займа» завершено. На основе смоделированных данных составлена таблица хранилища данных с помощью компонентов среды SQL Server Management Studio, представленная в следующем разделе.

Таблица 6. Данные о таблицах фактов и измерений

Dim_Client	Dim_Status	Dim_Regions	Dim_County	Dim_Payment	Dim_Periods	Dim_Quarters	Dim_Years	Fact_Operations
Client_id (int)	Status_id (int)	Regions_id (int)	Country_id (int)	Payment_id (int)	Periods_id (int)	Quarters_id (int)	Years_id (int)	Operation_id (int
Name (nvarchar [225])	Status_name (nvarchar [225])	Reg_name (nvarchar [225])	Country_name (nvarchar [225])	Name (nvarchar [225])	Month_name (nvarchar [225])	Quarter (nchar [50])	Year_name (smallint)	Amount (smallint)
Surname (nvarchar [225])			Regions_id (int)	Description (nvarchar [225])	Quarters_id (int)			Payment_id (int)
Midname (nvarchar [225])								Client_id (int)
Gender (nvarchar [225])								Years_id (int)
Status_id (int)								Periods_id (int)
Country_id (int)

Глава 3. Создание информационно-аналитической системы на основе хранилища данных

3.1 Создание хранилища данных

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

Для создания таблицы необходимо выполнить следующие шаги без помощи кодирования (на примере создания таблицы Dim_Client):

· Необходимо создать базу данных «MFIs database» с помощью вкладки «Создание базы данных», в которой будет расположена система хранилища данных (рис.8);

Рисунок 8 Окно Создание баз данных

· Далее необходимо войти в базу данных «MFIs database» и выбрать вкладку «Таблицы». Нажав правой кнопкой по вкладке, переходим в раздел «Создать таблицу». Открывается таблица, представленная на рис.9, поля которой необходимо заполнить значениями;

Рисунок 9 Создание таблицы

· Водим первой строкой наименование первичного ключа Client_id типа int. Чтобы задать первичный ключ необходимо выбрать данное поле и после нажатия правой кнопкой мыши выбрать «Задать первичный ключ». Также с целью автоматического заполнения данного поля, начиная с единицы, необходимо в Свойствах таблицы перейти во вкладку «Спецификация идентификатора» и напротив поля «Идентификатор» выбрать да (рис.10);

Рисунок 10 Создание суррогатного ключа

· Далее заполняем столбцы таблицы «Имя столбца» и «Тип данных», как показано на рис. 11;



Рисунок 11 Заполнение полей таблицы

· Далее необходимо изменить наименование таблицы. Переходим в раздел «Свойства» и в поле «Имя Идентификатора» вносим новое название таблицы Dim_Client и нажимаем кнопку сохранить (рис.12);

Рисунок 12 Изменение наименования таблицы

· В результате была создана таблица Dim_Client, представленная на рис.13.



Рисунок 13 Таблица Dim_Client

Аналогичным способом были созданы таблицы Dim_Status, Dim_Regions, Dim_County, Dim_Payment, Dim_Periods, Dim_Quarters, Dim_Years, Fact_Operations.

Последним этапом разработки хранилища данных является соединение созданных таблиц. Рассмотрим данную процедуру на примере соединения таблицы Dim_Client и Dim_Country:

· Создадим диаграмму базы данных, в которой будет располагаться структура хранилища данных. Переходим во вкладку «Создать диаграмму базы данных» и добавим созданные таблицы (рис.14);

Рисунок 14 Создание диаграммы базы данных

· Соединим таблицу Dim_Client и Dim_Country по полю Country_id и нажимаем кнопку сохранить (рис.15);

Рисунок 15 Процесс соединение таблиц Dim_Client и Dim_Country

· Таблицы соединены (рис.16).

Рисунок 16 Представление соединения таблиц Dim_Client и Dim_Country

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



Рисунок 17 Хранилище данных

SQL код, сформированный с помощью инструмента «Мастер формирования сценариев», который описывает процесс создания исходной схемы хранилища данных, представлен в Приложении 1 данной работы.

В конечном счете, создано 9 таблиц. Подробнее о каждой таблице:

· Таблица Dim_Client содержит всю информацию о клиенте, которую он ввел в момент регистрации;

· С таблицей Dim_Client связана таблица Dim_County, куда вносятся информация о месте регистрации клиента. В свою очередь таблица Dim_Country имеет агрегированную таблицу Dim_Regions, где отдельно хранится информация о регионах округов;

· С таблицей Dim_Client также связана таблица Dim_Status, куда входит наименование статуса заёмщика: первичный клиент, периодичный клиент, постоянный клиент. В зависимости от данного статуса в МФО выносятся решения о возможной сумме выдачи;

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

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

· Для удобства создания отчётов и анализов мною была введена таблица Dim_periods и таблица Dim_Years, которые включает показатели времени. Данное измерение позволит в будущем фильтровать информацию, например по месяцу, когда был выдан займ. В случае если анализ необходимо провести квартально, существует агрегированная от Dim_periods таблица Dim_Quarters;

· Таблица Fact_Operations связана с таблицами Dim_Client, Dim_Payment, Dim_periods и Dim_Years.

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

3.2 Применение ETL-инструментов для загрузки данных в хранилище

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

Clients.xlsx: файл, который содержит информацию о клиентах компании с начала 2014 по конец 2015;

Regions.xlsx: файл, содержащий информацию о регионах;

Countries.xlsx: файл, содержащий информацию об округах;

Operations. xlsx: файл, содержащий информацию об операции.

Как было сказано ранее, для преобразования и загрузки данных в хранилище данных используется среда SQL Server Business Intelligence Studio: Integration Services Project. Данный инструмент позволяет загружать массивы данных из внешних источников (в нашем случае это файлы формата Excel, представленные выше) в хранилище данных, учитывая тот факт, что таблицы хранилища соединены между собой с помощью суррогатных ключей. Следует отметить, что загрузка данных для таких таблиц измерений, как Dim_Status, Dim_Payment, а также генерации временных данных для измерений Dim_periods, Dim_Years, Dim_Quarters производится вручную ввиду отсутствия большого потока информации (число строк не превышало отметки 12).

Компонент под названием «Integration Services» входит в состав платформы данных Microsoft SQL Server 2008R2 и служит для построения высокоэффективной интеграции данных и для решения задач, связанных с последовательно выполняемыми действиями, включающими операции по извлечению, преобразованию и загрузке (ETL) данных в хранилище. Служба Integration Services выполняет задачу загрузки и преобразования данных из неструктурированного файла непосредственно в таблицы фактов и измерений SQL Server. Важной особенностью данного инструмента является простота обновления данных в хранилище данных. Мастер изменяющихся измерений создает инструкции SQL, которые обновляют и заменяют записи, обновляют связанные записи, а также добавляют новые столбцы в таблицы. Данная функция позволит в дальнейшем упростить процесс обновления данных в хранилище данных.

Загрузка данных с помощью Integration ServicesГорбач И.В. Microsoft SQL Server 2005 Analysis Services. OLAP и многомерный анализ данных. М.: БХВ-Петербург, 2007, 928 с. осуществляется мгновенно и представляет собой классическую модель загрузки. Процесс начинается с выгрузки данных (без преобразований) из указанных внешних источников в отдельную базу, называемую промежуточная область. На следующем этапе все данные происходит загрузка данных из области в хранилище. На данном этапе данные значительно преобразуются и переводятся в необходимую схему хранения. Схема загрузки данных в хранилище данных представлена на рисунке 18.

Рисунок 18 Схема загрузки данных в ХД

Для загрузки файлов в хранилище данных с помощью Integration Services необходимо сделалась следующее:

· Создать подключение к базе данных, где находится построенное хранилище данных;

· Во вкладке «SSIS Toolbox» необходимо выбрать компонент Data Flow Task. Данный элемент предназначен для выполнения преобразования данных в ходе их перемещения из исходного источника в место назначения;

· Перейдя в компонент Data Flow Task необходимо во вкладке «Other Sources» выбрать Excel Source (исходный источник данных, из которого будут выгружаться данные), а также во вкладке «Other Destinations» выбрать OLE DB Destination (что послужит конечным источником хранилища данных);

· Осуществить загрузку каждого файла, представленного выше, в источник Excel Source с помощью инструмента Excel Connection Manager;

· Осуществить подключение компонента OLE DB Destination к SQL серверу, где находится построенное хранилище данных, и указать необходимую таблицу, куда необходима загрузка данных;

· После чего компоненты Excel Source и OLE DB Destination необходимо соединить, как представлено на рис.19;

· На последнем шаге необходимо запустить загрузку данных при помощи нажатия кнопки Start.

Рисунок 19 Применение ETL инструмента

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

· Начало процесса с нажатия кнопки Start;

· Затем Integration Services осуществляет проверку соединения с сервером SQL;

· При успешном подключении идет последовательная загрузка всех измерений;

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

микрофинансирование хранилище данные займ



Рисунок 20 Успешная загрузка данных в ХД

...