The Absolute Guide to Dashboarding and Reporting with Power BI E-Book

MrExcel's Holy Macro! Books

• Herausgeber: Packt Publishing
• Kategorie: Lebensstil
• Sprache: Englisch

Seitenzahl: 191

Veröffentlichungsjahr: 2024

Beschreibung

This book empowers readers to build professional dashboards and reports using Power BI. Starting with the basics of dashboards and reports, it delves into preparing data for visualizations, crafting detailed reports, and designing cohesive dashboards. The book provides clear methods for sharing work efficiently within organizations, ensuring that readers understand how to convert raw data into actionable insights.
Practical examples and techniques throughout the book equip professionals with the skills they need to enhance their business intelligence capabilities. Readers will discover how to structure data for clarity, develop key reporting techniques, and integrate advanced features to maximize Power BI's potential.
Whether you're a beginner or looking to refine your skills, this book offers a step-by-step approach to mastering Power BI's core capabilities, ensuring a seamless transition from data to decision-making.

Leseprobe

The Absolute Guide to Dashboarding

& Reporting with Power BI

by

Kasper de Jonge

Holy Macro! Books

PO Box 541731, Merritt Island, FL USA

© 2019 Kasper de Jonge

All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information or storage retrieval system without permission from the publisher. Every effort has been made to make this book as complete and accurate as possible, but no warranty or fitness is implied. The information is provided on an “as is” basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book.

Author: Kasper de Jonge

Tech Reviewer: Adam Saxton

Publisher: Bill Jelen

Compositor: Jill Cabot

Cover Design: Alexander Philip

Indexing: Nellie J. Liwam

Published by: Holy Macro! Books, PO Box 541731, Merritt Island, FL 32954 USA

Distributed by: Independent Publishers Group, Chicago, IL

First Printing: February 2019. Printed in USA

ISBN Print 978-1-61547-057-0, Mobi 978-1-61547-140-9, PDF 978-1-61547-240-6, ePub 978-1-61547-363-2

Library of Congress Control Number: 2019930607

Contents

Preface

Acknowledgments

1 - Introduction

2 - Understanding Dashboards and Reports

3 - Collecting and Preparing the Data

4 - Building the Main Report

5 - Building Detailed Reports

6 - Sharing Dashboards and Reports Within an Organization

Index

Preface

The first version of this book focused on using Power Pivot and Excel for dashboarding and reporting. After I finished that book in 2014, I was determined not to write another one: Writing a book is hard . As the popularity of Power BI rose, Bill Jelen nudged me to write a new version of the book, focusing now on using Power BI for dashboarding and reporting, but I put it off. Now, almost four years after Dashboarding and Reporting with Power Pivot and Excel was published, I am nearing completion of Dashboarding and Reporting with Power BI.

What made me decide to finally update the book? Well, frankly, Power BI is now ready for it. Over the past couple of years, we on the Power BI team have created a new product from scratch, and it finally has all the features needed to re-create Excel dashboards and reports—but with Power BI. Another great motivator is the fact that I am still getting great feedback on the first edition of the book.

I have been in and out of the business intelligence industry for the past 15 years. These years have brought fundamental shifts in the way we work. We have gone from full-fledged IT-centric reporting to now enabling business users to work together with IT and also enabling everyone in an organization to work with data.

When I met Power Pivot in 2009, I immediately fell in love. As soon as I installed the first beta of Power Pivot, I knew the business intelligence world that I worked in would change forever. Now that Power BI has come along, it is possible to create insights without being a business intelligence professional; you just need to be familiar with Excel. BI professionals and business users alike are enthusiastic for Power BI.

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 BI community. You can find me at my blog, http://www.kasperonbi.com, and on Twitter, at http://twitter.com/kjonge.

How I Got Started with Power BI

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 the story of 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. When I started going to a school that focused on IT, I actually started paying attention, and my grades finally started going up. Ever since then, I have been glued to computers.

“Working” on my Commodore 64 in 1988. Look at that wallpaper.

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. I was hooked. 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, thanks to BI tools, users were able to get their own profound new insights. 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. That five-day crash course in building multidimensional models was my introduction to Microsoft BI. After that, I largely focused on using cubes and reports to 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.kasperonbi.com.

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 directly to Excel users. This new product would make it possible for anyone to use 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 Gemini (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), an incredibly powerful language that enables users 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 cool new things we could do with it. 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 customers—and I was starting to get traction.

In June 2010, I attended TechEd in New Orleans. Rob Collie and many other folks from the Microsoft product team were there, too. The conference was a frenzy of Power Pivot discussions. It seemed like this was the only thing the entire BI community could talk about. I had many discussions with Rob about Power Pivot, and near the end of TechEd, Rob said, “I’m leaving Microsoft. Why don’t you take my job at Microsoft? I think you would do great.” I was stunned. I’d never thought that was possible and dismissed the idea pretty quickly.

After some talks with my wife, I decided to send Microsoft my resume. A few weeks later, I was interviewing with the team, and about four months later, I worked my first day at Microsoft, helping designing features for Power Pivot for SQL Server 2012. I was able to make a living working on the product I love. Pretty awesome!

Notes and Tips

This book covers a lot of different topics, written as a story about a user named Jim. Throughout the story, I often dive deeply into various subjects, call out certain areas, and give tips. To do this without deviating from the story, I make heavy use of notes as well as tips that fall into four categories:

Data Model TipVisualization TipPower BI TipPower BI Desktop Tip

At the end of the book, I provide an index of all these tips so you can easily find them at any time.

Hyperlinks

Throughout the book, I reference sites and blog posts for further reading, including my blog, Kasper on BI, Microsoft online help, and others. Because hyperlinks can be very long, I have used a URL-shortening tool to create shortened links, such as http://ppivot.us/SEUSO rather than http://www.powerpivotblog.nl/project-gemini-building-models-and-analysing-data-from-excel-memory-based-dimensional-model/. If you are reading a physical copy of this book, make sure you pay attention to the capitalization as you type the URLs because they are case sensitive.

Samples, End Results, and Data Sources

This book describes how to build reports and dashboards based on a Microsoft Access database. Some readers might find it valuable to follow along with the book and build the project themselves, and others might want to see and play with the end result themselves. I have therefore uploaded all files to my website for you to download: http://ppivot.us/sampl32d. All the measures used in this book are available at http://ppivot.us/daxfile.

Acknowledgments

A book is never written alone, and so many people contributed to this book in both small and big ways that it’s nearly impossible to write a complete list. Many users of Power BI both inside and outside Microsoft, bloggers, tweeps, conference attendees, and, of course, the Power BI development team helped me shape the book.

Of course, there are some people I need to especially thank for their help because without them, I wouldn’t even be in the position to write a book. I thank Rob Collie for putting me on this crazy journey in 2009, when we were trying to understand DAX during the Project Gemini timeframe and he urged me to change my life by going to work for the Analysis Services team and moving to Redmond.

I also have to give thanks to the true masters of DAX—Howie Dickerman, Srinivasan Turuvekere, Jeffrey Wang, and Marius Dumitru—for creating DAX in the first place and helping me each time the DAX became too magical. Finally, I thank Adam Saxton for helping out by doing a tech review and getting rid of those pesky bugs.

This book wouldn’t have been possible without the help of Bill Jelen as publisher, Kitty Wilson for editing, and Alexander Philip for the awesome cover design.

Finally, I want to thank my family, Mom and Dad, for getting it all started with that first Commodore 64. And of course I thank my beautiful girls—Anouk, Karlijn, and Merel—for putting up with my crazy passions and allowing me to spend time away from them.

1 - Introduction

This book is a follow-up to my earlier book, Dashboarding and Reporting with Power Pivot and Excel, which covers building reports and dashboards using Power Pivot for Excel. Now, four years later, the book you’re currently reading shows how to build reports and dashboards using Power BI instead. This book is a little different from most books already out there on Power BI. It doesn’t cover all the features of Power BI, nor does it cover the DAX language extensively. Many other books do those things well. A few good examples are Bill Jelen’s PowerPivot for the Data Analyst, Rob Collie’s DAX Formulas for PowerPivot,and Matt Allington’s Super Charge Power BI: Power BI Is Better When You Learn to Write DAX.

This book is intended as a very practical book to help you get started on a Power BI journey and bring your 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 Power BI. The journey starts with Jim finding out what information his organization needs to understand the current rhythm of its business. He then gathers the needed data for presentation in a dashboard, for which he must determine the best ways to visualize the information. As you follow Jim on this journey, you will use Power BI Desktop and DAX formulas to solve several very common business calculations, such as year-to-date revenue, variance-to-target, and year-over-year growth.

You will also watch as Jim creates reports in Power BI Desktop to allow those in his business to dive deeper into the numbers. Then you’ll see how to share those workbooks using Power BI.

In many places, this book dives deeply into subjects such as the Power BI analytical engine, DAX formulas, and Power BI and dashboard design tips and tricks.

What Is Business Intelligence?

Before you get hands-on with Power BI, 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 that are highly curated and complex.

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” for data. 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 those cubes to gain fast access to the data for doing quick analytics on large amounts of data. Then, on top of those cubes, canned reports are created to help users get insights into the data. In the 2000s, Excel improved this situation with the addition of PivotTables, which allow users to 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 cloud-based solutions like Microsoft Dynamics, Salesforce, and Internet of Things devices, as well as, of course, the number-one BI tool in the world: Excel. Users from the business side of an organization—without help from the IT side—can create reports directly at the source. These reports often bypass a BI solution completely or mash up data from the data warehouse with additional data retrieved from sources such as the ones just mentioned. This often leads to clashes between IT and business users 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 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 more and more quickly. BI was traditionally set to create insights through long projects, but that type of system makes it hard to quickly get insights into the ever-changing data. When and after the financial crisis hit in 2007–2009, the business world had to make many cutbacks, especially in the IT space. So, at the same time that IT departments were being expected to provide oversight of and more insights into data, they were being given fewer resources they could use to consolidate larger amounts of data.

But an organization doesn’t need to rely on just its IT department for data. An army of business users in any organization know Excel and also know the data inside out, and they are very proficient at creating reports and using data to gain insights. Microsoft recognized this and thought that perhaps business users and IT could work together to serve the information needs of the organization and use each other’s strengths instead of competing. In 2006, Microsoft began an incubation project called Gemini, named for the constellation. The twins in this project are IT and business users, working together.

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 best-selling 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-serve” 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 had hit the market in 1994, a lot had changed in the IT industry. Importantly, PCs had gotten more powerful, and memory had gotten much cheaper. For the Gemini team, this meant 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 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 have brought a lot of power to the fingertips of many Excel users. As Bill Jelen (also knowns as MrExcel) 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 wanted to bring the power of SSAS to a billion users of Excel—right on their desktops. This is referred to as “personal BI” or “self-service BI.” But project Gemini was meant to be more than an add-in for Excel. It was meant to provide “team BI” so that a workbook shared with team members using SharePoint would retain all the interactivity but could be used by many users at the same time, through a web browser—no Excel required. The idea was that the data in a workbook could be refreshed via an automated schedule so that new data would be added to the workbook with no work needed. Another benefit of sharing workbooks to SharePoint would be that it would allow IT to govern the data shared onto SharePoint.

In October 2009, Gemini was renamed PowerPivot for Excel, and it first shipped with Excel 2010 (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, Microsoft added a space to the tool’s name—Power Pivot (http://ppivot.us/ifdYe). Power Pivot today is still available for Excel 2010, Excel 2013, and Excel 2016. However, in Excel 2016, the name Power Pivot disappeared, and the functionality, which remains the same, became a native part of Excel.

Microsoft’s latest entrant in the BI world,