Как сделать иерархию дат в power bi
Добавил пользователь Cypher Обновлено: 04.10.2024
Я работаю над отчетом в Power BI. Одна из таблиц в моей модели данных собирает данные датчиков. Имеет следующие столбцы:
- Serial (int) т.е. 123456789
- Отметкавремени (datetime), т. Е. 20.12.2016, 12:04:23
- Чтение (десятичное) т.е. 123.456
Новая запись добавляется каждые несколько минут, с текущими показаниями с датчика.
Power BI автоматически создает иерархию для столбца дата-время, которая включает год, квартал, месяц и день. Поэтому, когда вы добавляете визуальный элемент в свой отчет, вы можете легко перейти к каждому из этих уровней.
Я хотел бы включить часть данных "Время" в иерархию, чтобы вы могли развернуть еще один уровень после "Дня" и посмотреть подробные показания за этот период.
Я уже настроил таблицу Date, используя функцию CALENDARAUTO(), добавил все соответствующие столбцы и связал ее с моей таблицей чтений, чтобы суммировать данные по дате, что прекрасно работает. Но это не включает измерение "Время".
Я посмотрел на следующие вопросы SO, но они не помогли:
Я также нашел эту статью, но это сбивало с толку:
3 ответа
Вы будете хотеть отдельные таблицы даты и времени. Вы не хотите помещать время в таблицу дат, потому что время повторяется каждый день.
Измерение Time является тем же принципом, что и измерение Date, за исключением того, что вместо строки для каждого дня у вас будет строка для каждой минуты или каждой секунды (в зависимости от того, насколько точным вы хотите быть - я бы не рекомендовал включать вторую, если он вам абсолютно необходим, так как он значительно увеличивает количество нужных вам строк, что влияет на производительность). Там не будет ссылки на дату в расписании.
Я включил столбец времени / текста, так как Power BI имеет привычку добавлять дату с 1899 к типам данных времени. Вы можете добавить другие столбцы, если они также будут вам полезны.
В вашей таблице фактов вы захотите разделить свой столбец даты и времени на отдельные столбцы даты и времени, чтобы можно было присоединить дату к таблице дат и время к таблице времени. Время, вероятно, нужно будет преобразовать в минуты или секунды ближайшего раунда, чтобы каждый раз в ваших данных соответствовал строке в вашем расписании.
Стоит сохранить, но скрыть исходное поле даты и времени в ваших данных на тот случай, если позже вы захотите рассчитать продолжительность, охватывающую дни.
В Power BI вы добавили бы атрибут времени (или атрибут часа (и минуты)) к атрибутам месяца / дня на своей оси, чтобы получить столбчатую диаграмму, которую можно детализировать с года> квартала> месяца> дня> часа> минута. Power BI не заботится о том, чтобы атрибуты были из разных таблиц.
Читаю книгу The Definitive Guide to DAX: Business intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI (Business Skills) и в главе про работу с Custom Calendar наткнулся на описание реализации недельного календаря — чтобы на графиках и в таблицах выводить данные по неделям. Только недели получаются в таком формате:
На мой взгляд это совершенно неудобно. Привычно видеть сразу о каких дата идет речь, например так:
Поделюсь как это сделать.
В Power Query есть функции Date.StartOfWeek и Date.EndOfWeek. С помощью которых получаем столбцы с датой начала и конца недели.
Далее объединяем эти столбцы.
Но по умолчанию недели будут сортироваться в алфавитном порядке
Для этого с помощью функции Date.Year создаем столбец с годом, с помощью функции Date.WeekOfYear столбец с номером недели и объединяем их.
Затем указываем, что наш столбец неделя нужно сортировать по этому значению. Полный код для вставки в Power Query можно взять с гитхаба
Этот дашборд является темплейтом и примером как можно оформлять дашборды. Владимир занимается развитием BI в компании и хочет улучшить внешний вид отчетов. Он сделал этот темплейт как пример, на которые могли бы ориентироваться сотрудники используя селф-сервис. Основная идея со слов Владимира — дать правильное оформление заголовка, фильтров и примеры визуализаций, которые могут быть использованы в дашбордах. Но само положение и размеры визуализаций в целом случайны.
Вот три исходных дашборда. Все данные фейковые и случайные, картинки публикуются с разрешения Владимира.
Дальше я подробно пройдусь по правилам, которые были нарушены и с помощью которых можно улучшить дашборд.
Выравнивание по центру
Такое выравнивание текста применяется в заголовках когда мы хотим привлечь внимание зрителя. Оно смотрится уместным на афише, вывеске или обложке книги. Использовать же выравнивание по центру для всех заголовков и элементов на дашборде — плохая идея. Получается много рваных краёв, а эффект привлечения внимания теряется и получается визуальный шум. Поэтому первое, что я сделал — поменял выравнивание всех заголовков, подзаголовков и фактоидов налево.
Правило контраста и иерархии
Вся типографика на дашборде должна подчиняться правилам иерархии. Самым большим элементом должен быть заголовок дашборда, затем заголовки блоков и т. п.
В темплейте же получилось, что самым значимыми элементами являются фактоиды. Ещё много разных начертаний, хотя достаточно было бы двух-трёх.
Правило близости, правило внутреннего и внешнего
Фильтры без аналитики
Очень частый пример того, что фильтры в дашбордах используются не на полную мощность. Если вы даете пользователю просто фильтры или кнопки, вы не помогаете ему выбрать значение фильтра — он должен заранее знать, что он хочет посмотреть. Или начать перещёлкивать фильтры, пытаясь запомнить значения графиков, чтобы сравнить их между собой. Вместо этого я обожаю применять мини бар-чарты, которые можно кликать и они будут работать как фильтры, но при этом они будут давать возможность сравнивать значение метрики между срезами ещё даже ничего не выбрав. Можно показывать, например, значение за последний период или усреднение по всем периодам.
Применив принципы, которые описал выше, у меня получился вот такой результат:
Power BI vs Tableau
Я занимался только версткой и оформлением, поэтому сравню именно эти аспекты.
Сам подход к верстке — отличается координально. С одной стороны, в Power BI, он гораздо более прост и привычен — полное ощущение, что работаешь с Power Point. А с другой, сразу ставит крест на нормальной адаптивности под разные экраны ноутбуков. Табло в этом плане круче.
Настройки оформления в Power BI сейчас довольно обширные (последний раз я трогал его года три назад), позволяют закрыть большинство кейсов. Мне только не хватило возможности настраивать внутренние паддинги для блоков. И вынесло мозг, что нельзя поменять цвет только части текста в строке.
Ещё выписал для себя список фич, которым прям позавидовал:
— Офигенные фильтры, которые при снятии всех галочек догадываются, что нужно показывать всё. Это очень удобно
— Крутая визуализация для факторного анализа (дерево метрик). Я такую в Табло два месяца делал, а тут бац и в два клика =(
— Копи-паст форматирования работает шикарно, а не как в Табло
— Группировки блоков на дашборде с помощью одной кнопки. Какой же кайф!
— Очень неплохие бар-чарт таблицы с правильным выравниванием цифр
— Можно вставить нормально ссылку в текст
— Умные подписи внутри тримапа при иерархии
Выводы
В последнее время мы достаточно много общаемся с пользователями, которые самостоятельно делают отчеты в Power BI, используя данные из нашего сервиса myBI Connect. Большая часть их вопросов связана с созданием моделей данных и вычислением каких-либо специфических показателей при помощи DAX. Про последний можно найти достаточно большое количество справочников, разборов и статей, а вот материалов по моделированию значительно меньше. Поэтому мы решили написать ряд статей, которые помогут начинающим пользователям Power BI ознакомиться с основными принципами работы с ним. Это первая статья и в ней мы рассмотрим наиболее распространенный подход к созданию моделей данных.
Для начала, что вообще такое модели данных?
Большая часть пользователей Power BI начинает свой путь с создания визуализаций для плоских Excel табличек, которые уже обладают всей нужной информацией. Но рано или поздно этого перестает хватать, появляется желание загрузить больше таблиц, которые в идеале должны взаимодействовать друг с другом. Вот тут и начинается моделирование данных.
Данные в Power BI хранятся в виде отдельных таблиц между которыми можно создавать связи. Организованная структура этих таблиц и связей между ними и называется моделью данных. Модель является основой отчета, поэтому к ее разработке необходимо подходить максимально ответственно, неправильная организация данных может сильно усложнить вычисления DAX, или даже привести к некорректному отображению данных, что далее будет сложно отследить на этапе визуализации.
Прежде чем вы приступите к созданию отчета, вы должны четко определиться с целью, которая перед ним стоит, какую задачу он должен решить, и, конечно, с данными, которые вам для этого понадобятся. На основе этого вы сможете создать необходимую модель и только после этого приступить к работе над самим отчетом.
Именно для того чтобы избежать ошибок на начальном уровне создания отчетов мы и предлагаем вам ознакомиться с наиболее распространенным методом создания моделей. Конечно, это не единственный верный подход, которому нужно следовать всегда и везде, но он является логически целостным и довольно легко масштабируемым, что позволяет создавать как простые, так и сложные модели, в которых вам будет легко ориентироваться в дальнейшем.
Схема Звезда
Схема Звезда — это способ организации данных, основанный на логическом разделении их на два типа, для хранения которых используются таблицы измерений и таблицы фактов. Таблицы измерений содержат набор описательных атрибутов, характеризующих объект. Это могут такие сведения как категория объекта, его название, какие-то физические характеристики объекта и т.д. Таблицы фактов содержат сведения о событиях, в которые вовлечены те или иные объекты. На практике это выглядит как связи с таблицами измерений в виде внешних ключей и цифровые показатели, характеризующие событие, такие как цена, количество и т.д.
В качестве примера мы будем рассматривать модель, созданную на основе выгрузки данных по рекламе из сервиса Яндекс.Директ:
На скриншоте представлена классическая Звезда, в центре которой находится таблица фактов, вокруг которой размещены таблицы измерений. Если говорить именно о нашем примере, то в таблицах измерений содержатся следующие сведения:
- параметры кампаний;
- параметры групп объявлений;
- параметры объявлений;
- домены;
- UTM- метки;
- даты.
В таблице фактов содержатся ссылки на таблицы измерений и статистика по объявлениям:
- показы на поиске и в рекламной сети;
- клики на поиске и в рекламной сети;
- расходы на поиске и в рекламной сети;
- средняя позиция показа;
- средняя позиция клика.
Основной принцип, который нельзя нарушать: таблицы измерений не могут иметь связи между собой. Это необходимо соблюдать всегда. К примеру вы решите связать таблицы содержащие сведения о кампаниях и группах объявлений — это кажется вполне логичным, но это может вызвать неоднозначность в связях (что запрещено в Power BI) или просто ограничить вас в использовании данных, находящихся в одной из этих таблиц. Все связи между разными объектами реализуются только через таблицы фактов.
Однако ради справедливости стоит отметить, что это правило не всегда является абсолютным.
Схема Снежинка
Все-таки связи между таблицами измерений возможны, и, хотя это является частным случаем Звезды, подобный способ организации данных носит название Снежинка. Пример подобной структуры показан на следующем скриншоте, на котором появились новые таблицы по таргетингу на регионы, связанному с группами объявлений, и быстрым ссылкам, связанным с объявлениями:
Подобный способ так же имеет право быть, но его следует использовать с большей осторожностью, а лучше вообще пытаться избегать по мере возможности. Основная причина в том, что чем больше связей мы имеем, тем больше шансов в них запутаться. Кроме этого наличие большого количества иерархических связей ( A -> B -> C -> D … ) может негативно сказаться на производительности.
В приведенном примере мы просто не можем сделать иначе, поэтому и применяем подобный подход: у одного объявления может быть несколько быстрых ссылок и одна группа объявлений может быть нацелена на показ в нескольких регионах. В тоже время мы имеем только иерархические связи, а не циклические, что позволяет избежать неоднозначности о которой упоминалось выше.
Как использовать модель?
Надеюсь, что основной принцип, заложенный в схемах Звезда и Снежинка стал понятен, поэтому сейчас мы можем перейти к рассмотрению того как подобная модель используется в Power BI.
На самом деле в данном случае все довольно просто, необходимо лишь следовать одному правилу: таблицы измерений используются для формирования срезов, а все меры вычисляются на основе таблиц фактов.
По данной мере видно, что мы накладываем фильтр на таблицу, содержащую сведения по кампаниям direct_campaigns, а количество показов считаем по таблице фактов direct_ads_facts. Благодаря существующей связи между этими таблицами фильтр наложенный на таблицу измерений распространяется и на таблицу фактов, что и позволяет вычислить необходимое нам значение:
Какие преимущества имеют эти схемы?
Данный подход на протяжении многих лет использовался для проектирования аналитических баз данных и отличается от других именно своей простотой получения необходимых сведений. В тоже время — это только вершина айсберга, самое интересное как раз вытекает из основного принципа разделения данных на логические элементы (объекты). Дело в том, что при использовании различных источников данных, некоторые объекты могут повторяться, но здесь стоит отметить что речь идет не столько о их названиях, сколько о их структуре. К примеру, если мы возьмем рекламные кампании в том же Яндекс.Директ и Facebook , то по своему существу они похожи, но по структуре данных отличаются. В тоже время если мы возьмем UTM -метки, которыми размечены URL объявлений в этих кампаниях, или дату, за которую мы выгрузили статистику, то они одинаковы во всех смыслах. Поэтому такие данные можно вынести отдельно в так называемые общие таблицы измерений, которые одинаковы и могут использоваться в разных таблицах фактов:
В данном случае мы имеем две таблицы фактов, которые находятся по бокам, и три таблицы общих измерений, которые размещены по середине. Данные были выгружены их разных рекламных площадок, но учитывая их организацию, мы можем их использовать совместно, т.е. посчитать общий расход на рекламу по дням или сайтам. Более того в эту модель можно легко добавить данные из других рекламных площадок или Google Analytics.
Обратная сторона модели
Сложно сказать, что у данного подхода есть явные недостатки, но ради справедливости следует отметит тот факт, что при его использовании есть вероятность незначительного увеличения объема памяти, занимаемого моделью, и как следствие некоторое снижение производительности, по сравнению с тем, если бы все данные хранились в одной большой таблице. В тоже время не стоит забывать, что на практике мы редко имеем возможность работы с такого рода данными… Поэтому возможность получения прозрачности данных и масштабируемости модели, за счет незначительного снижения производительности сложно назвать недостатком.
Мы прекрасно понимаем, что в рамках одной статьи невозможно описать все тонкости моделирования, но в то же время мы и не ставили перед собой такой задачи, а хотели лишь выделить основные моменты, которые возможно заставят читателей задуматься о роли создания моделей и постараться разобраться в этой интересной теме чуть-чуть более подробно. Со своей стороны, мы не планируем останавливаться на этом и в следующий раз постараемся рассмотреть более практичный и немного более сложный пример, чем те, которые мы рассматривали в этой статье.
Если у Вас остались вопросы или комментарии, пожалуйста, напишите их под этой статьей. И спасибо, что дочитали, надеемся это было полезным)
UPD. Мы продолжили развивать тему моделирования данных и опубликовали следующую статью, в которой рассматриваем пример построения модели на данных Google Analytics.
Читайте также: