Моделирование измерений в хранилище Microsoft Fabric: таблицы фактов

Область применения:✅ конечная точка аналитики SQL и хранилище в Microsoft Fabric

В этой статье приведены рекомендации и рекомендации по проектированию таблиц фактов в трехмерной модели. Он предоставляет практические рекомендации по хранилищу в Microsoft Fabric, который поддерживает множество возможностей T-SQL, таких как создание таблиц и управление данными в таблицах. Таким образом, вы полностью управляете созданием таблиц трехмерной модели и их загрузкой с данными.

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

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

Таблицы фактов также включают ключи измерения, определяющие размерность фактов. Ключевые значения измерения определяют степень детализации фактов, которая является атомарным уровнем, на котором определяются факты. Например, ключ измерения даты заказа в таблице фактов продаж задает степень детализации фактов на уровне даты, а ключ измерения целевой даты в целевой таблице фактов продаж может задать степень детализации на уровне квартала.

Чтобы легко определить таблицы фактов, обычно префиксируйте их имена с f_ помощью или Fact_.

Структура таблицы фактов

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

CREATE TABLE f_Sales
(
    --Dimension keys
    OrderDate_Date_FK INT NOT NULL,
    ShipDate_Date_FK INT NOT NULL,
    Product_FK INT NOT NULL,
    Salesperson_FK INT NOT NULL,
    <…>
    
    --Attributes
    SalesOrderNo INT NOT NULL,
    SalesOrderLineNo SMALLINT NOT NULL,
    
    --Measures
    Quantity INT NOT NULL,
    <…>
    
    --Audit attributes
    AuditMissing BIT NOT NULL,
    AuditCreatedDate DATE NOT NULL,
    AuditCreatedBy VARCHAR(15) NOT NULL,
    AuditLastModifiedDate DATE NOT NULL,
    AuditLastModifiedBy VARCHAR(15) NOT NULL
);

Первичный ключ

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

Ключи измерений

В примере таблицы фактов есть различные ключи измерения, определяющие размерность таблицы фактов. Ключи измерения — это ссылки на суррогатные ключи (или атрибуты более высокого уровня) в связанных измерениях.

Таблица фактов может ссылаться на измерение несколько раз. В этом случае это называется измерением, играющим ролью. В этом примере таблица фактов содержит ключи OrderDate_Date_FK и ShipDate_Date_FK ключи измерения. Каждый ключ измерения представляет отдельную роль, но существует только одно физическое измерение даты.

Рекомендуется задать каждый ключ измерения как NOT NULL. Во время загрузки таблицы фактов можно использовать специальные элементы измерения для представления отсутствующих, неизвестных, N/A или состояний ошибок (при необходимости).

Атрибуты

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

Показатели

Пример таблицы фактов также имеет меры, такие как Quantity столбец. Столбцы мер обычно числовые и часто аддитивные (это означает, что их можно суммировать и суммировать с помощью других агрегатов). Дополнительные сведения см . в разделе "Типы мер" далее в этой статье.

Атрибуты аудита

В примере таблицы фактов также есть различные атрибуты аудита. Атрибуты аудита являются необязательными. Они позволяют отслеживать, когда и как были созданы или изменены записи фактов, и они могут включать диагностические или устранение неполадок, возникающие во время процессов извлечения, преобразования и загрузки (ETL). Например, вы хотите отслеживать, кто (или какой процесс) обновил строку и когда. Атрибуты аудита также могут помочь диагностировать сложную проблему, например, когда процесс ETL неожиданно останавливается.

Размер таблицы фактов

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

Концепции проектирования фактов

В этом разделе описаны различные концепции проектирования фактов.

Типы таблиц фактов

Существует три типа таблиц фактов:

• Таблицы фактов транзакций
• Периодические таблицы фактов моментального снимка
• Накопление таблиц фактов моментальных снимков

Таблицы фактов транзакций

Таблица фактов транзакций хранит бизнес-события или транзакции. Каждая строка хранит факты с точки зрения ключей измерения и мер, а также при необходимости других атрибутов. Все данные полностью известны при вставке, и они никогда не изменяются (за исключением исправлений ошибок).

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

Периодические таблицы фактов моментального снимка

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

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

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

Накопление таблиц фактов моментальных снимков

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

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

При накапливающейся таблице фактов моментального снимка имеется несколько ключей измерения даты, каждый из которых представляет событие вехи. Некоторые ключи измерения могут записывать состояние N/A, пока процесс не будет доставлен в определенную веху. Меры обычно записывают длительность. Длительность между вехами может дать ценные сведения о бизнес-рабочем процессе или процессе сборки.

Типы мер

Меры обычно являются числовыми и обычно аддитивными. Однако некоторые меры не всегда могут быть добавлены. Эти меры классифицируются как полуаддитивные или неаддитивные.

Аддитивные меры

Аддитивная мера может быть суммирована в любом измерении. Например, количество заказов и выручка от продаж являются аддитивными мерами (при условии, что доход записывается для одной валюты).

Полуаддитивные меры

Полуаддитивная мера может быть суммирована только в определенных измерениях.

Ниже приведены некоторые примеры полуаддитивных мер.

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

Недитивные меры

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

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

Хотя некоторые меры нельзя суммировать, они по-прежнему допустимы. Их можно агрегировать с помощью счетчика, отдельных счетчиков, минимального, максимального, среднего и других. Кроме того, недитивные меры могут стать аддитивными, когда они используются в вычислениях. Например, цена единицы, умноженная на количество заказов, производит доход от продаж, который является аддитивным.

Таблицы фактов без фактов

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

Статистические таблицы фактов

Сводная таблица фактов представляет свертки базовой таблицы фактов к более низкой размерности и/или более высокой детализации. Его цель — ускорить производительность запросов для часто запрашиваемого измерения.

Связанный контент

В следующей статье этой серии вы узнаете о рекомендациях и рекомендациях по проектированию для загрузки таблиц трехмерных моделей.