Course Overview:

Our Azure Data Engineering course will help IT professionals become subject matter experts, connecting,
Transforming and integrating data from various structured and unstructured data sources into suitable
Building for developing research solutions.

Course Content:

Chapter 1: Cloud Introduction & Azure Basics

• Azure Implementation: IaaS, PaaS, SaaS, Benefits of Azure Cloud Environment.

•  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, Resource Groups in Azure Portal.

•  Azure SQL Server [Logical Server].

•  Schema Migration & Data Migration.

Chapter2 Synapse SQL Pools

• Dedicated SQL Pools in Azure.

•  Enterprise Data Warehouse with Synapse.

• DWU: Data Warehouse Units, Resources.

•  Massively Parallel Processing (MPP).

•  Control Nodes and Compute Nodes.

•  SQL Pool Access from SSMS Tool.

•  T-SQL Queries @ SQL Pools.

•  Start/Resume/Pause, Scaling Options.

•  Creating Tables in Azure SQL Pool.

•  Compression, MAX DOP & Indexes.

•  Distributions: Round Robin, Hash.

•  Distributions: Replicate and Usage.

•  Data Imports.

•  Dynamic Views with PDW.

•  Data Loads Monitoring, Resource Class.

Chapter 3: Azure Data Factory Concepts

• Azure Data Factory (ADF) Concepts.

•  Hybrid Data Integration at Scale.

•  ADF Pipeline Components & Usage.

•  Configure ADF Resource in Azure.

•  Understanding ADF Portal and IR.

•  Linked Services and Connections.

•  Datasets and Tables / Files for ETL.

•  ADF Pipelines: Design, Publish & Trigger.

•  ADF Pipeline with Copy Data Tool.

•  Creating Azure Storage Account.

•  Storage Container, BLOB File Uploads.

•  Data Loads with Azure BLOB Files.

•  DIU Allocations & Concurrency.

•  Linked Services, Datasets.

•  Pipeline Trigger, Author and Monitor.

Chapter 4: ADF Pipelines & Polybase

•  Copy Data Tool for ETL Operations.

•  Azure SQL DB to Synapse Data Loads.

•  Working with Multi Tables Data Loads.

•  Query Options for Source Datasets.

•  Transformations with Copy Data Tool.

•  Rename, Rearrange & Remove Options.

•  Pipeline Execution: DTU & DOCP.

•  ADF Pipeline Monitoring Options.

•  ADF Pipelines: Execution Settings.

•  ADF Logging Options & Storage Account.

•  Compression Option, DOP and DOCP.

•  ETL Staging & Performance.

•  Staging with Storage Account, Container.

•  ADF Pipeline Monitoring; Polybase.

Chapter 5: On Premise Data Loads with ADF

•  On-Premise Data Sources with Azure.

•  Self Hosted Integration Runtime (IR).

•  Access Keys, Remote Linked Services.

•  Synapse SQL Pool (DW) with OnPremise.

•  Staged Data Copy and Performance.

•  Pipeline Executions and Monitoring.

•  Pipeline RunIDs and Audits / Tracing.

•  Incompatible Rows Skips, Fault Tolerance.

•  Incremental Loads with Files (BLOB).

•  Pipeline Executions and Schedules.

•  Regular Schedules and Tumbling Window.

•  Execution Retry and Delay Options.

•  Binary Copy, Last Modified Date in Blob.

•  Automated Loops and Trigger Schedules.

•  Incremental Loads Verification Tests.

Chapter 6: ADF Data Flow - 1

•  Limitations with Copy Data Tool.

•  Data Flow Task, Data Flow Activity;

•  Transformations with Data Flow.

•  Spark Cluster for Debugging.

•  Cluster Node Configurations.

•  Data Preview Options with DFT;

•  SELECT Transformation & Options.

•  JOIN Transformation and Usage.

•  Conditional Split Transformation.

•  Aggregate & Group By Transformations.

•  Synapse Sink Options with DFT.

•  DFT Optimization Techniques.

•  Pipeline Debug Runs and ETL Testing.

•  Spark Cluster For Pipeline Executions.

•  Pipeline Monitoring & Run IDs.

Chapter 7: ADF Data Flow - 2

•  ADF Pipelines For ETL Operations.

•  Data Flow Tasks and Activities in Synapse.

•  Pivot Transformation For Normalization.

•  Generating Pivot Column, Aggregations.

•  Pivot Transformation and Pivot Settings.

•  Pivot Key Selection, Value and Nulls.

•  Pivoted Columns and Column Pattern.

•  Column Prefix, Help Graphic & Metadata.

•  Window Functions & Usage in Data Flow.

•  Rank / DenseRank / Row Number.

•  Over Clause and Input Options.

•  Derived Column Transformations;

•  Exists & Lookup Transformations.

•  Reusing Data Flow Tasks in Synapse.

•  Pipeline Validations & Executions.

Chapter 8: Azure Synapse Analytics

•  Azure Synapse Analytics Resource.

•  Azure Synapse Analytics Workspace.

•  Managed Resource Group, SQL Account.

•  SQL Admin Account and its Purpose.

•  Operations with Synapse Workspace.

•  ADLS Gen 2 Storage Account, Container

•  Synapse Studio (Synapse Portal).

•  Dedicated SQL Pools & Spark Pools.

•  Creating Dedicated SQL Pools.

•  Synapse Tables, Data Loads with T-SQL.

•  COPY INTO Statements with T-SQL.

•  Clustered Column Store Indexes.

•  Compressions.

•  Aggregation with Data Loads in Azure Synapse.

Chapter 9: Synapse Analytics with Spark

•  Apache Spark Pool in Azure Synapse.

•  Spark Cluster Nodes: Vcores, Memory.

•  Creating Spark Clusters @ Synapse Studio.

•  Python Notebooks For Remote Access.

•  Creating Databases in Apache Spark Pool.

•  Data Loads from Dedicated SQL Pools.

•  Table Creations, Aggregation Operations.

•  PySpark Code for Data Operations, Writes.

•  Serverless Pool in Azure Synapse.

•  Connections, Usage with Serverless Pool.

•  Using Azure Open Datasets in Synapse.

•  OPENROWSET and BULK Data Loads.

•  Azure Storage Account : Data Analysis.

•  Working with Parquet Files in Synapse.

•  Python Notebooks (Pyspark) in Synapse.

Chapter 10: Incremental Loads @ Synapse

•  Incremental Loads with Synapse Studio.

•  Multi Table Merge Operations; On-Premise Data Sources & Timestamps.

•  Azure SQL DB Destinations, Watermarks; Watermark Table Usage & Audits.

•  Stored Procedures for Timestamp Updates.

•  Table Data Type and Dynamic MERGE.

•  SQL Queries for Datasets and Fetch.

•  Lookup Activity and its Usage un Synapse.

•  Expressions in ADF Portal for Lookup.

•  Expressions in ADF Portal for Source.

•  Output Pipeline Expression, Data Window.

•  Concat Function, Run IDs Expressions.

•  JSON Parameters, Pipeline Scheduling.

•  Pipeline Validation, Trigger and Monitoring.

Chapter 11: Optimizations, Power Query

•  ADF ETL with GUI : Power Query.

•  Power Query Resource Creation, Use.

•  Source Data Configurations & Settings.

•  Rename, Remove, Pivot, Group By, Order.

•  Index, Filter, Remove Error Rows.

•  Using Power Query Activity, ADF Pipelines.

•  Spark Cluster Configurations for Pipelines.

•  Concurrency, Big Data Recommendations.

•  Storage Optimization Techniques; ETL Optimization Techniques.

•  SQL Pool (Synapse) Optimizations.

•  Indexes, Partitions, Distributions, DOP.

•  Pipeline Optimization Techniques.

•  Partitions, DOCP, Compressions, DIU.

•  Staging, Polybase and Core Counts.

Chapter 12: Pipeline Monitoring, Security

•  Azure Monitor Resource and Usage.

•  Pipeline Monitoring Techniques.

•  ADF: Pipeline Monitoring and Alerts.

•  Synapse: Pipeline Monitoring and Alerts.

•  Synapse: Storage Monitoring and Alerts.

•  Conditions, Signal Rules and Metrics.

•  Email Notifications with Azure.

•  Concurrency, Big Data Recommendations.

•  Azure Active Directory (AAD) Users, Groups.

•  IAM: Identity & Access Management.

•  Synapse Workspace Security with RBAC.

•  ADF Security with RBAC: Owner, Contributor.

•  Azure Synapse SQL Pool Security: Logins.

•  Users, Roles and Resource Classes (RC).

•  Assigning RCs to Users. Object Level Security.

Azure Storage & Stream Analytics

Chapter 1: Azure Storage & Containers

•  Storage Components in Microsoft Azure.

•  Azure Storage Services and Types – Uses.

•  High Availability, Durability & Scalability.

•  Blob: Binary Large Object Storage.

•  Blobs, File Share, Queues and Tables.

•  Data Lake Gen 2 Operations with Azure.

•  Azure Storage Account Creation.

•  Azure Storage Container: Usage.

•  Azure Data Explorer: Operations

•  File Uploads, Edits and Access URLs.

•  Azure Storage Explorer Tool Usage.

•  Azure Account Options in Explorer.

•  Directory Creation, File Operations.

•  End User Access Options With Files.

•  Data Explorer Vs Storage Explorer Tool.

Chapter 2: Azure Migrations, BLOB Imports

•  SQL Server (On-Premise) to Azure Migration.

•  Source Database Scripts & Validations.

•  BACPAC File Generation From SSMS Tool.

•  Azure Data Lake Storage and SSMS Access.

•  Azure Storage Container, BACPAC Files.

•  Azure SQL Server Creation From Portal.

•  Azure SQL DB Imports, Storage SAS Keys.

•  Azure SQL Database Migrations, Verification.

•  BLOB Data Access from On-Premise.

•  Data Imports From Excel and CSV Files.

•  BLOB Data Imports using T-SQL Queries.

•  SAS - Shared Access Signature Generation.

•  CSV File -Uploads, Downloads, Edits, Keys.

•  Master Keys, Credentials, External Sources.

•  BULK INSERT Statement and Data Imports.

•  T-SQL Imports : Practical Limitations.

Chapter 3: Azure Tables, Shares

•  Azure Tables - Real-time Usage.

•  Schema-less Design and Access Options.

•  Structured and Relational Data Storage.

•  Tables, Entities and Properties Concepts.

•  Azure Tables: Creation and Data Inserts.

•  Azure Tables in Portal - GUI and Data Types.

•  Azure Tables: Data Imports in Explorer.

•  Data Edits, Queries & Delete Operations.

•  Azure Files - SMB Protocol, Creation, Usage.

•  Shared Access, Fully Managed & Resiliency.

•  Performance, Size Requirements for Shares.

•  Azure Storage Explorer Tool for File Shares.

•  Azure Queues: Message Queues, Limitations.

•  Adding Messages, Queuing and De-Queuing.

•  Data Access & Clear Queue from Explorer.

•  End Points for Azure Message Queues.

Chapter 4: Azure Storage Security, Admin

•  Azure Data Lake Storage Security Options.

•  Shared Access Keys - Primary, Secondary Keys.

•  SAS Key Generation: Container, Tables, Files.

•  SAS Key Permissions, Validation Options.

•  Access Keys: Account Level Permissions.

•  Azure Active Directory (AAD): Users, Groups.

•  Azure AD Security: RBAC with IAM, ACLs.

•  Owner Role, Contributor and Reader Role.

•  Azure Admin Operations: Replication Options.

•  Azure Regions and Azure Zones, Availability.

•  Locally & Geo Redundant Storage: LRS, GRS.

•  Read Access (RA-GRS) & Geo Zone (GZRS).

•  Classic Deployment Model, Encryption at REST.

•  CORS Support (Cross Origin Resource Sharing).

•  Auditing Access and Network Access Rules.

•  Firewalls and Advanced Threat Protection.

Chapter 5: Azure Monitoring, Power BI

•  Azure Monitor, Metrics & 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 Options.

•  Azure Alerts and Conditions, Notifications.

•  Signal Logic Conditions and Emails.

•  Power BI Desktop Tool Installation.

•  Binary Data and Record Data Access

•  Azure Data Lake Storage: Access Keys.

•  Azure Data Lake Storage with Power BI.

•  BLOB File Access with Power BI.

•  Azure Tables Creation and File Imports.

•  Azure Table Access with Power BI.

Chapter 6: Azure Stream Analytics, IoT

•  Azure Steam Analytics: Real-time Usage.

•  Real-time Data Processing, Event Tracking.

•  Ingest, Deliver and Analysis  Opration.

•  Azure Stream Analytics Jobs Concept.

•  Understanding Input & Output Options.

•  SAQL Qeries for Stream Analytics Jobs.

•  IoT: Internet Of Things For Real-time Data.

•  Need for IoT Hubs and Event Hubs.

•  Thing IoT Device for Data Inputs.

•  Creating Azure Stream Analytics Resource.

•  Stream Analytics Jobs for Historical Data.

•  Azure SQL Database Options for ASA Jobs.

•  SAQL: Query Formatting and Validation.

•  Historical Data Uploads, ASA Job Execution.

•  Stream Analytics Job Monitoring Options.

Chapter 7: IoT Hubs & Event Hubs

•  Azure Stream Analytics For API Data.

•  IoT Hubs & IoT Devices, Connection Strings

•  Raspberry APP Connections with IoT Hub.

•  Azure Storage Account and Container.

•  Creating Azure Stream Analytics Job.

•  Configuring Input Aliases with IoT Hub

•  Configuring Output Alias with ADLS Gen 2 .

•  SAQL Query and Job Executions.

•  Monitoring.

•  Azure Event Hubs and Event Instances.

•  Event Hub Namespaces, Partition Counts.

•  Access Policies, Permissions & Defaults.

•  Root Manage Shared Access Key & Options.

•  Connection Strings & Event Service Bus.

•  Telco App Installation, Executions. LIVE Data.

•  On-Premise Integration with ASA Jobs.

Chapter 8: Azure Stream Analytics Security

•  Securing Azure Stream Analytics Resource

•  RBAC: Role Based Access Control & IAM.

•  Owner, Contributor, Reader, Resource Owner.

•  Network Level and IP Address Security.

•  Virtual Machine and Virtual Network Options.

•  Port Level Configuration and Stream Apps.

•  Data Feed Security for Event Hub, IoT Hub.

•  Azure Virtual Machine (VM) Security Options.

•  Azure Key Vaults & ADLS [Data Lake] Security.

•  Azure Passwords, Keys and Certificates.

•  Azure Key Vaults - Name and Vault URI.

•  Inbuilt Managed Key and Azure Key Vault.

•  Standard Type, Premium Type Azure Key Vaults.

•  Secret Page, Key Backups and Key Restores.

•  Adding Keys to Azure Vaults. Key Type, Size.

   Azure Databricks & Spark SQL

Chapter 1: Azure Intro, Azure Databricks

•  Cloud Introduction and Azure Basics For Databricks.

•  IaaS, PaaS, SaaS; Benefits of Azure Cloud Environment.

•  Storage, ETL and Streaming Components.

•  Need for Azure Databricks (ADB).

•  ETL and Data Storage Components.

•  Databricks File System (DBFS) and Usage.

•  Azure Databricks Service Creation.

•  Azure Databricks Workspace Operations.

•  Spark Cluster Configurations & Capacity.

•  Driver Nodes and Worker Nodes in Spark.

•  Master Node & Cluster Creation Process.

•  Databricks Runtime Service & DBUs.

•  File Uploads into DBFS.

•  Table Creations.

•  Promoting Headers, Data Type Changes.

•  DBFS Storage Navigations.

Chapter 2: SQL Notebooks & Python

•  Notebooks: Concept, Usage Options.

•  Creating SQL Notebooks in Databricks.

•  Using DBFS Tables in SQL Notebooks.

•  Data Access and Analytics Options.

•  SparkSQL Queries: SELECT, GROUP BY.

•  SparkSQL Queries: Aggregates, Conditions.

• Notebook Operations: Download, Clone.

• Notebook Operations: Upload, Reuse.

• SQL Notebooks with Python Code.

• Using DBFS Sample Data Sources (CSV).

• Data frames: Creation and Real-time Use.

• Pandas Dataframe, Virtual Table Creation.

• Dataframe Data Access, Caching Options.

• Take () and Display () Functions in PySpark.

• Temporary View Creation and Access.

• SparkSQL Queries, Analytics, Chart Reports.

Chapter 3: Python Notebooks

• Azure SQL Server Configurations.

• Azure SQL Database Creation.

• Azure Firewall Rules and IP Address.

• Allow Azure Services, Remote Access.

• Connection Tests with SSMS Tool.

• Python Notebooks with Azure Databricks.

• Data Imports and Table Creations (Code).

• Parquet Files and Usage in Databricks.

• Using Dataframes for Data Operations.

• SparkSQL Queries with SELECT, TOP; Establishing Connections to Azure

• SQL DB; JDBC Connection Strings, Dataframe Writer.

• JDBC Properties, Port Settings & Options; Data.

• Extraction, SQLContext & Dataframes.

• Pandas Data Frame for Big Data Analytics.

• JDBC URL Options & PySparkSQL Modules.

Chapter 4: Scala Notebooks, Transformations

• Scala Notebooks with Azure Databricks

• Azure SQL Database Connections with Scala.

• Util Properties , Connection Settings For Driver.

• Connection Settings for Worker Node @ Spark.

• Dataframe Creation and Data Access in Scala.

• Display Options and Data Transformations.

• Data Loads to Azure SQL Database Tables.

• Data Reads from Azure SQL DB & Analytics.

• Databricks Jobs : Creation Options, Usage.

• Job Limits, Workspace & Concurrency Limits.

• Notebooks with and without Parameters.

• Jobs with Default Parameters, Executions.

• Interactive, Automated Clusters for ADB Jobs.

• Job Schedules and Manual Executions.

• Active Jobs, Recently Run Jobs & Monitoring.

• Job Execution Reports & Workspace Options.

Chapter 5: ADB with Virtual Network

• Azure Virtual Network Configuration.

• Azure VNets, Address Spaces & Range.

• Subnet and Subnet Address Range.

• Azure Databricks Deployments with VN

• CIDR Range and IP Addresses.

• Public Subnet and Private Subnet.

• Spark Cluster Creation with ADB.

• End User Access using Virtual Network

• Spark Cluster Notebook Operations.

• Notebook Exports, Imports, ReUse.

• SparK Cluster Configuration Changes.

• Power BI Connections, JDBC, User Token.

• Notebook Scheduling and Jobs.

• Interactive Clusters and Real-time Use.

• All Purpose Clusters and Advantages.

Chapter 6: DeltaLakes & ADF Integration

• Azure Delta Lake Implementation.

• ACID Properties and Upsert Advantages.

• Delta Engine Optimizations & Uses.

• Pipeline Creation with JSON Files in DBFS.

• Delta Tables Creation, Data Loads.

• Spark Cluster Settings: Auto Optimize.

• Auto Compact and Delta Table Optimize.

• Delta Locations.

• Data Retrieval, Versions.

• Azure Databricks Integrations with ADF.

• Defining Scala Notebooks in ADB.

• Using Notebooks in Azure Data Factory.

• spark.conf.set & fs.azure.account.key.

• spark.read.format, Option() and Head().

• dbutils() with wasbs & BLOB Data Sources.

• ADF Pipelines and Trigger Schedules.

Chapter 7: Databricks Integrations

• Azure Databricks with Data Lake Storage.

• Handling Unstructured Data in Azure.

• Data Preparation and Staging Operations.

• Azure App (Service Principal) Registration.

• Azure Key Vault Creation & Key Usage.

• Service Principal Permissions @ Data Loads.

• Tenants and Authorization Settings.

• Client Credentials, Token Provider Options.

• Azure Databricks Clusters, Spark Notebooks.

• Dynamic Connections and Blob Access.

• Data Preparation & Big Data Ingestion.

• Data Extraction and ADLS Storage.

• Show (), transformations, wasbs; Azure SQL Server & Synapse.

• Incremental Changes & Data Loads.

• 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).

• Azure Stream Analytics Jobs with IoT.

• Azure Data Bricks and DBFS, Notebooks.

• Big Data Analytics & Power BI Reports.

• Project Requirement.

• Project Solution.

• Project FAQs.

• Concept wise FAQs.

• Resume Guidance.

• Mock Interviews.

• DP 203 Certification Guidance.

Module 2: Database Basics, SQL, T-SQL Queries

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

• Database Engine Component and OLTP.

• BI Components, Data Science Components.

• ETL, MSBI and Power BI Components.

• Course Plan, Resume, Project.

• 24 x 7 Lab.

• Software Installation Pre-Requisites.

Chapter 2: SQL SERVER INSTALLATIONS

• System Configuration Checker Tool.

• Versions and Editions of SQL Server.

• SQL Server Prerequisites : S/W, H/W.

• SQL Server 2016 / 2017 Installation;

• SQL Server 2019 Installation.

• Instance Name; Instances : Types.

• Default Instance, Named Instances.

• Port Numbers; Service and Service Account.

• Authentication Modes and Logins.

• File Stream, Collation Properties.

Chapter 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 GUI.

• Data Insertion & Storage.

• SQL : Real-time Usage.

• DDL, DML, SELECT, DCL and TCL Statements.

• Data Storage, Inserts - Basic Level; SELECT.

• Table Data Retrieval.

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

• AND and OR; IN and NOT IN.

• Between, Not Between.

• LIKE and NOT LIKE.

• UPDATE Statement.

• DELETE & TRUNCATE.

• Logged and Non-Logged Operations.

• ADD, ALTER and DROP Statements.

Chapter 5: SQL BASICS - 3, T-SQL Introduction

• Schemas : Group Tables in Database.

• Using Schemas for Table Creation.

• Using Schemas in Table Relations.

• Table Migrations across Schemas.

• Default Schema : "dbo".

• Import and Export Wizard.

• Bulk Operations; Excel File Imports / Exports.

• SQL Server Native Client.

• Executing SSIS Packages, Data Loads.

• Local and Global Temporary Tables.

• # & ## Prefix.

• Temporary Vs Permanent Tables.

Chapter 6: CONSTRAINTS & INDEXES BASICS

• Constraints and Keys - Data Integrity.

• NULL, NOT NULL Property on Tables.

• UNIQUE KEY Constraint.

• PRIMARY KEY Constraint.

• FOREIGN KEY Constraint, References.

• CHECK Constraint.

• DEFAULT Constraint.

• Identity Property : Seed & Increment.

• Database Diagrams and ER Models.

• Relationships Verification and Links.

• Indexes : Basic Types and Creation.

• Index Sort Options, Search Advantages

• Clustered and Non Clustered Indexes.

• Primary Key and Unique Key Indexes.

Chapter 7: JOINS and TSQL Queries: Level 1

• JOINS - Table Comparisons.

• INNER JOINS For Matching Data.

• OUTER JOINS For (non) Match Data.

• Left Outer Joins.

• Right Outer Joins - Example Queries.

• FULL Outer Joins.

• One-way and Two Way Comparisons

• "ON" Conditions

• Join Unrelated Tables

• NULL, IS NULL in Joins

• CROSS JOIN and CROSS APPLY.

• Join Options: Merge, Loop and Hash Joins.

• Performance Advantages.

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

• Joins with Group By Queries.

• Nested Sub Queries.

• UNION and UNION ALL.

• Nested Sub Queries with Group By, Joins.

• Comparing WHERE, HAVING Conditions.

Chapter 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

Chapter 10: View, Procedure, Function Basics

• Views : Types, Usage in Real-time.

• System Predefined Views and Audits.

• Listing Databases, MERGE Statement.

• Tables, Schemas.

• Functions : Types, Usage in Real-time.

• Scalar, Inline and Multi-Line Functions.

• System Predefined Functions, Audits.

• DBId, DBName, ObjectID, Object Name.

• Variables & Parameters.

• User & System Predefined Procedures.

• Parameters.

• Sp_help, Sp_helpdb and sp_helptext.

• sp_pkeys, sp_rename and sp_help.

• When to use Which Database Objects

Chapter 11: Triggers & Transactions

• Triggers - Purpose, Real-world Usage .

• FOR/AFTER Triggers; INSTEAD OF Triggers.

• INSERTED, DELETED Memory Tables.

• DML Automations using Memory Tables.

• Read Only Tables using DML Triggers.

• Enable Triggers and Disable Triggers.

• Database Level, Server Level Triggers.

• Transactions & ACID Properties.

• Auto Commit; EXPLICIT & IMPLICIT.

• COMMIT and ROLLBACK.

• Open Transaction.

• Query Blocking Scenarios @ Real-time.

• NOLOCK and READPAST Lock Hints.

Chapter 12: ER MODELS, NORMAL FORMS

• Normal Forms for Entity Relationships.

• First, Second, Third Normal Forms Usage.

• Boycee-Codd Normal Form: BNCF : Usage.

• 4 NF, EKNF, ETNF. Functional Dependency.

• Multi-Valued, Transitive Dependencies.

• Composite Keys and Composite Indexes.

• 1:1, 1:M, M:1, M:M Relationship Types.

• SQL Queries Access in Reporting Tools.

• Storing SQL Queries into Views.

• Creating Office Data Connection Files.

• Excel Pivot Reports and Reports.

• SQL Queries (Auto Generated) in BI Tools.

• FETCH OFFSET, NEXT ROWS.

Data Refresh (Manual, Automated).

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

• DA 100 Exam Guidance.

• Types of Reports in Real-World.

• Interactive & Paginated Reports.

• Analytical & Mobile Reports.

• Data Sources Types in Power BI.

• Licensing Plans.

• Power BI Training : Lab Plan.

• Power BI Dev, Prod Environments.

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

Chapter 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; Disabling Slicers.

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

Chapter 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 Realtime.

• Visual Filter, Page Filter, Report Filter.

• Basic, Advanced and TOP N Filters.

• Category and Summary Level Filters.

• Drill Thru Filters, Drill Thru Reports.

• Keep All Filters" Options in Drill Thru.

• CrossReport Filters, Include, Exclude.

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

• Enter Data.

• Data Modeling: Currency, Relations.

• Summary, Format, Synonyms.

• Web & Mobile View in PBI.

Chapter 6: VISUALIZATION PROPERTIES

• Stacked Charts and Clustered Charts.

• Line Charts, Area Charts, Bar Charts.

• 100% Stacked Bar and Column Charts.

• Map Visuals: Tree, Filled, Bubble.

• Cards, Funnel, Table, Matrix.

• Scatter Chart: Play Axis, Labels.

• Series Clusters.

• Waterfall Chart.

• ArcGIS Maps.

• Infographics.

• Color Saturation, Sentiment Colors.

• Column Series, Column Axis in Lines.

• Join Types: Round, Bevel, Miter.

• Shapes, Markers, Axis, Plot Area.

• Data Colors .

• Series, Custom Series and Legends.

Chapter 7: POWER QUERY LEVEL 1

• Power Query Architecture and M Language.

• Data Types, Literals and Values.

• Power Query Transformation Types.

• Table & Column.

• Text & Number Transformations.

• Date, Time and Structured data.

• List, Record & Table.

• let, source, in statements @ M Lang.

• Power Query Functions, Parameters.

• Invoke Functions.

• Get Data, Table Creations, Edit.

• Merge and Append Transformations.

• Join Kinds, Advanced Editor, Apply.

• ETL Operations with Power Query.

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

• Move Column and Split Column.

• Extract, Format.

• Date & Time Transformations.

• Deriving Year, Quarter, Month, Chapter.

• Add Column: Query Expressions.

• Query Step Inserts and Step Edits.

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

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

Chapter 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, Columns with DAX.

• Quick Measures.

• SUM, AVERAGEX, KEEPFILTERS

• Dynamic Expressions, IF in DAX.

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

• Slicing.

• Dynamic Expressions, RETURN.

• Date, Time, Text Functions.

• Logical, Mathematical Functions.

• Running Total, EARLIER Function.

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

• TOCHAPTER, DATE, CHAPTER with DAX.

• Time Intelligence Functions.

Chapter 13: POWER BI CLOUD - 1

• Power BI Service Architecture.

• Power BI Cloud Components, Use.

• App Workspaces, Report Publish 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 Interaction in Dashboard.

• Adding Images, Custom Links.

• Videos & Embed Links.

• API Data Sources.

• Streaming Dataset Tiles (REST API).

 

Chapter 14: POWER BI CLOUD - 2

Dashboards Actions, Report Actions.

• DataSet Actions: Create Report.

• Share, Metrics and Exports.

• Mobile View & Dashboard Themes.

• Q & A [Cortana], Pin Visuals.

• Export, Subscribe, Subscribe.

• Favourite, Insights, Embed Code.

• Featured Dashboards and Refresh.

• Gateways Configuration, PBI Service.

• Gateway Types.

• Gateway Clusters, Data Refresh : Manual, Automatic.

• PBIEngw Service; DataFlows, Power Query.

• Expressions; Adding Entities, JSON Files.

Chapter 15: EXCEL, ROW LEVEL SECURITY

• 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 Modelling.

• DAX Roles Creation and Testing.

• Power BI Users to Roles.

• Custom Visualizations.

• Histogram, Gantt Chart, Info graphics.

Chapter 16: REPORT SERVER, REPORT BUILDER

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

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

• Mobile Report Publisher.

Chapter 17: AZURE BI INTEGRATIONS WITH POWER BI

• Power BI with SQL Server Source.

• Power BI with Azure SQL Database.

• Power BI with Azure Data Warehouse (Synapse).

• Power BI with Azure Data Lake.

• Power BI with Azure Databricks.

• Power BI with Azure Cosmos DB.

• Power BI with Azure BLOB Storage.

• Azure AD Authentication.

Chapter 18: Real-time Project [Sales & Customers]

• Resume, Project Oriented FAQs and Solutions