Работая в сфере аналитики и мониторя различные инструменты BI рано или поздно наталкиваешься на обзор или упоминание надстройки Power Pivot Excel. В моем случае знакомство с ним произошло на конференции Microsoft Data Day.
Особых впечатлений после презентации инструмент не оставил: Да, бесплатен (в рамках лицензии Office), да — есть некий ETL функционал в части получения данных с разрозненных источников (БД,csv,xls, и т.д.), Join-ов этих источников и скармливания в оперативку записей на порядки выше 1 млн.строк в Excel. Короче, посмотрел и забыл.
А вспомнить пришлось, когда появилась необходимость идентификации определённых явлений в данных на новом месте работы, где директор IT департамента наотрез отказался установить SSMS даже несмотря на мои аргументы в привычности данного инструментария и потребности его в в моей работе (ну… это только начало войны, в итоге еще и R установим).
На написание данной статьи сподвигло то что в рунете по этому инструменту не много какой либо детальной информации о конкретных приемах работы, все больше о звездах, поэтому решил, изучая этот инструмент, написать данный обзор.
Собственно, постановка задачи (на обезличенном примере) следующая:
В исходных данных csv файла:
Есть торговые точки, детализированные до строк накладных, при этом допускается для точек с одинаковым наименованием иметь разные адреса только в том случае если они расположены в разных городах, но в исходном массиве данных есть точки, у которых попадаются разные адреса в одном и том же городе при том, что названия точек одинаковые (имя торговой точки уникально, т.е. это единица сети или отдельно стоящая точка). Как частный случай в агрегированном виде:
Поиску и очистке данных штатными средствами office мешают следующие обстоятельства:
• Детализация данных до строк накладной
• Количество записей в несколько миллионов строк
• Отсутствие sql инструментария (К примеру: Access — не в комплекте)
Конечно можно залить любую бесплатную СУБД (хоть десктоп версию, хоть серверную) но для этого во-первых нужны админские права, во-вторых статья была бы уже не про Power Pivot.
Задача: для каждой атомарной записи требуется дополнительное вычисляемое поле, которое посчитает для каждого наименования торговой точки уникальное количество адресов в рамках того же города. Данное поле требуется для быстрого нахождения всех имен торговых точек в городе, где адресов больше 1.
Думаю, удобнее всего решать и рассказывать итерационно, с допущением что у нас знания по DAX в зачаточном уровне ( а у меня – так и есть).
Поэтому предлагаю пока оторваться от задачи и рассмотреть некоторые базовые аспекты.
Шаг 1. Чем отличается вычисляемый столбец от вычисляемой меры?
Вот пример вычисляемого столбца для выделения НДС из поля отгрузки с НДС используя встроенные формулы DAX:
=ROUND([Отгрузка с НДС]*POWER(1,18;-1)*0,18;2)
Как видно из примера вычисляемый столбец (Назовем его НДС) работает с каждой атомарной записью по горизонтали.
Теперь добавим вычисляемое поле для цены за штуку без НДС:
=ROUND([Отгрузка с НДС]*POWER(1,18;-1)/[Отгрузка шт];2)
Теперь для сравнения добавим в меру расчет средней цены за штуку:
Средняя цена за штуку без НДС: =ROUND(AVERAGE([Поле_Цена за штуку без НДС]);2)
Как видно из формулы, мера работает со столбцом исходных данных по вертикали, поэтому она всегда должна содержать в себе какую то работающую с множеством функцию (Сумму, среднюю, дисперсию и т.д.)
При возврате в сводную таблицу Excel это выглядит так:
Обратите внимание, если вычисляемое поле НДС на каждом уровне данных (зеленая обводка на уровне торговой точки, города или итого по таблице) показывает сумму, что в принципе – корректно, то сумма цен вычисляемого поля «Цена за штуку без НДС» (красная обводка) вызывает вопросы.
А вот вычисляемая мера «Средняя цена за штуку без НДС» вполне имеет право на жизнь в рамках данного аналитического куба.
Отсюда делаем вывод, что вычисляемое поле «Цена за штуку без НДС» является вспомогательным инструментом для расчета меры «Средняя цена за штуку без НДС» и дабы не смущать пользователя этим полем мы скроем его из списка клиентских средств, оставив меру средней цены.
Еще одно отличие меры от столбца – она позволяет добавить визуализацию:
К примеру, построим KPI степени разброса цен с целевой границей 35% путем деления корня из дисперсии на среднюю арифметическую.
К_вар:=STDEV.P([Поле_Цена за штуку без НДС])/AVERAGE([Поле_Цена за штуку без НДС])
В итоге видим такую таблицу в Excel (кстати расчетное вспомогательное поле цен уже не в списке доступных полей справа):
Двойной клик на 80%-м коэффициенте показывает, что цены действительно колбасит вокруг средней:
Cильнее чем при коэффициенте 15%:
Итак, на данном шаге мы рассмотрели основные отличия мер от полей в рамках 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'))
Основное внимание обратим к знаменателю: Я уже упоминал выше что основное отличие вычисляемого поля от меры заключается в том что в поле формулы считают по горизонтали ( в рамках одной записи) а меры – по вертикали ( в рамках одного атрибута). Здесь мы смогли скрестить свойства поля и свойство меры через метод 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)
Как видите, цена несколько высоковата при средней арифметической 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'[Город]))
Обратите внимание на разницу в отображении данных в таблице, я специально бросил адреса в область мер что бы посчитать их количество и сравнить с новым полем которое вывел в заголовок строк после имени торговой точки.
Отчетлива видна разница: если обычный расчет количества адресов идет для каждой точки в городе и потом только выводит промежуточный итог для агрегата «Город» то использование оконных функций позволяет присвоить каждой атомарной записи значение любого агрегата, либо использовать его в каких-то промежуточных расчетах вычисляемого поля ( как было показано выше).
Возвращаемся к исходной задаче
Итак, напомню, исходная постановка задачи: для каждой атомарной записи требуется дополнительное вычисляемое поле, которое посчитает для каждого наименования торговой точки уникальное количество адресов в рамках того же города. Не забываем, что датасэт у нас детализирован до строк накладной, поэтому перед подсчетом адресов внутри окна их необходимо сгруппировать.
Запрос на 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'[Имя ТТ]))
В итоге у нас появилась возможность отобрать подозрительные записи, где на одну и ту же точку в одном городе приходится более 1 адреса.
Конечно в процессе изучения (пробежавшись взглядом на другие формулы) становится понятно что DAX в PowerPivot гораздо мощнее чем показано в данном топике, но объять необъятное за раз – точно не получится.
Надеюсь было интересно.
Автор: Ananiev_Genrih
