ETL процедура. Создание табличной структуры Хранилищ данных

Записаться

Создание целевого хранилища

Продолжаем работу над хранилищем. Теперь нужно создать целевые таблицы, в которые будет попадать информация из базы-источника. Фактически речь идёт о хранилище данных (data warehouse).

В измерении доставки переименуем поле провинции в ProvinceName, чтобы название было понятным.

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

В демонстрационной базе AdventureWorks 2017 несколько предметных областей уже сведены в одну базу: персоны, HR, продажи и другие данные. На практике это были бы отдельные базы: HR-информация, финансы, бухгалтерия и т. п. Их пришлось бы соединять запросом к нескольким источникам.

Создаём базу DW Demo — демонстрационное хранилище данных (data warehouse). При создании выбираем максимально простое логирование (logging), чтобы база не занимала много места.

Измерение сотрудника

Создаём первую таблицу измерения — DimEmployee. Это измерение (dimension) сотрудника.

Сначала создаём внутренний идентификатор. Это не исходный идентификатор сотрудника из источника, а суррогатный ключ (surrogate key) хранилища. Он нужен для быстрой и удобной записи данных. Исходный ключ сотрудника сохраняем отдельно: он понадобится для связи с источником и трассировки.

Поля таблицы:

• EmployeeID — внутренний идентификатор, тип int, первичный ключ (primary key);
• BusinessEntityID — идентификатор сотрудника в исходной системе;
• имя сотрудника — nvarchar(50);
• BirthDate — дата рождения, тип date;
• DepartmentID — идентификатор департамента;
• DepartmentName — название департамента, nvarchar(50);
• StartDate и EndDate — даты перемещения по департаментам, тип date.

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

Измерение продукта

Следующая таблица — DimProduct, измерение продукта.

Поля таблицы:

• ProductID — внутренний суррогатный ключ, int, первичный ключ;
• исходный ProductID — int;
• ProductName — nvarchar(50);
• ProductCategoryID — int;
• CategoryName — nvarchar(50);
• SubcategoryName — nvarchar(50);
• ModelName — nvarchar(50);
• Color — nvarchar(15);
• ListPrice — тип money;
• StartDate и EndDate — даты истории цены, тип datetime.

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

Измерение адреса доставки

Создаём третье измерение — DimAddress, то есть измерение адреса доставки.

Поля таблицы:

• AddressID — внутренний суррогатный идентификатор, int;
• CountryRegionCode — nvarchar(3);
• CountryRegionName — nvarchar(50);
• StateProvinceCode — код провинции;
• StateProvinceID — int;
• ProvinceName — название провинции, nvarchar(50);
• City — город, nvarchar(30).

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

Таблица фактов

Теперь нужна центральная таблица — таблица фактов (fact table). Создаём FactSales — факты продаж.

Поля таблицы:

• EmployeeID — int;
• ProductID — int;
• AddressID — int;
• Date — дата, измерение времени;
• Amount — денежная сумма, тип money;
• Quantity — количество, тип int.

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

Схема «звезда»

Выносим все созданные таблицы на диаграмму и связываем их. В центре находится FactSales, к ней присоединяются измерения: DimEmployee, DimProduct, DimAddress. Получается схема «звезда» (star schema).

Каждая таблица измерений собирается из данных первичного источника. Затем заполняется центральная таблица фактов. После этого можно переходить к написанию ETL-процедуры (Extract, Transform, Load — извлечение, преобразование, загрузка).

Краткие итоги

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

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

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

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

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