Learn Power BI

A beginner's guide to developing interactive business intelligence solutions using Microsoft Power BI

Greg Deckler

• Publisher: Packt Publishing
• Year: 2019
• Print ISBN: 9781838644482
• eBook ISBN: 9781838646653
• Edition: 1
• Topic: Computer Science
• Subtopic: Business Intelligence
• Index: Computer Science

Solve business challenges with Microsoft Power BI's advanced visualization and data analysis techniques

Key Features

• Create effective storytelling reports by implementing simple-to-intermediate Power BI features
• Develop powerful analytical models to extract key insights for changing business needs
• Build, publish, and share impressive dashboards for your organization

Book Description

To succeed in today's transforming business world, organizations need business intelligence capabilities to make smarter decisions faster than ever before. This Power BI book is an entry-level guide that will get you up and running with data modeling, visualization, and analytical techniques from scratch.

You'll find this book handy if you want to get well-versed with the extensive Power BI ecosystem. You'll start by covering the basics of business intelligence and installing Power BI. You'll then learn the wide range of Power BI features to unlock business insights. As you progress, the book will take you through how to use Power Query to ingest, cleanse, and shape your data, and use Power BI DAX to create simple to complex calculations. You'll also be able to add a variety of interactive visualizations to your reports to bring your data to life. Finally, you'll gain hands-on experience in creating visually stunning reports that speak to business decision makers, and see how you can securely share these reports and collaborate with others.

By the end of this book, you'll be ready to create simple, yet effective, BI reports and dashboards using the latest features of Power BI.

What you will learn

• Explore the different features of Power BI to create interactive dashboards
• Use the Query Editor to import and transform data
• Perform simple and complex DAX calculations to enhance analysis
• Discover business insights and tell a story with your data using Power BI
• Explore data and learn to manage datasets, dataflows, and data gateways
• Use workspaces to collaborate with others and publish your reports

Who this book is for

If you're an IT manager, data analyst, or BI user new to using Power BI for solving business intelligence problems, this book is for you. You'll also find this book useful if you want to migrate from other BI tools to create powerful and interactive dashboards. No experience of working with Power BI is expected.

Section 1: The Basics

The objective of this section is to introduce you to key concepts, example scenarios, and downloading supporting data of Power BI.

This section comprises of the following chapter:

• Chapter 1, Introduction to Business Intelligence and Power BI

Introduction to Business Intelligence and Power BI

Power BI is 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 in order 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 in terms of which company to choose. We may also research these firms online. This is more data that ultimately supports our decision.

In this chapter, we will explore the key 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, licensing, and core tools such as the Power BI Desktop and the Power BI Service.

The following topics will be covered in this chapter:

• Key concepts of business intelligence
• The Power BI ecosystem
• Power BI licensing
• Power BI Desktop and Power BI Service

Key concepts of business intelligence

Business intelligence, in the context of organizations, revolves around making better decisions about your business. Unlike the example in the introduction, organizations are not generally concerned with bathrooms, but rather with what can make their business more effective, efficient, 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 diminished?

There are endless questions that businesses need to answer every day, and these businesses need data coupled with business intelligence tools and techniques in order to answer these 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:

• Domain
• Data
• Model
• Analysis
• Visualization

Domain

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

• Sales
• Marketing
• Manufacturing/production
• Logistics
• Research and development
• Purchasing
• Human resources
• Accounting/finance

Each of these business functions or departments represents a domain within which business intelligence can be used to answer questions that can assist us in making better decisions.

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 and which sales personnel are performing worse. Business intelligence can provide this insight as well as help determine which activities enable certain sales professionals to outperform others. This information can then be used to train and mentor sales personnel who are performing more poorly.

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 which 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's 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 much more 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 are almost never housed within a single data source; organizations inevitably have multiple sources of relevant data.

In addition to internal data, business intelligence is most effective when internal data is combined with external data. Crucially, external data is data that is generated outside of the boundaries of an organization's operations. Such external data includes things such as the business's overall global economic performance, census information, and competitor prices. All of this data exists irrespective of any particular 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 that 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. These sources are most often relational databases, which include technologies such as Microsoft SQL Server, Microsoft Access, Azure Table storage, Azure SQL database, Oracle, MySQL, IBM DB2, Teradata, PostgreSQL, Informix, and Sybase. In addition, this category of data sources includes relational database standards and APIs such as Open Database Connectivity (ODBC) and Object Linking and Embedding Database (OLE DB).

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 videos, audio, images, and text. Word processing documents, emails, social media posts, and web pages 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) or as a file in a filesystem such as the New Technology File System (NTFS) or the Hadoop Distributed File System (HDFS).

Unstructured data also includes so-called NoSQL databases, which include data stores such as document databases, graph databases, and key-value stores. These databases are specifically designed to store unstructured data. Document databases include Microsoft Azure Cosmos DB, MongoDB, 10Gen, Cloudant (IBM), Couchbase, and MarkLogic. Graph databases include Neo4j and HyperGraphDB. Key-value stores include Microsoft's Cosmos DB, Basho Technologies' Riak, Redis, Aerospike, Amazon Web Services' DynamoDB, Basho Technologies, Couchbase, Datastax's Cassandra, MapR Technologies, and Oracle. Finally, wide-column stores include Cassandra and HBase.

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 include tab and delimited text files, 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 data access protocols, such as the Open Data Protocol (OData) and other Representational State Transfer (REST) 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 Twitter and Facebook; as well as other online systems such as MailChimp, Salesforce, Smartsheet, Twilio, Google Analytics, and GitHub, to name a few. These data protocols abstract how the data is stored, whether that is a relational database, NoSQL database, or simply a bunch of files.

The vast majority of 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 a number of 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, or data model, refers to the way in which one or more data sources are organized in order to support analysis and visualization. Models are built by transforming and cleansing data, helping to define the types of data within those sources, as well as the definition of data categories for specific data types.

Organizing

Models can be extremely simple, such as a single table with columns and rows. However, business intelligence almost always involves multiple tables of data, and most often involves multiple tables of data 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 eac...

Table of contents

• Title Page
• Copyright and Credits
• About Packt
• Contributors
• Preface
• Section 1: The Basics
• Introduction to Business Intelligence and Power BI
• Section 2: The Desktop
• Up and Running with Power BI Desktop
• Connecting and Shaping Data
• Creating Data Models and Calculations
• Unlocking Insights
• Creating the Final Report
• Section 3: The Service
• Publishing and Sharing
• Using Reports in the Service
• Understanding Dashboards, Apps, and Security
• Data Gateways and Refreshing Datasets
• Other Books You May Enjoy