Power BI from Rookie to Rock Star

13 - 17 Aug 2018 // Speaker: Reza Rad
print

In this training course, you will learn Power BI from beginner to advance. You will learn how to use Power BI for simple data analysis situations as well as complex business intelligence scenarios. You will learn about Power BI Desktop, Power BI Website, and components of Power BI which are; Get and Transform (or Power Query), Modelling (or Power Pivot), and Visualization. You will also learn about Power Query Formula Language (Called M informally), and DAX. This course designed to give you an end to end view of Power BI, so you are able to use Power BI straight away in your everyday challenges for data analysis.

Here are the list and detailed agenda of each module:

In this training course, you will learn Power BI from beginner to advance. You will learn how to use Power BI for simple data analysis situations as well as complex business intelligence scenarios. You will learn about Power BI Desktop, Power BI Website, and components of Power BI which are; Get and Transform (or Power Query), Modelling (or Power Pivot), and Visualization. You will also learn about Power Query Formula Language (Called M informally), and DAX. This course designed to give you an end to end view of Power BI, so you are able to use Power BI straight away in your everyday challenges for data analysis.

 

1.1: Introduction to Power BI

Power BI is the newest Microsoft Business Intelligence and Data Analysis tool. In this module, we will go through basics of this product, and introduce all five components of Power BI (Power Query, Power Pivot, Power View, Power Map, and Power Q&A). You will see some demos and introduction about Power BI desktop, Office 365 Power BI subscription, and Power BI website, and mobile apps. You will see some basic demos of how easy to use Power BI in some scenarios.

  • Introduction to Power BI: What is Power BI?
  • Power BI Desktop; The First Experience
  • Power BI Website; You’ll Need Just a Web Browser
  • Introduction to Power BI Components: Power Query, Power Pivot, Power View, Power Map, and Power Q&A.

 

1.2: Getting Data

Getting Data is the first experience of working with Power BI. You can connect many data sources on-premises or on cloud. For some data sources, you can have a live or direct connection, for some connection can work offline. For some connections, you need a gateway or connector to be installed. In this module, you will learn everything about getting data experience of Power BI.

  • What is Power Query: Introduction to Data Mash-Up Engine of Power BI
  • Get Started with Power Query: Movies Data Mash-Up
  • Power BI Get Data from Excel: Everything You Need to Know
  • Definitive Guide to Power BI Personal Gateway and Enterprise Gateway
  • File Sources
  • Folder as a Source
  • Database Sources
  • Analysis Services Connection
  • Get Data from Azure SQL Database
  • Azure SQL Data Warehouse Source
  • Software as A Source
  • Web Source

 

 

1.3: Power Query for Data Transformation

Data analysis and BI world starts from data extraction and transformation. Power Query is the data transformation engine of Power BI. Power Query comes as part of Excel 2016, or as an add-in for Excel 2013 and 2010. Power Query is also part of Power BI Desktop. In this module you will learn about all different version of Power Query, their similarities, and differences, as well as configuration and requirements of using them.

Power Query uses a graphical user interface to apply transformations on the dataset. However Power Query works with a functional scripting language behind the scenes. The key to learn Power Query is learning the functional language called M. There are many features in M that are not available in the Power Query GUI. In this module you will learn how to understand M, and how to write M scripts even from scratch. You will learn writing custom functions in M as well as many other useful features.

The content that you will learn in this module includes but not limited to;

  • Different versions of Power Query
  • Power Query Introduction
  • Query Editor
  • Transformation GUI
  • Row Transformations
  • Column Transformations
  • Data Type
  • Adding Column
  • Text Transformations
  • Number Column Calculations
  • Date and Time Calculations
  • Data types, Lists, Records, and tables in M
  • M built-in functions
  • Generators in M
  • Writing Custom Functions
  • Error handling
  • Advanced Data Transformations with M

 

1.4: Data Modelling and DAX

Power Pivot is xVelocity in-memory data modelling engine of the Power BI. Modelling effectively is the key of high performance BI solution. In this module, you will learn basics of Power Pivot such as creating relationships, and calculated members, as well as advanced best practices and DAX expressions. DAX is Data Analytical eXpression language. DAX has similar structure to excel functions, but it is different. In this module, you will learn DAX from beginner to advance. You will learn basic functions as well as complex functions and scenarios of using them in real world challenges.

The content that you will learn in this module includes but not limited to;

  • Power Pivot xVelocity engine basics and concepts
  • Relationships in Power BI
  • Best practices
  • Introduction to DAX
  • Calculated Columns
  • Measures
  • Calculated Tables
  • Row Context vs Set Context
  • Advanced calculations using Calculate functions
  • Time Intelligence Functions
  • DAX Advanced

 

1.5: Data Visualization

Data Visualization is the front end of any BI application; this is the user view point of your system. It is critical to visualize measures, and dimensions effectively so the BI system could tell the story of the data clearly. In this module, you will learn conceptual best practices of data visualizations which are valid through all data visualization tools. You will learn Power View and Power Map skills. Power View is the interactive data visualization tool and Power Map is 3D geo-spatial data visualization tool. You will learn how to create effective charts, and dashboards using these tools as well as best practices for working with these tools. There are two versions of Power View; Power View for Excel, and Power View for SharePoint which will be covered in this module as well.

The content that you will learn in this module includes but not limited to;

  • Power BI Desktop Visualization
  • Custom Visuals in Power BI Desktop
  • Formatting Visuals in Power BI Desktop
  • Waterfall Chart
  • Built-in Charts and Graphs in Power BI
  • Sorting, Filtering, and categorization
  • KPIs
  • Maps and Geo-Spatial Visualization

 

1.6: Power BI Service, Dashboards, Q&A, and Gateways

Power BI introduced a new engine for users which focus on users who wants to play with the data more, and understand the story behind the data better. Power Q&A is question and answering engine which works based on human natural language. Power Q&A automatically will be enabled on Power BI files deployed into Power BI website, or Office 365 subscription. However, there are some tips and tricks about how to design your model to get the best type of answers through Power Q&A. In this module, you will learn best practices for modelling that effect on the response of Power Q&A.

The content that you will learn in this module includes but not limited to;

  • Power Q&A introduction
  • Design best practices for Power Q&A
  • Power BI Service
  • Dashboard vs Report
  • Gateways

 

1.7: Administration & Security

Power BI components can be deployed into different environments such as Power BI website, Office 365 subscription, or even SharePoint on-premises. In this module, we will go through deployment options, Configurations and requirements required for each environment. You will learn how to deploy your Power BI files into Power BI website. You will also learn how to build dashboards in the website, and how to work with Mobile Apps for Apple, Android and Windows Phone.

The content that you will learn in this module includes but not limited to;

  • Groups and Work Spaces
  • Sharing
  • Row Level Security
  • Schedule Refresh

In this course, you will learn all things about Power Query, from zero to hero. You will learn Power Query from basic level with getting data from different data sources. You will learn about different types of transformations available in Query Editor. You will also learn about M (Power Query Formula Language) in depth. Unique features such as error handling, generators, structured columns, custom functions and many other advanced level features of Power BI data transformations will be explored through hands-on labs and lectures. After this course you will be able to implement any types of data transformation through Power Query in Excel or Power BI.

 

2.1: Get Data

  • Different Data Sources
  • What is Power Query?
  • Power Query in Excel and Power BI
  • Basic Transformations
  • Get Data from Web

2.2: Query Editor

  • Base structures in Power Query
  • Query Editor in Details
  • Get Data from SQL Server
  • Get Data from CSV Files
  • Get Data from Excel
  • Get Data from JSON
  • Transformation Pane

2.3: Power Query Syntax

  • Data Types in Power Query
  • Query Properties
  • Transformation Steps
  • Query Operations; Duplicate, and Reference

2.4: Combine Queries

  • Merge, Joining queries
  • Append, creating a big list
  • Combine Binaries; Looping through files in a folder


2.5: Transformations

  • Column Operations
  • Row Operations
  • Filtering
  • Sorting
  • Add as new query / Drill Down

2.6: Table Transformations

  • Group By
  • Extending Group By with Expression Editor
  • Transpose
  • Pivot, Unpivot

2.7: Text Transformations

  • Split
  • Merge (Concatenate)
  • Parse
  • Extract

2.8: Numeric Transformations

  • Standard
  • Scientific
  • Statistics

2.9: More Transformations

  • Date Transformations (Year, Month, Quarter)
  • Extending Fiscal Date Column
  • Time Transformations
  • Adding Time/Date banding
  • Structured Column
  • Expand
  • Aggregate
  • Add Custom Column

2.10: Power Query Formula Language: M

  • What is M? and the importance of learning M
  • M Syntax
  • Things to Consider for M Scripting
  • Basic examples of M
  • Values in M
  • Logical Operations

2.11: Advanced M Scripting

  • #Shared Keyword; function library of Power Query
  • Parameters
  • Custom Functions
  • Error Handling in Power Query
  • Generators in Power Query: Implementing Loop Structure
  • EACH singleton function

2.12: Use Cases

  • Date Dimension with Power Query
  • Day Number of Year Custom Function
  • Looping through files in a folder with Power Query
  • Online Date Time Scheduler with Power Query

In this course, you will learn DAX from zero to hero. You will learn how to design the best model in Power BI with relationships, considering formatting and data types. You will learn about DAX which is the data modeling expression language in Power BI (and also SSAS Tabular, and Power Pivot). You will learn from Simple DAX calculations to complex expressions and calculations for solving real world challenges of a BI solution.

 

3.1: Power BI Modeling 101

  • Relationship in Power BI
  • Relationship based on multiple Columns
  • Role Playing Dimension
  • Formatting
  • Hide/Unhide
  • Hierarchy
  • Basic Calculated Column and Measure
  • Sort by Column

3.2: Introduction to DAX

  • Syntax of DAX
  • Naming in DAX
  • Logical Operations
  • Data Types in Power BI Model
  • Overview of Functions
  • Variables

3.3: Functions in DAX in Details

  • Text Functions
  • Information Functions
  • Logical Functions
  • Date and Time Functions
  • Aggregation Functions

3.4: Filter, Value, and Relationship Functions

  • ALL
  • Examples of using ALL function
  • Filter Function
  • Related
  • RelatedTable

3.5: Evaluation Contexts

  • Row Context
  • Filter Context
  • Exception for Row Context
  • Exception for Filter Context
  • Calculate Function

3.6: Time Intelligence Functions

  • Year to Date, Quarter to Date
  • Fiscal Year to Date
  • Same Period Last Year
  • Running Total
  • Rolling 12 Month Sales
  • Average 12 Month Sales
  • Rolling 6 Months
  • Flexible banding

3.7: Calculated Tables

  • Role Playing Dimension
  • Top 10 Customers
  • More Scenarios of Using Calculated Tables
  • Performance Consideration

3.8: Best Practices (Tips and Tricks)

  • Solving DAX Now and Today Time zone Issue
  • Modeling Best Practices
  • Advanced Time Intelligence
  • Advanced Relationship in DAX
  • Hierarchies in DAX

In this course, you will learn all things about data visualization in Power BI. Day starts with basics of visualizations of basic charts. You will learn best practices and considerations for coloring, choosing the right visual, numeric visualization and many other tips through the course. You will learn about custom visuals, how to build custom visuals. All tips and tricks in visualizations including filters, slicers, drill down/up, best practices of map visualizations and many other real-world examples with hands-on lab and lecture. You will leave this course with a Jedi level of visualization.

 

4.1: Visualization Basics

  • Basic Charts
  • Clustered vs Stacked
  • Quick Calcs in Power BI
  • Interaction of Visuals
  • Pages

4.2: Visualization Best Practices

  • Pie Chart; Be Careful
  • Coloring
  • Show me the Numbers
  • Which Chart is the Best?
  • Story Telling
  • Combining Visuals

4.3: Slicing and Dicing

  • Filters
  • Slicers
  • Filtering Slicers
  • Drill Down/Up

4.4: Custom Formatting Visuals

  • Labels
  • Legend
  • Color
  • Extended visualization formatting
  • Group / bin

4.5: Special Visuals

  • Waterfall Chart; Cashflow
  • Scatter Chart; Story Telling
  • Line Chart; Powerful
  • Table and Matrix with Conditional Formatting
  • R Visualization

4.6: Map Visualization

  • Default map visual
  • Filled Map; Good, bad, and the ugly
  • Shape Map
  • Custom Map Visuals
  • ArcGIS

4.7: Custom Visuals

  • Using Custom Visuals
  • SynopticPanel by SQLBI
  • Chicklet Slicer
  • 3D Map with GlobeMap Visual
  • Sunburst
  • Time Slicers

4.8: KPIs in Power BI

  • KPI Default visual
  • Dial Gauge
  • Linear Gauge
  • Bullet Chart

4.9: Building a Custom Visual

  • Basics of scripting language
  • Writing first simple custom visual
  • Components of a custom visual component

In this course, you will learn everything about Power BI Service. The difference of dashboard and reports; setting up a gateway; difference of personal and on-premises gateway. You will learn all tips and tricks of configuration and installation, and performance of gateway to connect Power BI on cloud report to on-premises data source and schedule it to refresh. You will also learn about DirectQuery and LiveQuery and the difference between these two methods. You will learn about architecting a solution with Power BI considering integration options of Power BI with other applications. You will also learn all tips about sharing and security with best practices. This course will be delivered in lecture style plus hands-on lab experiments.

 

5.1: Gateways

  • What is Gateway?
  • Two types of Gateway
  • Personal Gateway; an example
  • On-premises Gateway; not only for Power BI

5.2: On-premises Gateway in Details

  • Requirements for on-premises gateway
  • Architecture Flow diagram of the gateway
  • Installation and Configuration considerations
  • Install and Configure Gateway example
  • Data Source Definition in Power BI Service
  • Schedule Refresh

5.3: Gateway Performance

  • Performance Considerations
  • Limitations of Gateway
  • Gateway for SSAS Live data source

5.4: DirectQuery

  • Step beyond 1/10GB Limitation in Power BI
  • Limitations of DirectQuery
  • Dashboard and Report considerations for DirectQuery
  • Performance Considerations

5.5: Live Query

  • Using SSAS Live data source
  • Difference of DirectQuery and Live Query
  • Enterprise Scale architecture
  • UPN Mapping
  • Architecture of Gateway and SSAS Live Connection

5.6: Power BI Service

  • Dashboard vs Report
  • Dashboard Tiles
  • Data Classification
  • Power Q&A
  • Tips and Tricks for Power Q&A

5.7: Sharing

  • Dashboard Sharing
  • Work Groups in Power BI
  • Content Packs
  • Publish to Web
  • Comparison of all Sharing methods
  • Sharing Architecture for enterprise

5.8: Administration

  • Admin Portal
  • Set Power BI Administrator
  • Tenant Settings
  • Audit Log

5.9: Row Level Security

  • Row Level Security in Power BI; regardless of Data Source
  • Row Level Security in SSAS Live Connection
  • Dynamic Row Level Security

5.10: Integration

  • Analyze in Excel
  • Power BI Publisher for Excel
  • Reporting Services and Power BI
  • Power BI and Power Point

5.11: Real-time

  • REST API of Power BI
  • Real-time Tiles
  • Azure Stream Analytics

5.12: Architecture

  • Architecture for Enterprise Scale
  • Architecture for Reporting
  • Architecture for Self-Service
  • Licensing

You will also get:
1. Certificate of Completion from RADACAD.
2. Course Handouts, demos and case studies.

What to bring: Please bring your own personal laptop.

Date: 13 – 17 August 2018

Time: 9.00am – 4.30pm

Course Fees: SGD3,995.00
(20% discount for Early Bird sign-up by 29 July 2018)

Contact adrian.tay@saage.edu.sg for more information

Speaker Profile

Reza Rad is world well-known name in the Microsoft BI field. Reza Rad is invited speaker in world’s best and biggest SQL Server and BI conferences such as PASS Summits, PASS Rallys, SQLBits, TechEds, Ignites, and so on.  He is author of books on this topic, and he has more than 15 years’ experience in the Microsoft BI technologies. 10 years of his experience focused on training and consulting to the largest BI teams in the world. He is also Microsoft Certified Trainer for years. He is Microsoft SQL Server MVP (Most Valuable Professional) focused on BI and Data Analysis. He has been awarded MVP from Microsoft because of his dedication and expertise in Microsoft BI technologies. He is the author of Power BI online book; from Rookie to Rock Star.

Reza’s accomplishments:

  • Microsoft Regional Director
  • Power BI PUG All Star Winners
  • Microsoft Data Platform MVP (Most Valuable Professional) 2011-2019
  • Author of book; Power BI from Rookie to Rock Star
  • Author of book; Pro Power BI Architecture
  • Author of book; Microsoft Business Intelligence; A Developer’s Guide
  • Author of book; SQL Server Integration Services 2012 Cookbook
  • Speaker of well-known conferences such as Microsoft Ignite North America and New Zealand, PASS Summit and Microsoft Business Applications Summit.

What others say about the training & trainer;

“Reza completely lived up to his hype. I was completely impressed with his Power BI knowledge. I would recommend Radacad Power BI Training without hesitation.” – Daniel R Tuma // Senior Systems Analyst


“It’s incredible to see just how much PowerBI and PowerQuery radically simplified…” – Je rey Weir // Independent Consultant


“I must say your “Power BI Essentials” course has been the most comprehensive Power BI course I have found to date.” – Rob Wilby // Independent Consultant


“All-in all, I would say that I’m now a very confident Power BI user after attending Reza’s pre-con.” – Martin Catherall // Data Platform MVP


“Invest in Reza as he will not only save you a whole heap of time, but he comes up with long lasting and very powerful Power BI solutions for your business.” – Dan Cheshire // Senior Project Manager


Highly recommended!” – Sonia Buckley// Data Analyst

Payment

SAA-GE accepts the following:

  • Cash
  • NETS
  • Cheques are to be made payable to “SAA Global Education Centre Pte Ltd” for course enrolment.
  • Credit Cards (Visa/ Master Card/ UnionPay)
  • Telegraphic Transfer

SAA Global Education does not accept payment in foreign currencies.

Terms & Conditions

  1. SAA Global Education reserves the right to amend the course details and trainer(s) at our discretion.
  2. Course is subject to a minimum participation before commencement.

EVENT COUNTDOWN

WHERE TO FIND US?