Анализ данных при помощи Microsoft Power BI и Power Pivot для Excel

Альберто Феррари и Марко Руссо

Анализ данных  
при помощи Microsoft 
Power BI  
и Power Pivot  
для Excel

Analyzing Data  
with Microsoft Power BI  
and 
Power Pivot for Excel

Alberto Ferrari and Marco Russo

Анализ данных  
при помощи Microsoft 
Power BI и Power Pivot  
для Excel

Альберто Феррари и Марко Руссо

Москва, 2020

УДК 
004.424
ББК 
32.372
Ф43

Альберто Феррари и Марко Руссо
Ф43 
Анализ данных при помощи Microsoft Power BI и Power Pivot для Excel / пер. 
с анг. А. Ю. Гинько. – М.: ДМК Пресс, 2020. – 288 с.: ил.

ISBN 978-5-97060-858-6

УДК 004.424

ББК 32.372

В этой книге представлены базовые техники моделирования данных в Excel 
и Power BI. Авторы, специалисты в области бизнес-аналитики, делают акцент 
на реальных ситуациях, с которыми регулярно сталкиваются как консультанты. Они продемонстрируют общие техники моделирования, научат читателя 
производить расчеты с календарем, расскажут об использовании снимков для 
подсчета количества товаров в наличии, о том, как работать с несколькими 
валютами одновременно, и подробно объяснят на примерах многие другие 
полезные операции.
Издание предназначено как для новичков, так и для специалистов в области 
моделирования данных, желающих получить советы экспертов. Для изучения 
материала требуется владение Excel на среднем или продвинутом уровне.

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

Copyright Authorized translation from the English language edition, entitled 
ANALYZING DATA WITH POWER BI AND
POWER PIVOT FOR EXCEL, 1st Edition by ALBERTO FERRARI; MARCO RUSSO, 
published by Pearson Education, Inc, publishing as Microsoft Press, Copyright 
©2017
RUSSIAN language edition published by DMK PRESS PUBLISHING LTD., 
Copyright © [2020].

ISBN (анг.) 978-1-5093-0276-5                     © 2017 by Alberto Ferrari and Marco Russo
ISBN (рус.) 978-5-97060-858-6                     ©  Оформление, издание, перевод,  
ДМК Пресс, 2020

Оглавление

Рецензия ................................................................................................................. 9 

Предисловие от издательства .............................................................10

Введение ...............................................................................................................11
Для кого предназначена эта книга? ........................................... 11
Как мы представляем себе нашего читателя? ........................... 11
Структура книги .......................................................................... 12
Условные обозначения ................................................................ 14
Сопутствующий контент ............................................................. 14
Благодарности .............................................................................. 14
Список опечаток и поддержка .................................................... 14
Обратная связь ............................................................................. 15
Оставайтесь с нами ...................................................................... 15

Глава 1. Введение в моделирование данных .........................17
Работа с одной таблицей ............................................................. 18
Введение в модель данных ......................................................... 25
Введение в схему «звезда» .......................................................... 33
Понимание важности именования объектов ............................ 40
Заключение .................................................................................. 42

Глава 2. Использование  
главной/подчиненной таблицы  ........................................................45
Введение в модель данных с главной и подчиненной  
таблицами .................................................................................... 45
Агрегирование мер из главной таблицы ....................................47
Выравнивание главной и подчиненной таблиц ........................ 55
Заключение .................................................................................. 58

Глава 3. Использование множественных 
таблиц фактов ..................................................................................................59
Использование денормализованных таблиц фактов ................ 59
Фильтрация через измерения .................................................... 66
Понимание неоднозначности модели данных .......................... 69

Работа с заказами и счетами ...................................................... 72

Расчет полной суммы по счетам для покупателя ..................77
Расчет суммы по счетам, включающим данный  
заказ от конкретного покупателя ........................................... 78
Расчет суммы заказов, включенных в счета .......................... 78

Заключение .................................................................................. 81

Глава 4. Работа с датой и временем ...............................................83
Создание измерения даты и времени ........................................ 83
Понятие автоматических измерений времени ..........................87

Автоматическая группировка дат в Excel ...............................87
Автоматическая группировка дат в Power BI Desktop .......... 89

Использование нескольких измерений даты и времени ......... 90
Обращение с датой и временем ................................................. 96
Функции для работы с датой и временем .................................. 99
Работа с финансовыми календарями ....................................... 101
Расчет рабочих дней .................................................................. 104

Учет рабочих дней в рамках одной страны или региона ...... 104
Учет рабочих дней в разных странах ....................................107

Работа с особыми периодами года ........................................... 111

Работа с непересекающимися периодами ........................... 111
Периоды, связанные с текущим днем .................................. 113
Работа с пересекающимися периодами ............................... 116

Работа с недельными календарями ......................................... 118
Заключение ................................................................................ 124

Глава 5. Отслеживание исторических атрибутов ...............127
Введение в медленно меняющиеся измерения .......................127
Использование медленно меняющихся измерений ............... 133
Загрузка медленно меняющихся измерений .......................... 136

Исправление гранулярности в измерении .......................... 140
Исправление гранулярности в таблице фактов ................... 143

Быстро меняющиеся измерения .............................................. 145
Выбор оптимальной техники моделирования ........................ 149
Заключение ................................................................................ 150

Глава 6. Использование снимков ................................................... 151
Данные, которые нельзя агрегировать по времени ................ 151
Агрегирование снимков ............................................................ 153
Понятие производных снимков ............................................... 159
Понятие матрицы переходов .................................................... 162
Заключение ................................................................................ 168

6  
Оглавление

Глава 7. Анализ интервалов даты и времени ........................ 169
Введение во временные данные .............................................. 170
Агрегирование простых интервалов ........................................ 172
Интервалы с переходом дат ...................................................... 175
Моделирование рабочих смен  
и временных сдвигов ................................................................ 180
Анализ активных событий ........................................................ 182
Смешивание разных интервалов ............................................. 192
Заключение ................................................................................ 198

Глава 8. Связи «многие ко многим» ............................................. 201
Введение в связи «многие ко многим» .................................... 201

Понятие шаблона двунаправленной фильтрации .............. 203
Понятие неаддитивности ..................................................... 206

Каскадные связи «многие ко многим» ..................................... 208
Временные связи «многие ко многим» .................................... 211

Факторы перераспределения  
и процентные соотношения ................................................. 215
Материализация связей «многие ко многим» ......................217

Использование таблицы фактов в качестве моста .................. 218
Вопросы производительности .................................................. 219
Заключение ................................................................................ 223

Глава 9. Работа с разными гранулярностями ....................... 225
Введение в гранулярности ........................................................ 225
Связи на разных уровнях гранулярности .................................227

Анализ данных о бюджетировании...................................... 228
Использование DAX для распространения фильтра ........... 230
Фильтрация при помощи связей .......................................... 233
Скрытие значений на недопустимых  
уровнях гранулярности ......................................................... 235
Распределение значений по уровням  
с большей гранулярностью ................................................... 239

Заключение ................................................................................ 241

Глава 10. Сегментация данных в модели ................................ 243
Вычисление связей по нескольким столбцам ......................... 243
Вычисление статической сегментации .................................... 246
Использование динамической сегментации ........................... 248
Понимание потенциала вычисляемых столбцов:  
ABC-анализ ................................................................................ 251
Заключение ................................................................................ 256

Оглавление  7

Глава 11. Работа с несколькими валютами .............................257
Введение в различные сценарии ...............................................257
Несколько валют источника, одна валюта отчета ................... 258
Одна валюта источника, несколько валют отчета ................... 263
Несколько валют источника, несколько валют отчета ............ 268
Заключение ................................................................................ 270

Приложение A. Моделирование данных 101 ...................... 271
Таблицы ...................................................................................... 271
Типы данных .............................................................................. 273
Связи ........................................................................................... 273
Фильтрация и перекрестная фильтрация ................................ 274
Различные типы моделей ......................................................... 279

Схема «звезда» ....................................................................... 279
Схема «снежинка» .................................................................. 280
Модели с таблицами-мостами .............................................. 281

Меры и аддитивность ................................................................ 283

Аддитивные меры ................................................................. 283
Неаддитивные меры ............................................................. 283
Полуаддитивные меры .......................................................... 283

Предметный указатель ........................................................................... 285

8  
Оглавление

Рецензия

Вы держите в руках уникальную по нескольким причинам книгу.
Во-первых, это первая книга на русском языке по системе бизнесаналитики Microsoft Power BI. В течение нескольких последних лет, когда слушатели после тренингов по Excel, Power Pivot и Query спрашивали 
«что мне почитать про Power BI?», я не знал, что ответить. Англоязычной 
литературы написано по этой теме уже много, но на русском – полный 
ноль. Теперь уже нет.
Во-вторых, я очень рад, что в качестве первой ласточки издательство 
«ДМК Пресс» решило перевести именно эту книгу. Альберто Феррари и 
Марко Руссо однозначно входят в круг самых достойных авторов в этой области. Они щедро делятся своими знаниями в книгах и статьях, выступают 
на конференциях и проводят тренинги по Power Pivot, DAX и Power BI ещё 
с самого начала появления этих технологий и знают о них больше, чем кто 
бы то ни было. Отдельно, как тренер, хочу отметить их преподавательский 
талант, стройность и логичность объяснений, красоту примеров – это дорогого стоит.
Бизнес-аналитика (Business Intelligence, BI) давно уже перестала быть 
уделом гиков-айтишников из миллиардных корпораций. Сегодня она способна принести пользу при принятии управленческих решений в компании любого калибра, помочь визуализировать результаты и непрерывно 
отслеживать их динамику, собирая данные из разных «вселенных»: бухгалтерских программ, баз данных, файлов, интернета. Сегодня каждый может (и должен!) быть «сам себе аналитик». И эта книга – настоящий клад 
и огромное подспорье для всех, кто встал на этот путь.

Николай Павлов,
Microsoft Certified Trainer, Microsoft Most Valuable Professional,
автор проекта «Планета Excel», www.planetaexcel.ru

Предисловие от издательства

Отзывы и пОжелания

Мы всегда рады отзывам наших читателей. Расскажите нам, что вы думаете 
об этой книге – что понравилось или, может быть, не понравилось. Отзывы 
важны для нас, чтобы выпускать книги, которые будут для вас максимально 
полезны.
Вы можете написать отзыв прямо на нашем сайте www.dmkpress.com, 
зайдя на страницу книги, и оставить комментарий в разделе «Отзывы 
и рецен зии». Также можно послать письмо главному редактору по адресу 
dmkpress@gmail.com, при этом напишите название книги в теме письма.
Если есть тема, в которой вы квалифицированы, и вы заинтересованы в написании новой книги, заполните форму на нашем сайте по адресу 
http://dmkpress.com/authors/publish_book/ или напишите в издательство 
по адресу dmkpress@gmail.com.

СпиСОк ОпечатОк

Хотя мы приняли все возможные меры для того, чтобы удостовериться 
в качест ве наших текстов, ошибки все равно случаются. Если вы найдете 
ошибку в одной из наших книг – возможно, ошибку в тексте или в коде, – 
мы будем очень благодарны, если вы сообщите нам о ней. Сделав это, вы 
избавите других читателей от расстройств и поможете нам улучшить последующие версии этой книги.
Если вы найдете какие-либо ошибки в коде, пожалуйста, сообщите о них 
главному редактору по адресу dmkpress@gmail.com, и мы исправим это 
в следующих тиражах.

нарушение автОрСких прав

Пиратство в интернете по-прежнему остается насущной проблемой. Издательство «ДМК Пресс» очень серьезно относится к вопросам защиты авторских прав и лицензирования. Если вы столкнетесь в интернете с незаконно 
выполненной копией любой нашей книги, пожалуйста, сообщите нам адрес 
копии или веб-сайта, чтобы мы могли применить санкции.
Пожалуйста, свяжитесь с нами по адресу электронной почты dmkpress@
gmail.com со ссылкой на подозрительные материалы.
Мы высоко ценим любую помощь по защите наших авторов, помогающую нам предоставлять вам качественные материалы.