ETL процедура. Создание запроса измерений. Часть 2

Записаться

Совет

В случае появления ошибки:

Msg 208, Level 16, State 1, Line 1

Недопустимое имя объекта "Production.Product".

Учтите, что запросы выполняются к определенной базе данных. Текст ошибки говорит о том, что либо не выбрана база данных, либо она банально отсутствует. Для проверки в начале запроса добавить строку:

USE AdventureWorks2017;

В качестве альтернативы можно при открытом запросе в левом верхнем углу окна Management Studio выбрать из выпадающего списка именно базу данных AdventureWorks2017.

На будущее, для написания запроса сразу к определенной базе данных надо нажать правую кнопку мыши на базе и выбрать "Создать запрос".

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

Измерение «Продукт»

После завершения измерения «Персонал» переходим к измерению «Продукт». Для него нужны: категория, подкатегория, наименование продукта, цена на момент времени заказа и номер продукта.

Состав атрибутов

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

1. идентификатор продукта (Product ID);
2. название продукта;
3. идентификатор категории (Category ID);
4. название категории;
5. идентификатор подкатегории (Subcategory ID);
6. название подкатегории;
7. модель;
8. цвет;
9. цена.

Сначала удобнее запрашивать идентификатор продукта, затем его название, потом категорию и подкатегорию. Идентификатор категории ставится перед названием категории, а идентификатор подкатегории — перед названием подкатегории.

Цена и история изменений

Цена продукта связана с таблицей истории цены (Price History). Возможны два случая.

Если цена по прайс-листу (list price) в истории цены не указана или равна NULL, это означает, что цена никогда не менялась. Тогда берется базовая цена из описания самого продукта.

Если цена по прайс-листу в истории указана, берется соответствующая цена из истории.

Дата начала (Start Date) соответствует цене в истории. Если Start Date не указана, подставляется заведомо низкий год — 1990. Это признак первоначальной цены: в тот момент компании еще не было. Если дата окончания (End Date) не указана, подставляется 3000 год.

Соединения таблиц

Сначала таблица продукта соединяется с подкатегорией. Используется левое внешнее соединение (left outer join). Это нужно потому, что не у каждого продукта указана подкатегория. При внутреннем соединении (inner join) продукты без подкатегории не попали бы в выборку.

Затем выполняется left outer join с категорией. Причина та же: есть продукты с подкатегорией, но без категории. Если использовать inner join, часть продуктов будет потеряна.

Далее left outer join применяется к таблице истории цены. У продукта может не быть ни одной записи об изменении цены. Если записи нет, продукт все равно должен остаться в выборке с базовой ценой.

Последнее соединение — с моделью продукта. Модель указана не у каждого продукта, поэтому снова используется left outer join. Условия соединения — совпадение соответствующих идентификаторов.

Пример результата

В выборке есть продукты без категории, подкатегории, модели и даже без цвета. Есть товары с категориями и подкатегориями. Цены могут быть базовыми: начало — 1990 год, окончание — 3000 год. Например, до 29 мая цена была 1,56, а с 30 мая стала 61,92; она актуальна до сих пор, потому что указан 3000 год. Есть товары с нулевой базовой ценой и товары с ненулевой ценой. Иногда запись об изменении цены есть, хотя значение не изменилось. Результат сохраняется как измерение «Продукт».

Измерение «Доставка»

После продукта создается третье измерение — «Доставка». Для него нужны: страна, код страны, регион, код региона, город, код города.

Устранение дублей

Здесь используется DISTINCT (дестинкт) — только различные результаты. В один город может приходить много заказов, но для измерения нужен перечень городов доставки, а не повторение одного и того же города много раз. Например, Москва должна быть указана один раз.

Иерархия и таблицы

Иерархия атрибутов делается более наглядной:

1. код страны (Country Origin Code);
2. код региона или провинции (State Province Code);
3. идентификатор провинции (State Province ID);
4. название провинции (State Province Name);
5. название города.

Используется таблица State Province, которая соединяется с таблицей Address по коду провинции. После этого измерение «Доставка» сохраняется.

Переход к целевым таблицам

К этому моменту подготовлены все необходимые измерения. Теперь нужно создать целевые таблицы, в которые будет загружаться информация.

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

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

История цены показывает, как отделить базовое значение от временного. Если изменения не было, используется исходная цена; если изменение есть, применяется историческая запись. Границы периода важны не сами по себе, а как способ отделить начальное состояние от текущего. Подстановка условных дат создает непрерывный интервал, при котором цена не исчезает из анализа и не искажает итоги. Это практический компромисс между точностью источника и пригодностью модели.

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

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