Power Pivot: Оконные функции под соусом DAX

    [в связи со спорным переносом 1 части поста на geektimes (при том что 2-я часть осталась на хабре) возвращаю 1-ю часть на место]

    Работая в сфере аналитики и мониторя различные инструменты BI рано или поздно наталкиваешься на обзор или упоминание надстройки Power Pivot Excel. В моем случае знакомство с ним произошло на конференции Microsoft Data Day.

    Особых впечатлений после презентации инструмент не оставил: Да, бесплатен (в рамках лицензии Office), да — есть некий ETL функционал в части получения данных с разрозненных источников (БД,csv,xls, и т.д.), Join-ов этих источников и скармливания в оперативку записей на порядки выше 1 млн.строк в Excel. Короче, посмотрел и забыл.

    А вспомнить пришлось, когда появилась необходимость идентификации определённых явлений в данных

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

    Собственно, постановка задачи (на обезличенном примере) следующая:

    В исходных данных csv файла:

    image

    Есть торговые точки, детализированные до строк накладных, при этом допускается для точек с одинаковым наименованием иметь разные адреса только в том случае если они расположены в разных городах, но в исходном массиве данных есть точки, у которых попадаются разные адреса в одном и том же городе при том, что названия точек одинаковые (имя торговой точки уникально, т.е. это единица сети или отдельно стоящая точка). Как частный случай в агрегированном виде:

    image

    Поиску и очистке данных штатными средствами office мешают следующие обстоятельства:

    • Детализация данных до строк накладной
    • Количество записей в несколько миллионов строк
    • Отсутствие sql инструментария (К примеру: Access — не в комплекте)

    Конечно можно залить любую бесплатную СУБД (хоть десктоп версию, хоть серверную) но для этого во-первых нужны админские права, во-вторых статья была бы уже не про Power Pivot.

    Задача: для каждой атомарной записи требуется дополнительное вычисляемое поле, которое посчитает для каждого наименования торговой точки уникальное количество адресов в рамках того же города. Данное поле требуется для быстрого нахождения всех имен торговых точек в городе, где адресов больше 1.

    Думаю, удобнее всего решать и рассказывать итерационно, с допущением что у нас знания по DAX в зачаточном уровне.
    Поэтому предлагаю пока оторваться от задачи и рассмотреть некоторые базовые аспекты.

    Шаг 1. Чем отличается вычисляемый столбец от вычисляемой меры?
    Вот пример вычисляемого столбца для выделения НДС из поля отгрузки с НДС используя встроенные формулы DAX:

    =ROUND([Отгрузка с НДС]*POWER(1,18;-1)*0,18;2)

    image

    Как видно из примера вычисляемый столбец (Назовем его НДС) работает с каждой атомарной записью по горизонтали.
    Теперь добавим вычисляемое поле для цены за штуку без НДС:

    =ROUND([Отгрузка с НДС]*POWER(1,18;-1)/[Отгрузка шт];2)

    image

    Теперь для сравнения добавим в меру расчет средней цены за штуку:

    Средняя цена за штуку без НДС: =ROUND(AVERAGE([Поле_Цена за штуку без НДС]);2)

    image

    Как видно из формулы, мера работает со столбцом исходных данных по вертикали, поэтому она всегда должна содержать в себе какую то работающую с множеством функцию (Сумму, среднюю, дисперсию и т.д.)

    При возврате в сводную таблицу Excel это выглядит так:

    image

    Обратите внимание, если вычисляемое поле НДС на каждом уровне данных (зеленая обводка на уровне торговой точки, города или итого по таблице) показывает сумму, что в принципе – корректно, то сумма цен вычисляемого поля «Цена за штуку без НДС» (красная обводка) вызывает вопросы.
    А вот вычисляемая мера «Средняя цена за штуку без НДС» вполне имеет право на жизнь в рамках данного аналитического куба.

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

    image

    Еще одно отличие меры от столбца – она позволяет добавить визуализацию:

    К примеру, построим KPI степени разброса цен с целевой границей 35% путем деления корня из дисперсии на среднюю арифметическую.

    К_вар:=STDEV.P([Поле_Цена за штуку без НДС])/AVERAGE([Поле_Цена за штуку без НДС])

    image

    В итоге видим такую таблицу в Excel (кстати расчетное вспомогательное поле цен уже не в списке доступных полей справа):

    image

    Двойной клик на 80%-м коэффициенте показывает, что цены действительно колбасит вокруг средней:

    image

    Cильнее чем при коэффициенте 15%:

    image

    Итак, на данном шаге мы рассмотрели основные отличия мер от полей в рамках PowerPivot.

    Шаг 2. Усложняем: Посчитаем долю каждой записи в общих продажах.
    Вот первый пример сравнения подходов оконных функций MS SQL Server и DAX:

    Понятно, что в рамках сводных таблиц это делается буквально в 2 клика мышкой не касаясь клавиатуры, но для понимания попробуем это непосредственно в PowerPivot с применением формул.

    На sql я бы это написал так (за огрехи не пинать, ибо Word синтаксис SQL Server не проверяет):

    Begin Select 't1.Имя ТТ', 't1.Город', 't1.Адрес', 't1.Продукт', 't1.№ ТТН', 't1.Дата ТТН', 't1.Отгрузка, шт', 't1.Отгрузка с НДС', 't1.Отгрузка, шт'/sum('t1.Отгрузка, шт') over () as share from Table as t1 order by 't1.Отгрузка, шт'/sum('t1.Отгрузка, шт') desc
    


    Здесь, как можно заметить окно открывается через все записи датасэта, попробуем аналогичную вещь в PowerPivot:

    =[Отгрузка шт]/CALCULATE(SUM([Отгрузка шт]);ALL('Таблица1'))

    image

    Основное внимание обратим к знаменателю: Я уже упоминал выше что основное отличие вычисляемого поля от меры заключается в том что в поле формулы считают по горизонтали ( в рамках одной записи) а меры – по вертикали ( в рамках одного атрибута). Здесь мы смогли скрестить свойства поля и свойство меры через метод CALCULATE. И если ширину окна в SQL мы отрегулировали через Over() то здесь мы сделали это через All().

    Попробуем теперь, обладая данным навыком, сделать с нашими данными что –нибудь полезное, например, вспомнив что показатель разброса цен вокруг средних варьировался в широком диапазоне, попробуем выделить статистические выбросы цен через правило 3-х сигм.

    Оконные функции на sql будут смотреться так:

    Select 't1.Имя ТТ', 't1.Город', 't1.Адрес', 't1.Продукт', 't1.№ ТТН', 't1.Дата ТТН', 't1.Отгрузка, шт', 't1.Отгрузка с НДС', 't1.Цена за шт без НДС',   CASE WHEN ABS('t1.Цена за шт без НДС' - AVG('t1.Цена за шт без НДС') OVER() ) > 3 * STDEV('t1.Цена за шт без НДС') OVER() THEN 1 ELSE 0 END as Outlier from Table as t1  Go
    


    А вот то же самое в DAX:

    =if(ABS([Поле_Цена за штуку без НДС]-CALCULATE(AVERAGE([Поле_Цена за штуку без НДС]);ALL('Таблица1')))>(3*CALCULATE(STDEV.P([Поле_Цена за штуку без НДС]);all('Таблица1')));1;0)

    image

    Как видите, цена несколько высоковата при средней арифметической 40,03 руб.

    Шаг 3. Сужаем окна.
    Попробуем теперь посчитать в вычисляемом поле каждой записи общее количество записей в рамках того города, к которому принадлежит и данная запись.
    На MS sql Server оконные функции будут выглядеть так:

    Select 't1.Имя ТТ', 't1.Город', 't1.Адрес', 't1.Продукт', 't1.№ ТТН', 't1.Дата ТТН', 't1.Отгрузка, шт', 't1.Отгрузка с НДС', 't1.Цена за шт без НДС',   count('t1.*) OVER( partition by 't1.Город' )   as cnt from Table as t1  Go
    


    В DAX:
    =CALCULATE(COUNTROWS('Таблица1');ALLEXCEPT('Таблица1';'Таблица1'[Город]))

    image

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

    image

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

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

    Запрос на SQL Server:

    With a1 as (Select 't1.Имя ТТ', 't1.Город', 't1.Адрес', 't1.Продукт', 't1.№ ТТН', 't1.Дата ТТН', 't1.Отгрузка, шт', 't1.Отгрузка с НДС', 't1.Цена за шт без НДС',   count(Distinct  't1.Адрес') OVER( partition by 't1.Город', 't1.Имя ТТ' )   as adrcnt  from Table as t1)  
    Select * from a1 where adrcnt>1
    


    Теперь нам ничего не мешает это сделать и в DAX:

    =CALCULATE(DISTINCTCOUNT('Таблица1'[Адрес]);ALLEXCEPT('Таблица1';'Таблица1'[Город];'Таблица1'[Имя ТТ]))

    image

    В итоге у нас появилась возможность отобрать подозрительные записи, где на одну и ту же точку в одном городе приходится более 1 адреса.
    image

    Конечно в процессе изучения (пробежавшись взглядом на другие формулы) становится понятно что DAX в PowerPivot гораздо мощнее чем показано в данном топике, но объять необъятное за раз – точно не получится.

    Надеюсь было интересно.
    Продолжение статьи здесь
    Метки:
    Поделиться публикацией
    Реклама помогает поддерживать и развивать наши сервисы

    Подробнее
    Реклама
    Комментарии 0

    Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.