Read an Excerpt
CHAPTER 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.
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, Power BI, was released in 2015. Built on the success of Power Pivot, Power BI has truly revolutionized the business intelligence world. Whereas business users were the primary audience for Power Pivot, Power BI is accessible enough that all users in an organization can use it to make sense out of all the data at their disposal. Many businesses no longer just rely on data sources locked away on premises but also use cloud-based data solutions (for example, Dynamics 365, Salesforce, Marketo, Google Analytics). These cloud solutions allow users to run their marketing or sales businesses online, and they enable users to get started with a few clicks, without having to set up and maintain servers within their organizations. Cloud solutions contain troves of interesting information relevant for business intelligence, but that information is often not readily accessible to the data warehouse.
Power BI has two main components:
• PowerBI.com: This is the main platform that allows users to collaborate on data and create a dashboard (often called a single pane of glass) from all the data that's important in a given scenario. Business users can easily sign in to Power BI themselves, with just their corporate email addresses, and then connect to their cloud solutions directly by entering their usernames and passwords. They can then find not just out-of-the-box content from cloud solutions but also content created by their own corporate business intelligence teams or business users. Power BI also allows business users to collaborate with data analysts and traditional BI teams in a seamless and frictionless manner.
• Power BI Desktop: Data analysts and authors can use an interactive analytical desktop product called Power BI Desktop. This dedicated report authoring tool enables users to transform data, create powerful reports and visualizations, and easily publish to Power BI. Power BI Desktop is available for free for anyone (http://ppivot.us/tes3ew). It contains the same engine that is used for Power Pivot and SQL Server Analysis Services, so most of what you know about Power Pivot can be applied with Power BI Desktop. Where Power Pivot and Power BI Desktop diverge is in how they allow you to visualize the data. Power Pivot is aimed at visualizing in Excel, with traditional PivotTables and PivotCharts, whereas Power BI Desktop has a visualization stack that is aimed at interactive data exploration and more free-form data visualization. To load data, Power BI Desktop uses functionality called Power Query, which allows business users to connect to 100+ data sources and mash up and cleanse data in a very user-friendly manner.
To keep up with the ever-changing industry, Power BI is always moving, and both PowerBI.com and Power BI Desktop are updated every month. This means users get new features and functionality every month of the year. It also means that this book is already outdated even as I am writing it. However, even if some of the tools and techniques covered in this book look slightly different in your version of Power BI, the concepts should still be applicable.
In 2012 the SSAS team released the SSAS tabular model, a version of Power BI Desktop that does not run inside Power BI but runs on a server or as part of Power BI Premium and Azure. The SSAS tabular model is developed using Microsoft Visual Studio. Most of its features and functionality are identical to those in Power BI, but the SSAS tabular model has some additional features that allow for working with larger amounts of data as it is designed for larger-scale enterprise projects.
This book focuses on Power BI, but many of the modeling tips and tricks can be applied to the SSAS tabular model as well. For in-depth information on the tabular model, see Tabular Modeling in Microsoft SQL Server Analysis Services by Marco Russo and Alberto Ferrari (http://ppivot.us/fd3sg4).
CHAPTER 2
Understanding Dashboards and Reports
In this book, you will learn how to use Power BI to find insights from data. Before you can do that, though, you need to understand some basics. The main goal of building anything in Power BI is to display information from one or several "raw" data sources, either for your own use or to report the information to someone else. When you work with data for yourself, you don't have to think very hard about what it means because it makes sense to you. But when you want to show data to someone else, you have to think a little harder because you need to determine the reasons users are requesting information and what their goals are. You have to think about how to communicate the data so users can easily understand it.
When communicating insights about data, it's important to think about how to show and visualize the relevant information in an efficient way. Before you display a bunch of tables and charts, you need to think about why you would use them. You need to consider whether to place one chart adjacent to another chart. Most people don't think about this. This book looks at some examples and investigates how to visualize information in an effective way, following some basic principles.
To determine how to display some particular information, you need to think about the reason someone wants you to show that information. The answer will determine how you shape the data, which you are likely to do in a report. The business intelligence world uses the term report to describe a mechanism for sharing information with users. The Bing dictionary tells a similar story for the noun report: "an artifact that tells about what happened."
In general, there are three types of reports in business intelligence: dashboards, static reports, and interactive reports. Sometimes you need to use only one type of report, but often the various types work together and even complement each other. Let's look at each type in turn. Then, later in this book, you will be ready to learn how to use Power BI to build dashboards and interactive reports.
Dashboard is a very loaded term in business intelligence that is often seen as being synonymous with BI. People seem to want or think they need dashboards without knowing what they really are or why they need them. But everyone seems to agree that dashboards look sexy and are cool to have. A dashboard can indeed show all the information you need in a consolidated, simple, intuitive, clear, and car-like display.
Unfortunately, dashboards are typically hotspots of flashy charts, traffic lights, and gauges that fail to deliver on the promise of information at a glance. The primary goal of a dashboard should be to deliver the right information in an insightful way. A dashboard should enable someone to spot the information needed at a glance. It's something a user looks at every day or even multiple times a day to see the current rhythm of the business and detect the areas that need immediate attention. Usually a dashboard contains information from multiple areas. For example, it might contain sales, the number of new customers, and employee retention — all in a single pane of glass.
(Continues…)
Excerpted from "The Absolute Guide to Dashboarding and Reporting with Power BI"
by .
Copyright © 2019 Kasper de Jonge.
Excerpted by permission of Holy Macro! Books.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.
