Chapter 1: Cloud Basics, Azure SQL

• Cloud Introduction and Azure Basics
• Azure Implementation: IaaS, PaaS, SaaS
• Azure Data Engineer: Job Roles
• Azure Storage Components
• Azure ETL & Streaming Components
• Need for Azure Data Factory (ADF)
• Need for Azure Synapse Analytics
• Azure Resources and Resource Types
• Azure Account, Subscription (Free)
• Azure SQL Server [Logical Server]
• Firewall Rules and Azure Services
• Azure SQL Database Deployment
• Azure SQL Pool Deployment
• Compute: DTU Versus DWU
• Test Connections from SSMS

Chapter 2: Synapse SQL Pools (DWH)

• Dedicated SQL Pools in Azure
• Data Warehouse with Synapse
• Massively Parallel Processing (MPP)
• Control Nodes and Compute Nodes
• DMS: Data Movement Service
• Start/Resume/Pause & Scaling
• SQL Pool Config @ TSQL Scripts
• Start/Resume/Pause, Scaling Options
• Table Creations @ TSQL Scripts
• Table Partitions: Left & Right
• Distributions: Round Robin, Hash
• Distributions: Replicate and Usage
• Auto Indexing & Column Store
• Planning for Big Data Loads
• Need for ADF: Azure Data Factory

Chapter 3: Azure Data Factory, Pipelines

• Azure Data Factory (ADF) Concepts
• ADF Pipelines : Architecture
• Integration Runtime (IR) & Use
• Linked Services and Datasets
• Pipeline Activities: Copy Data Tool
• DIU : Data Integration Units
• DTU Vs DWUs Vs DIU
• ADF Pipeline with Copy Data Tool
• Azure SQL DB to Synapse Data Loads
• Multi Tables Data Loads with ADF
• Bulk Insert, Data Copy Methods
• ETL Staging: Storage Account
• Staging Container Connections
• DIU Allocations & Publish
• ETL Pipeline Monitoring, Runs

Chapter 4: OnPremise Data Loads, Upsert

• Copy Data Tool : Incremental Loads
• On-Premise Data Sources with Azure
• Self Hosted Integration Runtime (IR)
• Access Keys, Remote Linked Service
• Synapse SQL Pool (DW), OnPremise
• ETL Staging with Storage Account
• Copy Method: Polybase – Tuning
• Polybase : Big Data Loads
• ETL Pipelines for Incremental Loads
• Business Keys For Table Upsert
• Pipeline Schedules with ADF
• ETL Logging with Storage Account
• Copy Method: UPSERT
• DIU, DOCP & Publish
• Manual Pipeline Executions in ADF

Chapter 5: File Incremental Loads in ADF

• Incremental Loads with Files (BLOB)
• ETL Schedules: Tumbling Window
• Execution Retry and Delay Options
• Binary Copy, Structural Data Loads
• Incremental Loads Verification Tests
• Incompatible Rows & Fault Tolerance
• Pipeline Compression & Tuning
• Pipeline Publish, Monitor Options
• Azure Monitor Resource : Metrics
• ADF Metrics and Pipeline Runs
• ADF: Pipeline Monitoring and Alerts
• Synapse: Storage Monitoring, Alerts
• Conditions, Signal Rules and Metrics
• Alerts & Action Groups: Emails
• Email Notifications with Azure

Chapter 6: ADF Data Flow – 1

• Data Flow Task, Data Flow Activity
• Transformations with Data Flow
• Spark Cluster For Debugging
• Cluster Node Configurations
• Spark Cluster Types & Sizing
• Transaction Optimized – Capacity
• Memory Optimized – Capacity
• Data Cleansing with ADF
• Data Orchestration with Data Flow
• SELECT Transformation & Options
• Conditional Split Transformation
• UNION, SELECT Transformation
• Spark Cluster For Pipeline Executions
• Pipeline Monitoring & Run IDs
• Adding Data Flow into Pipelines

Chapter 7: ADF Data Flow – 2

• ADF Pipelines For ETL Operations
• Data Flow Tasks, Activities in Synapse
• JOIN & EXISTS Transformations
• Aggregate & Group By Transformations
• Window Functions, Rank in Data Flow
• Rank / DenseRank / Row Number
• Derived Column Transformation
• Lookup, Surrogate Key, Parse
• Type Convert, Cast Transformations
• Reusing Data Flow Tasks in Synapse
• Pipeline Validations & Executions
• Inline Datasets, Schema Drift
• Data Deduplication with ADF
• DFT Optimization Techniques
• Data Flow Task – Staging, Logging

Chapter 8: Azure Synapse Analytics

• Azure Synapse Analytics Resource
• Azure Synapse Analytics Workspace
• Managed Resource Group, SQL Account
• Synapse Workspace & Synapse Studio
• Operations with Synapse Workspace
• ADLS Gen 2 Storage Account, Container
• Synapse Studio: Scripts & Pipelines
• Dedicated SQL Pools : Creation, Use
• Synapse Tables, Data Loads with TSQL
• COPY INTO Statements with T-SQL
• Row Terminator and Compressions
• T-SQL Queries and Aggregations
• Aggregation Data Loads in Synapse
• Creating Synapse Pipelines with TSQL
• Stored Procedure Activity & Triggers

Chapter 9: Synapse Analytics with Spark

• Synapse Pipelines: Performance Advantage
• Pivot Transformation For Normalization
• Generate Pivot Column, Aggregations
• Pivot Transformation & Pivot Setting
• Pivot Key Selection, Value and Nulls
• Pivoted Columns and Column Pattern
• Column Prefix, Help Graphic, Metadata
• Denormalized Data and Aggregations
• Apache Spark Pool in Azure Synapse
• Spark Cluster Nodes: Vcores, Memory
• Notebooks : Purpose, Usage Options
• Python Notebooks For Remote Access
• Creating Databases in Apache Spark Pool
• Data Loads from Dedicated SQL Pools
• PySpark Code for Data Operations, Writes

Chapter 10: Synapse Security & Parameters

• Azure Active Directory (AAD) Users, Groups
• IAM: Identity & Access Management
• Synapse Workspace Security with RBAC
• ADF Security: RBAC, Owner, Contributor
• Azure Synapse SQL Pool Security: Logins
• Creating SQL Logins & Users : master
• SQL Users in Azure SQL DB and SQL Pool
• Grant, Control, Revoke: Security Roles
• Parameters – Creation and Use in Pipelines
• Dynamic Connections with Credentials
• User Name and Password Connectivity
• Dynamic Dataset Configurations
• Pipeline Expressions with Parameters
• Resource Classes and Usage with SQL Pool

Chapter 11:  Change Data Capture (CDC)

• Change Data Capture (CDC) Data Loads
• Incremental Loads with CDC Types
• SQL Server CDC : ETL Load Dates
• Pipeline Expression, Data Window
• JSON Parameters, Pipeline Scheduling
• ETL Optimization Techniques
• Serverless Pool in Azure Synapse
• Connections, Use with Serverless Pool
• Using Azure OpenDatasets in Synapse
• OPENROWSET and BULK Data Loads
• Working with Parquet Files in Synapse
• Python Notebooks (Pyspark) in Synapse

Chapter 1: Azure Fundamentals – Storage

• Azure Resources: Storage Components
• Storage Resources and Properties
• Resource Groups & Subscriptions
• Azure Storage : Files, Tables and ETL
• Azure Storage Account & Use
• Data Lake Storage Account (ADLS)
• Advanced Options: HNS Property
• Resource Location, Resource Group
• Azure Portal: Deployment Verifications
• Azure Portal: Deployment Verification
• Storage Account : Basic Properties
• Overview Page: Status, HNS State
• Azure Storage : Access Options
• Azure Storage Explorer Tool
• Explorer Tool : Configuration

Chapter 2:  Azure Storage Operations

• BLOB: Binary Large Objects
• Storage Browser and Service Pages
• Storage Browser: Container Creation
• Storage Browser: Folder, File Uploads
• Service Page: Container Creation
• Service Page: Folder, File Uploads
• Container, Folder, File Properties
• Limitations with Storage Portal
• Azure Data Explorer Tool : Usage
• Contrainer: Creation, Properties
• File Uploads, Edits and Access URLs
• Azure Storage Explorer Tool Usage
• Azure Account Options in Explorer
• Directory Creation, File Operations
• Limitations with Explorer Tool

Chapter 3:  Azure Storage Security, ACLs

• Azure Data Lake Storage Security Options
• Shared Access Keys: Primary, Secondary
• SAS Key Generation: Container, Tables
• SAS Key Permissions, Validation Options
• Access Keys: Account Level Permissions
• Azure Active Directory: Users, Groups
• Azure AD Security: RBAC, IAM, ACLs
• Owner Role, Contributor, Reader Role
• Azure Data Lake Storage Security
• ACL : Access Control Lists & Security
• Azure BLOB Storage Containers & ACLs
• Folder Level and File Level Security
• ACL Permissions: Read, Write, Execute
• Access Policy: Creation, Realtime Use
• rwacdl; Azure Principals, CORS

Chapter 4:  SQL Database Migrations

• OnPremise SQL DB to Azure Migration
• SSMS Tool, SQL Database Installation
• Source Database Scripts & Validations
• BACPAC File Generation: SSMS Tool
• Table Selection & Advanced Options
• Azure Data Lake Storage, SSMS Access
• Azure Storage Container, BACPAC Files
• IAM and Account Key Authentication
• Azure SQL Server Creation From Portal
• Azure SQL Database Deployment
• DTU : Data Transaction Units, Pricing
• Azure Firewall Configuration, Security
• Azure SQL Database Imports (bacpac)
• Azure SQL Server with ADLS Containers
• Azure SQL DB Migrations, Verification

Chapter 5:  Azure Tables & Replication

• Azure Tables – SchemaLess Design
• Azure Tables: Creation, Data Inserts
• Tables, Entities, Properties Concepts
• Structured, Relational Data Storage
• Azure Tables: GUI, Data Types
• Azure Tables: Big Data Imports
• Data Edits, Queries, Delete Operations
• Odata Options (REST API), End Points
• Azure Storage: Replications, DR Options
• LRS: Locally Redundant Storage
• GRS: Globally Redundant Storage
• ZRS: Zone Redundant Storage
• Replication Options and Advantages
• Replication Verification, Modifications
• Storage Endpoints, Failover Partner

Chapter 6: Azure Stream Analytics, IoT

• Azure Stream Analytics Real-time Use
• Real-time Data Processing, Events
• Ingest, Deliver & Analysis Operations
• Azure Stream Analytics Jobs Concept
• Understanding Input, Output Options
• SAQL Queries: Stream Analytics Jobs
• IoT: Internet Of Things, Real-time Data
• Need for IoT Hubs and Event Hubs
• Conditional Split Transformation
• Creating IoT Device for Data Inputs
• Creating Azure Stream Analytics Job
• Stream Analytics for Historical Data
• Azure SQL Database for ASA Jobs
• SAQL: Query Formatting, Validation
• Historical Data Upload, ASA Jobs

Chapter 7: Azure Event Hubs

• Azure Stream Analytics For API Data
• IoT Hubs, IoT Devices, Connection Strings
• Rasberry APP Connections with IoT Hub
• Azure Storage Account and Container
• Creating Azure Stream Analytics Job
• Configuring Input Aliases with IoT Hub
• Output Aliases with ADLS Gen 2
• SAQL Query, Job Executions; Monitoring
• Azure Event Hubs and Event Instances
• Event Hub Namespaces, Partition Counts
• Access Policies, Permissions & Defaults
• RootManageSharedAccessKey & Options
• Connection Strings & Event Service Bus
• Telco App : Executions & LIVE Data
• On-Premise App Integration, ASA Jobs

Chapter 8: Storage Architecture, Queues

• Azure Storage Account : Architecture
• Etag: Replication & Encryption Use
• BLOB Types: Block, Append & Page
• Access Tiers: Hot, Cool, Cold Types
• Archive Access Tier & Retention
• Legal Hold & Time Bound Access
• Pricing : HNS, Security, Encryption
• EndPoint URL & Read-Only Use
• Azure File Share Service (Files)
• Mounting Files From On-Premise
• SMB File Share : Hot, Optimized
• Azure Queue Service & Messages
• Message Queues : Operations
• Storage Explorer Tool with Shares
• Azure Storage Services: ETL Needs

Chapter 9:  Monitoring & Key Vaults

• Azure Monitor, Metrics & Activity Logs
• Monitoring Azure Storage Namespaces
• Add KQL Metrics; Account, Blob and File
• Total Ingress and Egress Metrics: Charts
• Average Latency, Transaction Count
• Request Breakdowns, Signal Logic
• Azure Alerts & Conditions, Notifications
• Signal Logic Conditions and Emails
• Key Vaults Types: Standard & Premium
• Secret Page, Key Backups, Key Restores
• Azure Key Vaults – Name and Vault URI
• Inbuilt Managed Key and Azure Key Vault
• Key Vaults Types: Standard & Premium
• Secret Page, Key Backups, Key Restores
• Managed Identity with ETL Process

Real-time Project (End to End)

• Online Retail Database Data Source
• Azure Migrations and ETL Concepts
• Azure SQL Pool (Synapse DWH) Tables
• Apache Spark Pool : Databases, Tables
• Azure Data Lake Storage (ADLS Gen 2)
• Handling Unstructured Data in ADF
• End to End Workflows, Automations

• Azure Logic Apps: Automated Workflows
• Visual Designer & Prebuild Templates
• Server Less Integrations in Azure
• Workflow, Triggers and Actions
• Managed Connectors, Integrations
• ARM Template : Deployments
• ARM Templates : ADF, ADLS

• ADLS with Spark Databases
• Aggregations with Big Data Loads
• Parameterized ETL Sources
• Parameterization & Workflows
• Python Notebooks to Scala
• Azure SQL DB Connections
• ARM Templates & JSON

• Project Requirement
• Project Solution, FAQs
• Concept wise FAQs
• Resume Guidance
• Mock Interviews (1 to 1)
• DP 203 Certification Guidance
• DP 203 Sample Papers (Latest)

Chapter 1: Azure Intro, Azure Databricks

• Azure Cloud : SaaS, PaaS, PaaS & IaaS
• Azure Cloud : Storage, ETL Resources
• Azure Databricks : Compute Resources
• Need for Azure Databricks (ADB)
• Azure Databricks : Purpose & Config
• Azure Databricks Service Creation
• Azure Databricks Components
• Azure Databricks Workspace, Usage
• Spark Cluster Configurations, Capacity
• Driver Nodes, Worker Nodes in Spark
• Cluster Types : Personal, Unrestricted
• CPU, Memory & IO Resources
• Virtual Machines (VM) for Clusters
• Databricks : Runtime & DBFS Storage
• DBFS : Files, Tables with Spark DB

Chapter 2: SparkDatabase, SQL Notebooks

• DBFS : File Uploads from ON-Premise
• Creating Spark Tables; Spark DB
• Data Explorer: HIVE Metastore
• Data Explorer: Spark Database, Tables
• Notebooks: SQL, Python and Scala
• Creating SQL Notebooks in Databricks
• Creating User Defined Spark Databases
• Connecting / Using Spark Databases
• Spark SQL : Big Data Loads
• Spark SQL : Database & Table List
• Spark SQL : Data Aggregations, Jobs
• Spark SQL : Data Analytics, Reports
• Analytics: X, Y Axis, Group By
• Notebooks : Export, Import, Clone
• Notebooks : Storage & Versions

Chapter 3: Python Intro,  Data Loads

• Python : Introduction, Real-time Use
• Python For ETL and DWH
• Python For Azure: Data Engineer
• Python Data Frames & Purpose
• Python Dataframes – Pandas
• Python with Spark Integrations
• PySpark for DDL and ETL
• PySpark Versus SQL Notebooks
• Reading DBFS Data into Spark
• Creating Dataframes for ETL
• Temporary Views & Dataframes
• Spark Temp Views: Aggregations
• Spark Table Loads, HIVE Data
• write.format() & overwrite
• Parquet Tables with Spark DB

Chapter 4: PySpark with ADLS

• Azure Storage Account : Creation
• Azure Data Lake Storage : HNS
• Creating Containers in ADLS
• BLOB File Uploads / Generation
• Account Key : Access Key, SAS Key
• BLOB Access URL for Databricks
• WASBS URL for PySpark Notebook
• Generating PySpark Script
• PySpark Connection Variables
• Databricks : Data Import Scripts
• Config Options with ADLS, Spark
• config (), Session Context
• DataFrames with Temp Tables
• Escape Sequence with SparkSQL
• Data Explorer: HIVE & Spark DB

Chatper 5: PySpark Widgets

• Widgets : Notebook Parameters
• widget module : Text, Combo
• Dropdown, Multi Select Parameters
• dbutils help(), get() & remove()
• Dataframes, Spark SQL @ Variables
• Python Data Frames, Spark SQL
• Reading Parameters Values
• Parameters Versus Variables
• Using Parameters For Temp Tables
• Using Parameters for Spark Tables
• Data Storage and HIVE Metastore
• Reading Parameterized Data
• Format Strings with PySpark
• Dynamic Queries with Spark SQL
• Aggregations and f Strings

Chapter 6: Architecture, Workflows

• Driver Nodes, Worker Nodes, DBUs
• RDD : Resilent Data Distribution
• DAG : Directed Acyclic Graph
• Hadoop HDES and Spot Instance
• Cluster Manager, Master Node
• RDDS, Worker, Excecutor & Slave
• Hadoop HDES & Databricks Runtime
• Databricks Optimization Techniques
• Spot Instance, Photon Acceleration
• All Purpose Cluster, Job Cluster
• Databricks Jobs: Creation & Tasks
• Jobs with Parameters, Executions
• Task Dependency & Notifications
• Continuous & Manual Schedules
• Active Jobs, Recent Run Jobs, Monitor

Chapter 7: Databricks Security, Scala

• Azure Databricks Security Operations
• Azure Active Directory (Azure AD)
• AD Users and RBAC with IAM
• Owner, Contributor & Reader Roles
• Workspace Admin Permissions
• Notebook Permissions & Share
• Workflow Security, HTTP Path
• User Tokens & ServerName
• Scala : Differences with PySpark
• Scala : Variables Declaration, Usage
• SparkSQL with Scala Notebooks
• Temp Views with Scala Notebooks
• Aggregations with Scala Notebooks
• Visual Data Analytics with Scala
• PySpark to Scala Conversions

Chapter 8: Scala with ADLS, Azure SQL

• Data Imports with Azure SQL DB
• Using Scala for Big Data Loads
• Spark SQL Queries @ Temp Views
• Variables, display(), read()
• Scala Transformations, display()
• JSON, AVRO and DBFS Mounts
• azure.sas.container @ ADLS
• write.jdbc() & JVM
• JDBC Connection, DataframeWriter
• Data Extraction, SQLContext
• Spark Context and Spark Session
• SQLServerDriver with Scala
• ADLS with Scala Notebooks
• Parameters (Widgets) with Scala
• Compare Python with Scala

Chapter 9: DeltaLake Incr Loads, DWH

• Azure DeltaLake Implementation
• ACID Properties, Upsert Advantages
• Delta Engine Optimizations & Uses
• Pipeline Creation: JSON Files in DBFS
• Delta Tables Creation, Data Loads
• Spark Cluster Settings: Auto Optimize
• Auto Compact, Delta Table Optimize
• JSON Files, Delta Streaming Location
• Joins and Merge with Delta Tables
• Incremental Loads, Delta Tables
• Create & Use DWH with Databricks
• Upsert (Merge) with Spark Tables
• Big Data & Jupyter Notebooks
• Databricks with Data Factory (ADF)
• End to End Implementations

Azure Data Engineering with Power BI (For Power BI Registrations)

• Power BI with Synapse SQL Pool
• Power BI with Synapse Analytics
• Get Data: Storage Modes
• Direct Query, Performance Inspector
• Aggregated Data Analytics
• Data Gateways : Auto Refresh

• Power BI with ADLS : Record Query
• Power BI with ADLS : BLOB Data
• Power BI with Spark DB : JDBC
• Power BI with Spark DB : User Tken
• Power BI with Spark DB : LIVE Data
• Power BI with Spark DB : Refresh

• Azure Purview : Data Governance
• Unified SaaS for Multi Cloud
• Data Mapping and Resilence
• Automated Data Discovery
• Sensitive Data Labels : SQL Server
• Interactive Data Lineage

• Trusted Data Discovery in Azure
• Confidential Data & Trust
• DataCatalog, Data Estate Insights
• Azure Key Vaults, ADLS Security
• Azure Passwords, Keys, Certificates
• Azure Key Vaults – Name, Vault URI
• Managed Key & ETL Connections

Ch 1: POWER BI INTRODUCTION

• Power BI : Introduction to Analytics
• Power BI Tools Suite, Advantages
• Power BI : Career Options, Plan
• Power BI Developer Job Role
• Microsoft Data Analyst Job Role
• Big Data Analyst Job Role
• Power BI Data Analyst (PL 300)
• Data Engineer*, Power BI (DP 500 *)
• Artificial Intelligence (AI) Visuals
• AI Enabled Power BI Features
• Course – Lab Plan with Design Tools
• Need for Power Query & DAX
• Power BI Licensing Types
• Power BI Cloud – Advantages
• Power BI Report Server Advantages

Ch 2: Basic Report Design

• Power BI Eco System: Architecture
• Data Sources & Types in Real-world
• Report Types: Interactive, Paginated
• Analytical Reports & Mobile Reports
• Data Sources : File, Database, Web
• Visualizations : Report Shapes
• Power BI Design Tools, Requirements
• Power BI Desktop Tool : Installation
• Desktop Interface: Overview, Canvas
• Get Data, Data View, Report View
• In-Memory Xvelocity Database
• Basic Visuals: Table, Tree Map
• Data Labels, Legend, Category
• Local Store: PBIX & PBIT Files
• Data Points and Tooltips

Ch 3: Visual Interaction, Visual Sync

• Visual Interaction with Data Points
• Disabling / Enabling Interactions
• Edit Interactions: Format Options
• Spotlight and Focus Mode
• Report Export to CSV, PDF
• Tooltip Options and Usage
• Working with Pages in PBI
• Rename, Duplicate, Hide Pages
• Slicer Visual : Real-time Usage
• Orientation, Selection Properties
• Slicer Settings : Tiles & Slider
• Single & Multi Select, Header
• Number, Text, Show Summary
• Date Slicer and Value Selections
• Slicer List, Dropdowns & Clear

Ch 4: Grouping & Hierarchies

• Grouping : Visuals with Pdf Sources
• 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
• Group with Bins & Clustering
• Group, Layer with Selection Pane
• Creating Hierarchies in Power BI
• Independent, Dependant Drill-Down
• Drill-Down with Interactive Reports
• Conditional Drilldowns, Data Points
• Drill Up Buttons and Operations
• Expand & Show Next Level
• Dynamic Data Drills Limitations

Ch 5: Filters & Bookmarks

• Filters : Types and Usage in Real-time
• Visual Filter, Page Filter, Report Filter
• Basic, Advanced and TOP N Filters
• Category and Summary Level Filters
• Data / Drill Options, DrillThru Filters
• Keep All Filters” Options in DrillThru
• CrossReport Filters, Include, Exclude
• Drill-thru Filters, Page Navigations
• Bookmarks : Report Navigations
• Buttons, Images with Actions
• Selection Pane, Actions, Text URLs
• Show Data and See Records
• Custom Tooltips, Table Visual
• Table Vs Matrix : Drill-downs
• Styles, Cell Properties, Databars
• Conditional Formatting, Divergent

Ch 6: Big Data Access, Visuals

• OLTP Databases, Big Data Sources
• Azure Database Access, Reports
• Import, Direct Query & Dual Mode
• Data Modeling: Do Not Summarize
• Data Modeling: Currency, Relations
• Power BI Archtiecture, Eco System
• Power BI Interface for Reports
• Stacked Chart, Clustered Chart
• Line Chart, Area Chart, Bar Chart
• 100% Stacked Bar & Column Chart
• Map Visuals: Tree, Filled, Bubble
• Small Multiples, Legends, Axis
• Cards, Funnel, Table, Matrix
• Scatter Chart : Play Axis, Labels
• Waterfall Chart, Multi Row Cards

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
• let, source, in statements @ M Lang
• Get Data, Table Creations and Edit
• ETL Operations with Power Query
• Merge Transformations in Power BI
• Join Kinds: Inner, Outer & Apply
• Union All Transformation & Appends
• Power Query Editor, Step Edits
• Close & Apply Options. Report Design

Ch 8: POWER QUERY LEVEL 2

• Query Duplicate, Query Reference
• Group By and Advanced Options
• Aggregations with Power Query
• Transpose, Header Promotion
• Reverse Rows and Row Count
• Data Type Changes & Detection
• Replace Columns: Text, NonText
• Advanced Query Edit Options
• Replace Nulls: Fill Up, Fill Down
• Pivot, Unpivot Transformations
• Move Column and Split Column
• Date & Time Transformations
• Derive Year, Quarter, Month, Day
• Add Column : Query Expressions
• Query Step Inserts and Step Edits

Ch 9: POWER QUERY LEVEL 3

• Big Data Loads : Parameter Queries
• Creating Parameters in Power Query
• Parameter Data Types, Default Lists
• Static & Dynamic Lists: List Queries
• Convert Tables to Lists, Use Cases
• Linking Parameters to Queries
• Testing Parameters with Canvas
• Multi-Valued Parameter Lists
• Creating Lists in Power Query
• Converting Lists to Table Data
• Invoke Function, Type Conversions
• Function Query & Parameter List
• Columns From Examples, Indexes
• Conditional Columns, Expressions
• Disable / Enable Data Loads

Ch 10: POWER BI CLOUD – 1

• Power BI Cloud Components
• App Workspaces, Report Publish
• Reports & Related Datasets Cloud
• Creating New Reports in Cloud
• Report Publish, Report Uploads
• Report Edits and New Reports
• Report Actions: Downloads
• Dataset Usage Options in Cloud
• Dashboards Creation and Usage
• Pining Visuals and Report Pages
• Visual Pin Actions in Dashboards
• Dashboard & LIVE Interactions
• Media Tiles: Images, Custom Links
• Q & A Option with Dashboards
• Pin with Q & A; Standard Visuals

Ch 11: POWER BI CLOUD – 2

• Report Actions : Share, Subscribe
• Report Actions : Lineage, Embed
• Report Actions : Export Options
• Report Actions : Public User Access
• Dashboard Actions : Share, Subscribe
• Dashboard Actions : Themes, Lineage
• Dashboard Actions : Share, Subscribe
• Favorite, Insights, Embed Code
• Gateways Configuration, PBI Service
• Gateway Types, Cloud Connections
• Gateway Cluster, Add Data Sources
• Data Refresh : Manual, Scheduled
• Power Query Parameters, Gateways
• DataFlows, Power Query in Cloud
• Lineage, Share, Subscribe, Insights
• Performance Inspector& Gateways

Ch 12: POWER BI CLOUD – 3

• Workbooks : Excel Online & Pins
• Power BI Apps: Creation & Usage
• Power BI Segments, Content
• Navigation Screens, Audience
• App Publish, Verification & Edits
• Export, Share & Subscribe
• List View & Lineage View Options
• Power BI Scorecards: Realtime Use
• Paginated Reports – Design & Usage
• Power BI Report Builder Tool
• Microsoft Report Builder Tool
• Report Builder : Datasets, Charts
• Report Builder : Bar Charts, Fields
• Report Builder : Creating RDL Files
• Paginated Reports : Deployments

Ch 13: DAX Functions – Level 1

• DAX : Importance in Real-time
• DAX Data Types, Syntax Rules
• DAX Measures and Columns
• ROW Context and Filter Context
• Operators, Special Characters
• DAX Functions, Vertipaq Engine
• DAX Cheat Sheet : Expressions
• Data Analytics with DAX
• DAX Measures : Expressions
• ISBLANK, IF, IN, SUM
• SUMX, AVG, AVERAGEX
• Data Models: Fact, Dimensions
• Detecting Relations for DAX
• Star & Snowflake Schemas
• Data Modeling Options in DAX

Ch 14: DAX Functions – Level 2

• Quick Measures in Power BI
• Average and Filtered Average
• Running Totals, EARLIER( )
• RELATED, COUNTROWS
• CALCULATE Function Conditions
• ALL Members Scope & IN
• Account and Time Calculations
• Star Rating, DAX Expressions
• Data Modeling Options in DAX
• 1:1, 1:M and M:1 Relations
• Working with Facts & Measures
• Modeling : Missing Relations
• Relationships & Importance
• Modeling : Relation Management
• Modeling with Multiple Keys

Ch 15: DAX Functions – Level 3

• DAX : Variables and Expressions
• Dynamic Expressions, RETURN
• Current Value, Previous Value
• SELECTED VALUE, Joins
• FORMAT Function with DAX
• RELATED, Joins in DAX
• DAX Expressions with SQL DB
• Time Intelligence Functions
• Date Dimension : Generation
• CALENDAR(), DATESYTD()
• TOTALYTD, TOTALQTD
• TODAY, DATE, DAY with DAX
• SELECTEDVALUE, FORMAT
• Date, Time and Text Functions

Ch 16: DAX Functions – Level 4

• RLS: Row Level Security
• Data Models in Power BI Desktop
• DAX Roles Creation and Testing
• DAX Expressions & Operators
• PBIX Uploads: Power BI Cloud
• Dataset Security with DAX Roles
• Entity Sets and Slicing in DAX
• Dataflows with Power BI
• Analytical Reports – DAX Usage
• Creating Data Models with DAX
• Datasets in Excel and Dashboards
• Using Excel Analyzer in Power BI
• Power BI Data Source in Excel
• Connection Strings and Refresh
• Analytical Reports – Limitations

Ch 17: Power BI Report Server

• Power BI Report Server Config
• SQL Server Instance Verifications
• Report Server DB, Temp Database
• WebService & WebPortal URL
• Uploading Interactive Reports
• End User Report Share (pdf)
• Power BI Desktop RS Tool
• Interactive Reports: Report Server
• Mobile Reports : Design Options
• Mobile Reports : Grids, Elements
• Mobile Reports : Uploads, Edits
• Paginated Reports : Deployments
• Paginated Vs Interactive Reports
• Paginated Vs Analytical Reports
• Paginated Vs Mobile Reports
• Power BI Report Server Vs Cloud

Ch 18: Power BI Admin & AI

• Power BI Cloud Management
• Power BI Admin : Alerts
• Workspace Management, Users
• Security: Report, Dataset Levels
• Security: Dataset, App Levels
• Security: Workspace Options
• PBI Performance Inspector
• Power BI & Artificial Intelligence
• Power BI & CoPilot Add-Ins
• AI Visuals & Big Data Analytics
• Smart Narrative and Q & A
• Infographics, Icons and Labels
• Key Influencer Visual in Power BI
• Metrics Visual, Performance
• Paginated Reports Visual

Ch 1: DATABASE INTRODUCTION

• Databases Introduction & Purpose
• Database Types : OLTP, DWH, OLAP
• Microsoft SQL Server Advantages, Use
• SQL Server Components and Usage
• Microsoft SQL Server – Career Options
• Developer, DBA, Data Engineer
• Data Analyst, Data Scientist Careers
• SQL : Purpose, Real-time Usage Options
• SQL Versus Microsoft T-SQL [MSSQL]
• Course Plan, Real-time Project, Resume
• 24 x 7 Online Lab for Remote DB Access
• Versions and Editions of SQL Server
• SQL Server Pre-requisites : S/W, H/W
• System Configuration Checker Tool

Ch 2: SQL SERVER INSTALLATION

• SQL Server & SSMS Installation Plan
• SQL Server Pre-requisites : S/W, H/W
• SQL Server 2022 & 2019 Installation
• Database Engine Feature, OLTP
• Instances : Types and Properties
• Default Instance, Named Instances
• Service and Service Account Use
• Authentication Modes and Logins
• Windows Logins and SQL Logins
• SQL Server Management Studio
• Server Connections with SSMS Tool
• Local and Remote Connections
• System Databases: Master and Model
• MSDB, TempDB, Resource Databases

Ch 3: SSMS Tool, SQL BASICS – 1

• Creating Databases: Files [MDF, LDF]
• Creating Tables in User Interface
• Data Insertion & Report in User Interface
• SQL : Purpose and Real-time Usage
• SQL Versus T-SQL : Basic Differences
• DDL, DML, SELECT, DCL and TCL
• Creating SSMS Sessions : SPID
• Create, Connect Databases using SQL
• Creating Tables with INT, CHAR
• Data Storage, Inserts – Basic Level
• Table Data Verifications with Select
• SELECT Statement for Table Retrieval
• Identify Databases and Tables
• Identify Sessions and Session ID

Ch 4: SQL BASICS – 2

• Creating Tables: VARCHAR, FLOAT
• Single Row Inserts, Multi Row Inserts
• Rules for Data Insertion Statements
• SELECT with WHERE Conditions
• AND and OR Operators Usage
• IN Operator and NOT IN Operator
• Between, Not Between Operators
• LIKE and NOT LIKE Operators
• ORDER BY, TOP & OFFSET
• Basic Sub Queries with SELECT
• UPDATE Statement & Conditions
• DELETE & TRUNCATE Statements
• ALTER, ADD COLUMN Statements
• DROP Statements: Table, Database

Ch 5: SQL Basics – 3, TSQL 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 Retrieval & 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 6: Constraints, Index 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 & 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 7: Joins Basics, TSQL Queries

• JOINS – Table Comparisons Queries
• INNER JOINS For Matching Data
• OUTER JOINS For (non) Match Data
• Join Queries with “ON” Conditions
• Left Outer Joins – Example Queries
• Right Outer Joins – Example Queries
• FULL Outer Joins: Realtime Scenarios
• CROSS JOIN and CROSS APPLY
• One-way, Two way Data Comparisons
• Using Table Aliases & Column Aliases
• Optimizing Join Queries with Indexes
• Choosing Correct Comparison Columns
• Joining Unrelated Tables in TSQL
• Self References, Self Joins in TSQL

Ch 8: Group By in TSQL, Views Basics

• GROUP BY: Importance, Realtime Use
• GROUP BY Queries and Aggregations
• Group By Queries with Having Clause
• Group By Queries with Where Clause
• Using WHERE and HAVING in T-SQL
• Group By with Joins in TSQL
• Query Execution Order & Aliases
• Joins with Sub Queries, Formatting
• Database Objects: Overview & Usage
• Views: Types, Usage in Real-time
• Creating, Executing & Verifying Views
• Storing Queries in Database Views
• Excel Analytics – Joins & Views
• Excel Office Data Connection Reports

Ch 9: Functions, Procedures Basics

• Functions with SQL Server, TSQL
• Scalar, Inline, Table Functions
• Variables: Declare, Real-time Use
• Creating, Executing Functions
• Functions for Computations
• Functions for Parameterized Joins
• Procedures: Usage in Real-time
• Using Parameters in SQL Server
• Parameterized Joins in TSQL
• Compilation with Stored Procedures
• sp_help, sp_helptext, sp_helpindex
• sp_helpdb, sp_rename, sp_recompile
• System Views For Metadata Audits
• DBID, DBName, ObjectID, ObjectName

Ch 10: 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
• Batch Concept and Go Statement
• Open Transactions in Real-time
• Using Conditional Commits, Rollbacks

Ch 11:  Normal Forms, Cursors

• First Normal Form and Atomicity
• Third Normal Form and MVD Property
• Boycee-Codd Normal Form : BNCF
• Fourth Normal Form : Advantages
• Self Reference Keys and 4 NF Usage
• 1:1, 1:M, M:1, M:M Relationship Types
• Linked Servers Configurations, RPC
• Linked Servers, Remote Joins in TSQL
• 2 Part, 3 Part, 4 Part Naming Styles
• Remote Joins Queries and Aliases
• Cursors – Basics, Data Operations
• Cursors – Life Cycle & Declaration
• Cursors Types, FETCH Operations
• Cursors – Deallocate, Real-world Use

Ch 12: TSQL Queries, SQL Analytics

• IIF() Function with SELECT Query
• WHEN..THEN..ELSE
• WHEN MATCHED, NOT MATCHED
• Incremental Loads, Upsert Statement
• Stored Procedures: Merge Statement
• UNION and UNION ALL Operator
• Window Functions: Rank, Dense Rank
• Row_Number, PartitionBy in TSQL
• Duplicate Row Identification, Deletion
• Grouping, Cube, Rollup, Lag, Lead
• Data Types: Numerical, Date, Time
• Data Types: Characters, Real, Float
• Date & Time Functions, DateAdd
• String Functions, Concat, SubString