Куб строится не напрямую на учётной базе, а на хранилище данных, устроенном по специальной схеме. Самая распространённая из них — звезда. Её описал Ральф Кимбалл, и за тридцать лет лучше для аналитики так ничего и не придумали.
Таблица фактов
В центре звезды — таблица фактов. Каждая строка в ней — событие: продажа позиции в чеке, отгрузка, проводка. В строке лежат числа (количество, сумма, себестоимость) и ключи, указывающие на справочники.
Главное решение при проектировании таблицы фактов — зерно: что именно описывает одна строка. «Одна позиция чека» и «итог по товару за день в магазине» — разные зёрна, и от выбора зависит, какие вопросы модель сможет отвечать. Детальное зерно гибче, укрупнённое — компактнее. Зерно выбирают по самому детальному вопросу, который бизнес будет задавать.
Таблицы измерений
Вокруг фактов — таблицы измерений: дата, товар, магазин, клиент. В них всё, по чему группируют и фильтруют: названия, категории, регионы, признаки. Измерения обычно намного меньше фактов, но «шире»: в них много текстовых колонок.
Схема в целом выглядит как звезда: факты в центре, измерения лучами вокруг.
Суррогатные ключи
Факты ссылаются на измерения не по коду из 1С, а по суррогатному ключу — простому целому числу, которое выдаёт само хранилище. Это нужно по трём причинам:
- в разных источниках один и тот же товар может иметь разные коды;
- коды в источниках иногда переиспользуют или меняют;
- суррогатный ключ позволяет хранить историю изменений справочника.
История изменений справочников
Допустим, магазин в июне перешёл из региона «Центр» в регион «Север». К какому региону относить его продажи за март? Для этого есть приёмы, которые называют медленно меняющимися измерениями (slowly changing dimensions, SCD):
- тип 1 — просто перезаписать значение; вся история «переедет» в новый регион;
- тип 2 — завести новую версию строки с датами действия; мартовские продажи останутся в «Центре», июльские попадут в «Север».
Какой тип выбрать, решает бизнес, а не программист: это вопрос о том, как должна выглядеть история в отчётах.
CREATE TABLE dim_store (
store_key INT IDENTITY PRIMARY KEY, -- суррогатный ключ
store_code NVARCHAR(20) NOT NULL, -- код из учётной системы
store_name NVARCHAR(200) NOT NULL,
region NVARCHAR(100) NOT NULL,
valid_from DATE NOT NULL,
valid_to DATE NULL, -- NULL — действующая версия
is_current BIT NOT NULL
);
А снежинка?
Снежинка — та же звезда, но измерения разложены на несколько связанных таблиц: товар ссылается на подкатегорию, подкатегория на категорию. Это экономит место и знакомо тем, кто привык к нормализованным базам, но усложняет модель и запросы.
На практике для SSAS почти всегда выбирают звезду: модель получается проще, а движку SSAS всё равно, сколько места занимает текст в измерениях. Снежинка уместна, когда справочник огромный или одна и та же ветка используется несколькими измерениями.
Хорошо спроектированное хранилище — половина успеха аналитического проекта. Если в нём есть зерно, суррогатные ключи и понятная история изменений, модель SSAS собирается поверх него за дни, а не за месяцы.