Ch 1: SQL SERVER INTRODUCTION

• Data, Databases and RDBMS Software
• Database Types : OLTP, DWH, OLAP
• Microsoft SQL Server Advantages, Use
• Versions and Editions of SQL Server
• SQL : Purpose, Real-time Usage Options
• SQL Versus Microsoft T-SQL [MSSQL]
• Microsoft SQL Server - Career Options

• SQL Server Components and Usage
• Database Engine Component and OLTP
• BI Components, Data Science Components
• ETL, MSBI and Power BI Components
• Course Plan, Concepts, Resume, Project
• 24 x 7 Online Lab for Remote DB Access
• Software Installation Pre-Requisites

Ch 5: SQL Basics - 3, T-SQL INTRO

• Database Objects : Tables and Schemas
• Schemas : Group Tables in Database
• Schemas : Security Management Object
• Creating Schemas & Batch Concept
• Using Schemas for Table Creation
• Data Storage in Tables with Schemas
• Data Retreival and Usage with Schemas
• Table Migrations across Schemas

• Import and Export Wizard in SSMS
• Data Imports with Excel File Data
• Performing Bulk Operations in SSMS
• Temporary Tables : Real-time Use
• Local and Global Temporary Tables
• # and ## Prefix, Scope of Usage

Ch 9: JOINS, T-SQL QUERIES Level 3

• GetDate, Year, Month, Day Functions
• Date & Time Styles, Data Formatting
• DateAdd and DateDiff Functions
• Cast and, Convert Functions in Queries
• String Functions: SubString, Relicate
• Len, Upper, Lower, Left and Right
• LTrim, RTrim, CharIndex Functions

• MERGE Statement - Comparing Tables
• WHEN MATCHED and NOT MATCHED
• Incremental Load with MERGE Statement
• IIF() Function for Value Compares
• CASE Statement : WHEN, ELSE, END
• ROW_NUMBER() and RANK() Queries
• Dense Rank and Partition By Queries

Ch 2: SQL SERVER INSTALLATIONS

• System Configuration Checker Tool
• Versions and Editions of SQL Server
• SQL Server and SSMS Installation Plan
• SQL Server Pre-requisites : S/W, H/W
• SQL Server 2016 / 2017 Installation
• SQL Server 2019 Installation
• Instance Name and Server Features
• Instances : Types and Properties

• Default Instance, Named Instances
• Port Numbers, Instance Differences
• Service and Service Account Use
• Authentication Modes and Logins
• Windows Logins and SQL Logins
• FileStream and Collation Properties

Ch 6 : CONSTRAINTS,INDEXES Basics

• Constraints and Keys - Data Integrity
• NULL, NOT NULL Property on Tables
• UNIQUE KEY Constraints: Importance
• PRIMARY KEY Constraint: Importance
• FOREIGN KEY Constraint: Importance
• REFERENCES, CHECK and DEFAULT
• Candidate Keys and Identity Property

• Database Diagrams and ER Models
• Relationships Verification and Links
• Indexes : Basic Types and Creation
• Index Sorting and Search Advantages
• Clustered and NonClustered Indexes
• Primary Key and Unique Key Indexes
• Need for Indexes - working with Keys

Ch 10: VIEW, SPs, Function Basics

• Views : Types, Usage in Real-time
• System Predefined Views and Audits
• Listing Databases, Tables, Schemas
• Functions : Types, Usage in Real-time
• Scalar, Inline and Multi-Line Functions
• System Predefined Functions, Audits
• DBId, DBName, ObjectID, ObjectName
• Variables & Parameters in SQL Server

• Procedures : Types, Usage in Real-time
• User & System Predefined Procedures
• Parameters and Dynamic SQL Queries
• Sp_help, Sp_helpdb and sp_helptext
• sp_pkeys, sp_rename and sp_help
• Important System Objects and Metadata

Ch 3: SSMS Tool, SQL BASICS - 1

• SQL Server Management Studio
• Local and Remote Connections
• System Databases: Master and Model
• MSDB, TempDB, Resource Databases
• Creating Databases : Files [MDF, LDF]
• Creating Tables in User Interface
• Data Insertion & Storage. Limitations

• SQL : Purpose and Real-time Usage
• SQL Versus T-SQL : Basic Differences
• DDL, DML, SELECT, DCL and TCL
• Creating Tables using SQL Scripts
• Data Storage, Inserts - Basic Level
• Table Data Verifications with Select
• SELECT Statement for Table Retrieval

Ch 7: JOINS, T-SQL Queries : Level 1

• JOINS - Table Comparisons Queries
• INNER JOINS For Matching Data
• OUTER JOINS For (non) Match Data
• Left Outer Joins with Example Queries
• Right Outer Joins with Example Queries
• FULL Outer Joins - Realtime Scenarios
• Join Queries with "ON" Conditions

• Join Unrelated Tables in SQL Server
• NULL, IS NULL Operators in Joins
• CROSS JOIN and CROSS APPLY
• CROSS JOIN Versus CROSS APPLY
• One-way & Two Way Data Comparisons
• Important Join Queries in T-SQL
• Join Options: Merge, Loop, Hash

Ch 11: Triggers & Transactions

• Triggers - Purpose, Real-world Usage
• FOR/AFTER Triggers - Real time Use
• INSTEAD OF Triggers - Real time Use
• INSERTED, DELETED Memory Tables
• Using Triggers for Data Replication
• Enable Triggers and Disable Triggers
• Database Level, Server Level Triggers

• Transactions : Types, ACID Properties
• Transaction Types and AutoCommit
• EXPLICIT & IMPLICIT Transactions
• COMMIT and ROLLBACK Statements
• Open Transaction Scenarios & Cause
• Query Blocking Scenarios @ Real-time
• NOLOCK and READPAST Lock Hints

Ch 4: SQL BASICS - 2

• Creating Databases & Tables in SSMS
• Single Row Inserts, Multi Row Inserts
• Rules for Data Insertion Statements
• SELECT Statement @ Data Retrieval
• SELECT with WHERE Conditions
• Batch Concept and Go Statement
• AND and OR Operators Usage
• IN Operator and NOT IN Operator

• Between, Not Between Operators
• LIKE and NOT LIKE Operators
• UPDATE Statement & Conditions
• DELETE & TRUNCATE Statements
• Logged and Non-Logged Operations
• ADD, ALTER and DROP Columns
• ALTER & DROP Table Statements

Ch 8: Group By, T-SQL Queries Level 2

• GROUP BY Queries and Aggregations
• Group By Queries with Having Clause
• Group By Queries with Where Clause
• Using WHERE and HAVING in T-SQL
• Rollup : Usage and T-SQL Queries
• Cube : Usage and T-SQL Queries
• UNION and UNION ALL Operator
• EXISTS Operator, Query Conditions

• Sub Queries and Alternatives to Joins
• Using Joins with Group By Queries
• Using Joins with Nested Sub Queries
• Sub Queries with Joins and Group By
• Using UNION and UNION ALL in Queries
• Nested Sub Queries with Group By, Joins
• Comparing WHERE, HAVING Conditions

Ch 12 : ER MODELS, NORMAL FORMS

• Normal Forms for Entity Relationships
• First Normal Form and Atomocity
• Second Normal Form, Candidate Keys
• 3rd Normal Form Multi Value Dependancy
• Boycee-Codd Normal Form : BNCF
• Fourth Normal Form Realtime Advantages
• Self Joins & Self Reference Keys
• 1:1, 1:M, M:1, M:M Relationship Types

• Joins with Group By Queries
• Joins with Sub Queries, Formating
• Office Data Connections, Excel Reports
• Excel Pivot Reports and Reports
• SQL Queries (Auto Generated) in BI Tools
• FETCH OFFSET, NEXT ROWS, Order By
• Data Refresh (Manual and Automated)

Ch 1 : POWER BI BASICS

• Power BI Job Roles in Real-time
• Power BI Data Analyst Job Roles
• Business Analyst - Job Roles
• Power BI Developer - Job Roles
• Power BI for Data Scientists
• Comparing MSBI and Power BI
• Comparing Tableau and Power BI
• MCSA 70-778, MCSA 70-779 Exam

• Types of Reports in Real-World
• Interactive & Paginated Reports
• Analytical & Mobile Reports
• Data Sources Types in Power BI
• Power BI Licensing Plans - Types
• Power BI Training : Lab Plan
• Power BI Dev & Prod Environments

Ch 7 : POWER QUERY LEVEL 1

• Power Query M Language Purpose
• Power Query Architecture and ETL
• Data Types, Literals and Values
• Power Query Transformation Types
• Table & Column Transformations
• Text & Number Transformations
• Date, Time and Structured Data
• List, Record and Table Structures

• let, source, in statements @ M Lang
• Power Query Functions, Parameters
• Invoke Functions, Execution Results
• Get Data, Table Creations and Edit
• Merge and Append Transformations
• Join Kinds, Advanced Editor, Apply
• ETL Operations with Power Query

Ch 13 : POWER BI CLOUD - 1

• Power BI Service Architecture
• Power BI Cloud Components, Use
• App Workspaces, Report Publish
• Reports & Related Datasets Cloud
• Creating New Reports in Cloud
• Report Publish and Report Uploads
• Dashboards Creation and Usage
• Adding Tiles to Dashboards

• Pining Visuals and Report Pages
• Visual Pin Actions in Dashboards
• LIVE Page Interaction in Dashboard
• Adding Media: Images, Custom Links
• Adding Chs and Embed Links
• API Data Sources, Streaming Data
• Streaming Dataset Tiles (REST API)

Ch 2: BASIC REPORT DESIGN

• Power BI Desktop Installation
• Data Sources & Visual Types
• Canvas, Visualizations and Fields
• Get Data and Memory Tables
• In-Memory xvelocity Database
• Table and Tree Map Visuals
• Format Button and Data Labels
• Legend, Category and Grid

• PBIX and PBIT File Formats
• Visual Interaction, Data Points
• Disabling Visual Interactions
• Edit Interactions - Format Options
• SPOTLIGHT & FOCUSMODE
• CSV and PDF Exports. Tooltips
• Power BI EcoSystem, Architecture

Ch 8 : POWER QUERY LEVEL 2

• Query Duplicate, Query Reference
• Group By and Advanced Options
• Aggregations with Power Query
• Transpose, Header Row Promotion
• Reverse Rows and Row Count
• Data Type Changes & Detection
• Replace Columns: Text, NonText
• Replace Nulls: Fill Up, Fill Down

• PIVOT, UNPIVOT Transformations
• Move Column and Split Column
• Extract, Format and Numbers
• Date & Time Transformations
• Deriving Year, Quarter, Month, Day
• Add Column : Query Expressions
• Query Step Inserts and Step Edits

Ch 14 : POWER BI CLOUD - 2

• Dashboards Actions,Report Actions
• DataSet Actions: Create Report
• Share, Metrics and Exports
• Mobile View & Dashboard Themes
• Q & A [Cortana] and Pin Visuals
• Export, Subscribe, Subscribe
• Favorite, Insights, Embed Code
• Featured Dashboards and Refresh

• Gateways Configuration, PBI Service
• Gateway Types, Cloud Connections
• Gateway Clusters, Add Data Sources
• Data Refresh : Manual, Automatic
• PBIEngw Service, ODG Logs, Audits
• DataFlows, Power Query Expressions
• Adding Entities and JSON Files

Ch 3 : Visual Sync, Grouping

• Slicer Visual : Real-time Usage
• Orientation, Selection Properties
• Single & Multi Select, CTRL Options
• Slicer : Number, Text and Date Data
• Slicer List and Slicer Dropdowns
• Visual Sync Limitations with Slicer
• Disabling Slicers,Clear Selections

• Grouping : Real-time Use, Examples
• List Grouping and Binning Options
• Grouping Static / Fixed Data Values
• Grouping Dynamic / Changing Data
• Bin Size and Bin Limits (Max, Min)
• Bin Count and Grouping Options
• Grouping Binned Data, Classification

Ch 9 : POWER QUERY LEVEL 3

• Creating Parameters in Power Query
• Parameter Data Types, Default Lists
• Static/Dynamic Lists For Parameters
• Removing Columns and Duplicates
• Convert Tables to List Queries
• Linking Parameters to Queries
• Testing Parameters and PBI Canvas

• Multi-Valued Parameter Lists
• Creating Lists in Power Query
• Converting Lists to Table Data
• Advanced Edits and Parameters
• Data Type Conversions, Expressions
• Columns From Examples, Indexes
• Conditional Columns, Expressions

Ch 15 : EXCEL & RLS

• Import and Upload Options in Excel
• Excel Workbooks and Dashboards
• Datasets in Excel and Dashboards
• Using Excel Analyzer in Power BI
• Using Excel Publisher in PBI Cloud
• Excel Workbooks, PINS in Power BI
• Excel ODC Connections, Power Pivot

• Row Level Security (RLS) with DAX
• Need for RLS in Power BI Cloud
• Data Modeling in Power BI Desktop
• DAX Roles Creation and Testing
• Adding Power BI Users to Roles
• Custom Visualizations in Cloud
• Histogram,Gantt Chart,Infographics

Ch 4 : Hierarchies, Filters

• Creating Hierarchies in Power BI
• Independent Drill-Down Options
• Dependant Drill-Down Options
• Conditional Drilldowns, Data Points
• Drill Up Buttons and Operations
• Expand & Show Next Level Options
• Dynamic Data Drills Limitations
• Show Data and See Records

• Filters : Types and Usage in Real-time
• Visual Filter, Page Filter, Report Filter
• Basic, Advanced and TOP N Filters
• Category and Summary Level Filters
• DrillThru Filters, Drill Thru Reports
• Keep All Filters" Options in DrillThru
• CrossReport Filters, Include, Exclude

Ch 10 : DAX Functions - Level 1

• DAX : Importance in Real-time
• Real-world usage of Excel, DAX
• DAX Architecture, Entity Sets
• DAX Data Types, Syntax Rules
• DAX Measures and Calculations
• ROW Context and Filter Context
• DAX Operators, Special Characters
• DAX Functions, Types in Real-time

• Vertipaq Engine, DAX Cheat Sheet
• Creating, Using Measures with DAX
• Creating, Using Columns with DAX
• Quick Measures and Summaries
• Validation Errors, Runtime Errors
• SUM, AVERAGEX, KEEPFILTERS
• Dynamic Expressions, IF in DAX

Ch 16 : Report Server, RDL

• Need for Report Server in PROD
• Install, Configure Report Server
• Report Server DB, Temp Database
• Webservice URL, Webportal URL
• Creating Hybrid Cloud with Power BI
• Using Power BI DesktopRS
• Uploading Interactive Reports

• Report Builder For Report Server
• Report Builder For Power BI Cloud
• Designing Paginated Reports (RDL)
• Deploy to Power BI Report Server
• Data Source Connections, Report
• Power BI Report Server to Cloud
• Tenant IDs Generation and Use
• Mobile Report Publisher, Usage

Ch 5 : Bookmarks, Azure, Modeling

• Drill-thru Filters, Page Navigations
• Bookmarks : Real-time Usage
• Bookmarks for Visual Filters
• Bookmarks for Page Navigations
• Selection Pane with Bookmarks
• Buttons, Images with Actions
• Buttons, Actions and Text URLs
• Bookmarks View & Selection Pane

• OLTP Databases, Big Data Sources
• Azure Database Access, Reports
• Import & Direct Query with Power BI
• SQL Queries and Enter Data
• Data Modeling : Currency, Relations
• Summary, Format, Synonyms
• Web View & Mobile View in PBI

Ch 11 : DAX Functions - Level 2

• Data Modeling Options in DAX
• Detecting Relations for DAX
• Using Calculated Columns in DAX
• Using Aggregated Measures in DAX
• Working with Facts & Measures
• Modeling : Missing Relations
• Modeling : Relation Management

• CALCULATE Function Conditions
• CALCULATE & ALL Member Scope
• RELATED & COUNTROWS in DAX
• Entity Sets and Slicing in DAX
• Dynamic Expressions, RETURN
• Date, Time and Text Functions
• Logical, Mathematical Functions
• Running Total & EARLIER Function

Ch 17: MSBI Integrations

• Power BI with SQL Server Source
• Power BI with SQL Data Warehouse
• Power BI with SSAS OLAP Server
• Power BI with Azure SQL DB Source
• Power BI with Azure SQL Warehouse
• Power BI with Azure Analysis Server
• Power BI with SSRS (RDL) Reports

• Power BI Report Builder Tool
• Installation & Configuration
• Paginated Reports Design, Use
• Data Sources, Datasets, RDL
• Report Publish (RDL) to Cloud
• Report Verifications, Data Sync
• Interactive Vs Paginated Reports
• Creating, Managing Alerts in Cloud

Ch 6 : Visualization Properties

• Stacked Charts and Clustered Charts
• Line Charts, Area Charts, Bar Charts
• 100% Stacked Bar & Column Charts
• Map Visuals: Tree, Filled, Bubble
• Cards, Funnel, Table, Matrix
• Scatter Chart : Play Axis, Labels
• Series Clusters & Selections

• Waterfall Chart and ArcGIS Maps
• Infographics, Icons and Labels
• Color Saturation, Sentiment Colors
• Column Series, Column Axis in Lines
• Join Types : Round, Bevel, Miter
• Shapes, Markers, Axis, Plot Area
• Display Units,Data Colors,Shapes
• Series, Custom Series and Legends

Ch 12 : DAX FUNCTIONS Level 3

• 1:1, 1:M and M:1 Relations
• Connection with CSV, MS Access
• AVERAGEX and AVERAGE in DAX
• KEEPFILTERS and CALCUALTE
• COUNTROWS, RELATED, DIVIDE
• PARALLELPERIOD, DATEDADD
• CALCULATE & PREVIOUSMONTH

• USERELATIONSHIP, DAX Variables
• TOTALYTD , TOTALQTD
• DIVIDE, CALCULATE, Conditions
• IF..ELSE..THEN Statement
• SELECTEDVALUE, FORMAT
• SUM, DATEDIFF Examples in DAX
• TODAY, DATE, DAY with DAX
• Time Intelligence Functions - DAX

• Project Requirement Analysis
• Implementing SDLC Phases
• Requirement Gathering, FSA

• PBIX Report Design
• Visualizations, Properties
• Analytics and Formating

• Data Modeling, Power Query
• Dynamic Connections, Azure DB
• Parameters and M Lang Scripts

• DAX Requriements, Analysis
• Cloud and Report Server
• Project FAQs and Solutions