IL - Power BI for Data Professionals

Course Overview

This course covers using Power BI Desktop to create and deploy data sets and reports as well as deploying, sharing, and securing assets in PowerBI.com. 

We will start off by reviewing the components of the Power BI ecosystem. Then we will begin to build a model in Power BI Desktop by acquiring data using Power Query/M. Next, we will shape and model the data using M and DAX. Then we will build calculations to support analysis and reporting. 

Once we have a working data set, we will build a report using default and custom visuals, buttons, and bookmarks. Then we’ll add dynamic row-level security roles.  

Next, we’ll explore various ways to deploy and share the data model and the report in PowerBI.com. This will include organization of work spaces, user roles within the work space, row-level security, and use of Power BI apps. We will also look at scheduling the refresh of data in imported and composite datasets. 

The course will wrap up by looking at some re-usability and life-cycle management techniques including version control, pre-production environments, use of shared and certified data sets, and suggested quality checks before deploying to production. 



Course Details
  • Duration: 3 Days
  • Level: 300

Who this course is designed for
  • Business Intelligence and Data Professionals

  • The course demonstrates the steps required to create a Power BI dataset and report, deploy them to PowerBI.com, and share them securely with the appropriate users.
  • You will be able to acquire data from multiple data sources, model the data into a star schema, and add calculations to meet business needs. This course will also help you to manage Power BI files, understand options for production and pre-production environments, and reuse datasets where applicable.
  • Using Power Query/M to connect to data sources such as SQL Databases, Excel files, text files, JSON files, and websites
  • Using parameters to change connection strings or filter data
  • Cleansing and shaping data with Power Query/M
  • Creating relationships between tables
  • Setting data types, data formats, data categories, and default summarization on columns
  • Creating and using a date table
  • Creating hierarchies in tables
  • Using DAX to create calculated tables and calculated columns
  • Formatting report pages
  • Creating and formatting interactive visualizations
  • Adding buttons, shapes, and bookmarks in reports
  • Implementing row-level security
  • Deploying reports and datasets in Power BI
  • Creating and sharing workspaces
  • Publishing apps
  • Scheduling data refresh
  • Putting reports and datasets in version control
  • Creating and using shared and certified datasets
  • Basic familiarity with business intelligence concepts such as OLAP databases and star schemas

Outline



MODULE 1: Overview of the Power BI Ecosystem

In this module we will review the components and features provided in the Power BI ecosystem.

• Overview of Power BI components
• Example use cases
• Power BI integration with other Microsoft products

MODULE 2: Power BI Desktop

This module provides an overview of Power BI Desktop functionality.

• Acquiring Power BI Desktop
• Update schedule and update announcements
• Menus, panes, and views in Power BI Desktop
• Options and settings (auto date/time, preview features, diagnostics) 

MODULE 3: Data Acquisition Basics

This module covers the basics of acquiring data in Power BI Desktop.

• Data Source options
• Using Power Query graphical user interface
• Viewing and writing M
• Dataset storage options (imported, live connection, DirectQuery, composite)
• Shaping table data: grouping, transposing, pivoting, unpivoting, merging, appending
• Transforming columns: splitting columns, merging columns, formatting columns, cleaning columns, transforming date columns
• Conditional columns
• Custom columns and column from examples
• Privacy levels 

MODULE 4: Power Query Development

In this module, you will  build on the data acquisition basics and add performance optimization and reusability techniques.

• Parameters
• Functions
• Duplicate and reference queries
• Removing blanks and errors and duplicates
• Viewing column statistics
• Query dependencies
• Disabling loading of a query
• Grouping in the Query Pane
• Data Source Settings
• Query Folding

MODULE 5: Data Modeling and the xVelocity Engine

This module covers how the xVelocity engine in Power BI stores data and reviews various data modeling techniques.

• Purpose of a data model
• Types of data models
• Star schema
• Snowflake schema
• Date table
• Columnar database
• In-memory database
• Data compression
• Relationship types and design
• Column properties (type, format, sort by, default aggregation, data category, visibility)
• Creating hierarchies

MODULE 6: DAX Basics

This module introduces DAX and uses it to create calculated columns, measures, and tables.

• Calculated columns
• Using the FORMAT function
• Calculated measures
• Implicit vs explicit measures
• Calculated tables
• Quick Measures
• Use of variables in calculated measures
• Basic aggregations
• Simple filters using the CALCULATE function
• IF and SWITCH functions
• Evaluation context in DAX
• Scalar vs Table Functions
• Iterator functions
• USERELATIONSHIP and role-playing dimensions
• Using the LOOKUPVALUE function 

MODULE 7: Advanced DAX

This module builds upon DAX basics to implement more complex calculations to meet common business needs.

• The FILTER function
• Data type conversions
• Error handling
• Time Intelligence functions and period comparison
• Cumulative totals 
• Semi-additive measures
• Computing percentages over hierarchies
• Comparing results to budgets
• Related distinct count
• Static segmentation
• Dynamic segmentation
• Ranking and Top N
• Dynamic measures with parameter tables

MODULE 8: Optimizing the data model


In this module, we provide information on optimizing Power BI models to improve performance and identify helpful free tools to use in the process.

• Remove unnecessary rows and columns
• Push calculations lower in the Power BI stack
• Dynamic Management Views
• DAX Studio
• Denormalization to star schema
• Column cardinality
• Handling datetime columns (splitting date from time, creating a time table)
• Using Boolean calculated columns to optimize complex calculated measures
• Formula engine versus storage engine
• Vertipaq Analyzer
• DAX query plans
• Reducing materialization

MODULE 9: Building Interactive Reports

This module covers the basics of building reports in Power BI Desktop.

• Formatting report pages
• Creating and formatting interactive visualizations
• Using custom visuals
• Filters and slicers
• Cross-filtering and cross-highlighting
• Drill-down and drillthrough
• Adding shapes, textboxes, and buttons
• Using bookmarks
• Interactive Q&A (natural language query)

MODULE 10: Data Visualization Techniques in Power BI

This module is all about how you can make your Power BI report impactful with proven report design techniques.

• Choosing the appropriate chart type
• Removing clutter
• Highlighting what is important
• Creating an effective navigation path
• Preattentive Attributes
• Cognitive Load
• Gestalt Principles
• Affordances
• Accessibility
• Data storytelling techniques

MODULE 11: Overview of the Power BI Service

This module covers navigation in PowerBI.com as well as the basics of sharing, securing, and refreshing your data.

• Basic navigation within PowerBI.com
• Workspaces
• Assets within a workspace (report, dataset, dashboard, workbooks, dataflows)
• Extra functionality in the Power BI Service (Quick Insights, Analyze in Excel)
• Usage metrics
• Important Power BI tenant settings

MODULE 12: Sharing and Securing your Report

This module covers how to publish a PBIX file to PowerBI.com and considerations when doing so.

• Implementing row-level security (RLS) roles in Power BI Desktop
• Static vs dynamic RLS
• Methods for publishing your report to PowerBI.com
• Populating RLS role membership
• Setting up scheduled data refresh
• On-premises data gateway 
• Workspace membership roles
• Sharing directly from a workspace
• Building a dashboard
• Publishing an app
Other sharing options (Power BI embedded in Azure, organizational embedding, publish to web, email subscriptions)
• Designing for Power BI mobile

MODULE 13: Designing Enterprise Solutions in Power BI

This module covers how to ensure your Power BI environment is optimized for protecting information and application lifecycle management

• Source control of Power BI assets
• Shared and certified datasets
• Shared dataset vs dataflows
• Options for handling multiple environments (dev/test/prod)

Contact the experts at Opsgility to schedule this class at your location or to discuss a more comprehensive readiness solution for your organization.

Contact a Cloud Training Specialist
Looking for on-demand training?
Hands-On Learning with Azure or 365