Книжная полка Сохранить
Размер шрифта:
А
А
А
|  Шрифт:
Arial
Times
|  Интервал:
Стандартный
Средний
Большой
|  Цвет сайта:
Ц
Ц
Ц
Ц
Ц

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

Покупка
Артикул: 748355.01.99
Доступ онлайн
999 ₽
В корзину
В этой книге представлены базовые техники моделирования данных в Excel и Power BI. Авторы, специалисты в области бизнес-аналитики, делают акцент на реальных ситуациях, с которыми регулярно сталкиваются как консультанты. Они продемонстрируют общие техники моделирования, научат читателя производить расчеты с календарем, расскажут об использовании снимков для подсчета количества товаров в наличии, о том, как работать с несколькими валютами одновременно, и подробно объяснят на примерах многие другие полезные операции. Издание предназначено как для новичков, так и для специалистов в области моделирования данных, желающих получить советы экспертов. Для изучения материала требуется владение Excel на среднем или продвинутом уровне. Все права защищены. Любая часть этой книги не может быть воспроизведена в какой бы то ни было форме и какими бы то ни было средствами без письменного разрешения владельцев авторских прав.
Феррари, А. Анализ данных при помощи Microsoft Power BI и Power Pivot для Excel : практическое руководство / А. Феррари, М. Руссо ; пер. с англ. А. Ю. Гинько. - Москва : ДМК Пресс, 2020. - 288 с. - ISBN 978-5-97060-858-6. - Текст : электронный. - URL: https://znanium.com/catalog/product/1210705 (дата обращения: 29.03.2024). – Режим доступа: по подписке.
Фрагмент текстового слоя документа размещен для индексирующих роботов. Для полноценной работы с документом, пожалуйста, перейдите в ридер.
Альберто Феррари и Марко Руссо

Анализ данных  
при помощи 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 со ссылкой на подозрительные материалы.
Мы высоко ценим любую помощь по защите наших авторов, помогающую нам предоставлять вам качественные материалы.

Введение

Пользователи Excel любят цифры. А может, те, кто любят цифры, любят 
Excel. Как бы то ни было, если вам нравится доходить до самой сути при 
анализе любых наборов данных, скорее всего, вы провели немало времени, 
работая с Excel, сводными таблицами и формулами.
В 2015 году увидел свет программный продукт Power BI. И сегодня справедливо будет утверждать, что те, кто любят цифры, любят также Power Pivot 
для Excel и Power BI. Эти средства имеют много общего – в частности, их 
объединяет движок баз данных VertiPaq, а также язык DAX, унаследованный 
от SQL Server Analysis Services.
В прежних версиях Excel процесс анализа информации главным образом основывался на загрузке наборов данных, расчете значений в столбцах 
и написании формул для построения графиков. При этом в своей работе вы 
сталкивались с серьезными ограничениями – начиная с размера рабочей 
книги и заканчивая тем, что язык формул Excel не лучшим образом подходит для решения числовых задач большого объема. Новый движок, лежащий в основе Power BI и Power Pivot, стал огромным шагом вперед. С ним 
в вашем распоряжении оказался полный функционал баз данных, а также 
потрясающий язык DAX. Но ведь с большой силой приходит и большая ответственность! И если вы хотите воспользоваться всеми преимуществами 
этих новых средств, вам придется многому научиться. В частности, необходимо будет познакомиться с основами моделирования данных.
Моделирование данных – это отнюдь не ядерная физика, а лишь набор базовых знаний, которым должен овладеть всякий, кто заинтересован 
в анализе данных. К тому же если вы любите цифры, то вам непременно 
придется по душе моделирование данных. Освоить эту науку будет несложно, а вместе с тем вы получите массу удовольствия.
В этой книге вы познакомитесь с базовыми концепциями моделирования 
данных на практических примерах, с которыми наверняка не раз встречались в жизни. В наши планы не входило написание запутанной книги с подробным описанием комплексных решений, необходимых для реализации 
сложных систем. Вместо этого мы сосредоточились на реальных ситуациях, 
с которыми ежедневно сталкиваемся в работе в качестве консультантов. 
Когда к нам обращались за помощью, а мы видели, что имеем дело с типичной задачей, то отправляли ее прямиком в архив. Позже, открыв заветный 
ящик, мы получили ценные примеры для книги и расположили их в порядке, пригодном для обучения моделированию данных.

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

Для кОгО преДназначена эта книга?

Целевая аудитория книги довольно разнообразна. В нее входят и пользователи Excel, применяющие в своей практике Power Pivot, и специалисты по 
анализу данных в Power BI, и даже новички в области бизнес-аналитики, 
желающие познакомиться с основами моделирования данных. Все они потенциальные читатели данной книги.
Заметьте, что мы не включили в этот список тех, кто целенаправленно 
хочет почитать о создании моделей данных. Изначально мы предполагали, 
что наш читатель может даже не знать, что ему нужно какое-то моделирование каких-то данных. Наша цель – дать вам понять, что проектирование 
моделей данных – это как раз то, что вам нужно, и познакомить с базовыми 
принципами этой прекрасной науки. В общем, если вам интересно, что такое моделирование данных и чем оно так полезно, эта книга для вас.

как мы преДСтавляем Себе нашегО читателя?

Мы предполагаем, что наш читатель обладает базовыми знаниями в области сводных таблиц Excel и/или имеет опыт использования Power BI в качестве средства отчетности и моделирования. Наличие аналитических навыков также приветствуется. В своей книге мы не затрагиваем вопросы 
интерфейса Excel или Power BI. Вмес то этого мы фокусируем свое внимание 
исключительно на моделях данных – как проектировать и модифицировать 
их так, чтобы значительно упростить запросы. Так что наша задача – рассказать вам, что делать, а как это делать, вы уж решите сами. Мы не планировали создавать пошаговое руководство, а хотели максимально простым 
языком объяснить достаточно сложную тему.
Также мы намеренно обошли вниманием описание языка DAX. Было 
бы невозможно уместить в одной книге и теорию моделирования данных, 
и DAX. Если вы уже знакомы с этим языком, вам будет проще разобраться 
с многочисленными примерами кода на DAX, представленными в данной 
книге. В противном случае советуем вам прочитать книгу «Подробное руководство по DAX» (The Definitive Guide to DAX), являющуюся полноценным 

12  
Введение

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

Структура книги

Книга начинается с пары легких вводных глав, за которыми следуют главы, 
каждая из которых посвящена отдельному виду модели данных. Предлагаем вам краткое описание:
 
 глава 1 «Введение в моделирование данных». Является вводной частью в базовые принципы моделирования данных. В ней мы расскажем, что из себя представляет модель данных, начнем говорить 
о понятии гранулярности, определим понятия основных моделей 
хранилища данных – «звезда» и «снежинка», – а также поговорим 
о нормализации и денормализации;
 
 глава 2 «Использование главной/подчиненной таблицы». Описывает 
наиболее распространенный сценарий с наличием главной и подчиненной таблиц. В этой главе мы обсудим пример с заказами и строками заказов, размещенными в двух отдельных таблицах фактов;
 
 глава 3 «Использование множественных таблиц фактов». Описывает 
сценарии, в которых у вас есть множество таб лиц фактов, на основании которых необходимо построить единый отчет. В этой главе мы 
подчеркнем важность создания корректной многомерной модели для 
облегчения работы с информацией;
 
 глава 4 «Работа с датой и временем». Это одна из самых длинных 
глав книги. В ней затронуты вопросы логики расчетов на основании временных периодов. Мы расскажем, как правильно создать 
таблицу-календарь и работать с функциями времени (YTD, QTA, 
PARALLELPERIOD и др.). После этого приведем несколько примеров 
расчетов на основании рабочих дней, поработаем с особыми периодами года и поясним в целом, как правильно работать с датами;
 
 глава 5 «Отслеживание исторических атрибутов». В этой главе описываются особенности использования в модели данных медленно 
меняющихся измерений. Также представлено детальное описание 
трансформаций, которые необходимо выполнить для отслеживания исторических атрибутов, и даны инструкции по написанию 
корректного кода на DAX, учитывающего медленно меняющиеся 
измерения;
 
 глава 6 «Использование снимков». Описывает любопытные аспекты 
использования снимков (snapshot). В этой главе вы узнаете, что такое 
снимки, когда и для чего их необходимо использовать, а также как 
рассчитывать значения при применении снимков. Кроме того, мы 
посмотрим, как можно использовать мощную модель с применением 
матрицы переходов;

Введение  13

 
 глава 7 «Анализ интервалов даты и времени». В этой главе мы пойдем 
еще на шаг дальше, чем в главе 5. Мы продолжим заниматься временными вычислениями, но на этот раз обратимся к модели данных, 
в которой события, хранящиеся в таблице фактов, обладают определенной длительностью, а значит, требуют особого подхода для получения корректных результатов;
 
 глава 8 «Связи многие ко многим». Описывает характерные особенности использования связей «многие ко многим». Такой тип связи 
играет важную роль в любой модели данных. Мы рассмотрим обычные связи «многие ко многим», связи с каскадными действиями и их 
использование с учетом факторов перераспределения и фильтров. 
Также обсудим вопросы производительности таких связей и способы 
ее улучшения;
 
 глава 9 «Работа с разными гранулярностями». В этой главе мы углубимся в работу с таблицами фактов с разными уровнями гранулярности. Мы рассмотрим примеры из области бюджетирования, в которых таблицы фактов будут хранить информацию с разной степенью 
детализации, и предложим несколько альтернативных способов для 
решения этих ситуаций как при помощи языка DAX, так и непосредственно в модели данных;
 
 глава 10 «Сегментация данных в модели». В этой главе мы рассмотрим несколько моделей с применением техники сегментации. Начнем с простой сегментации по цене, пос ле чего перейдем к анализу 
динамической сегментации с использованием виртуальных связей. 
В конце главы проведем ABC-анализ средствами DAX;
 
 глава 11 «Работа с несколькими валютами». В этой главе мы рассмотрим особенности работы с несколькими валютами. Взаимодействуя 
с курсами валют, важно понимать их специфику и в соответствии 
с ней строить модель данных. Мы проанализируем несколько сценариев с разными требованиями и для каждого из них выработаем оптимальное решение;
 
 приложение A «Моделирование данных 101». Это приложение можно 
рассматривать как справочное руководство. Здесь мы кратко опишем 
на примерах все базовые концепции, использованные в этой книге. 
При возникновении вопросов вы всегда можете обратиться к приложению, освежить в памяти соответствующую тему и вернуться к чтению.
Сложность моделей и решений будет возрастать на протяжении всей книги, так что мы советуем читать ее последовательно, а не прыгать от главы 
к главе. Так вы сможете постепенно идти от простого к сложному и осваивать по одной теме за раз. После прочтения книга может стать для вас справочным руководством, и когда вам потребуется построить ту или иную модель данных, вы можете смело открыть нужную главу и воспользоваться 
предложенным решением.

14  
Введение

Доступ онлайн
999 ₽
В корзину