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

success (Execute SQL Task) - данный элемент содержит T-SQL процедуру, которая формирует запись в таблицу лога logExecution об успешном завершении задачи по загрузке данных в хранилище. Предварительно производится подсчет строк, которые были перенаправлены в лог logRedirectedRow в процессе выполнения ETL-проекта. Данная информация также вносится в строку результатов выполнения выгрузки.

Для регистрации возникновения критических ошибок, которые все-таки привели к прерыванию процесса загрузки, в проект был добавлен обработчик события onError. Данный обработчик представляет собой задачу выполнения SQL-скрипта error (Execute SQL Task), который формирует запись в таблицу лога logExecution об остановке выполнения проекта.

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

Для решения данной проблемы было решено использовать файл конфигурации проекта (Package Configuration). Файл конфигурации проекта является опциональным и системным для SSIS-проекта и имеет разрешение .dtsConfig. Содержимое файла организовано в соответствии XML-разметкой. Данный файл с названием wt_conf был создан в корневой папке SSIS-проекта и в него были включены OLE DB подключения (wtDB и wtDW) и переменные уровня проекта, которые содержат строки подключения к файлам в файловой системе.

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

Иллюстрация 3.8: Строка подключения в файле конфигурации.

Дополнительно, для обеспечения требования универсальности был создан второй ETL-проект, который ориентируется на основную СУБД-источник Oracle 11g, а не MS SQL Server. В целом, он в точности повторяет представленную модель. Отличия заключаются в источниках данных: вместо OLE DB Source используется Oracle Source. Его графическое обозначение представлено на иллюстрации 3.9. Однако важно отметить, что для того, чтобы организовать доступ к Oracle СУБД на сервере с ETL-проектом должна быть установлена специальная для этого утилита - «Microsoft connectors v2.0 for Oracle».

Иллюстрация 3.9: Oracle источник данных.

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

3.3 Третий этап. Разработка OLAP-куба

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

Во второй главе данной работы для реализации OLAP-куба был аргументировано выбран компонент SQL Server Analysis Services. Данный продукт представляет удобный и интуитивно понятный интерфейс для разработки куба. На его базе был создан проект и назван wtDW_SSAS.

Первым шагом при разработке OLAP-куба является процесс определения источника(ов) данных (Data Source). В данном случае источником будет разработанное хранилище данных, поэтому в SSAS-проекте с ним было организовано соединение.

Затем формируется представление данных (Data Source Views). Обычно промышленные хранилища данных содержат огромное количество сущностей, но для проведения специального анализа на основе OLAP-куба часто требуются только определённый набор тематически связанных таблиц (отдельная витрина). Для того чтобы определить только необходимые сущности, используется представление данных. В данный SSAS-проект были добавлены все таблицы, кроме тех, которые предназначены для ведения лога.

Следующим шагом создаются и разворачиваются на сервере необходимые для куба измерения. В процессе их создания выделяются важные для последующего анализа иерархии. Далее представлены основные выделенные иерархии. Для большинства измерений было установлено значение `hidden' свойства UnknownMember для запрещения отображения неопределённых значений измерений.

В измерении «Мероприятие» было выделено четыре основных иерархии, позволяющие сгруппировать названия мероприятий в зависимости от различных пользовательских и системных справочников: «Форма проведения», «Организационная форма проведения», «Статус мероприятия», «Тип мероприятия». Иллюстрация 3.10 демонстрирует эти иерархии.

Иллюстрация 3.10: Иерархии измерения «Мероприятие».

В измерении «Календарь» была выделена наиболее востребованная в сфере корпоративного обучения иерархия - dateHierarchy (илл. 3.11-а), которая следующие уровни детализации: год -> квартал -> название месяца-> день. В измерении «Подразделение» была также выделена одна иерархия - disbandedHierarcy, группирующая подразделения по статусу расформированности (илл. 3.11-б). Наконец, в измерении «Сотрудник» были выделены две иерархии, группирующие всех сотрудников по рабочему статусу и полу (илл. 3.11-в).

А)Б)В)

Иллюстрация 3.11: Выделенные иерархии.

Все рекурсивные таблицы по умолчанию уже содержат встроенную иерархию (parent-child hierarchy). Однако для корректного отображения значений данных иерархий было установлено свойство `NonLeafDataHidden' свойства MemberWithData, позволяющее скрыть дублирующиеся значения. Дополнительно, были произведены соответствующие настройки для того, чтобы в иерархии отображались названия информационных объектов (названия категорий или подразделений), а не идентификационные коды.

Следующим этапом является непосредственная разработка куба данных на основе построенных измерений и иерархий. В процессе его создания указываются таблицы фактов и выделяются метрики. В данном случае метриками являются все атрибуты, не входящие в состав первичного ключа, таблиц фактов. Автоматически для каждого факта генерируется еще одна метрика: наличие записи в таблице. Данная метрика отражает посещение сотрудником одного мероприятия, прохождение одно курса и так далее. Также были разработаны так называемые вычисляемые члены (Calculated Members) для некоторых фактов. Например, нет смысловой нагрузки в агрегации количества попыток сотрудников, затраченных на прохождение курса, в разрезе иерархии подразделений. В данном случае интересе среднее количество попыток. Иллюстрация 3.12 демонстрирует схему сгенерированного куба. На ней желтым цветом выделены таблицы фактов, а синим - измерения.

Иллюстрация 3.12: Разработанный OLAP-куб.

После осуществления всех описанных выше шагов OLAP-куб был успешно развернут и обработан на соответствующем экземпляре Analysis Services SQL Server'а. В результате в данном экземпляре была создана база данных с названием, совпадающим с названием проекта - wtDW_SSAS.

С помощью специальной функции SQL Server Management Studio был сгенерирован скрипт создания этой базы данных и назван wtDW_SSAS.xmla. Данный программный код можно выполнять на любом экземпляре Analysis Services, предварительно поправив (при необходимости) в XML-разметке скрипта строку соединения с источником данных. После его выполнения будет автоматически создана структура только что разработанного куба со всеми измерениями. На основе созданной базы данных с помощью проекта «Импорт с сервера» в Visual Studio будет воссоздан SSAS-проект с кубом данных. Таким образом, можно заключить SSAS-проект, разработанный на третьем этапе, также удовлетворяет требованию универсальности. Данное требование обеспечивает возможность развертывания во всех организациях, использующих продукт WebTutor.

3.4 Развертывание и тестирование решения на реальных данных

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

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

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

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

Иллюстрация 3.14: Сводный отчет по очному обучению сотрудников

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

Excel представляет возможность изменить детализацию отчета, сворачивая (drill-up) и разворачивая (drill-down) необходимые уровни иерархий. Данное действие выполняется при нажатии на кнопку (+). При нажатии на данную кнопку в иерархии календаря стоимость посещения мероприятия сотрудниками детализируется в соответствующий год до кварталов; далее до месяцев и дней.

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

Иллюстрация 3.15: Сводный отчет по организации мероприятий

Часто сотрудникам назначается один и тот же курс один раз в определённый период (например, раз в 2 года). Данные курсы призваны освежать знания сотрудников, связанные с их профессиональной деятельностью. Для банковской сферы такой тематикой курсов будет: противодействие легализации (отмыванию) доходов, полученных преступным путем, и финансированию терроризма (ПОД ФТ). Возможно построение отчета, отражающего среднее количество затраченных попыток на прохождение курса, в разрезе иерархий и подразделений календаря. Отчет представлен на иллюстрации 3.16.

Иллюстрация 3.16: Сводный отчет по пройденному курсу.

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

Итак, в данной главе были последовательно пройдены все этапы разработки: от проектирования до непосредственной реализации компонентов решения. Результатом данной главы стал готовое дополнение для системы WebTutor, удовлетворяющее всем поставленным требованиям.

Иллюстрация 3.17 Иллюстрация 3.18

Заключение

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

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

Во второй главе была исследована концепция аналитической обработки данных в реальном времени противопоставленная концепции оперативной обработки транзакций. Затем были последовательно разобраны составляющие концепции Business Intelligence: сбор данных, их анализ и формирование отчетности. На основе теоретического базиса и описания предметной области был сделан вывод об использования подхода Ральфа Кимбалла для физического проектирования хранилища данных и оптимальности использования продуктов экосистемы SQL Server'a от компании Microsoft.

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

Разработанное дополнение отличает следующий набор особенностей:

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

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

Аккумулирование данных из нескольких источников. Система собирает и согласует данные из нескольких разнородных источников.

Универсальность. Разработанное дополнение может быть внедрено на серверах любых организаций, использующих систему WebTutor, при условии наличия установленного SQL Server'а редакции Business Intelligence.

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

Отдельным пунктом стоит выделить «возможность доработки». Компоненты решения разработаны на базе экосистемы SQL Server'а и требуют её наличие. А значит, в случае необходимости могут быть доработаны потенциальными пользователями в любой момент времени. Примером такой доработки может служить разработка автоматического отправления уведомления со сформированным отчетом на корпоративную почту ответственных лиц. Данный функционал был разработан для тестовом сервере государственной корпорации на базе SSIS-проекта с помощью элемента ScriptTask. Данный элемент содержит разработанный программный код на языке C#. В нем задано smtp-подключение, используемое в организации. Иллюстрация 4.1 содержит пример отправляемого сообщения со вложенным. xlsx отчетом.

Иллюстрация 4.1: Пример оповещения.

Также существуют перспективы дальнейшего развития данного дополнения. Они заключаются в отдельной разработке web-интерфейса для визуализации данных из OLAP-куба в качестве дополнения к MS Excel. Его, преимуществом, прежде всего, будет возможность гибкого разграничения прав на формирование различных отчетов, использование измерений, метрик, а так же синхронизации ролей доступа с самой системой WebTutor. На данный момент автором уже разработана front-end составляющая данного интерфейса (илл. 4.2).

Иллюстрация 4.2: web-интерфейс.

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

Список использованной литературы

1. Aspin, A. (2012) SQL Server 2012 Data Integration Recipes: Solutions for Integration Services and Other ETL Tools (Expert's Voice in SQL Server) 2012th Edition. Apress.

2. Codd E. F., Codd S. B., and Salley C. T. (1993) Providing OLAP to User-Analysts: An IT Mandate.

3. Elkins D., Pinder D. (2015) E-Learning Fundamentals: A Practical Guide.

4. Evelson, Boris (21 November 2008). "Topic Overview: Business Intelligence"

5. Inmon, W. (2008) 2.0 - Architecture for the next generation of data warehousing. Amsterdam: Elsevier Press.

6. Inmon, Bill. (1992) Building the Data Warehouse. Wiley.

7. J. Cross, I. Hamilton. (2002) The DNA of eLearning. Internet Time Group.

8. Kimball, R. (1996) The data warehouse toolkit. New York: Wiley.

9. P. Turley, J. Kasprzak, S. Cameron, S. Iizuka, and P. Guzman. (2007) SQL Server 2005 Integration Services Step by Step. Microsoft Press, Washington.

10. Westerlund P. (2008) Business Intelligence: Multidimensional Data Analysis. Master Thesis in Computing Science.

11. Ponniah, P. (2001) Data warehousing fundamentals: a comprehensive guide for IT professionals. New York: John Wiley & Sons, Inc.

12. Rosemary Harrison. (2005) Learning and Development. CIPD Publishing.

13. S. Chaudhuri and U. Dayal. (1997) An Overview of Data Warehousing and OLAP Technology. SIGMOD Rec.

14. Tannenbaum, S I; Yukl, G (January 1992). Training and Development in Work Organizations. Annual Review of Psychology

15. Thomsen E. (1997) OLAP Solutions: Building Multidimensional Information Systems, 2nd Edition. John Wiley & Sons.

16. George, S. Inmon vs. Kimball: Which approach is suitable for your data warehouse? Электронный доступ: http://searchbusinessintelligence.techtarget.in/tip/Inmon-vs-Kimball-Which-approach-is-suitable-for-your-data-warehouse (дата обращения 23.12.2015)

17. Global Corporate E-learning Market to Reach over USD 31 Billion by 2020. Электронный доступ: (дата обращения 23.03.2016)

18. Диго С. М. Базы данных. Проектирование и создание. Учебно-методический комплекс, Москва: Изд. Центр ЕАОИ

19. Каганов. В. (2011) Информационные технологии как инструмент повышения конкурентоспособности образовательных программ в системе корпоративного обучения. Синергия.

20. Кроль Л., Пасс Ю. (2004) Развитие организации и HR-менеджмент. Класс

21. Нильсен Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. - М.: ООО «И. Д. Вильямс», 2008. - 1232с.

22. Сарка Д., Лах М. Microsoft® SQL Server® 2012. Реализация хранилищ данных. Учебный курс Microsoft (+CD)

23. Туманов В.E. Проектирование хранилищ данных для систем безнес-аналитики: учебное пособие - М.: Интернет-Университет Информационных Технологий: БИНОМ, 2011 - 615 стр.

24. Федоров А., Елманова Н. (2002). Введение в OLAP- технологии Microsoft.М.: Диалог МИФИ.

25. Харинатх С., Кэррол М. Microsoft SQL Server Analysis Services 2008 и MDX для профессионалов. Пер. с англ. - М.: ООО «И. Д. Вильямс», 2010. - 1074с.

26. Официальный сайт компании Внешэкономбанка. Электронный доступ: http://www.veb.ru/ (дата обращения 20.05.2016)

27. Официальный сайт компании Oracle. Электронный доступ: http://www.oracle.com/ (дата обращения 20.02.2016)

28. Официальный сайт компании WebSoft. Электронный доступ: http://www.websoft.ru/ (дата обращения 12.02.2016)

29. Официальный сайт консалтинговой компании Gartner. Электронный доступ: http://www.gartner.com (дата обращения 14.12.2016)

30. Сообщество разработчиков Microsoft. Электронный доступ: http://msdn.microsoft.com (дата обращения 23.03.2016)

31. Справочная система SP-XML. Электронный доступ: http://docs.datex.ru/ (дата обращения 20.11.2015)

Размещено на Allbest.ru