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.