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:
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:
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).