Глава 1. Что такое DAX?
DAX, или выражения анализа данных (Data Analysis eXpressions), – это язык программирования в средах Microsoft Power BI, Microsoft Analysis Services и Microsoft Power Pivot для Excel. Он был создан в 2010 году – с первым выходом надстройки PowerPivot для Microsoft Excel 2010. Да, тогда название PowerPivot писалось слитно, а пробел появился лишь через три года. С тех пор язык DAX постоянно набирал популярность как в среде пользователей Excel, применяющих его для создания моделей данных в Power Pivot, так и в сообществе бизнес-аналитики, где этот язык используется для проектирования моделей в Power BI и Analysis Services. DAX присутствует во многих инструментах, которые объединяет один табличный движок (Tabular). Именно поэтому мы будем часто говорить просто о табличных моделях, подразумевая все инструменты сразу.
DAX – простой язык. При этом он существенно отличается от других языков программирования, так что на освоение его новых концепций у вас может уйти немало времени. По опыту преподавания DAX тысячам студентов мы можем заметить, что с основами языка проблем обычно не возникает – можно приступить к его использованию уже через несколько часов после начала обучения. Что касается продвинутых тем вроде контекста вычисления, итерационных функций и преобразования контекста, они могут вызвать серьезные затруднения. Но не сдавайтесь! Наберитесь терпения. Когда вы вникнете в эти концепции, вы поймете всю простоту языка DAX. К нему нужно просто привыкнуть.
В начале первой главы мы расскажем о том, что представляет из себя модель данных с таблицами и связями. Мы советуем прочитать эти страницы всем, независимо от опыта, чтобы понять, какую терминологию мы будем использовать на протяжении всей книги, описывая таблицы, модели и разные типы связей.
В следующих разделах мы дадим полезные советы читателям, имеющим определенные навыки работы с другими языками, такими как SQL, MDX и язык формул Microsoft Excel. Каждому из этих языков мы отведем отдельный раздел, чтобы читатели могли сравнить их с DAX. Если вам это поможет, попробуйте смотреть на DAX через призму этих языков. Прочитав заключительный раздел «DAX для пользователей Power BI», переходите к следующей главе, с которой, по сути, и начинается наше путешествие в мир DAX.
Введение в модель данных
Язык DAX предназначен для расчета бизнес-показателей посредством формул в модели данных. Некоторые читатели могут знать, что из себя представляет модель данных. Для остальных мы сделаем разъяснение.
Модель данных (data model) – это набор таблиц, объединенных связями.
Все мы знаем, что такое таблица. Это перечисление строк, содержащих информацию, при этом каждая строка поделена на столбцы. Столбец в свою очередь характеризуется определенным типом данных и содержит единый фрагмент информации. Обычно мы называем строку в таблице записью. Табличный способ хранения информации очень удобен в плане организации данных. По сути, таблица сама по себе является моделью данных, пусть и в своей простейшей форме. Так что когда мы вводим на лист Excel текст и цифры, мы создаем модель данных.
Если модель состоит из нескольких таблиц, вполне вероятно, что вам захочется связать их. Связь (relationship) представляет собой объединение двух таблиц. Такие таблицы мы называем связанными (related). Графически связь двух таблиц обозначается линией между ними. На рис. 1.1 показан пример модели данных.
@Рис. 1.1. Модель данных, состоящая из шести таблиц
Далее перечислим важные аспекты связей между таблицами:
таблицы, объединенные связью, выполняют разные роли. Одна из них представляет сторону «один», а вторая – «многие», которые помечены на схеме данных символами «1» и «*» (звездочка) соответственно. Обратите внимание на связь между таблицами Product (Товары) и Product Subcategory (Подкатегории товаров) на рис. 1.1. Одной подкатегории может принадлежать несколько товаров, тогда как один товар может представлять только одну подкатегорию. Таким образом, таблица Product Subcategory являет собой сторону «один» в этой связи, а Product – сторону «многие»;
существуют особые виды связей. Это связи «один к одному» (1:1) и слабые связи (weak relationships). В связи «один к одному» обе таблицы представляют собой сторону «один», тогда как в слабых связях они могут находиться на стороне «многие». Такие особые виды связей не слишком распространены, и мы подробно обсудим из в главе 15;
столбцы, использующиеся для объединения таблиц и обычно имеющие одинаковые имена, называются ключами (keys) связи. При этом в ключевом столбце таблицы, представляющей сторону «один», должны находиться уникальные значения без пропусков. В то же время в таблице «многие» значения в ключевом столбце могут повторяться, и чаще всего это так и есть. Столбец, содержащий исключительно уникальные значения, называется ключом таблицы;
связи могут образовывать цепочки. Каждый товар принадлежит какой-то подкатегории, которая в свою очередь представляет определенную категорию товаров. Следовательно, каждый товар можно отнести к конкретной категории. Но чтобы получить ее название, необходимо пройти к ней от товаров через цепочку из двух связей. На модели данных, представленной на рис. 1.1, присутствует цепочка связей, состоящая сразу из трех звеньев – от таблицы Sales к Product Category;
стрелкой посередине связи обозначается направление перекрестной фильтрации (cross filter direction). По рис. 1.1 видно, что связь между таблицами Sales и Product отмечена стрелками в обоих направлениях, тогда как остальные связи в модели – однонаправленные. Стрелкой обозначается направление распространения фильтра по этой связи. Поскольку выбор правильных направлений для фильтров является одним из важнейших навыков в работе с моделью данных, мы подробно обсудим эту тему в следующих главах. Обычно мы не советуем пользователям включать двунаправленную фильтрацию (bidirectional filtering) в связях, как сказано в главе 15. В этой модели такая связь присутствует исключительно в образовательных целях.
Введение в направление связи
Каждая связь может характеризоваться однонаправленной или двунаправленной перекрестной фильтрацией (кросс-фильтрацией). Фильтр всегда распространяется от стороны «один» к стороне «многие». Если же связь двунаправленная, то есть обозначена на схеме двумя разнонаправленными стрелками, фильтр по ней может распространяться и в обратном направлении.
Приведем пример, который поможет вам лучше разобраться в этом. Если построить отчет на основе модели данных, представленной на рис. 1.1, вынеся годы (Calendar Year) на строки, а количество проданных товаров (Quantity) и количество наименований товаров (Count of Product Name) – в область значений, мы увидим вывод, показанный на рис. 1.2.
@Рис. 1.2. Отчет демонстрирует эффект фильтрации по нескольким таблицам
Столбец Calendar Year принадлежит таблице дат (Date). А поскольку таблица Date представляет сторону «один» в связи с продажами (Sales), движок отфильтрует таблицу Sales по годам. Именно поэтому количество проданных товаров в отчете показано с разбивкой по годам.
С таблицей товаров (Products) дело обстоит несколько иначе. Фильтрация в этом случае работает корректно, поскольку связь, объединяющая таблицы Sales и Product, является двунаправленной. Выводя в отчет количество наименований товаров, мы фактически получаем ежегодно продаваемый ассортимент посредством фильтра, распространенного от таблицы Sales к Product. Если бы связь между Sales и Product была однонаправленной, результат был бы иным, и мы расскажем об этом в следующих разделах.
Если модифицировать отчет, вынеся на строки цвет товаров (Color) и добавив в область значений количество дат (Count of Date), результат также поменяется. Вывод этого отчета можно видеть на рис. 1.3.
@Рис. 1.3. В отчете показано, что в отсутствие двунаправленной связи фильтрация таблиц не выполняется
Столбец Color, вынесенный на строки отчета, принадлежит таблице Product. А поскольку Product представляет сторону «один» в связи с таблицей Sales, значения в столбце Quantity посчитались корректно. Поле Count of Product Name правильно отфильтровалось, поскольку его источником является таблица Product, вынесенная на строки. Неожиданные значения мы видим в столбце Count of Date. Здесь для всех строк указано одно и то же число, представляющее общее количество строк в таблице Date.
Фильтр, идущий от столбца Color, не распространяется на Date, поскольку связь между таблицами Date и Sales – однонаправленная. Таким образом, несмотря на то, что фильтр в таблице Sales активен, он не может распространиться на таблицу Date по причине однонаправленности связи.
Если сделать связь между таблицами Date и Sales двунаправленной, результат будет иным, как видно из рис. 1.4.
Теперь в столбце отображается количество дней, когда как минимум один товар выбранного цвета был продан. На первый взгляд кажется, что стоит все связи в модели сделать двунаправленными, чтобы позволить фильтрам распространять свое действие во все стороны и доставать правильные данные. Как вы узнаете из этой книги, такой подход почти никогда не будет оправдан. Вы должны выбирать направление фильтрации для связей в зависимости от модели, с которой работаете. Если вы последуете нашим советам, то откажетесь от применения двунаправленной фильтрации там, где это возможно.
@Рис. 1.4. Если активировать двунаправленную фильтрацию, таблица Date будет отфильтрована по столбцу Color
DAX для пользователей Excel
Велика вероятность, что вы знакомы с языком формул Excel, который немного напоминает DAX. В конце концов корни DAX лежат в Power Pivot для Excel, и разработчики сделали все, чтобы эти языки были похожими. Эти сходства облегчат вам переход на DAX. Но не стоит забывать и о различиях в этих языках.
Ячейки против таблиц
В Excel все вычисления производятся над ячейками, которые обладают координатами. Так что мы можем написать формулу вроде этой:
= (A1 * 1.25) — B2
В DAX концепция ячеек с координатами просто отсутствует. Этот язык работает с таблицами и столбцами, а не с отдельными ячейками. Как следствие, выражения DAX обращаются именно к таблицам и столбцам, что сказывается на синтаксисе языка. Однако концепция таблиц и столбцов не нова для Excel. Если выделить диапазон и воспользоваться пунктом Format as Table (Форматировать как таблицу), можно писать формулы в Excel, обращающиеся непосредственно к таблицам и столбцам. На рис. 1.5 в столбце SalesAmount вычисляется выражение, ссылающееся на столбцы в той же таблице, а не на ячейки в рабочей книге.
@Рис. 1.5. В формулах Excel можно ссылаться на столбцы таблицы
В Excel можно обращаться к столбцам, используя следующий формат: [@ColumnName]. Здесь ColumnName – название столбца, а символ @ говорит о том, что необходимо взять значение из текущей строки. Синтаксис получился не самым интуитивно понятным, но мы обычно и не пишем такие выражения вручную. Они появляются автоматически при нажатии на ячейку: Excel сам заботится о вставке нужного кода.
Таким образом, в Excel есть два разных вида вычислений. Можно использовать стандартное обращение к ячейкам – в этом случае формула для ячейки F4 будет выглядеть так: E4*D4. Или же применять ссылки на столбцы внутри таблицы. Это позволит использовать одинаковые выражения во всех ячейках столбца, а Excel в своих расчетах будет брать значение из конкретной строки.
В отличие от Excel, DAX работает исключительно с таблицами. Все формулы должны ссылаться на столбцы внутри таблиц. Например, в DAX предыдущая формула будет выглядеть так:
Sales[SalesAmount] = Sales[ProductPrice] * Sales[ProductQuantity]
Как видите, каждое название столбца предваряется наименованием соответствующей таблицы. В Excel мы не указываем названия таблиц, поскольку там формулы работают внутри одной таблицы. DAX же работает в модели данных, состоящей из нескольких таблиц. Как следствие, мы просто обязаны конкретно указывать таблицы, ведь в разных таблицах могут находиться столбцы с одинаковыми названиями.
Многие функции DAX работают подобно аналогичным функциям в Excel. К примеру, функция IF в обоих языках применяется одинаково:
Excel ЕСЛИ ( [@SalesAmount] > 10; 1; 0)
DAX IF ( Sales[SalesAmount] > 10; 1; 0)
Единственным существенным отличием между Excel и DAX является способ обращения к целому столбцу. В Excel, как мы уже говорили, символ @ в выражении [@ProductQuantity] означает, что необходимо взять значение из текущей строки. В DAX нет необходимости указывать этот факт явно, поскольку такое поведение является для языка обычным. В Excel мы можем обратиться ко всем строкам в столбце, убрав из формулы символ @. Это можно видеть на рис. 1.6.
@Рис. 1.6. В Excel можно сослаться на весь столбец, опустив символ @ в формуле
Значение столбца AllSales одинаковое для всех строк и равно общему итогу по столбцу SalesAmount. Иными словами, в Excel существует четкое синтаксическое разграничение между обращением к ячейке в конкретной строке и к столбцу в целом.
DAX ведет себя иначе. В этом языке для вычисления столбца AllSales из рис. 1.6 можно было бы использовать следующую формулу:
AllSales := SUM ( Sales[SalesAmount] )
И здесь нет никаких отличий между извлечением значения из текущей строки или из всего столбца. DAX понимает, что мы хотим просуммировать все значения из столбца, поскольку его название передается в качестве аргумента в агрегирующую функцию (здесь это функция SUM). Таким образом, если Excel требует явного указания, какие данные извлекать из столбца, DAX решает эту неоднозначность автоматически. Такая разница в подходах к вычислениям может смущать – по крайней мере, поначалу.
Excel и DAX: два функциональных языка
В чем язык формул Excel и DAX похожи, так это в том, что оба они являются функциональными языками программирования. Функциональные языки состоят из выражений, в основе которых лежат вызовы функций. В Excel и DAX не реализованы концепции операторов, циклов и переходов, характерные для большинства языков программирования. В DAX буквально все является выражениями. Это бывает непросто понять тем, кто приходит из других языков программирования, а для пользователей Excel, наоборот, должно быть привычно.
Итерационные функции в DAX
Концепция, которая может оказаться для вас в новинку, – это итерационные функции или просто итераторы (iterators). В Excel все расчеты выполняются последовательно, по одному за раз. В предыдущем примере вы видели, что для того, чтобы рассчитать итог по продажам, мы создали столбец, в котором цена умножалась на количество. На втором шаге мы подсчитывали сумму по этой колонке. Получившийся результат впоследствии можно использовать в качестве знаменателя при подсчете, например, доли продаж по каждому товару.
В DAX все это можно сделать за один шаг с помощью итерационных функций. Итератор делает ровно то, что и должен, исходя из названия, – проходит по таблице и производит вычисления в каждой строке, одновременно агрегируя запрошенное значение.
Таким образом, вычисления из предыдущего примера можно произвести при помощи одной итерационной функции SUMX:
AllSales :=
SUMX (
Sales;
Sales[ProductQuantity] * Sales[ProductPrice]
)
Такой подход имеет как достоинства, так и недостатки. К достоинствам можно отнести то, что мы можем производить множество вычислений за один шаг, не беспокоясь о создании вспомогательных столбцов, функциональность которых ограничивается лишь промежуточными формулами. Недостатком же является то, что программирование на DAX менее визуально по сравнению с формулами Excel. Мы ведь даже не видим столбца с результатом умножения цены на количество – она существует только во время вычисления.
Как вы узнаете позже, у вас есть возможность создания вычисляемых столбцов для хранения подобных промежуточных вычислений. Но делать это не рекомендуется, поскольку в этом случае будут задействованы дополнительные ресурсы памяти и может пострадать производительность, если вы не используете режим DirectQuery совместно с Aggregations, о чем мы поговорим в главе 18.
DAX требует изучения теории
Будем откровенны: DAX является не единственным языком программирования, для использования которого вам понадобится обширная теоретическая база. Разница лишь в подходе. Признайтесь, вы ведь частенько ищете в интернете сложные формулы и шаблоны, которые помогут вам в решении вашего собственного сценария. И шансы на то, что вы найдете подходящую формулу для Excel, достаточно высоки – вам останется лишь адаптировать ее под свои нужды.
Но в DAX дела обстоят иначе. Вам придется досконально изучить этот язык и понять, как работает контекст вычисления, чтобы написать работающий код. Без должной теоретической базы вам может показаться, что DAX производит свои вычисления каким-то магическим образом, или что он выдает цифры, не имеющие с реальностью ничего общего. Проблема не в DAX, а в том, что вы не понимаете всех тонкостей его работы.
К счастью, теоретическая база языка DAX ограничивается всего несколькими концепциями, которые мы опишем в главе 4. Приготовьтесь много учиться. После освоения этой главы DAX перестанет быть для вас тайной, а мастерство его использования будет зависеть исключительно от приобретенного опыта. Помните: знание – всего лишь полдела. И не пытайтесь двигаться дальше, пока досконально не освоите контекст вычисления.
DAX для разработчиков SQL
Если вы знакомы с языком SQL, значит, у вас уже есть опыт работы с множеством таблиц и связей. В этом плане вы почувствуете себя в DAX как дома. По сути, вычисления здесь базируются на выполнении запросов к нескольким таблицам, объединенным связями, и агрегировании значений.
Работа со связями
Первые отличия между SQL и DAX заметны в области организации связей в модели данных. В SQL можно настроить внешние ключи в таблицах для определения связей, но движок никогда не будет использовать эти связи без явного на то указания. Например, если у нас есть таблицы Customers и Sales, и столбец CustomerKey является первичным ключом в Customers и внешним – в Sales, можно написать следующий запрос:
SELECT
Customers.CustomerName,
SUM ( Sales.SalesAmount ) AS SumOfSales
FROM
Sales
INNER JOIN Customers
ON Sales.CustomerKey = Customers.CustomerKey
GROUP BY
Customers.CustomerName
Хотя мы определили в модели внешние ключи для осуществления связей, нам все равно необходимо всякий раз явно указывать в запросе условия для выполнения соединений. Это приводит к увеличению объема запросов, зато можно каждый раз использовать разные условия для связей, что дает максимум свободы в извлечении данных.
В DAX связи являются составной частью модели данных, и все они – LEFT OUTER JOIN. А раз так, вам нет необходимости каждый раз указывать их в запросе, DAX автоматически будет использовать связи при задействовании объединенных таблиц. Так что на DAX можно переписать предыдущий запрос SQL следующим образом:
EVALUATE
SUMMARIZECOLUMNS (
Customers[CustomerName];
«SumOfSales», SUM ( Sales[SalesAmount] )
)
Поскольку движок знает о созданной связи между таблицами Sales и Customers, объединение таблиц в запросе происходит автоматически. После этого функции SUMMARIZECOLUMNS останется выполнить группировку по столбцу Customers[CustomerName], причем для этого нет определенного ключевого слова: функция SUMMARIZECOLUMNS автоматически группирует данные по выбранным столбцам.
DAX как функциональный язык
SQL – декларативный язык. Вы определяете набор данных, который желаете извлечь, посредством оператора SELECT, при этом не беспокоясь о том, как именно движок это сделает.
DAX, напротив, является функциональным языком. В нем каждое выражение является вызовом функции. При этом параметры функции в свою очередь также могут быть вызовами функций. Анализ всех этих параметров приводит к созданию сложного плана выполнения запроса, который и вычисляется движком DAX с целью получить результат.
Например, если нам понадобится получить информацию о покупателях, живущих в Европе, мы можем написать следующих запрос на SQL:
SELECT
Customers.CustomerName,
SUM ( Sales.SalesAmount ) AS SumOfSales
FROM
Sales
INNER JOIN Customers
ON Sales.CustomerKey = Customers.CustomerKey
WHERE
Customers.Continent = ‘Europe’
GROUP BY
Customers.CustomerName
В языке DAX мы не объявляем условие в операторе WHERE. Вместо этого мы используем специальную функцию FILTER для осуществления фильтрации, как показано ниже:
EVALUATE
SUMMARIZECOLUMNS (
Customers[CustomerName];
FILTER (
Customers;
Customers[Continent] = «Europe»
);
«SumOfSales»; SUM ( Sales[SalesAmount] )
)
Вы видите, как работает функция FILTER: она возвращает только покупателей, проживающих в Европе, как мы и хотели. Порядок, в котором мы встраиваем функции в код, и виды функций, которые используем, очень важны как с точки зрения получения результата, так и в плане производительности запросов. В языке SQL это тоже важно, хотя там мы больше надеемся на оптимизатор запросов (query optimizer) при построении наилучшего плана выполнения. В DAX оптимизатор также занят своими прямыми обязанностями, но на вас как на разработчике лежит большая ответственность за написание быстро работающего кода.
DAX как язык программирования и язык запросов
В SQL существует четкое разделение между языком запросов и языком программирования, то есть набором инструкций, используемых для создания хранимых процедур (stored procedures), представлений (views) и других объектов в базе данных. В каждом диалекте SQL присутствуют свои операторы, призванные обогатить язык. Но в DAX не делается четких разграничений между языком запросов и языком программирования. Множество функций работают с таблицами и возвращают таблицы в качестве результата. Функция FILTER из предыдущего кода – лишь один из примеров.
В этом отношении DAX, пожалуй, проще SQL. Изучая его как язык программирования – а им он изначально и является, – вы узнаете все необходимое для использования его и в качестве языка запросов.
Подзапросы и условия в DAX и SQL
Одной из мощнейших особенностей языка запросов SQL является возможность использования подзапросов. В DAX применяется похожая концепция, но с учетом функциональной направленности языка.
Например, чтобы извлечь информацию о покупателях, сделавших покупки на сумму более $100, можно написать следующий запрос SQL:
SELECT
CustomerName,
SumOfSales
FROM (
SELECT
Customers.CustomerName,
SUM ( Sales.SalesAmount ) AS SumOfSales
FROM
Sales
INNER JOIN Customers
ON Sales.CustomerKey = Customers.CustomerKey
GROUP BY
Customers.CustomerName
) AS SubQuery
WHERE
SubQuery.SumOfSales > 100
В DAX можно добиться похожего эффекта с использованием вложенных функций, как показано ниже:
EVALUATE
FILTER (
SUMMARIZECOLUMNS (
Customers[CustomerName];
«SumOfSales», SUM ( Sales[SalesAmount] )
);
[SumOfSales] > 100
)
В этом коде результаты подзапроса, извлекающего CustomerName и SumOfSales, прогоняются через функцию FILTER, которая оставляет в результирующем наборе только строки со значениями SumOfSales, превышающими 100. В данный момент вы можете не понимать, что делает этот код. Но, постепенно постигая все премудрости DAX, вы обнаружите, что в этом языке использовать подзапросы намного легче, чем в SQL, и код получается более естественным по причине функциональной природы DAX.
DAX для разработчиков MDX
Многие специалисты в области бизнес-аналитики переключаются на DAX как на новый язык табличного движка Tabular. В прошлом они использовали язык MDX для построения и обращения к многомерным моделям данных (Multidimensional models) Analysis Services. Если вы из их числа, приготовьтесь изучать абсолютно новый язык, поскольку у DAX и MDX не так много общего. Более того, некоторые концепции в DAX будут сильно напоминать вам MDX, но смысл их будет совершенно иным.
По опыту можем сказать, что путь от MDX к DAX наиболее тернист. Чтобы изучить DAX, вам придется забыть все, что вы знаете о MDX. Выкиньте из головы многомерные пространства (multidimensional spaces) и приготовьтесь к приобретению новых знаний с нуля.
Многомерность против табличности
MDX работает в многомерном пространстве, определенном моделью данных. Его форма зависит от измерений (dimensions) и иерархий (hierarchies), присутствующих в модели, и в свою очередь определяет систему координат многомерного пространства. Пересечения наборов элементов в разных измерениях определяют точки в многомерном пространстве. Может понадобиться немало времени, чтобы понять, что элемент [All] любой иерархии атрибута – это не более чем точка в многомерном пространстве.
В DAX все намного проще. Тут нет измерений, элементов и точек в многомерном пространстве. Да и самого многомерного пространства тоже нет. Есть иерархии, которые мы можем определять в модели данных, но они существенно отличаются от иерархий в MDX. Пространство DAX построено на таблицах, столбцах и связях. Таблицы в модели Tabular не являются ни группами мер (measure group), ни измерениями. Это просто таблицы, для проведения вычислений в которых вы можете сканировать их, фильтровать и суммировать значения. Все базируется на двух основных концепциях: таблицах и связях.
Скоро вы узнаете, что с точки зрения моделирования данных табличный движок предоставляет меньше возможностей по сравнению с многомерным. Но в данном случае это не означает, что в вашем распоряжении будет меньший аналитический потенциал, поскольку вы всегда можете использовать DAX в качестве языка программирования, чтобы обогатить модель данных. Истинный потенциал движка Tabular заключается в потрясающей скорости DAX. Обычно разработчики стараются не злоупотреблять языком MDX без необходимости, поскольку оптимизировать такие запросы бывает непросто. DAX, напротив, славится своим впечатляющим быстродействием. Так что большинство сложных вычислений вы будете производить не в модели данных, а в формулах DAX.
DAX как язык программирования и язык запросов
И DAX, и MDX являются одновременно и языком программирования, и языком запросов. В MDX это разделение обусловлено наличием скриптов, в которых помимо базового языка MDX можно использовать специальные операторы вроде SCOPE, применимые исключительно в скриптах. В запросах MDX на извлечение данных вы пользуетесь оператором SELECT. В DAX все несколько иначе. Вы можете использовать его как язык программирования для определения вычисляемых столбцов, вычисляемых таблиц и мер. И если концепция вычисляемых столбцов и таблиц является новинкой в DAX, то меры очень напоминают вычисляемые элементы в MDX. Можно также использовать DAX в качестве языка запросов – например, для извлечения информации из модели Tabular при помощи Службы отчетов (Reporting Services). При этом в функциях DAX нет четкого разграничения в плане использования – все они могут быть применены как в запросах, так и в вычислении выражений. Более того, в модели Tabular можно также использовать запросы, написанные на языке MDX. Таким образом, хотя MDX и может использоваться с табличной моделью данных в качестве языка запросов, когда речь идет о программировании в среде Tabular, единственным вариантом является DAX.
Иерархии
Производя большинство вычислений с помощью языка MDX, вы полагаетесь на иерархии. Если вам необходимо получить сумму продаж по предыдущему году, вам придется извлечь PrevMember из CurrentMember иерархии Year и использовать это выражение для переопределения фильтра в MDX. Например, вы можете написать такую формулу для осуществления расчетов по предыдущему году на MDX:
CREATE MEMBER CURRENTCUBE.[Measures].[SamePeriodPreviousYearSales] AS
(
[Measures].[Sales Amount],
ParallelPeriod (
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
)
);
В мере используется функция ParallelPeriod, возвращающая соседний элемент относительно CurrentMember на иерархии Calendar. Таким образом, это вычисление базируется на иерархиях, определенных в модели. В DAX мы бы для этого использовали контекст фильтра и стандартные функции для работы с датой и временем, как показано ниже:
SamePeriodPreviousYearSales :=
CALCULATE (
SUM ( Sales[Sales Amount] );
SAMEPERIODLASTYEAR ( ‘Date'[Date] )
)
Можно произвести это вычисление разными способами, в том числе при помощи функции FILTER, но идея остается прежней: вместо использования иерархий мы применяем фильтрацию таблиц. Это очень существенное различие, и вам, вероятно, будет не хватать иерархий в DAX, пока не привыкнете к новой для себя концепции.
Еще одним весомым отличием между этими языками является то, что в MDX вы ссылаетесь на [Measures].[Sales Amount], тогда как функция агрегации, которая вам нужна, уже определена в модели. В DAX предопределенные агрегации не используются. Фактически, как вы заметили, вычисляемое выражение в приведенном выше примере следующее: SUM(Sales[Sales Amount]). Никаких предопределенных агрегаций в модели нет. Мы определяем их тогда, когда нам нужно. Всегда можно создать меру, вычисляющую сумму продаж, но эта тема выходит за рамки этого раздела и будет описана позже в этой книге.
Более существенным отличием DAX от MDX является то, что в MDX очень активно используется инструкция SCOPE для реализации бизнес-логики (опять же с использованием иерархий), тогда как в DAX применяется совсем другой подход. Вообще, работы с иерархиями не хватает этому языку.
Например, если нам нужно очистить меру на уровне Year, в MDX мы могли бы написать следующее выражение:
SCOPE ( [Measures].[SamePeriodPreviousYearSales], [Date].[Month].[All] )
THIS = NULL;
END SCOPE;
В DAX нет функций, похожих на SCOPE, и для получения аналогичного результата придется выполнить проверку контекста фильтра, как показано ниже:
SamePeriodPreviousYearSales :=
IF (
ISINSCOPE ( ‘Date'[Month] );
CALCULATE (
SUM ( Sales[Sales Amount] );
SAMEPERIODLASTYEAR ( ‘Date'[Date] )
);
BLANK ()
)
Из кода функции понятно, что она возвратит результат, только если пользователь находится в календарной иерархии на уровне месяца или ниже. В противном случае функция вернет пустое значение (BLANK). Позже вы узнаете, как работает эта функция. Стоит отметить, что это выражение более уязвимо к ошибкам, чем код на MDX. Да, честно говоря, языку DAX очень не хватает функций для работы с иерархиями.
Вычисления на конечном уровне
Используя язык MDX, вы, возможно, привыкли избегать проведения расчетов на конечном уровне (leaf-level) элементов. Это настолько медленная операция, что всегда будет предпочтительнее предварительно рассчитывать значения и использовать агрегацию для возврата результата. В DAX вычисления на конечном уровне работают невероятно быстро, а предварительные агрегации служат другим целям и используются только в работе с большими наборами данных. Вам придется несколько изменить подход к проектированию моделей данных. В большинстве случаев модели, идеально подходящие для многомерной среды SQL Server Analysis Services, будут не лучшим образом показывать себя в движке Tabular, и наоборот.
DAX для пользователей Power BI
Если вы пропустили предыдущие разделы и сразу оказались тут, что ж, приветствуем! Язык DAX является родным для Power BI. И если у вас нет опыта работы с Excel, SQL или MDX, Power BI станет для вас первой средой, в которой вы сможете изучать DAX. В отсутствие навыков построения моделей данных при помощи других инструментов вам будет приятно узнать, что Power BI является мощнейшим средством анализа и моделирования, а DAX во всем ему помогает.
Возможно, вы не так давно начали работать с Power BI, а сейчас хотите сделать очередной качественный шаг вперед. Если это так, приготовьтесь к увлекательному путешествию в мир DAX.
Вот вам наш совет: не ожидайте, что уже через пару дней вы сможете писать сложный код на DAX. Этот язык потребует от вас полной концентрации и внимания, а на его освоение может уйти немало времени, включая практическую работу. По опыту можем сказать, что после проведения первых простых вычислений на DAX вы будете просто восхищены. Но восхищение пропадет, когда вы дойдете до изучения контекстов вычислений и функции CALCULATE – наиболее сложных составляющих языка. В этот момент вам все покажется очень сложным. Но не отчаивайтесь! Большинство разработчиков DAX проходили через это. На этой стадии вы уже так много всего изучите, что бросать все будет просто жалко. Читайте и практикуйтесь снова и снова, и вы увидите, что озарение придет раньше, чем вы ожидаете. И тогда вы очень быстро завершите чтение этой книги – уже в статусе гуру по DAX.
Контексты вычислений – это сердце языка DAX. Освоение их может занять много времени. Мы не знаем никого, кому удалось бы узнать все о DAX за пару дней. Но, как и с любым сложным делом, со временем вы научитесь получать наслаждение от мелочей. А когда решите, что знаете уже все, перечитайте книгу заново. Уверяем, вы найдете для себя массу полезных нюансов, которые при первом прочтении казались не такими важными, но с приобретением опыта смогут заиграть новыми красками.
Насладитесь остатком этой книги!
… Материал не полный и предоставлен для ознакомления …