About This Book

This book is a little different from most books already out there on Power Pivot. It doesn't cover all the features of Power Pivot, nor does it cover the DAX language extensively. Many books before this one have already done those things well. Two good examples are Bill Jelen's PowerPivot for the Data Analyst and Rob Collie's DAX Formulas for PowerPivot.

This book is intended as a very practical book to help you get started on a Power Pivot journey that will bring your Excel and data analysis skills to the next level. This book follows Jim, a business user who is very familiar with Excel, on his journey to create a financial dashboard and complementary reports in Microsoft Excel. The journey starts with Jim finding out what information his organization needs in order to understand the current rhythm of its business. He then gathers that information and shapes it into a dashboard, in which he must determine the best ways to visualize information. As you follow Jim on this journey, you will use Power Pivot and DAX formulas to solve several very common business calculations, like year-to-date revenue, variance to target, and year-over-year growth.

You will also learn to create reports in Excel and Microsoft Power View to allow Jim's business to dive deeper into the numbers. Then you'll see how to share those workbooks to SharePoint and Office 365 Power BI.

In many places, this book dives deeper in subjects like the Power Pivot engine, DAX formulas, and Excel and dashboard design tips and tricks. Most of this book applies to both Excel 2010 and Excel 2013. However, Chapter 5 applies only to Excel 2013 because it's about Power View, which is not available in Excel 2010.

I hope you will find this book very useful in creating dashboards that provide insights into data, and I'm looking forward to seeing you out there in the Power Pivot community. You can find me at my blog, http://www.powerpivotblog.com, or on Twitter, at @kjonge.

What Is Business Intelligence?

Before you get hands-on with Excel, it's important to look at why the tools discussed in this book even exist.

Business intelligence (BI) has traditionally been used as an umbrella term to refer to software and practice that should lead to better insights and decisions for an organization. Instead of making decisions based on gut feeling, an organization can base its decisions on actual facts it visualizes by using business applications. Many Excel professionals are likely to think, "Hey, that's what I'm doing every day, but I don't give it a fancy name!"

BI gained traction in the 1990s, when companies started creating and collecting more and more data but couldn't get the information into the hands of the business users to create insights and make decisions based on that information.

Building BI solutions has traditionally been the territory of IT organizations and consulting firms. It has often resulted in very heavy-weight and expensive projects; these highly curated and complex systems have brought together a lot of information from all over a company into a data warehouse.

A data warehouse collects data from all over a company and consolidates it into what many think of as "the single version of the truth." An IT organization may want all data to flow through the BI system to make sure it's consistent and non-redundant, in order to gain "correct" insights.

To make the data in a data warehouse actionable, organizations have often created cubes on top of the data warehouses. They have optimized these cubes to gain fast access to the data for doing quick analytics on large amounts of data. Organizations have created canned reports based on these cubes in order for users to get insight into the data. In the 2000s, Excel improved this situation, making PivotTables available, so users could drag and drop data from a cube straight into Excel.

Today, the stream of information that flows through an organization comes not just from BI systems but also from the number-one BI tool in the world: Excel. Users from the business side of an organization — not from the IT side — create Excel reports. These reports often bypass a BI solution completely or mash up data from the data warehouse with additional data retrieved elsewhere. This often causes IT and business users to clash because IT folks want the data to come from their BI solution, but the business cannot wait for IT to provide that information. The world does not stand around and wait for the data to become available. Events happen all the time, and it is often crucial for an organization to react quickly.

As the pace of the world has increased and as more and more data has become available to organizations, CFOs and other stakeholders in organizations have wanted to get insights into data faster and faster. BI traditionally was set to create insights through long projects, but that type of system makes it hard to quickly get insights into the data. When the recent financial crisis hit, the business world had to make many cutbacks, especially in the IT space. So at the same time that IT departments are expected to provide more insights and provide oversight over the data, they now have fewer resources to consolidate larger amounts of data.

But an organization doesn't need to rely on just its IT department for data. An army of Excel users in any business knows the data inside out, and they are very proficient at creating reports and using data to gain insights. What if Excel users and IT could work together to serve the information needs of the organization and use each other's strengths instead of competing? This was exactly the idea that started the self-service revolution in 2006 at the Microsoft campus in Redmond. At that time, Microsoft began an incubation project called Gemini, named for the constellation. The twins in this project are IT and business users, working together.

The Self-Service Revolution: Power Pivot

Microsoft started its BI journey in 1994 by creating the very successful product Microsoft SQL Server Analysis Services (SSAS), which is designed for developers with an IT background. It is the bestselling analytical database engine in the industry. The idea behind Gemini was to shape the world-leading BI product SSAS into something that fits in Excel and can be used by Excel professionals. The Gemini incubation team aimed to determine whether it would be possible to empower Excel professionals and at the same time have them work together with IT. The team wanted to figure out how to put more business intelligence into the hands of the business users and allow them to "self-service" the information.

The Gemini team determined that it needed to create a product with a few radical features:

• The ability to work with massive amounts of data: Since SSAS hit the market in 1994, a lot has changed in the IT industry. Importantly, PCs have gotten more powerful, and memory has gotten much cheaper. What this meant for the Gemini team is that the product would need to work on the data and optimize it for analytics use in Excel. Whereas Excel 2010 and earlier allowed a user to work with 1 million rows of data, the Gemini team wanted a product that would allow users to work with very very large amounts of data directly in Excel — much larger amounts of data than anyone could have dreamed of before. The team thought that working with 200 million rows of data should be like a walk in the park.

• The ability to create a single PivotTable that combines data from two separate tables without writing a single VLOOKUP() — One of the most common uses of Excel is combining data from several separate data sources into a single report. In traditional Excel you need to use the complicated Excel function VLOOKUP to combine the data into a single table. In Power Pivot you can leave the data in the separate tables and just create a relationship.

• The Data Analysis Expressions (DAX) language — DAX, which is designed for analytics, is based on the Excel formula language and even shares some functions with Excel. At the same time, it's very different from the Excel formula language; whereas the Excel formula language references cells in a worksheet, DAX references tables and columns.

These three changes together bring a lot of power to the fingertips of many Excel users. As Bill Jelen describes in his book PowerPivot for the Data Analyst (http://ppivot.us/5Vqxd), "There are two types of Excel users: People who can do a VLOOKUP with their eyes closed and everyone else. ... Suddenly, hundreds of millions of people who (a) know how to use a mouse and (b) don't know how to do a VLOOKUP are able to perform jaw-dropping business intelligence analyses." Project Gemini brings the power of SSAS to a billion users of Excel — right on their desktop. This is referred to as "personal BI" or "self-service BI."

But project Gemini is more than an add-in for Excel. A workbook can be shared with team members using SharePoint or Office 365. When it is shared, the workbook retains all the interactivity but can be used by many users at the same time, through a web browser (with no Excel required). The data in a workbook can be refreshed via an automated schedule so that new data is added to the workbook with no work needed! This is called "team BI." Sharing workbooks to SharePoint also allows IT to govern the data shared onto SharePoint.

In October 2009, Gemini was renamed PowerPivot for Excel, and it would ship with Excel 2010 (see http://ppivot.us/5Vd7u). It was quite clear that PowerPivot would radically change both business intelligence and Excel. Shortly after the release of Excel 2013, PowerPivot was given a space in its name — Power Pivot (http://ppivot.us/ifdYe) — and that is the term we use for the remainder of this book.

Power Pivot Versions

As mentioned earlier, Power Pivot can be used with Excel on the desktop or in the browser using SharePoint or SharePoint Online in Office 365 and Power BI. In this chapter we take a brief look at each and see what the differences are.

Power Pivot for Excel

Power Pivot is available for both Excel 2010 and Excel 2013:

• Excel 2010 — When Excel 2010 was released, Power Pivot was first available as a free, downloadable add-in for Excel 2010. The first version that shipped was called PowerPivot 2008 R2, also known as Power Pivot v1. In 2012 a follow-up version of the add-in, PowerPivot 2012, was released. This version can still be downloaded for free, from http://ppivot.us/Fmbg4.

• Excel 2013 — With Excel 2013, Power Pivot no longer ships as a separate download from Excel but rather as part of Excel. Today it's available with the following versions of Excel:

* Office Professional Plus

* Office 365 Professional Plus

* Excel 2013 Standalone

Both the Excel 2010 and Excel 2013 versions of Power Pivot are available in two flavors: 32 and 64 bit. The difference between the two has to do with the amount of memory Power Pivot can use on your machine. If possible, choose the 64-bit version of Power Pivot, which allows you to work with larger amounts of data. Not everyone has this luxury, though, as an IT department may centrally roll out the 32-bit version of Office for the entire organization, since most users don't need the 64-bit version of Office. Even though I prefer the 64-bit version, having the 32-bit version won't prohibit you from working with Power Pivot.

To see what version of Excel you are running, select File, Account, About Excel and look at the top right of the About window.

All examples and screenshots in this book use Excel 2013, but almost all the functionality described here can also be used with Excel 2010. The exception is Chapter 5, which delves into building Power View reports.

Power Pivot for SharePoint and Office 365

In order to share workbooks on SharePoint, an add-in to SharePoint needs to be installed on the SharePoint server. For this use, Power Pivot for SharePoint can be installed from a SQL Server installation medium. IT departments usually set up Power Pivot for SharePoint.

With the release of Office 365 Power BI, you can buy an Office 365 subscription that allows you to share workbooks to SharePoint for Office 365 without worrying about setting up an environment.

You'll learn more about sharing workbooks in Chapter 6.

Tabular Models

In 2012 the SSAS team brought forward the Analysis Services Tabular Model. This is a version of Power Pivot that does not run inside Excel but runs on a server and is developed using the Microsoft programming tool Visual Studio. Most of the development is identical to that in Power Pivot in Excel, but it has some additional features that allow for working with larger amounts of data and that add security to the model.

This book focuses on Power Pivot for Excel and sharing these workbooks in either SharePoint or Office 365. For in-depth information on the Tabular Model, see Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model by Marco Russo, Alberto Ferrari, and Chris Webb (http://ppivot.us/3sblk).

How I Got Started with Power Pivot

Today I work on the Microsoft BI team, which creates amazing tools that allow every Excel and business user in the world to gain insights into data. This is my story and how my love of Power Pivot brought me to work at Microsoft.

I have been passionate about computers and IT from the moment my parents bought me a Commodore 64 in 1988. Ever since then, I have been glued to computers, and when I started going to a school that focused on IT, I actually started paying attention, and my grades finally started going up.

My first jobs were not in crunching data or getting numbers to people using Excel. I was riding the tail end of the dot-com bubble in the late 1990s, building websites. I've always had an affinity for trying to make sense of large amounts of data, but I had no idea there was a whole world out there that did this for a living — or that it had a name. I fondly remember that somewhere along the line, I tried to use HTML and SQL Server 6.5 to create a report that contained several charts. I continued going down the development path, using SQL, .NET, and ASP.NET while living in the weapon of choice for every developer: Visual Studio.

In 2004 I made a career switch to a DBA/developer role, where I was introduced to data warehousing, which I found to be like extreme database modeling. I was hooked instantly. Here I was also introduced to the tools that go on top of data warehouses, such as Cognos PowerPlay, which allows users in a business to analyze the data in their organization. I realized that users were able to get profound new insights, thanks to BI tools. They were enthusiastic about being able to work with such data for the first time.

When I decided I wanted to see some different companies, I tried my hand at consulting and moved back into a developer role. But I kept trying to get work that allowed me to give data to users in any shape or form. After about two years, I wanted back into business intelligence and managed to talk my manager into sending me off to an Analysis Services course. This five-day crash course in building multidimensional models was my introduction to Microsoft BI. After that, I largely focused on using cubes and reports and build BI solutions, as well as on occasional data warehouse jobs. I became a typical BI developer, working on long projects to deliver value to business users who usually had to wait some time to get the data they needed. They often came to my desk, asking for new calculations or additions to the models because they did not have the capability or tools to do it themselves. I wasn't really an Excel user, but I worked closely with business users (typically ones who did use Excel) to make sure they got the information they needed. While I was doing this work, I also started blogging, mostly to keep track of my findings for later reference. I still maintain that blog, at http://www.powerpivotblog.nl.

One day in late 2008, I heard about a new project called Gemini that would allow business users to gather and analyze their own data directly inside Excel (see http://ppivot.us/SEUSO). I was intrigued with this revolutionary technology that would bring the power of the complex cubes world to users directly inside Excel. This new product would make it possible for anyone in Excel to load millions of rows of data from multiple different places and combine the result into one report with ease. It seemed like science fiction to me then.

In August 2009, I finally got a chance to play with project Gemini and take it for a spin myself (http://ppivot.us/O1NUW). I was awed and in love. Gemini made it easy to quickly build reports that had before taken hours.

Then, in November 2009, my eyes were really opened, when I was introduced to the language that was underneath it all: DAX (http://ppivot.us/v3ThX). DAX is an incredibly powerful language that enabled me to do a lot with ease.

Around the same time, I found a partner in my Power Pivot explorations: Rob Collie (http://ppivot.us/aqdx8). We spent many nights trying to figure out how Power Pivot worked and trying to find new cool things we could do with Power Pivot. It was a pretty amazing time. I started trying to convince my manager that Power Pivot was a great tool and that we should use it in our day-to-day work with our customers; I was starting to get traction there.