Authors: Alberto Ferrari & Marco Russo
Publisher: Microsoft Press
Pages: 487
ISBN: 978-0735676343
Aimed at: Experienced Excel users and developers
Rating: 4.5
Reviewed by: Kay Ewbank

Need to turn mass quantities of data into meaningful information? If so is this the book you need?

PowerPivot started life as an add-on for Excel in Excel 2010, with the aim of turning business intelligence into a topic that could be carried out by any Excel user. It has its own query and extended function language called DAX (Data Analysis eXpressions language), and you can use PowerPivot on datasets up to millions of rows. This sounds unlikely, but the technology behind PowerPivot comes from some innovative standalone software that Microsoft bought in and incorporated. In reality, you’re using the tabular engine from SQL Server Analysis Services 2012 running as a process within Excel. In Excel 2013 PowerPivot was moved from its add-in status to be part of the main spreadsheet software, though some features are still accessed as an add-in.

The whole idea of self-service BI is one that Microsoft has been keen to push. As a developer or data analyst, this is great news. Countless Excel users get started with BI, realise it could be incredibly useful, and put a lot of time and effort into creating solutions. Some get all the way, but an awful lot more realise it would be much more cost effective to pay a real programmer and/or data analyst to make their mad idea work. This is why it makes sense as a programmer to learn PowerPivot. It’s Access and the departmental database all over again.

The book starts with guided tour of PowerPivot for Excel 2013, and shows you how to create a simple Power View report. Power View is the reporting tool half of PowerPivot. The authors then explain the parts of PowerPivot that are still accessed from the add-in – calculated columns and fields and hierarchies.

DAX is introduced next. While DAX is described as a programming language, it is essentially a set of specialised functions and query operators that let you put together formulae and expressions. If you want to use flow of control, you still end up working in Visual Basic for Applications (VBA).

Having introduced the basics of DAX, Ferrari and Russo go on to explain the basics of data modelling. This is where much of the skill of BI is based. Concepts such as normalization and denormalization, the structure of SQL queries, and relationships and how they work are all covered. The chapter ends with a look at the structure of data marts and data warehouses. The next topic to be covered is that of publishing to SharePoint. You didn’t imagine Microsoft would invent a technology that didn’t somehow involve SharePoint, did you? The chapter covers publishing workbooks to SharePoint for team BI (what a terrible thought), as well as using PowerPivot for SharePoint as a server-side application that you can program and extend.

The two chapters on ‘understanding evaluation contexts’ and ‘understanding Calculate’ are the main core of the book. All DAX expressions are evaluated in a context – the environment within which the formula is found. Working out what a formula will actually be used on is one of the trickier aspects of BI. As the authors explain, if you have a formula that looks as though it works out the sum of all the sales amounts, it will give one answer if used on the raw table, but a different answer if used within a cell in a pivot table that slices sales by product type, where it will calculate the sum of sales for the particular product type shown in that slice. I thought Ferrari and Russo explained this concept well, as they did the Calculate function. Just as in SQL the Select statement is 90 percent of the language, so Calculate is the most important function in DAX. There’s a good chapter on Power View, and another on ‘shaping the reports’ that describes how to use features such as Key Performance Indicators.

The next three chapters cover specific aspects of DAX – date calculations, advanced DAX, and using DAX as a query language. The date chapter looks at how to use concepts such as Year To Date and Month To Date, working versus non-working days, moving averages, and all the other things that go wrong when trying to do date calculations. Advanced DAX is actually a collection of ‘scenarios and solutions’ based on the authors’ experience as consultants and when giving courses. Next comes a chapter on automating operations using VBA with some code examples showing things like using the Model object. I’d have preferred a lot more coverage of VBA, but there’s enough to get you started. The book ends with a comparison of PowerPivot in Excel, SharePoint, and SQL Server Analysis Services.

This is a good book. It’s well written and the examples make sense. I’d have liked more on the more advanced possibilities of DAX, and of using VBA with PowerPivot, but there’s enough to show you what’s possible.