Table Of ContentAnalyzing 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 mspinput@microsoft.com. 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
