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.

Power BI Training with Advanced Power Query (M Language) and DAX Language Expressions. This Power BI Training course includes End to End Data Visualizations and Big Data Analytics with Basic to Advanced Power Query, Basic to Advanced DAX Language included Big Data Analytics for Business Analysts. Power BI Service For Data Refresh, On-premise Data Gatewways, Excel Power Query Data Analytics with Power BI Service (Cloud) and Mobile Reports on Google Analytics are also included in this Power BI Online Training course along with Mock Interviews, Resume Guidance, Concept wise Interview FAQs and Real-time Project

Power BI Training Plans

  PLAN A PLAN B
Description Power BI T-SQL + Power BI
Duration 3.5 Weeks 6 Weeks
Real-Time Project Check-Symbol-for-Yes Check-Symbol-for-Yes
Resume, Job Support Check-Symbol-for-Yes Check-Symbol-for-Yes
Power BI Desktop & REST API Check-Symbol-for-Yes Check-Symbol-for-Yes
Power BI Service & Report Server Check-Symbol-for-Yes Check-Symbol-for-Yes
Excel, M Language & DAX Croos-symbol-for-Yes Croos-symbol-for-Yes
Basic SQL, SQL Server, T-SQL Croos-symbol-for-No Check-Symbol-for-Yes
Queries, Joins, SPs, Tuning Croos-symbol-for-No Check-Symbol-for-Yes
Data Modelling, Azure DB Reports Croos-symbol-for-No Check-Symbol-for-Yes
End-to-End Project, Resume Croos-symbol-for-No Check-Symbol-for-Yes
Total Course Fee INR 6,000/- INR 10,000/-

Trainer & Profile : Mr. Sai Phanindra T (12+ Yrs of Exp)

Timings (IST) Free Demo Start Date  
Schedules for Power BI Training
1 9 AM - 10 AM Sep 18th Sep 19th Register
2 6:15 PM - 7:30 PM Sept 26th Sept 27th Register
3 9:30 AM - 11:30 AM (Weekend) Aug 18th Aug 19th Register
Schedules for SQL Server & T-SQL Training
1 6:30 AM to 7:45 AM Sep 2nd Sep 3rd Register
2 9 AM to 10:15 AM Aug 28th Aug 29th Register
3 10:30 AM to 11:30 AM Aug 18th Aug 18th Register
4 11:30 AM to 12:45 PM Sep 4th Sep 5th Register
5 5 PM to 6 PM Aug 21st Aug 22nd Register
6 6:15 PM to 7:30 PM Aug 28th Aug 29th Register

If above schedule does not work for you, opt for Power BI Training Videos

Power BI Training Highlights : Basic--Advanced DAX Power Query (M Lang) LIVE Edits, RLS Managed Gateways Azure Integration SSRS,SSAS in Power BI R Language Resume Guidance

Power BI Training Course Contents:

Module I: Power BI Basics, Visuals

Module II: ETL and Data Modelling

Module III: Power BI Service (Cloud)

Chapter 1 : INTRODUCTION TO POWER BI

  • Power BI Introduction - Target Users
  • Need for Big Data and BI Technologies
  • Purpose of BI and Power BI Suite of Tools
  • Power BI as a Self-Service BI, Scope of Usage
  • Comparing Power BI with Microsoft BI (MSBI)
  • Comparing Power BI with Tableu, QlickView
  • Power BI as an End-to-End BI Suite Of Tools
  • Microsoft Data Platform Technologies with BI
  • Data Colloboration, Visualization and IOT
  • Power BI with Reporting Services (SSRS)
  • Power BI For Data Scientists and AI Scope
  • Power BI For Data Analysts, Business Analysts
  • Power BI Job Roles and Responsibilities
  • Power BI Component : Overview, Lab Plan
  • MCSA Examination for Power BI: 70-778

Chapter 9 : Power Query & M Language - 1

  • Power Query [M Language] - Purpose
  • Power Query Usage & Operation Types
  • Power Query Architecture and Usage
  • QUERY Concept, Properties, Validations
  • Power Query for Data Mashup Operations
  • Basic Data Types, Literals and Values
  • Expressions and Primitives in M Language
  • LIST : Syntax, Examples and Usage
  • RECORD : Syntax, Examples and Usage
  • TABLE : Syntax, Examples and Usage
  • Power Query Connection Formats, Settings
  • let, source & in statements in M Lang
  • Power BI Canvas: Edits, Applied Steps
  • Queries and Applied Steps, Edit Queries

Chapter 17 : Power BI Service (Cloud) - 1

  • Power BI Cloud & Power BI Workspace
  • App Workspace Creation in Real-time
  • Publish Reports from Power BI Desktop
  • Reports and Datasets in Power BI Cloud
  • Pin Visuals and Pin LIVE Report Pages
  • Dashboard Creation and Tiles, Media
  • Images, Web Content, Videos, Q & A
  • Mobile View, Web View, QR Codes, Shares
  • Enabling Interactive Visuals. Embed Codes
  • Report Shares and Dashboard Shares
  • App Publish Options and App Updates
  • Power BI Report Edits in Cloud, Visuals
  • Download PBIX Reports from Cloud
  • Sharepoint Integration, PPTX and Excel

Chapter 2: ARCHITECTURE, INSTALLATION

  • Power BI Architecture - in Detail, Components
  • Power BI - Integration Options in Real-time
  • Types of Reports in Power BI - Interactive
  • Analytical, Paginated and Mobile Reports
  • Power BI Licensing Plans & Pricing Options
  • Power BI Ecosystem Components - Purpose
  • Need for Power BI Service (Cloud) and Azure
  • Power BI Report Server and Report Builder
  • Power BI Mobile Report Publisher Tool Usage
  • Power BI with Excel - Excel Analyzer Tool
  • Power BI Desktop & Excel Publisher Tools
  • Power BI Desktop - Installation, Requirements
  • Understanding Power BI Desktop Tool - usage
  • Power BI Canvas, Visualizations and Fitlers
  • Fields, Reports, Data, Relationship Screens
  • Need for Power Query and DAX Expressions
  • Various Viusalizations in Power BI - Overview

Chapter 10 : Power Query & M Language - 2

  • Data Sources with Excel, File Formats
  • Data Sources with Database Connections
  • BLANK Data Sources Creation & Scope
  • Creating LIST, RECORD, TABLE Queries
  • Functions in Power Query and Usage
  • Defining Functions and Invoke Options
  • Mashup Operations in Power Query Editor
  • Row Filters, Column Filters, Renames
  • Promoting Headers and Query Settings
  • MERGE Queries For Combining Queries
  • Inner Join and Left/Right Outer Joins
  • Left Anti Join, Right Anti Join, Full Join
  • UNION All, Group By and Aggregations
  • Close and Apply Options. Data Imports
  • Binning with Groups and Query Formats
  • SubString Functions, Splits (Row, Column)
  • Parsing XML and JSON Files. Formatting

Chapter 18 : Power BI Service (Cloud) - 2

  • Dashboard Properties and Security
  • Report properties and Security
  • Dataset properties and Security
  • Report Insights, Related Items & Metrics
  • Quick Insights. Publishing App Workspace
  • Power BI LIVE Report Edits, Downloads
  • Content Packs : Creation and Use
  • Organizational and Service Level
  • Content Packs from Azure MarketPlace
  • Gateway Clusters - Installation
  • Personal and Enterprise Gateways
  • NT SERVICE\PBIEgwSERVICE Account
  • Dataset Refresh Options and Schedules
  • Azure Databus Integration, ODG Logs

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 and 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: POWER QUERY & M LANG. - 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, toText() Functions
  • Multi Valued Parameters. Table Columns
  • Dynamic Table Expressions and Functions
  • Report Design with Structured Table Values

Chapter 19 : Excel with Power BI

  • Using Excel with Power BI Reports
  • Using Excel Analyser in Power BI
  • Using Excel Publisher in Cloud
  • Creating Datasets with Excel, Office 365
  • Excel Uploads & Cloud Imports, Office 365
  • Cell Selection and PINS in Power BI
  • Excel Online Service - Edits and Pins
  • Excel ODC Connections and Real-time Use
  • Excel Power Pivot and Power BI Cloud
  • Excel Drilldowns and Drillthru Reports
  • Create Key Performance : KPI Reports
  • Excel for Big Data Analysis, Reporting
  • Worksheets and Dashboards with Excel
  • Power Pivot Reports in with Excel
  • Power View Report Options in Excel

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

Chapter 12 : Power Query & M Language - 4

  • New Queries from Dataset Fields, Usage
  • Removing Duplicate Rows and Columns
  • Column Delimters and Field Formatting
  • Change Type and Transform Operations
  • Replace Values & Errors, Transpose
  • Group By Aggregations, Fill, Reverse Rows
  • Format Options in M Language. Bookmarks
  • Pivot and Unpivot Options in Power Query
  • Data Type Conversions & Format Options
  • Data Modeling Options with Power Query
  • Modeling Operations - Custom Columns
  • Query Transforms, Sort Direction, Statistics
  • Enabling / Disabling Loads. ToList Options
  • Creating Parameters & Variables. Functions
  • Date and Time Columns. String Functions
  • Advanced Edit Options, Custom Queries
  • Custom Functions in Power Query M Lang
  • Grouping and Sub Groups with Queries

Chapter 20: GATEWAYS n REST APIs

  • 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 (Interactive)
  • Power BI Database for Streaming Datasets
  • Need for Spark Connections with Power BI
  • Using Streaming Datasets in Tiles in Cloud
  • Integrate Power BI Dashboards with RESTAPI
  • Power BI Data Gateway - Architecture
  • Gateway Installation, Configuration
  • PBIEngw Service, ODG, Privacy Levels
  • Using Gateways in Power BI Cloud
  • SSO - Single SignOn Security Options
  • Kerberos Authentication, Refresh Modes
  • Incremental Loads, Gateway Versions

Chapter 5 : BIG DATA ACCESS @ POWER BI

  • Database Properties and 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 with Power BI Visuals
  • Import Option with Database - Advantages
  • Direct Query Option in Database : Advantages
  • Options with Small and Big Databases
  • Accessing Azure SQL Database with Power BI
  • Reports with Azure SQL DB Connections
  • SQL Quries with Power BI Data Connections
  • Limitations with Direct Query in PBI
  • Query Storage Mode in Power BI Desktop
  • Relation Detection in Power BI Desktop
  • Relationship Management, Inactive Relations
  • One - One, One - Many, Many - One Relations
  • Defining Relations for Visual Interactions

Chapter 13 : DAX Functions & Usage - 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 Entities
  • 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 - Types and Use
  • Creating and Using Measures with DAX
  • Creating and Using Columns with DAX
  • Vertipaq Engine & Special Characters
  • Operators in DAX - Real-time Usage
  • Limitations of DAX Formulas

Chapter 21: 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 Visual Properties - 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 Labels
  • 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 Proprties
  • Axis, Legend, Value and Tooltip
  • Stacked Bar Chart, Clustered Column Chart
  • Stacked Bar Chart : Properties
  • Stacked Column Chart : Properties
  • Clustered Bar Chart : Properties
  • Clustered Column Chart : Properties
  • 100% Stacked Bar and Column Charts
  • Data Labels and Legend Properties
  • Comparing Bar Charts and Column Charts

Chapter 14 : DAX Functions & Usage - 2

  • 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
  • Currency Conversions, Field Formatting
  • SUM and SUMX Functions: Differences
  • CALCULATE(), CALCULATEX() Functions
  • CALCULATETABLE Functions. VAR, VARP
  • DATESMTD, DATESQTD, DATESYTD
  • ENDOF(), FirstDay(), LastDay()
  • SAMEPERIODLASTYEAR, STARTOF()
  • Other DAX Functions and Examples
  • DAX Expressions with Quick Measure
  • DAX Usage for Row Level Security
  • Creating Roles with Power BI Desktop
  • DAX Filters and Multi Value Conditions
  • Manage Roles and Membership Options
  • VIEW AS ROLE. Information Functions

Chapter 22 : 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 Lanugage Compiler from RMAN
  • R Language License from Intel MKL
  • Enabling R Script 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
  • Power BI Limitations with R Visuals
  • Duplciate Data Removal with R Visuals
  • Power BI with R for Data Scientists

Chapter 7 : Power BI Visual Properties - 2

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

Chapter 15 : DAX - 3 (DAX FUNCTIONS)

  • BLANK, CODE, CONCATENATE
  • CONCATENATEX,FIND,FIXED
  • FORMAT,LEFT,LEN
  • LOWER, MID, REPLACE, REPT
  • RIGHT, SEARCH, SUBSTITUTE
  • TRIM, UNICHAR, UPPER, VALUE
  • DATE, DATEVALUE, DAY
  • EDATE, EOMONTH, NOW
  • HOUR, MINUTE, MONTH
  • SECOND, TIME, TIMEVALUE
  • TODAY, WEEKDAY, WEEKNUM
  • YEAR YEARFRAC
  • EARLIEST ,FILTER ,FILTERS
  • HASONEFILTER HASONEVALUE ISCROSSFILTERED
  • ISFILTERED KEEPFILTERS RELATED
  • RELATEDTABLE USERELATIONSHIP VALUES
  • ALLEXCEPT ALLNOBLANKROW ALLSELECTED
  • CALCULATE CALCULATETABLE DISTINCT,EARLIER

CHAPTER 23 : POWERBI with SSRS, SSAS, R

  • SSRS Integration with Power BI
  • SSRS Report Portal URL to Power BI Cloud
  • Power BI KPI Reports Vs SSRS KPI Reports
  • Convering and Working with Mobile Reports
  • Report Buidler Reports to Powert BI
  • Generating QR Codes and Report Security
  • Reporting JSON Files, Bulk Data Loads
  • OLAP DataSources in Power BI
  • Using MDX Queries with PowerBI Queries
  • MDX SELECT and Perspective Access
  • KPIs and MDX Expressions with Power BI
  • MDX Queries and Filters with Power BI
  • Linked Servers and T-SQL SPROCs with MDX
  • YTD, PARALLELPERIOD, ALLMEMBERS
  • Implementing Row Level Security with DAX
  • Security Roles and Role Members. Tests
  • Using R for Power BI, Streaming DataSets
  • R Script and Integration with Power BI
  • PowerBI Reports using R & CSV / DAT Files
  • R Script with Azure SQL Database Sources
  • Using R Script Editor with Power BI Desktop
  • R Script Security and Microsoft OneDrive

Chapter 8 : Power BI Visual Properties - 3

  • Tree Map, Funnel and Gague Reports
  • Group and values in Tree Maps
  • Min. Max Values and Goal Values
  • Single Row Card and Multi Row Cards
  • Non-Interactive Visuals @ MultiRow
  • Callout Values and KPI Reports
  • Indicator, Trend and Target Goals
  • Map Reports and Filled Map Reports
  • ArcGIS Maps and Real-time Usage. Options
  • Using Buttons in Power BI Desktop
  • 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
  • Power BI Buttons and Options

Chapter 16 : DAX - 4

    CLOSINGBALANCEMONTH, CLOSINGBALANCEQUARTER CLOSINGBALANCEYEAR, DATEADD,DATESBETWEEN DATESMTD,DATESQTD,DATESYTD ENDOFMONTH, ENDOFQUARTER , ENDOFYEAR , FIRSTDATE, FIRSTNONBLANK , LASTDATE , LASTNONBLANK, NEXTDAY, NEXTMONTH, NEXTQUARTER, NEXTYEAR, OPENINGBALANCEMONTH , OPENINGBALANCEQUARTER, OPENINGBALANCEYEAR, PARALLELPERIOD, PREVIOUSDAY, PREVIOUSMONTH, PREVIOUSQUARTER, PREVIOUSYEAR, SAMEPERIODLASTYEAR, STARTOFMONTH, STARTOFQUARTER , STARTOFYEAR , TOTALMTD, TOTALQTD, TOTALYTD,ALL, FILTER

Chapter 24 : 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 and REST API Integration. This project involves Microsoft OneDrive and Google Drive Integration, High Level End User Colloborations with 360 degree Dashboards. Also includes Power BI Report Server Paginated Reports and Data Gateways with Azure DataSync Services and ODG Logs used in Real-world.

Note : Above course curriculum applicable for registrations from July 9th, 2018.

SQL Server & T-SQL Queries (Applicable for Plan B)

Module I: SQL Server & Design, Queries, Joins

Module II: T-SQL Queries, Tuning & Programming

CHAPTER 1: SQL SERVER INSTALLATION

  • What is Data? What is Database? File Store Limitations?
  • Why Microsoft SQL Server? Advantages (Technical/Usage)
  • SQL Server - Career Options, Certifications, Projects
  • What is SQL? What is T-SQL? Differences. Why T-SQL?
  • MSBI Components and Real-time Activities
  • Versions and Editions of SQL Server - Overview
  • Session Wise Plan, Material and Real-time Project Details
  • LAB PLAN - 24x7 LIVE Server (Online Lab)
  • SQL Server Software - Server Installation Steps
  • SQL Server Tools Installation and Verification
  • SSMS in Windows OS, SOS Tool in Mac & LINUX OS
  • H/W & S/W Requirements. Server Configuration Options
  • Instance Types : Default and Named Instances. Instance IDs
  • SQL Server Tools - SQL Server Management Studio (SSMS)

CHAPTER 7: STORED PROCEDURES - LEVEL 1

  • Stored Procedures - Purpose, Syntax, Properties and Types
  • Compilation, Precompilation and Query Optimization (QO)
  • Variables - Usage and Data Types in Stored Procedures
  • Parameters - Usage and Data Types in Stored Procedures
  • Stored Procedure Executions - Syntax, Alternate Options
  • Stored Procedures for Data Validations & Missing Identity
  • Stored Procedures for Dynamic SQL Queries. Views & SPs
  • Stored Procedures for Data Reporting. Advantanges, Tuning
  • Important System Procedures For Metadata Access. Usage
  • Important Extended Procedures For Application Operations
  • IF.. ELSE, IF .. ELSE IF, IIF Conditions. PRINT statements
  • Error Handling Techniques in T-SQL: TRY, CATCH, THROW
  • Dynamic Parameters and Variables. Examples with Views
  • Default Parameter Values, Data Types and NULL Values
  • Batch Executions with Stored Procedures. Variants

CHAPTER 2: SQL BASICS - DDL, DML, SELECT

  • Basic SQL for Beginners. Introducing Databases, Tables
  • What is SQL? Why T-SQL? Basic SQL Queries in SSMS
  • DDL and DML Statements - Creating & Using Databases
  • Table Creation (Basic Level) - Columns and Data Types
  • Issues with Digital Data into Characters. Missing Values
  • INSERT / Store Data into SQL Server Tables - Options
  • Single Row and Multiple Row Inserts with NULL Values
  • SELECT Queries and Basic Operators : IN, BETWEEN
  • IS, UNION, UNION ALL, Other Basic SQL Operators
  • UPDATE Statements with / without Conditions. SET
  • DELETE Statements with Conditions. Logging Options
  • TRUNCATE Statement - DELETE Comparisons, Logging
  • SYSTEM DATABASES - Purpose and Importance. Resource
  • CLIENT - SERVER Architecture (TDS) & Client Statistics

CHAPTER 8: STORED PROCEDURES - LEVEL 2

  • Stored Procedures for Sub Queries, Dynamic Queries
  • Using NCHAR and NVARCHAR in Stored Procedures
  • Variables and Parameters in Stored Procedures
  • Using Stored Procedures with Database Tables
  • Using Stored Procedures with Views and System Viewss
  • Metatada Access and Compilation Options
  • Important System Stored Procedures and Usage
  • Sp_help, Sp_helpdb, sp_pkeys, sp_helptext
  • Sp_recompile, sp_rename, sp_renamedb
  • User Defined Procedures with System SPs
  • Using Extended Stored Procedures - Usage
  • Recursion of Stored Procedures and Practical Usage
  • Stored Procedures for Dynamic Values, sysname data
  • Unicode Data and OUTPUT Parameters with SProcs

CHAPTER 3: SQL SERVER DATABASE DESIGN

  • SQL Database Architecture - Logical and Physical View
  • Database Properties - Files - Types - Storage Options
  • Data Files : Purpose and Sizing. Detailed Architecture
  • Filegroups : Purpose and Grouping Options. Properties
  • Log files : Sizing, Placement & Detailed Architecture
  • Pages, Extents (Uniform, Mixed). Data Allocation Process
  • Write Ahead Log (WAL) and Log Sequence Number (LSN)
  • Virtual Log File (VLF) and MINI LSN. Operation Audits
  • Database Creation using GUI - Adding Files, Filegroups
  • Database File and Filegroup Options. GUI Limitations
  • Database Creation using T-SQL Scripts. SYNTAX Rules
  • Database with Filegrowth, Autogrowth, MAXSIZE Options
  • mdf, ndf, ldf and Custom Extensions. Dynamic Extensions
  • Planning and Designing Very Large Databases (VLDB)
  • CHAR versus VARCHAR Differences - Type, Size Allocations

CHAPTER 9: VIEWS - FUNCTIONS & QUERIES

  • Views on Tables - Stored SELECT Statements, Data Access
  • SCHEMABINDING and ENCRYPTION Options - Advantages
  • Cascaded Views and WITH CHECK OPTION, Advantages
  • Orphan Views - Scenarios and Realworld Solutions
  • Common System Views For Metadata Access, Object IDs
  • Functions: Types, Purpose and Usage. Return Values
  • Scalar Value and Inline Table Value Functions
  • Multi-Line Table Value Returning Functions - Usage
  • Table Variables and Parameters in SQL Server. Usage
  • ROLLUP and CUBE - Sub Totals, Grand Totals, Aggregates
  • ROLLUP of Table Data. Column Aggregations. ORDER BY
  • CUBE on Table Data - Purpose & Usage. Permutations
  • Queries with GROUPING() Option in SELECT, Using HAVING
  • HAVING versus WHERE Conditions - Usage Differences

CHAPTER 4: TABLE DESIGN & QUERIES

  • Table Design - Creation. Columns - Data Types, Length
  • Routing Tables to Database File Groups, Advantages
  • Schemas - Purpose, Creation and Usage with Tables
  • Table Design using T-SQL Scripts - Syntax, Examples
  • Data Types, Length, NULLs and Naming Conventions
  • UNION, UNION ALL Operators. Differences, Row Order
  • CREATE, ALTER, DROP -- INSERT, UPDATE, DELETE
  • SELECT Queries with Schema on Tables, Column Aliases
  • T-SQL Data Types and NULL Values. Computed Columns
  • Comparing DELETE and TRUNCATE - TLog Files
  • T-SQL Operators: IN, BETWEEN, IS, AND, OR, EXISTS
  • Default Schema and Default Filegroup for Table Design
  • Basic Sub Queries - SELECT, MIN/ MAX. Column Aliases
  • Temporary Tables : Purpose and Types. Local and Global
  • Synonyms : Purpose. Alternate Object Reference, Queries

CHAPTER 10: TRANSACTIONS & TRIGGERS

  • Need for Transactions, Transaction Scenarios
  • ACID Properties and Transaction Types. Atomic Property
  • EXPLICIT, IMPLICIT Transactions - Query Blocking
  • IMPLICIT Transactions - Usage, Database Settings
  • AUTOCOMMIT Transactions - Advantages, Examples
  • OPEN Transactions and Audits. OPENTRAN commands
  • Nested Transactions and COMMIT / ROLLBACK Rules
  • SavePoint Options with Explicit Transactions, Rollbacks
  • LOCK HINTS : READPAST, NOLOCK, HOLDLOCK - Usage
  • Triggers - Purpose and Types. Scope Of Usage
  • DML Triggers - Events, Types and Practical Usage
  • FOR / AFTER Triggers and INSTEAD OF Triggers
  • INSERTED & DELETED Memory Tables with DML Triggers
  • Triggers for DML Operation Audits and Data Sampling
  • Database Triggers and Server Level Triggers

CHAPTER 5: CONSTRAINTS and KEYS

  • Constraints and Keys - Ensuring Table Data Integrity
  • Normal Forms - Types, Relational Database (RDB) Design
  • OLTP Database Model & BCNF - Relations with PK / UQ
  • NULL, NOT NULL and Default Nullability for Columns
  • UNIQUE KEY Constraints: Importance, Uniqueness, Nulls
  • PRIMARY KEY Constraint: Properties, Priority, Limitations
  • FOREIGN KEY Constraint: References, Relations & Usage
  • CHECK Constraints: Properties, Conditions and Usage
  • DEFAULT Constraints: Properties, Usage and Limitations
  • Relations with Tables across Multiple Schemas, Usage
  • Identity Property with / without PRIMARY KEY, Usage
  • Naming Conventions For Constraints, Columns and Tables
  • Normal Forms - Types, Purpose and Usage. With Examples
  • BCNF: Boycee-Codd Normal Form and Practical Usage

CHAPTER 11: INDEXES and QUERY TUNING OPTIONS

  • Indexes: Architecture (Page Level), Purpose and Types
  • Clustered Indexes - Architecture, Fragmentation Issues
  • Non Clustered Indexes - Architecture, Column References
  • Execution Plans and Query Optimization (QO) Techniques
  • Execution Plan - Table Scan, Index Scan and Index Seek
  • INCLUDED INDEXES - Index Seeks, Query Tuning
  • COLUMNSTORE Indexes - Advantages, Usage Examples
  • FILTERED Indexes & Online Indexes
  • Materialized Views / Indexed Views - Tuning Options
  • Query Optimizer (QO) Options for Index Pages, Data Pages
  • Limitations of Indexes - Impact on DML and SELECT
  • Primary Key Index, Composite Indexes and Precautions
  • RID and Index Key Concepts. Index Page - Data Page Arch"
  • Real-world Considerations For Indexes (Tables, Views)

CHAPTER 6: JOINS, SUB QUERIES & NESTED QUERIES

  • JOINS - Purpose and Types, Use Case Scenarios
  • JOIN - Types, Queries and Importance of Reports
  • CROSS JOIN in detail. Examples and Conditions @ WHERE
  • INNER JOIN in detail. Examples with WHERE and ON
  • Comparing INNER JOIN with CROSS JOIN for Conditions
  • OUTER JOINS in detail. LEFT, RIGHT and FULL Joins
  • SELF JOINS with INNER / OUTER Joins. Usage Scenarios
  • Working with Self Joins on non key columns, advantages
  • JOINS with more than 2 tables. Syntax, Precedence Order
  • Query Optimization Considerations with Schema References
  • Deciding the best Join Type, Order and Query Options
  • JOIN Queries with Options and UNION, UNION ALL Operators
  • Basic Sub Queries and Joins. Alternate Syntax & Queries
  • Using ON and WHERE for Join Conditions. Working with NULLs
  • Using SubQueries for Self Joins and Outer Joins
  • Working with Nested Queries and Nested Sub Queries
  • Using Sub Queries and Nested Sub Queries with Outer Joins
  • End User Access to SQL Databases - Reporting Tools, Options
  • A Real-world Case Study understanding Joins & Queries

CHAPTER 12: SQL SERVER ARCHITECTURE

  • Client - Server Architecture of SQL Server
  • SQL Server Tools - Connection Options, TDS Packets
  • Protocols : TCP / IP, Named Pipes, Shared Memory
  • SQL Native Client (SNAC) and OLE DB Drivers / Providers
  • ISO - OSI Model of Data Connections, Encrypted Data
  • Query Processing and Query Optimizer (QO) Components
  • SQL Server Architecture For Database Engine, LCM Options
  • Architecture - Query Processor and Storage Engine
  • Architecture - Query Parser, Optimizer, Mini LSN, MDAC
  • Architecture - SQL Engine, SQL Manager and Query Buffers
  • Architecture - Write Ahead Log (WAL), Lazy Writer Threads
  • Architecture - SQLOS Threads and Task Schedulers, CLR
  • SQL Database Architecture - RAID Levels (S/W, H/W)
  • Log Sequence Numbers (LSN) and Time Mapping. Audits
  • Log File Architecture - Virtual Log Files and Usage
  • Log File Architecture - Mini LSN & Degree Of Parallelism
  • DB Catalogs, CLR Integration and MDAC Components
  • LSN Timestamps and MINILSN. Background Threads @ SQL
 
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