Analyzing_Data_with_Microsoft_Power_BI_and_Power_Pivot_for_Excel.pdf - Analyzing Data with Microsoft Power BI and Power Pivot for Excel Alberto Ferrari

Analyzing_Data_with_Microsoft_Power_BI_and_Power_Pivot_for_Excel.pdf

This preview shows page 1 out of 250 pages.

You've reached the end of your free preview.

Want to read all 250 pages?

Unformatted text preview: 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 . 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 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 ix CHAPTER 1 Introduction to data modeling 1 CHAPTER 2 Using header/detail tables 23 CHAPTER 3 Using multiple fact tables 35 CHAPTER 4 Working with date and time 55 CHAPTER 5 Tracking historical attributes 91 CHAPTER 6 Using snapshots 111 CHAPTER 7 Analyzing date and time intervals 127 CHAPTER 8 Many-to-many relationships 153 CHAPTER 9 Working with different granularity 173 CHAPTER 10 Segmentation data models 189 CHAPTER 11 Working with multiple currencies 203 APPENDIX A Data modeling 101 215 Index 227 iii This page intentionally left blank Contents Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix Chapter 1 Introduction to data modeling 1 Working with a single table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Introducing the data model. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Introducing star schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Understanding the importance of naming objects . . . . . . . . . . . . . . . . . . . . . 20 Conclusions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 Chapter 2 Using header/detail tables 23 Introducing header/detail. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Aggregating values from the header . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Flattening header/detail . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 Conclusions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 Chapter 3 Using multiple fact tables 35 Using denormalized fact tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 Filtering across dimensions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 Understanding model ambiguity. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 Using orders and invoices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 Calculating the total invoiced for the customer. . . . . . . . . . . . . . . . . . . 50 Calculating the number of invoices that include the given order of the given customer. . . . . . . . . . . . . . . . . . . . . . . . . 50 Calculating the amount of the order, if invoiced. . . . . . . . . . . . . . . . . . 50 Conclusions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 v Chapter 4 Working with date and time 55 Creating a date dimension. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55 Understanding automatic time dimensions. . . . . . . . . . . . . . . . . . . . . . . . . . . . 58 Automatic time grouping in Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58 Automatic time grouping in Power BI Desktop. . . . . . . . . . . . . . . . . . . 60 Using multiple date dimensions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 Handling date and time. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66 Time-intelligence calculations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 Handling fiscal calendars. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .69 Computing with working days. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72 Working days in a single country or region. . . . . . . . . . . . . . . . . . . . . . . 72 Working with multiple countries or regions. . . . . . . . . . . . . . . . . . . . . . 74 Handling special periods of the year. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 Using non-overlapping periods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 Periods relative to today. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80 Using overlapping periods. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82 Working with weekly calendars . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84 Conclusions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89 Chapter 5 Tracking historical attributes 91 Introducing slowly changing dimensions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91 Using slowly changing dimensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96 Loading slowly changing dimensions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 Fixing granularity in the dimension. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102 Fixing granularity in the fact table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104 Rapidly changing dimensions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106 Choosing the right modeling technique . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 Conclusions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110 Chapter 6 Using snapshots 111 Using data that you cannot aggregate over time. . . . . . . . . . . . . . . . . . . . . . . 111 Aggregating snapshots. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112 Understanding derived snapshots. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118 vi Contents Understanding the transition matrix. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119 Conclusions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125 Chapter 7 Analyzing date and time intervals 127 Introduction to temporal data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127 Aggregating with simple intervals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129 Intervals crossing dates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131 Modeling working shifts and time shifting. . . . . . . . . . . . . . . . . . . . . . . . . . . . 135 Analyzing active events. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137 Mixing different durations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145 Conclusions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151 Chapter 8 Many-to-many relationships 153 Introducing many-to-many relationships. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153 Understanding the bidirectional pattern. . . . . . . . . . . . . . . . . . . . . . . . 155 Understanding non-additivity. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157 Cascading many-to-many. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .158 Temporal many-to-many . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161 Reallocating factors and percentages. . . . . . . . . . . . . . . . . . . . . . . . . . . 164 Materializing many-to-many. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166 Using the fact tables as a bridge. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167 Performance considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168 Conclusions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171 Chapter 9 Working with different granularity 173 Introduction to granularity. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173 Relationships at different granularity. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175 Analyzing budget data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175 Using DAX code to move filters. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177 Filtering through relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179 Hiding values at the wrong granularity . . . . . . . . . . . . . . . . . . . . . . . . . . 181 Allocating values at a higher granularity. . . . . . . . . . . . . . . . . . . . . . . . 185 Conclusions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187 Contents vii Chapter 10 Segmentation data models 189 Computing multiple-column relationships. . . . . . . . . . . . . . . . . . . . . . . . . . . . 189 Computing static segmentation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192 Using dynamic segmentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194 Understanding the power of calculated columns: ABC analysis. . . . . . . . . 196 Conclusions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201 Chapter 11 Working with multiple currencies 203 Understanding different scenarios. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203 Multiple source currencies, single reporting currency. . . . . . . . . . . . . . . . . . 204 Single source currency, multiple reporting currencies. . . . . . . . . . . . . . . . . . 208 Multiple source currencies, multiple reporting currencies. . . . . . . . . . . . . . 212 Conclusions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214 Appendix A Data modeling 101 215 Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215 Data types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217 Relationships. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217 Filtering and cross-filtering. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218 Different types of models. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221 Star schema. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222 Snowflake schema. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222 Models with bridge tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224 Measures and additivity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225 Additive measures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225 Non-additive measures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225 Semi-additive measures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225 Index 227 viii Contents Introduction E xcel 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 accomplished our goal with this book. Moreover, you will be on your path to becoming a successful data modeler. This last step—that is, becoming a great data modeler—will only come with experience and after many failures. Unfortunately, experience is not something you can learn in a book. ix Who this book is for This book has a very wide target of different kind of people. You might be an Excel user who uses Power Pivot for Excel, or you may be a data scientist using Power BI. Or you could be starting your career as a business-intelligence professional and you want to read an introduction to the topics of data modeling. In all these scenarios, this is the book for you. Note that we did not include in this list people who want to read a book about data modeling. In fact, we wrote the book thinking that our readers probably do not even know they need data modeling at all. Our goal is to make you understand that you need to learn data modeling and then give you some insights into the basics of this beautiful science. Thus, in a sentence if you are curious about what data modeling is and why it is a useful skill, then this is the book for you. Assumptions about you We expect our reader to have a basic knowledge of Excel Pivot Tables and/or to have used Power BI as a reporting and modelling tool. Some experience in analysis of numbers is also very welcome. In the book, we do not cover any aspect of the user interface of either Excel or Power BI. Instead, we focus only on data models, how to build them, and how to modify them, so that the code becomes easier to write. Thus, we cover “what you need to do” and we leave the “how to do it” entirely to you. We did not want to write a step-by-step book. We wanted to write a book that teaches complex topics in an easy way. One topic that we intentionally do not cover in the book is the DAX language. It would have been impossible to treat data modeling and DAX in the same book. If you are already familiar with the language, then you will benefit from reading the many pieces of DAX spread throughout this book. If, on the other hand, you still need to learn DAX, then read The Definitive Guide to DAX, which is the most comprehensive guide to the DAX language and ties in well with the topics in this book. Organization of this book The book starts with a couple of easy, introductory chapters followed by a set of monographic chapters, each one covering some specific kind of data model. Here is a brief description of each chapter: x Introduction ■■ ■■ ■■ ■■ ■■ ■■ ■■ ■■ Chapter 1, “Introduction to data modeling,” is a brief introduction to the basic concepts of data modeling. Here we introduce what data modeling is, we start speaking about granularity, and we define the basic models of a data warehouse—that is, star schemas, snowflakes, normalization, and denormalization. Chapter 2, “Using header/detail tables,” covers a very common scenario: that of header/detail tables. Here you will find discussions and solutions for scenarios where you have, for example, orders and lines of orders in two separate fact tables. Chapter 3, “Using multiple fact tables,” describes scenarios where you have multiple fact tables and you need to build a report that mixes them. Here we stress the relevance of creating a correct dimensional model to be able to browse data the right way. Chapter 4, “Working with date and time,” is one of the longest of the book. It covers time intelligence calculations. We explain how to build a proper date table and how to compute basic time intelligence (YTD, QTA, PARALLELPERIOD, and so on), and then we show several examples of working day calculations, handling special periods of the year, and working correctly with dates in general. Chapter 5, “Tracking historical attributes,” describes the use of slowly changing dimensions in your model. This chapter provides a deeper explanation of the transformation steps needed in your model if you need to track changing attributes and how to correctly write your DAX code in the presence of slowly changing dimensions. Chapter 6, “Using snapshots,” covers the fascinating aspects of snapshots. We introduce what a snapshot is, why and when to use them, and how to compute value...
View Full Document

  • Fall '17
  • NA

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture