Analyzing Data with Microsoft Power BI and Power Pivot for Excel Alberto Ferrari and Marco Russo PUBLISHED BY Microsoft Press A division of Microsoft Corporation One Microsoft Way Redmond, Washington 98052-6399 Copyright © 2017 by Alberto Ferrari and Marco Russo. All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by any means without the written permission of the publisher. Library of Congress Control Number: 2016931116 ISBN: 978-1-5093-0276-5 Printed and bound in the United States of America. First Printing Microsoft Press books are available through booksellers and distributors worldwide. If you need support related to this book, email Microsoft Press Support at [email protected]. Please tell us what you think of this book at https://aka.ms/tellpress. This book is provided “as-is” and expresses the author’s views and opinions. The views, opinions and information expressed in this book, including URL and other Internet website references, may change without notice. Some examples depicted herein are provided for illustration only and are fictitious. No real association or connection is intended or should be inferred. Microsoft and the trademarks listed at https://www.microsoft.com on the “Trademarks” webpage are trademarks of the Microsoft group of companies. All other marks are property of their respective owners. Acquisitions Editor: Devon Musgrave Editorial Production: Polymath Publishing Technical Reviewer: Ed Price Copy Editor: Kate Shoup Layout Services: Shawn Morningstar Indexing Services: Kelly Talbot Editing Services Proofreading Services: Corina Lebegioara Cover: Twist Creative • Seattle Contents at a glance Introduction Chapter 1 Introduction to data modeling Chapter 2 Using header/detail tables Chapter 3 Using multiple fact tables Chapter 4 Working with date and time Chapter 5 Tracking historical attributes Chapter 6 Using snapshots Chapter 7 Analyzing date and time intervals Chapter 8 Many-to-many relationships Chapter 9 Working with different granularity Chapter 10 Segmentation data models Chapter 11 Working with multiple currencies Appendix A Data modeling 101 Index Contents Introduction Who this book is for Assumptions about you Organization of this book Conventions About the companion content Acknowledgments Errata and book support We want to hear from you Stay in touch Chapter 1. Introduction to data modeling Working with a single table Introducing the data model Introducing star schemas Understanding the importance of naming objects Conclusions Chapter 2. Using header/detail tables Introducing header/detail Aggregating values from the header Flattening header/detail Conclusions Chapter 3. Using multiple fact tables Using denormalized fact tables Filtering across dimensions Understanding model ambiguity Using orders and invoices Calculating the total invoiced for the customer Calculating the number of invoices that include the given order of the given customer Calculating the amount of the order, if invoiced Conclusions Chapter 4. Working with date and time Creating a date dimension Understanding automatic time dimensions Automatic time grouping in Excel Automatic time grouping in Power BI Desktop Using multiple date dimensions Handling date and time Time-intelligence calculations Handling fiscal calendars Computing with working days Working days in a single country or region Working with multiple countries or regions Handling special periods of the year Using non-overlapping periods Periods relative to today Using overlapping periods Working with weekly calendars Conclusions Chapter 5. Tracking historical attributes Introducing slowly changing dimensions Using slowly changing dimensions Loading slowly changing dimensions Fixing granularity in the dimension Fixing granularity in the fact table Rapidly changing dimensions Choosing the right modeling technique Conclusions Chapter 6. Using snapshots Using data that you cannot aggregate over time Aggregating snapshots Understanding derived snapshots Understanding the transition matrix Conclusions Chapter 7. Analyzing date and time intervals Introduction to temporal data Aggregating with simple intervals Intervals crossing dates Modeling working shifts and time shifting Analyzing active events Mixing different durations Conclusions Chapter 8. Many-to-many relationships Introducing many-to-many relationships Understanding the bidirectional pattern Understanding non-additivity Cascading many-to-many Temporal many-to-many Reallocating factors and percentages Materializing many-to-many Using the fact tables as a bridge Performance considerations Conclusions Chapter 9. Working with different granularity Introduction to granularity Relationships at different granularity Analyzing budget data Using DAX code to move filters Filtering through relationships Hiding values at the wrong granularity Allocating values at a higher granularity Conclusions Chapter 10. Segmentation data models Computing multiple-column relationships Computing static segmentation Using dynamic segmentation Understanding the power of calculated columns: ABC analysis Conclusions Chapter 11. Working with multiple currencies Understanding different scenarios Multiple source currencies, single reporting currency Single source currency, multiple reporting currencies Multiple source currencies, multiple reporting currencies Conclusions Appendix A. Data modeling 101 Tables Data types Relationships Filtering and cross-filtering Different types of models Star schema Snowflake schema Models with bridge tables Measures and additivity Additive measures Non-additive measures Semi-additive measures Index Introduction Excel users love numbers. Or maybe it’s that people who love numbers love Excel. Either way, if you are interested in gathering insights from any kind of dataset, it is extremely likely that you have spent a lot of your time playing with Excel, pivot tables, and formulas. In 2015, Power BI was released. These days, it is fair to say that people who love numbers love both Power Pivot for Excel and Power BI. Both these tools share a lot of features, namely the VertiPaq database engine and the DAX language, inherited from SQL Server Analysis Services. With previous versions of Excel, gathering insights from numbers was mainly a matter of loading some datasets and then starting to calculate columns and write formulas to design charts. Yes, there were some limitations: the size of the workbook mattered, and the Excel formula language was not the best option for huge number crunching. The new engine in Power BI and Power Pivot is a giant leap forward. Now you have the full power of a database and a gorgeous language (DAX) to leverage. But, hey, with greater power comes greater responsibility! If you want to really take advantage of this new tool, you need to learn more. Namely, you need to learn the basics of data modeling. Data modeling is not rocket science. It is a basic skill that anybody interested in gathering insights from data should master. Moreover, if you like numbers, then you will love data modeling, too. So, not only is it an easy skill to acquire, it is also incredibly fun. This book aims to teach you the basic concepts of data modeling through practical examples that you are likely to encounter in your daily life. We did not want to write a complex book on data modeling, explaining in detail the many complex decisions that you will need to make to build a complex solution. Instead, we focused on examples coming from our daily job as consultants. Whenever a customer asked us to help solve a problem, if we felt the issue is something common, we stored it in a bin. Then, we opened that bin and provided a solution to each of these examples, organizing them in a way that it also serves as a training on data modeling. When you reach the end of the book, you will not be a data-modeling guru, but you will have acquired a greater sensibility on the topic. If, at that time, you look at your database, trying to figure out how to compute the value you need, and you start to think that—maybe—changing the model might help, then we will have