Dashboarding and Reporting with Power Pivot and Excel

How to Design and Create a Financial Dashboard with PowerPivot – End to End

Kasper de Jonge

• Publisher: Holy Macro! Books
• Year: 2014
• Print ISBN: 9781615470273
• eBook ISBN: 9781615473397
• Edition: 1
• Topic: Ciencia de la computación
• Subtopic: Almacenamiento de datos

Microsoft's revolutionary Power Pivot is a tool that allows users to create and transform data into reports and dashboards in new and much more powerful ways using the most-used analytical tool in the world: Excel.

This book, written by a member of Microsoft's Power Pivot team, provides a practical step by step guide on creating a financial dashboard. The book covers in detail how to combine and shape the relevant data, build the dashboard in Excel, providing layout and design tips and tricks, prepare the model to work with fiscal dates, and show values used in many financial reports, including year-to-date, variance-to-target, percentage-of-total, and running totals reports.

Accessibly written, this book offers readers a practical, real-world scenario and can be used as a day-to-day reference. Though the guide focuses on Power Pivot for Excel 2010, a chapter that discusses Power View—compatible with Excel 2013— and Power BI is also included.

1- 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 o...

Table of contents

• Acknowledgments
• Preface
• 1- About This Book
• 2- Introduction to Dashboards and Reports
• 3- Collecting and Preparing the Data
• 4- Building a Dashboard in Excel
• 5- Building Interactive Reports with Excel and Power View
• 6- Sharing Dashboards and Reports Within an Organization
• Bibliography and Suggested Readings