.png)
Business Intelligence
Academy Training
Ted Pattison, MVP
Developer, Instructor and Microsoft MVP
Critical Path Training
This course is an accelerated 2-day workshop designed to teach how attendees how to build reporting and data analysis projects with Power BI Desktop and how to share Power BI reports and dashboards with a wide audience of consumers. During the course, students will gain the real-world skills required to design, deploy, upgrade and monitor reporting and dashboard solutions using the rapidly-evolving Power BI platform. All the essential Power BI concepts and techniques discussed in lectures are reinforced with hands-on lab exercises in which students will move through the actual steps required to build reporting and dashboard solutions and then to make those solutions accessible using app workspaces and the Power Bi app model.
This course includes in-depth coverage of the query tools and data modeling tools in Power BI Desktop. Students will learn how to design complex queries and to write advanced DAX expressions to calculate rankings, rolling averages and year-to-year comparisons. Students will also learn how to design interactive reports using slicers, bookmarks and drillthrough pages. The course discusses essential differences between reports and dashboards and also examines the strengths and weaknesses between the various types of datasets including imported datasets, DirectQuery datasets and LiveConnect datasets.
Instructor: Ted Pattison of Critical Path Training
Audience: Business Users and Data Analysts
Level: Intermediate
Prerequisites: All students will require a Windows PC running Windows 10, Windows 8.1 or Windows 7 to complete the lab exercises for this course. It is recommended that students install Power BI Desktop before arriving.
Delivery Method: Classroom-based training with students attending in person
Course Modules
1. Introducing the Power BI Platform
2. Designing Queries with Power BI Desktop
3. Designing a Data Model with Power BI Desktop
4. Designing Interactive Reports in Power BI Desktop
5. Designing Dashboards in the Power BI Service
6. Distributing Content using App Workspaces
Module 01: Introducing the Power BI Platform
This module provides an overview of the Power BI platform and explains the role of the Power BI Service, Power BI Desktop and Power BI Mobile. Students will learn how to get started with the Power BI Service creating reports and dashboards using a browser. The module also provides students with a quick primer on working with Power BI Desktop and installing the Power BI app on a mobile device. This introductory module concludes with a discussion of how to keep current with all the monthly updates that Microsoft consistently applies to Power BI Desktop and the Power BI Service.
Topics Covered
• Understanding the Power BI Platform
• Creating a Trial Office 365 Tenant for Testing
• Creating Datasets, Reports and Dashboards
• Getting Started with Power BI Desktop
• Staying in Sync with Monthly Updates
Hands-on Lab: Getting Up and Running with the Power BI Service
• Exercise 1: Create a new Office 365 Trial Tenant
• Exercise 2: Create a New Power BI Report with Multiple Pages
• Exercise 3: Create a Power BI Dashboard
• Exercise 4: Getting Started with Power BI Desktop
Module 02: Designing Queries with Power BI Desktop
This module examines the query tools in Power BI Desktop. Students will learn to import data from a variety of sources including Excel workbooks, databases, web pages and SharePoint lists. Students will also learn effective techniques for cleansing data during the import process and for converting text-based columns into numeric types. The module discusses the best practice of designing queries to generate a star schema composed of fact tables and dimension tables. Along the way, students will learn advanced query design techniques such as working directly with M code and creating query parameters, query functions and Power BI template files.
Topics Covered
• Deciding What To Measure
• Query Design Fundamentals
• Designing Data Model using a Star Schema
• Working with the Query Editor Window
• Importing Content From SharePoint Online
• Understanding Parameters and Template Files
• Designing with Function Queries
Hands-on Lab: Designing Queries to Extract and Transform Data
• Exercise 1: Importing Data from a SQL Azure Database
• Exercise 2: Design Queries to Transform and Reshape Customer Sales Data
• Exercise 3: Using Power Query to Transform and Reshape Product Data
• Exercise 4: Using Power Query to Transform and Reshape Product Sales Data
• Exercise 5: Using Power Query to Transform and Reshape Invoice Data
Module 03: Designing a Data Model with Power BI Desktop
This module explains the technical details of creating table relationships and the best practices for writing maintainable DAX expressions for calculated columns and measures. The module demonstrates how to create dynamic lookup tables using DAX, how to create dimensional hierarchies and how to configure the data category for geographical data columns to facilitate mapping data to countries, states, cities and zip codes. Student will learn how to use What-If parameters. This module includes an in-depth examination of the DAX evaluation context and explains how row context and filter context affect the way you write advanced DAX expressions. Students will learn how to use the CALCULATE function to manipulate the current filter context and how to extend a data model with a calendar table and the Time Intelligence support in DAX to analyze data in a variety of time dimensions.
Topics Covered
• Creating Table Relationships
• Creating Calculated Columns and Measure
• Creating Tables using DAX Expressions
• Configuring Fields for Geographic Mapping
• Creating Dimensional Hierarchies
• Using the DAX Calculate Function
• Calendar Tables and Time Intelligence
Hands-on Lab: Designing a Data Model in Power BI Desktop
• Exercise 1: Configure Table Relationships
• Exercise 2: Create Calculated Columns using DAX
• Exercise 3: Create a Dynamic Lookup Table using DAX
• Exercise 4: Create Measures using DAX
• Exercise 5: Configure Geolocation Columns using Data Categories
• Exercise 6: Extend the Data Model using Dimensional Hierarchies
• Exercise 7: Extend the Data Model with a Custom Calendar Table
• Exercise 8: Create Measures using DAX Time Intelligence Functions
Module 04: Designing Interactive Reports in Power BI Desktop
This module teaches students how to design interactive reports in Power BI Desktop using slicers, visual highlighting and drill actions. The module explains how to use bookmarks and drillthrough pages to design Power BI reports with interactive navigation and filtering capabilities. The module demonstrates importing custom visuals and designing reports using Phone Layout view. The module examines how report themes can be used to provide a consistent style and branding across multiple reports. The module also discusses the PBIX project publishing process and how it differs when publishing to the Power BI Service versus publishing to Power BI Report Server. The module concludes with an examination of using the Publish to Web feature and the Publish to SharePoint feature with a report that has already been published to the Power BI Service.
Topics Covered
• Designing Interactive Reports
• Creating the Top 5 Products List
• Working with Bookmarks and Drillthrough
• Using Report Themes
• Importing Custom Visuals
• Designing Reports for Mobile Devices
• Publishing Power BI Reports
Hands-on Lab: Designing Interactive Reports in Power BI Desktop
• Exercise 1: Create the Sales Revenue Breakdown Report
• Exercise 2: Import a Custom Visual for Use in a Power BI Report
• Exercise 3: Create the Top 5 Products Report
• Exercise 4: Create the Top 10 Customers Report
• Exercise 5: Publish Your Project and Its Reports to the Power BI Service
Module 05: Designing Dashboards in the Power BI Service
The module teaches students the fundamentals of designing dashboards and examines the strengths and limitations of dashboards when compared to Power BI reports. Students will learn how to create and execute natural language queries and how to pin natural language query results to create dashboard tiles. The module demonstrates using Phone Layout view in the Power BI Service to customize the rendering of dashboards when viewed using mobile devices. Student will learn how to configure data alerts on dashboard tiles as well as how to leverage dashboard sharing as a simple mechanism to share dashboards and their underlying reports with other users. The module concludes with an examination of creating real-time dashboards and a discussion of when to use streaming datasets versus push datasets versus hybrid datasets.
Topics Covered
• Understanding How Dashboards Differ from Reports
• Working with Natural Language Queries
• Configuring Data Alerts on Dashboard Tiles
• Sharing Dashboards with Other Users
• Designing Real-time Dashboards
• Understanding Streaming vs Push vs Hybrid Datasets
Hands-on Lab: Creating Dashboards in the Power BI Service
• Exercise 1: Create the Regional Sales 2015 Report
• Exercise 2: Create the Regional Sales 2015 Dashboard
• Exercise 3: Share the Regional Sales 2015 Dashboard
Module 06: Distributing Content using App Workspaces
The module examines the best practice of building a custom solution in an app workspace and then using the app model to distribute the custom solution on a wide-scale basis. This module walks through the steps of publishing an app workspace and making it accessible to other users as an installable app. Students will learn the various ways one can install a Power BI app. The module demonstrates how to upgrade reports and dashboards after an app has been installed using staged updates. The module explains the essential concepts involved with Power BI Premium and dedicated capacities and demonstrates how to configure an app workspace to run within a dedicated capacity. Along the way, this module will provide students with the decoder ring necessary to understand the capacity-based licensing model and strategies for distributing content to users with the Power BI free license.
Topics Covered
• Understanding the Role of App Workspaces
• Publishing App Workspaces and Installing Apps
• Updating Apps and Managing App Lifecycle
• Understanding Power BI Premium and Capacities
• Configuring App Workspaces and Dedicated Capacity
• Distributing Power BI Content to Free License Users
Hands-on Lab: Distributing Content using a Power BI App
• Exercise 1: Create New App Workspaces for a Custom Solution
• Exercise 2: Publish a Power BI Desktop Project to the Wingtip Sales App Workspace
• Exercise 3: Create and Design the Product Sales Dashboard
• Exercise 4: Publish an App Workspace as a Power BI App