Interview with Marco Russo and Alberto Ferrari about their new book about PowerPivot
Marco Russo and Alberto Ferrari just released new book: "Microsoft PowerPivot for Excel 2010: Give Your Data Meaning". I interviewed Marco and Alberto about their book and about PowerPivot product.
Vidas: I would like to start this interview with asking you to introduce yourself.
|Marco Russo: I’m born as a programmer, but in 1998 I started to deal with Data Warehouse, Data Marts and my life diverted to Business Intelligence. I still like to play with Visual Studio (I also wrote some books about .NET and LINQ) but I have to say I’m committed to BI, as you can see from my blog since 2004!
I’m also particularly proud of the “Expert Cube Development with Microsoft SQL Server 2008 Analysis Services” book I wrote with Alberto and Chris Webb. We got embarrassing reviews. I mean, when people says “must have” I’m embarrassed. That’s probably a way to express my happiness…
|Alberto Ferrari: I am a trainer and a consultant, I spend most of my time with customers to help them build complex data warehouses or with software houses that need advices with their customers, building BI solutions. In the spare time, I play with my child, trying to make him say “SQL” but… he still needs some time. :-) I have a blog but I don’t write too much, I prefer to write whitepapers about methodological approaches to the BI, like the SQLBI methodology, which I have developed with Marco.|
Vidas: When and why did you guys decide to write a book about PowerPivot? How much time it took you to write this book?
Alberto Ferrari: Well, the story of the book is nice and we have explained it in the book preface. When I first saw PowerPivot I thought it was a nice toy but nothing more, thus I was not interested in writing about it. After having played with it for a few days I changed my mind: PowerPivot is powerful and will change the way we think at BI solutions, thus a book became an interesting task. We started in December 2009 and finished in August 2010, dedicating to the book most of our spare time.
Marco Russo: Alberto already talked too much. The complete story of the book is in the preface and, really, I cannot say more here. Well, there is a table cover in this story. Is it enough to increase the mystery?
Talking seriously: the book is almost 400 pages long. According to initial plan, it should have been shorter. But we asked for more pages when it was clear we would have never agreed to cut something. As a reader, I prefer books that are shorter and more dense (in terms of content). But as an author, I know that being clear, simple, deep and short… is not so easy!
Vidas: How did you split writing job - who wrote which chapters?
Marco Russo: Alberto, you can do a good job with this question, let me drink my soda, thanks.
Alberto Ferrari: Well, I and Marco work well together because we are completing each other. I like to write long descriptions and to give overview of the architecture, Marco likes the most complex, inner and technical topics. Thus, I wrote all the introductory parts of the book and many of the examples, while Marco wrote about the most complex parts of DAX. That said, each chapter has been reviewed and changed by both, so at the end it is not easy to say who wrote what, we spend hours and hours together to decide what to write.
Vidas: I am sure you know about your competitors - at this point there are 3 other published books about PowerPivot. How is your book different? What your book has that other published books don't?
Alberto Ferrari: There are already good books about PowerPivot but I think that our one is the first that covers two very important topics: data modeling and DAX. Our background is not Excel but Business Intelligence, so we think to PowerPivot not only as an Excel add-in, but as a one of the many layers in a complex BI solution. While you can surely use PowerPivot to simply aggregate huge tables, when it comes to perform complex analysis you definitely need to learn both the DAX programming language and to model your data so that queries run fast. This doesn’t mean that PowerPivot is hard to learn. What I mean is that to exploit the full power of PowerPivot, you will need to take time to study it and our book provides you all the topics needed to become a real PowerPivot master. On the other hand, we do not cover in deep SharePoint integration and complex configurations, since our focus is on PowerPivot, not in its configuration and deployment in an IT shop.
Marco Russo: Alberto is right. I think that many of the existing PowerPivot books should be on the shelf of a BI developer, because if you look at the skills required to install, maintain and support PowerPivot, skills range from SharePoint to Excel passing through SQL Server and Analysis Services. That said, our book contains many explanations of DAX but also ready-to-use formulas for common business scenarios. We are particularly business oriented and we probably take for granted that the reader is already an Excel user and is able to understand the behavior of user interface without too many examples. We used the book real estate for explaining how you can create your data model with PowerPivot and how to get right numbers in the fastest way. Other books are more didactical about Excel and PowerPivot user interface. You can read all of them and you might always learn something new from each one.
Vidas: Not all users might understand why proper data modeling might be important for PowerPivot. Can you give a small example from your book/experience?
Alberto Ferrari: Absolutely yes. A first example might be banding, i.e. grouping values into discrete bands, so that you can analyze, for example, products sold in the price range of 1.00$ to 10.00$, then from 10.01$ to 20.00$ and so on. In the book we show three different solutions to the same scenario where, changing the data model (we move from a simple sales table to a more complex model with many to many relationships) the solution gets more and more clean and elegant. The same applies to a PowerPivot database used to simulate the adoption of different couriers, even if the original data is not perfectly shaped, by means of some simple tricks the user can change the data model to fit his needs and get a boost in performance and power. I don’t believe that an Excel user will need to know everything about data modeling, but knowing the fundamentals will prove to be very useful. Moreover, since the user needs to load data from his company database, it will be much better if he knows what to ask. I mean, database administrator can provide views and queries to PowerPivot users, so that they can browse data, but on the other side, PowerPivot users will need to know what to ask, so that they get useful sets of data.
Marco Russo: I see many people using DAX in PowerPivot to solve problems that would have been better solved by using a proper modeling technique on the PowerPivot tables. I’ve seen the same error using MDX on Analysis Service for many years, too. Designing a good data model is the first step to create a solution that is easy to maintain and also faster than other solutions.
Vidas: So who is target audience for your book?
Marco Russo: Microsoft uses the definition of “Information Workers” but in reality I would add “that already use Excel” to correctly identify the main target audience.
Alberto Ferrari: We wrote the book thinking at Excel Power Users, thus we do not require a reader to know programming languages, Business Intelligence or Data Modeling techniques. A good understanding of Excel is enough to get the most out of the book. Nevertheless, we think it might be interesting for DBA and BI analysts too, because knowing what your users can do with your data might help you in integrating PowerPivot in the BI infrastructure of your company.
Vidas: What are your thoughts about DAX? How easy it is to learn it.
Alberto Ferrari: DAX is easy to start with. You can write DAX formulas very quickly, at the beginning. Then, when you start thinking at more complex formulas, you will hit what we call the “CALCULATE wall”. CALCULATE is the most powerful function to work with, but it is very hard to learn, since it involves a deep understanding of evaluation contexts and PowerPivot internals. Nevertheless, when you move that step forward, the full power of DAX is in your hands and you will be able to write formulas that run at a blazing speed. So I can say that DAX is both easy and hard, easy to start with, hard to master. I surely wish I had our book available when I learned DAX but, we being the first to learn it, we had to work very hard. I remember when, speaking with the development team and asking them some advice for complex formulas, they always showed us a formula so elegant, compact and fast that we never thought at. Now that we learned DAX and CALCULATE, we are able to write the same formulas, but it has been difficult to reach this mastery.
Marco Russo: Alberto explained the CALCULATE issue very well. I hope that our book will help everyone to break the CALCULATE wall. I also have to say that DAX is much easier to learn if you… don’t know MDX! Yes, when you know MDX you are also tempted to apply the same logic to build a DAX expression. However, this doesn’t work. If you only know SQL, you are in a better position, initially. But when you want to break the CALCULATE wall, you need a new mindset, in any case. I don’t want to say it’s hard. Reading the book, it should be relatively easy, I hope!
Vidas: I know you were working with SSAS for many years so you have very good experience in building data warehouses. Do you think introduction of PowerPivot will change how data warehouse projects are done?
Marco Russo: It is going to change the requests that users will make to IT. It would be wrong assuming that data warehouses are dead and that self-service BI will replace them. The reality is the opposite, in my opinion. PowerPivot will help some users to find solutions to non-recurring reporting tasks and to reporting problems that don’t have budget for a “traditional” BI solution. But the demand for “validated” data that can be used in user-made reports can only increase. So, data warehouse will become much more important. Do you agree, Alberto?
Alberto Ferrari: Surely. Users will be able to perform complex analysis by themselves and to extend the SSAS solution with new data. Thus, the corporate BI will focus on ETL and OLAP cubes, knowing that the OLAP cube will be only the starting point for user analysis. If a user wants a new calculated column or a new kind of analysis, he now can perform this with PowerPivot and, if he finds it useful to many others, can ask the SSAS team to implement it in the corporate BI. Moreover, with PowerPivot users need to load data from both SSAS and the relational engine, thus the BI team will need to provide them easy to use interfaces to the database, so that self-service BI will really shine. One last important point is that PowerPivot can be used as a prototyping tool, to produce a very quick prototype of the final project, so that end users can quickly jump into the project and see the final result, giving good advices on it.
Vidas: What would be your recommendations to current SSAS developers - should they look into PowerPivot for their Enterprise DW implementations?
Alberto Ferrari: Absolutely yes. The Microsoft roadmap for Business Intelligence is clear: SharePoint, Excel, PowerPivot are currently well integrated with the BI stack and, in the future, DAX will play a major role in BI solutions. Self-Service BI is at the beginning and will evolve over the years. Moreover, we might imagine that some DAX ideas and technologies will be moved to the corporate BI, thus I think that learning DAX and PowerPivot is a mandatory step.
Marco Russo: I would be more direct. If you are a SSAS developer and you still want to play the BI game in the next years, you have to learn DAX. Sooner or later, you will need it. Thus, you have three choices. First, you can ignore DAX, waiting for your skills becoming relatively obsolete. Second, you can wait until when DAX will become mandatory, and you will have to get a new mindset next to MDX and SQL – and this will require months, not just days or weeks – and your project milestones might be not compatible with that requirement. Third, the suggested one: start learning DAX now. Get our book and learn it faster and easier, instead of losing your favorite movie because your DAX formula doesn’t work and you want to fix it.
Vidas: What’s next for you?
Alberto Ferrari: In the immediate future we will speak at PASS 2010, providing some intermediate and advanced PowerPivot sessions. Moreover, we have announced a PowerPivot workshop that will start in Netherlands in December (next to the Schiphol international airport) and hopefully move all around Europe to teach PowerPivot to Excel users who wants to use PowerPivot for their analysis. Then, since we like always be one step beyond, we are now studying SQL11 Denali, to understand what the future will bring to BI users.
Marco Russo: I would add that we are looking forward to speak at some future TechEd edition too. We had to renounce speaking at TechEd Europe 2010, because it has been scheduled the same week as PASS 2010 this year. But we definitely want to speak in Europe again (and also in other continents!) and we hope that 2011 will be a good year for that!
Vidas: Marco, Alberto - thank you very much for your time. This was very interesting interview. Your blog was one of the main resources that I used to learn DAX and I found it to be extremely helpful, so I really look forward to reading your book and learning more about PowerPivot and DAX.
Marco Russo and Alberto Ferrari: You’re welcome!
More info about book "Microsoft PowerPivot for Excel 2010: Give Your Data Meaning"
"Microsoft PowerPivot for Excel 2010: Give Your Data Meaning"
Authors: Marco Russo, Alberto Ferrari
Released date: October 08, 2010
Table of contents:
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