Трансформация в схему «Звезда»

  10 минутное чтение  

2. Трансформация в схему «Звезда»

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

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

Модель данных - это объединение определенной группы таблиц хранилища. Например, объединение таблиц какого-либо бизнес-процесса.

Управление моделями данных реализовано через настройку документа Трансформации в схему «Звезда». Документ трансформации позволяет управлять процессом внесения изменений в структуру хранилища данных.

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

2.1. Создание модели данных

Открыть пункт меню «Меню: Управление хранилищем \ Модели данных» (см. Рисунок 1, 1, 2 ).

Для создания новой модели данных необходимо нажать кнопку [Создать] (или «Создать новый элемент копированием текущего») (см. Рисунок 1, 3 ).

Рисунок 1. Создание модели данных

2.2. Настройка модели данных

Для настройки модели данных необходимо

  • выбрать/создать базу данных (см. Рисунок 2, 1 );
  • выбрать проект (см. Рисунок 2, 2 );
  • ввести название модели (см. Рисунок 2, 3 );
  • заполнить описание модели (см. Рисунок 2, 4 ).

Рисунок 2. Настройка модели данных

2.3. Создание документа трансформации

Открыть пункт меню «Меню: Управление хранилищем \ Трансформация таблицы в схему звезда» (см. Рисунок 3, 1, 2 ).

Для создания нового документа необходимо нажать кнопку [Создать] (или «Создать новый элемент копированием текущего») (см. Рисунок 3, 3 ).

Рисунок 3. Создание документа трансформации

2.4. Настройка документа трансформации

Для настройки документа трансформации необходимо:

  • выбратьсоздать базу данных (см. Рисунок 4, 1 );
  • создать/выбрать правило трансформации (см.Рисунок 4, 2 );
  • создать/выбрать модель данных (см. Рисунок 4, 3 );
  • выбрать режим загрузки (см.Рисунок 4, 4 ).

Детальнее о режимах загрузки моделей данных см. Режимы загрузки модели.

Рисунок 4. Настройка документа трансформации

2.4.1. Настройка таблиц исходных данных

Добавление таблиц исходных данных (src-таблицы) осуществляется на вкладке «Настройки таблиц источников» в документе трансформации.

Рисунок 5. Настройка таблиц исходных данных

2.4.2. Настройка таблиц стэйджинга

Настройка таблиц стэйджинга (stg-таблица) осуществляется на вкладке «Настройки таблиц стэйджинга» в документе трансформации.

Вкладка состоит из 3 разделов: «Список таблиц стэйджинга», «Поля исходной таблицы», «Поля таблицы стэйджинга».

Для настройки таблицы стэйджинга необходимо:

  • перейти на вкладку «Настройка таблиц стэйджинга» (см. Рисунок 6, 1 );
  • выбрать/добавить таблицу стэйджинга (см.Рисунок 6, 2);
  • выбрать в левом блоке полей те поля, которые необходимо создать в таблице (см. Рисунок 6, 3 );
  • нажать кнопку переноса полей в правую область (см. Рисунок 6, 4 ).

Рисунок 6. Настройка таблиц стэйджинга

2.4.3. Настройка таблиц фактов

Настройка таблиц фактов (fct-таблица) осуществляется на вкладке «Настройки таблиц фактов» в документе трансформации.

Вкладка состоит из 3 разделов: «Список таблиц фактов», «Поля таблицы стэйджинга», «Поля таблицы фактов».

Для настройки таблицы фактов необходимо:

  • перейти на вкладку «Настройка таблицы фактов» (см.Рисунок 7, 1 );
  • выбрать/добавить таблицу фактов (см.Рисунок 7, 2 );
  • выбрать в левом блоке полей те поля, которые необходимо создать в таблице (см.Рисунок 7, 3 );
  • нажать кнопку переноса полей в правую область (см.Рисунок 7, 4 ).

Рисунок 7. Настройка таблиц фактов

2.4.4. Настройка таблиц справочников

Настройка таблиц справочников (dim-таблица) осуществляется на вкладке «Настройки таблиц справочников» в документе трансформации.

Для настройки таблицы справочника необходимо:

  • перейти на вкладку «Настройка таблиц справочников» (см.Рисунок 8, 1 );
  • выбрать/добавить таблицу (см.Рисунок 8, 2 );
  • ввести имя таблицы (см.Рисунок 8, 3 );
  • выбрать тип кортежа (см.Рисунок 8, 4 );
  • выбрать в левом блоке полей те поля, которые необходимо создать в таблице (см.Рисунок 8, 5 );
  • нажать кнопку переноса полей в правую область (см. Рисунок 8, 6 ).

Рисунок 8. Настройка таблиц справочников

Для настройки ключей справочника необходимо:

  • перейти на вкладку «Таблица ключей справочника»
  • выбрать/добавить ключ (см.Рисунок 9, 1 )
  • в левой области выбрать поля и нажать на кнопку переноса в правую область (см.Рисунок 9, 2 ).

Рисунок 9. Настройка ключей справочника

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

  • нажать на кнопку [Выбрать существующую таблицу справочника] на форме «Редактирование справочника» и выбрать справочник (см. Рисунок 10, 1 );
  • выбрать режим соединения по записям (см. Рисунок 10, 2);
  • выбрать режим создания новых записей (см. Рисунок 10, 3 );
  • выбрать режим создания таблицы вариантов (см. Рисунок 10, 4 ).

Рисунок 10. Настройка повторного использования справочника

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

2.5. Запись и проведение документа трансформации

Для записи созданных настроек необходимо нажать на кнопку [Записать] (см. Рисунок 11, 1 ).

Для проведения документа трансформации необходимо нажать на кнопку [Провести] (см.Рисунок 11, 2 ).

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

Для просмотра полученных сценариев необходимо перейти на вкладку «Сценарии» (см.Рисунок 11, 3 ) и выбрать сценарий для просмотра.

Рисунок 11. Проведение документа трансформации

Шаги сценария сгруппированы в логические блоки процесса трансформации исходной таблицы в целевую связку таблиц фактов и справочников (см.Рисунок 12).

Рисунок 12. Дерево сценария обработки данных при трансформации

Структура сценария трансформации в схему «Звезда» в режиме «Пересоздание»:

  • Удаление таблиц (начало). На этом этапе происходит удаление всех таблиц создаваемой модели данных.
  • Первичный слой – сервисные шаги (начало). На этом этапе создаются и наполняются сервисные таблицы, которые хранят информацию о периодах загрузки данных, а также время начала и завершение выполнения сценария обработки данных.
  • Первичный слой. На этом этапе создаются и наполняются таблицы стэйджингого слоя модели данных, а также применяются правила преобработки исходных данных.
  • Ядро – справочники. На этом этапе создаются таблицы справочников. Алгоритм создания справочника зависит от выбранного типа кортежа (например, в режиме смешанного кортежа применяются правила постобработки данных, которые позволяют повысить качество справочной информации)
  • Ядро – факты. На этом этапе создаются таблицы фактов. Таблицы стэйджингого слоя соединяется с таблицами справочниками, для формирования внешних ключей.
  • Первичный слой – сервисные шаги (завершение). На этом этапе сервисные таблицы наполняются информацией о завершении процесса трансформации.
  • Ядро – связи. На этом этапе создаются связи между таблицами модели.
  • Удаление таблиц (завершение). На этом этапе происходит удаление временных таблиц создаваемой модели данных. Временные таблицы используются для хранения промежуточных результатов трансформации исходных данных. Например, для расчета аналитических показателей для выявления дублей по настроенным бизнес ключам при применении правил обработки данных.
  • Метаданные. На этом этапе происходит импорт метаданных созданной модели в справочник структуры БД, а также происходит созданиеобновление ER-диаграммы в справочнике моделей данных.

2.6. Запуск трансформации в схему «Звезда»

Запуск сценария обработки данных осуществляется на вкладке «Основное» (см. Рисунок 13, 1 ), кнопка [Выполнить все шаги сценария] (см. Рисунок 13, 2 )

Рисунок 13. Запуск сценария обработки данных при трансформации

Другой способ запустить сценарий на вкладке «Шаги сценария» (см. Рисунок 14, 1 )

Выделить группу «Шаги сценария» (см. Рисунок 14, 2 ) и нажать кнопку [Выполнить] (см.Рисунок 14, 3 ).

Рисунок 14. Запуск сценария на вкладке «Шаги сценария»

После запуска сценария обработки данных открывается окно «Исполнение сценария»

Рисунок 15. Окно исполнения сценария

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

Рисунок 16. Лог выполнения сценария

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

Рисунок 17. ER-диаграмма модели данных

Трансформация в схему «Звезда» (дополнительно)

Режимы загрузки модели

Документ трансформации в схему «Звезда» поддерживает несколько режимов загрузки данных в модель:

  • Пересоздание. В этом режиме модель данных после каждого запуска сценария обработки данных создается заново. При этом удаляются все объекты модели кроме ml-таблиц (таблицы в которых хранится информация о настройках выбора записей справочника при наличии дублей по базовому ключу).
  • Инкрементальная загрузка. В этом режиме модель дополняется новыми данными полученными из источников. Порция новых данных определяется по временным меткам, сохраняемым в служебном поле TIMESTAMP z-таблиц при выгрузке данных из системы источника.

Режим инкрементальной загрузки требует наличия служебных (аудитных) полей в таблицах системы источника, для того чтобы была возможность настроить SQL-запросы в правилах выгрузки для получения новых порций данных. При отсутствии служебных полей с информацией о временных метках появления записей в системе источнике требуется экспертная настройка алгоритмов определения новых порций данных (так называемая «дельта»).

Правила предобработки данных

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

Для того чтобы создать новое правило необходимо:

  • создать правило обработки полей с текстом правила, где «#FIELD\_NAME#» шаблон наименования поля, в процессе генерации текста шага этот шаблон будет заменен на наименование поля;
  • при настройке stg-таблицы выделить поле и нажать кнопку [Редактировать правила], в открывшемся окне добавить правило обработки (см. Рисунок 18)

Другие примеры правил обработки:

  • подавление первого слова в строке «substring("#FIELD\_NAME#", charindex('' '', "#FIELD\_NAME#") + 1, len("#FIELD\_NAME#"))», может быть использовано для анализа поля содержащего ФИО, например, после подавления первого слова, проведя повторную агрегацию значений по урезанной строке можно выявить случаи смены фамилии или наличие опечатки;

  • обработка NULL-значений «coalesce("#FIELD\_NAME#", ''[не определено]'')», применяется для подготовки данных.

Рисунок 18. Настройка правил предобработки данных

Типы кортежей при создании справочников

Документ трансформации в схему «Звезда» поддерживает следующие типы кортежей при создании справочников:

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

Поддерживается выбор значения по показателям:

  • кол-во строк (меньше/больше), т.е. производится расчет частоты с которой встречается то или иное значение;

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

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

Настроечные ml-таблицы при этом типе кортежа не создаются.

Настройка повторного использования справочников

При настройке повторного использования справочников поддерживаются несколько режимов:

  • Режим создания новых записей. При выборе режима «Без создания новых записей» загрузка данных осуществляется так что при отсутствии необходимого элемента в повторно используемом справочнике внешний ключ на него в fct-таблице проставляется как фиктивный (значение -1). При выборе режима «С созданием новых записей» отсутствующие элементы в справочнике добавляются в процессе загрузки данных.
  • Режим соединения по записям. (внутреннее или внешнее соединение)
  • Режим суффикса поля внешнего ключа. Когда необходимо в одной таблице фактов реализовать повторное использования справочника несколько раз, режим суффикса поля внешнего ключа позволяет управлять наименованием поля внешнего ключа, при выборе режима «Автоматический» - суффикс наименования поля создается с использованием порядковой нумерации положения справочника на вкладке «Настройка таблиц справочников» (при наличии нескольких внешних ключей на справочник m1d1 из таблицы фактов m2f1, поля внешних ключей будут иметь следующие наименования m1d1_id_1 и m1d1_id_2). Примерами структур с двойным повторным использованием могут быть «текущий и предыдущий статус», «начальная и конечная точки доставки товара», «адрес регистрации и адрес для информирования», т.е. факт ссылается на один и тот же справочник несколько раз, это приводит к необходимости следить за метаданными целевой таблицы фактов, чтобы не допускать повтора наименований полей. При выборе режима «Пользовательский» есть возможность задать суффикс вручную (например, m1d1_id_prev и m1d1_id_next) заполнив поле «Суффикс поля внешнего ключа». В режиме «Без суффикса» внешний ключ создается без добавления суффикса в наименование поля (например, m1d1_id).

Префиксы

Префикс названия объектов хранилища данных состоит из буквенной части и числовой. Числовая часть ведется в справочнике «Максимальные номера» («Управление хранилищем» -> «Сервис»)

Буквенная часть префикса, на примере модели кадрового учета:

  • m – модель (например, «m3_Кадровый учет» – модель объединяющая таблицы по бизнес процессу кадрового учета)
  • z – первичные данные (например, « z_Назначения»)
  • s – stg-таблица (например, « m3s1_z_Назначения» – первая stg-таблица в третьей модели с данными о кадровых перемещениях)
  • f – fct-таблица (например, « m3f1_Назначения» – таблица фактов с собственными атрибутами и показателями)
  • d – справочник (например, « m3d1_Должность» – первый справочник с атрибутами должностей)
  • v – пометка справочника вариантов (например, « m3d1v_Должность» – таблица вариантов первого справочника третьей модели)
  • ml – пометка вспомогательной таблицы настройки выбора записей справочника (например, « m3d1v_Должность_ml » –таблица настройки выбора записей)
  • k – номера ключа при применении правил обработки записей справочников (например, « m3d1v_k1_КраткоеНаименование » – таблица значений поля КраткоеНаименование при обработке записей по базовому ключу). Примечание, базовой ключ – это первый ключ справочника.