Понимание звездной схемы и её важности для Power BI

Эта статья предназначена для моделей данных Power BI Desktop. В ней описывается схема звезд и ее релевантность для разработки семантических моделей Power BI, оптимизированных для повышения производительности и удобства использования.

Обзор схемы "Звезда"

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

Таблицы измерений

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

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

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

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

Нормализация и денормализация

Чтобы понять некоторые понятия схемы звезд, описанные в этой статье, важно знать два термина: нормализация и денормализация.

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

Если, однако, таблица продаж хранит сведения о продукте за пределами ключа, он считается денормализованным. На следующем рисунке обратите внимание, что столбцы ProductKey и другие, связанные с продуктом, записывают продукт.

Когда вы получаете данные из экспортного файла или извлечённого набора данных, скорее всего, это представляет денормализованный набор данных. В этом случае используйте Power Query для преобразования и формирования исходных данных в несколько нормализованных таблиц.

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

Релевантность схемы "Звезда" для семантических моделей Power BI

Схема star и многие связанные понятия, представленные в этой статье, очень важны для разработки моделей Power BI, оптимизированных для повышения производительности и удобства использования.

Рассмотрим, что каждый визуальный элемент отчета Power BI создает запрос, который отправляется в семантическую модель Power BI. Как правило, запросы фильтруют, группируют и суммируют данные модели. Затем хорошо разработанная модель — это модель, которая предоставляет таблицы для фильтрации и группировки, а также таблиц для суммирования. Этот дизайн соответствует принципам звёздной схемы.

• Таблицы измерений позволяют фильтровать и группировать.
• Таблицы фактов позволяют агрегировать данные.

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

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

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

Существует множество концепций, связанных с схемой звезд, которые можно применить к семантической модели Power BI. В их число входят следующие:

• Меры
• Суррогатные ключи
• Измерения Snowflake
• Измерения ролевых игр
• Медленно изменяющиеся измерения
• Ненужные измерения
• Вырожденные измерения
• Таблицы фактов без фактов

Меры

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

• Явные меры создаются на основании формулы, написанной в выражениях анализа данных (DAX), которая позволяет достигать суммирования. Выражения мер часто используют функции агрегирования DAX, такие как SUM, , MINMAXи AVERAGEдругие, чтобы получить скалярный результат значения во время запроса (значения никогда не хранятся в модели). Выражение меры может варьироваться от простых агрегатов столбцов до более сложных формул, которые переопределяют контекст фильтра и /или распространение связей. Дополнительные сведения см. в статье "Основы DAX" в Power BI Desktop.
• Неявные меры — это столбцы, которые можно суммировать с помощью визуального элемента отчета или Q&A. Они предлагают удобство для вас в качестве разработчика модели, так как во многих случаях вам не нужно создавать (явные) меры. Например, столбец продаж Sales Amount торгового посредника Adventure Works можно суммировать различными способами (сумма, подсчет, среднее, медиана, минимум, максимум и другие), без необходимости создания метрики для каждого возможного типа агрегирования.

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

Однако существует три убедительные причины, по которым можно создавать меры, даже для простых сводных данных на уровне столбцов:

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

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

• Если вы хотите управлять тем, как авторы отчета суммируют столбцы определенными способами. Например, столбец продаж Unit Price торгового посредника (который представляет ставку на единицу) можно суммировать, но только с помощью определенных функций агрегирования. Она никогда не должна быть суммирована, но она подходит для суммирования с помощью других функций агрегирования, таких как min, max или average. В этом случае модельер может скрыть столбец Unit Price и создать меры агрегации для всех соответствующих функций агрегирования.

  Этот подход к проектированию хорошо подходит для отчетов, созданных в службе Power BI, и для Q&A. Однако динамические подключения Power BI Desktop позволяют авторам отчетов отображать скрытые поля на панели данных, что может привести к обходу этого подхода к проектированию.

Суррогатные ключи

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

Связи семантической модели Power BI основаны на одном уникальном столбце в одной таблице, который распространяет фильтры на один столбец в другой таблице. Если таблица измерений в семантической модели не включает ни одного уникального столбца, необходимо добавить уникальный идентификатор, чтобы стать "единственной" стороной связи. В Power BI Desktop это требование можно выполнить, добавив столбец индекса в Power Query.

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

Измерения Snowflake

Измерение snowflake — это набор нормализованных таблиц для одной бизнес-сущности. Например, Adventure Works классифицирует продукты по категориям и подкатегории. Продукты назначаются подкатегориям, и подкатегории, в свою очередь, назначаются категориям. В реляционном хранилище данных Adventure Works измерение продукта нормализуется и хранится в трех связанных таблицах: DimProductCategory, DimProductSubcategoryи DimProduct.

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

В Power BI Desktop можно выбрать имитацию схемы снежинки для измерения (например, если ваши исходные данные имеют такую структуру) или объединить исходные таблицы для формирования одной денормализованной таблицы модели. Как правило, преимущества одной таблицы моделей перевешивают преимущества нескольких таблиц моделей. Наиболее оптимальное решение может зависеть от объема данных и требований к удобством использования модели.

При выборе имитации дизайна размерности снежинки:

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

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

Медленно изменяющиеся измерения

Медленно изменяющееся измерение (или SCD) — это тип измерения, который обеспечивает надлежащее управление изменением его элементов с течением времени. Она применяется, когда значения бизнес-сущностей изменяются медленно с течением времени незапланированным образом. Хорошим примером SCD является измерение клиента, так как его столбцы контактных данных, такие как адрес электронной почты и номер телефона, редко изменяются. В отличие от этого, некоторые измерения считаются быстро изменяющимися при частом изменении атрибута измерения, например рыночной цены акций. Распространенный подход к проектированию в этих случаях заключается в хранении быстро меняющихся значений атрибутов в таблице фактов как меры.

Теория дизайна звездной схемы относится к двум общепринятым типам SCD: Тип 1 и Тип 2. Таблица измерений может быть типом 1 или типом 2 или поддерживать оба типа одновременно для разных столбцов.

Тип 1 SCD

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

Непериодическое обновление таблицы измерений модели Power BI достигает результата SCD типа 1. Он обновляет данные таблицы, чтобы обеспечить загрузку последних значений.

Тип 2 SCD

Тип 2 SCD поддерживает версии для элементов измерения. Если исходная система не хранит версии, обычно это процесс загрузки хранилища данных, который обнаруживает изменения и соответствующим образом управляет изменением таблицы измерений. В этом случае таблица измерений должна использовать суррогатный ключ для предоставления уникальной ссылки на версию элемента измерения. Он также содержит столбцы, определяющие допустимость диапазона дат версии (например, StartDate и EndDate) и, возможно, столбец флага (например, IsCurrent), чтобы легко фильтровать по текущим элементам измерения.

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

Важно понимать, что если исходные данные не хранят версии, необходимо использовать промежуточную систему (например, хранилище данных) для обнаружения и хранения изменений. Процесс загрузки таблицы должен сохранять существующие данные и обнаруживать изменения. Когда обнаруживается изменение, процесс загрузки таблицы должен аннулировать текущую версию. Он записывает эти изменения, обновляя EndDate значение и вставляя новую версию со StartDate значением, начиная с предыдущего EndDate значения. Кроме того, связанные факты должны использовать поиск на основе времени для получения значения ключа измерения, относящееся к дате фактов. Семантическая модель Power BI использует Power Query и поэтому не может получить этот результат. Однако он может загружать данные из предварительно загруженной таблицы измерений SCD Типа 2.

Семантическая модель Power BI должна поддерживать запросы исторических данных для элемента, вне зависимости от изменений, и для версии элемента, представляющей определенное состояние элемента во времени. В контексте Adventure Works данный дизайн позволяет запрашивать данные о продавце независимо от назначенного региона продаж или для конкретной версии профиля продавца.

Чтобы достичь этого требования, таблица измерения семантической модели Power BI должна содержать столбец для фильтрации продавца и другой столбец для фильтрации определенной версии продавца. Важно, чтобы столбец версии предоставлял неоднозначное описание, например David Campbell (12/15/2008-06/26/2019) или David Campbell (06/27/2019-Current). Кроме того, важно обучить авторов отчетов и потребителей основам SCD Type 2 и как достичь соответствующих конструкций отчетов путем применения правильных фильтров.

Рекомендуется включить иерархию, которая позволяет визуальным элементам углубляться до версии.

Ролевые измерения

Измерение воспроизведения ролей — это измерение , которое может фильтровать связанные факты по-разному. Например, в Adventure Works таблица измерений даты связана тремя отношениями с фактами продаж реселлера. Ту же таблицу измерений можно использовать для фильтрации фактов по дате заказа, дате доставки или дате доставки.

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

В семантической модели Power BI эту структуру можно имитировать, создавая несколько связей между двумя таблицами. В примере компании Adventure Works таблицы "Дата" и "Продажи торговых посредников" будут иметь три отношения.

Хотя такая структура возможна, между двумя таблицами семантической модели Power BI может быть только одна активная связь. Все остальные связи должны быть неактивными. Наличие одной активной связи означает, что фильтрация по умолчанию распространяется от даты к продажам реселлера. В этом экземпляре активная связь устанавливается на наиболее распространенный фильтр, используемый в отчетах, который в Adventure Works является связью по дате заказа.

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

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

Чтобы преодолеть эти ограничения, распространенный метод моделирования Power BI — создать таблицу измерений для каждого экземпляра, выполняющего роль. Вы можете создать каждую таблицу измерений в виде ссылочного запроса с использованием Power Query или вычисляемой таблицы с использованием DAX. Модель может содержать таблицу Date, таблицу Ship Date и таблицу Delivery Date, каждая из которых имеет одну активную связь с соответствующими столбцами таблицы продаж реселлера.

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

Рекомендуется следовать рекомендациям по проектированию при создании таблиц измерений модели для каждой роли:

• Убедитесь, что названия столбцов понятны сами по себе. Хотя можно иметь столбец Year во всех таблицах дат (имена столбцов уникальны в своей таблице), по умолчанию заголовки визуальных элементов не описывают этот столбец сами по себе. Рекомендуется переименовать столбцы в каждой таблице ролей измерения так, чтобы таблица Ship Date содержала столбец года с именем Ship Year, и т. д.
• При необходимости убедитесь, что описания таблиц предоставляют отзывы авторам отчетов (с помощью подсказок области данных ) о настройке распространения фильтров. Эта ясность важна, если модель содержит универсальную именованную таблицу, например Date, которая используется для фильтрации множества таблиц фактов. В случае, если эта таблица имеет, например, активную связь со столбцом даты заказа на продажу у торгового посредника, рассмотрите возможность предоставления описания таблицы, например Filters reseller sales by order date.

Дополнительные сведения см. Руководство по действующим и недействующим отношениям.

Измерения нежелательной почты

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

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

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

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

Вырожденные измерения

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

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

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

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

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

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

Более убедительным способом использования таблицы фактов без фактов является хранение связей между измерениями. Это подход, который мы рекомендуем для проектирования семантической модели Power BI с целью определения связей между измерениями по принципу "многие ко многим". В структуре отношений измерений "многие ко многим" таблица фактов без фактов называется мостовой таблицей.

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

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

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

Дополнительные сведения о схеме звезд или семантической модели Power BI см. в следующих статьях:

• Статья Википедии по дименсиональному моделированию
• Связи модели в Power BI Desktop
• Руководство по отношениям "один к одному"
• Руководство по связям "многие ко многим"
• Руководство по двунаправленным отношениям
• Руководство по активным и неактивным отношениям
• Многомерное моделирование в Microsoft Fabric Warehouse
• Вопросы? Попробуйте обратиться к сообществу Fabric
• Есть предложения? Поделитесь идеями по улучшению Fabric