ABC-анализ в SQL Server Analysis Services. Практическое руководство.

ABC-анализ – достаточно распространённый метод, который позволяет определить степень важности ресурса. Чаще всего он применяется к продаваемым товарам или предлагаемым услугам. Подробнее о самой концепции можно прочитать статью на Wikipedia. Нас же интересует практическая реализация данной методологии на базе SQL Server Analysis Services (далее SSAS). Итак, приступим. Практическое руководство будет показано на примере БД розничной торговой сети.

Во-первых нам нужно определиться что же мы будем анализировать. Анализировать мы будем товары, которые продаем. Соответственно у нас уже есть некий базовый SSAS-проект в котором присутствует измерение Номенклатура и показатели розничных продаж: Количество, Сумма, Сумма со скидкой, Маржа и % маржи. Теперь, выбрав нужный показатель, мы должны категорировать Номенклатуру по критериям 80% — A, 15% — B, 5% — C. Как же это реализовать в SSAS и при этом добиться приемлемой производительности? После нескольких вариантов реализации мы пришли к наиболее оптимальному решению (по нашему мнению), о котором сейчас и расскажем.

В интернете можно найти следующую реализацию ABC-анализа. Создаем именованный динамический набор, который содержит список элементов Номенклатуры с 80% — A и 15% — B. Используется функция TopPercent. Выглядит это примерное так:

CREATE HIDDEN DYNAMIC SET CURRENTCUBE.[A,80%] AS
  TopPercent([Номенклатура].[Номенклатура].[Номенклатура], 80, [Measures].[Сумма]);
CREATE HIDDEN DYNAMIC SET CURRENTCUBE.[B,15%] AS
  TopPercent([Номенклатура].[Номенклатура].[Номенклатура], 95, [Measures].[Сумма]);

Соответственно, вычисляемая мера ABC может выглядеть следующим образом:

CREATE MEMBER CURRENTCUBE.[Measures].[ABC] AS
CASE

                WHEN [Measures].[Сумма] >= (Tail([A,80%]).ITEM(0), [Measures].[Сумма]) THEN "A"
                WHEN [Measures].[Сумма] >= (Tail([B,15%]).ITEM(0), [Measures].[Сумма]) THEN "B"
                ELSE "C"
END;

Тут всё просто. Динамический набор содержит в себе отсортированные в обратном порядке по Сумме элементы Номенклатуры для определенной категории. Последний элемента набора A,80% содержит элемент Номенклатуры, которая бы попала в категорию A, поэтому мы можем взять значение Суммы этого элемента как граничное значение. Метод прост, понятен, достаточно быстро работает (с некоторыми оговорками, см. ниже) и широко применяется. Из недостатков следует выделить прежде всего использование динамических наборов, которые сильно тормозят UI, например фильтры в Excel при работе с кубом, т.к. наборы постоянно пересчитываются даже когда это не требуется и тот факт, что этот метод работает только на нижнем уровне иерархии Номенклатуры. Попытаемся исправить оба недостатка.

Начнем с уровней иерархии. Чтобы метод заработал на любом уровне нам нужно создать по два набора (A, B) для каждого уровня, а с учетом “притормаживания” этот метод не наш выбор. Кроме того хотелось бы динамически подставлять показатель, для которого рассчитывается категория ABC. С этого и начнем. Создадим новое измерение, которое назовем Показатели. Но для этого нам потребуется создать именованный запрос в нашем DSV.

abc1

И само измерение:

Measures Dimension

Теперь нужно добавить вычисляемую меру Текущий показатель. По-умолчанию она будет представлять собой Сумму, а при выборе в фильтре элемента измерения Показатели динамически подменяться:

Current Measure

Теперь создадим два именованных набора C,5% и B,15%. Решено было использовать функцию BottomPercent вместо TopPercent, т.к. более точно определяется попадание в категории C и B.

ABC dynamic sets

И, собственно, сама вычисляемая мера ABC:

ABC measure

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

В общем и целом практически всё готово. Осталось только “победить” существенные задержки в UI из-за использования динамических наборов. Наше решение хоть и не выглядит изящным, но зато работает в полной мере. Потребуется библиотека ASSP. На момент написания статьи реализуемый в библиотеке функционал версии 1.3.6 немного не соответствовал нашим потребностям, поэтому за основу была взята версия из репозитория (предварительная 1.3.7 с поддержкой GetCurrentCommand) и немного доработана. Нами был изменен файл StringFilters.cs и добавлена функция RegexIsMatch, которая в отличие от стандартной IsLike принимает в качестве шаблона паттерн регулярного выражения. Компилируем сборку, подключаем к инстансу SSAS и редактируем именованные наборы следующим образом.

ABC dynamic sets with optimization

Суть всего вышенаписанного заключается в том, что при использовании фильтров в Excel генерируется запрос, который возвращает список элементов измерения. В этом случае нет никакого смысла вычислять именованные наборы, которые должны рассчитываться только, если в текущем запросе встречается название меры ABC и измерение Номенклатура.

Полный текст скрипта куба, реализующего ABC-анализ можно скачать здесь.

1  

Подробный пример построения модели PowerPivot

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

0  

Квазигеофильтрация в PowerPivot

Бизнес-сценарий использования на примере радиуса поиска

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

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

Интерфейсно в PowerView это выглядит следующим образом:

GeoFiltering1

 

GeoFiltering2

Пользователь выбирает фильтром Мой город (например, Киров), в каталоге марок автомобилей выбирает конкретную марку (например, Audi). В области фильтров движком задается предельный радиус поиска объявлений о продаже автомобилей, общее количество объявлений отображается на карте размером кружка. Внизу на графике показана средняя цена на данную  марку в разрезе по всем городам. Такой подход к фильтрации заменяет привычный множественный фильтр и часто используется на зарубежных автосайтах, но почему-то игнорируется отечественными. Для потребителя же важно увидеть объявления из нескольких регионов, находящихся в разных федеральных округах, но в пределах досягаемости. Не каждый из Кирова поедет за интересным предложением в Краснодар, но в Казань (ПФО), Сыктывкар (СЗФО) или Кострому (ЦФО) ездят часто. По рисунку видно, что в радиусе 750 км наибольшее количество предложений в Кирове, Перми, Нижнем Новгороде и Казани. При этом наиболее низкая средняя цена по рынку из приведенного списка городов в Кирове. Такой подход существенно упрощает задачу по экспресс-мониторингу рынка.

Техническая реализация в PowerPivot и PowerQuery

Основой для расчета является матрица расстояний. Стоит обратить внимание, что геометрическое вычисление расстояний между точками конкретно в этом случае применять нельзя, потому что нас интересует расстояние по автомобильным дорогам общего пользования с твердым покрытием, паром исключаем. В связи с этим, матрицу расстояний для экономии времени я заполнил с логистического сайта вручную, поскольку в данном случае это оказалось быстрее, чем разбираться с API. Такие матрицы расстояний вы могли видеть в атласах автомобильных дорог. Формат таблицы очень удобен именно для ручного заполнения.

GeoFiltering3

Дальше необходимо отменить свертывание таблицы в терминах PowerQuery (операция unpivot) и заполнить пары расстояний (Город1 – Город 2; Город 2 – Город 1). Эти операции удобно выполнить в PowerQuery:

GeoFiltering4

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

GeoFiltering5

Как видно из схемы модели PowerPivot, поле Мой город таблицы Расстояния выносится в фильтр отчета, а связь с таблицей фактов Предложения осуществляется через промежуточное измерение Города для реализации связи многие-ко-многим, поскольку поле Город поиска таблицы Расстояния дублируется.

GeoFiltering6

Важно отметить, что для правильного вычисления с учетом устанавливаемых фильтров вычисляемая мера Кол-во предложений расположена в таблице Расстояния, именно она используется для отображения в отчете.

Как видно из примера, интересное интерфейсное решение прикладной задачи по геофильтрации данных имеет простое решение на PowerPivot и PowerQuery.

1  

О пользе временной шкалы в Excel 2013

Практическое применение в отчетах типа Like-to-Like

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

С интерфейсной точки зрения в Excel 2013 это реализуется великолепно за счет появления временной шкалы (Time Line). Она однозначно более наглядна, чем множественный выбор в фильтрах, поскольку выделить несколько месяцев можно простым перетаскиванием границ диапазона.

TimeLine1

TimeLine2

 

По рисунку Дата – текущий период, ДатаСравнения – предыдущий период, Средняя, Суммарная и Максимальная продолжительности, а также Число отказов – это выраженное в виде KPI сравнение усредненных показателей за периоды. Сравнение выполняется в разрезе по филиалам. Все очень наглядно и понятно: «потаскав» бегунки можно быстро понять положение дел в целом – стало лучше или хуже в текущем периоде по отношению к произвольному предыдущему.

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

TimeLine3

 

Здесь мы видим те же два периода (Дата и ДатаСравнение), а на график уже выведены Удельное число отказов в текущем, сравниваемом периодах и нормативное значение. Визуально график читается великолепно: все, что «внутри» серого кольца – это хорошо, все что за границами серого кольца – плохо. По осям лепестков опять же филиалы.

Техническая реализация в PowerPivot

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

TimeLine4

 

Основная таблица фактов связана со всеми справочниками и измерением Дата. Вспомогательная таблица фактов (дубль основной) связана с теми же справочниками (что обеспечивает сквозную фильтрацию в разных периодах при установке фильтров по справочникам), но со вспомогательным измерением ДатаСравнение (что обеспечивает независимую фильтрацию в разных периодах по временным измерениям). Для удобства использования все меры, отражающие сравнение показателей, выполнены в основной таблице фактов, вспомогательная таблица фактов скрыта из пользовательского интерфейса.

TimeLine5

 

0  

Самые свежие пакеты обновления для Microsoft Office

Недавно обнаружил страничку в интернете. На ней выкладываются и постоянно актуализируются самые свежие пакеты обновлений для Microsoft Office. Пользуйтесь.

0  

SSAS: «Что это за показатель?»

Сегодня поговорим об SQL Server Analysis Services. И поговорим о достаточно больших проектах, с большим количеством кубов и мер (показателей). Довольно часто у пользователей возникает вопрос: «Что это за показатель и как он считается?» Конечно же решение напрашивается само собой — нужно написать документацию. Но как сделать так, чтобы она была интегрирована с клиентским средством, например с Excel? Ответ прост — в SSAS есть стандартное средство для реализации этого сценария. Нам понадобится любой Web-сервер, в частности мы будем использовать SharePoint с шаблоном корпоративного Wiki-сайта.

Открываем в кубе вкладку Действия и создаем новое действие с типом URL и целевым типом ячейка. В поле действие указываем код:

"http://intranet/sites/bi-wiki/Pages/" + [Measures].CurrentMember.Properties("CUBE_NAME") + " - " + [Measures].CurrentMember.Name + ".aspx"

В заголовке пишем:

"Информация о " + [Measures].CurrentMember.Name + "..."
MDX заголовок = Да

 
MeasureInfo

Сохраняем и публикуем на сервере. Собственно вот и все! Теперь мы должны создать статьи на wiki-портале, где имя страницы должны быть вида <Имя куба> — <Имя меры>.

MeasureInfo3

Заходим в Excel и видим новый пункт меню над каждым значением показателя.

MeasureInfo2

Если нажать по этому пункту меню, то откроется Wiki-страничка с описанием показателя. Если страница не будет найдена, то появится сообщение об ошибке.

MeasureInfo4

0  

Ограничения на размер файла Excel в Power BI for Office 365

Когда мы тестировали Power BI for Office 365 Preview, то в какой-то момент остро встала проблема с размером файла Excel. Было объявлено, что ограничение размера файла Excel в Power BI составляет 250 МБ. Но однажды ситуация почти заставила в этом усомниться. Лучше поясню на примере. Допустим я подготовил какой-то файл Excel с моделью PowerPivot и какими-то отчетами, а также это сделал мой коллега, но у него совсем другие данные, совсем другая модель. В результате имеем два файла, мой под номером 1, коллеги под номером 2:

image1

Как видно, оба файла далеки до размера в 250 МБ. Итак, сначала я добавляю свой файл в Power BI:

image2

Все замечательно. Теперь я добавлю файл коллеги:

image3

Очевидно, что в этот раз все не замечательно. Но я последую совету и попробую включить через меню:

image4

В результате:

image5

На самом деле все очень просто. Но обо всем по порядку:
Вообще, максимальный размер файла, который можно загрузить в SharePoint Online (напомню, что Power BI это приложение в SharePoint Online) составляет 2 Гб. Максимальный размер файла, который можно открыть с помощью служб Excel Online составляет 10 Мб. По умолчанию такое же ограничение установлено в OnPremise версии SP, но там это значение можно изменить, а в SharePoint Online нет. Понятно, что для задач бизнес-аналитики, ограничение размера файла в 10 Мб очень серьезная проблема – большинство моделей все-таки больше. Поэтому максимальный размер файла в Power BI увеличили до 250 Мб. Но есть важное уточнение – 250 Мб это ограничение на размер модели данных, а не всего файла. Дело в том, что при активации книги из файла Excel извлекается модель и размер книги уменьшается на ее размер. Таким образом, если сама модель не превышает 250 Мб и то, что осталось после извлечения модели удовлетворяет ограничениям Excel Online (10 Мб), то файл будет успешно активирован.

Поэтому пара-тройка советов:

  1. Если собираетесь использовать книгу в Power BI for O365, то старайтесь данные грузить сразу в модель, а не на лист.
  2. Если же данные все-таки приходится держать на листах этой же книги, то легко проверить будет ли получившийся файл активирован. Для этого просто переименуйте расширение файла в *.zip и откройте как архив. Модель там будет видна как файл:image6
    Если размер модели будет меньше 250 Мб и разница между размером всего файла и размером модели будет меньше 10 Мб, то файл будет успешно активирован.
  3. Можно воспользоваться инструментом Workbook Size Optimizer. Данный инструмент анализирует вашу книгу и дает советы по уменьшению размера книги. Вот тут и тут можно посмотреть подробнее на работу этого инструмента.
0  

Анализ потока почты с помощью средств Power BI

Сегодня я расскажу о том, как вы сможете проанализировать свою почтовую переписку средствами Power BI, правда если в качестве почтовой системы у Вас используется Exchange или Exchange Online. Мы воспользуемся заготовкой, которую мы сделали здесь. Напомню, у нас теперь есть книга Excel с моделью Power Pivot, в котором есть два измерения: Дата и Время. Возьмем эту же книгу за основу и приступим.

image1

Так как мой компьютер находится в домене, то Power Query все определил автоматически и без всяких дополнительных запросов подключился к моему почтовому ящику. В результате в панели навигации я увидел:

image2

Дважды кликаем по ветке Mail. Откроется окно редактирования запроса:

image3

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

image4

Как видно на картинке папка пока видится всего одна, это потому что Power Query подгрузил только часть данных, нажмем на надпись «Загрузить еще» и выберем необходимые папки:

image5

Отлично, теперь в запросе только входящие письма. Теперь удалим те столбцы (с помощью кнопки Удалить столбцы на ленте, либо через контекстное меню), которые нам не потребуются далее. Я удалил все, кроме Sender, DateTimeReceived, Importance, HasAttachments:

image6

Теперь нам нужно немного доработать наш запрос. Как видно в столбце Sender вместо одного значения содержится одномерный массив. Я хочу видеть имя отправителя, поэтому сделаю небольшое преобразование:

image7

Теперь я разобью столбец DateTimeReceived на два: Дата и Время. Для этого я создам пользовательский столбец:

image9

И в окне мастера напишу формулу:

image10

А затем переименую столбец в Дата. Аналогично сделаю для второго столбца, формула там будет немного другая:
#time(Time.Hour([DateTimeReceived]), Time.Minute([DateTimeReceived]), 0)
Почему именно такая формула – в моем измерении времени гранулярность минута, т.е. секунды там не учитываются, поэтому мы здесь их тоже обрежем.
Затем удалю столбец DateTimeReceived. Именую набор как Входящая почта, ставлю галку Загрузить в модель данных:

image11

Нажимаю Применить и закрыть. Загрузка пошла:

image12

Далее я сделал точно такой же запрос, но уже на исходящую почту (отфильтровав по папке Отправленные). За исключением одного – теперь поле Sender мне не интересно, я и так знаю, что отправителем являюсь я, поэтому этот столбец я удалил, а оставил DisplayTo, а также построил столбцы Дата и Время на базе столбца DateTimeSent. Назвал набор данных Исходящая почта и загрузил в модель.
Я хочу сделать еще одно дополнительное измерение «Вложения» с двумя параметрами «Есть вложение» и «Без вложения». Создам пустой запрос, в редакторе напишу следующий запрос:
= Table.ExpandRecordColumn(Table.FromList({ [ID=false, Value="Без вложения"], [ID=true, Value="Есть вложение"] }, Splitter.SplitByNothing(), { «Data» }), «Data», {«ID», «Value»}, {«ID», «Value»})
Назову Вложения и загружу в модель.
Переходим в модель – на вкладке Power Pivot нажимаем Управление. Открываем вкладку Входящая почта и переименовываем столбцы: Sender.Name -> Отправитель, Importance -> Важность. Указываем, что поле Дата имеет тип данных Дата. Делаем столбцы HasAttachments, Дата и Время невидимыми в клиентских средствах.

image13

Теперь нам нужно сделать показатель (еще называют мерой), который бы показывал количество писем. Я назову ее просто «Входящие». В области вычисления в любой ячейке нужно написать формулу:
Входящие:=COUNTROWS(‘Входящая почта’)
Выглядит это так:

image14

Повторяем для Исходящей почты.
Затем идем на вкладку Вложения и переименовываем столбец: Value -> Вложение. Указываем, что поле ID имеет тип данных True/False и делаем его невидимым в клиентских средствах.

image15

Нам осталось совсем немного. Теперь мы имеем говоря языком разработчиков кубов таблицы фактов (Входящая почта, Исходящая почта) и таблицы измерений (Дата, Время, Вложения). Теперь нам нужно их связать между собой. Сделать это можно разными способами, я покажу как это сделать «мышкой». Переключаемся в режим представления диаграммы:

image16

И начинаем создавать связи. В контейнере Входящая почта цепляем мышкой поле Дата и тащим на аналогичное поле в контейнере Дата. В результате появится связь:

image17

Далее из контейнера Входящая почта цепляем поле Время и тащим на поле TimeID в контейнере Время (тут следует отметить, что у меня сделано не очень красиво с точки зрения проектирования – обычно мы поля в измерениях и фактах по которым идет связь именуем одинаково, т.е. Дата-Дата, Время-Время и т.д., но тут чтобы не растягивать я оставлю все как есть, просто помните об этом). Затем из контейнера Входящая почта цепляем поле HasAttachments и тащим на поле ID в контейнере Входящие. Повторяем тоже самое для Исходящей почты:

image18

Ну теперь можно и отчеты построить. Возвращаемся в Excel и на вкладке Вставка выбираем Power View:

image19

В результате видим пустой «холст» и элементы как при работе со сводными таблицами:

image20

Я думаю на этот пост хватит технических деталей. Посмотрим отчеты, а как их делать расскажу в других статьях.
Итак, мой первый мини «дашборд»:

image21

Видно, что в 2010 году интенсивность переписки росла от начала года к концу, при этом доля писем с вложениями была достаточно велика.

image22

Сначала меня этот «выстрел» удивил самого, но потом вспомнил, что именно тогда мы начали делать рассылки из CRM и это делал я. Потом передал эту функцию другому. В 2011 году стал меньше посылать письма с вложениями.

image23

Как ни странно, но в 2012 году пик активности выпал на апрель, не припомню каких-то факторов это объясняющих.

image24

В 2013 году в апреле опять видна большая активность, но сентябрь опередил, с каждым годом все меньше и меньше вложений в письмах, может SkyDrive помогает?

image25

Специально снял все фильтры – НИКОГДА я не отправлял письма в 4 и 5 утра, во все остальное время бывало.

image26

В декабре 2013 активен был с утра и уже ближе к концу рабочего дня.

image27

В 2013 году больше всех писем я получил от Саши Пинкуса, он явно был активен. Я ему писал раза в четыре меньше. И внимание! На 11 месте Facebook! И это я еще больше половины писем удалял.

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

0  

Добавление измерения времени в модель Power Pivot

Многие из вас уже знают, что есть специальная надстройка к Excel, которая называется Power Pivot. С ее помощью можно связывать книгу Excel с большими объемами данных, а затем использовать эту базу данных как источник для отчетов и т.д.  Появился Power Pivot еще в SQL Server 2008 R2 для Excel 2010, в Excel 2013 надстройка уже встроена и устанавливать ее не нужно – важно знать только лишь, что Excel должен входить в состав пакета Office Pro Plus 2013 или Office 365.
Сегодня мы попрактикуемся в работе с Power Pivot и создадим базовые элементы модели – измерения дат и времени. В качестве источников данных мы будем использовать готовые запросы Power Query, которые создал Дима. Более того, он не просто их создал, а также дал возможность мне их использовать. Итак, открываем Excel и поехали:
Идем на вкладку Power Query и авторизуемся с учетной записью на нашем тенанте O365

image1

Далее жмем «Сетевой поиск», указываем, что искать нам надо в нашей организации и вводим фразу для поиска. Результат виден ниже на картинке.

image2

Если сейчас дважды кликнуть на строку DimTime в результатах поиска, то данные будут добавлены на лист Excel. Мне этого не нужно, я хочу добавить данные сразу в модель Power Pivot (такая возможность появилась совсем недавно). Для этого я выберу пункт «Правка» в окне предварительного просмотра, либо в контекстном меню результата поиска. В режиме редактирования я смогу выбрать где размещать данные, а также присвою набору данных имя «Время»:

image3

После нажатия кнопки «Применить и закрыть»,  данные загрузятся в модель.

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

image4

Все потому, что Дима сделал не конкретный набор данных, а функцию, которая принимает в качестве параметров начальную и конечную дату диапазона. И в самом деле, зачем нам диапазон с какого-нибудь 2000 года и до 2020. Мы при построении модели лучше знаем в каком интервале у нас фактические данные. Поэтому нам нужно просто добавить запрос в книгу, чтобы потом использовать его для создания набора данных. Нажимаем правка и снимаем галки у параметров загрузки:

image5

Далее жмем «Применить и закрыть». В результате, если на вкладке Power Query нажать кнопку «Книга», мы увидим два запроса:

image6

Осталось получить набор данных. Для этого создадим пустой запрос

image7

На вкладке Просмотр нажмем кнопку «Расширенный редактор» и напишем запрос:

let
Источник = DimDate(#date(2012, 01, 01), #date(2014, 12, 31))
in
Источник

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

image8

Далее как обычно нажимаем «Применить и сохранить». На этом работа с загрузкой данных в модель закончена.

Для работы с моделью нужно на вкладке Power Pivot нажать кнопку «Управление». В результате откроется окно Power Pivot с загруженными наборами данных «Время» и «Дата». Я специально присвоил им такие имена (вместо DimTime и DimDate), потому что именно с такими названиями наши измерения будут отображаться в сводной таблице.

Теперь нам нужно провести некоторые манипуляции, чтобы наши измерения стали удобными для пользователей. Начнем мы с измерения «Дата». Для начала мы пройдемся по всем столбцам и укажем правильные типы данных, так как Power Pivot просто проигнорировал типы данных указанных в запросе Power Query и сделал их все строковыми – я был очень удивлен такому поведению, ведь из других источников импортируется хорошо. Я даже попробовал сначала загрузить данные на лист, а уже потом добавить в модель – типы данных определились правильно. Но я не хочу загромождать книгу дополнительными листами, поэтому не поленюсь и отредактирую параметры:

image9

Для столбца DateID тип Дата, для остальных столбцов, где в названии есть «ID» — целое число.
Далее, укажем модели, что это таблица дат (тогда в сводной таблице станет доступен очень удобный слайсер «Временная шкала»):

image10

Кнопка «Пометить как таблицу дат» доступна только если в наборе данных есть поле с типом datetime.
Затем переименуем те столбцы, которые будут видны в клиентских средствах. Это можно сделать либо дважды кликнув по заголовку, либо через контекстное меню. Я переименовываю следующие столбцы:

DateID -> Дата
YearID -> Год
QuarterOfYearName -> Кварталы
QuarterName -> Квартал
MonthOfYearName -> Месяцы
MonthName -> Месяц
WeekOfYearName -> Недели
WeekName -> Неделя
DayOfWeekName -> День недели

Прежде чем идти дальше я сделаю несколько пояснений. У кого-то может возникнуть вопрос, почему я использую столбец YearID, а не YearName – ответ прост, лично мне больше нравится отображение в виде «2012», а не «2012 г». Ну и мне удобнее использовать название «Кварталы», вместо «Квартал в году» или «Квартал года». Но это не значит, что делать нужно именно так – в своей модели архитектор Вы.

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

image11

Проделываем эти операции для всех столбцов, которые остались не переименованными.

Давайте посмотрим, что у нас получилось. Идем в Excel и на вкладке Данные жмем «Существующие подключения», далее на вкладке «Таблицы» выбираем «Таблицы в модели данных книги» и создаем отчет сводной таблицы. В панели сводной таблицы мы видим то, что мы уже сделали в модели:

image12

Переместим поле «Дата» в область «Строки». В результате в сводной таблице мы увидим список дат. Удалим это поле и переместим туда поле «Месяцы»:

image13

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

Возвращаемся в Power Pivot и настраиваем порядок сортировки для всех видимых столбцов.

image14

Вернувшись в сводную таблицу увидим, что сортировка сейчас работает правильно для всех полей (или если говорить языком разработчиков кубов – атрибутов измерения).

Итак, все хорошо, но чего-то не хватает. Не хватает нам иерархий, пойдем и сделаем несколько. Возвращаемся в Power Pivot и переключимся из представления данных в представление диаграммы:

image15

Чтобы было удобнее нажмем кнопку «Показать скрытые». Затем будем делать иерархии. Для того, чтобы добавить в измерение новую иерархию нужно кликнуть соответствующую копочку:

image16

Я собираюсь сделать иерархию Год-Квартал-Месяц-День, но такое название слишком длинно, и я назову новую иерархию Г-К-М-Д:

image17

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

image18

 

Я сделал еще две иерархии: Год-Месяц-День (Г-М-Д) и Год-Неделя-День (Г-Н-Д):

image19

Идем в Excel и видим наши иерархии в панели сводной таблицы. Если мы поместим с область строк иерархию Г-К-М-Д, то в таблице увидим вполне себе симпатичную иерархию:

image20

Теперь осталось измерение Время. Я не буду подробно останавливаться на шагах, так как порядок действий такой же. Сразу покажу результат:

image21image22

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

0  

Публикация общих запросов в Power Query

В Power Query предусмотрена замечательная возможность делиться своими запросами с другими людьми. В настоящее время это можно делать между пользователями тенанта Office 365.

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

После того, как мы написали и протестировали запрос Power Query его можно опубликовать. Для этого нужно наличие учетной записи в Office 365. На вкладке Power Query нажимаем кнопку Sign In и вводим учетные данные от своего аккаунта.

image1

После чего нажимаем правой кнопкой по запросу и выбираем пункт меню Share, либо ищем такую же кнопку, выделив запрос, на вкладке Query.

image2 image3

После чего откроется окно в котором укажем имя запроса, его описание, если требуется и для кого он будет доступен. После нажатия кнопки Share a Copy запрос будет опубликован.

image4

Чтобы найти чужой (или свой) запрос нам нужно на вкладке Power Query выбрать кнопку Online Search.

image5

Справа откроется область поиска, в которой нужно определить место, где искать данные и что искать. При это не нужно забывать войти под своей учетной записью Office 365.

OnlineSearch

При наведении на запрос будет отображено окно предпросмотра и ниже кнопки с действиями.

image7

Нажимаем Add to worksheet и запрос будет скопирован к нам на лист. Однако в действительности копируется не сам запрос, а ссылка на него и мы не можем увидеть его код или отредактировать. Если мы попытаемся открыть текст запроса в Advanced Editor, то увидим примерное следующее.

= Embedded.Value(«db00a962-0e6d-4aaa-aa55-7e6a9abaeb9e»)

Здесь содержится идентификатор запроса, который является ссылкой на версию. Это означает то, что если автор запроса его отредактирует, то в поиске Online Search будет отображаться только самая свежая версия, но старая ссылка будет все еще действительной и будет продолжать работать. Вы можете проверить это подменяя идентификатор для разных версий запроса.

Если же мы – автор запроса и хотим его удалить из общего доступа или отредактировать, то потребуется перейти на вкладку Power Query и отыскать кнопку Shared.

image8

Справа будут отображены опубликованные вами запросы.

image9

При наведении на запрос мы можем его открыть на редактирование, изменить настройки или вовсе удалить. Отредактированный запрос можно точно так же опубликовать, как и создать новый. Отличие лишь в том, что появляется дополнительная кнопка Update, при нажатии на которую запрос будет обновлен и будет создана его новая (текущая) версия. Старые версии, как уже было сказано выше будут продолжать оставаться доступными по ссылке с идентификатором.

image10

Таким образом, мы вкратце познакомились с интересной возможностью делиться своими запросами и\или данными в Power Query.

0