Azure SSAS Classrrom Training in Hyderabad

Ch 1: INTRODUCTION TO SSAS & CONFIGURATION

• Installation and Configuration of MSBI - SSAS
• SSAS Component & Tools - Operational Modes
• Multidimensional Mode : Properties & Usage
• Tabular Mode : Purpose, Properties and Usage (ROLAP)
• PowerPivot Mode : Properties and Usage (Overview)
• Multidimensional Mode Instances, Verification
• SSAS Service Accounts & Usage. SQL Browser Service

• SSDT Tool: SQL Server Data Tools - SSDT / Visual Studio
• Understanding Developer Environment (SSDT) Interface
• SSAS Online Training - Lab Plan, Resources & Databases
• Need for OLAP Databases and Cubes For Analysis - OLAP
• Need for MDX : Multidimensional Expression Language
• Need for DAX : Data Analysis Expression Language
• Need for XMLA and DMX Environments. SSAS Architecture
• SSAS Workflow in Real-world. Sources to SSAS to Reports
• Data Source Configurations & DB Installations for Lab

Ch 7: TABULAR MODE INSTALLATION, CUBE DESIGN

• SSAS Tabular Mode : Purpose, Scope of Real-time Usage
• SSAS Tabular Mode Server Installation, Mode Verification
• Tabular Mode Server and Database Architecture
• Tabular Mode Advantages with Data Sources, Cube Design
• Tabular Mode Design : SSDT, Power Query, DAX
• Purpose of Power Query and DAX for OLAP Cube Design
• In-Memory Vertipaq Storage - Performance Advantages

• BISM : Business Intelligence Semantic Model
• Tabular Mode Cubes : Developing Data Models (SMDL)
• Workspace Servers : Integrated and Dedicated Modes
• Comparing Workspace Server and Integrated Options
• Compatibility Levels with OLAP Tabular Mode Solutions
• Cube Design with SQL Server Databases : Data Imports
• Workflow Operations with Tabular Mode Cube Design
• Data Sources, SSDT and Tabular Mode Cube Design
• SSAS OLAP Environment and Cube Report Entities

Ch 2: BASIC CUBE DESIGN WITH SSAS, EXCEL PIVOT

• Basic Cube Design with SSDT (SQL Server Data Tools)
• SSAS OLAP Entities : Data Source, Data Source View
• Cube Design Concepts : Measure Groups, Measures
• Identifying Dimensions, Attributes and Members
• Basic Cube Design : DS, DSV, Cube Wizard, Dimensions
• Adding Attributes. SSAS Deployment and Cube Access
• PROCESS of OLAP Cube. Options and Online Deployment
• Cube Browsing (Basic Level) using SSMS & SSDT Tools

• Excel Connections for SSAS Databases & OLAP Cube Access
• Excel PIVOT Tables and Basic Chart Report Design in SSAS
• Piechart Reports and Attribute Filters. Usage Statistics
• Common Deployment Errors : Logon Failures @ OLAP Server
• OLAP Server Impersonation Account - NT ATHORITY Account
• OLAP Deployment Warnings and Solutions. Precautions
• End to End Implementation of SSAS - Basic Level Example

Ch 8: TABULAR MODE CUBE DESIGN, DAX - 1

• Cube Design with SQL Server Databases : Data Imports
• Identifying Tables, Dimensions, Attributes and Members
• Designing Measure Groups, Aggregated Measures
• Grid Formats and Diagram Formats. Process Options
• BUILD and DEPLOY Options with Integrated Workspace
• Cube Browsing with "Analyze in Excel" Options, Reports
• Tabular Mode Cube Design : Creating, Using Hierarchies

• DAX - User Interface and Data Types, Basic Examples
• DAX Usage : DAX Queries and DAX Expressions
• DAX Aggregated Measures with DAX, DAX Syntax
• Hierarchies in Cube Design : Adding Levels
• Relationship Edits : Active, Inactive Relations
• Tabular Mode Cube Redesign with Import Templates
• Model Options : Process, Calculate Option Advantages
• Data Load Verifications, Tabular Mode Explorer

Ch 3: HIERARCHIES, MDX - LEVEL 1

• Working with Data Source Views (DSV) : Named Calculations
• Named Queries & Dimension Attributes. Explore Data in DSV
• Basic Dimension Types : Database Dimension @ Entity Level
• Cube Dimension Based on Entity Relations in DSV
• Creating Hierarchies in Multidimensional Cube : Purpose
• Grouping Attributes within Database Dimensions
• Testing Hierarchies in Cube Browser : SSMS, SSDT Tools

• MDX: Multidimensional Expression Language. Syntax Rules
• MDX Queries Syntax and MDX Expressions Syntax
• MDX Syntax and Axis Models. Cube Data into Rows, Columns
• Practical Advantages of MDX: Reports, Cube Writebacks
• MDX Queries with Dimension Attributes, Members. Axis
• MDX Queries on Hierarchies, Levels and Attribute Keys
• MEMBERS, CHILDREN, ALL MEMBERS - Cube Aggregates
• SELECT Statement in MDX with CROSSJOIN Examples

Ch 9: TABULAR MODE - DAX LEVEL 2

• KPIs (Key Performance Indicators) Realtime Use
• Partitions in Tabular Mode Cube Design
• Using Power Query for Partition Design
• Power Query Expressions and Data Filters
• Import Data Options and ETL Operations
• Defining Measures with DAX Expressions
• Defining Perspectives for Cube Access

• Data Expression Language (DAX) Basics
• DAX Usage : Columns and Measures in SSAS
• Auto-generated Expressions in DAX, Usage
• Member Representations in DAX Queries
• DAX Functions, Expressions and Real-time Usage
• Standard Functions, Time Intelligence Functions
• DAX FILTER() and CALCULATE() Operations
• Time Dimention and YTD(), QTD(), MTD()

Ch 4: CALCULATIONS, MDX - LEVEL 2

• MDX Queries with WHERE, EXCEPT, RANGE, Operators
• NONEMPTY, Multiple Member Values. Limitations @ WHERE
• PARENT and CHILDREN with MDX Hierarchies. Tuple Inverse
• MDX ORDER, TOPCOUNT / HEAD, BOTTOMCOUNT/ TAIL
• FILTERS in MDX - CURRENT MEMBER, EMPTY MEMBER
• FILTER Expressions with AND / OR and LEFT / RIGHT Range
• MDX Query Batches - GO Statement and Query Separator
• ADOMD Client : MDX Query Processing and Real-time Use

• MDX Calculations - Creation and Scope of Usage
• Calculations with MDX Expressions: Measure Level
• Calculations with MDX Expressions: Attribute Level
• Time Based Calculations with MDX Scripts in Cube
• TIME DIMENSIONS - Purpose and Advantages, Properties
• Time Keys and Time Attributes - Calendar / Fiscal / ISO
• BI Enhancements (Cube) : Advantage and Usage Scope
• Identifying Attributes, Hierarchies For Time Enhancements
• Testing Time Intelligence : YoY, YTD, QTD, MTD, etc..

Ch 10: OLAP DATABASE MANAGEMENT

• OLAP DB Backups - Multidimensional, Tabular
• OLAP DB Restores - Multidimensional, Tabular
• Detach and Attach Operations with OLAP DBs
• OLAP Database Processing and XMLA Scripts
• Cube Processing Jobs with DB Engine Agent
• OLAP Database Scripting with XMLA, Cloning
• OLAP Database Security Roles - MDX, DAX
• Partition Management - Split and Merge

• OLAP Cube Audits and Usage Based Optimization
• OLAP Cube Writebacks. Cube Updates with MDX
• Tabular Mode Cube Processing Options
• Direct Query & In Memory with Direct Query
• In-Memory Options with Direct Query in OLAP
• Data Mining - Decision Trees, Clustering Alg"
• Training, Testing Sets. Lift Charts, DMX Queries
• Dimension Types : Fact, Role Playing, Degenerate
• Multidimensional - Tabular Mode Comparisons

Ch 5: PARTITIONS, AGGREGATIONS, PERSPECTIVES

• Partitions : Purpose and Architecture, Tuning Operations
• Cube Partitions : Storage, Slice Options. Query Conditions
• Query Binding and Table Binding Options in Partition Design
• Aggregations - Purpose and Usage. Predefined Calculations
• Aggregation Types: Full, Default, None and Unrestricted
• Measure Properties and Default Aggregation Types in OLAP
• Linking Aggregations and Partitions. Cube Slicing Options
• Additive and Semi-Additive Measures - Aggregation Options

• Storage Modes : Multidimensional, Relational and Hybrid
• Aggregation Storage & Measure Group Storage: MOLAP/ROLAP
• MOLAP - Automatic Processing, Scheduled, Medium Latency
• Low Latency Processing and Custom Scheduling Options
• Proactive Caching Options and Silence / Override Interval
• Cache Rebuild & Overwrite Options. Processing with Partitions
• Perspectives - Purpose and Scenarios For Invalid Cube Access
• Dimension Usage Tab for Dimension Relations. End User Access
• Translations : Creation and Real-time Use. Language Settings

Ch 11: Real-time Project for SSAS

• Working with SQL Server Data Sources
• Data Modelling and Relation Management
• Creating Bridging Tables and References
• Identifying Measures and Measure Groups
• Identifyig Attributes and Dimensions

• Adding Hierarchies and Attribute Relations
• Time Intelligence and BI Enhancements
• Cube Calculations with MDX / DAX
• Defining KPIs, Perspectives, Roles
• Cube Deployment and End user Cube Access

• Adding Hierarchies and Attribute Relations
• Time Intelligence and BI Enhancements
• Cube Calculations with MDX / DAX
• Defining KPIs, Perspectives, Roles
• Cube Deployment and End user Cube Access

Ch 6: KPIs, OLAP CUBE DEPLOYMENTS

• Key Performance Indicators (KPI) - Purpose, Design Options
• MDX Calculations for GOAL, VALUE, STATUS & TREND
• Variance and Value Computations. Format Options, Scope
• KPI Organizer, Calculations. MDX Expressions, Time Analysis
• FORMAT_STRING, MDX Expressions and Member Operators
• MEMBER Names and SOLVE_ORDER Expressions in MDX
• Parent KPIs with Member Hierarchies, KPI Browser Options
• KPIs for Drill-Up, Drill-Down in MS Excel. Pivot Tables

• SSAS Deployment Wizard : BUILD and Configuration Files
• Deployment Options, Settings, Targets: Transaction Settings
• SSAS Deployment Wizard Usage: Impersonation Settings
• SSAS Deployment Accounts and Passwords. Security Inherit
• OLAP Cube Security Roles and Partition Settings (MOLAP)
• Key Error Logs, Custom Storage and Error Locations
• Scripting Deployment Procedure. Executing of XMLA Scripts
• OLAP Database Processing Options - Full, Default, None

Power BI with SSAS

• Multidimensional Mode Cube Access in Power BI
• LIVE and Data Import Storage Modes
• Data Modelling Restrictions in Power BI
• Using KPIs and Measures From OLAP in Power BI
• Using Attributes and Categories in Power BI

• Tabular Mode Cube Access in Power BI
• LIVE and Data Import Storage Modes
• Data Modelling Operations in Power BI
• Incremental Data Loads and Data Refresh
• Creating New DAX Measures in Power BI

• Tabular Mode Advantages with Power BI
• Time Intelligence and BI Enhancements
• Interactive Reports : Cloud Publish
• Data Refresh and Gateway for OLAP DB

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

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)