Power BI - LIVE Online Training

Chapter 1 : POWER BI INTRODUCTION

• Need for Big Data and BI Technologies
• Power BI : Self-Service BI, Usage Scope
• Power BI Versus Microsoft BI (MSBI)
• Power BI Versus Tableau, QlickView
• Power BI as End-to-End BI Suite, IOT
• Power BI with Reporting Services (SSRS)
• Data Scientists and Artificial Intelligence
• Data Analysts and Business Analysts
• Power BI Job Roles and Responsibilities
• Power BI Component : Overview, Lab Plan
• MCSA Examination for Power BI: 70-778
• Power BI Architecture and EcoSystem
• Types of Reports in Real-time Usage
• Power BI Tools and Implementation Plan
• Power BI Licensing and Excel Analytics

Chapter 7 : POWER QUERY - LEVEL 1

• Power Query [M Language] - Purpose
• Power Query Usage & Operation Types
• Power Query Architecture and Usage
• QUERY Concept, Properties, Validations
• Power Query - Data Mash Up Operations
• Basic Data Types, Literals and Values
• Expressions, Primitives in M Language
• Structured Data Values in Power Query
• LIST, RECORD, TABLE in Power Query
• Power Query Connection Format Settings
• let, source, in statements in M Lang
• Functions, Parameters in Power Query
• INVOKE Functions & Execution Results
• Power BI Canvas: Edits, Applied Steps
• Frowns, Query Header Row Formatting

Chapter 13 : POWER BI CLOUD - 1

• Power BI Service (Cloud) Registration
• Power BI Service (Cloud) Architecture
• App Workspace Creation in Real-time
• Publish Reports from Power BI Desktop
• Reports, Datasets in Power BI Cloud
• Deployment Verifications in Cloud
• Creating Dashboards and Realtime Use
• Pin Visuals and Pin LIVE Report Pages
• Dashboard Creation with Tiles, Media
• Tiles - Images, Web Content, Chapters
• Subscriptions, Insights and Refresh
• Metrics, Distribution Methods, Actions
• View Related & Featured Dashboards
• Favorites, Shares, Dashboard Themes
• Performance Inspector and QR Codes

Chapter 2: POWER BI BASIC EXAMPLES

• Using Power BI Desktop Tool - Options
• Report Visuals, Fields, Pages and Filters
• Report, Data and Relationship Options
• Working with Visualizations and PBI Canvas
• Working with Enter Data, Get Data Options
• Get Data from DAT Files, Basic Reports
• Enter Data from Excel Files, Basic Reports
• Concept & Use of PBIX and PBIT Files
• Creating PBIX Files and Re-Using Reports
• Creating PBIT Files & Data Import Options
• Designing Simple / Basics Reports in PBI
• Visual Interactions in Power BI - Options
• Spotlight Options with Visuals, Real-time Use
• Slicer Visual in Power BI and Data Filters
• Reusing Slicer across multiple Report Pages
• Visual Sync Property, two directional Filters

Chapter 8 : POWER QUERY - LEVEL 2

• Power Query Transformations Categories
• Query Combine & MERGE Transformations
• Join Options in Merge Transformation
• Truncate, Replace, Split, Reduce Rows
• Manage Columns, Hide / Show Columns
• Grouping, Aggregations, Column Formats
• Transpose, Reverse Rows Transformations
• Power Query - Row Count and Replace
• Data Type Detection - Scenarios, Use
• Data Type Conversions and Value Replace
• Fill Up and Fill Down, Rename Options
• PIVOT and UNPIVOT Transformations
• MOVE, FILTER and ConvertToList()
• SPLIT, FORMAT, MERGE Transformations
• EXTRACT, PARSE, NUMBER, COMBINE
• Working with Date, Time Transformations
• Deriving Year, Quarter, Month, Day

Chapter 14 : POWER BI CLOUD - 2

• Dataset Operations @ Power BI Cloud
• Dataset Actions - Report Creation Options
• Power BI LIVE Report Edits, Downloads
• Quick Insights and PBIX Downloads
• Content Packs in PBI Cloud in Real-time
• Creating and Using Content Packs
• Organizational, Service Content Packs
• Publishing App Workspace, Updates
• Dataset Refresh Options with DataSource
• Power BI Data Gateway - Architecture
• Gateway Installation, Configuration
• Using Gateways in Power BI Cloud
• SSO - Single Sign On Security Options
• Kerberos Authentication, Refresh Modes
• Incremental Loads, Gateway Versions
• Data Gateway and Privacy Levels
• PBIEngw Service and ODG Logs, Audits

Chapter 3 : HIERARCHIES & FILTERS

• Grouping and Binning with Fields
• Bin Size and Biz Limits (Max, Min)
• Creating Hierarchies. DrillDown Reports
• Drill Thru Reports and Conditional Filters
• Expand, Expand All Down, Goto Next Level
• Drill Up, Drill Down. Exclude & Include
• See Data, Export Data, See Records Options
• Filters : Types and Usage in Real-time
• Visual Filters, Page Filters, Report Filters
• Drill-thru Filters with Hierarchy Levels
• Basic, Advanced, TOP N Filters - Usage
• Filtering at Category Level, Summary Level
• OLTP and OLAP Databases with Power BI
• On-premise & Azure Cloud Database Access
• Import and Direct Query with Power BI
• Connect LIVE options with OLAP Databases

Chapter 9 : POWER QUERY - LEVEL 3

• Intrinsic & Query Parameters, Usage
• Parameter Data Types, Default Value Lists
• Static Lists, Dynamic Lists For Parameters
• Creating Parameters in Data Set Queries
• Converting Columns to Lists in Power Query
• Dynamic Dropdowns, Multi Valued Parameters
• Data Conversions and toText() Functions
• FROWN and Token Comma Expression
• Applied Steps - Edits, New Steps
• DELETE and DELETE UNTIL END
• ENABLE LOAD and Grouping Queries
• IF..ELSE Conditions in Power Query
• Data Modeling Options - Custom Columns
• Columns From Examples, Indexed Column
• Conditional Column, Custom Functions
• Variables, Parameters - Power Query

Chapter 15 : EXCEL, SECURITY, ALERTS

• Using Excel with Power BI Reports
• Using Excel Analyzer in Power BI
• Using Excel Publisher in PBI Cloud
• Creating Datasets in Excel, Office 365
• Cell Selection and PINS in Power BI
• Excel Online Service - Edits and Pins
• Excel ODC Connections - Power Pivot
• Excel Workbooks, Reports, Dashboards
• Import and Upload Options in Excel
• Power Pivot Reports in with Excel
• Security Levels in Power BI Cloud
• Dashboard Security, Report Security
• Dataset Security - Read and Reshare
• App Workspace Security Management
• Creating, Managing Alerts in Cloud
• Row Level Security (RLS) with DAX

Chapter 4 : Power BI VISUALS - LEVEL 1

• Power BI Visualizations and Types
• Fields, Formats and Analytics Options
• Table Visuals & Properties, Filters
• Data Bar and Data Scaling Options
• Divergent Colors and Data Labelling
• Conditional Formatting, Grid Lines
• Matrix : Sub Totals, Grand Totals
• Drilldown Options : Row and Column
• Row Groups and Column Groups in Matrix
• Slicer Visual - Properties, Alignment
• Single Select and Show All Options
• Chart Reports - Types, Common Properties
• Axis, Legend, Value and Tooltip Options
• Stacked Bar Chart, Clustered Column Chart
• Stacked Bar Chart : Properties, Use
• Stacked Column Chart : Properties, Use
• Clustered Bar Chart : Properties, Use
• Clustered Column Chart : Properties
• 100% Stacked Bar and Column Charts
• Data Label Properties, Legend Properties
• Comparing Bar Charts and Column Charts

Chapter 10 : DAX FUNCTIONS - LEVEL 1

• DAX : Importance and Real-time Usage
• DAX as library of Functions, Operators
• Real-world usage of DAX with Power BI
• Real-world usage of Excel, MSBI SSAS
• DAX Architecture and Entity Sets
• Formulation Rules for using DAX
• DAX Data Types and Working Options
• Syntax, Functions, Context Options
• Concept of Measures and Calculations
• ROW Context and Filter Context in DAX
• DAX Structures and Syntax Options
• DAX Functions and Types in Real-time
• Creating and Using Measures with DAX
• Creating and Using Columns with DAX
• Vertipaq Engine & Special Characters
• Operators in DAX - Real-time Usage
• DAX Formulas Limitations in Power BI

Chapter 16: REST API, CUSTOM VISUALS

• REST APIs with Power BI Cloud Service
• Streaming Datasets Creation in Power BI
• API : Usage. SubKeys and Channels
• Push and Pubnub Datasets in Power BI
• Azure Stream for Real-time Data Reports
• Real-time Data Tracking Visuals in PBI
• Power BI Database @ Streaming Datasets
• Use Streaming Datasets in Tiles in Cloud
• Use Power BI Dashboards with REST API
• Power BI Datasets Usage in PBI Desktop
• Marketplace Visuals and PBIVIZ Files
• HeatMaps - Visual, Real-time Usage
• FlowMaps - Visual, Real-time Usage
• Table HeatMaps with Real-time Usage
• GanttChart - Visual, Real-time Usage
• Data Caching Options in Power BI
• Preview Features in Power BI Desktop
• Shape Visual, JSON and XML Option

Chapter 5 : POWER BI VISUALS - LEVEL 2

• Line Chart, Area Chart Properties, Usage
• Stacked Area Chart - Properties, Usage
• Line and Stacked Column Charts Usage
• Line and Clustered Column Chart Usage
• Visual Fields, Shared Axis, Legend
• Column Series and Column Axis in Lines
• Join Types, Lines: Round, Bevel, Miter
• Shapes and Markers in Power BI Visuals
• X Axis, Y Axis Properties, Plot Area
• Display Units, Data Colors, Shapes
• Series, Custom Series and Legends
• Ribbon Chart - Properties, Line Width
• Match Series and Plot Area Options
• Waterfall Chart - Sentiment Colors
• Breakdown Count - Decrease / Increase
• Scatter Chart - Play Area and Axes
• Axis Values, Color Saturation, Legend
• Line and Donut Charts - Properties
• Inner Radius, Details and Values
• Data Point and Series Limitations

Chapter 11 : DAX FUNCTIONS - LEVEL 2

• Data Modeling Options in DAX
• Detecting & Adding Relations for DAX
• Power BI DAX Functions - Types, Usage
• Power BI DAX Functions - Cheat Sheet
• Power BI Reports - DAX Functionalities
• Calculated Columns, Aggregated Measures
• Quick Measures in DAX - Auto validations
• PowerPivot xVelocity & Vertipaq Store
• In-Memory Processing : DAX Performance
• Date and Time & Text Functions
• Time Intelligence Functions in DAX
• Logical & Mathematical Functions
• Data Modeling with DAX. Creating Roles
• SUM and DATEDIFF Examples in DAX
• TODAY, DATE, DAY Arguments with DAX
• DIVIDE, CALCULATE, Conditions
• Using Variables in DAX, Calculations
• IF..ELSE..THEN Statement in DAX
• SELECTEDVALUE, FORMAT Functions
• RELATED & COUNTROWS in DAX
• CALCULATE, SUM, ALL Functions

Chapter 17: POWER BI REPORT SERVER

• Power BI Admin Console and Settings
• Installing Power BI Report Server
• Report Server Configuration Manager
• Configuring Power BI Report Server
• Report Server Database TempDB Database
• Webservice URL, Webportal URL - Usage
• Report Builder Installation & Usage
• SQL Server Data Tools (SSDT) Tool Usage
• Designing Paginated Reports (RDL), Tests
• Deploy to Power BI Report Server, Settings
• Data Source Connections, Report Options
• Register Power BI Report Server to Cloud
• Tenant IDs Generation and Real-time Usage
• Integrating Power BI Intranet & Cloud
• Creating Hybrid Cloud with Power BI
• Publish RDL Reports to Power BI Cloud
• Paginated Reports Vs Interactive Reports

Chapter 6 : POWER BI VISUALS - LEVEL 3

• Tree Map, Funnel and Gauge Reports
• Group and Values in Tree Map Visuals
• Min. Max Values and Goal Indicators
• Single Row Card and Multi Row Cards
• Non-Interactive Visuals in MultiRow
• Callout Values in KPI Reports and Use
• Indicator, Trend and Target Goals in KPIs
• Map Reports and Filled Map Reports
• ArcGIS Maps - Latitudes and Longitudes
• Using Buttons, Images in Power BI Canvas
• Bookmarks in Power BI Desktop - Usage
• Using Bookmarks with Buttons, Actions
• Using Bookmarks for Visual Filters
• Using Bookmarks for Page Navigations
• Using Selection Pane with Bookmarks
• Buttons, Images in Power BI Desktop

Chapter 12 : DAX FUNCTIONS - LEVEL 3

• AVERAGEX and AVERAGE in DAX
• KEEPFILTERS, COUNTROWS, RELATED
• DIVIDE with FILTER, ALLSELECTED
• ALL, MAX DAX, PARALLELPERIOD, DATEDADD
• CALCULATE with PREVIOUSMONTH
• FILTER with ROWCOUNT, USERELATIONSHIP
• TOTALYTD , TOTALQTD, TOTALMTD
• Time Intelligence with DIVIDE Functions
• Time Intelligence with CALCULATE
• DATESINPERIOD and AVERAGEX
• DAX Variables, Time Type Dimensions
• HOUR, NOW, SWITCH, TRUE
• RETURN Values with DAX Functions
• DAX Modeling Components - use
• TABLES, COLUMNS, RELATIONS
• MEASURES and HIERARCHIES
• INTELLISENSE with DAX Editors
• Comments in DAX - Syntax Options
• DISTINCTCOUNT and DIVIDE
• SAMEPERIODLASTYEAR, IF, VARIANCE

Chapter 18 : POWER BI with R

• Introduction to R Language, Usage
• R Scripts and R Visuals - Realtime Use
• MRO : Microsoft R Open - Installation
• MKL : Math Kernel Library Usage Options
• R Language Compiler from RMAN
• R Language License from Intel MKL
• Enabling R Script in Power BI
• Using R Script Visuals in Power BI
• Installing R Studio for R Scripts
• Install FORECAST Packages in R Studio
• Using R Script Editor Window in R Studio
• Writing R Scripts in Power BI. Executions
• Using R Visual in Power BI Desktop
• "game" Console in Power BI Report Fields
• Console, Date and Search Options with R
• Executing and Testing R Scripts
• Power BI Limitations with R Visuals
• Duplicate Data Removal with R Visuals
• Power BI with R for Data Scientists
• R Visuals - Limitations in Power BI

Chapter 19 : Power BI Real-time Project

This Power BI Training includes ONE Real-time Project Implementation on LIVE, 24 x 7 Climate Control Datasets with Cloud & REST API Integration. This project uses Microsoft One Drive, High Level End User Collaborations with 360 degree Dashboards. Includes Power BI Report Server Paginated Reports and Data Gateways with Azure Data Sync and ODG Logs.

 

This Power BI Project includes Power BI Cloud with Mobile Integrations and Excel Analytics with Paginated Reports and Interactive Reports.

DAY 1: DATABASE & SQL SERVER BASICS

• Introduction to Data, Databases, DBMS
• Database Basics : Types of Databases
• DB Types: OLTP, OLAP, DWH, HTAP
• Microsoft SQL Server : Advantages
• SQL, Transact SQL (T-SQL) Variants
• Microsoft SQL Server - Career Options
• SQL Developers : Job Responsibilities
• BI Developers : Roles, Responsibilities
• Data Analyst : Roles, Responsibilities
• Business Analyst Vs Data Analyst
• Course Plan, Mock Interview Details
• Real-time Project Details For Resume
• 24x7 LIVE Server Access (Online Lab)

DAY 7: JOINS AND QUERIES - Level 1

• JOINS - Types and Real-time Usage
• JOIN Types and JOIN Options in T-SQL
• INNER JOIN - Examples, WHERE, ON
• OUTER JOIN - LEFT, RIGHT, FULL
• CROSS JOIN and CROSS APPLY
• MERGE JOIN - Joining Bigger Tables
• LOOP JOIN - Joining Smaller Tables
• HASH JOIN - Joining HEAP Tables
• GROUP BY Queries and Aggregations
• GROUP BY Queries with Having Clause
• ROLLUP, CUBE Queries with Group By
• Grouping () Functions in Rollup, Cube
• Replacing Nulls: ISNULL, COALESCE

DAY 2: SQL SERVER INSTALLATION

• SQL Server 2019 Installation Guidance
• SQL Server 2017 and 2016 Installation
• Installation Pre-Requisites and Precautions
• SQL Server Management Studio (SSMS)
• SSMS - SQL Server Instance Connections
• SQL Server Versions, Editions Comparisons
• Evaluation, Developer Editions - Instances
• Instances, Instance Types & Instance IDs
• System Administrator Users @ Instance
• Windows Authentication Type and its Use
• Mixed Mode Authentication Type and Use
• Default Admin Account in SQL Server : "sa"
• SQL Server Services and Service Accounts
• File Stream and Collation in SQL Instances

DAY 8: SUB QUERIES, JOINS - Level 2

• Joining 2 Tables with Group By, Having
• Joining 3 Tables with Group By, Having
• Joining 4 Tables with Group By, Having
• Using MERGE and ROLLUP with Joins
• Sub Queries Versus Joined Queries
• Nested Queries and Joins in WHERE
• Nested Sub Queries with JOIN Options
• Cast and Convert Functions - Usage
• Date, Time Functions, Sub Queries
• DateAdd, DateDiff, Date Time Types
• Date & Time Styles Usage in Queries
• Data Formatting Options in Queries
• Using Date and Time Formats in Joins
• Join Queries - Aggregations, Formats

DAY 3: BASIC SQL QUERIES - Level 1

• DDL, DML, SELECT, DCL and TCL
• Creating SQL Databases and Tables
• CREATE, ALTER, DROP Statements
• INSERT, UPDATE, DELETE Statements
• Data Inserts, Values and Table Scan
• Data Types & their Usage (Basic Level)
• INTEGER, CHAR, FLOAT Data Types
• Character Range for Character Data
• INSERT and INSERT INTO Statements
• Single Row Inserts, Multi Row Inserts
• SELECT Statement for Table Retrieval
• WHERE Examples : =, !=, <,>, <=, >=
• AND, OR, NOT, IN, NOT IN Conditions
• BETWEEN, NOT BETWEEN Conditions

DAY 9: INDEXES, VIEWS QUERIES

• Indexes: Architecture and Types
• Clustered and Non Clustered Indexes
• Included and ColumnStore Indexes
• FILTERED and COVERING Indexes
• UNIQUE Indexes, Query Optimizer
• LIVE Online Indexes in Real-time
• B Tree Structures and IAM Pages
• Tuning Joined Queries, Conditions
• Views on Tables - SCHEMABINDING
• ENCRYPTION and CHECK OPTION
• Orphan Views - Real-world Solutions
• Cascaded Views, Encrypted Views
• System Views for Metadata Access
• Indexed Views / Materialized Views

DAY 4: BASIC SQL QUERIES - Level 2

• CHAR Versus VARCHAR Data Types
• VARCHAR & NVARCHAR Data Types
• GO Statement, SQL BATCH Concept
• BIGINT, BIT, SQL_Variant Data Types
• IS NULL, NOT NULL. LIKE, NOT LIKE
• ORDER BY with ASC, DESC Options
• DISTINCT, TOP and COUNT() Options
• FETCH, OFFSET, NEXT ROW Options
• UNION, UNION ALL and Sub Queries
• Single Quotes, Double Quotes, Aliases
• UPDATE : Conditional & Unconditional
• DELETE and TRUNCATE Commands
• ALTER Command   DROP Command

DAY 10: Functions, Procedures - 1

• Scalar Value Returning Functions
• Inline Table Functions, Dynamic Joins
• Multi-Line Table Functions Usage
• Table Variables and Table Data Type
• Variables & Parameters in SQL Server
• OBJECTID, OBJECTNAME Functions
• System Functions & Metadata Access
• Stored Procedures - Purpose, Usage
• IF .. ELSE and ELSE IF Conditions
• Using Procedures with Parameters
• Recompilation of Stored Procedures
• Sp_help, Sp_helpdb and sp_helptext
• Sp_recompile, sp_pkeys System SPs

DAY 5: SERVER, DB ARCHITECTURE

• Client Server Architecture, TDS Packets
• SNAC and OLE DB Command for TDS
• TDS Packets and SQL Query Processing
• SQL Server Architecture Components
• Protocols, SQL Native Client (SNAC)
• Query Optimizer (QO) and SQL Manager
• Storage Engine, File and DB Manager
• Transaction Manager and Lock Manager
• Buffer Manager, SQL OS and IO Buffer
• Synchronization and Thread Scheduler
• Logs, Checkpoints, Virtual Log File (VLF)
• DB Data Files (mdf) and Log Files (ldf)
• Primary/Secondary Files (ndf), Filegroups
• Write Ahead Log (WAL) and Lazy Writer
• Log Sequence Number (LSN), Mini LSN

DAY 11: TRIGGERS, TRANSACTIONS

• Triggers - Purpose, Types Of Usage
• DML Triggers - Events, Practical Use
• FOR/AFTER Triggers - Real time Use
• INSTEAD OF Triggers - Real time Use
• INSERTED, DELETED Memory Tables
• Enable Triggers and Disable Triggers
• Database Level DDL Triggers - Usage
• Server Level DDL Triggers - Usage
• COMMIT and ROLLBACK Statements
• EXPLICIT & IMPLICIT Transactions
• Autocommit Transaction, ACID Options
• Conditional Commits and Rollbacks
• Open Transactions and Query Impact
• Query Blocking Scenarios @ Real-time
• NOLOCK and READPAST Lock Hints

DAY 6 : CONSTRAINTS Level 1

• Constraints, Keys - Table Data Integrity
• NULL, NOT NULL Property on Tables
• UNIQUE KEY Constraints: Importance
• PRIMARY KEY Constraint: Importance
• FOREIGN KEY Constraint: Importance
• REFERENCES, OLTP Relational Tables
• CHECK, DEFAULT Constraints Usage
• Candidate Keys and Identity Property
• Normal Forms : 1 NF, 2 NF and 3 NF
• BCNF, 4 NF, EKNF and ETNF Designs
• Schemas - Purpose. Using DBO schema
• User Defined Schemas & Data Transfer
• Database Diagrams, PK-FK Base Tables
• Local Temporary Tables and Sessions
• Global Temporary Tables and Connections

• Writing Queries : Joins, Group By
• Joining 2 and More Tables, Formatting
• Writing Sub Queries with Joins
• ROW_NUMBER and RANK Functions
• DENSE_RANK, PARTITION BY Queries

• Excel File - Imports and Exports
• Excel Pivot Tables and Pivot Charts
• Excel ODC Connections in Real-time
• CASE and IIF() Conditional Queries
• String Functions with JOIN Queries