Analyzing your data with Power BI for BI Professionals

Introduction to Power BI

Power BI is a product that covers many things: Power BI in Excel, Power BI Desktop, the Power BI cloud service, ... . In this introduction we set the scene for the rest of the training, introducing the different aspects of Power BI.

• The need for Business Intelligence
• Self-Service BI versus Enterprise BI
• Power BI core concepts
• Introducing Power BI Desktop
• Introducing the Power BI Service
• Power BI Service Tiers and Features

Creating Queries using Power BI Desktop

In this chapter you will learn how to create queries using Power Query in Power BI Desktop to extract and transform data from source systems like SQL Server, Oracle, Excel files, CSV files,...

• Creating Power Queries and working with Query steps
• Loading data from CSV and Excel files
• Loading data from relational databases
• Applying basic transformations
• Query Folding
• Importing data, using Direct Query or using Live Connections
• LAB: Creating Queries using Power BI Desktop

Writing Advanced Queries in Power BI Desktop

Often your data sources contain data that require extra processing. You will learn about the more advanced transformations that Power BI has to offer like the Pivot, Unpivot, Column By Examples,... . Combining data from multiple queries into one query will also be covered in this chapter. In the second part you will learn about query parameters, how to write query functions and manage data source definitions.

• Advanced Transformations in Power Query
• Appending and Merging Queries
• Working with Query Parameters
• Defining and using Query Functions
• Managing Data Source Security
• LAB: Writing Advanced Queries in Power BI Desktop

Building a Data Model

Participants learn why and how to create a data model, including data sorting, data types and formatting, hiding tables and columns, creating hierarchies, ... . We will also have a look at the storage of data in import mode, including the techniques that are used for compression. We will see the Vertipaq analyzer as a tool to analyze and reduce storage costs.

• Why do we need a Data Model?
• Authoring data models in Power BI Desktop
• Controlling field formatting
• Creating hierarchies inside tables
• Data model storage in import mode
• Analyzing storage costs using the Vertipaq Analyzer or DAX Studio
• LAB: Building and enhancing a Data Model

Creating Relationships

Participants will learn about the different types of relationships available in Power BI, including 1:n, 1:1 and n:m relationships. We will see the limitations of relationships, including the fact that there can only be one active relationship between two tables. We will see how to overcome this limitation for role-playing dimensions. Finally, we will discuss the concept of filter direction, and have a look at single- and cross filter directions.

• Cardinality of relationships
• Handling role-playing dimensions
• The Cross-Filter direction of relationships
• LAB: Working with Relationships

Working with Storage modes

We will have a look at the different storage modes of a Power BI data model (Import data, DirectQuery and Live connection). We will discuss a solution for the problem that in this era of big data, we often face data sources that are too big to be imported and too slow to be directly queried. The solution for this dilemma may be to use a combination of a directly queried detailed table and an imported aggregation table. We will see how to create such a combination of tables.

• Importing data
• Using DirectQuery
• Using a Live connection
• Creating aggregation tables
• LAB: Working with Storage Modes

Data Analysis Expressions (DAX)

DAX (Data Analysis Expressions) is the formula language used in the Power BI Datamodel, Excel Power Pivot data models, and in Analysis Services Tabular. Among other things, it's used to create calculated columns, measures and tables. In this chapter, we will introduce the basic concepts of DAX. If students are interested in a deeper dive into this language, we refer them to the two day course Mastering DAX

• DAX introduction
• Creating Calculated Columns
• Creating Tables
• Creating Measures
• Working with CALCULATE
• Time Intelligence Functions
• LAB: Introduction to DAX

Designing Reports in Power BI Desktop

After the model has been fine-tuned we can use this to create our reports. Power BI allows you to create very impressive reports, and in this module you will explore many visualizations, as well as the interaction between these visualizations.

• Overview of the Report Canvas
• Adding text boxes, shapes and images
• Using the built-in visualizations
• Add style and branding through themes
• Creating Power BI Templates
• LAB: Designing Reports in Power BI Desktop

Enhancing Reports in Power BI Desktop

Sometimes a static report just isn't enough. In this module you'll learn how to make interactive reports which are easy to use for any type of end user. Take your reports to the next level.

• Working with Slicers and Filters
• Working with Drill through report pages
• Creating custom Tooltips
• Working with Bookmarks
• Visual hierarchies and drill-down behavior
• Grouping and Binning data
• Numeric range parameters
• Field parameters
• Using Custom Visuals
• Create reports that are optimized for phones
• LAB: Enhancing Reports in Power BI Desktop

Introduction to the Power BI Service

If you want to share models and reports with you co-workers, you should upload them to the Power BI service. In this module you will learn about the general features of the Power BI service, the next modules dive into the details.

• What is the Power BI Service?
• Power BI Service pricing and features
• Creating and using Workspaces
• Working with Datasets and Reports
• LAB: Working with the Power BI Service

Creating Datasets and Reports in Power BI Service

For some types of reporting you do not want to go through the hassle of creating a dataset in Power BI Desktop. That's why for a limited set of data sources we can start using them directly from the Power BI service without the need to first create an offline model. In this module you'll learn how to create these models directly in the Power BI Service.

• Creating Datasets in the Power BI Service
• Connecting to a dataset from the service in Power BI Desktop
• DirectQuery for Power BI datasets and Azure Analysis Services
• Using Quick Insights
• Creating Reports in the Power BI Service
• Printing Reports
• Export to PowerPoint
• LAB: Creating Datasets and Reports in Power BI Service

Creating Dashboards and Analyzing Data with Power BI Service

Dashboards allow you to combine visuals from different reports into one page for quick access. This module teaches you to build and customize these dashboards in the Power BI Service.

• Creating Dashboards
• Printing Dashboards
• Configuring Dashboards for Mobile Devices
• Working with Power BI Q&A
• Analyze a dataset from the service in Excel
• Working with Goals
• LAB: Creating Dashboards and Analyzing Data in Excel

Sharing Content in the Power BI Service

Power BI has different mechanisms to share content with other users: Sometimes you want to share in order to develop something together, or you want others to customize something, or maybe you want others to have read-only permissions on the shared object. In this module you'll learn how to accomplish this with the different sharing options in the Power BI Service.

• Sharing Reports and Dashboards with internal and external users
• Sharing content inside a Workspace
• Creating and consuming Power BI Apps
• Publishing Reports to the web
• Working with Report Subscriptions
• Embedding a report in SharePoint online or Microsoft Teams
• Report usage metrics
• LAB: Sharing Content in the Power BI Service

Managing Data Refresh in Power BI

Data models that are uploaded to the Power BI Service need to have their data refreshed from time to time. This module shows the different options to configure the data refresh, for cloud data as well as data which runs on-premises.

• Scheduling Dataset refreshes
• The need for a Data Gateway
• Installing and configuring a Data Gateway
• Creating Data Sources on a Data Gateway

Securing a Data Model

Security roles can be defined in Power BI Desktop. In the Power BI service, we can enforce row level security by assigning (groups of) people to the different security roles. We will see how to set up Row Level Security in Power BI Desktop, including Dynamic Row Level Security, and how to enforce it in the Power BI Service.

• Enhancing a Data Model with Row Level Security
• Configuring Row Level Security
• Implementing Dynamic Security
• Security and Relationships