ETL процедура. Загрузка таблицы фактов. Часть 2

Записаться

Внимание!

Работа по созданию OLAP кубов и проведению анализа данных требует полного функционала SQL Server. Для этого есть две редакции: developer и enterprise. Версия Developer бесплатная и рекомендована для образовательных целей. Enterprise версия бесплатно предоставляется на период 180 дней. 

Обе версии можно скачать по ссылке https://www.microsoft.com/ru-ru/sql-server/sql-server-downloads. 

Возможная ошибка

Если у  вас уже создана своя база данных Cinema, то восстановление из резервной копии с именем существующей базы данных не возможно. В этом случае необходимо удалить свою БД с таким названием (можно сделать резервную копию перед удалением на всякий случай), а потом уже разворачивать предоставленную в курсе версию.

1. Уточняющий запрос по продукту и файлы ошибок

Второй уточняющий запрос посвящён продукту. Система предупреждает: указано, что строки без пары нужно выгружать в отдельный файл, но сам файл не выбран. Поэтому выбираем файл для ошибок. Плоский файл (flat file) здесь не нужен; используем неструктурированный файл (row-file). Создаём файл «Продакт Эрр» — ошибки по продукту. Также заранее создаём файлы для будущих измерений (dimension): «Адрес» и «Сотрудник», чтобы не возвращаться к этому позже. В настройках выбираем файл «Продакт Эрр» и все колонки для выгрузки.

2. Уточняющий запрос по сотруднику

Третья размерность — сотрудник. Логика та же: дальше идут только совпавшие строки, несовпавшие попадают в файл ошибок. Используется частичное кэширование (caching). Соединяемся с хранилищем и выбираем таблицу «Сотрудник».

Соответствие с сотрудником: ставим галочку на ключ, сопоставляем Sales Person ID с Business Entity ID. Также выгружается департамент сотрудника. Департамент должен быть актуален на момент продажи, поэтому Order Date сопоставляем с Start Date.

При первом проходе возникает несовпадение типов: в измерении сотрудника указан Date, а в источнике — DateTime. Исправляем тип, сохраняем, обновляем таблицу и повторяем сопоставление. После этого связываем Line Total, Quantity, Product ID, City, State, Province и Sales Person с Business Entity ID.

Правило: идентификатор сотрудника в продаже должен совпасть с идентификатором в измерении «Сотрудник». Один сотрудник встречается столько раз, сколько раз он менял департамент. Поэтому нужно выбрать версию сотрудника на тот момент, когда он работал в департаменте, соответствующем дате продажи. За дату продажи отвечает Order Date — второй параметр в условии. Используем оператор Between: дата продажи должна находиться между Start Date и End Date.

Затем выгружаем ошибки в отдельный файл: выбираем файл «Сотрудник», все столбцы.

3. Агрегация фактов

Перед загрузкой факта нужно выполнить агрегацию (aggregate). Показатели Line Total и Quantity суммируются. Операция суммирования ставится после полного соответствия по всем измерениям. Берём строки, успешно прошедшие сопоставление, и суммируем Quantity и Line Total. Объединение идёт по Employee ID, Product ID, Address ID и Order Date. Если в одну дату один сотрудник продал один товар с доставкой на один адрес, такие продажи нужно просуммировать.

4. Загрузка в хранилище

Результат загружаем в хранилище (data warehouse), в таблицу фактов (fact table). Назначаем имена: Product Error, Employee Error, Aggregate, Fact DB. Убираем проверку ограничений. Задаём соответствие: Order Date → Date, Order Quantity → Quantity, Line Total → Amount. Сохраняем процедуру.

На верхнем уровне процедура выглядит так:

1. Очистка хранилища от старых значений.
2. Параллельная загрузка измерений.
3. Загрузка информации из базы данных.
4. Загрузка всех продаж в таблицу фактов с сопоставлением по измерениям и суммированием по общим ключам.

5. Запуск и проверка

Настраиваем подключение (connection), компилируем, выполняем развертывание (deploy) и запускаем пакет (package). При первом запуске возникает ошибка: в очистке не указано, в рамках какого подключения она выполняется. Указываем подключение хранилища.

Смотрим ход выполнения. Очистка завершена, загрузка измерений завершена, идёт загрузка фактов. Можно провалиться на уровень ниже и видеть количество обработанных строк. Выгрузка из базы заканчивается, затем проходят сопоставление по адресу, продукту и сотруднику, после — суммирование. Основной перепад по числу строк происходит на стадии суммирования. В рассмотренном запуске из 121 000 строк до суммирования дошло 60 000; 60 000 строк по сотрудникам попало в ошибку. Причины требуют отдельного анализа.

6. Результат

В хранилище заполнены измерения «Адрес», «Сотрудник», «Продукт» и центральная таблица фактов. Бизнес-аналитик видит ID продавца, ID товара, ID адреса, дату заказа, денежную сумму и количество. Данные представлены в форме схемы «звезда» (star schema). ETL-процедура (Extract, Transform, Load — извлечение, преобразование, загрузка) выполнила выгрузку, загрузку, проверку соответствия идентификаторов, правило по дате и суммирование. Это основа для дальнейшего построения OLAP-кубов (Online Analytical Processing cube). В файлах ошибок видны проблемы кодировки, основные ошибки попали в измерение сотрудника. Далее предстоит собирать OLAP-кубы из готовых схем «звезда» и «снежинка» и завершить курс практикой по анализу данных.

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

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

Ключевой методический прием — учет историчности. Если атрибут объекта меняется во времени, его нельзя присоединять по текущему значению; необходимо выбирать версию, действовавшую на дату события. Это делает модель пригодной для ретроспективного анализа.

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

Практическое применение связано с подготовкой данных для бизнес-аналитика: пользователь получает не сырые выгрузки, а структурированную схему «звезда», где факты связаны с измерениями. Это основа для последующего построения OLAP-кубов и анализа продаж. Ошибки при этом не исчезают, а становятся объектом отдельного разбора, что поддерживает управляемое качество данных.

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