Добавление измерения времени в модель 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

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

leave your comment


пять − 3 =