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 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 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: 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 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 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: 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 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 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: 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 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 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

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 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

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: 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 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 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: 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 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 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: 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 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 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: 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 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

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

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

Real-time Project (End to End)

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

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

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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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

Phase 1 : Basic Report Design

• Project Requirement Analysis
• Requirement Gathering, FSA
• Report Design with Excel
• Basic Data Modelling
• Infographics, Histograms
• Analytics and Formating

Phase 2 : SME Level

• Report Design with SQL DB
• SQL Database : Joins, Views
• Dual Storage Mode, SQL Queries
• Data Modeling, Power Query
• Dynamic Connections, Azure DB
• Parameters and M Lang Scripts

Phase 3: Deployments (Cloud, Server)

• DAX Requriements, Analysis
• Cloud and Report Server
• Custom Visualizations
• 3party Visuals & REST API *
• Project FAQs and Solutions
• One - One Resume, Mock Interview

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 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 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 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 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 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 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 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 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 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 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 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