Схема «звезда» в Power BI: почему одна плоская таблица не может ответить на вопрос

1. Факт или измерение, и как решает модель

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

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

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

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

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

Вот та часть, которая удивляет людей. В Power BI нет параметра, который бы объявлял таблицу той или иной. В руководстве прямо сказано: «Не существует свойства таблицы, которое разработчики моделей задавали бы для установки типа таблицы как измерения или факта. Фактически оно определяется отношениями модели». В частности, кардинальность: «Сторона «один» всегда представляет собой таблицу измерений, а сторона «многие» всегда является таблицей фактов».

Таким образом, тип — это не ярлык, который вы применяете. Это следствие того, как вы объединили вещи, а это значит, что вы установили правильные отношения. является получить модель правильно.

2. Плоский стол и вопрос, на который он не может ответить

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

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

Вот ответ, когда в модели присутствует таблица продуктов со столбцом стоимости.

Проверьте очередь вручную: 23 стола по стоимости 140 каждый — это 3220, а 5060 минус 3220 — 1840. И теперь вместо списка претензия. Лампы занимают последнее место по выручке и первое по рентабельности. Рейтинг выручки и рейтинг маржи ставят их на противоположные концы, и единственное, что стоит между этими двумя предложениями, — это вторая таблица и связь.

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

3. Разделение: что и куда

Перед разделением: возьмите восемь столбцов плоской таблицы и отсортируйте их по категориям «описывает что-то» и «является событием или числом». Сделайте это, прежде чем читать дальше.

Вот где они приземляются.

Два решения в этой таблице заслуживают объяснения, потому что именно в них люди ошибаются.

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

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

Измерение даты — это то, о чем никто не сожалеет. Даже на данных за пять месяцев именно это делает показатели помесячных, годовых и предыдущих периодов вообще работоспособными по причинам, рассмотренным в DAX Временная аналитика.

4. Построение отношений и направление, которое имеет значение

Перед настройками: присоединяете Товары к Заказам по названию товара. Прежде чем читать дальше, предскажите, в какую сторону должны двигаться фильтры.

Из продуктов в заказы и только так. Выбор «Товары» в слайсере должен сократить заказы. Выбор заказа не должен сокращать список товаров. Каждое отношение имеет три параметра, и значения по умолчанию обычно верны.

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

Затем в визуальных элементах делайте срезы по столбцам измерения, а не по таблице фактов. Оба работают в нашей модели, поскольку Продукт находится в обеих таблицах, и они ведут себя по-разному в тот момент, когда продукт существует без заказов. Нарезка по Products[Product] показывает его с пустой тактой; нарезка по Orders[Product] никогда не упоминает об этом. Один из них — это отчет, в котором может быть указано: «Эта строка ничего не продала», а другой — отчет, который незаметно теряет это значение.

Мое собственное правило наведения порядка: как только связи существуют, скройте ключевые столбцы таблицы фактов из представления отчета. Никто не должен выбирать между Orders[Product] и Products[Product] во время создания отчета, и скрытие одного из них удаляет выбор.

5. Снежинка: когда одно измерение цепляет другое

Перед формой: наши продукты имеют категории «Мебель» и «Освещение». Вы можете хранить категории в отдельной таблице, присоединенной к продуктам. Скажите, что за это можно купить и сколько это будет стоить.

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

• Загружено больше таблиц, «что менее эффективно с точки зрения хранения и производительности».
• «Необходимо пройти более длинные цепочки распространения фильтров взаимосвязей», поэтому фильтры требуют дополнительного перехода.
• Больше таблиц на панели «Данные», «что может привести к менее интуитивно понятному интерфейсу», особенно если в дополнительной таблице есть один или два столбца.
• «Невозможно создать иерархию, включающую столбцы из более чем одной таблицы», поэтому детализация «Категория-продукт» не может существовать.

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

Так что сгладьте это: поместите «Категорию» в таблицу «Продукты» в виде столбца, именно так и построена наша трехстрочная таблица. Стоимость — это повторение слова «Мебель» дважды, что в таблице из трех строк ничего не значит, а в таблице товаров из трех тысяч строк все равно ничего.

Чек, по нашим данным: Мебель составляет 8290 выручки и Освещение 1600, и в сумме они составляют 9890. Эта группировка работает, поскольку Категория находится в измерении, к которому присоединена таблица фактов, на расстоянии одного шага.

6. Два свидания за одним столом: ролевые измерения

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

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

Два способа справиться с этим, и второй — тот, который рекомендует Майкрософт.

Активируйте неактивную связь внутри конкретных мер. USERELATIONSHIP включает один на время одного ВЫЧИСЛЕНИЯ.

Revenue by Ship Date =
CALCULATE (
    [Total Revenue],
    USERELATIONSHIP ( 'Date'[Date], Orders[ShipDate] )
)

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

Постройте вторую таблицу дат. Таблица дат отправки, клонированная с самого начала, каждая из которых имеет свою активную связь. Одна строка DAX создает его: Ship Date = 'Date'. Затем переименуйте столбцы так, чтобы они были самоописательными: «Год поставки», а не «Год», чтобы заголовок диаграммы читался правильно без редактирования. Стоимостью является дублирование таблицы измерений, а поскольку таблицы измерений небольшие, это редко вызывает беспокойство.

7. Исключения, которые Майкрософт действительно рекомендует

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

Есть ровно один общий, и знание его убережет вас от чрезмерной нормализации.

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

Еще три фигуры, которые стоит узнать, когда вы их встретите, все они задокументированы, ни одна из наших шестнадцати рядов не нужна:

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

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

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

Теперь представьте себе список таблиц вашей собственной модели. Какие таблицы существуют потому, что что-то действительно имеет свою структуру, а какие — потому, что исходная система экспортировала их отдельно?

Крайние случаи, которые застают людей врасплох

Шесть, каждая из которых стоила кому-то дня.

Дублирующиеся ключи на стороне измерения. Power BI отказывается создавать связь «один-ко-многим», если сторона «один» не уникальна, а в сообщении об ошибке указывается ограничение, а не строка, вызывающая нарушение. Найдите дубликат с помощью быстрой группы, прежде чем предположить, что связь невозможна.

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

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

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

Нарезка по копии столбца таблицы фактов. Работает до тех пор, пока у элемента измерения не останется фактов, а затем он автоматически исчезнет из среза. Скройте описательные столбцы таблицы фактов, чтобы удалить выбор.

Моделирование одного плоского экспорта, потому что это один плоский экспорт. Форма отправленного вам файла — это факт о системе отправителя, а не о вашей модели. Power Query существует для его изменения, и Power Query это тот же инструмент, выполняющий ту же работу в Эксель.