Общие сведения о схеме звезд и важности Power BI

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

Эта статья не предназначена для полного обсуждения схемы звезды. Дополнительные сведения см. непосредственно на опубликованное содержимое, например хранилище данных набор средств: Окончательное руководство по моделированию измерений (3-й выпуск, 2013) Ральф Кимбол и другие.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Показатели

В схеме звездочки мера — это столбец таблицы фактов, в котором хранятся суммированные значения.

В модели Power BI мера имеет другое, но аналогичное определение. Это формула, написанная в выражениях анализа данных (DAX), которая достигает суммирования. Выражения мер часто используют функции агрегирования DAX, такие как SUM, MIN, MAX, AVERAGE и т. д. для получения скалярного результата во время запроса (значения никогда не хранятся в модели). Выражение меры может варьироваться от простых агрегатов столбцов до более сложных формул, которые переопределяют контекст фильтра и /или распространение связей. Дополнительные сведения см. в статье о DAX Basics в Power BI Desktop .

Важно понимать, что модели Power BI поддерживают второй метод для достижения суммирования. Любой столбец (и обычно числовые столбцы) можно суммировать визуальным элементом отчета или Q&A. Эти столбцы называются неявными мерами. Они предлагают удобство для вас в качестве разработчика модели, так как во многих случаях вам не нужно создавать меры. Например, столбец sales Sales Sales Amount adventure Works можно свести в множество способов (суммы, подсчета, среднего, медианы, мина, максимума и т. д.), без необходимости создания меры для каждого возможного типа агрегирования.

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

• Когда вы знаете, что авторы отчетов запрашивают модель с помощью многомерных выражений(многомерных выражений), модель должна включать явные меры. Явные меры определяются с помощью DAX. Этот подход к проектированию весьма релевантн, если набор данных Power BI запрашивается с помощью многомерных выражений, так как многомерные выражения не могут достичь суммирования значений столбцов. В частности, многомерные выражения будут использоваться при выполнении анализа в Excel, так как сводные таблицы выдают многомерные запросы.
• Когда вы знаете, что авторы отчетов создают отчеты Power BI с разбивкой на страницы с помощью конструктора запросов многомерных выражений, модель должна включать явные меры. Только конструктор запросов многомерных выражений поддерживает агрегаты серверов. Таким образом, если авторы отчетов должны иметь меры, оцененные Power BI (а не подсистемой отчетов с разбивкой на страницы), они должны использовать конструктор запросов многомерных выражений.
• Если необходимо убедиться, что авторы отчетов могут суммировать только столбцы определенными способами. Например, столбец продаж торгового посредника sales Unit Price (который представляет ставку на единицу) можно суммировать, но только с помощью определенных функций агрегирования. Она никогда не должна быть суммирована, но она подходит для суммирования с помощью других функций агрегирования, таких как min, max, average и т. д. В этом экземпляре модельер может скрыть столбец "Цена единицы" и создать меры для всех соответствующих функций агрегирования.

Этот подход к проектированию хорошо подходит для отчетов, созданных в служба 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 можно имитировать структуру измерения snowflake (возможно, так как исходные данные делает) или интегрировать (денормализовать) исходные таблицы в одну таблицу моделей. Как правило, преимущества одной таблицы моделей перевешивают преимущества нескольких таблиц моделей. Наиболее оптимальное решение может зависеть от объема данных и требований к удобством использования модели.

При выборе имитации макета измерения snowflake:

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

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

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

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

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

Тип 1 SCD

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

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

Тип 2 SCD

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

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

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

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

Чтобы добиться этого требования, таблица измерения модели Power BI должна содержать столбец для фильтрации продавца и другой столбец для фильтрации определенной версии продавца. Важно, чтобы столбец версии предоставлял неоднозначное описание, например "Майкл Блит (12.15.2008-06.26.2019)" или "Майкл Blythe (current)". Кроме того, важно обучить авторов отчетов и потребителей основам SCD Type 2 и как достичь соответствующих конструкций отчетов путем применения правильных фильтров.

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

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

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

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

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

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

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

Чтобы преодолеть эти ограничения, распространенный метод моделирования Power BI — создать таблицу типов измерений для каждого экземпляра, играющего роль. Обычно вы создаете дополнительные таблицы измерений в виде вычисляемых таблиц с помощью DAX. С помощью вычисляемых таблиц модель может содержать таблицу даты, таблицу "Дата доставки" и таблицу "Дата доставки", каждая из которых имеет одну и активную связь с соответствующими столбцами таблицы продаж торгового посредника.

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

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

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

Дополнительные сведения см . в руководстве по активной и неактивной связи.

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

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

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

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

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

Вырожденные аналитики

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

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

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

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

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

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

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

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

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

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

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

• Статья Википедии по моделированию измерений
• Create and manage relationships in Power BI Desktop (Создание связей и управление ими в Power BI Desktop)
• Руководство по отношениям "один к одному"
• Руководство по связям "многие ко многим"
• Руководство по двунаправленным отношениям
• Руководство по активной и неактивной связи
• Вопросы? Задайте их в сообществе Power BI.
• Есть предложения? Участие в разработке идей по улучшению Power BI