640580cvr.indd We are pleased to announce the release of Microsoft® PowerPivot for Excel® 2010: Give Your Data Meaning, by Marco Russo and Alberto Ferrari, to the printer. This book, for Excel power users and business analysts, will be available soon. In the meantime here are the Contents at a Glance and an excerpt from the Introduction.

 

Contents at a Glance


Preface
Acknowledgments
Introduction
1   First Steps with PowerPivot
2   PowerPivot at Work
3   Introduction to DAX
4   Data Models
5   Loading Data and Models
6   Evaluation Context and CALCULATE
7   Date Calculations in DAX
8   Mastering PivotTables
9   PowerPivot DAX Patterns
10 PowerPivot Data Model Patterns
11 Publishing to SharePoint
Appendix: DAX Functions Reference
Index

 

Introduction

Excerpt:

Since we first began thinking about Microsoft PowerPivot for Excel 2010, we have had to
revise our thoughts a number of times. First we needed to be persuaded to look at the tool
seriously. Then when we decided to write about it, we had to decide how to do that. Writing
a book is a hard work. Two authors writing a book together is much harder. You might think at
first that the project requires of each person half the time it would take either author to write
the whole book himself. As it turns out, a joint project requires a lot of communication because
each person discusses the drafts written by the other, which leads to long conversations about
what the final results should be. Despite our misunderstanding of the time it would take us
to write this book, we think that the final result is much better for the reader, who can read a
more balanced and accurate description of our topic in these pages than either of us might
have produced otherwise.

That said, there is a point where enough is enough and you do not want to negotiate a
shared vision anymore. For this reason, you can read our two separate points of view in
the Preface, which records our personal, uncensored, biased, and conflicting thoughts. But
both of us agree on an important point: PowerPivot is not a gadget. A first impression of
PowerPivot might mislead you. We made this wrong evaluation too, at the beginning of our
acquaintance with PowerPivot. Do not make the same mistake. PowerPivot is powerful and
useful, and it defines a new path for the era of self-service Business Intelligence (BI).

Who Is This Book For?


We wrote this book for Excel users who are interested in using PowerPivot to produce
Business Intelligence reports by themselves. In the book, we take for granted that you
have a good understanding of Excel. Business Intelligence skills are not required; we
provide insights and descriptions of what is useful if you are just entering the world of
self-service BI, covering the required topics more fully in cases we thought greater detail
might be necessary.

The book is composed of 11 chapters. We suggest that you read the book from cover to cover
even if we understand that many of you will probably jump directly to find the most appropriate
information for your business. Nevertheless, so that you understand at least the PowerPivot
basics, Chapters 1 through 5 are mandatory. Chapters 1, “First Steps with PowerPivot,” and 2,
“PowerPivot at Work,” are introductory and explain the basics of PowerPivot; you see some
simple examples and focus on the differences between classical PivotTables made with Excel
and the new PowerPivot engine. Chapter 3, “Introduction to DAX,” introduces the DAX
programming language, which is the language used by PowerPivot to compute formulas.

We devote Chapter 4, “Data Models,” to data modeling, which is an important topic because
good data modeling helps you to get the results you want faster and with minimal use
of DAX, exploiting all of the PowerPivot features. Chapter 5, “Loading Data and Models,”
covers all of the many different options for loading data into PowerPivot. Whether you
are using a SQL server, Microsoft Access, an OLAP cube on Microsoft SQL Server Analysis
Services, or any other media that stores your company information, you will find detailed
instruction on how to load data from there to PowerPivot.

Chapter 6, “Evaluation Context and CALCULATE,” is all about DAX, and you will find yourself
reading it more than once. We devote considerable attention to the evaluation context and
the most powerful function in PowerPivot, which is CALCULATE. Although you have seen
many calculations before this chapter, only here do you begin to understand how they really
work. You might find Chapter 6 hard to understand at first glance, but do not worry about it.
Whenever you feel the need, go back and give it a second and maybe third read. Every time
you wonder why a formula does not return the result you intended, this chapter is likely to
contain the explanation of the behavior and the keys for writing a better and faster formula
for your calculation.

Chapters 7 through 11 can be read separately because, even if their topics increase in
complexity from Chapters 7 through 11, each one treats specific topics. Chapter 7, “Date
Calculations in DAX,” deals with date calculations, such as Year To Date, Parallel Period
In Prior Year, computation of working days, and many other very useful and frequently
necessary date calculations. We strongly suggest that you learn this material well because a
correct calendar table is mandatory for performing date intelligence in PowerPivot. Chapter 8,
“Mastering PivotTables,” covers interesting Excel and PowerPivot features aimed at the production
of reports. It describes a mix of Excel 2010 and PowerPivot features, with many examples
of how to use them. The chapter also offers many ideas that you can use to build your reports.
Chapter 9, “PowerPivot DAX Patterns,” demands your close attention. You come to it with
basic knowledge about DAX; in the course of reading Chapter 9, you learn many complex
patterns of formulas, such as ratios, percentages, ranking, and ABC analysis. When you complete
this chapter, you deserve the rank of DAX expert. Chapter 10, “PowerPivot Data Model
Patterns,” is the logical continuation of Chapter 9: after you become a DAX expert, you need
to move a step further and become a data modeling expert. You see some examples of
common scenarios for which you need to use both the DAX programming language and a
specific data model. Chapter 11, “Publishing to SharePoint,” is the only chapter dedicated
to the Microsoft SharePoint integration with PowerPivot. It presents basic SharePoint
functionalities and shows how to extend PowerPivot features using its SharePoint
integration.