Записаться

Для лекции используйте файл AdventureWorks2017.bak (необходимо распаковать).

1. Введение

Мы продолжаем курс, посвящённый хранилищам данных. Сегодня практическое занятие. Хранилище данных (Data Warehouse, DW) включает много компонентов. Первый этап — собрать информацию из разных транзакционных систем, то есть из OLTP-систем (On-Line Transaction Processing — оперативная обработка транзакций).

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

2. Схема хранилища

Хранилище организуем по схеме «звезда» (star schema). В центре находится таблица фактов, к ней присоединены таблицы измерений (dimensions). Перед реализацией нужно понять, какие данные необходимы, какие преобразования выполнить и в какие целевые таблицы загрузить результат.

3. Постановка задачи

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

3.1 Измерение «Персонал»

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

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

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

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

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

Нас интересует территориальная принадлежность покупателя, но не его фамилия, имя, отчество. Нужны страна, код страны, регион, код региона, город и код города. Это даёт агрегацию до уровня региона и страны.

3.4 Измерение «Время»

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

4. Показатели

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

5. Возможности анализа

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

6. Источник данных

Источник — транзакционная база данных магазина. В качестве учебной базы используется база Microsoft AdventureWorks. Есть версии 2012, 2014, 2016, 2017. Мы работаем с SQL Server 2017, поэтому используем AdventureWorks 2017.

Эта база посвящена магазину, который торгует спортивными велосипедами и аксессуарами. У магазина есть розничные точки продаж и интернет-магазин, а также доставка по всему миру. База учебная, но её структура близка к структуре реального магазина. Далее нужно детально рассмотреть таблицы и связи между ними.

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

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

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

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

Источник в учебном примере — AdventureWorks. Он моделирует международную торговлю велосипедами и аксессуарами через розницу и онлайн. Хотя база учебная, её структура демонстрирует типовые связи транзакционной системы. Практический вывод: качество аналитики зависит от предварительного проектирования. Сначала требования и метрики, затем измерения и иерархии, затем ETL и загрузка. Если пропустить этот порядок, хранилище будет содержать данные, но не сможет надёжно отвечать на вопросы бизнеса.