Опубликовано по ссылке:
В SQLBI у нас прекрасная работа – мы обучаем и консультируем людей по всему миру. Ежегодно мы встречаемся с тысячами людей из разных стран – такими разными, но такими одинаковыми в своей страсти к бизнес-аналитике и DAX. Вместе со студентами и заказчиками мы решаем сценарии различной степени сложности.
Представьте, что к вам обратился студент с просьбой помочь ему рассчитать количество новых покупателей для своего отчета. Вы, разумеется, помогаете ему. Затем решаете подобную задачу для кого-то еще. И еще. В какой-то момент вам, естественно, захочется выработать готовое решение или шаблон. И именно это сподвигло нас в 2013 году запустить сайт daxpatterns.com. Мы постепенно начали накапливать шаблоны повторяющихся задач и собирать коллекцию готовых формул на языке DAX, призванных решать наиболее часто встречающиеся сценарии. В то время мы не собирались писать новую книгу, а лишь хотели собрать некое подобие базы данных для своих будущих решений. При этом сайт предназначался преимущественно для нас самих.
Однако, как это часто бывает, жизнь распорядилась по-своему. К счастью, на этот раз в положительном смысле. Наш сайт показал невероятные цифры по посещаемости. Читатели загружали шаблоны, преследуя сразу две цели: во-первых, они получали готовое решение своего сценария, а во-вторых, улучшали свои навыки в области DAX, разбирая наши формулы. Из-за разницы форматов мы включали примеры для Excel 2010 и Excel 2013 – кстати, последние прекрасно работают и в более поздних версиях. В конце концов мы объединили все написанные нами рецепты в одну книгу – так на свет появилось первое издание «Шаблонов DAX» (DAX Patterns). Это был конец 2015 года. В то время мы еще даже не опубликовали первое издание книги «Подробное руководство по DAX» (The Definitive Guide to DAX), так что решили включить в «Шаблоны DAX» небольшое введение в язык.
Много воды утекло с тех пор. Язык DAX обогатился несколькими новыми функциями. Но, что более важно, рынок пополнился новым продуктом под названием Power BI, и рост количества потенциальных разработчиков DAX приобрел экспоненциальный характер. Сегодня эти люди в основной своей массе работают именно с Power BI, а когда мы опубликовали первое издание книги, которую вы держите в руках, этот программный продукт еще даже не был анонсирован.
В течение пяти последних лет мы не прекращали разрабатывать новые шаблоны. Мы знакомились с новыми студентами и заказчиками, решали новые сценарии, улучшали свои собственные навыки владения языком DAX. К тому же у нас появились тысячи читателей, которые обеспечивали нам качественную обратную связь по созданным ранее шаблонам. Внимательное изучение их отзывов позволило нам лучше понять, чего они ждут от наших шаблонов. За это время мы также успели выпустить два издания книги «Подробное руководство по DAX» (The Definitive Guide to DAX), что лишило смысла снабжение нового варианта книги о шаблонах описанием языка по примеру первого издания.
В общем, в какой-то момент пришло время обновить как наш сайт, посвященный шаблонам DAX, так и книгу. Тогда мы закатали рукава и принялись за работу, плоды которой вы сейчас держите в руках.
Важно отметить, что при написании второго издания мы не использовали примеры из первой редакции. Мы хотели всё начать с нуля – таков был наш план. В результате весь код был переписан заново с использованием последних новинок в DAX и Power BI и при необходимости адаптирован под Excel 2019.
При разработке второго издания книги мы уделили повышенное внимание следующим аспектам:
существенно увеличили долю сценариев с использованием функций логики операций со временем. Это одна из наиболее распространенных и проблемных тем, так что мы решили посвятить ей гораздо больше времени;
большой популярностью в первом издании книги пользовались шаблоны, посвященные вычислению количества новых покупателей и постоянных клиентов. Этому аспекту бизнес-аналитики во второй редакции мы также уделили повышенное внимание, расширив количество формул и моделей данных при расчете соответствующих показателей;
увеличили количество действительно полезных шаблонов в книге – в частности это касается примеров, которые, по нашему опыту, наиболее часто встречаются на практике;
одновременно мы уменьшили число шаблонов, которые со временем утратили свою актуальность. Например, в 2015 году большой популярностью пользовались специфические статистические расчеты по причине недостатка статистических функций в прежних версиях языка DAX. С тех пор в DAX появилось множество полезных функций, призванных облегчить написание формул для соответствующих вычислений. В связи с этим необходимость в подобном контенте просто отпала;
мы исключили из книги незаполненные фрагменты кода. В первом издании книги большинство листингов были представлены с полями для подстановки, которые читатели могли менять на свои названия столбцов. Сейчас же мы отказались от такой практики в пользу показа полностью рабочего кода, поскольку вам и так в большинстве случаев придется адаптировать модель данных и формулы для полноценного использования наших примеров. Мы посчитали, что такой подход позволит сделать листинги более простыми для восприятия и встраивания в конкретную модель;
мы пересмотрели все без исключения формулы. В результате весь код, представленный в книге, был максимально оптимизирован с точки зрения производительности. При этом мы не заявляем, что выстроенные нами шаблоны безусловно лучшие – они лучшие из того, что мы смогли придумать. Если вы знаете, как сделать наш код быстрее и лучше, расскажите. Раздел с комментариями на нашем сайте – отличное место для подобных предложений;
каждый сценарий представлен в сопроводительных файлах в двух вариантах: для Power BI и Excel, тогда как иллюстрации в книге приведены исключительно на примере Power BI;
и, наконец, мы улучшили электронную версию книги. Это означает, что форматирование листингов теперь должно оставаться неизменным вне зависимости от размера вашего мобильного устройства.
Для чего мы издали эту книгу
Если вас интересует разница между содержимым книги, которую вы держите в руках, и наполнением сайта daxpatterns.com, спешим заверить вас, что ее нет. Стоит ли покупать эту книгу, чтобы получить дополнительный контент? Нет. Доступ к нашему сайту бесплатный, и вы можете изучить все его содержимое [на английском языке], а также скачать все шаблоны.
Приобретать книгу стоит, если вы хотите всегда иметь под рукой разработанные нами шаблоны на бумажном носителе или пополнить нашей книгой свою коллекцию. Это также поможет нам поддержать свой бизнес. Честно говоря, мы были удивлены тем, сколько людей купили первое издание этой книги. Это мотивировало нас на продолжение работы – мы всерьез занялись актуализацией и обновлением сайта и следующего издания книги. Надеемся, что эта работа будет продолжена!
На сайте daxpatterns.com все представленные в книге шаблоны также снабжены видеофрагментами. В них подробно объясняется, как использовать тот или иной шаблон и как работают формулы. Эти видео – платные. При этом вы можете купить их все или по одному – для конкретной темы, которую хотите изучить более подробно. Это дополнительная услуга, и она пользуется популярностью. Некоторым достаточно книги, другие хотят смотреть видео, а есть и такие, которым нужно все и сразу.
Как использовать эту книгу
Что вы найдете в данной книге? В каждой главе мы будем разбирать один конкретный шаблон, и читать ее можно без предварительного ознакомления с другими главами книги. Например, вы можете разбирать шаблон, касающийся курсов валют, без рассмотрения примеров с анализом покупательской корзины или вычислениями, связанными с логикой операций со временем.
Каждая глава начинается с краткого описания соответствующего бизнес-сценария, следом за которым идет подробный разбор примененного решения, включая код на языке DAX, использованный в примере. При этом описание самого листинга приводится довольно краткое, а меры при необходимости документируются в коде при помощи комментариев.
Для каждого шаблона вы можете загрузить соответствующий ему сопроводительный контент, короткая ссылка на который размещается в начале каждой главы. Как мы уже говорили, скачиваемые файлы представлены в двух форматах: для Power BI и Excel.
Книга может быть использована в качестве справочного пособия. Когда вам нужно реализовать тот или иной шаблон, вы не хотите читать длинные описания – вам нужен лишь код и краткое сопровождение к нему. В связи с этим мы решили не утомлять вас пространными размышлениями, поставив во главу угла код на языке DAX.
И все же перед тем как реализовывать на практике тот или иной шаблон, мы настоятельно советуем вам прочитать соответствующую главу целиком. Причина в том, что мы зачастую даем несколько вариантов решения сценария, и вам предстоит самому выбрать наиболее подходящий для вашего случая. Для каждого шаблона мы также предоставляем демонстрационные файлы в формате Power BI и Power Pivot для Excel. Иногда версии кода будут незначительно отличаться. В качестве решения предпочтение всегда будет отдаваться программному продукту Power BI, в котором на момент написания книги реализованы все актуальные новинки DAX. Некоторые из этих особенностей пока не были реализованы в Power Pivot, и именно это послужило поводом для нашего выбора в пользу Power BI.
Есть в данной книге одно исключение, и касается оно темы вычислений, связанных со временем. Как мы уже сказали, этому аспекту бизнес-аналитики мы уделили в книге особое внимание, представив четыре различных шаблона для подобных расчетов. Каждый из четырех шаблонов весьма объемен, а вместе они занимают более 40 % книги. Поэтому мы решили выделить отдельную вводную главу, посвященную вычислениям, связанным со временем, цель которой – помочь вам сделать правильный выбор шаблона для вашего сценария. Таким образом, если вам необходимо реализовать на практике расчеты, связанные с логикой операций со временем, для начала прочитайте введение, после чего приступайте к освоению главы, в которой представлено описание выбранного вами шаблона.
Требования
Мы хотим сразу оговориться о том, что данная книга не является полноценным учебником по языку DAX.
Мы предполагаем, что вы уже знакомы с этим языком, иначе вы не сможете в полной мере использовать представленные в данной книге шаблоны. В большинстве шаблонов применяются продвинутые техники DAX, которые вам следует освоить и использовать в своих рабочих сценариях. Читая эту книгу, вы не научитесь писать запросы на DAX с нуля. Скорее она позволит вам стать более квалифицированным разработчиком.
Мы надеемся, что вы будете использовать предложенные в книге шаблоны с последними версиями Power BI или Excel, поскольку язык DAX не стоит на месте, а постоянно развивается. Мы проверяли шаблоны в июньской версии Power BI 2020 года, Excel 2019 и Excel для Microsoft 365 версии 2006. Большинство примеров будут прекрасно работать и с более ранними версиями Power BI и Excel, но мы не можем гарантировать этого, поскольку не проводили тесты с использованием всех прежних версий продуктов.
Благодарности
Последним по порядку, но не по значению, во введении мы приведем раздел с благодарностями.
Кого мы больше всех хотим поблагодарить, так это вас. Работа, которую мы проделали над книгой, стала возможна только благодаря продолжительным дискуссиям с нашими читателями, пользователями, заказчиками и студентами – такими, как вы. Так что вы, даже не осознавая этого, можете считать себя в некотором смысле соавтором этой книги. А если разместите свои комментарии на наших общедоступных форумах, то фактически продолжите вносить вклад и в наши будущие работы.
Но есть и люди, которых мы хотим отметить отдельно за участие в создании данной книги. В первую очередь это Даниил Маслюк (Daniil Maslyuk), тщательнейшим образом проверивший все предложенные нами шаблоны на практике, нашедший все возможные ошибки и обеспечивший нас неоценимой обратной связью. Также хотелось бы сердечно поблагодарить Клэр Косту (Claire Costa) за вклад в редакцию наших текстов на английском и улучшение их восприятия. Точностью выражений и правильностью формулировок мы обязаны именно ей. Серджио Мурру (Sergio Murru) приложил руку к созданию версий сопроводительных файлов для Excel, что позволило пользователям Power Pivot для Excel также применять в работе наши шаблоны. Кроме того, хочется отметить Даниэле Перилли (Daniele Perilli), без которого книга и сайт не были бы столь привлекательными. За наполнение книги и все возможные ошибки в ней ответственность несем только мы, но если эта книга сможет помочь вам рассчитать нужные вам показатели в Excel или Power BI и хорошо разобраться в интересующих вас темах, в этом заслуга тех, кого мы перечислили.
Наслаждайтесь DAX!
Глава 1. Вычисления, связанные со временем
В данной главе мы представим вашему вниманию четыре шаблона вычислений, связанных со временем (time-related calculation), о которых подробнее будем говорить далее в книге. Цель этой главы – помочь вам выбрать наиболее подходящий шаблон в зависимости от поставленной задачи. Дело в том, что когда речь идет о вычислениях, связанных со временем, бывает довольно трудно выбрать лучший вариант для конкретного сценария.
Что же подразумевается под вычислениями, связанными со временем? Как ясно из определения, сюда включаются все возможные расчеты, в которых так или иначе фигурирует временная составляющая. В примерах вы встретите разные варианты вычислений, включая нарастающие итоги с начала года, квартала и месяца. В этих вычислениях значения учитываются с начала указанного временного периода (года, квартала или месяца соответственно) до отчетной даты и возвращается нужная агрегация. При этом определение временного периода меняется в зависимости от того, с каким календарем вы работаете: с григорианским или финансовым. На рис. 1.1 приведен пример вычисления нарастающих итогов, где YTD (year-to-date) означает итог с начала года, в QTD (quarter-to-date) – с начала квартала.
@Рис. 1.1. Примеры вычисления нарастающих итогов
Также в шаблоны работы со временем включаются вычисления показателей сравнения одного временного периода с другим. Например, вы можете сравнить продажи за текущий месяц с аналогичным показателем за этот же месяц годом ранее. Еще одним примером шаблона, связанного со временем, является вычисление скользящего среднего (moving average) за определенный период. Допустим, вам может быть интересно узнать скользящее среднее по продажам за последние 12 месяцев. Этот метод позволяет сгладить линии на графике и исключить из вычислений влияние сезонности. В четырех шаблонах для работы со временем реализован соответствующий набор вычислений.
Что отличает один шаблон от другого, так это определение используемого календаря. Вы, наверное, заметили, что на рис. 1.1 для нарастающего итога с начала года выделены сразу два столбца. В зависимости от того, какой календарь вы используете – григорианский или финансовый, – показатели будут разные. Из-за разницы в определениях календарей вычисления, связанные со временем, могут значительно усложняться.
Например, в вашей компании может быть принят недельный календарь на основе стандарта ISO или даже свой собственный. В календаре, основанном на неделях, каждый месяц и год начинаются с одного и того же дня недели. Таким образом, год в соответствии с недельным календарем может начаться в предыдущем календарном году, а закончиться – в следующем. Усложняется ситуация и тем, что в некоторых календарях год может разбиваться на 13 периодов вместо привычных 12 для нужд учета. Именно требования к календарю должны лежать в основе выбора шаблона в той или иной ситуации.
Четыре представленных шаблона для работы со временем в порядке увеличения сложности:
Стандартные вычисления на основе времени.
Вычисления, базирующиеся на месяцах.
Вычисления, базирующиеся на неделях.
Пользовательские вычисления, связанные со временем.
Шаблон стандартных вычислений для работы со временем реализован при помощи встроенных функций логики операций со временем языка DAX. В этом случае предполагается, что вы используете для расчетов стандартный григорианский календарь, а ваш финансовый год начинается одновременно с началом квартала по григорианскому календарю. Например, функции логики операций со временем прекрасно работают, если ваш финансовый год начинается 1 июля (начало третьего квартала по григорианскому календарю). В то же время эти функции могут выдавать неожиданные результаты при начале финансового года 1 марта. И причины здесь две: во-первых, эта дата не совпадает с началом квартала по григорианскому календарю, во-вторых, есть застарелая ошибка в обработке високосных годов при работе с финансовыми календарями. Несмотря на указанные ограничения, данный шаблон достаточно легко использовать и реализовывать, поскольку он опирается на стандартные функции DAX и работает с обычной таблицей дат.
В следующих трех шаблонах стандартные функции логики операций со временем не используются. Вместо этого они основаны на обычных функциях DAX, что добавляет гибкости в отношении определения кварталов, месяцев и недель. При реализации этих шаблонов вам необходимо создать собственную таблицу дат со столбцами, которые будут использоваться в мерах DAX для определения составных временных частей года. Например, вам понадобится один столбец для года, один для квартала, месяца, а также дополнительные столбцы, которые позволят упростить вычисления.
Более того, при определении и фильтрации периодов должны быть учтены многие детали. Некоторые вычисления, которые могут представляться простыми с точки зрения человека, могут оказаться довольно сложными для компьютера. Сравнивая текущий квартал с предыдущим, вы будете оперировать разным количеством дней в этих периодах, поскольку первый квартал года (с января по март) включает в себя меньше дней по сравнению со вторым (с апреля по июнь). То же самое касается и месяцев: январь всегда длиннее февраля, и если вы захотите сравнивать показатели по месяцам, вам придется оперировать разным количеством дней.
Если же стандартные функции логики операций со временем не подходят для вашего сценария, вам необходимо обратиться к одному из следующих трех шаблонов, каждый из которых предполагает создание собственной таблицы дат.
Шаблон, основанный на месяцах, является наиболее простым из них. Вычисления в нем реализованы с учетом того, что вас не будет интересовать детализация по дням. К примеру, такой шаблон подойдет в случае, если вы собираетесь сравнивать друг с другом исключительно месяцы. Если вы предполагаете в процессе анализа опускаться на уровень ниже, этот шаблон не годится. Допустим, если вам понадобится сравнить показатели за три дня в одном квартале с теми же тремя днями в предыдущем квартале, потенциала выбранного шаблона вам просто не хватит. Помимо недостатков данного шаблона в виде ограничений, заключающихся в невозможности проводить детализированный анализ, есть у него и свои плюсы: работает он достаточно быстро, и реализовать его относительно просто. Более того, он прекрасно справляется со сценариями, предполагающими наличие более 12 месяцев во временном интервале. Данный шаблон обладает гибкостью, характерной для пользовательских шаблонов, в сочетании с легкостью реализации – большей в сравнении со стандартным шаблоном. Если вас не пугает ограничение в виде месячной гранулярности, этот шаблон может стать вашим выбором.
Шаблоны, базирующиеся на неделях, рассматривают в качестве календарной основы, как ясно из названия, недели. ISO 8601 представляет собой один из стандартов определения недельной системы дат, хотя во многих странах приняты другие национальные стандарты для идентификации годов, кварталов и недель. В году 52 или 53 недели, а квартал состоит из 13 недель, при этом каждый квартал по составу недель в месяцах может относиться к одной из следующих категорий: 5+4+4, 4+5+4 или 4+4+5. Если в году 53 недели, в одном из кварталов будет присутствовать 14 недель. Поскольку неделя совсем не обязательно должна полностью входить в месяц, группу недель, входящих в квартал, стоит называть периодом, даже если зачастую к ней обращаются как к месяцу. Именно поэтому в следующем описании мы будем называть месяцы периодами.
В связи с тем что базовой сущностью недельного календаря является именно неделя, не может быть никакого соответствия между годом в григорианском календаре и недельном. Недельный календарь всегда начинается с одного и того же дня недели, например с понедельника или воскресенья. И этот день недели далеко не всегда будет выпадать на 1 января. Для недельного календаря вполне нормальной будет ситуация, при которой год может начаться, к примеру, 29 декабря предыдущего года или 3 января текущего. Несмотря на всю свою необычность, у недельных календарей есть и свои достоинства, в числе которых то, что каждый «месяц» в квартале насчитывает одинаковое количество дней недели. Таким образом, при сравнении двух кварталов будет сопоставляться одинаковое количество дней при одинаковом распределении дней недели.
Недельные календари требуют наличия в модели данных отдельной таблицы дат со столбцами, облегчающими вычисления в DAX. Стоит отметить, что в языке DAX нет готовых функций для осуществления вычислений в подобных нестандартных календарях. В связи с этим все вычисления в этом случае выполняются при помощи особого кода на DAX. Что касается сложности шаблонов, то для недельных календарей она выше по сравнению с месячными, поскольку здесь вы можете опускаться в фильтрах до детализации по дням. Если в вашем сценарии предусмотрена недельная аналитика, этот шаблон подойдет вам лучше остальных.
Шаблон, предполагающий пользовательские вычисления, связанные со временем, является одновременно самым гибким, но и наиболее сложным в реализации. Расчеты в этом шаблоне схожи со стандартным шаблоном для работы со временем, за тем лишь исключением, что вычисления производятся при помощи базовых функций языка DAX – без использования специальных функций логики операций со временем. В связи с этим шаблон получается невероятно гибким, и вы можете свободно влиять на поведение всех сопутствующих вычислений. Но с большой гибкостью приходит и большая сложность.
Код на языке DAX при реализации таких шаблонов зачастую оказывается слишком сложным. К тому же разработчику необходимо уделять большое внимание мелким деталям. Используйте этот шаблон только в том случае, если остальные не отвечают требованиям вашей бизнес-логики и вам просто необходимо воспользоваться максимально возможной гибкостью системы дат.
Так какой же шаблон для работы со временем вам использовать?
если ваши требования к работе с датами и временем укладываются в понятия, принятые в григорианском календаре, вашим выбором может быть стандартный шаблон;
если для ваших отчетов вполне достаточно уровня гранулярности по месяцам (а это зачастую так, даже если кажется, что иначе), вам подойдет месячный шаблон – он достаточно быстрый и простой;
если ваша бизнес-логика завязана на недельный календарь, вам предстоит присмотреться к соответствующему шаблону;
в случае, если всего перечисленного выше вам недостаточно и вам действительно нужна максимальная гибкость, что ж, готовьтесь к трудному и увлекательному путешествию в мир контекстов фильтра с погружением в пользовательские вычисления, связанные со временем.
Помните, что в проектах, связанных с бизнес-аналитикой, зачастую проще – значит, лучше. Выберите наиболее простой шаблон, удовлетворяющий вашим требованиям. Но если вы хотите узнать обо всех возможных шаблонах перед тем, как сделать свой выбор, вы можете прочитать все четыре главы, посвященные шаблонам для работы со временем, и только после этого остановиться на одном из предложенных вариантов.
Текст неполный и представлен для ознакомления…
Купить книгу:
https://dmkpress.com/catalog/computer/data/978-5-97060-909-5/
Промокоды:
Бумажная версия: промокод Ginko_DAX_Patterns_paper
Версия PDF: промокод Ginko_DAX_Patterns_PDF