Power BI Online Training

Official Learning Partner

Course Highlights

What is Power BI?

Power BI is a cloud-based, elegant end-to-end business analytics tool that enables anyone to visualize, analyze, forecast any type of data with greater speed, efficiency, and understanding.

What this Power BI course includes?

This Power BI Training includes EVERY detail. From very basics – Installation, details of each Power BI Visual, On-premise and Cloud Data Access, Azure Integration, Data Modelling and ETL Techniques, Power Query (M Language), DAX Functions, Variables, Parameters, Power BI Dashboards, App Workspace, Data Gateways, Alerts, Power BI Report Server Components, Power BI Mobile Reports, Excel Integration, Excel Analysis, KPIs, Microsoft PL 300 Certification guidance, Resume Guidance, Concept wise Interview FAQs and ONE Real-time Project.

Custom Visuals
Big Data Sources
Excel Analysis
Google Big Query

Gateways, Rest API
Tabular Mode
Tabular Mode OLAP
Cube Design

Power BI Mobile
Big Data, JSON
Excel Online
Report Server

Power BI Admin
Data Models
Tabular Mode DAX
Excel Analytics

Course Content

Power BI Training
Course Contents:

Brochure Download Register Now

Module 1 : SQL Server & T-SQL Queries [Applicable for Power BI Plans B, C]

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, Views & Excel

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
Computed Columns, Variant Type
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 Merge, Cursors

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

Case Study 1: Database Design with Tables,
Constraints, Keys & Relations

Case Study 2: Joins with Group By,
Sub Queries, Views, Excel Analytics

Module 2 : Power BI [Applicable for Power BI Plans A, B, C]

Part 1: Power BI Report Design

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

Part 2: Power Query, Cloud (Service)

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

Part 3: DAX & Report Server

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

Power BI : Realtime Project (Sales – Retail)

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

Module 3 : Azure Data Engineer [Applicable for Power BI Plan C]

Part 1: Azure Data Factory, Synapse Analytics

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

Part 2: Data Lake Storage, Stream Analytics

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)

Part 3: Databricks, Spark, Python

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

Power BI : Realtime Project (Sales – Retail)

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

Power BI Training (with DAX, Power Query, Cloud, Report Server Admin)

	Plan A 1. Power BI	Plan B 1. SQL Server TSQL 2. Power BI	Plan C 1. SQL Server TSQL 2. Power BI 3. Azure Data Engineer
Total Duration	4 Weeks	7 Weeks	14 Weeks
Power BI Report Design	✔	✔	✔
Power BI Desktop, Custom Visuals	✔	✔	✔
Data Modelling with Power Query	✔	✔	✔
Data Modelling with DAX	✔	✔	✔
Power BI Cloud, Excel Analysis	✔	✔	✔
Power BI Mobile, R, REST API	✔	✔	✔
PL 300 Exam Guidance	✔	✔	✔
Real-Time Project in Power BI	✔	✔	✔
SQL Database, T-SQL Queries	✖	✔	✔
Tuning Tools, Execution Plans	✖	✔	✔
Normal Forms, Joins, Query Tuning	✖	✔	✔
Sub Queries, Stored Procedures	✖	✔	✔
Excel Pivot Tables, Pivot Charts	✖	✔	✔
ADF : Azure Data Factory	✖	✖	✔
ADF : Data Imports, ETL	✖	✖	✔
ADF : Data Flows, Wrangling	✖	✖	✔
ADF : Transformations, ETL	✖	✖	✔
Synapse: Configuration, Loads	✖	✖	✔
Synapse: ETL with ADF, DWH	✖	✖	✔
Synapse: Performance Tuning	✖	✖	✔
Synapse: MPP, cDWH, DIUs	✖	✖	✔
ADB : Azure Data Bricks	✖	✖	✔
ADB : Architecture, Data Loads	✖	✖	✔
ADB : Workspace, Delta Tables	✖	✖	✔
DP 203 Certification Guidance	✖	✖	✔
Total Course Fee ( Payable in Installments)	INR 13000USD 150	INR 18000USD 200	INR 42000USD 500

Power BI Training Schedules

S No	Time (IST, Mon - Fri)	Start Date	Register
1	8 AM - 9 AM	May 28th	Register
2	6 PM - 7 PM	Apr 17th	Register
3	8 PM - 9 PM	Apr 29th	Register

Azure Data Engineer Training Schedules

S No	Time (IST, Mon - Fri)	Start Date	Register
1	7 AM - 8 AM	Apr 30th	Register
2	11:30 AM - 12:30 PM	May 7th	Register
3	7 PM - 8 PM	Apr 23rd	Register

SQL Server & T-SQL Schedules

S No	Time (IST, Mon - Fri)	Start Date	Register
1	6 AM - 7 AM	May 15th	Register
2	8 AM - 9 AM	Apr 30th	Register
3	10:30 AM - 11:30 AM	Apr 18th	Register
4	6 PM - 7 PM	May 7th	Register
5	8 PM - 9 PM	May 21st	Register
6	9 PM - 10 PM	Apr 23rd	Register

CURRICULUM

"*" indicates required fields

SQL SCHOOL

24x7 LIVE Online Server (Lab) with Real-time Databases.
Course includes ONE Real-time Project.

Technical FAQs

Who is SQL School? How far you have been in the training services ?

SQL School is a registered training institute, established in February 2008 at Hyderabad, India. We offer Real-time trainings and projects including Job Support exclusively on Microsoft SQL Server, T-SQL, SQL Server DBA and MSBI (SSIS, SSAS, SSRS) Courses. All our training services are completely practical and real-time.CREDITS of SQL School Training Center

We are Microsoft Partner. ID# 4338151
ISO Certified Training Center
Completely dedicated to Microsoft SQL Server
All trainings delivered by our Certified Trainers only
One of the few institutes consistently delivering the trainings for more than 8+ Years online as inhouse
Real-time projects in
- Healthcare
- Banking
- Insurance
- Retail Sales
- Telecom
- ECommerce

I registered for the Demo but did not get any response?

Make sure you provide all the required information. Upon Approval, you should be receiving an email containing the information on how to join for the demo session. Approval process usually takes minutes to few hours. Please do monitor your spam emails also.

Why you need our Contact Number and Full Name for Demo/Training Registration?

This is to make sure we are connected to the authenticated / trusted attendees as we need to share our Bank Details / Other Payment Information once you are happy with our Training Procedure and demo session. Your contact information is maintained completely confidential as per our Privacy Policy. Payment Receipt(s) and Course Completion Certificate(s) would be furnished with the same details.

What is the Training Registration & Confirmation Process?

Upon submitting demo registration form and attending LIVE demo session, we need to receive your email confirmation on joining for the training. Only then, payment details would be sent and slot would be allocated subject to availability of seats. We have the required tools for ensuring interactivity and quality of our services.

Please Note: Slot Confirmation Subject to Availability Of Seats.

Will you provide the Software required for the Training and Practice?

Yes, during the free demo session itself.

How am I assured quality of the services?

We have been providing the Trainings – Online, Video and Classroom for the last EIGHT years – effectively and efficiently for more than 100000 (1 lakh) students and professionals across USA, India, UK, Australia and other countries. We are dedicated to offer realtime and practical project oriented trainings exclusively on SQL Server and related technologies. We do provide 24×7 Lab and Assistance with Job Support – even aftrer the course! To make sure you are gaining confidence on our trainings, participans are requested to attend for a free LIVE demo based on the schedules posted @ Register.html. Alternatively, participants may request for video demo by mailing us to contact@sqlschool.com Registration process to take place once you are happy with the demo session. Further, payments accepted in installments (via Paypal / Online Banking) to ensure trusted services from SQL School™

YES, We use Enterprise Edition Evaluation Editions (Full Version with complete feature support valid for SIX months) for our trainings. Software and Installation Guidance would be provided for T-SQL, SQL DBA and MSBI / DW courses.

Why Choose SQL School

100% Real-Time and Practical
ISO 9001:2008 Certified
Concept wise FAQs
TWO Real-time Case Studies, One Project
Weekly Mock Interviews
24/7 LIVE Server Access
Realtime Project FAQs
Course Completion Certificate
Placement Assistance
Job Support
Realtime Project Solution
MS Certification Guidance