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.
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
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).
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.