Анализ данных при помощи Microsoft Power BI и Power Pivot для Excel
Покупка
Издательство:
ДМК Пресс
Перевод:
Гинько Александр Юрьевич
Год издания: 2020
Кол-во страниц: 288
Дополнительно
Вид издания:
Практическое пособие
Уровень образования:
ВО - Бакалавриат
ISBN: 978-5-97060-858-6
Артикул: 748355.01.99
Доступ онлайн
В корзину
В этой книге представлены базовые техники моделирования данных в Excel и Power BI. Авторы, специалисты в области бизнес-аналитики, делают акцент на реальных ситуациях, с которыми регулярно сталкиваются как консультанты. Они продемонстрируют общие техники моделирования, научат читателя производить расчеты с календарем, расскажут об использовании снимков для подсчета количества товаров в наличии, о том, как работать с несколькими валютами одновременно, и подробно объяснят на примерах многие другие полезные операции. Издание предназначено как для новичков, так и для специалистов в области моделирования данных, желающих получить советы экспертов. Для изучения материала требуется владение Excel на среднем или продвинутом уровне.
Все права защищены. Любая часть этой книги не может быть воспроизведена в какой бы то ни было форме и какими бы то ни было средствами без письменного разрешения владельцев авторских прав.
Тематика:
ББК:
УДК:
ОКСО:
- ВО - Бакалавриат
- 09.03.01: Информатика и вычислительная техника
- 09.03.02: Информационные системы и технологии
- 09.03.03: Прикладная информатика
- 09.03.04: Программная инженерия
ГРНТИ:
Скопировать запись
Фрагмент текстового слоя документа размещен для индексирующих роботов.
Для полноценной работы с документом, пожалуйста, перейдите в
ридер.
Альберто Феррари и Марко Руссо Анализ данных при помощи 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 Введение
Доступ онлайн
В корзину