Практический пример использования Excel + PowerPivot для подготовки корпоративной отчетности

Вводная

Опуская стандартные формулировки, кочующие из статьи в статью, из презентации в презентацию, скажу кратко: «Да, до сих пор в 21 веке есть проблема с отчетами». Точнее у самих отчетов как раз все хорошо: они большие, красивые — технологий-то много, а вот быстро найти нужную цифру в своих собственных данных становится все сложнее и сложнее. Путь от задумки до реализации в крупной компании все длиннее. Вот почему 2 года назад компания Microsoft задала тренд на «самообслуживание» потребителей информации, и с тех пор всем настал Self Service BI, получивший качественное развитие с появлением в «облаке» Power BI. За многочисленными «…services…» и «…power…», стоит бессменный с 1985 года Excel (по крайней мере, когда мы говорим именно о Self Service BI), обновляющийся и прирастающий новыми для пользователя возможностями.

Исходные данные

Это электронная таблица-отчет «Ведомость аварийных отключений технологического оборудования». Таблица содержит данные о фактах аварийных отключений за июль, август, сентябрь 2013 года.

1

Задача

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

Что требуется?

Сразу развею миф про «это может каждый». Под «самообслуживанием» предлагаю понимать в первую очередь возможность выполнить все своими собственными силами на своем компьютере без использования корпоративных серверов, баз данных, порталов и пр. ресурсов, которыми аналитик, как правило, не распоряжается напрямую. Определенные навыки по написанию формул, общие знания о реляционных базах данных и здравый смысл приложить придется. Как правило, это и является должностными обязанностями бизнес-аналитика. Из программного обеспечения понадобится Excel 2013 из Office 2013 Professional Plus. Если захотим опубликовать – аккаунт в Office 365 c Power BI.

Подготовка данных

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

2

В примере на листе Excel набит шаблон для таблицы дат, который содержит заголовки для будущего измерения, списки расшифровок для месяцев и дней недели, примерные формулы, которые могут пригодиться. Задаем начальную дату измерения. Брать старт от начала веков не вижу смысла, поскольку в нашем случае таблица фактов содержит данные только за три месяца. Протаскиваем автозаполнением даты по 30.09.2013.

3

Выделяем диапазон со всеми заголовками и датами, рядом появляется кнопка с быстрыми действиями. С помощью нее форматируем диапазон как таблицу. Это принципиально важно, поскольку только форматированная таблица может быть добавлена как связанная таблица в модель PowerPivot. Также применить форматирование к диапазону можно на ленте.

4

Теперь у нас есть таблица дат с заполненным ключевым полем Дата. Заполняем остальные атрибуты измерения.

5 6

Атрибуты Месяц и ДеньНедели требует небольшого пояснения. В нашем примере они вынесены в явно прописанные ряды для того, чтобы сделать модель независимой от локали системы, поскольку если, например, месяц возвращать формулой, то на английской локали мы получим английское названия месяцев. Тут тонкость в том, что при работе в превью-продуктах (например в настоящий момент PowerBI) русской локали там в принципе нет, а пользователи должны видеть русскую разыменовку атрибутов. При использовании явно указанных рядов мы можем писать в любом нужном нам формате: Январь, ЯНВАРЬ, Янв, ЯНВ и т.д. Также следует отметить, что все атрибуты, как правило, задаются попарно, если содержат строковое значение: КварталКлюч (целое) – Квартал (строковое), МесяцКлюч (целое) – Месяц (строковое) и т.д. Делается это для организации внешних ключей (связи между таблицами) и, что более важно, правильной сортировки. Например, если месяцы отсортировать по алфавиту, мы получим: Август, Апрель, Декабрь, Июль и т.д. Поэтому в дальнейшем в модели мы будем обязательно указывать поле сортировки для всех строковых атрибутов. Результат представлен на рисунке.

7

Добавляем созданную таблицу в модель PowerPivot. Для этого необходимо проверить Имя таблицы (именно так будет названо измерение в модели PowerPivot).

8

После проверки имени таблицы нажимаем Добавить в модель на ленте.

9

По поводу закладки на ленте PowerPivot. Надстройка PowerPivot до Excel 2013 скачивается как add-on, в Excel 2013 в составе Office
Professional Plus она уже есть по умолчанию, для Excel 2013 из Office Standard ее в принципе нет, для Excel 2013 из Office 365 она идет наиболее актуальная (например, в ней есть альтернативные переводы атрибутов измерений в модели PowerPivot). Если надстройка установлена, но закладки на ленте нет, возможно она не активна. Ее нужно активизировать через меню Файл – Параметры – Надстройки – Надстройки COM – Перейти… и отметить флагом нужные.

10 11

Следующая таблица, которая будет включена в модель PowerPivot – это таблица времени. Подход нам уже знаком.

12

. 13

В итоге у нас есть вторая таблица, связав которую с моделью PowerPivot получаем измерение Время.

14

Дальнейшие действия требуют пояснения. Существует два способа добавить таблицы фактов в модель:

  1. Непосредственно в модели PowerPivot воспользоваться импортом данных. Импорт данных возможен практически из любых источников, в том числе и из файлов *.xls, *.csv, что актуально в нашем примере. В случае больших массивов данных является безальтернативным (см. ниже), но накладывает ограничения на публикацию файла, поскольку модель пытается обновиться с несуществующего источника (если это локальный файл, а не, скажем, Azure или запрос Power Query), вызывая ошибку (по крайней мере в PowerBI Preview). Однако, при импорте данных непосредственно в модель PowerPivot выполняется компрессия движком Verti Paq, для пользователя это означает, что большие данные занимают очень мало места. Поэтому, если не предполагаются эксперименты с публикацией модели или источник доступен в интернете, можно смело использовать непосредственный импорт. К сожалению, в случае импорта с локальных файлов принудительно разорвать связь модели и источника нельзя в отличие от таблиц на листе Excel.
  2. Выполнить импорт данных на лист Excel, разорвать связь данных на листе с источником, добавить данные в модель PowerPivot как связанную таблицу. Этот способ имеет ограничения по объему данных (http://office.microsoft.com/en-gb/excel-help/data-model-specification-and-limits-HA102837464.aspx). Практически я столкнулся с ограничением в 10 Мб на лист в Office 365 при публикации книжки (см. третью сноску по приведенной ссылке). Зато файл с моделью получаются абсолютно автономными, то есть исключены попытки автоматического обновления данных в книге с внешних источников, что гарантирует результат при любых экспериментах с публикациями. Способ подходит, если таблицы фактов имеют небольшой размер, как в нашем случае.

Поскольку у нас стоит задача максимально попробовать все варианты публикации данных, и размер таблиц фактов относительно не велик (порядка 1,5 тысяч записей), остановимся на втором варианте. В связи со всем вышесказанным, переносим таблицу-отчет «Ведомость аварийных отключений технологического оборудования» непосредственно в нашу книгу. Самый быстрый способ в случае работы с книгами Excel – использовать копирование листа в другую книгу.

15

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

1. Сформировать на отдельном листе сводную таблицу на основании модели PowerPivot.

16

2. В строки вынести будущее измерение (в нашем случае Наименование филиала)

17

3. Полученные значения строк вставить как значения в заготовку таблицы измерения.

18

А дальше уже по пройденному пути: форматировать как таблицу, проверить имя, … Но! Во-первых, данный вариант пригоден только в случае, если таблица фактов в модели статична, по крайней мере выборка «…DISTINCT Наименование филиала…» неизменна (это именно наш случай). Во-вторых, перед добавлением таблицы в модель необходимо выполнить обогащение данных, а проще говоря заполнить руками недостающие атрибуты. К недостающим атрибутам в данном случае относится поселок, широта и долгота. Широта и долгота обеспечивают нам абсолютную привязку на любой картографической подложке. Тут есть тонкость: на глубину до административного центра субъекта РФ картография Bing (только она доступна в PowerMap и PowerView) работает хорошо при условии указания полного адреса – страна, субъект, город. Как только нам необходимо опуститься глубже, например, поселок, не являющийся даже районным центром, тут уже возможна не правильная привязка на карте. В любом случае, заполнить широту и долготу при правильном подходе с использованием, например, Google Maps намного быстрее, чем писать полные адреса поселков, а потом еще и отлавливать ошибки. Однако данный способ, естественно, не будет работать, когда требуется закрасить некую область на карте, например, область или штат. После заполнения недостающих атрибутов добавляем таблицу в модель PowerPivot.

19

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

20

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

21

Согласно принятому ранее решению сначала добавляем данные на лист Excel посредством импорта из текстового файла.

22

Форматируем диапазон как таблицу, присваиваем имя таблице, добавляем в модель PowerPivot.

23

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

Подготовка модели PowerPivot

Переходим в окно управления PowerPivot.

24

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

25

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

26

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

27

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

28

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

29

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

Время=TIME(HOUR([Время отключения]);MINUTE([Время отключения]);0)+2

Дата=DATE(YEAR([Время отключения]);HOUR([Время отключения]);DAY([Время отключения]))

Продолжительность=HOUR([Время восстановления потребителей]-[Время отключения])+MINUTE([Время восстановления потребителей]-[Время отключения])/60

Продолжительность вычисляем в часах, выставляем точность 2 знака.

Скрываем все столбцы в таблице, кроме столбца Продолжительность.

30

Таблица Время требуем минимальной доработки. Устанавливаем поля сортировки, скрываем ключевые поля. Для более понятного отображения лучше изменить формат полей Время, Минута, Час на короткий.

31

Таблицы Филиал и ПричинаДефекта остаются без редактирования.

В таблицу Погода добавляем три вычисляемых столбца:

Ветер=IF(FIND(«-»;[Скветра];1;0);VALUE(mid([Скветра];FIND(«-»;[Скветра];1;0)+1;LEN([Скветра])-FIND(«-»;[Скветра];1;0)));VALUE([Скветра]))

Гроза=IF(FIND(«гроз»;[Явл];1;0);1;0)

ВремяКлюч=TIME(TRUNC([Время]/100);MOD([Время];100);0)+2

В столбце Ветер выполняем парсинг текстового поля Скветра, берем максимальное значение (порывы) ветра. В столбце Гроза парсим поле Явл на предмет наличия в данный момент грозы. Столбец ВремяКлюч парсится из строкового значения поля Время. Все столбцы скрываем из пользовательского интерфейса.

32

Далее для реализации «хитрых» отчетов типа Like-to-Like (сравнение показатель двух произвольных временных периодов) необходимо задублировать таблицы ВедомостьАварийныхОтключений и Дата, создав их клоны ВедомостьАварийныхОтключенийСравнение и ДатаСравнение. Для реализации требуемого функционала исходные таблицы и их клоны не должны быть связаны между собой в модели PowerPivot. Такой прием используется и в классических кубах, когда дублируется таблица фактов. На ленте в разделе Данные выбираем Существующие подключения – Таблицы – таблица ВедомостьАварийныхОтключений, способ представления Таблица.

33

Кроме того, необходимо разорвать связь с исходной таблицей, иначе ее нельзя будет добавить в модель PowerPivot.

34

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

35

Теперь можно добавить связи между таблицами. Удобнее и быстрее это делать непосредственно на макете. Результат на рисунке.

36

Модель PowerPivot допускает создание иерархий. В нашем примере отчетов они не понадобятся. Для порядка добавим единственную иерархию в таблицу Дата.

37

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

38

Добавить необходимо следующие меры: Таблица Погода Киров: MAX Ветер:=MAX([Ветер])                 SUM Гроза:=SUM([Гроза]) Таблица Ведомость аварийных отключений:

                Число Продолжительность:=DISTINCTCOUNT([Продолжительность])

                Ср Продолжительность Сравнение:=AVERAGE(‘ВедомостьАварийныхОтключенийСравнение’[Продолжительность])

                МАХ Продолжительность Сравнение:=MAX(‘ВедомостьАварийныхОтключенийСравнение’[Продолжительность])

                Сумма Продолжительность Сравнение:=IF(DISTINCTCOUNT(‘ДатаСравнение’[МесяцКлюч])<>0;SUM(‘ВедомостьАварийныхОтключенийСравнение’[Продолжительность])/DISTINCTCOUNT(‘ДатаСравнение’[МесяцКлюч]);SUM(‘ВедомостьАварийныхОтключенийСравнение’[Продолжительность]))

                Ср Продолжительность:=AVERAGE([Продолжительность])

                МАХ Продолжительность:=MAX([Продолжительность])

                Сумма Продолжительность:=IF(DISTINCTCOUNT(‘Дата’[МесяцКлюч])<>0;SUM([Продолжительность])/DISTINCTCOUNT(‘Дата’[МесяцКлюч]);SUM([Продолжительность]))

                Показатель Ср Продолжительность:=IF([Ср Продолжительность Сравнение]<>0;([Ср Продолжительность Сравнение]-[Ср Продолжительность])/[Ср Продолжительность Сравнение]*100;100)

                Показатель MAX Продолжительность:=IF([МАХ Продолжительность Сравнение]<>0;([МАХ Продолжительность Сравнение]-[МАХ Продолжительность])/[МАХ Продолжительность Сравнение]*100;100)

                Показатель Сумма Продолжительность:=IF([Сумма Продолжительность Сравнение]<>0;([Сумма Продолжительность Сравнение]-[Сумма Продолжительность])/[Сумма Продолжительность Сравнение]*100;100)

Для добавление KPI можно воспользоваться контекстным меню, кликнув на мере.

39

Настраиваем KPI для мер Показатель Ср Продолжительность, Показатель MAX Продолжительность, Показатель Сумма Продолжительность. Настройка однотипная, принимаем за норму 15-ти процентное отклонение. Результат проделанной работы на рисунке.

40

Подготовка отчетов При наличии готовой модели в Excel наступает самая что ни на есть Self Service BI’айность: drag and drop’ь налево и направо. Для этого достаточно создать новый лист Excel, на ленте выбрать Существующие подключения – Таблицы – Таблицы в модели данных книги – Отчет сводной таблицы.

41

Общий вид на рисунке.

42

Общие рекомендации из практики подготовки отчетности в Excel:

Один лист – одна сводная таблица – одна сводная диаграмма

Правило не без исключений, бывает красиво смотрятся на листе и 3-4 графика, но это только в случае, когда вы долго и тщательно подгоняете, компонуете, и данные статичны. Если сводная таблица сформирована по принципу «матрица», то есть резиново может расти и вниз, и вправо, то советую придерживаться правила, иначе при наезде одной сводной таблицы на другую будет возникать ошибка (каждый раз при открытии файла и обновлении данных). Это правило работает именно для продакшн-отчетов, когда данные обновляются, изменяется число элементов измерений. Для демонстраций можно от него отступать.

Сводная таблица на B2

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

«Нет» портянкам

Если отчет не влезает на один экран и требует скроллинга (особенно горизонтального) – значит его стоит разбить на два отчета либо использовать срезы. Правило не относится к регламентированным формам, которые исторически ценятся исключительно за число столбцов >50 и формат этак 3хА4. Теперь подготовим отчеты, раскрывающие предметную суть нашей модели: корреляция между погодными условиями и отказами технологического оборудования. Прошу оставить возгласы «Капитан очевидность!..», поскольку предполагать наличие зависимости — это одно, а вот выразить зависимость в цифрах для годового отчета о работе, например, сервисной службы — это совсем другое. Первый отчет – оперативная сводка, основанная на KPI. Стало лучше, хуже, не изменилось. Основное назначение – руководитель быстро зашел, увидел «красное», задал вопрос.

43

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

44

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

45

Корреляция погодных условий с отказами по времени суток. Четко виден пик грози отказов оборудования.

46

Выводы

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

leave your comment


четыре × 7 =