PowerBI - "одна таблица" vs "звезда"
В #PowerBI все вычисления зависят от контекста, в котором выполняются. И чтобы контекстом было просто управлять, требуется создать "правильную" модель данных.
Исходя из собственного опыта и опыта множества других разработчиков, наиболее оптимальной является модель схемы "звезда", когда в центре у вас находится таблица фактов, а вокруг таблицы измерений (справочников), которые фильтруют факты.
Но начинающие аналитики и разработчики, особенно те, кто идёт из Excel, любят работать с одной таблицей, в которой "всё есть".
Как правило, источником данных таких таблиц служит учетная система, из которой ИТ-служба настроила выгрузку "по максимуму", чтобы их беспокоили как можно меньше.
Предлагаю на примере двух наиболее популярных задач в аналитике разобрать, почему схема со справочниками лучше, чем схема из одной таблицы:
А в конце бонусом расскажу: как "спрятать" в фильтрах то, что не продавалось?
Как сравнить продажи текущего года с предыдущим
Для начала создадим две базовые меры для каждой модели:
Создадим одинаковые визуализации в виде таблиц по Subcategory
Далее создаём меры, которые вычисляют сумму продаж за предыдущий год, для этого воспользуемся функцией DATEADD:
Добавляем созданные меры в визуализации и в таблице с данными из схемы "одна таблица" получаем ошибку:
Для новичка такое сообщение мало что скажет, поэтому требуется пояснение:
Функции DATEADD необходимо сдвинуть каждую дату в текущем контексте (2016 год) на один год назад, но если в 2016 году для какой-то Subcategory не было продажи, хотя бы один день, то функция не будет знать откуда сдвигать данные.
При использовании схемы "звезда" в модели присутствует справочник Calendar, который содержит все даты каждого года, а так как все связи однонаправленные, то есть таблица Products фильтрует таблицу Sales, но не фильтрует Calendar и наоборот, то такой ошибки не возникает.
Как посчитать долю продаж
Для расчёта доли продаж Subcategory в Category применим функцию ALLEXCEPT:
Как видно на скриншоте выше, результат для схемы "одна таблица" получился некорректным. Так как первый аргумент функции ALLEXCEPT это таблица, с которой удаляются все фильтры, кроме указанных после запятой. То есть в том числе был удалён и фильтр по году, таким образом мера показывает продажи категории за все года. Для схемы "звезда" всё считается верно, так как фильтр сбрасывается только с таблицы Products, а фильтр по году из таблицы Calendar по прежнему действует.
Исправим меру для схемы "одна таблица":
Теперь всё верно, но если появится ещё какой-то фильтр, например по месяцу, то придётся вновь исправлять меру или создавать новую, которая будет учитывать новый фильтр, что усложняет разработку и поддержание отчёта в сравнении со схемой "звезда".
Бонус: как "спрятать" в фильтрах то, что не продавалось
Одним из аргументов сторонников схемы "одна таблица" является: необходимо, чтобы срезы показывали только те значения, которые есть в данных в текущем контексте, а не весь справочник.
Слева на скриншоте срез из схемы "одна таблица" - он содержит только те Subcategory, по которым были продажи. Справа срез из схемы "звезда" - это полный справочник товаров, даже те, по которым не было продаж. Если вам не нужна такая информация, то эти товары лучше фильтровать ДО загрузки в модель. Но "лишние" Subcategory будут оставаться, если мы будем в отчёте схемы "звезда" фильтровать не только товары.
В срезе схемы "одна таблица" осталось только две Subcategory, а в срезе схемы "звезда" по прежнему все значения. "Неопытные" разработчики начинают изменять типы связей с односторонней на двунаправленные:
С одной стороны - это решает проблему:
А с другой, это приводит к усложнению действий контекста - из-за того, что теперь фильтры по связям распространяются на все таблицы, проходя через таблицу Sales. Например, перестала работать мера, которая считает сумму продаж за предыдущий год:
Правильным решением будет фильтровать срез по значению меры. Можно воспользоваться любой базовой мерой или создать простую меру, которая считает строки в таблице фактов:
Далее помещаем меру в фильтры визуального элемента и выбираем: "не является пустым" - Применить фильтр
Для наглядности добавил срезы по Subcategory, для которых отключено взаимодействие по срезу год, но для среза схемы "звезда" также действует фильтр по мере.
Надеюсь эти три небольших примера сравнения убедили вас, что схема "звезда" лучшее решение относительно схемы "одна таблица".
Если вы хотите более подробно изучить моделирование данных в #PowerBI, то рекомендую сайт(sqlbi.com) и книги Альберто Феррари и Марко Руссо. Кстати, почти все они переведены на русский язык замечательным переводчиком Александром Гинько.
Ссылка на файл-пример для скачивания.