Microsoft Excel 2013: Building Data Models With PowerPivot

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.

 

Banner

 

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.

 

Banner


Core Java for the Impatient, 3rd Ed

Authors:  Cay S. Horstmann 
Publisher: Addison Wesley
Pages: 576
ISBN: 9780138052102
Print: 0138052107
Kindle: B0B8RZZBDJ
Audience: Smart programmers wanting in-depth coverage
Rating: 4.8
Reviewer: Mike James

The key to this book is the word "impatient" in the title. What does this m [ ... ]



Functional Programming in C#, 2nd Ed (Manning)

Author: Enrico Buonanno
Publisher: Manning
Date: February 2022
Pages: 448
ISBN: 978-1617299827
Print: 1617299820
Kindle: B09P1Z2PPB
Audience: C# developers
Rating: 5
Reviewer: Mike James
Is C# a good language for functional programming?


More Reviews

 

Last Updated ( Wednesday, 12 February 2014 )