Learn Microsoft Power BI E-Book

Greg Deckler

• Herausgeber: Packt Publishing
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Veröffentlichungsjahr: 2025

Beschreibung

Everyone’s swimming in data. The ones who thrive? They know how to make sense of it with the right tools quickly and effectively. Learn Microsoft Power BI, Third Edition is your essential guide to mastering one of the most powerful platforms for data analysis and visualization. In this newly revised and expanded edition, Greg Deckler, a 7-time Microsoft MVP and Power BI expert, takes you on a journey of data exploration and discovery using Microsoft Power BI to ingest, cleanse, and organize data and uncover key business insights that can be shared effectively.
Covering the latest features and interface changes, including Microsoft Fabric and Copilot, this book guides you through deploying, adopting, and governing Power BI within your organization. You’ll get to grips with the fundamentals of business intelligence projects and learn how to leverage your expertise in the broader Power BI ecosystem. From ingesting and cleansing your data to transforming it into stunning visualizations, reports, and dashboards that speak to business decision-makers, this book will fully prepare you to become the data analysis hero of your organization or kickstart a rewarding career in business intelligence.

Leseprobe

Learn Microsoft Power BI

Third Edition

A comprehensive, beginner-friendly guide to real-world business intelligence

Greg Deckler

Learn Microsoft Power BI

Third Edition

Copyright © 2025 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing or its dealers and distributors, will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

Portfolio Director: Sunith Shetty

Relationship Lead: Apeksha Shetty

Project Manager: Shashank Desai

Content Engineer: Tiksha Abhimanyu Lad

Technical Editor: Seemanjay Ameriya

Copy Editor: Safis Editing

Indexer: Manju Arasan

Proofreader: Tiksha Abhimanyu Lad

Production Designer: Pranit Padwal

Growth Lead: Bhavesh Amin

First published: September 2019

Second edition: February 2022

Third edition: August 2025

Production reference: 1040825

Published by Packt Publishing Ltd.

Grosvenor House

11 St Paul’s Square

Birmingham

B3 1RB, UK.

ISBN 978-1-83664-741-6

www.packtpub.com

I would like to dedicate this book to my son Rocket who graduated from high school on June 7th, 2025

– Greg Deckler

Editorial Reviews

The third edition of Learn Power BI provides an updated step-by-step guide to utilizing primary features in data transformation, semantic modeling and analysis, interactive data visualization, and content distribution. Additionally, valuable project planning and data governance considerations are reviewed, including user and capacity-based licensing, row-level security roles, and alternative models for deploying Power BI for an organization.

– Brett Powell, author of Mastering Microsoft Power BI and Microsoft Power BI Cookbook 

There’s something powerful about a tool that doesn’t just visualize data, but transforms how an organization thinks. Power BI is one of those tools. And this book? It’s not just a technical guide. It’s a structured blueprint for elevating decision-making at every level of a business, from the frontline analyst to the C-suite strategist.

When I started Onyx Data, my mission was clear: to help people unlock the value of their data. But too often, I’d see brilliant individuals overwhelmed by dashboards that confused more than clarified, or semantic models that hid insights behind complexity. That’s why this book resonates so deeply. It doesn’t just teach Power BI, it teaches business intelligence the right way, by starting with the why, and guiding you through the how with clarity and intent.

Across the book, Greg shows you what makes great BI work:

A commitment to business-first thinking (Chapter 1)A disciplined approach to project planning and stakeholder alignment (Chapter 2)A mastery of building reliable semantic models that scale across departments (Chapters 4–5)

This book doesn’t shortcut the fundamentals. It walks you through transforming raw data into decision-ready assets, all while keeping the end-user experience front and center. From shaping data in Power Query to publishing secure reports to Microsoft Fabric, Greg strikes the right balance between precision and practicality.

What I also appreciate is how it speaks to the real-world scenarios we face every day, tailoring reports for execs, defining row-level security, optimizing licensing decisions, and ultimately, creating tools that people actually use.

If you’re here to learn Power BI to get ahead in your role, modernize your organization, or build solutions that stand the test of scale, you’re in the right place. My advice? Don’t just follow the clicks, understand the thinking. Because when data professionals combine tooling with strategic intent, the results aren’t just visual, they’re transformative.

Let this book be your guide. And then go build something remarkable.

– Leon Gordon, Microsoft MVP, Forbes Tech Council, Gartner Ambassador, LinkedIn Top Voice, and CEO of Onyx Data

Reading Greg’s books is always an entertaining experience. He often provides deep, sometimes unconventional, and thought-provoking insights and methods for working with Power BI. Hence, I was excited to read the third edition of his extremely popular book, Learn Microsoft Power BI. This book doesn’t disappoint in that regard. Learn Microsoft Power BI takes you on a thoughtfully sequenced journey – from grasping general business intelligence principles to deploying enterprise-grade solutions using Microsoft Power BI. The opening chapters demystify high-level BI strategy and project planning before diving into hands-on work with Power BI Desktop. By the time you reach the report-building and sharing chapters, you’ve already mastered the under-the-hood mechanics, ensuring that dashboards aren’t just pretty but also performant and reliable.

– Nikola Ilic, founder of Data Mozart, Microsoft Data Platform MVP

The book is written well, and the copy and tech editing are handled well. I was particularly impressed to see that Art Tennick was a tech reviewer. I regularly mention Art’s books. The book is targeted at beginners and starts by describing why on Earth you might be using these tools in the first place. The book has a good discussion on how to use the various transformations in Power Query and achieve good visualizations in reports. Finally, I liked the discussion on career paths and so on at the end of the book. I’ve not seen that mentioned in other books, yet it’s certainly useful for beginners. Overall, I really enjoyed this book. It’s exactly the right length and provides a good coverage of the topic. Great work, Greg!

– Dr. Greg Low, Founder and Principal Mentor at SQL Down Under, long-term Microsoft MVP and RD

Contributors

About the author

Greg Deckler is a seven-time Microsoft MVP for Data Platform and an active member of the Columbus Ohio IT community, having founded the Columbus Azure ML and Power BI User Group (CAMLPUG) and presented at many conferences and events throughout the country. An active blogger and community member interested in helping new users of Power BI, Greg actively participates in the Power BI community, having authored over 200 Power BI Quick Measures Gallery submissions and over 7,500 authored solutions to community questions. Greg is also vice president at a global consulting firm.

My sincere thanks to the entire Power BI community for your support through the years. In addition, I want to thank the entire team at Packt for giving me the opportunity to write books.

About the reviewers

Art Tennick is the author of 20 computer books and over 2,000 magazine, blog, and LinkedIn articles. He wrote one of the first books on DAX way back in 2009. He is an independent freelance consultant on Power BI, Analysis Services, Fabric, Python, R, and SQL Server. This is the fourth book for Packt for which he has been a technical reviewer. You can find him on LinkedIn.

Thank you, Rita and Emma, as always.

Peter ter Braake started working in IT after graduating in Physics from the University of Utrecht in 1993. After a few years, data became his primary focus. In 2002, Peter became a Microsoft Certified Trainer and has since divided his time between teaching and consultancy. This has led to extensive experience across a wide range of companies and industries.

Join our community on Discord

Join our community’s Discord space for discussions with the authors and other readers: https://discord.gg/hvqvgyGH

Contents

Editorial Reviews

Preface

Who this book is for

What this book covers

To get the most out of this book

Get in touch

Your Book Comes with Exclusive Perks - Here’s How to Unlock Them

Unlock Your Book’s Exclusive Benefits

How to unlock these benefits in three easy steps

Step 1

Step 2

Step 3

Need help?

Part 1: The Basics

Understanding Business Intelligence and Power BI

Exploring key concepts of business intelligence

Domain

Data

Internal and external data

Structured, unstructured, and semi-structured data

Model

Transforming and cleansing

Defining and categorizing

Organizing

Analysis

Visualization

Discovering the Power BI ecosystem

Core and Power BI-specific

Core and non-Power BI-specific

Non-core and Power BI-specific

Natively integrated Microsoft technologies

The extended Power BI ecosystem

Choosing the right license

Shared capacity

Free

Pro

Dedicated capacity

Microsoft Fabric

Premium Per User

Embedded

Power BI Report Server

Introducing Power BI Desktop and the Power BI service

Power BI Desktop

The Power BI service

Summary

Questions

Further reading

Planning Projects with Power BI

Planning business intelligence projects

Identifying stakeholders, goals, and requirements

Stakeholders

Goals

Requirements

Procuring the required resources

Domain experts

Data modelers

Report authors

Administrators

Defining the data sources

Designing a data model

Identifying facts and dimensions

Creating a bus matrix

Determining a model mode

Planning reports and dashboards

Example scenario: utilization reporting

Identifying stakeholders, goals, and requirements

Procuring the required resources

Defining the data sources

Designing a model

Planning reports and dashboards

Summary

Questions

Further reading

Part 2: The Desktop

Up and Running with Power BI Desktop

Technical requirements

Downloading and running Power BI Desktop

Downloading Power BI Desktop

Power BI Desktop (the trusted Microsoft Store app)

Power BI Desktop (EXE)

Power BI Desktop (Report Server edition)

Running Power BI Desktop

Touring the desktop

Header

Views

Panes

Canvas

Wallpaper

Page and Layout Controls

Footer

Ribbon

The Formula Bar

Introducing DAX

Generating data

Creating a calculated table

Creating calculated columns

Formatting columns

Creating visualizations

Creating your first visualization

Formatting your visualization

Adding analytics to your visualization

Creating and using a slicer

Creating more visualizations

Editing visual interactions

Summary

Questions

Further reading

Connecting to and Transforming Data

Technical requirements

Getting data

Creating your first query

Getting additional data

Transforming data

Touring Power Query Editor

Header

Ribbon

Formula Bar

Queries Pane

Data Canvas

Query Settings

Footer

Transforming budget and forecast data

Cleaning up extraneous bottom rows

Filtering rows

Unpivoting data

Using Fill

Changing data types

Transforming People, Tasks, and January data

Transforming the People query

Transforming the Tasks query

Transforming the January query

Merging, copying, and appending queries

Merging queries

Expanding tables

Disabling queries from being loaded

Duplicating queries

Changing sources

Appending queries

Verifying and loading data

Organizing queries

Checking column quality, distribution, and profiles

Loading the data

Summary

Questions

Further reading

Creating Semantic Models and Calculations

Technical requirements

Creating a semantic model

Touring the Model view

Modifying the layout

Creating and understanding relationships

Exploring the semantic model

Creating calculations

Calculated columns

Understanding the context for calculated columns

Creating calculated columns for utilization

Using calculated columns to resolve data granularity issues

Measures

Understanding the context for measurements

Creating measures for utilization

Using visual calculations

Checking and troubleshooting calculations

Boundary cases

Slicing

Grouping

Summary

Questions

Further reading

Unlocking Insights

Technical requirements

Segmenting data

Creating groups

Creating hierarchies

Exploring hierarchies

Understanding Row Level Security

Creating roles

Testing roles

Using report navigation features

Drill through

Using drill through

Buttons

Types of buttons

Page and bookmark navigator buttons

Button actions

Button states

Bookmarks

Creating and using bookmarks

Advanced bookmarks

Questions and answers (Q&A)

Best practices for Q&A

Using a Q&A button

Using Q&A in report authoring

Synonyms

Advanced analysis techniques

The Analyze and Summarize features

Top-N filtering

Gauges and KPIs

What-if parameters

Conditional formatting

Quick measures

Report tooltip pages

Creating a report tooltip page

Using a report tooltip page

Key influencers

Summary

Questions

Further reading

Creating the Final Report

Technical requirements

Preparing the final report

Planning the final report

Cleaning up

Using a theme

Creating a page template

Using Sync slicers

Adjusting the calendar

Adding report filters

Creating the final report pages

Creating the Executive Summary page

Creating the Division Management page

Creating the Branch Management page

Creating the Hours Detail page

Creating the Employee Details page

Creating the Introduction page

Finishing up

Creating mobile views

Testing

Cleaning up

Summary

Questions

Further reading

Part 3: The Service

Publishing and Sharing

Technical requirements

Creating an account

Microsoft 365

Microsoft Fabric trial

Introducing the service

Touring the service

Header

Preferences

Resources and extensions

Governance and insights

Navigation pane

Canvas

Publishing and sharing

Creating a workspace

Publishing

What happens when you publish?

Sharing

Summary

Questions

Further reading

Using Reports in the Power BI Service

Technical requirements

Viewing and using reports

File menu

Save a copy

Download this file

Manage permissions

Print this page

Embed report

Generate a QR code

Settings

Export menu

Analyze in Excel

PowerPoint

PDF

Share

Teams

Explore

Subscribe to report

Managing subscriptions

Set alert

Edit

Ellipsis (…)

Reset

Bookmark

Report bookmarks

Personal bookmarks

Persistent filters

View

Refresh visuals

Comment

Add to Favorites

Editing and creating reports

Editing reports

Creating a mobile layout

Creating a report

Summary

Questions

Further reading

Understanding Dashboards, Apps, Metrics, and Security

Technical requirements

Understanding dashboards

Creating a dashboard

Working with dashboards

The File menu

Edit

Ellipsis menu

Working with tiles

Sizing and position

Tile options

Creating and using apps

Creating an app

Getting and using apps

Working with metrics

Creating scorecards and goals

Using scorecards and goals

Understanding security and permissions

Workspace permissions

App permissions

Object permissions

RLS

Summary

Questions

Further reading

Refreshing Content

Technical requirements

Installing and using data gateways

Downloading and installing a data gateway

Personal mode installation

Standard mode installation

Running a data gateway

Personal mode

Standard mode

Configuring a data gateway

Service Settings

Diagnostics

Network

Connectors

Recovery Keys

Managing a data gateway

Adding connections

Refreshing semantic models

Scheduling a refresh

Summary

Questions

Further reading

Part 4: The Future

Deploying, Governing, and Adopting Power BI

Technical requirements

Understanding usage models

Anarchy model

Centralized model

Federated model

Golden semantic model

Hybrid model

Governing and administering Power BI

Classifying content

Domains

Tags

Endorsements and featured content

Tenant settings

Microsoft Fabric

Help and support settings

Domain management settings

Workspace settings

Information protection

Export and sharing settings

Discovery settings

App settings

Integration settings

Power BI visuals

R and Python visual settings

Audit and usage settings

Dashboard settings

Developer settings

Admin API settings

Gen1 dataflow setting

Template app settings

Q&A settings

Semantic model security

Advanced networking

Metric settings

User experience experiments

Share data with your Microsoft 365 services

Insight settings

Datamart setting

Data model setting

Scale-out settings

OneLake settings

Git integration

Copilot and Azure OpenAI Service

Additional workloads

Organizational visuals

Deploying Power BI content

Gateway governance

Purview

Adopting Power BI

Adoption strategies

Summary

Questions

Further reading

Working with Microsoft Fabric and Copilot

Technical requirements

Leveraging Data Factory

Working with Data Warehouse

Using Microsoft Fabric items

Working with Copilot

Summary

Questions

Further reading

Putting Your Knowledge to Use

Technical requirements

Understanding the BI opportunity

Understanding the types of BI jobs and roles

BI analysts

BI developers

BI administrators

BI project managers

Data warehouse architects

BI managers

BI directors or CDOs

Growing your job and career

Understanding the employment and career opportunities

Consulting services

Understanding the differences between consultants and employees

Job search strategies

Interviewing tips

Negotiating benefits and compensation

Negotiating the salary

Continuing your journey

Summary

Questions

Further reading

Other Books You May Enjoy

Index

Landmarks

Cover

Index

Share your thoughts

Once you’ve read Learn Microsoft Power BI, Third Edition, we’d love to hear your thoughts! Please click here to go straight to the Amazon review page for this book and share your feedback.

Your review is important to us and the tech community and will help us make sure we’re delivering excellent quality content.

Your Book Comes with Exclusive Perks - Here’s How to Unlock Them

Unlock this book’s exclusive benefits now

Scan this QR code or go to packtpub.com/unlock, then search this book by name. Ensure it’s the correct edition.

Note: Keep your purchase invoice ready before you start.

Enhanced reading experience with our Next-gen Reader:

Multi-device progress sync: Learn from any device with seamless progress sync.

Highlighting and notetaking: Turn your reading into lasting knowledge.

Bookmarking: Revisit your most important learnings anytime.

Dark mode: Focus with minimal eye strain by switching to dark or sepia mode.

Learn smarter using our AI assistant (Beta):

Summarize it: Summarize key sections or an entire chapter.

AI code explainers: In the next-gen Packt Reader, click the Explain button above each code block for AI-powered code explanations.

Note: The AI assistant is part of next-gen Packt Reader and is still in beta.

Learn anytime, anywhere:

Access your content offline with DRM-free PDF and ePub versions—compatible with your favorite e-readers.

Unlock Your Book’s Exclusive Benefits

Your copy of this book comes with the following exclusive benefits:

Next-gen Packt Reader

AI assistant (beta)

DRM-free PDF/ePub downloads

Use the following guide to unlock them if you haven’t already. The process takes just a few minutes and needs to be done only once.

How to unlock these benefits in three easy steps

Step 1

Keep your purchase invoice for this book ready, as you’ll need it in Step 3. If you received a physical invoice, scan it on your phone and have it ready as either a PDF, JPG, or PNG.

For more help on finding your invoice, visit https://www.packtpub.com/unlock-benefits/help.

Note: Did you buy this book directly from Packt? You don’t need an invoice. After completing Step 2, you can jump straight to your exclusive content.

Step 2

Scan this QR code or go to packtpub.com/unlock.

On the page that opens (which will look similar to Figure X.1 if you’re on desktop), search for this book by name. Make sure you select the correct edition.

Figure 0.1: Packt unlock landing page on desktop

Step 3

Sign in to your Packt account or create a new one for free. Once you’re logged in, upload your invoice. It can be in PDF, PNG, or JPG format and must be no larger than 10 MB. Follow the rest of the instructions on the screen to complete the process.

Need help?

If you get stuck and need help, visit https://www.packtpub.com/unlock-benefits/help for a detailed FAQ on how to find your invoices and more. The following QR code will take you to the help page directly:

Note: If you are still facing issues, reach out to [email protected].

Part 1

The Basics

The objective of this part is to introduce you to the key concepts of business intelligence and Power BI, understand how Power BI projects are conducted, and introduce you to the example scenario used throughout the rest of this book.

This part of the book includes the following chapters:

Chapter 1, Understanding Business Intelligence and Power BIChapter 2, Planning Projects with Power BI

1

Understanding Business Intelligence and Power BI

Power BI is part of a powerful ecosystem of business intelligence tools and technologies from Microsoft. But what exactly is business intelligence, anyway? Simply stated, business intelligence is all about leveraging data to make better decisions. This can take many forms and is not necessarily restricted to just business. We use data in our personal lives to make better decisions as well. For example, if we are remodeling a bathroom, we get multiple quotes from different firms. The prices and details in these quotes are pieces of data that allow us to make an informed decision regarding which company to choose. We may also research these firms online. This is more data that ultimately supports our decision.

This book explores Microsoft Power BI as well as Microsoft Fabric, covering everything from foundational concepts to semantic modeling, visualization techniques, and more. You are given step-by-step instructions for connecting to various data sources, transforming and shaping data using Power Query, building effective semantic models, and creating interactive reports and dashboards. In addition, topics such as DAX (short for Data Analysis Expressions), AI-powered analytics, and performance optimization strategies are covered. With practical examples and real-world scenarios, this book seeks to teach you the skills needed to leverage Power BI for data-driven decision-making and business intelligence solutions.

In this chapter, we explore the fundamental concepts of business intelligence, as well as why business intelligence is important to organizations. In addition, we take a high-level tour of the Power BI ecosystem, including Microsoft Fabric, as well as covering licensing and core tools—Power BI Desktop and the Power BI service.

The following topics are covered in this chapter:

Exploring key concepts of business intelligenceDiscovering the Power BI ecosystemChoosing the right licenseIntroducing Power BI Desktop and the Power BI service

Exploring key concepts of business intelligence

In the context of organizations, business intelligence is about making better decisions for your business. Unlike the example in the introduction, organizations are not generally concerned with bathrooms but rather with what can make their business more efficient, effective, and profitable. The businesses that provided those quotes on bathroom remodeling need to answer questions such as the following:

How can the business attract new customers?How can the business retain more customers?Who are the competitors and how do they compare?What is driving profitability?Where can expenses be reduced?

There are endless questions that businesses need to answer every day, and these businesses need data coupled with business intelligence tools and techniques to answer such questions and make effective operational and strategic decisions.

While business intelligence is a vast subject in and of itself, the key concepts of business intelligence can be broken down into five areas:

DomainDataModelAnalysisVisualization

Now, let’s look at these areas in greater detail.

Domain

A domain is simply the context where business intelligence is applied. Most businesses are composed of relatively standard business functions or departments, such as the following:

SalesMarketingManufacturing/productionSupply chain/operationsResearch and developmentHuman resourcesAccounting/finance

The domain helps in narrowing down the focus regarding which questions can be answered and what decisions need to be made. For example, within the context of sales, a business might want to know which sales personnel are performing better or worse, or which customers are the most profitable.

Business intelligence can provide such insights as well as helping to determine which activities enable certain sales professionals to outperform others, or why certain customers are more profitable than others. This information can then be used to train and mentor sales personnel who are performing less effectively or to focus sales efforts.

Within the context of marketing, a business can use business intelligence to determine which types of marketing campaigns, such as email, radio, print, TV, and the web, are most effective in attracting new customers. This then informs the business where they should spend their marketing budget.

Within the context of manufacturing, a business can use business intelligence to determine the mean time between failure (MTBF) for machines that are used in the production of goods. This information can be used by the business to determine whether preventative maintenance would be beneficial and how often such preventative maintenance should occur.

Clearly, there are endless examples of where business intelligence can make an organization more efficient, effective, and profitable. Deciding on a domain in which to employ business intelligence techniques is a key step in enabling business intelligence undertakings within organizations since the domain dictates which key questions can be answered, the possible benefits, as well as what data is required in order to answer those questions.

Data

Once a domain has been decided upon, the next step is identifying and acquiring the data that’s pertinent to that domain. This means identifying the sources of relevant data. These sources may be internal or external to an organization and may be structured, unstructured, or semi-structured in nature.

Internal and external data

Internal data is data that is generated within an organization by its business processes and operations. These business processes can generate large volumes of data that is specific to that organization’s operations. This data can take the form of net revenues, sales to customers, new customer acquisitions, employee turnover, units produced, cost of raw materials, and time series or transactional information.

This historical and current data is valuable to organizations if they wish to identify patterns and trends, as well as for forecasting and future planning. Importantly, all the relevant data to a domain and question is almost never housed within a single data source; organizations inevitably have multiple sources of relevant data.

In addition to internal data, business intelligence is often more effective when internal data is combined with external data. Crucially, external data is data that is generated outside the boundaries of an organization’s operations. External data includes things such as overall global economic trends, census information, customer demographics, household salaries, and the cost of raw materials. All this data exists irrespective of any single organization.

Each domain and question will have internal and external data that is relevant and irrelevant to answering the question at hand. However, do not be fooled into believing that simply because you have chosen manufacturing/production as the domain, other domains, such as sales and marketing, do not have relevant sources of data.

If you are trying to forecast the required production levels, sales data in terms of pipelines can be very relevant. Similarly, external data that points toward overall economic growth may also be extremely relevant, while data such as the cost of raw materials may very well be irrelevant.

Structured, unstructured, and semi-structured data

Structured data is data that conforms to a rather formal specification of tables with rows and columns. Think of a spreadsheet where you might have columns for the transaction ID, customer, units purchased, and price per unit. Each row represents a sales transaction. Structured data sources are the easiest sources for business intelligence tools to consume and analyze. The most common structured data sources are relational databases such as Microsoft SQL Server, Microsoft Access, Azure SQL Database, Oracle, MySQL, IBM Db2, Teradata, PostgreSQL, Informix, and Sybase. In addition, this category also includes such things as Azure Table storage and relational database standards such as Open Database Connectivity (ODBC) and Object Linking and Embedding Database (OLE DB), which are supported standards for accessing a wide variety of relational databases as well as other data storage systems.

Unstructured data is effectively the opposite of structured data. Unstructured data cannot be organized into simple tables with rows and columns. Such data includes things such as video, audio, and images. Text documents, social media posts, and online reviews are also examples of largely unstructured data. Unstructured data sources are the most difficult types of sources for business intelligence tools to consume and analyze. This type of data is either stored as Binary Large Objects (BLOBs), online files or posts, or files in a filesystem, such as the New Technology FileSystem (NTFS) or the Hadoop Distributed File System (HDFS).

Semi-structured data has a structure but does not conform to the formal definition of structured data, that is, tables with rows and columns. Examples of semi-structured data include tab and delimited text files, eXtensible Markup Language (XML), other markup languages such as HTML and XSL, JavaScript Object Notation (JSON), and Electronic Data Interchange (EDI). Semi-structured data sources have a self-defining structure that makes them easier to consume and analyze than unstructured data sources but require more work than true, structured data sources.

Semi-structured data also includes so-called NoSQL databases, which are specifically designed to store both structured and unstructured data. They include data stores such as:

Document databases: Document databases generally store data in JSON, Binary JSON (BSON), or XML and include Microsoft Azure Cosmos DB, MongoDB, Cloudant (IBM), Couchbase, and MarkLogic.Graph databases: Graph databases represent and store data using graph concepts such as nodes, edges, and properties. Examples include Neo4j and HyperGraphDB.Key-value stores: Key-value stores, also known as dictionaries or hash tables, treat data as records that can each have a different number and type of fields. Basho Technologies’ Riak, Redis, Aerospike, Amazon Web Services’ DynamoDB, Couchbase, DataStax’s Cassandra, and MapR Technologies are examples of key-value stores.Wide-column stores: Wide-column stores organize data into column families versus rows like traditional relational databases. Examples include Cassandra and HBase.

Finally, semi-structured data also includes data access protocols, such as Open Data Protocol (OData) and other Representational State Transfer (REST) application programming interfaces (APIs). These protocols provide interfaces to data sources such as Microsoft SharePoint, Microsoft Exchange, Microsoft Active Directory, and Microsoft Dynamics; social media systems such as X/Twitter and Facebook; as well as other online systems such as Mailchimp, Salesforce, Smartsheet, Twilio, Google Analytics, and GitHub. These data protocols abstract how the data is stored, whether that is a relational database, NoSQL database, or simply a bunch of files.

Most business intelligence tools, such as Power BI, are optimized for handling structured and semi-structured data. Structured data sources integrate natively with how business intelligence tools are designed. In addition, business intelligence tools are designed to ingest semi-structured data sources and transform them into structured data. Unstructured data is more difficult but not impossible to analyze with business intelligence tools. In fact, Power BI has some features that are designed to ease the ingestion and analysis of unstructured data sources. However, analyzing such unstructured data has its limitations.

Model

A model, also known as a semantic model or data model, refers to the way in which one or more data sources are organized to support analysis and visualization. Models are built by transforming and cleansing data, which helps define the types of data within those sources, as well as to categorize the data according to specific data types. Building a model generally involves three elements:

Transforming and cleansingDefining and categorizingOrganizing

Transforming and cleansing

When building a semantic model (also known as a data model), it is often (read: always) necessary to clean and transform the source data. Data is never clean— it must always be massaged for bad data to be removed or resolved. For example, when dealing with customer data from a Customer Relationship Management (CRM) system, it is not uncommon to have the same customer entered with multiple spellings. The format of data in spreadsheets may make data entry easy for humans but can be unsuitable for business intelligence purposes. In addition, data may have errors, missing data, inconsistent formatting, or even something as seemingly simple as trailing spaces. These types of situations can cause problems when performing business intelligence analysis. Luckily, business intelligence tools such as Power BI provide mechanisms for cleansing and reshaping the data to support analysis. This might involve replacing or removing errors in the data, pivoting, unpivoting, or transposing rows and columns, removing trailing spaces, or other types of transformation operations.

Transforming and cleansing technologies are often referred to as Extract, Transform, Load (ETL) tools and include products such as Microsoft’s SQL Server Integration Services (SSIS), Azure Data Factory, Alteryx, Informatica, Dell Boomi, Salesforce’s MuleSoft, Skyvia, IBM’s InfoSphere Information Server, Oracle Data Integrator, Talend, Pentaho Data Integration, SAS’s Data Integration Studio, Sybase ETL, and QlikView Expressor.

Defining and categorizing

Semantic models also formally define the types of data within each table. These data types include text, decimal number, whole number, percentage, date, time, date and time, duration, true/false, and binary. The definition of these data types is important as it defines what kind of analysis can be performed on the data. For example, it does not make sense to create a sum or average of text data types; instead, you would use aggregations such as count, first, or last.

Finally, semantic models also define the data category of data types. While a data type such as a postal code might be numeric or text, it is important for the model to define that the numeric data type represents a postal code. This further defines the type of analysis that can be performed on this data, such as plotting the data on a map. Similarly, it might be important for the semantic model to define that a text data type represents a web or image Uniform Resource Locator (URL). Typical data categories include such things as address, city, state, province, continent, country, region, place, county, longitude, latitude, postal code, web URL, image URL, and barcode.

Organizing

Semantic models can be extremely simple, such as a single table with columns and rows. However, business intelligence almost always involves multiple tables of data, often coming from multiple sources. Thus, the model becomes more complex as the various sources and tables of data must be combined into a cohesive whole. This is done by defining how each of the disparate sources of data relate to one another.

As an example, let’s say you have one data source that represents a customer’s name, contact information, and perhaps the size of the business by revenue and/or the number of employees. This information might come from an organization’s CRM system. The second source of data might be order information, which includes the customer’s name, units purchased, and the price that was paid. This second source of data comes from the organization’s Enterprise Resource Planning (ERP) system. These two sources of data can be related to one another based on the unique name or ID of the customer.

Some sources of data have prebuilt models. In other words, there are defined relationships between data entities or tables within the data source. This includes traditional data warehouse technologies for structured data as well as analogous systems for performing analytics over unstructured data. Data warehouses are traditionally built upon Online Analytical Processing (OLAP) technology and include systems such as Microsoft’s Analysis Services, Snowflake, Oracle’s Essbase, AtScale cubes, SAP HANA and Business Warehouse servers, and Azure Synapse. With respect to unstructured data analysis, technologies such as Apache Spark, Databricks, and Azure Data Lake Storage are used.

Note that OLAP refers to a method of storing data that optimizes read operations for reporting purposes. This is in contrast to Online Transaction Processing (OLTP) systems, which store data in a manner that is optimized for write operations.

Analysis

Once a domain has been selected and data sources have been combined into a model, the next step is to perform an analysis of the data. This is a key process within business intelligence as this is when you attempt to answer questions that are relevant to the business using internal and external data.

For example, simply having data about sales is not immediately useful to a business. To predict future sales revenue, it is important that such data is aggregated and analyzed. This analysis can determine the average sales for a product, the frequency of purchases, and which customers purchase more frequently than others. Such information allows better decision-making by an organization.

Data analysis can take many forms, such as grouping data, creating simple aggregations such as sums, counts, and averages, as well as creating more complex calculations, identifying trends, correlations, and forecasting. Many times, organizations have, or wish to have, Key Performance Indicators (KPIs), which are tracked by the business to help determine the organization’s health or performance. KPIs might include such things as employee retention rate, net promoter score, new customer acquisitions per month, gross margin, and Earnings Before Interest, Tax, Depreciation, and Amortization (EBITDA). Such KPIs generally require that the data is aggregated, has calculations performed on it, or both. These aggregations and calculations are called metrics or measures and are used to identify trends or patterns that can inform business decision-making. In some cases, advanced analysis tools such as programming languages, machine learning and artificial intelligence, data mining, streaming analytics, and unstructured analytics are necessary to gain the proper insights.

There are numerous programming languages that have either been specifically designed from the ground up for data analytics or have developed robust data analytics packages or extensions. Two of the most popular languages in this space include R and Python. Other popular languages include SQL, Multidimensional Expressions (MDX), Julia, SAS, MATLAB, Scala, and F#.

There is also a wide variety of machine learning and data mining tools and platforms for performing predictive analytics around data classification, regression, anomaly detection, clustering, and decision-making. Such systems include TensorFlow, Microsoft’s Azure Machine Learning, DataRobot, Alteryx Analytics Hub, H2O.ai, KNIME, Splunk, RapidMiner, and Prevedere.

Streaming analytics becomes important when dealing with Internet of Things (IoT) data. Streaming analytics processes data in real time or near real time. In these situations, tools such as Striim, StreamAnalytix, TIBCO Event Processing, Apache Storm, Azure Stream Analytics, Oracle Stream Analytics, and Microsoft Fabric’s Real-Time Intelligence workload are used.

When dealing with unstructured data, tools such as Pig and Hive are popular, as well as tools such as Apache Spark and Azure AI for vision, speech, and sentiment analysis.

Of course, any discussion around data analytics tools would be incomplete without including Microsoft Excel. Spreadsheets have long been the go-to analytics tool for business users, and the most popular spreadsheet today is Microsoft Excel. However, other spreadsheet programs, such as Google Sheets, Smartsheet, Apple Numbers, Zoho Sheet, and LibreOffice Calc, also exist.

Visualization

The final key concept in business intelligence is visualization or the actual presentation of the analysis being performed. Humans are visually oriented and thus it is advantageous to view the results of the analysis in the form of charts, reports, and dashboards. Charts may take the form of tables, matrices, pie charts, bar graphs, and other visual displays that help provide context and meaning to the analysis, and multiple charts are combined to make reports and dashboards. In the same way that a picture is worth a thousand words, visualizations allow thousands, millions, or even trillions of individual data points to be presented in a concise manner that is easily consumed and understandable. Visualization allows the analyst or report author to let the data tell a story. This story answers the questions that are originally posed by the business and thus delivers the insights that allow organizations to make better decisions.

Individual charts or visualizations typically display aggregations, KPIs, and/or other calculations of underlying data that have been summarized by some form of grouping. These charts are designed to present a specific facet or metric of the data within a specific context. For example, one chart may display the number of web sessions by the day of the week, while another chart may display the number of page views by browser.

Business intelligence tools allow multiple individual tables and charts to be combined on a single page or report. Modern business intelligence tools such as Power BI support interactivity between individual visualizations to further aid the discovery and analysis process. This interactivity allows the report consumer to click on portions of individual visualizations, such as bar charts, maps, and tables, in order to drill down, highlight, or filter the information presented or determine the influence of a particular portion of a chart on the rest of the visualizations in a report. This goes beyond typical legacy visualization tools such as SQL Server Reporting Services (SSRS) or SAP Crystal Reports, which only provide minimal user interactivity when it comes to choosing from predefined filters. For example, given the two charts we referenced previously, the report consumer can click on a particular day of the week in the first report to display the page visit breakdown per browser for the chosen day of the week in the second chart:

Figure 1.1 – Two bar charts: (L) sessions by DayOfWeek; (R) Page views by browser

Finally, dashboards provide easy-to-understand visualizations of KPIs that are important to an organization. For example, the CEO of a corporation may wish to see only certain information from sales, marketing, operations, and human resources. Each of these departments may have its own detailed reports, but the CEO only wishes to track one or two of the individual visualizations within each of those reports. Dashboards enable this functionality.

Visualization software includes venerable tools such as SSRS and Crystal Reports, as well as software such as Birst, Domo, MicroStrategy, Qlik Sense, Tableau CRM, SAS Visual Analytics, Sisense, Tableau, ThoughtSpot, and TIBCO Spotfire.

Now that we have examined the key concepts and overarching themes of business intelligence, it is time to delve a layer deeper and discover the technologies that enable business intelligence within the Power BI ecosystem.

Discovering the Power BI ecosystem

While Power BI is often classified as a visualization tool, the reality is that Power BI is not a single tool but rather part of a collection of interrelated tools and services that form a complete business intelligence ecosystem. This ecosystem spans the entire business intelligence spectrum: domain, data, model, analysis, and visualization. In addition, this ecosystem includes components that are specific not only to Power BI itself but also to other Microsoft technologies that encompass and interoperate with Power BI, as well as third-party integrations. This interoperation with other Microsoft tools and technologies, as well as third parties, makes Power BI a formidable business intelligence platform, whose value far exceeds that of more siloed business intelligence tools in the market.

While the Power BI ecosystem is vast and complex, this ecosystem can be broken down into the following categories:

Core and Power BI-specificCore and non-Power BI-specificNon-core and Power BI-specificNatively integrated Microsoft technologiesExtended ecosystem

Core and Power BI-specific

Core and Power BI-specific technologies are systems that are specific to Power BI and fundamental to its use. Core and Power BI-specific technologies include the following:

Power BI Desktop is a free Windows-based application that is installed on a local desktop or laptop computer. It is the primary tool used to ingest, cleanse, and transform various sources of data, combine the data into models, and then analyze and visualize the data through the creation of calculations, visualizations, and reports. Once reports have been created in Power BI Desktop, these reports are often published to the Power BI service for sharing and collaboration.The Power BI service is a cloud-based Software as a Service (SaaS) online platform. The Power BI service can be used for light report creation and editing, dashboard creation, and sharing, collaborating on, and viewing reports. Some data sources can be connected directly from the Power BI service, but the ability to model and analyze that data is limited.

Core and non-Power BI-specific

Core and non-Power BI-specific technologies are technologies that are a core part of Power BI but are also used in other software and systems. These include the following:

Power Query is the Microsoft technology that provides data connectivity and transformation. This technology allows business users to access hundreds of different sources and transform the data as required. Data sources supported by Power Query include many different file types, databases, Microsoft Azure services, and third-party services. Power Query also provides a Software Development Kit (SDK) that allows for the creation of custom connectors so that third parties can create their own data connectors that seamlessly interoperate with Power Query. Power Query is used within Power BI Desktop, Microsoft Excel, Microsoft SQL Server Data Tools for Visual Studio, and Microsoft Dataverse (formerly Common Data Service).DAX is a language that consists of a collection of functions, operators, and constants that can be used to write formulas or expressions that return calculated values. Similar to Excel functions or MDX, DAX helps you create new information from data that’s already in your model.A data gateway is software that is installed to facilitate access from the Power BI service to on-premises data sources. A data gateway allows the Power BI service to refresh data from one or more data sources housed within on-premises systems. The data gateway comes in two modes – Personal and Enterprise. Personal mode can only be used with Power BI, while Enterprise mode can be used with Power BI as well as Power Automate, Microsoft Power Apps, Azure Analysis Services, and other Azure Logic Apps.Analysis Services tabular modeling is an evolution of Microsoft’s multidimensional cubes. This technology is available outside of Power BI within Analysis Services but is also fundamental to Power BI. Models that are built within Power BI are actually built using SSAS Tabular, and Power BI Desktop runs a full instance of SSAS Tabular under the hood, so to speak. Thus, when building models in Power BI Desktop, you are actually building an Analysis Services tabular model.Microsoft AppSource, or simply AppSource, is a marketplace for finding apps, add-ons, and extensions to Microsoft software, including Microsoft 365, Azure, Dynamics 365, Cortana, and Power BI. Within Power BI, AppSource hosts custom visuals that can be downloaded and added to Power BI reports that have been authored within the desktop. These visuals are supported within the Power BI service as well.

Non-core and Power BI-specific

Non-core and Power BI-specific technologies are technologies that are specific to Power BI but not necessarily required to use Power BI. These include the following:

Power BI Report Server (PBIRS) is an on-premises technology that is a superset of SSRS. Similar in function to the Power BI service, PBIRS allows Power BI reports authored in Power BI Desktop and Excel to be published and viewed while remaining fully on-premises. Because PBIRS is a superset of SSRS, it can also host paginated reports (.rdl).Power BI Embedded is a system of REST APIs that can be used to display visualizations (charts), reports, and dashboards within custom applications that serve customers that are external to an organization. Power BI Embedded is often used by Independent Software Vendors (ISVs) and developers.Power BI mobile applications are native Android, iOS, and Windows applications that are downloadable from the respective platform stores: Google Play, the Apple App Store, and the Microsoft Store. Power BI mobile apps are touch-optimized for viewing and interacting with Power BI reports that are published to the Power BI service.

Natively integrated Microsoft technologies

Natively integrated Microsoft technologies include the following:

Microsoft 365 is Microsoft’s ubiquitous line of subscription services, which includes traditional Office applications, plus other productivity services that are enabled via the cloud (the internet). Central to Microsoft 365 is the concept of a tenant, an organization’s very own slice of Microsoft 365. Power BI integrates natively with Microsoft 365 so that when a user subscribes to Power BI, the email address is checked for existing Microsoft 365 tenants, and if one exists, the Power BI user will be added to that tenant. If an Microsoft 365 tenant does not exist, Power BI will provision a new Microsoft 365 tenant, sometimes called a shadow tenant, and the Power BI user will be added to that tenant.Excel incorporates many underlying Power BI technologies as native add-ons. These include Analyze in Excel and Power Pivot, which provides access to Power Query, and the same underlying semantic model used by Power BI (Analysis Services tabular models). Excel is also a first-class citizen within the Power BI service and is called Workbooks within the Power BI service.Power Platform is an encompassing ecosystem that includes Power BI as well as Power Automate, Power Apps, and Power Virtual Agents:Power Automate is a workflow technology that has a native connector for Power BI that supports both triggers and actions. Triggers are based on the Power BI service’s data alerts, and actions support both streaming and non-streaming datasets in the service. In addition, there is a Power Automate visualization available in AppSource.Power Apps is a form-based technology from Microsoft that provides a native connector to Power BI, as well as a default visualization. In addition, Power BI tiles can be embedded into Power Apps applications. Finally, Power Apps uses Power Query technology as part of its data integration feature.Power Virtual Agents enables the creation and use of intelligent agents or bots that can perform tasks or have intelligent, contextual conversations with humans. Power Virtual Agents chatbots can be integrated into Power BI workspaces.The Microsoft Visio desktop application has a custom visual for Power BI that was built by Microsoft. This visual allows you to link data within Power BI to a Visio diagram where the data is displayed using values or colors within the Visio drawing.SharePoint provides the ability to embed Power BI reports within SharePoint via a native Power BI report web part.Dynamics 365 provides the ability to embed Power BI visuals natively within Dynamics 365 reports and dashboards. In addition, Power BI has native connectors for Dynamics. Finally, there are several apps available for Dynamics in the Power BI service.Dataverse (formerly Common Data Service) is actually central to Power Automate, Power Apps, Power Virtual Agents, Dynamics 365, and Power BI. Dataverse allows an organization to store and manage data from numerous business applications within a set of standard and custom entities. Entities allow an organization to create a business-focused definition of their data and use this data within apps. Power BI has a native connector for Dataverse.Azure Machine Learning technologies are becoming pervasive within Power BI. This includes the ability to create columns from examples within Power Query, as well as custom visualizations, such as the key influencers visual. In addition, dataflows in Power BI Premium capacities can leverage Automated Machine Learning (AutoML) and Azure AI services.One of the native outputs from Azure Stream Analytics is Power BI. This allows you to stream data that is running through Azure Stream Analytics and display it on Power BI’s dashboard tiles.Report Builder is a venerable Microsoft tool for the creation of paginated reports (.rdl