...

воскресенье, 13 июля 2014 г.

[Из песочницы] Power Pivot: Оконные функции под соусом DAX

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

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


А вспомнить пришлось, когда появилась необходимость идентификации определённых явлений в данных на новом месте работы, где директор IT департамента наотрез отказался установить SSMS даже несмотря на мои аргументы в привычности данного инструментария и потребности его в в моей работе (ну… это только начало войны, в итоге еще и R установим).



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


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


В исходных данных 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 гораздо мощнее чем показано в данном топике, но объять необъятное за раз – точно не получится.


Надеюсь было интересно.


This entry passed through the Full-Text RSS service — if this is your content and you're reading it on someone else's site, please read the FAQ at http://ift.tt/jcXqJW.


Комментариев нет:

Отправить комментарий