Learning Microsoft Power BI

Chapter 1. Intro to Power BI

You’re a data person. You understand your data. You know how spreadsheets work. But there’s so much data to process, and your spreadsheets aren’t cutting it.

You need a way to visualize the data and share it with business users so that they can see the analytics, understand the data as you do, and even manipulate the visualizations with little to no training.

If that’s why you’re looking at this book, you made the right move. Microsoft Power BI is exactly what you need. This book will show you how to get up to speed quickly—so quickly that you’ll be building and publishing reports that will wow your colleagues and make your mom proud.

Microsoft Power BI is a data analytics and visualization tool powerful enough for the most demanding data scientists but accessible enough for everyday use by anyone needing to get more from their data.

In the beginning, back when life was simpler (in 2011), Power BI was just a simple piece of desktop software. But it isn’t anymore. It’s an entire business intelligence ecosystem that can fit into multiple diverse technology stacks.

This chapter introduces Microsoft Power BI, discusses the entire Power BI family of products, provides an overview of how Power BI works, and looks at what distinguishes it from other similar tools. By the end of this chapter, you’ll:

• Know what components fit in the Power BI ecosystem and why they’re important.

• Learn the history of Microsoft’s business intelligence work to learn how that got us to Power BI.

• Discover what makes Power BI different from its competitors.

What Is Power BI?

Power BI is both a piece of software and a larger ecosystem of products. Usually when people throw out the term “Power BI,” it’s in reference to the desktop authorship software. However, when discussing how most people will (visually) share the fruits of their work with others, it’s done in the context of the Power BI service, a software-as-a-service (SaaS) solution that hosts Power BI datasets and reports that can be used by others who have access.

Even beyond these two features, a wide variety of products in the family allow you to embed reports into websites and other applications, view reports on your mobile device, and even have your own version of the SaaS solution on premises.

This book focuses on Power BI Desktop and the Power BI service since they are your most basic and valuable building blocks.

Power BI Desktop is a tool for data investigation and visualization. Analysts can take data and create interactive reports that enable end users to garner insights that were previously buried. In finance, you might use Power BI to automate the generation of profit and loss (P&L) statements or analyze costs over time. In construction, you could use Power BI to identify variances in times to complete projects based on team composition or geographical factors. In retail, you might identify which of your products are the most successful, while pinpointing which ones might be on the cusp of taking off if given a bit more of a push via a what-if analysis.

According to Microsoft at the 2021 Business Applications Summit, 97% of the Fortune 500 uses Power BI in some capacity. That means it’s a technology you can trust putting your time and effort into, especially if you’re looking for the kind of insight that transforms your enterprise. Or in my case, it’s the excuse to build a Pokédex for my daughter. Sometimes you just really want to be the best, like no one ever was.

Power BI Components

Power BI today consists of a wide variety of products that allow users to create and consume reports from your data. According to Microsoft (at the time of publishing), here are all the components that make up the Power BI family of products:

• Power BI Desktop

• Power BI service

• Power BI Mobile

• Power BI Report Builder

• Power BI Report Server on premises

• Power BI Embedded

There is much to unpack in these products, but the main focus of this book is on the first two components. We’ll spend most of our time learning Power BI Desktop because that’s the foundation you need; it’s what the whole ecosystem is built around. Then we’ll discuss the Power BI service in more detail toward the end because you’re going to need that knowledge to publish (and then share) your amazing work that’ll make you the envy of your department.

With that in mind, a quick overview of these components will be useful in the future, so here they are:

Power BI Desktop

A free application you install on a local computer that you can use to connect to, transform, and visualize data. This is the building block for all the other portions of the Power BI ecosystem.

Power BI service

An online SaaS solution that lets end users share reports created in Power BI Desktop or Power BI Report Builder with users across an organization. (In case you’re wondering, the “s” in “service” is lowercase on purpose; that’s just how Microsoft named it.)

Power BI Mobile

A set of applications for Windows, iOS, and Android that allows end users to view reports in the Power BI service from their mobile devices without having to use a web browser.

Power BI Report Builder

A free application you install on a local computer that you use to generate pixel-perfect paginated reports in the same form as SQL Server Reporting Services. For example, if you want to build something to automate invoice generation or create long lists of data for distribution, you could do that here.

Power BI Report Server on premises

If, for security reasons, you cannot publish reports to the Power BI service, your IT team may put a version of that software on an internal server behind the company firewall using on-premises computing resources, as opposed to cloud resources. Power BI Report Server is not always in feature parity with the Power BI service. That’s because Report Server is updated only three times a year (January, May, and September). It’s also worth noting that if you are going to deploy reports to Report Server on prem, you will need to use a special version of Power BI Desktop that is in alignment with the version of Report Server installed.

Power BI Embedded

Allows you to integrate Power BI reports and visuals into applications or websites. This has its own pricing and licensing structure.

Now that the whole family has been introduced, we’ll shift focus to the two components pertinent to this book, Power BI Desktop and Power BI service.

Power BI Desktop

Power BI Desktop is software that allows you to connect, transform, and visualize data. Let’s dig into some details. Power BI Desktop comprises its own components. The two that are most important to a Power BI beginner are the Power BI canvas and Power Query, so those are the two we’re going to focus on. Essentially, this is where you’ll spend the most time in your Power BI Desktop work.

The Power BI canvas is the place where you build visualizations. Think of the canvas as a PowerPoint slide for your data. Here you’ll use drag-and-drop functionality to pull information into different visualizations to explore your data and garner insights. This is also where you’ll apply formatting to visuals, add images and text boxes, and more.

Power Query is used to import and manipulate data, essentially shaping it. In Power BI, unlike Excel, for example, you do not edit cells of data; you manipulate columns of data by using its functions, wizards, and formulas. Power Query provides options for creating custom columns based on rules you design. It lets you combine multiple tables of data or add values from one table to another.

Everything in Power Query first begins with getting data from your sources, and Power Query supports a huge number of data sources. You want to connect to a database? SQL? Oracle? Teradata? Power Query has you covered. You want to connect to an Excel workbook to get a table? No problem. Comma-separated values (CSV)? Easy. Cloud sources? Also not a problem.

Microsoft has gone out of its way to create new connectors to data sources to show that Power BI is not just to be used with other Microsoft products but wherever your data lives. If you become sufficiently talented at M (the programming language of Power Query), you can even, in theory, create your own custom data connectors to data sources that aren’t officially supported. Just note that this book isn’t going to discuss M or advanced Data Analysis Expressions (DAX) topics or actual programming. We’re here to help you as a Power BI beginner, and you’ll do just fine without those.

The Power BI Service

Now we get to the good stuff that’s going to move you from an ordinary person who just produces reports to a celebrity whose reports draw people from far and wide. The Power BI service, the online SaaS solution, allows users to share their reports from Power BI Desktop with other users in their organization.

Everyone has access to their own personal workspace for free. You get one personal workspace that is pregenerated when you log in for the first time, and it’s like a private development space in the larger Power BI service environment. You technically can share things from this personal workspace, but it’s not a best practice to do so, and anyone you share it with would still need the appropriate licensing to view it.

The right way to share reports with other users is to create a new workspace and invite them to that workspace. To be eligible to be invited to a workspace, a user must have a Power BI Pro license, or your organization must be using Power BI Premium dedicated capacity to share reports with users who do not have Power BI Pro licenses.

The Power BI service lets other end users explore reports you’ve created to get insights from your work. This exploration can take the form of dashboards of curated visuals you put together. Or it can be access to a report you’ve created with all its pages. Or it can even be the ability to ask natural language questions using the Q&A feature to get insights from the data.

The Power BI service also includes several other features, such as the ability to create special objects known as dataflows. These dataflows can be used to get information in the Power BI service outside of a database, while allowing end users to access that data and combine it with other data inside a Power BI Desktop model.

Developers can manage deployment pipelines for workspaces in the Power BI service, which lets you create and manage the development, test, and production workspaces. Deployment pipelines enable ongoing development work on Power BI projects, without impacting the user experience for items already being used by end users.

A new feature in the Power BI service gives users the ability to create goals. The goals are tracked using data in the Power BI service. Information on the goals can then be shared with appropriate users for quick, actionable insight.

In sum, the Power BI service is the critical glue that makes Power BI different from, say, simply sharing an Excel workbook around the office. It creates a shared space enabling people to see the same insights securely, while inviting them to explore shared data elements that can be curated for meeting the specific needs of each end user.

The Power Platform

Now let’s take a step back and look at the big picture: what are the “Power” products within the Microsoft family? The Power Platform is a larger compilation of low- or no-code products that support one another, with Power BI as just one component. While we won’t train you on these other items, it’s good to know what else is out there in case you develop a need to integrate one of the products into your Power BI reports in the future:

Power Apps

A low- or no-code development environment where you can develop your own applications to solve different business challenges

Power Automate

A framework that allows end users to create “flows” that automate organizational processes

Power Virtual Agents

A no-code tool that lets you build chatbots to engage with customers and employees

Each of the components can be used by Power BI to create insights to help push your work forward. Let’s go through some examples of how each piece could work with Power BI.

In Power Apps, for instance, you could have an application that would allow a site inspector to take notes and upload that data to a SQL Server database. A Power BI report could also be connected to that SQL Server database, download that information that was uploaded by the Power App, and update the report based on the new data being added by the numerous inspectors in the field using Power Apps.

Let’s say your boss, for whatever reason, wants to see a static version of a report every day. Well, you could manually go into the Power BI service and create an export, download it, write up an email, and click Send. Instead, a more efficient option would be to use Power Automate to create a flow that would automate the task for you, ensuring that at 8 a.m. sharp every day there’s a nice PDF in your boss’s inbox with the most up-to-date version of your Power BI report. If that doesn’t get you points, I don’t know what will.

When it comes to virtual agents (software that provides customer service to humans, mimicking a customer service representative), a large amount of data is collected in the process whenever end users interact with your chatbots. All that data is collected and stored, which means Power BI can generate reports about it. This creates an end-to-end reporting solution that allows your organization to get textual insights into what your consumers are really looking for from your organization. The end users can work with and see the actual data.

How Did We Get to Power BI?

Microsoft’s history in business intelligence is long and storied. In many ways, Power BI is the most recent (and maybe final) chapter, representing the culmination of business intelligence capabilities developed in a series of components Microsoft built throughout the years. For you to get the most out of this product, it’s worth discussing how Microsoft’s business intelligence stack got to Power BI and what that journey means for you as an end user.

This section will provide you with valuable context: why was Power BI developed, why is it important, and what products are interrelated? Knowing this up front will help you the same way it helps when you do research about a company before you walk into a job interview. The clarity you gain will serve you well in the future.

SQL Server: Microsoft’s Relational Database

In 1989, Microsoft released its first relational database in the form of SQL Server for OS/2. A database is a piece of software that contains and organizes large portions of data for different uses. While SQL Server was the first step (and a necessary one) for Microsoft to move into business intelligence, a database alone isn’t sufficient to provide business intelligence.

SQL Server Analysis Services Multidimensional: One Small Step into BI

As processing power grew, new methods to process data became popular—for example, data cubes. In 1998, Microsoft released its first online analytical processing (OLAP) engine and called it OLAP Services, which would eventually become SQL Server Analysis Services. OLAP Services is a fancy way to say a cube-based way to interact with data for analysis. The cube approach dominated many enterprise BI environments for well over a decade.

SQL Server Reporting Services: Pixel-Perfect Reporting, Automated Reports, and More

Microsoft eventually needed to add a pixel-perfect reporting option to SQL Server. This was required because, as data use cases grew, the need to create reusable assets to an exact specification grew as well. For example, you want to make sure that every invoice you print is in exactly the same format every time.

In 2004, Microsoft released SQL Server Reporting Services as an add-on to SQL Server 2000, with its second version being released alongside SQL Server 2005. SQL Server Reporting Services had several features that were useful in an enterprise deployment, including pixel-perfect report generation, automated report distribution, and, in many deployments, the ability for end users to generate queries to the backend SQL Server database through a user interface.

Excel: A Self-Service BI Tool

Every piece of software mentioned so far is what we would define as enterprise business intelligence tools. These expensive tools required large teams to manage and deploy them.

If enterprise business intelligence is defined by its large deployments and high levels of investment, self-service business intelligence is the ability to use and manipulate data in such a way that you empower the end user to explore and analyze the data they have.

Microsoft’s history in self-service business intelligence comes down to one core product that almost everyone has seen or touched once: Microsoft Excel. The first version of Excel came out for the Macintosh in 1985. At its core, Excel is a product that allows you to take data, pull it into a “flat” extract, and manipulate it or make impromptu calculations on it as needed. Excel empowered end users to take their data and get insights out of it. That’s the premise of self-service business intelligence.

Power Pivot

In 2010 Microsoft released PowerPivot. PowerPivot was later renamed to add a space so it was two words, thereby matching the other product names in this new Power BI suite of tools. Originally an add-on for Excel, Power Pivot let end users get information from a myriad of sources and store that information in a relational OLAP (ROLAP) model inside the workbook. Power Pivot also shipped with Power Query. Power Query is an in-engine extract, transform, and load (ETL) tool that allows for data manipulation using the M language.

Important New Functionality That Leads to Power BI

Around this time, we began to see enterprise and self-service business intelligence start to flow together. In SQL Server 2012, Microsoft released a new feature with Analysis Services called the tabular model. Analysis Services could now support a method of data organization more like that of a classic data warehouse, as opposed to a cube structure that becomes increasingly difficult to manage over time and tends to be more confusing for end users. The difference was that to get performance gains in this tabular model, Microsoft developed its first columnar (column-based) data store technology. Eventually, this would become what we know as VertiPaq today, the in-memory columnar data store Analysis Services tabular model. So basically, with these improvements, performance became really fast.

Alongside this process, a new formula language called DAX was developed to support these tabular models that allowed for calculations across those columns of data to help make that data actionable.

The next version of Power Pivot released with Excel 2013 used this engine as the base for its work.

Power BI Desktop Is Born

On July 24, 2015, the first generally available version of Power BI Desktop was released to the world. Inside Power BI Desktop was an entire enterprise-level semantic (designed to be understood by people) modeling tool with the VertiPaq engine and the DAX formula language. It used Power Query to get information from a wide variety of sources and pull it into the engine, and it allowed for transformations that could shape that data for future analysis.

Figure 1-1 shows the timeline of the Microsoft business intelligence tracks and how they converge, highlighting some of the milestones over the last 30 years in both enterprise and self-service business intelligence.

No one will quiz you on this history, but hopefully it has given you the perspective to understand how we got here. I mean, sure…not all of us keep history books on Microsoft on our bookshelves, but not all of us have secret shrines to Satya Nadella either. It’s a life choice.

Figure 1-1. This Power BI timeline shows the evolution of business intelligence from Excel to present-day Power BI Desktop

Power BI Desktop Under the Hood

Power BI Desktop works because under the hood it has two powerful engines. These are what make the whole thing work on a technical level. There’s the formula engine, which takes data requests, processes them, and generates a query plan for execution. Then there’s the storage engine, which stores the data of the data model and pulls the data requested by the formula engine to satisfy a query’s demand.

Another way to look at it is to think of the formula engine as the brain. It figures out the best way to approach a problem and sends the appropriate work order to the right parts of the body to get it done. The storage engine is the body that receives those commands and does the work of getting all the data together.

VertiPaq: The Storage Engine

Let’s meet the storage engine of SQL Server Analysis Services tabular models, called VertiPaq. This enterprise-level semantic modeling tool is included with every copy of Power BI Desktop. When you pull data into Power BI Desktop, the VertiPaq engine reads the data source post-transformation and puts the data into a columnar structure. This division of the data allows for faster queries via selective column selection and data compression as entire columns get compressed. This compression of the data significantly cuts the file size compared to what it would be otherwise. It then puts the entirety of the data model in local memory. This view can be refreshed from the original data sources.

Now, before you get excited and run out to celebrate, know that this data storage engine comes with a significant hurdle for users coming from, say, Excel. You cannot modify individual cells of data. As the data is converted into columns for storage and indexed and then compressed, the data inside the model becomes effectively immutable. You can add calculated columns and measures, but the underlying data doesn’t change. If you want to change the data, you must either go back to the transformation step of the data (say, in Power Query) or go back to the data source and make your edits there, and then refresh your data.

DAX: The Formula Engine

Also, let’s discuss the formula engine and its language, DAX. DAX is a formula language used in Analysis Services Tabular, Power BI, and Power Pivot. When you want to access the data in your data model, DAX is how it’s done. This is done in the same way as someone would write SQL to get data from a database. Power BI users will most commonly use DAX to create measures and calculated columns. The wonderful thing about Power BI is that for simple drag-and-drop functionality, or when visuals get created, Power BI generates the DAX for you and passes it to the internal engine to have its query plan generated and executed.

Nothing you do in Power BI is done without DAX. You may not see it, but it’s always there, playing the pivotal role of figuring out how best to get the information from your data model to satisfy your request.

What Makes Power BI Different from Its Competitors?

Honestly, there has never been a better time to be a data analyst than today. Many of the tools in the marketplace have a variety of strengths and weaknesses, and Power BI is no exception. In fact, just to see how many competitors there are in this marketplace, let’s take a quick look at Figure 1-2, which shows Gartner’s Magic Quadrant for Analytics and Business Intelligence Platforms for 2021.

Now, whatever you think about the position of the competitors on the analysis, the sheer number of them can be enough to make your head spin. Each competitor has a reason they’re in the market today. Notice, though, that the leader’s quadrant contains only three players: Qlik, Tableau, and Microsoft.

Figure 1-2. Gartner’s Magic Quadrant for Analytics and Business Intelligence Platforms

The real source of differentiation between Microsoft and its competitors in this space is the ability to execute on its plan for its software. Microsoft has a more than 30-year history in business intelligence, and SQL Server is itself now over 30 years old. Microsoft has been in this game a very long time and has the highest number of supporting technologies to its business intelligence platform, as compared to the others.

All these major competitors offer products that allow you to take data and turn it into great data visualizations that help you learn something you didn’t know from your data. I will forever be jealous of Tableau’s capability to click and drag for groupings, for instance.

Regardless of Tableau’s dazzle, Power BI offers a tool for data ingestion that is unequaled in terms of ease of use for nontechnical resources in Power Query. It also has one of the strongest, if not the strongest, analysis engines on the planet today in the form of Analysis Services Tabular. These tools have accelerated the rate of data democratization inside many organizations. Power BI has created citizen data analysts around the world who use data to do transformative work. Indeed, by putting in the effort to read and digest this book, you’re taking the steps necessary to join that community!

Here are some examples. The world’s leading conservation organization, the World Wide Fund for Nature, uses Power BI to share impact effects with donors. Engineers at Cummins use Power BI to do advanced capacity planning to get engines out the door more quickly. Humana leverages Power BI to centralize and visualize data against more than 45 unique data sources across its enterprise, using Power BI as a consolidation platform for end users. King’s College London uses Power BI’s artificial intelligence (AI) visuals to identify key factors that could indicate shifts in student performance, allowing for targeted outreach to maximize the opportunities for student success. These are just some of the varied use cases happening today on this platform.

Power BI has decades of Analysis Services experience behind it, with a frontend that can now match its promise. In addition, Microsoft releases updates for Power BI Desktop every single month with new features, connectors, and visualizations. Microsoft is committed to the Power Platform, and it’s safe to say Microsoft will be here for the long haul. When 97% of the Fortune 500 agree on something, there’s probably a good reason.

Conclusion

Power BI at its core is more than just a desktop authorship tool. It’s an entire platform that Microsoft has been working toward for the better part of three decades. It has the unique strength of having two of the most enterprise-tested analysis engines in the world, VertiPaq and DAX. It also has a great tool to allow nontechnical users to get disparate data together and begin real analysis on that data with Power Query.

Power BI Desktop is now an enterprise-level solution that is used by the world’s largest companies, nonprofits, and even small businesses to help get insights from their data that would have previously been impossible.

With an understanding of what Power BI is, we are ready to finally open the software with a clear vision about what we’ll do with it. That begins with the Report view, so get a soda, pet your dog, and let’s dive in. This next chapter will cover the user interface and how to use it.