Power BI Training Classes

Complete Real-time and Practical Power BI Training with Real-time Scenarios. Power BI is a cloud-based, elegant end-to-end business analytics tool that enables anyone to visualize, analyze, forecast any type of data with greater speed, efficiency, and understanding.

This Power BI Classroom Training includes EVERY detail of each Power BI Visual, Usage and Properties as per the latest versions. This Power BI course includes On-premise and Cloud Data Access, REST API, Azure Stream and R Integration including Data Modelling and ETL Techniques with Basic to Advanced Power Query (M Language), DAX Language Functions, Power BI Dashboards, Streaming Datasets, App Workspace, Content Packs, Data Gateways, Alerts, Power BI Report Server Components, Power BI Mobile Repots, Excel Integration, Excel Analysis, KPIs for Big Data Analytics, Microsoft OneDrive, Google Big Query Datasets and Facebook are also a part of this Power BI Classroom Training course (Version: 2.62.5222.582) in addition to Mock Interviews, Resume Guidance, Concept wise Interview FAQs and ONE Real-time Project.

Power BI Training Plans

  PLAN A PLAN B PLAN C
Power BI Training Course For Data Analysts
Business Analyst
Power BI Dev Power BI Dev
with SQL, T-SQL
Total Duration 2 Weeks 3.5 Weeks 6 Weeks
Power BI Report Design Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
Power BI Desktop and Visuals Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
Python, Google, Facebook Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
R, REST APIs, Gateways Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
Custom Visuals, MarketPlace Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
Power BI Cloud, Excel Analysis Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
Power BI Mobile Reports Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
Dashboards, AppWork Space Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
Data Modelling with Power Query Check-Symbol-for-No Check-Symbol-for-Yes Check-Symbol-for-Yes
Data Modelling with DAX Check-Symbol-for-No Check-Symbol-for-Yes Check-Symbol-for-Yes
Basic to Advanced DAX and RLS Check-Symbol-for-No Check-Symbol-for-Yes Check-Symbol-for-Yes
Real-Time Project in Power BI Check-Symbol-for-No Croos-symbol-for-Yes Croos-symbol-for-Yes
SQL Database Concepts Check-Symbol-for-No Check-Symbol-for-No Check-Symbol-for-Yes
SQL Server T-SQL Queries Check-Symbol-for-No Check-Symbol-for-No Check-Symbol-for-Yes
Joins, Query Tuning Check-Symbol-for-No Check-Symbol-for-No Check-Symbol-for-Yes
Sub Queries, Stored Procedures Check-Symbol-for-No Croos-symbol-for-No Check-Symbol-for-Yes
Normal Forms and RAID Croos-symbol-for-No Croos-symbol-for-No Check-Symbol-for-Yes
End-to-End Project, Resume Croos-symbol-for-No Croos-symbol-for-No Check-Symbol-for-Yes
Total Course Fee INR 4,000/- INR 6,000/- INR 10,000/-

Trainer & Profile : Mr. Sai Phanindra T

Timings (IST) Course Start Date
Schedules for Power BI Training
1 9 AM - 10 AM Oct 2nd Register
2 7:30 - 9:30 AM (W) Nov 3rd Register
3 9:30 AM - 11:30 AM (W) Oct 6th Register
Schedules for SQL Server & T-SQL Training
1 6:30 AM to 7:45 AM Oct 17th Register
2 9 AM to 10:15 AM Started Register
3 11:15 AM to 12:15 PM Sep 21st Register
4 5:30 PM to 6:30 PM Oct 4th Register
5 7:30 AM to 9:30 AM (Wk) Oct 6th Register

If above schedules does not work for you, please register for Power BI Training Videos

ALL SESSIONS ARE PRACTICAL, REAL-TIME.

 

TRAINING HIGHLIGHTS

Custom Visuals Power BI Mobile
Big Data Sources R, Python, JSON
Excel Analysis Excel Online
Google Big Query Report Server
Gateways, Rest API Power BI Admin

Power BI Training Course Contents:

Module I: Power BI Basics, Visuals

Module II: Power BI Service (Cloud)

Module III: ETL and Data Modelling

Chapter 1 : POWER BI INTRODUCTION

  • Power BI Introduction - Target Users
  • Need for Big Data and BI Technologies
  • Purpose of BI and Power BI Suite of Tools
  • 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, Cloud
  • Microsoft Data Platform with Power BI
  • Data Collaboration, Visualization, 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

Chapter 9 : 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 17 : 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 2: POWER BI ARCHITECTURE

  • Power BI Architecture - Data Components
  • Power BI - Integration in Real-time
  • Interactive Reports, Paginated Reports
  • Analytical Reports and Mobile Reports
  • Power BI Licensing Plans and Pricing
  • Power BI Ecosystem and BI Components
  • Power BI Service (Cloud) and Azure Cloud
  • Power BI Report Server and Report Builder
  • Power BI Mobile Report Publisher Tool
  • Power BI with Excel - Excel Analyzer Tool
  • Power BI Desktop & Excel Publisher Tools
  • Power BI Desktop Installations - 32bit, 64bit
  • Understanding Power BI Desktop Tool
  • Power BI Canvas, Visualizations and Filters
  • Fields, Reports, Data, Relationship Screens
  • Need for Power Query and DAX Expressions
  • Visualizations in Power BI - Overview

Chapter 10 : 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 18 : 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 3: 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 11 : 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 19 : POWER QUERY - LEVEL 3

  • Creating Parameters in Power Query
  • Intrinsic & Query Parameters, Usage
  • Parameter Data Types, Default Value Lists
  • Static Lists, Dynamic Lists For Parameters
  • Creating Parameters in Power Query Edits
  • Creating Parameters in Data Set Queries
  • Converting Columns to Lists in Power Query
  • Reports with Range Values - Bookmarks
  • Dynamic Dropdowns with PBIX Reports
  • Parameters with PBIT Reports, Prompts
  • Report Design with Dynamic WebPages
  • Creating Lists and Tables in Power BI
  • Data Conversions and toText() Functions
  • Multi Valued Parameters. Table Columns
  • Dynamic Table Expressions and Functions
  • Report Design with Structured Table Values

Chapter 4 : HIERARCHIES and FILTERS

  • Working with Hierarchies in Power BI
  • Creating Hierarchies - Options and Usage
  • Data Drill Options with Power BI Hierarchies
  • Data Explore Options - Spotlight and Focus
  • Expand, Expand All Down, Goto Next Level
  • Drill Up, Drill Down and Parent Aggregations
  • See Data, Export Data, See Records Options
  • Include and Exclude with Multi-Field Values
  • Filters and Real-time Usage with Power BI
  • Export Options to CSV Files and Data Drills
  • Using Report Filters, Page Filters in PBIX
  • Using Drill-down Filters and Visual Filters
  • Basic, Advanced, TOP N Filters - Usage
  • Apply and Clear Options with Value Filters
  • Filtering at Category Level, Summary Level
  • Filtering Techniques in Power BI Visuals
  • Usage of Filters in Real-world - Synopsys
  • Cortana and Q & A Features in Power BI

Chapter 12: 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 20 : POWER QUERY - LEVEL 4

  • Common Query & DataSet Options
  • Creating Query Duplicates and use
  • Creating Query References and Use
  • FROWN and Token Comma Expression
  • Applied Steps - Edits and Data Inserts
  • New Step Inclusions using Power Query
  • DELETE and DELETE UNTIL END
  • ENABLE LOAD Options in Power Query
  • Grouping Queries - Usage in Realtime
  • Query Transforms, Sort Direction, Statistics
  • IF..ELSE Conditions in Power Query
  • Data Modeling Options with Power Query
  • Modeling Operations - Custom Columns
  • Columns From Examples in Real-time
  • Conditional Column, Indexed Column
  • Variables, Parameters - Power Query
  • Date and Time Columns. String Functions
  • Custom Functions in Power Query M Lang

Chapter 5 : BIG DATA with POWER BI

  • Database Properties, Types of Databases
  • OLTP and OLAP Databases with Power BI
  • DWH and HTAP Databases with Power BI
  • On-premise Database Access with Power BI
  • Cloud Database Access in Power BI Visuals
  • Import Option with Database - Advantages
  • Direct Query Option in Real-time, Usage
  • Options with Small and Big Databases
  • Accessing Azure SQL Database in Power BI
  • Reports with Azure SQL DB Connections
  • SQL Queries and Power BI Data Connections
  • Direct Query Limitations in Power BI
  • Query Storage Mode in Power BI Desktop
  • Relation Detection in Power BI Desktop
  • Relationship Management, Inactive Relations
  • 1 - 1, 1 - Many, Many - 1 Table Relations
  • Defining Relations for Visual Interactions

Chapter 13: 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 21 : 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 6 : 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 14 : POWER BI WITH SSAS, SSRS

  • MSBI SSRS Integration with Power BI
  • SSRS Report Portal URL to Power BI Cloud
  • Power BI KPI Reports Vs SSRS KPI Reports
  • Converting and Working with Mobile Reports
  • Report Builder Reports to Powert BI
  • IMPORT and CONNECT LIVE Options
  • Query Mode and Complete Cube Browse
  • Using MDX Queries with Power BI Queries
  • MDX SELECT and Perspective Access
  • KPIs and MDX Expressions with Power BI
  • Using KPIs in Power BI Reports, Usage
  • Cube & Perspective Browsing in PBI
  • MDX Queries and Filters with Power BI
  • SSAS MDX Scripts with Power BI Visuals
  • Using KPI Fields in Power BI Visuals
  • KPI Visual : Goals, Status and Trends
  • SSAS Tabular Mode Options in Power BI
  • MDX @ Tabular Mode OLAP in Power BI
  • DAX @ Tabular Mode OLAP in Power BI
  • DAX Filters in Tabular Mode SSAS
  • MDX, DAX Tabular Mode OLAP in Power BI

Chapter 22 : 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 7 : 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 15 : 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 23 : DAX FUNCTIONS - LEVEL 3

  • AVERAGEX and AVERAGE in DAX
  • KEEPFILTERS and CALCUALTE
  • COUNTROWS and FILTER Functions
  • Using RELATED with FILTER Function
  • DIVIDE with FILTER, ALLSELECTED
  • Using ALL, MAX DAX Functions in FILTER
  • Using ALLSELCTED and Differences
  • Using Numeric and Text Conditions
  • PARALLELPERIOD, DATEDADD in DAX
  • CALCULATE with PREVIOUSMONTH
  • FILTER Conditions with ROWCOUNT
  • USERELATIONSHIP Function in DAX
  • TOTALYTD , TOTALQTD, TOTALMTD
  • Time Intelligence with DIVIDE Functions
  • Time Intelligence with CALCULATE
  • DATESINPERIOD and AVERAGEX
  • EARLIER for Time Based Comparisons
  • Using Variables in DAX Computations
  • Time Type Dimensions - DAX Expressions
  • Considerations for Quick Measures

Chapter 8 : 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 16 : 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

Chapter 24 : DAX FUNCTIONS - LEVEL 4

  • DAX Variables, Joins and Usage
  • Value Assignments with DAX
  • CONCATENATE Function in DAX
  • HOUR, NOW, SWITCH, TRUE
  • RETURN Values with DAX Functions
  • Numeric and Other DAX Options
  • DAX Modeling Components - use
  • TABLES, COLUMNS, RELATIONS
  • MEASURES and HIERARCHIES
  • INTELLISENSE with DAX Editors
  • Comments in DAX - Syntax Options
  • DISTINCTCOUNT and DIVIDE
  • SAMEPERIODLASTYEAR and IF
  • Revenue and Budget with DAX
  • VARIANCE Calculations in DAX
  • TOTALYTD, YTD, QTD and MTD

Note : Above course curriculum applicable for registrations from Sep 15th, 2018.

SQL Server & T-SQL Queries (Applicable for Power BI Training : Plan C)

Module I: SQL Basics, SQL Server Concepts

Module II: T-SQL Queries & Programming Basics

DAY 1: DATABASE & SQL SERVER BASICS

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

DAY 8: JOINS AND QUERIES - LEVEL 1

  • JOINS - Types and Real-time Usage Examples
  • JOIN Types and JOIN Options with SQL Server
  • CROSS JOIN - Examples and WHERE Conditions
  • INNER JOIN - Examples with WHERE and ON
  • OUTER JOIN - LEFT, RIGHT and FULL
  • Comparing OUTER JOINS and Types, Examples
  • Comparing INNER JOIN & OUTER JOIN, Examples
  • Joins with Constraints and Relational Columns
  • Joining unrelated Tables : Real-time Scenarios
  • SELF JOINS and NATURAL JOINS with SQL Server
  • MERGE JOIN, LOOP JOIN and HASH JOIN Options
  • Join Options - Query Performance Advantages
  • Joining more than 2 Tables with Examples
  • Join Query Considerations for Big Databases

DAY 2: SQL SERVER INSTALLATION

  • SQL Server 2017 Installation Concepts
  • SQL Server 2016 Installation Concepts
  • SSMS Tool: SQL Server Management Studio
  • SQL OPS (SQL Operations Studio) Tool and Types
  • SSMS - Installation in Windows Operating System
  • SQL OPS - Installation in Linux and MAC OS
  • SSMS and SQLOPS Tools - Server Connection Tests
  • Version and Editions - Features Comparison Charts
  • Instance, Types and Collation Options with SQL Server
  • Authentication, Service and FileStream Properties
  • Adding Administrator Users for SQL Server Instance
  • Windows Authentication Type - Realtime Use
  • Mixed Mode Authentication Type - Realtime Use
  • Common Installation Errors in SQL Server - Ref Doc
  • Older Version Installations of SQL Server- Ref Doc

DAY 9: SUB QUERIES, JOINS - LEVEL 2

  • GROUP BY Queries for Data Reporting
  • GROUP BY, HAVING and GROUPING() Keywords
  • ROLLUP and CUBE Functions for Data Reports
  • Sub Totals, Grand Totals and Aggregate Options
  • Replacing Nulls: ISNULL(), COALESCE() Functions
  • CASE, IIF(), CHOOSE() and Aggregates
  • JOINS with Group BY & HAVING Conditions
  • JOINS with Group BY & ROLLUP Options
  • ROLLUP & CUBE with GROUPING, Row Status
  • Query Execution Order with Join Queries
  • Working with Sub Queries on Tables, Joins
  • Nested Queries and Nested Sub Queries
  • Sub Queries Versus JOIN Queries - Conditions
  • Sub Queries with JOIN Queries - Conditions
  • Using Sub Queries with Join Options

DAY 3: BASIC SQL QUERIES - LEVEL 1

  • DDL, DML, SELECT, DCL and TCL in SQL Queries
  • Creating Databases and Tables in SQL Server
  • CREATE, ALTER, DROP Statements - Basic Level
  • INSERT, UPDATE, DELETE Statements on Tables
  • Data Insert Statements - SELECT - Modify
  • Data Types with Table Columns, Basic Usage
  • INTEGER, CHAR and FLOAT Data Types in SQL
  • Character Range for Character Data - Limits
  • INSERT and INSERT INTO Statements in SQL
  • Single Row Inserts and Multi Row Inserts
  • SELECT Statement for Table Data Retreival
  • WHERE Examples with =, !=, <, >, <=, >=
  • AND, OR, NOT, IN, NOT IN Conditions in SQL
  • BETWEEN, NOT BETWEEN Conditions in SQL

DAY 10: INDEXES & QUERY TUNING

  • Indexes: Architecture, Purpose and Types
  • Clustered Indexes - Architecture, Pages
  • Non Clustered Indexes - Architecture, Pages
  • Defining Clustered Indexes on Table Columns
  • Defining NonClustered Indexes on Table Columns
  • INCLUDED Indexes and COLUMNSTORE Indexes
  • FILTERED Indexes and COVERING Indexes
  • ONLINE Indexes and UNIQUE Indexes : Examples
  • PRIMARY KEY and Clustered Indexes
  • UNIQUE KEY and Non Clustered Indexes
  • Composite Indexes and Real-time Usage
  • B Tree Structures : Root, IAM Pages, Branches
  • Query Optimizer (QO) Component in SQL Server
  • LIVE Query Statistics, Index Selectivity Options

DAY 4: BASIC SQL QUERIES - LEVEL 2

  • Comparing CHAR and VARCHAR Data Types, Sizing
  • VARCHAR and NVARCHAR Data Types in Tables
  • GO Statement and SQL "BATCH" Concepts
  • BIGINT, BIT, SQL_Variant Data Types and Use
  • IS NULL, NOT NULL. LIKE, NOT LIKE Operators
  • ORDER BY with ASC, DESC in SQL Queries
  • DISTINCT, TOP, COUNT() Statements in SQL
  • FETCH, OFFSET, NEXT ROW Pagination Options
  • UNION, UNION ALL and Sub Queries in SQL
  • Single Quotes, Double Quotes and Aliases
  • UPDATES : Conditional and Unconditional, SET
  • DELETE and TRUNCATE Commands : Differences
  • ALTER Command and DROP Command with Tables
  • Client - Server Architecture and TDS Packets Size

DAY 11: VIEWS, FUNCTIONS

  • Scalar Value Returning Functions Examples
  • Inline Table Functions, Dynamic Joins
  • Multi-Line Table Functions and Usage
  • Table Variables and Table Data Type
  • Variables and Parameters in SQL Server
  • System Functions, Date-Time Functions
  • String Functions and PIVOT Operation
  • CAST, CONVERT and Date / Time Sytles
  • Views on Tables - SCHEMABINDING Option
  • ENCRYPTION & WITH CHECK OPTION
  • Orphan Views and Realworld Solutions
  • Cascaded Views and Encrypted Views
  • System Views for Metadata Access
  • Indexed Views / Materialized Views

DAY 5: SQL SERVER, DB ARCHITECTURE

  • SQL Server Architecture, Database Architecture
  • Network Protocols Purpose, SQL Native Client (SNAC)
  • Query Processor : Parser and Compiler Components
  • Query Optimizer, SQL Manager, Database Manager
  • Storage Engine, File Manager, Transaction Manager
  • Lock Manager, SQLOS and SQL Buffer Manager
  • Data Files, Log Files, Pages and Extents - Types
  • MDF, NDF, LDF Files. FileGroups Usage in Tables
  • Log Files: Checkpoints and Virtual Log File (VLF)
  • Write Ahead Log (WAL) and Lazy Writer Components
  • Log Sequence Number (LSN), MINI LSN Processes
  • Local Temporary Tables and Usage Examples
  • Global Temporary Tables and Usage Examples
  • Session Scope and Connection Scope Examples

DAY 12: STORED PROCEDURES (SPs)

  • Stored Procedures - Purpose and Types
  • Real-time Use with Stored Procedures
  • Using Stored Procedures : Data Validations
  • Using Stored Procedures for Stored Queries
  • Variables, Parameters and Data Types
  • Stored Procedure Executions - Options
  • Precompilation and Recompilation Options
  • IF.. ELSE, IF .. ELSE IF, IIF Conditions
  • PRINT Statements with Stored Procedures
  • Important System Stored Procedures
  • OUTPUT Parameters with Stored Procedures
  • Batch Executions with Stored Procedures
  • MERGE Statement with Stored Procedures
  • MERGE - MATCHED, NONMATCHED Data, DMLs

DAY 6, 7 : CONSTRAINTS & KEYS

  • Constraints and Keys - Table Data Integrity
  • NULL, NOT NULL Property on Table Columns
  • UNIQUE KEY Constraints: Importance, Use
  • PRIMARY KEY Constraint: Importance, Use
  • FOREIGN KEY Constraint & REFERENCES
  • CHECK Constraint : Importance, Conditions
  • DEFAULT Constraint : Importance, Values
  • Identity Property : SEED, INCREMENT Values
  • Using multiple Constraints in Table : Usage
  • Candidate Keys Concept in SQL Server
  • Constraints Priority and Real-time Use
  • Normal Forms - First Normal Form & Atomocity
  • 2 Normal Form - Functional Dependancy
  • 3 NF, BCNF, EKNF, 4 NF in Real-time
  • OLTP (Relational) Database Design Patterns
  • Cascading : ON UPDATE, ON DELETE CASCADE
  • Composite Primary Keys and Practical Use
  • Self Referencing Keys and Practical Use
  • Schemas - Purpose, Creation and Usage
  • Database Diagrams, PK-FK Base Tables
  • Data Imports with Excel - Schema Reference

DAY 13, 14: TRIGGERS & TRANSACTIONS

  • Triggers - Purpose and Types Of Usage
  • DML Triggers - Events, Practical Use
  • FOR / AFTER Triggers - Realtime Use
  • INSTEAD OF Triggers - Realtime Use
  • INSERTED & DELETED Memory Tables
  • Using Triggers for DML Audits - Examples
  • Using Triggers to prevent DMLs - Examples
  • Enabling, Disabling Triggers. NOCOUNT ON
  • Database Level Triggers, DDL Operations
  • Server Level Triggers, DDL Operations
  • Transactions - Basics, ACID Properties
  • COMMIT, ROLLBACK Concepts in T-SQL
  • EXPLICIT, IMPLICIT Transactions - Usage
  • AUTOCOMMIT Transactions - Advantages
  • IMPLICIT_TRANSACTIONS @ Session Level
  • OPEN Transactions and Query Blocking
  • NOLOCK, READPAST Lock Hints in T-SQL
  • @@TRANCOUNT and Conditional Commits
  • Conclusion: Need for SQL Server T-SQL @ Power BI
Real-time Case Study on Medicare Database for Joins, Queries and Reports
 
EVERY SESSION IS COMPLETELY PRACTICAL. REAL-TIME. TASKS, MATERIAL, LAB WORK for EVERY SESSION. Register Today
 

Who can benefit from this Power BI Classroom Training course?

Complete Real-time and Practical Power BI Training with Real-time Scenarios. Power BI is a cloud-based, elegant end-to-end business analytics tool that enables anyone to visualize, analyze, forecast any type of data with greater speed, efficiency, and understanding. It connects users to a broad range of data through easy-to-use dashboards, interactive reports and compelling visualizations for your day to day corproate business data needs!

Job-Oriented Real-time Training @ SQL School Training Institute - Trainer : Mr. Sai Phanindra T