Применение ETL-инструмента - Создание модели хранилища данных

Результатом процесса сбора и моделирования данных стали файлы в различных форматах (.txt, .xls, .xlsx, .csv). К этим файлам относятся:

    - Файл Авиакомпании. txt - файл, содержащий информацию об авиакомпаниях, включающую их наименования на русском и английском языках и их web-ресурсы. - Файл Места. csv - файл со списком регионов Российской федерации с названием федерального округа, включающего регион, и с наименованием регионального центра. - Файл dirAndType. xls - файл, относящийся к измерениям "Тип перевозки" и "Направление перевозки". Данные в Excel-файле разнесены по 2 листам книги. - Файл Объемы_перевозок. xlsx - файл, в котором содержится информация по объемам перевозок грузов с указанием года, квартала, региона, направления перевозки, типа перевозки и компании (model_company).

Суть данного этапа заключается в корректной загрузке данных из перечисленных выше файлов в хранилище данных с учетом того, что таблица фактов соединяется с измерениями через суррогатные ключи измерений [24]. Также в ETL системе Pentaho Data Integration необходимо сгенерировать временные данные и загрузить их в измерение dim_date.

Как упоминалось в 1 главе, графическая оболочка для проектирования и проверки выполнения функций Pentaho Data Integration (PDI) именуется Spoon. Эта программа позволяет реализовывать двухуровневый процесс преобразования данных и передачи их в хранилище. Первый (верхний) уровень представлен в виде Заданий (Jobs), а второй уровень состоит из Трансформаций (Transformations). Обычно при наполнении хранилища данных используется одно задание, состоящее из нескольких трансформаций. Трансформации в задании выполняются последовательно [19].

В данной части процесс обработки данных и закачки их в хранилище будет построен следующим образом:

    (1) Создание трансформации "Генерация временного измерения" (2) Создание трансформации "Регионы" (3) Создание трансформации "Направления перевозки" (4) Создание трансформации "Типы перевозки" (5) Создание трансформации "Авиакомпании" (6) Создание трансформации "Заполнение таблицы фактов" (7) Создание и запуск задания "Заполнение ХД" (8) Проверка результатов

Схема трансформации "Генерация временного измерения" представлена на иллюстрации 5.

Схема трансформации "Регионы" представлена на иллюстрации 6.

На данном изображении представлены свойства шага по загрузке данных из файла. В поле "The row number field name" (имя поле для номера строки) указано наименование столбца, который система будет автоматически добавлять последовательные номера строк. Этим действием производится генерация суррогатных ключей для таблицы измерения. То же самое действие проделывается и с остальными таблицами измерений, так как в источниках данных (файлах) суррогатных ключей нет.

Трансформация "Направления авиатранспортных перевозок" представлена на иллюстрации 7:

Трансформация "Типы перевозки":

Трансформация "Авиакомпании":

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

На данном шаге производится просмотр каждого измерения для каждой строки из файла "Объемы_перевозок. xlsx" и в соответствии и натуральным атрибутом извлекается суррогатный ключ из просматриваемого измерения. Эта операция выполняется при участии объектов PDI под названием Combination Dimension lookup/update (просмотр/обновление измерения). Cуррогатные ключи добавляются к массиву данных, и на последнем шаге из этого массива в таблицу фактов записываются только суррогатные ключи и меры.

Схема задания "Заполнение ХД" представлена на иллюстрации 11. Данный процесс относится к полной загрузке данных в хранилище данных.

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

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

После завершения процесса в журнале задания появились следующие записи:

Процесс завершился успешно. Также необходимо осуществить проверку наличия данных в самой СУБД. Для этого можно написать простой запрос [4]:

Select * from dbo. fact_transportation;

В результате выполнения запроса система вернула 1624 строки. Пример нескольких строк показан на иллюстрации 14.

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

1. Применение BI-приложения и моделей data mining.

В данной части работы будет рассмотрено применение BI-инструментов для консолидированного отображения данных [9], а также моделей Data Mining для получения прогнозов по развитию авиатранспортной системы России.

В первой главе был проведен обзор инструментов анализа данных. В качестве инструмента BI была выбрана среда Microsoft Sql Server Analysis Services (SSAS) [17]. В данной среде будет построен многомерный куб на основе базы данных, спроектированной и заполненной на предыдущих шагах.

SSAS предоставляет разработчикам возможность создания многомерных кубов при помощи Microsoft BI Development Studio. Создание куба в данной среде имеет форму проекта. В проекте имеется набор объектов, как показано на иллюстрации 15

На рисунке представлены такие объекты, как источники данных, представления источников данных, кубы, измерения, модели добычи данных и прочие.

В первую очередь необходимо определить источник данных. В данном случае необходимо организовать соединение с базой данных AirAnalysis через драйвер Ole Db.

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

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

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

Таблица 8: Описание таблиц измерений многомерного куба

Название таблицы

Атрибуты

Иерархии

Ключевое поле

Тип измерения

Dim_date

Date_id,

Date_year,

Date_quater,

Date_date

Уear - quater

Date_id

Временное измерение

Dim_company

Company_id,

Company_title_rus,

Company_title_eng,

Company_web_site

Нет

Company_id

Обычное (regular) измерение

Dim_direction

Direction_id,

Direction_title,

Direction_desc

Нет

Direction_id

Обычное (regular) измерение

Dim_place

Place_id,

Place_fed_distinct,

Place_region,

Place_center

Fed_distinct - region

Place_id

Обычное (regular) измерение

Dim_transportation_type

Tr_type_id,

Tr_type_title,

Tr_units

Нет

Tr_type_id

Обычное (regular) измерение

На этом заканчивается процесс создания многомерного куба. Далее из него можно различными способами получать интересующую пользователя информацию. Одним из таких способов является язык MDX [25]. Этот язык специально предназначен для создания запросов к многомерным кубам.

В рамках исследования стоит следующий вопрос: "Увеличивалась ли доля авиатранспортных потоков в МТУ по отношению к общему авиатранспортному потоку по стране?". На этот вопрос легко можно ответить, используя язык MDX и MS Excel. MDX-запрос имеет следующий вид:

Select {[Dim Date].[Hierarchy].[Date Year].&;[2005],[Dim Date].[Hierarchy].[Date Year].&;[2006],

[Dim Date].[Hierarchy].[Date Year].&;[2007],[Dim Date].[Hierarchy].[Date Year].&;[2008],

[Dim Date].[Hierarchy].[Date Year].&;[2009],[Dim Date].[Hierarchy].[Date Year].&;[2010],

[Dim Date].[Hierarchy].[Date Year].&;[2011]} on Columns,

{[Dim Place].[Place Region].&;[Московский регион],

[Dim Place].[Place Region].[All]

} on rows

From [Air Analysis]

Результат выполнения запроса показан на иллюстрации 18.

В первой строке представлены ежегодные суммарные объемы авиаперевозок для Московского региона, а во второй -- для всех регионов, рассматриваемых в исследовании.

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

По графику хорошо видно, как изменяется доля МТУ за период с 2005 по 2011 год. После 2008 года доля МТУ значительно понизилась. Скорее всего, это было вызвано экономическим кризисом. Однако доля МТУ выросла с 0,21 до 0,22 за указанный период. Это говорит о том, что авиатранспортная система России за последнее десятилетие стала более централизованной и несбалансированной.

Следующим механизмом, при помощи которого из многомерного куба можно получить полезные данные, - это модели data mining (добыча данных) [9]. В SSAS есть встроенный набор моделей, таких как "Временные ряды", "Алгоритм нейронной сети", "Линейная регрессия", "Кластеризация последовательностей" и прочие. В данной работе поставлено две задачи, которые можно постараться решить при помощи моделей data mining. Во-первых, это прогнозирование объемов авиаперевозок до определенного года (в исследовании: до 2021 года), а, во-вторых, это выявление регионов, которые наиболее предрасположены к развитию на их территории транспортных узлов или "хабов". Первая задача будет решаться при помощи алгоритма временных рядов, а вторая задача будет решаться с использованием алгоритма кластеризации.

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

Прогнозные данные показаны на графике пунктиром. По рисунку видно, что тенденция роста объема авиатранспортных потоков сохранилась. Также видно, что с 2013 по 2017 год увеличиваются сезонные флуктуации, но к 2018 году система предсказывает значительное сглаживание таких колебаний. В целом, модель выдает прогнозное значение на 2 квартал 2021 года, равное 120 000 тонн перевезенных грузов на территории России. Значение этого показателя в первом квартале 2005 года равнялось чуть более 30 000 тонн. То есть модель спрогнозировала 4-х кратный рост объемов авиатранспортных потоков.

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

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

Москва:

Московский регион

Хабы (1 приоритет):

Ленинградский регион, Тюменская область

Хабы (2 приоритет):

Ханты-Мансийский автономный округ (Югра)

Хабы (3 приоритет):

Краснодарский край, Республика Татарстан, Свердловская область

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

Таблица 9: Регионы, города и их числовые обозначения

Название региона

Приоритет

Название города

Номер на карте

Ленинградский регион

1

Санкт-Петербург

1

Тюменская область

1

Тюмень

2

Ханты-Мансийский автономный округ (Югра)

2

Сургут

3

Краснодарский край

3

Краснодар

4

Республика Татарстан

3

Казань

5

Свердловская область

3

Екатеринбург

6

Также данные города отмечены на карте России [21] номерами соответственно таблице для большей наглядности (иллюстрация 22).

К сожалению, система не выделила ни одного региона, находящегося на Дальнем Востоке или в Западной Сибири.

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

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

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

Похожие статьи




Применение ETL-инструмента - Создание модели хранилища данных

Предыдущая | Следующая