ETL процедура. Загрузка таблиц измерений

Записаться

Подготовка проекта ETL

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

Microsoft предлагает комплексное решение: SQL Server может быть и OLTP-источником (Online Transaction Processing — оперативная обработка транзакций), и OLAP-решением (Online Analytical Processing — аналитическая обработка). В примере база-источник и хранилище находятся на одном сервере. На практике их часто разносят по разным серверам из-за нагрузки, безопасности и конфликтов доступа.

Код и объекты удобно создавать в Visual Studio. Компонент SQL Server Data Tools (SSDT) входит в Visual Studio начиная с 2019; в версии 2017 его нужно устанавливать отдельно. Создаём проект: File → New Project → Business Intelligence → Integration Services Project. Analysis Services нужен для аналитики и OLAP-кубов, Reporting Services — для отчётов, а нам нужен Integration Services для ETL.

Верхнеуровневый план

В проекте создаём задачу выполнения SQL и четыре потока данных:

• очистка;
• загрузка сотрудников;
• загрузка товаров;
• загрузка адресов;
• загрузка фактов.

Сначала выполняется очистка. Затем загружаются измерения сотрудников, товаров и адресов. Эти три измерения независимы, поэтому их загрузка может идти параллельно. Загрузка фактов начинается только после завершения всех трёх, потому что первичный ключ фактов состоит из ключей измерений.

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

Очистка

Используем Execute SQL Task (задача выполнения SQL). В ней указываем SQL-запрос на удаление. Сначала очищаем Fact Sales, потому что она связана с измерениями. Затем очищаем DimAddress, DimEmployee и DimProduct. После этого хранилище пусто. Опцию подготовки запроса ставим в False: для удаления предварительная подготовка не нужна.

Загрузка сотрудников

Создаём Data Flow Task (поток данных). Источник — база AdventureWorks 2017, назначение — хранилище DV-demo. Подключение: сервер localhost, авторизация Windows. Проверяем соединение.

Источник данных — SQL Command с запросом по сотрудникам. Проверяем запрос и превью. Назначение — таблица DimEmployee. Проверку ограничений отключаем, чтобы не тратить ресурсы. Сопоставляем поля.

Возникает проблема: в источнике есть Gender, но в DimEmployee не было поля пола. Добавляем в DimEmployee столбец Gender типа nchar(1), как в источнике. Затем снова сопоставляем.

Вторая проблема: FullName в цели имел nvarchar(50), а источник может дать до 150 символов: FirstName, MiddleName, LastName — по nvarchar(50) каждый. Увеличиваем FullName до 150, затем до 200, чтобы гарантированно вместить худший случай. После обновления метаданных повторяем сопоставление. Теперь поля совпадают.

Загрузка товаров и адресов

Товары: источник — Product DB, назначение — DimProduct. Выбираем SQL-команду, проверяем превью, отключаем проверку ограничений, сопоставляем поля один к одному.

Адреса: источник — база с адресами, назначение — DimAddress. Используем SQL-запрос с адресами вплоть до города. Проверяем, отключаем проверку ограничений, выполняем полное сопоставление полей.

Переход к загрузке фактов

После очистки и загрузки трёх измерений остаётся загрузить факты продаж. Их нужно связать с уже загруженными измерениями. После настройки этого шага ETL-процедуру можно выполнить и увидеть автоматическое заполнение хранилища.

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

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

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

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