Проектирование и разработка дополнения

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

Иллюстрация 2.1: Схема «Звезда».

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

Иллюстрация 2.2: схема «снежинка».

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

В большинстве случаев, хранилище наполняется данными из нескольких источников - OLTP систем, а перенос данных из источников в хранилище часто называют ETL-процедурами (от англ. Extract, Transform и Load). В рамках этого процесса данные извлекаются из источников, преобразуются в необходимую форму и, наконец, загружаются непосредственно в само хранилище (иллюстрация 2.4). ETL-процесс часто сопровождается проблемами, связанными с несогласованностью данных из разных источников: одни и те же данные могут иметь различную структуру, или же может получиться так, что сходные данные в нескольких источниках не будут совпадать. Таким образом, для того чтобы загрузить данные в хранилище, они должны быть согласованными. Процесс достижения этой цели называется очисткой данных (data cleaning).

Иллюстрация 2.3: процесс анализа данных.

OLAP-куб.

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

Иллюстрация 2.4 наглядно демонстрирует пример OLAP-куба. В данном примере измерения представлены гранями куба: «Продукт», «Календарь», «География продаж». В свою очередь, метрикой является объем продаж. Таким образом, на пересечении осей содержатся агрегированные по измерениям данные: информация о количестве проданного продукта на заданной территории в определенный промежуток времени.

В свою очередь, измерения часто организовываются в иерархии. Иерархия является структурой, которая определяет уровень гранулированности (от англ. granularity) данных и отношения между этими уровнями. Например, измерение времени может иметь месяц в качестве наименьшего уровня гранулированности, а выше по иерархии могут содержать кварталы и годы (иллюстрация 2.5).

Иллюстрация 2.4: OLAP-куб. Источник: msdn.microsoft.com.

Иллюстрация 2.5: Иерархия календаря. Источник: msdn.microsoft.com.

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

Отчетность.

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

При анализе данных с помощью BI-инструментов, наиболее распространенным способом организации данных является определение ключевых показателей эффективности (Key Performance Indicators), которые являются метриками, используемыми для измерения прогресса достижения организационных целей. Типичными КПЭ являются доход и валовая операционная прибыль.

2.3 Выбор методологии и средств проектирования решения

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

Сегодня рынок изобилует BI-системами различной функциональности и ценовой категории. Многие крупные производители программного обеспечения предоставляют целый набор своих продуктов, объединяя их единую экосистему: каждому этапу процесса переработки данных в форму пригодную для поддержки принятий решений (например, разработка ETL-процедур, физическая реализация хранилищ данных, организация OLAP-куба, отчетности и т.д.) соответствует свой элемент продуктовой линейки. Несомненно, в результате такого объединения достигается наиболее высокая производительность и простота внедрения, но, как правило, стоимость таких продуктов гораздо выше. Примером компаний, предоставляющих такие экосистемы, являются такие гиганты в области разработки ПО, как Oracle, IBM, Microsoft и другие.

В данной работе для реализации решения на базе BI-систем был выбран комплекс продуктов от компании Microsoft. Ниже описаны основные предпосылки для принятия данного решения.

Серверный компонент системы WebTutor может быть установлен только на операционную систему Windows Server версии 2000 и более новых или как компонент IBM Domino. На сегодняшний день второй вариант практически не используется. Более того, данная версия системы не развивается, а только поддерживается. Поэтому версия для Windows Server является основной. Данный факт означает, что у всех пользователей есть сервер под управлением операционной системы Windows Server.

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

Как уже было отмечено ранее, система WebTutor позволяет хранить данные в СУБД семейства Microsoft SQL Server и Oracle. Большинство организаций используют SQL Server. Прежде всего, это связано с тем, что поддержка данной СУБД появилась значительно раньше и развивается компанией-разработчиком активнее и лучше. Также очень весомым является экономический фактор: лицензия продукта от компании Microsoft стоит значительно дешевле, нежели СУБД Oracle. С другой стороны, любую СУБД нужно администрировать, а поддержка SQL Server'а проще. Однако используется и СУБД Oracle. Чаще всего её используют государственные предприятия и корпорации, для которых очень критичным является вопрос безопасности данных. А любая закупка нового программного обеспечение должна сопровождаться публичным тендером.

Поэтому в целом можно заключить: так как система WebTutor является Windows-приложением, то компоненты MS SQL Server'а редакции Business Intelligence версии 2014 более гармонично вписываются в общую архитектуру.

В качестве физической реализации хранилища данных будет использоваться сама СУБД MS SQL Server. Ниже перечислены основные составляющие компоненты экосистемы, которые будут использоваться при разработке решения:

Компонент Integration Services (сокр. SSIS) - для организации ETL-процедур: загрузки данных в хранилище;

Компонент Analysis Services (сокр. SSAS) - для организации OLAP-кубов;

Компонент Reporting Services (сокр. SSRS) - для генерации отчетности.

2.3.1 Выбор средства визуализации и анализа данных

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

Электронные таблицы были одним из самых первых BI-систем В этом (и только в этом) параграфе Business Intelligence понимается в соответствии с более узким определением. . Хотя доступность программного обеспечения в виде электронных таблиц и его использование в качестве инструмента для анализа данных берет свое начало в 60-ых годах предыдущего столетия, его повсеместное применение в области BI началось с интеграции OLAP технологий и сводных таблиц (pivot tables). В 1991 году Lotus выпустила Improve, а затем в 1993 году Microsoft - PivotTable. Как бы то ни было, Essbase была первым масштабируемым OLAP программным обеспечением для обработки больших наборов данных, что не удавалось достигнуть более ранним версиям электронных таблиц. Название Essbase представляет собой акроним Extended Spread Sheet Database, что переводится как «расширенная электронно-табличная база данных».

Пожалуй, не может быть никаких сомнений в том, что сегодня Microsoft Excel является наиболее часто используемым программным обеспечением для BI целей. Не смотря на то, что Excel является программным обеспечением для бизнеса общего назначения, его гибкость и простота использования делает его популярным инструментом анализа данных для миллиона пользователей по всему миру. Excel установлен на сотни миллионов персональных компьютеров: гораздо больше, чем любая другая платформы бизнес-аналитики. Не будет преувеличением сказать, что название стало именем нарицательным. От использования в образовательных целях до бытового применения и внедрения на предприятиях Excel доказал свою незаменимость. Большинство людей с образованием в области финансов и управления развили практические навыки владения Excel. Это делает Excel основной и самодостаточной BI платформой. Тем не менее, как и у всех систем, Excel имеет ряд недостатков, которые делают его в определенных условиях трудно применимым в качестве BI инструмента.

В качестве BI платформы Excel применятся на предприятиях совершенно разного размера:

Малый бизнес.

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

большинство из них уже и так имеют Excel лицензии;

большинство их сотрудников уже знают, как пользоваться Excel;

задачи таких компаний относительно более простые и могут быть решены с помощью Excel.

Средний бизнес.

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

Excel больше не может справиться с постоянно растущим объемом данных;

другие производители BI начали предлагать более рентабельные альтернативы.

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

Крупный бизнес.

Ситуация с крупными предприятиями является довольно сложной. Большинство из них уже имеют масштабные BI решения. Такие решения часто соединяют различные базы данных и хранилища данных в организациях. Данные организации уже сделали значительные инвестиции и продолжают их делать для того, чтобы расширять и сопровождать BI-системы. У них уже есть ряд информационных панелей (Dashboard) и отчетов, разработанных для своих подразделений. Тем не менее, бизнес-пользователям практически всегда требуются различные новые информационные панели и инструменты отчетности. Пожалуй, единственным программным обеспечением, которое предоставляет пользователям максимальную гибкость в создании собственных отчетов, является Excel. В результате, даже в крупных предприятиях использование Excel для BI целей является распространенным явлением. Часто бизнес-пользователи обращаются в свои хранилища данных или BI инструменты, извлекают из них данные посредством Excel. Там они могут провести их собственный анализ и построить свои собственные отчеты.

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

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

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

Глава 3. Разработка BI-решения

3.1 Первый этап. Разработка хранилища данных.

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

3.1.1 Выделение основных сущностей

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

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

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

Итого, на данном этапе было выделено 4 факта и 14 измерений, которые покрывают базовую информационную потребность представителей учебных центров.

3.1.2 Выделение атрибутов сущностей

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

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

Затем был сформирован список выделенных атрибутов фактов. Их количество значительно меньше, нежели атрибутов измерений. Атрибуты фактов представляют собой, прежде всего, метрики (или атрибуты, которые в ходе последующего многомерного анализа можно превратить в них) и мигрированные внешние ключи связанных измерений. В данной модели внешние ключи в фактах внесены в состав первичного ключа (FK-PK). Ниже представлен список выделенных атрибутов фактов:

Как результат, на данном этапе были выделены необходимые для описания предметной области атрибуты. Атрибуты содержат скрещённое название для обозначения их в выбранной СУБД. Дополнительно каждому атрибуту был присвоен типа данных, пригодный для использования в MS SQL Server.

3.1.3 Модель хранилища данных

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

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

Иллюстрация 3.1: Модель хранилища данных на физическом уровне.

Итак, хранилище данных организовано в виде объединения нескольких схем «снежинка». Так как многие известные практики рекомендуют использовать схему «Звезда» (нежели «Снежинка»), необходимо обосновать соответствующий выбор схемы. В целом, главной причиной такой модели данных является ориентированность на конечного пользователя без знаний языков запросов: возможность конструировать различные отчеты в Excel. Чаще всего не рекомендуется строить «Снежинки» для того, чтобы не ухудшать производительность запросов. Но в данном случае данных будет не настолько много, чтобы значимо негативно влиять на производительность.

Выделяются 4 измерения «Тип мероприятия», «Форма проведения мероприятия», «Организационная форма мероприятия», «Статус мероприятия», потомком которых является измерение «Мероприятие». Эти измерения являются справочниками, данные в которых меняются редко, но иногда добавляются и совсем редко изменяются. Теоретически данные измерения можно не выделять, а непосредственно включить в измерение «Мероприятие» в качестве отдельных атрибутов. Это сделано по следующей причине. Допустим, пользователь хочет получить сводную таблицу по количеству посещений сотрудниками образовательных мероприятий в разрезе иерархии подразделений и типа мероприятия с фильтром за 2015 год. Возможна ситуация, что за данный период не было мероприятий определенного типа и, следовательно, они не отобразятся в отчете. Но чтобы пользователь имел полную картину об обучении (включая информацию о том, что данных типов мероприятий проведено не было), нужно организовать доступ ко всему списку типов. Для этого справочники были выделены в отдельные таблицы.

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

Далее описана другая причина организации связей между измерениями. Как известно, выгрузка в хранилище происходит периодически (в данном случае раз в день). Допустим, вчера было создано мероприятие А, которое проводит обучающая школа Б и посетит определенный набор сотрудников. При загрузке данных в хранилище произойдет заполнение таблицы фактов «Посещение мероприятия». Но сегодня по какой-либо причине (ошибка администратора, изменение школы и т.д.) была изменена обучающая школа у мероприятия А на школу В. В таблицу факта «Посещение мероприятия» загрузятся новые данные, так как изменилась учебная школа (а она входит в состав первичного ключа). Теперь при формировании отчетности будет отображено, что эти сотрудники посетили два мероприятия, вместо одного. Соответственно, нужно как-то «пометить», что данное мероприятие является устаревшим, а данную запись в таблице фактов учитывать не нужно. Записи таблицы фактов не подлежат удалению или изменению, так как это противоречит самой концепции хранилищ данных. Решением проблемы будет ввод в состав атрибутов измерения «Мероприятие» ссылки на измерение «Обучающая организация» и атрибута, характеризующего актуальность данных - isActive.

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

Каждое измерение имеет два одинаковых атрибута: loadDate и changeDate. Данные поля служат для реализации так называемых медленно меняющихся измерений (от англ. Slowly Changing Dimension - SCD). Для того чтобы определить тип медленно меняющихся измерений, нужно ответить на вопрос: какая глубина историчности измерений, как правило, нужна пользователям.

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

Исключением является измерение «Сотрудник». Как правило, для бизнеса важно хранить исторические данные в этом разрезе. Типичная ситуация: сотрудник проходит обучение, а потом может поменять фамилию или перевестись в другое разделение. Во всех печатных документах, связанных с прохождением обучения, будут фигурировать старое ФИО и подразделение. Крайне важно, чтобы результаты полученных отчетов на основе разрабатываемой системы совпадали с документами, поэтому данные должны быть истерическими. Для этого применяется медленно меняющееся измерение второго типа (SCD2), а также вводится суррогатный ключ и дополнительный атрибут isActive. Другими измерениями с SCD2 являются «Обучающая организация» и «Мероприятие». Здесь второй тип используется для осуществления «пометки» устаревших записей при изменении родительских сущностей, которые напрямую связаны с каким-либо фактом. Пример такой ситуации был описан ранее в ситуации с изменением обучающей организации у мероприятия.

Разработанная модель содержит факт «Отсутствия на рабочем месте», который не содержит метрики (factl...