Azure BI LIVE Online Training

This impeccable Azure BI Training course is carefully designed for aspiring BI Developers, Consultants and Azure Professionals. This Azure BI Online Training includes basic to advanced Azure Data Factory (ADF), Azure Storage, Azure Data Lake (ADL) and Azure Analysis Services (AAS) concepts with Real-time Project on End to End Implementation. This Azure BI Online Training course also includes Azure Migrations, Azure DataWarehouse (ADW) [Azure Synapse], Azure Data Bricks for Big Data Analytics, helpful for your next Job as well as to reshape your resume.

Complete practical and realtime Azure BI Training course with 24x7 LIVE server, Resume Guidance, ONE Real-time Project with Interview & Placement Assistance.

Azure BI Training Plans

  PLAN A PLAN B PLAN C
  Azure BI SQL Server
with
Azure BI
SQL Server
with
Azure BI,
SQL DBA
Total Duration 18 Weeks 20 Weeks 24 Weeks
MSBI - SSIS: ETL, Data Warehouse
MSBI - SSIS: Dimension, Fact Loads
MSBI - SSIS: Star & Snowflake Schemas
MSBI - SSAS: OLAP Cube Design
MSBI - SSAS: MDX, DAX, XMLA, DMX
MSBI - SSAS: Data Modeling with MDX
MSBI - SSAS: Data Modeling with DAX
MSBI - SSRS: Report Design, Hosting
MSBI - SSRS: Azure Cloud Data Source
MSBI: MCSA Certification Guidance
MSBI: Real-Time Project
Power BI: Report Design, Visuals
Power BI: M Lang, DX for ETL
Power BI: Report Server, Admin
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 : Run Spark Jobs, Pools
ADB : Workspace, Delta Tables
Storage : Storage & Containers
Storage: BLOB Imports
Storage: Security
Azure SSIS
Azure Analysis Services
SQL: Database Basics, T-SQL
SQL : Constraints, Joins, Queries
T-SQL : Queries, SProcs, Lock Hints
SQL: Views, Group By, Self Joins
Routine SQL DBA Activities
Emergency SQL DBA Activities
SQL Server, DB Maintenance
DB Repairs, Security Management
Migrations, Patches, Upgrades
HA-DR : Replication, Log Shipping
Clustering, Always-On Availability
Total Course Fee *
*Course Payable in Installments
INR 63,000
USD 852
INR 67,000
USD 906
INR 84,000
USD 1136

Trainer: Mr. Sai Phanindra T

All Session Are Completely Practical & Real Time


Azure BI Training Highlights :

✔ Azure Fundamentals ✔ Azure AD
✔ Azure SQL Concepts ✔ Azure Migrations
✔ Azure AD ✔ Azure Key Vaults
✔ Azure Monitor ✔ Azure Functions
✔ Azure Data Factory ✔ Azure Synapse
✔ Azure Synapse ✔ Azure Strorage
✔ Data Lake Storage ✔ Data Lake Analytics
✔ Stream Analytics ✔ IoT, Event Hubs
✔ Azure Cosmos DB ✔ Azure Databricks
✔ Azure Notebooks ✔ U-SQL & NoSQL
✔ Python, Scala ✔ Spark Clusters
✔✔ End to End Real-time Project @ Resume

Azure BI Training Course Contents:

 

Ch 1: SQL SERVER INTRODUCTION

  • Data, Databases and RDBMS Software
  • Database Types : OLTP, DWH, OLAP
  • Microsoft SQL Server Advantages, Use
  • Versions and Editions of SQL Server
  • SQL : Purpose, Real-time Usage Options
  • SQL Versus Microsoft T-SQL [MSSQL]
  • Microsoft SQL Server - Career Options
  • SQL Server Components and Usage
  • Database Engine Component and OLTP
  • BI Components, Data Science Components
  • ETL, MSBI and Power BI Components
  • Course Plan, Concepts, Resume, Project
  • 24 x 7 Online Lab for Remote DB Access
  • Software Installation Pre-Requisites

Ch 5: SQL Basics - 3, T-SQL INTRO

  • Database Objects : Tables and Schemas
  • Schemas : Group Tables in Database
  • Schemas : Security Management Object
  • Creating Schemas & Batch Concept
  • Using Schemas for Table Creation
  • Data Storage in Tables with Schemas
  • Data Retreival and Usage with Schemas
  • Table Migrations across Schemas
  • Import and Export Wizard in SSMS
  • Data Imports with Excel File Data
  • Performing Bulk Operations in SSMS
  • Temporary Tables : Real-time Use
  • Local and Global Temporary Tables
  • # and ## Prefix, Scope of Usage

Ch 9: JOINS, T-SQL QUERIES Level 3

  • GetDate, Year, Month, Day Functions
  • Date & Time Styles, Data Formatting
  • DateAdd and DateDiff Functions
  • Cast and, Convert Functions in Queries
  • String Functions: SubString, Relicate
  • Len, Upper, Lower, Left and Right
  • LTrim, RTrim, CharIndex Functions
  • MERGE Statement - Comparing Tables
  • WHEN MATCHED and NOT MATCHED
  • Incremental Load with MERGE Statement
  • IIF() Function for Value Compares
  • CASE Statement : WHEN, ELSE, END
  • ROW_NUMBER() and RANK() Queries
  • Dense Rank and Partition By Queries

Ch 2: SQL SERVER INSTALLATIONS

  • System Configuration Checker Tool
  • Versions and Editions of SQL Server
  • SQL Server and SSMS Installation Plan
  • SQL Server Pre-requisites : S/W, H/W
  • SQL Server 2016 / 2017 Installation
  • SQL Server 2019 Installation
  • Instance Name and Server Features
  • Instances : Types and Properties
  • Default Instance, Named Instances
  • Port Numbers, Instance Differences
  • Service and Service Account Use
  • Authentication Modes and Logins
  • Windows Logins and SQL Logins
  • FileStream and Collation Properties

Ch 6 : CONSTRAINTS,INDEXES Basics

  • Constraints and Keys - Data Integrity
  • NULL, NOT NULL Property on Tables
  • UNIQUE KEY Constraints: Importance
  • PRIMARY KEY Constraint: Importance
  • FOREIGN KEY Constraint: Importance
  • REFERENCES, CHECK and DEFAULT
  • Candidate Keys and Identity Property
  • Database Diagrams and ER Models
  • Relationships Verification and Links
  • Indexes : Basic Types and Creation
  • Index Sorting and Search Advantages
  • Clustered and NonClustered Indexes
  • Primary Key and Unique Key Indexes
  • Need for Indexes - working with Keys

Ch 10: VIEW, SPs, Function Basics

  • Views : Types, Usage in Real-time
  • System Predefined Views and Audits
  • Listing Databases, Tables, Schemas
  • Functions : Types, Usage in Real-time
  • Scalar, Inline and Multi-Line Functions
  • System Predefined Functions, Audits
  • DBId, DBName, ObjectID, ObjectName
  • Variables & Parameters in SQL Server
  • Procedures : Types, Usage in Real-time
  • User & System Predefined Procedures
  • Parameters and Dynamic SQL Queries
  • Sp_help, Sp_helpdb and sp_helptext
  • sp_pkeys, sp_rename and sp_help
  • Important System Objects and Metadata

Ch 3: SSMS Tool, SQL BASICS - 1

  • SQL Server Management Studio
  • Local and Remote Connections
  • System Databases: Master and Model
  • MSDB, TempDB, Resource Databases
  • Creating Databases : Files [MDF, LDF]
  • Creating Tables in User Interface
  • Data Insertion & Storage. Limitations
  • SQL : Purpose and Real-time Usage
  • SQL Versus T-SQL : Basic Differences
  • DDL, DML, SELECT, DCL and TCL
  • Creating Tables using SQL Scripts
  • Data Storage, Inserts - Basic Level
  • Table Data Verifications with Select
  • SELECT Statement for Table Retrieval

Ch 7: JOINS, T-SQL Queries : Level 1

  • JOINS - Table Comparisons Queries
  • INNER JOINS For Matching Data
  • OUTER JOINS For (non) Match Data
  • Left Outer Joins with Example Queries
  • Right Outer Joins with Example Queries
  • FULL Outer Joins - Realtime Scenarios
  • Join Queries with "ON" Conditions
  • Join Unrelated Tables in SQL Server
  • NULL, IS NULL Operators in Joins
  • CROSS JOIN and CROSS APPLY
  • CROSS JOIN Versus CROSS APPLY
  • One-way & Two Way Data Comparisons
  • Important Join Queries in T-SQL
  • Join Options: Merge, Loop, Hash

Ch 11: Triggers & Transactions

  • Triggers - Purpose, Real-world Usage
  • FOR/AFTER Triggers - Real time Use
  • INSTEAD OF Triggers - Real time Use
  • INSERTED, DELETED Memory Tables
  • Using Triggers for Data Replication
  • Enable Triggers and Disable Triggers
  • Database Level, Server Level Triggers
  • Transactions : Types, ACID Properties
  • Transaction Types and AutoCommit
  • EXPLICIT & IMPLICIT Transactions
  • COMMIT and ROLLBACK Statements
  • Open Transaction Scenarios & Cause
  • Query Blocking Scenarios @ Real-time
  • NOLOCK and READPAST Lock Hints

Ch 4: SQL BASICS - 2

  • Creating Databases & Tables in SSMS
  • Single Row Inserts, Multi Row Inserts
  • Rules for Data Insertion Statements
  • SELECT Statement @ Data Retrieval
  • SELECT with WHERE Conditions
  • Batch Concept and Go Statement
  • AND and OR Operators Usage
  • IN Operator and NOT IN Operator
  • Between, Not Between Operators
  • LIKE and NOT LIKE Operators
  • UPDATE Statement & Conditions
  • DELETE & TRUNCATE Statements
  • Logged and Non-Logged Operations
  • ADD, ALTER and DROP Columns
  • ALTER & DROP Table Statements

Ch 8: Group By, T-SQL Queries Level 2

  • GROUP BY Queries and Aggregations
  • Group By Queries with Having Clause
  • Group By Queries with Where Clause
  • Using WHERE and HAVING in T-SQL
  • Rollup : Usage and T-SQL Queries
  • Cube : Usage and T-SQL Queries
  • UNION and UNION ALL Operator
  • EXISTS Operator, Query Conditions
  • Sub Queries and Alternatives to Joins
  • Using Joins with Group By Queries
  • Using Joins with Nested Sub Queries
  • Sub Queries with Joins and Group By
  • Using UNION and UNION ALL in Queries
  • Nested Sub Queries with Group By, Joins
  • Comparing WHERE, HAVING Conditions

Ch 12 : ER MODELS, NORMAL FORMS

  • Normal Forms for Entity Relationships
  • First Normal Form and Atomocity
  • Second Normal Form, Candidate Keys
  • 3rd Normal Form Multi Value Dependancy
  • Boycee-Codd Normal Form : BNCF
  • Fourth Normal Form Realtime Advantages
  • Self Joins & Self Reference Keys
  • 1:1, 1:M, M:1, M:M Relationship Types
  • Joins with Group By Queries
  • Joins with Sub Queries, Formating
  • Office Data Connections, Excel Reports
  • Excel Pivot Reports and Reports
  • SQL Queries (Auto Generated) in BI Tools
  • FETCH OFFSET, NEXT ROWS, Order By
  • Data Refresh (Manual and Automated)
Real-time Case Study - 1 (Sales & Retail)
Objective : Database Design, Table Design and Relations.
Involves Purchases, Products, Customers and Time Data with Various Data Types.
Real-time Case Study - 2 (Sales & Retail)
Objective : Queries, Excel Integration
Pivot Tables, Pivot Charts, ODC Connections

SQL Server Integration Services (SSIS)

SQL Server Analysis Services (SSAS)

SQL Server Reporting Services (SSRS)

Ch 1: SSIS INTRO, INSTALLATION

  • Integration Services (SSIS) & ETL / DWH
  • SSIS for Data Loads, ETL, Warehouse
  • SSDT : SQL Server Data Tools
  • SSIS Development, LIVE (Deployment)
  • Data Warehouse Design & ETL Process
  • DWH and ETL Structures Implementation
  • SSIS ETL for Data Reads, Data Cleansing
  • Data Warehouse (DWH) Design Principles
  • SSIS 2019, 2017 : SSIS DB Installations
  • SSIS Database, Catalog Folders, Storage
  • SSIS Catalog Database (SSIS DB) Creation
  • SQL Server Data Tools - SSDT / Visual Studio
  • SSDT Installation and Catalog Verification
  • SSIS, ETL, DWH, Data Flow, Data Buffer
  • SSIS Package Environment, SSDT Projects
  • SSIS & ETL Training - Lab Plan, Resources

Ch 1: SSAS INTRO, CONFIGURATION

  • Installation, Configuration of SSAS
  • SSAS Component & - Operational Modes
  • Multidimensional Mode : Properties, Usage
  • Tabular Mode Purpose : Properties (ROLAP)
  • PowerPivot Mode & Usage (Overview)
  • Multidimensional Mode Instance Verification
  • SSAS and SQL Browser Service Accounts
  • SQL Server Data Tools / Visual Studio
  • Developer Environment (SSDT) Interface
  • SSAS Training Lab Plan, Resources
  • OLAP Databases, Cubes For Analysis
  • MDX: Multidimensional Expression Language
  • DAX: Data Analysis Expression Language
  • SSAS Architecture : XMLA and DMX
  • SSAS Workflow and Sources in Real-world
  • Data Source Configuration, DB Installations

Ch 1: SSRS INTRO, INSTALLATION

  • Reporting Operations and Report Types
  • Paginated Reports, Interactive Reports
  • Analytical Reports & Mobile Reports
  • Reporting Solutions (SSRS) and Tools
  • Report Engine Architecture, Databases
  • SSRS Report Server Installation
  • Report Databases in SSRS and Usage
  • Web Service URL : Connections, Usage
  • Web Portal URL : Connections, Usage
  • ReportServerDB, TempDB Configuration
  • SQL Server Data Tools (SSDT)
  • Report Builder, Mobile Report Publisher
  • Report Design : Lab Plan, Data Sources
  • 3-Phase Report Life Cycle (End-End)
  • Report Builder Versus Report Designer
  • Report Server, Web Service Integration

Ch 2: SSIS ETL PACKAGES: BASICS

  • Control Flow Tasks Architecture, Purpose
  • Data Flow Tasks Architecture, Purpose
  • SSIS Packages @ Basic Data Flow, ETL
  • SSIS Projects and Package Creation
  • Data Pipelines in Data Flow Tasks
  • SSIS Packages Execution Process
  • Data Flow Objects, OLE DB Connections
  • SSIS Package Creation - Control Flow
  • DTSX Files for Package Execution
  • SSIS Execution, Package Errors & Logs
  • SSIS Transformation: Conditional Split
  • Excel Connection and Memory Reference
  • Source and Destination Assistants
  • DAT File Imports and Annotations
  • SSIS Project Configurations, Debugging
  • SSIS 64 Bit, 32 Bit Configurations

Ch 2: CUBE DESIGN WITH SSAS, EXCEL

  • Cube Design with SQL Server Data Tools
  • OLAP Data Source, Data Source View
  • Measure Groups, Measures, Members
  • Identifying Dimensions and Attributes
  • Cube Design : Cube Wizard, Dimensions
  • Add Attributes. Deployment, Cube Access
  • OLAP Cube Process. Online Deployment
  • Cube Browsing using SSMS, SSDT Tools
  • Excel Connections for SSAS Cubes
  • OLAP Cube Access, Pivot Charts
  • Excel Pivot Tables, Chart Report Design
  • Piechart Reports & Attribute Filters
  • Common Deployment Errors : Solutions
  • OLAP Server Impersonation - Settings
  • OLAP Deployment Warnings, Solutions
  • End to End Implementation of SSAS

Ch 2: BASIC REPORT DESIGN

  • Working with SQL Server Data Tools
  • Report Templates and Project, Solution
  • Basic Reports - Understanding Entities
  • Report Project Wizard Usage, Reports
  • Data Source Connections and Databases
  • Query Designer, Query Builder, Imports
  • Table, Matrix Reports with Report Wizard
  • Layout, Format - Drilldown Reports, Blocks
  • Stepped Reports, Multi Field Drilldowns
  • Report Template - Datasets & Reports
  • Table Headers & Formatting Expressions
  • Alternate Row Colors, Global Expressions
  • Formatting Styles, Expressions, Reusability
  • Expressions: IIF,Format,Ceiling,Round
  • Textbox Properties: Date Format, Numbers
  • Report Sources, Static/Dynamic Properties

Ch 3: MERGE & FUZZY LOOKUP

  • MERGE and UNION ALL Transformations
  • SORT, NOSORT and Advanced Sort
  • Synchronous, Asynchronous Tfns
  • Row, Partial Blocking Transformations
  • Fully Blocking Transformations - Buffers
  • Avoiding Fully Blocking Transformation
  • Bulk Load Operations, SSIS Data Imports
  • IsSorted & SortKey Position Options
  • SSIS Package Performance & Resources
  • Data Conversion Expressions
  • Fuzzy Lookup Transformation, References
  • Nomatch Cleansing @ Conditional Split
  • Index Creations, Lookup Transformation
  • Data Conversion, Derived Columns
  • Varchar, Nvarchar, Error Redirections
  • Threshold Values, Search Delimiters
  • _Similarity, _Confidence Columns

Ch 3: HIERARCHIES, MDX - LEVEL 1

  • Data Source Views Named Calculations
  • Named Querie, Dimension Attributes
  • Explore Data with Data Source View
  • Dimension Types : Dimension, Entity Level
  • Cube Dimension on Entity Relations
  • Hierarchies in Multidimensional Cube
  • Grouping Attributes in Dimensions
  • Testing Hierarchies : SSMS Cube Browser
  • Testing Hierarchies : SSDT Cube Browser
  • Multidimensional Expression Language
  • MDX Queries Syntax, MDX Expressions
  • MDX Axis Models. Cube into Rows
  • Advantages of MDX: Reports
  • MDX Queries with Attributes, Members
  • MDX Queries on Hierarchies, Keys
  • Members, Children, All Members
  • SELECT in MDX with CROSSJOIN

Ch 3: GROUPING, REPORT PARAMETERS

  • Grouping : Row Groups, Column Groups
  • Row Groups, Parent - Child Groups
  • Adding Groups to Existing & New Rows
  • Group Headers & Footers, Sub Totals
  • Field Visibility, Toggle with Parent
  • Row Group, Header/Footer Properties
  • Column Groups for Table Report, Options
  • Drill-down Report, Row Groups, Visibility
  • Column Group Advance Mode. Fixed Values
  • Repeating Column Headers on Every Page
  • Creating Parameters, Dataset Conditions
  • Single Value and Multi Value Parameters
  • Dynamic Parameters, Dependency Queries
  • SSRS Parameters with Dynamic Conditions
  • Dataset Links to Parameters, List Values
  • SSRS Expressions, Global Fields, Values
  • Advanced Options : Auto / Manual Refresh

Ch 4: SSIS CHECKPOINT & PIVOT

  • Execute SQL Task and OLE DB Queries
  • Transaction Options For SSIS Executables
  • Precedence - Success/Failure/Completion
  • SSIS Package Rollbacks Execution Options
  • Checkpoints Purpose with Data Flow Tasks
  • Checkpoint Files and SSIS Logging Tasks
  • Transactions with Checkpoint File in SSIS
  • Checkpoint Option Advantages, Limitations
  • FailPackageOnFailure, Checkpoint Property
  • REQUIRED/SUPPORT/ NOTSUPPORTED
  • Transaction Property, CHECKPOINT Files
  • Legacy Data, Data Cleansing, Formatting
  • Denormalization, Keys. Need for OLTP
  • PIVOT Transformation, Connection Assistant
  • Pivot Usage - Implementation. Key Values
  • Lineage ID - Purpose. Data Mappings
  • Lineage IDs for Column Mapping, Pivot Keys
  • SSIS Input Columns and Mappings
  • Data Viewer : Data Transfer Verification
  • Data Type Conversions, Error Redirection

Ch 4: CALCULATIONS, MDX - LEVEL 2

  • MDX Queries with WHERE, Except, Range
  • NonEmpty Function, Multi-Member Values
  • Parent, Children with MDX Hierarchies
  • ORDER Function in MDX, Binary Sorts
  • TOPCOUNT / HEAD, BOTTOMCOUNT
  • CURRENT MEMBER, EMPTY MEMBER
  • Filter Expressions with AND / OR
  • Filter with and LEFT / RIGHT Range
  • MDX Query Batches - GO Statement
  • Limitations @ WHERE, Tuple Inverse
  • ADOMD Client : MDX Query Processing
  • MDX Calculations - Creation and Scope
  • Calculations with MDX : Measure Level
  • Calculations with MDX : Attribute Level
  • Time Calculations with MDX Scripts
  • TIME DIMENSIONS - Purpose, Advantages
  • Time Attributes - Calendar / Fiscal
  • BI Enhancements : Advantage, Usage Scope
  • Time Enhancement Attributes, Hierarchies
  • MDX Functions: YoY, YTD, QTD, MTD,

Ch 4: CHARTS, DASHBOARDS, FITLERS

  • Chart Reports - Design, Properties
  • Series Values and Category Groups
  • Report Categories with Series Groups
  • Report Category Types and Differences
  • Visualizations: Trend, Discrete Chart
  • Clustered, Non Clustered Attributes
  • Series Labels: Properties, Formatting
  • Series Actions: Multi - Valued Parameters
  • Report Actions: URL, Report Filters
  • Dashboards : Creation and Real-time Use
  • Multiple Chart Areas, Legends in Charts
  • Dashboard Exports and Report Filters
  • Static and Parameterized Report Filters
  • Series, Markers Chart Areas, Limitations
  • 3-Dimensional Report Properties, Visibility
  • Range Charts, Data Bars, Area Charts
  • Report Actions with Parameters, Joins
  • Dataset and Toolbox Filters, Bookmarks
  • Filters Vs Parameters - Difference
  • Filter Conditions in Dataset, Toolbox

Ch 5: EVENTS, LOOPS, EXPRESSIONS

  • SSIS Package Events, Validation, Execution
  • PreExecution, Progress, Cleanup Events
  • SSIS Events, Errors/Warnings/Information
  • Configuring sysssislog System Tables
  • Debugging : Data Viewers and Breakpoints
  • ForEach Loop Container. File Connections
  • Variables For Linking DFT, Control Flow
  • Dynamic Connections with Variables
  • Iterations, Fetch, Index Mapping
  • SSIS Expressions for ETL and DWH
  • FOR LOOP Expressions in SSIS
  • Init/EvalExpression, AssignExpression
  • SSIS Expressions, Functions, Values
  • Data Insertions, Data Serializations
  • Counter Values, Variables & Parameters
  • SSIS for OS Level Operations, Loops
  • Execute SQL Task : Return Values

Ch 5: PARTITIONS & AGGREGATIONS

  • Partitions : Architecture, Tuning
  • Storage, Slicing. Query Conditions
  • Query and Table Binding in Partition
  • Aggregations - Predefined Calculations
  • Full, Default, None and Unrestricted
  • Measure, Default Aggregations in OLAP
  • Linking Aggregations and Partitions
  • Additive & Semi-Additive Measures
  • Storage Modes : Multidimensional
  • Aggregation, Measure Group Storage
  • Automatic, Scheduled, Medium Latency
  • Low Latency and Custom Scheduling
  • Proactive Caching, Silence Interval
  • Cache Rebuild and Processing
  • Perspectives - Purpose, Scenarios
  • Dimension Usage for OLAP Relations
  • Translations : Creation, Real-time Use

Ch 5: EXPRESSIONS, SHARED DATASETS

  • Shared Data Sources, Shared DataSets
  • Date-Time Expressions with RDL Files
  • FORMAT Function in SSRS, Parameters
  • Data Type Conversions, Int / String Types
  • String Functions, Page Breaks in SSRS
  • LOOKUP Function, Dataset Joins in SSRS
  • Field Value Replacement with Datasets
  • Using LIST Item from SSRS Toolbox
  • Field Expressions and Field Properties
  • #VALX, #VALY, #PERCENT, #SERIES
  • #LABEL, #AXISLABEL, #LEGENDTEXT
  • 3D Pie Charts, Funnel and Tree Map
  • 3D Funnel, Sunburst, Shape Charts
  • Doughnut, Pyramid, 3D Pyramid Reports
  • Parameterized Gauge Reports - Filters
  • Indicators : Value, State Expressions
  • RDL Expressions, Custom Functions

Ch 6: SSIS with ETL, Warehouse (DWH)

  • OLTP Database : Historical Data Loads
  • Data Warehouse (DWH) Purpose, Usage
  • Dimensions, Attributes, Members Types
  • Dimension Tables, Fact Tables Design
  • TYPE1 and TYPE2 ETL Implementation
  • SCD Type1, Type 2 for DWH in Sales
  • Inferred Members and Legacy Loads
  • Initial Data Loads with Data Marts
  • Business Keys & non Identity Columns
  • Surrogate Keys, Alternate Business Keys
  • Cascading OLTP / Stage to DWH Rows
  • Fixed Attributes, Changing Attributes
  • Historical Attributes. Inferred Updates
  • ETL Date, Row Status Transformations
  • Attribute Key Types in SCD, Limitations
  • Historical Attributes and Data Delta
  • SSIS Connection Assistants - Reuse
  • SCD Transformations in Real-time

Ch 6: KPIs, OLAP CUBE DEPLOYMENTS

  • Key Performance Indicators (KPI) Design
  • MDX GOAL, VALUE, STATUS & TREND
  • Variance Computations. Format Options
  • KPI Organizer, MDX Expressions
  • FORMAT_STRING and MDX Operators
  • MEMBER, SOLVE_ORDER Expressions
  • Parent KPIs with MDX Hierarchies
  • KPI Browser and KPI Conditions
  • Drill-Up and Drill-Down in Excel
  • SSAS Deployment Build, Configuration
  • SSAS Deployment Options and Settings
  • Deployment Targets: Transaction Options
  • Deployment Wizard : Impersonation
  • Deployment Accounts, Password Security
  • OLAP Cube Security Roles, Partitions
  • Key Error Logs and Error Locations
  • Scripting Deployment. XMLA Scripts
  • Processing Options - Full, Default

Ch 6: REPORT BUILDER, GAUGES

  • Report Builder Installation & Usage
  • Differences with Report Designer Tool
  • Data Source Creation with Report Builder
  • Dataset Creation with Report Builder
  • Dataset Design with Parameters, Filters
  • Query Designer with Report Builder
  • Toolbox Items Insertion and Properties
  • Column Aggregates, Auto Group By Edits
  • Adhoc Reports with Column Groups
  • Dynamic Row Colors, Report Expression
  • Gauge Reports: End User Access
  • Report Types - Radial, Linear Gauges
  • Indicators, Pointers, Scale Ranges
  • Browser Compatibility, Offline Reports
  • Gauge, Gauge Panel Properties, Filters
  • Scale Properties, Values, Label Options
  • Ranges & Labels, Items, Needle Options
  • Parameterized Gauge Reports, Datasets

Ch 7: Checksum & DWH Design

  • Checksum Transformation in ETL Loads
  • Configuring Checksum: SSIS 2019, 2017
  • Transformation Logic, Parity Checks CRC
  • Checksum For Type I, Type II ETL DWH
  • DWH Dimension Tables With Checksum
  • Lookup Transformation, Row Redirection
  • OLE DB Command and Input Parameters
  • Parameter Mapping, Dynamic Updates
  • Cache Transformation CAW Memory Files
  • Memory Connection Lookup with Cache
  • Tuning Lookup: Caching, Index Options
  • Pre-ETL Activities, DB Recovery Models
  • FULL/ PARTIAL CACHE & NOCACHE
  • Performance Tuning and Pre-ETL Loads
  • Dependent Data Flow Tasks, Post ETL
  • Internal Parameters and Query Updates
  • Cache Allocation Options with ETL DWH

Ch 7: TABULAR CUBE DESIGN

  • SSAS Tabular Mode : Purpose, Usage
  • SSAS Tabular Mode Server Installation
  • Tabular Mode Server and DB Architecture
  • Tabular Mode Advantages with Data Sources
  • Tabular Mode : SSDT, Power Query, DAX
  • Power Query, DAX for OLAP Cube Design
  • In-Memory Vertipaq Storage - Performance
  • Source Data Access Flexibility with ROLAP
  • Business Intelligence Semantic Model
  • Tabular Mode : Developing Data Models
  • Workspace Server : Integrated, Dedicated
  • Workspace Server and Integrated Options
  • Compatibility Levels : Tabular Solutions
  • Cube Design with SQL Databases : Imports
  • Workflow with Tabular Mode Cube Design
  • Data Sources, SSDT, Tabular Mode Design
  • SSAS OLAP Environment, Cube Reports

Ch 7: REPORT BUILDER, MAP REPORTS

  • Map Reports - Map Layers and Map Items
  • Map Gallery - ESRI Share Files (Geo Data)
  • SQL Server Data Sources, Geo Spatial data
  • Business Analysis Dashboards For Maps
  • Polygon, Tile, Line and Point Map Layer
  • Map Visualization and Bubble Map Reports
  • Data Fields, Labels, Visualization Indicators
  • Fields to Visualize, Color Rules and Labels
  • Editing Report Builder Reports in Designer
  • SSRS Deployment: Report Designer Reports
  • SSRS Deployment: Report Builder Reports
  • Report Deployment - Builds, Config Files
  • Webservice URL, Webportal URL Access
  • Data Source, Data Set Folders, Report URL
  • Deployment of Shared Data Sources
  • Deployment of Shared Datasets, Reports
  • Report Manager Uploads for RDL Files

Ch 8: CDC Transformations for DWH

  • DML Audits with Change Data Capture
  • CDC Tables with SQL Server Connections
  • CDC Connections & ADO.NET Integration
  • CDC Control Flow and CDC State Values
  • INITIAL LOADS & PRCESSING RANGE
  • State Variables, Net Changes, Logging
  • Initial & Incremental Dimension Loads
  • Dynamic CDC Control, OLEDB Command
  • Internal Parameters and Usage Options
  • Parameter Mapping For ETL Type1, Type2
  • Integrating Control Flow for CDC @ ETL
  • CDC Splitter - Row Inserts, Updates
  • CDC Precautions, Input & Output Range
  • Derived Column Transformations with CDC
  • Limitations of ADO.NET Connections
  • Master Child Packages,Parameter Binding
  • Package Passwords, Project Parameters
  • Project Configuration Options(32,64 bit)

Ch 8: TABULAR MODE CUBES, DAX - 1

  • Cube Design with SQL Server Databases
  • Data Imports, Workspace Server Processing
  • Identifying Tables (Entities) and Dimensions
  • Attributes and Members and Relationships
  • Measure Groups and Aggregated Measures
  • Grid and Diagram Formats. Process Options
  • BUILD, DEPLOY with Integrated Workspace
  • Cube Browsing : "Analyze in Excel" Reports
  • Tabular Mode Cube Design and Hierarchies
  • DAX - User Interface and Data Types
  • DAX Usage : DAX Queries, Basic Examples
  • DAX Expressions and Real-time Usage
  • DAX Aggregated Measures in DAX, Syntax
  • Hierarchies and Levels in Cube Design
  • Relations : Active,Inactive Relations
  • Tabular Mode Cube : SSDT Imports
  • Model Options : Process, ReCalculate
  • Data Loads and Tabular Mode Explorer

Ch 8: REPORT MANAGEMENT

  • Data Source Management, Subscriptions
  • Dependant Items, Security Operations
  • Edit Shared Data Sources in Web Portal
  • Shared Data Source Enable and Hide
  • Connection Types, Edits and Security
  • Shared Dataset Operations: Report Edits
  • Data Preview, Downloads, Link Reports
  • Report Security: Browser Role, User Access
  • Content Management, My Reports, Publisher
  • Report Builder, Report Definitions, Uploads
  • Report Tuning: Caching, Rebuilds, Refresh
  • Report Tuning: Report Snapshot, Schedules
  • Subscriptions: Standard and Data Driven
  • Email and File share Subscriptions in SSRS
  • Schedules and Report Delivery Options
  • Report Server Settings, Shared Schedules
  • Report Timeout, Report Parts and Publish
  • Report Builder Sub Reports, Report Parts

Ch 9: Fact Table Design, DWH Loads

  • Fact Table - Design and ER Model
  • DWH : STAR & SNOWFLAKE Schemas
  • Time Dimensions and ETL Date / Time
  • Link Time Dimension to Facts, Lookups
  • Parent-Child Packages for Fact Loads
  • Inferred Members for NULL Dimensions
  • SCD Wizard for DWH Fact Table Design
  • Parameter Mapping for Incr Updates
  • ETL Load IDs - Dimension Attributes
  • Error Handling, Event Handling in SSIS
  • Text Qualifiers with Flat File Sources
  • Fact Load Design for Initial, Incr Loads
  • End-to-End DWH Design Implementation
  • Direct Data Loads and Staging Tables
  • Fact Table Staging and Incr Updates

Ch 9: TABULAR MODE - DAX LEVEL 2

  • KPIs (Key Performance Indicators) & Use
  • Partitions in Tabular Mode Cube Design
  • Using Power Query for Partition Design
  • Power Query Expressions and Data Filters
  • Import Data Options and ETL Operations
  • Defining Measures with DAX Expressions
  • Defining Perspectives for Cube Access
  • Data Expression Language (DAX) Basics
  • DAX Usage : Columns & Measures in SSAS
  • Auto-generated Expressions in DAX, Usage
  • Member Representations in DAX Queries
  • DAX Functions, Expressions, Real-time Use
  • Standard and Time Intelligence Functions
  • DAX FILTER(), CALCULATE() Operations
  • Time Dimension and YTD(), QTD(), MTD()

Ch 9: MOBILE, KPI, CUBE REPORTS

  • Mobile Reports : Creation & Usage
  • Excel and Report Server Sources
  • Working with Mobile Report Publisher
  • Elements Layout: Master, Tablet
  • Grids and Color Palette. Deployment
  • RSMobile Formats: Uploads, Downloads
  • Shared Dataset in Report Builder Tool
  • KPI Reports: Design from Web Portal
  • KPI: Value, Goal, Status, Trend
  • KPI Visuals: Bar, Line, Step, Area
  • Custom URLs and Mobile Reports
  • Cube Reports with SSAS MDX, DAX
  • SSRS Cube Reports with Parameters
  • MDX Default Parameters, Options
  • SSAS OLAP Cube Actions with SSRS

Ch 10: DWH MIGRATIONS, SCRIPT Task

  • DataWarehouse Migrations with SSIS
  • Using SSIS Containers, Db Integrity Task
  • Pre-Database Migration Task Precautions
  • Online and Offline DB Transfer Options
  • Copy / Move with DWH Migrations
  • SMTP : Simple Mail Transfer Protocol
  • SQL Server Agent and Package Events
  • Data Profiling with ADO.NET Connectors
  • XML Files & SSIS Data Profiler Tool
  • Script Task - Working in SSIS Control Flow
  • Script Task - VB.NET Program Compilations
  • Variables, Parameters with SSIS Script Task
  • Read Only, Read Write Variable Expressions
  • Expressions and Debugging, Break Points
  • Variables, Parameters Mapping Expressions
  • File System Tasks and Limitations
  • SQLDataAdapters & System.Data.SQLClient

Ch 10: OLAP DATABASE MANAGEMENT

  • OLAP Backups - Multidimensional, Tabular
  • OLAP Restores - Multidimensional, Tabular
  • Detach, Attach Operations with OLAP DBs
  • OLAP Database Processing, XMLA Scripts
  • Cube Processing Jobs with SQL Agent
  • OLAP DB Scripting with XMLA, Cloning
  • OLAP DB Security Roles - MDX, DAX
  • Partition Management - Split, Merge
  • Cube Audits, Usage Based Optimization
  • Cube Writebacks. Cube Updates with MDX
  • Tabular Mode Cube Processing Options
  • Direct Query and In Memory Processing
  • In-Memory with Direct Query Processing
  • Data Mining: Decision Trees, Clustering Alg
  • Training, Testing Sets. Lift Charts & DMX
  • Dimension Types: Role Playing, Degenerate
  • Multidimensional - Tabular Comparisons

Ch 10: PROJECT WORK in SSRS

  • SQL Server Data Sources and Datasets
  • Designing RDL [Paginated], Expressions
  • Chart Reports, Line Reports, Options
  • Dataset with Parameters and Filters
  • Trend Analysis, Continuous Data Reports
  • Data Bar Reports and Stacked Reports
  • Multi-series Charts, Dynamic Chart Size
  • Axis Display Control : Paginated Reports
  • Parameters and Filters - When to use which
  • Complete Project Solution & Explanation
  • Work Flow Operations and Report Types
  • Report Design, Builds, Report Deployment
  • Report Edits, Data Source Changes to Azure
  • Azure SQL Database for SSRS DB Source
  • Report Management, Security, Subscriptions
  • Report Tuning, Caching, Snapshot Options
  • Project FAQs and Explanations for Resume

Ch 11: SSIS DEPLOYMENTS, UPGRADES

  • SSISDB Catalog Deployments, ISPAC Files
  • Package Builds, Verification, Scripts
  • Project Deployment Wizard Targets, Logs
  • DB Catalog Folders & Projects Creation
  • Package Executions - Scripts, Reports
  • Package Validations, 32/64 bit Options
  • Configurations & Parameter Management
  • Package Jobs @ SQL Agent. Job Steps
  • Job Schedules and Notifications Event Logs
  • Package Security - SSISDB Logins, Users
  • Folder Level and Project Level Security
  • Project Migration Utilities, Upgrades
  • Package Imports, Exports with ISPAC Files
  • Command-Line Deployment,Execution Utility
  • Package Execution & Validation Reports
  • Package Versions and Restores, Rollbacks

Ch 11: Real-time Project for SSAS

  • Working with SQL Server Data Sources
  • Data Modeling and Relation Management
  • Creating Bridging Tables and References
  • Identifying Measures and Measure Groups
  • Identifyig Attributes and Dimensions
  • Adding Hierarchies and Attribute Relations
  • Time Intelligence and BI Enhancements
  • Cube Calculations with MDX / DAX
  • Defining KPIs, Perspectives, Roles
  • Cube Deployment Options with MS OLAP
  • Adding Hierarchies and Attribute Relations
  • Time Intelligence and BI Enhancements
  • Cube Calculations with MDX / DAX
  • Defining KPIs, Perspectives, Roles
  • Cube Deployment & End user Cube Access
  • DAX Queries in MS Excel (Excel Analyzer)

End to End Implementation (Real-time)

  • End to End MSBI Implementation Process
  • Project Requirements and SDLC Life Cycle
  • Database Design and Entity Selection
  • Understanding OLTP Databases, Relations
  • Design of DWH : Data Warehouse Database
  • SCD Techniques for ETL, Dimension Loads
  • Fact Loads, STAR / SNOWFLAKE Schemas
  • DWH Database Limitations for Analysis
  • OLAP Databases for Data Analysis
  • Cube Design and Operational Modes
  • Mode Selection and Capacity Planning
  • Tabular Mode OLAP Cube: Advantages
  • Excel Analysis and Reports with MDX
  • Using DAX for Modeling, Cube Reports
  • Paginated Reports with OLAP, DWH
  • MSBI Limitations, Need for Azure BI
Ch 12: Realtime Project for SSIS & DWH
Ch 12: Integration with Power BI [Plan C]
MSBI Resume Guidance, Interview FAQs

Part 1: Power BI Report Design

Part 2: ETL, Data Modeling, DAX

Part 3: Power BI Cloud, Admin

Ch 1 : POWER BI BASICS

  • Power BI Job Roles in Real-time
  • Power BI Data Analyst Job Roles
  • Business Analyst - Job Roles
  • Power BI Developer - Job Roles
  • Power BI for Data Scientists
  • Comparing MSBI and Power BI
  • Comparing Tableau and Power BI
  • MCSA 70-778, MCSA 70-779 Exam
  • Types of Reports in Real-World
  • Interactive & Paginated Reports
  • Analytical & Mobile Reports
  • Data Sources Types in Power BI
  • Power BI Licensing Plans - Types
  • Power BI Training : Lab Plan
  • Power BI Dev & Prod Environments

Ch 7 : POWER QUERY LEVEL 1

  • Power Query M Language Purpose
  • Power Query Architecture and ETL
  • Data Types, Literals and Values
  • Power Query Transformation Types
  • Table & Column Transformations
  • Text & Number Transformations
  • Date, Time and Structured Data
  • List, Record and Table Structures
  • let, source, in statements @ M Lang
  • Power Query Functions, Parameters
  • Invoke Functions, Execution Results
  • Get Data, Table Creations and Edit
  • Merge and Append Transformations
  • Join Kinds, Advanced Editor, Apply
  • ETL Operations with Power Query

Ch 13 : POWER BI CLOUD - 1

  • Power BI Service Architecture
  • Power BI Cloud Components, Use
  • App Workspaces, Report Publish
  • Reports & Related Datasets Cloud
  • Creating New Reports in Cloud
  • Report Publish and Report Uploads
  • Dashboards Creation and Usage
  • Adding Tiles to Dashboards
  • Pining Visuals and Report Pages
  • Visual Pin Actions in Dashboards
  • LIVE Page Interaction in Dashboard
  • Adding Media: Images, Custom Links
  • Adding Chs and Embed Links
  • API Data Sources, Streaming Data
  • Streaming Dataset Tiles (REST API)

Ch 2: BASIC REPORT DESIGN

  • Power BI Desktop Installation
  • Data Sources & Visual Types
  • Canvas, Visualizations and Fields
  • Get Data and Memory Tables
  • In-Memory xvelocity Database
  • Table and Tree Map Visuals
  • Format Button and Data Labels
  • Legend, Category and Grid
  • PBIX and PBIT File Formats
  • Visual Interaction, Data Points
  • Disabling Visual Interactions
  • Edit Interactions - Format Options
  • SPOTLIGHT & FOCUSMODE
  • CSV and PDF Exports. Tooltips
  • Power BI EcoSystem, Architecture

Ch 8 : POWER QUERY LEVEL 2

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

Ch 14 : POWER BI CLOUD - 2

  • Dashboards Actions,Report Actions
  • DataSet Actions: Create Report
  • Share, Metrics and Exports
  • Mobile View & Dashboard Themes
  • Q & A [Cortana] and Pin Visuals
  • Export, Subscribe, Subscribe
  • Favorite, Insights, Embed Code
  • Featured Dashboards and Refresh
  • Gateways Configuration, PBI Service
  • Gateway Types, Cloud Connections
  • Gateway Clusters, Add Data Sources
  • Data Refresh : Manual, Automatic
  • PBIEngw Service, ODG Logs, Audits
  • DataFlows, Power Query Expressions
  • Adding Entities and JSON Files

Ch 3 : Visual Sync, Grouping

  • Slicer Visual : Real-time Usage
  • Orientation, Selection Properties
  • Single & Multi Select, CTRL Options
  • Slicer : Number, Text and Date Data
  • Slicer List and Slicer Dropdowns
  • Visual Sync Limitations with Slicer
  • Disabling Slicers,Clear Selections
  • Grouping : Real-time Use, Examples
  • List Grouping and Binning Options
  • Grouping Static / Fixed Data Values
  • Grouping Dynamic / Changing Data
  • Bin Size and Bin Limits (Max, Min)
  • Bin Count and Grouping Options
  • Grouping Binned Data, Classification

Ch 9 : POWER QUERY LEVEL 3

  • Creating Parameters in Power Query
  • Parameter Data Types, Default Lists
  • Static/Dynamic Lists For Parameters
  • Removing Columns and Duplicates
  • Convert Tables to List Queries
  • Linking Parameters to Queries
  • Testing Parameters and PBI Canvas
  • Multi-Valued Parameter Lists
  • Creating Lists in Power Query
  • Converting Lists to Table Data
  • Advanced Edits and Parameters
  • Data Type Conversions, Expressions
  • Columns From Examples, Indexes
  • Conditional Columns, Expressions

Ch 15 : EXCEL & RLS

  • Import and Upload Options in Excel
  • Excel Workbooks and Dashboards
  • Datasets in Excel and Dashboards
  • Using Excel Analyzer in Power BI
  • Using Excel Publisher in PBI Cloud
  • Excel Workbooks, PINS in Power BI
  • Excel ODC Connections, Power Pivot
  • Row Level Security (RLS) with DAX
  • Need for RLS in Power BI Cloud
  • Data Modeling in Power BI Desktop
  • DAX Roles Creation and Testing
  • Adding Power BI Users to Roles
  • Custom Visualizations in Cloud
  • Histogram,Gantt Chart,Infographics

Ch 4 : Hierarchies, Filters

  • Creating Hierarchies in Power BI
  • Independent Drill-Down Options
  • Dependant Drill-Down Options
  • Conditional Drilldowns, Data Points
  • Drill Up Buttons and Operations
  • Expand & Show Next Level Options
  • Dynamic Data Drills Limitations
  • Show Data and See Records
  • Filters : Types and Usage in Real-time
  • Visual Filter, Page Filter, Report Filter
  • Basic, Advanced and TOP N Filters
  • Category and Summary Level Filters
  • DrillThru Filters, Drill Thru Reports
  • Keep All Filters" Options in DrillThru
  • CrossReport Filters, Include, Exclude

Ch 10 : DAX Functions - Level 1

  • DAX : Importance in Real-time
  • Real-world usage of Excel, DAX
  • DAX Architecture, Entity Sets
  • DAX Data Types, Syntax Rules
  • DAX Measures and Calculations
  • ROW Context and Filter Context
  • DAX Operators, Special Characters
  • DAX Functions, Types in Real-time
  • Vertipaq Engine, DAX Cheat Sheet
  • Creating, Using Measures with DAX
  • Creating, Using Columns with DAX
  • Quick Measures and Summaries
  • Validation Errors, Runtime Errors
  • SUM, AVERAGEX, KEEPFILTERS
  • Dynamic Expressions, IF in DAX

Ch 16 : Report Server, RDL

  • Need for Report Server in PROD
  • Install, Configure Report Server
  • Report Server DB, Temp Database
  • Webservice URL, Webportal URL
  • Creating Hybrid Cloud with Power BI
  • Using Power BI DesktopRS
  • Uploading Interactive Reports
  • Report Builder For Report Server
  • Report Builder For Power BI Cloud
  • Designing Paginated Reports (RDL)
  • Deploy to Power BI Report Server
  • Data Source Connections, Report
  • Power BI Report Server to Cloud
  • Tenant IDs Generation and Use
  • Mobile Report Publisher, Usage

Ch 5 : Bookmarks, Azure, Modeling

  • Drill-thru Filters, Page Navigations
  • Bookmarks : Real-time Usage
  • Bookmarks for Visual Filters
  • Bookmarks for Page Navigations
  • Selection Pane with Bookmarks
  • Buttons, Images with Actions
  • Buttons, Actions and Text URLs
  • Bookmarks View & Selection Pane
  • OLTP Databases, Big Data Sources
  • Azure Database Access, Reports
  • Import & Direct Query with Power BI
  • SQL Queries and Enter Data
  • Data Modeling : Currency, Relations
  • Summary, Format, Synonyms
  • Web View & Mobile View in PBI

Ch 11 : DAX Functions - Level 2

  • Data Modeling Options in DAX
  • Detecting Relations for DAX
  • Using Calculated Columns in DAX
  • Using Aggregated Measures in DAX
  • Working with Facts & Measures
  • Modeling : Missing Relations
  • Modeling : Relation Management
  • CALCULATE Function Conditions
  • CALCULATE & ALL Member Scope
  • RELATED & COUNTROWS in DAX
  • Entity Sets and Slicing in DAX
  • Dynamic Expressions, RETURN
  • Date, Time and Text Functions
  • Logical, Mathematical Functions
  • Running Total & EARLIER Function

Ch 17: MSBI Integrations

  • Power BI with SQL Server Source
  • Power BI with SQL Data Warehouse
  • Power BI with SSAS OLAP Server
  • Power BI with Azure SQL DB Source
  • Power BI with Azure SQL Warehouse
  • Power BI with Azure Analysis Server
  • Power BI with SSRS (RDL) Reports
  • Power BI Report Builder Tool
  • Installation & Configuration
  • Paginated Reports Design, Use
  • Data Sources, Datasets, RDL
  • Report Publish (RDL) to Cloud
  • Report Verifications, Data Sync
  • Interactive Vs Paginated Reports
  • Creating, Managing Alerts in Cloud

Ch 6 : Visualization Properties

  • Stacked Charts and Clustered Charts
  • Line Charts, Area Charts, Bar Charts
  • 100% Stacked Bar & Column Charts
  • Map Visuals: Tree, Filled, Bubble
  • Cards, Funnel, Table, Matrix
  • Scatter Chart : Play Axis, Labels
  • Series Clusters & Selections
  • Waterfall Chart and ArcGIS Maps
  • Infographics, Icons and Labels
  • Color Saturation, Sentiment Colors
  • Column Series, Column Axis in Lines
  • Join Types : Round, Bevel, Miter
  • Shapes, Markers, Axis, Plot Area
  • Display Units,Data Colors,Shapes
  • Series, Custom Series and Legends

Ch 12 : DAX FUNCTIONS Level 3

  • 1:1, 1:M and M:1 Relations
  • Connection with CSV, MS Access
  • AVERAGEX and AVERAGE in DAX
  • KEEPFILTERS and CALCUALTE
  • COUNTROWS, RELATED, DIVIDE
  • PARALLELPERIOD, DATEDADD
  • CALCULATE & PREVIOUSMONTH
  • USERELATIONSHIP, DAX Variables
  • TOTALYTD , TOTALQTD
  • DIVIDE, CALCULATE, Conditions
  • IF..ELSE..THEN Statement
  • SELECTEDVALUE, FORMAT
  • SUM, DATEDIFF Examples in DAX
  • TODAY, DATE, DAY with DAX
  • Time Intelligence Functions - DAX

Ch 18 : REAL-TIME PROJECT
  • Project Requirement Analysis
  • Implementing SDLC Phases
  • Requirement Gathering, FSA
Phase 1:
  • PBIX Report Design
  • Visualizations, Properties
  • Analytics and Formating
Phase 2:
  • Data Modeling, Power Query
  • Dynamic Connections, Azure DB
  • Parameters and M Lang Scripts
Phase 3:
  • DAX Requriements, Analysis
  • Cloud and Report Server
  • Project FAQs and Solutions

Mod 1: Azure Data Factory [ADF], Synapse

Mod 2: Azure Storage & Stream Analytics

Mod 3: Azure Databricks & SparkSQL

Chapter 1: Cloud Basics, Azure SQL DB

  • Cloud Introduction and 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]
  • Firewall Rules and Azure Services
  • Connections with SSMS & ADS Tools
  • Working with Azure Portal
  • Resource Group Navigations, Options

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
  • General Purpose: Gen 1 & Gen 2 Versions
  • 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 1: Azure Intro, Azure Databricks

  • Azure Databricks : Purpose & Config
  • Need for Azure Databricks (ADB)
  • Azure Databricks Service Creation
  • Azure Databricks Workspace & Usage
  • Spark Cluster Configurations & Capacity
  • Driver Nodes and Worker Nodes in Spark
  • Master Node & Cluster Creation Process
  • Cluster Types and Capacity Options
  • Standard, High Concurrency Clusters
  • Databricks Runtime Service & DBUs
  • Databricks File System (DBFS) and Usage
  • Azure Databricks Workspace Operations
  • ETL and Data Storage Components
  • Spark Concepts and Spark SQL
  • Spark Context and Spark Session
  • DataFrame, Dataset and Real-time Use

Chapter 2: Synapse SQL Pools (DWH)

  • 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 with COPY Table
  • Dynamic Views (DMV) with PDW
  • Data Loads Monitoring, Resource Class

Chapter 2: Azure Migration, 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 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)
  • Dataframes: 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: 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 and Concurrency
  • Creating Linked Services, Datasets
  • Pipeline Trigger, Author and Monitor

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 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, DataframeWriter
  • JDBC Properties, Port Settings & Options
  • Data Extraction, SQLContext & Dataframes
  • Pandas Data Frame for Big Data Analytics
  • JDBC URL Options & PySparkSQL Modules

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, Consistency Check
  • Compression Option, DOP and DOCP
  • ETL Staging Advantages & Performance
  • Staging with Storage Account, Container
  • ADF Pipeline Triggers and Monitoring
  • Polybase For Azure Synapse, Advantages

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 Data Lake Storage Security Options
  • ACL : Access Control Lists & Security
  • Azure BLOB Storage Containers & ACLs
  • Folder Level and File Level Security
  • ACL Permissions: Read, Write & Execute
  • Access Policy: Creation and Realtime Use
  • Permissions: rwacdl; Azure Principals, CORS
  • Comparing IAM and ACLs in Data Lake Store

Chapter 4: Open Data Sources, DeltaLakes

  • Creating Python Notebooks with Databricks
  • Spark Dataframes with Azure OpenDatasets
  • Windows Azure Storage Blob [wasb] Sources
  • Creating Dataframes & Temporary Views
  • Using Print and Display Functions with ADB
  • Big Data Analysis with BLOB Data & Charts
  • Keys, Values, Aggregations, Display Type
  • Databricks Notebooks, Jobs and Stages
  • Azure DeltaLake 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

Chapter 5: OnPremise Data 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 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 5: Databricks Security & Jobs

  • Azure Databricks Security Operations
  • Azure Active Directory (Azure AD)
  • AD Users and RBAC with IAM
  • Owner, Contributor & Reader Roles
  • Workspace Admin Permissions
  • Notebook Permissions and Share Options
  • Shared Notebooks, User Access Options
  • Notebook Operations: Clone & Export
  • Databricks Jobs: Creation Options, Usage
  • Job Limits, Workspace, Concurrency Limits
  • Notebooks with and without Parameters
  • Jobs with Default Parameters, Executions
  • Interactive, Automated Clusters for Jobs
  • Job Schedules and Manual Executions
  • Active Jobs, Recently Run Jobs, Monitoring
  • ADB Jobs with Azure OData Sources, BLOB

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 6: Azure Stream Analytics, IoT

  • Azure Stream Analytics: Real-time Usage
  • Real-time Data Processing, Event Tracking\
  • Ingest, Deliver and Analysis Operations
  • Azure Stream Analytics Jobs Concept
  • Understanding Input & Output Options
  • SAQL Queries for Stream Analytics Jobs
  • IoT: Internet Of Things For Real-time Data
  • Need for IoT Hubs and Event Hubs
  • Creating IoT Device for Data Inputs
  • Creating Azure Strean 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 6: Databricks @ BLOB, Power BI

  • BLOB Data Access with Databricks
  • Accessing Storage Account, Container
  • Gerate, Use SAS: Shared Access Signature
  • dbutils.fs.mount() with DBFS Store
  • fs.azure.sas.container.strorageaccount
  • spark.read() and DBFS Mounts
  • Scala Transformations, Create Temp View
  • Spark SQL Queries with Temp Views
  • dataframe.write.jdbc() & JVM Properties
  • spark.read.jdbc() with Azure SQL DB
  • Power BI Integration with Databricks
  • Server Host Name, Port and Http Path
  • Cluster Configurations and JDBC
  • User Access Token Generation, Usage
  • Spark ClusterAccess, Power BI Analytics

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 7: IoT Hubs & 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
  • 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
  • RootManageSharedAccessKey & Options
  • Connection Strings & Event Service Bus
  • Telco App Installation, Executions. LIVE Data
  • On-Premise App Integration with ASA Jobs

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
  • Spark Notebooks For Dynamic Connections
  • Parameterized Options & Blob Access
  • Data Preparation & Big Data Ingestion
  • Data Extraction and ADLS Storage
  • show(), transformations, wasbs Options
  • Azure SQL Server & Synapse Creations
  • Data Loads with Incremental Changes

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
  • Row Terminator and Compressions
  • T-SQL Queries and Aggregations
  • Aggregation Data Loads in Synapse

Chapter 8: Azure Stream Analytics Security

  • 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
  • Using Azure Key Vaults to secure Resources
  • Azure Storage: Replications and DR Options
  • LRS: Locally Redundant Storage
  • GRS: Globally Redundant Storage
  • ZRS: Zone Redundant Storage
  • Replication Options and Advantages
  • Replication Verification and Modifications
  • Azure Storage Endpoints, Failover Partner
  • ADF Integration, Real-time Project
  • 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()
  • 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
  • Concept wise FAQs, Resume Guidance
  • Project Requirement, Solution, FAQs
  • DP 203 Certification Guidance

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 OpenDatasets 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 Resoruce 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)
  • ADF V1 to V2 Migrations, Considerations

Azure SSIS Training Content


Chapter 1: SSIS Migrations to Azure Data Factory
Provision Azure SQL Server Integration Services; SSIS DB Creation and Configuration Recommendations; Node Size, Node Number and Edition / License Options; Count, Parallel Execution Settings and DIUs; Edition Settings, logical SQL Server Configurations; SSIS DB Access from SSMS, SKU & Edition Checks; SSIS Package Validations with Connection Parameters; Generation of ISPAC Files in On-Premise, Validations; SSIS Catalog Database, Catalog Folders Creation; SSIS Deployments with ISPAC to Azure SSIS Catalog DB; Package Verifications and Acess from ADF Portal; SSIS Package Execution from ADF Portal, SSIS Store;

Chapter 2: Azure SSIS from Visual Studio (SSDT)
Accessing Azure Data Factory Resource from SSDT; Storage Account and BLOB Container Configurations; Azure SQL Server Deployment for SSIS Catalog DB; Configuring Azure Data Factory, Azure SSIS IR; Using Azure Enabled Integration Services Project in SSDT; Enabling, Linking ADF Account to SSIS Project; Using Azure BLOB Upload Task in SSIS - SSDT Tool; Storage Account, Access Key & Authentication Tests; SSIS Import and Export Operations with Azure; Excel Sheet to Azure SQL Database Tables in SSIS; Connection Settings and Package Executions in Azure; PIVOT Operations in SSIS, Data Uploads to Azure;

Chapter 3: SSIS Packages with Azure Feature Pack
Azure Feature Pack Installations and Real-time Usage; Using Azure BLOB Destination with ETL in SSIS; Using Azure SQL Server Databases with ETL in SSIS; SSIS Package Design with Azure Connections, Parameters; Azure SSIS Runtime Configurations, SSIS DB usage; Azure SSIS Package Executions with T-SQL Scripts; Real-time Considerations with Azure SSIS Migrations; Security Options - Data Security at Rest, TDE Options; Tools - Azure SSIS IR Customizations and Precautions; Pricing - Azure Hybrid Benefit (AHB), DIU and DTU Costs; 3rd Party Tools - Azure SSIS Feature Pack, IR Customization; Networking - Odata Sources, WAN & VNET Considerations;

Chapter 4: Elastic Jobs with Azure SSIS DB
Working with Azure SSIS DB - Standard Edition; Elastic Job Agent Service and Job Databases; Creating Master Keys and Database Credentials; Job Database Configurations with T-SQL Scripts; Testing Elastic Jobs from Azure Portal, T-SQL; Setting Retention Window in Azure SSIS DB with T-SQL; Start, Stop & Schedule Azure SSIS IR; Working with Web Activity in Azure Data Factory; Web Activity : PUSH / PULL Settings, Messages; Azure Management URLs, Azure SSIS IR Start & Stop; MSBI Authentication with Web Activity, Security; Execute SSIS Package Activity with ADF Pipeline; ADF Trigger Schedules and Tumbling Window;

Chapter 5: Azure SSIS Projects : Versioning, ADF Jobs
Azure SSIS Projects : Deployments and Exports; Azure SSIS Projects : Improts to SSDT Environment; Enabling ADF for Existing SSIS Projects, Parameters; SSIS Package Deployments. Deployment Rollbacks, LSN; Creating ADF Jobs from SSMS - using SSIS Schedules; Trigger Edits and Configuration Settings with ADF Jobs; Azure SSIS - Security Management; Creating Logins and Users in Azure SQL Server; User Mapping with SSIS Catalog Database, Scripts; Folder Level and Project Level Permissions in SSSDB; Read, Modify and Manage Permissions in SSIS DB; Principal IDs and Permission Types, Object Types; IAM : ADF Security @ Azure Active Directory Users, Groups;

Azure SSAS Training Content

Chapter 1: Azure Analysis Services Operations

Azure Analysis Services : Architecture and Purpose; PaaS Implementation of SSAS in Azure Cloud; Azure Analysis Services : Advantages over MSBI SSAS; Deployment, Integration and Low Cost of Ownership; Server Side Encryption (SSE) with Azure BLOB Data; SSAS Data Models with Azure Analysis Services; Deployment Options and Workflow Operations; Azure Analysis Services: Query Processing Unit (QPU); Service Tiers (Plans): Developer, Basic and Standard; Data Source Types and In-Memory Options in AS; Creating and Connecting to Azure Analysis Services; Sample Models: Creation, Connection and Analytics;

Chapter 2: Data Models with Azure Analysis Services

Creating Data Models in SQL Server Data Tools; Tabular Mode Data Models with Azure SQL Database; Working with In-Memory Xvelocity Vertipaq Database; Multi Factor Authentication (MFA) with SSMS Tool; Creating Analysis Services Tabular Mode Projects; Compatibility Levels and Integrated Workspace; Navigators, In-Memory Data Processing & Cubes; Data Model Deployments with Azure Analysis Server; Dimension & Attribute Identification in Data Models; Aggregated Measures, KPIs and Excel Data Analytics; Creating Hierarchies and Attribute Relations; Azure Active Directory Authentication For Azure AS;

Chapter 3: Azure Data Sources and DAX Calculations

Azure Synapse (Data Warehouse) Data Sources; Azure Storage Tables and Azure Files (BLOB) Data; Azure Cosmos Databases for Azure Analysis Services; Using SMK: Service Managed Key for Data Models; Using Azure Active Directory (AAD) Users and Groups; Using Azure Service Principals and Tenants for Azure AS; Using Service Vaults for Authentication Options; Direct Query and Import Options with Azure Sources; DAX Implementations with Azure Data Sources; DAX Measures and DAX Calculations for Attributes; Formatting Date and Time Calculations with DAX; YoY, YTD, MoM, QTD, YTD, MTD DAX Calculations;

Chapter 4: Azure AS with On-Premise Data Sources

Accessing On-Premise Data Sources For Azure AS; OnPremise Versus Azure Analysis Services; MSMDSRV.EXE Versus Azure PaaS Components; Azure Storage Account For Azure AS OLAP DB Store; In-Memory Vertipaq Database Concepts with OLAP; MCE : Maximum Core Entitlement with Azure OLAP; On-Premise OLAP Cube Deployments (Tabular); Migrating On-Premise to Azure Analysis Services; Creating abf Files with On-Premise OLAP Database; Creating and Using Azure Storage Containers; Restoring abf Files in Azure Analysis Services (PaaS); Testing OLAP DB Migrations with Excel Analyzer;

Chapter 5: Azure AS Data Model Management

Start & Stop Options. Scale Up & Scale Down Options; Azure Analysis Services: Monitoring, Activity Logs; Event Severity, Quick Insights and Usage Metrics; Diagnostic Events, Errors and Alerts in Azure AS; Configure OLAP Database Replica, Backups & Restores; Detach and Attach OLAP Databases, Migrations; IAM : Identity and Access Management with Azure AS; Analytical Role, Monitor Role in Azure AS Data Models; Owner Role, Contributor Role & Reader Role; AD User Permissions, Firewall, Alerts and Metrics; Firewalls, Data Security and Row Level Security; Common Errors and Solutions with Azure Analysis;

Chapter 6: End to End Implementation with Azure AS

Creating Azure Data Sources and Azure Synapse; Creating Azure Analysis Services Resources, Models; Creating Data Models with Azure Synapse Databases; Tabular Mode Data Models : Design and Compatibility; Data Imports, Data Preview, Processing & Deployment; DAX For Aggregated Measures, Aggregated Columns; RLS: Row Level Security Implementation with DAX; OLAP Deployment Options and OLAP Server Settings; OLAP Database Builds and Deployment Target Files; XMLA Scripts, OLAP DB Scripting & Cloning Options; Power BI for Azure Analysis Services Data Models; Interactive Reports Design with Azure OLAP Databases;

Module I: SQL Server & T-SQL

Installation, Architecture, DB Basics

Module II: Basic SQL DBA

Backup-Restores, Jobs, Performance Tuning, Security

Module III: Advanced SQL DBA

HA-DR, Errors & Solutions, Always-On, SLA

Ch 1: JOB ROLES, INSTALLATION

  • Introduction to Databases, DBMS
  • Microsoft SQL Server : Advantages, Use
  • Versions and Editions of SQL Server
  • SQL DBA Job Roles, Responsibilities
  • Routine Maintenance DBA Activities
  • Emergency SQL DBA Activities
  • SQL Server Pre-requisites : S/W, H/W
  • SQL Server 2019/2017/2016 Installation
  • Default Instance, Named Instances
  • Port Numbers, Instance Differences
  • Service and Service Account Use
  • Authentication Modes and Logins
  • Firewall Configuration in Real-time
  • SQLServr.exe and SQLBrowser.exe

Ch 10: BACKUPS - DB, Filegroup, File

  • Database Backups, Filegroup Backups
  • Log File Backups and Log Truncations
  • COPY_ONLY Backups and Real-time Use
  • Mirror Backups and Split Backups
  • Partial Backups - ReadOnly Filegroups
  • Format, Compression and Checksum
  • Backup Verification, RetainDays, Stats
  • ContinueOnError and Backup Scripts
  • GUI and Script Backups: Differences
  • Backup History Tables in MSDB - Joins
  • Backup Audits. HOT and COLD Backups
  • Backup Devices - Creation and Usage
  • Using Backup Devices - Advantages
  • Common Errors and Solutions

Ch 19: REPLICATION For HA - Level 1

  • Replication Architecture and Topology
  • Publication Types - Purpose, Importance
  • DB Articles, Publications, Subscriptions
  • Distribution DB Configuration, Snapshots
  • Snapshot Replication and Repl Agents
  • Adding Articles to Existing (LIVE) Replica
  • PUSH, PULL Subscriptions. N/W Shares
  • Transactional Replication Configuration
  • Log Reader Agent - Configuration, Keys
  • Replication Monitor - Tracer Tokens
  • Replication Monitor - Warnings, Alerts
  • Replication Monitor - Usage and Options
  • Replication Scripts, Adding Articles
  • Replication Warnings and Agent Alerts

Ch 2: SSMS Tool, SQL BASICS - 1

  • SSMS Tool Installation, Connections
  • SQL Server Management Studio
  • Single Server, Multi Server Connections
  • System Databases: Master and Model
  • MSDB, TempDB, Resource Databases
  • SQL and T-SQL : Basic Differences
  • DDL, DML, SELECT Statements
  • Using GUI and SQL Scripts in SSMS
  • Creating Databases : Files [MDF, LDF]
  • Creating Tables in SQL Server
  • Data Storage in Tables : Inserts
  • SELECT : Data Retrieval Statement
  • Table Scans. LIVE QUERY STATISTICS
  • Limitations with SSMS GUI Options

Ch 11: RESTORES & DB RECOVERY

  • Restore Phases - COPY, REDO, UNDO
  • RECOVERY, NORECOVERY Options
  • STANDBY and REPLACE in Restores
  • File, File Group & Metadata Restores
  • Backup Verifications using GUI, Scripts
  • VERIFYONLY : Backup Verification
  • STATS, UNLOAD, STOPAT and INIT
  • PARTIAL / PIECEMEAL Restores - Use
  • Tail Log Backup Usage in Real-time
  • Restores using GUI and T-SQL Scripts
  • MOVE Options for File Level Restores
  • Point-In-Time Restore, Checkpoint LSN
  • Standby Restores and Read-Only State
  • Common Errors and Solutions

Ch 20: REPLICATION For HA - Level 2

  • Merge Replication and Merge Agent Job
  • Replication Conflicts and ROWGUIDCOL
  • Subscription Reinitialization, Expiry Setting
  • Server Subscription & Client Subscription
  • Peer-Peer Replication Connections, Nodes
  • NodeID and Conflict Detection Options
  • Replication Conflicts and sp_MSRepl
  • sp_changedbowner, backup initialization
  • Replication Conflicts and Priority Settings
  • Replication Verify - Rowcount, Checksum
  • Disabling, Cleaning Replication Topology
  • Replication Strategies for HA and DR Plan
  • Replication for Load Balancing Topologies
  • Common Errors and Solutions

Ch 3: SQL BASICS For DBAs - 2

  • Creating Databases in SQL Server
  • Database Connections and Usage
  • Creating Tables and Data Storage
  • Data Inserts and SELECT Statement
  • WHERE Conditions and Keywords
  • IN Operator and NOT IN Operator
  • Between, Not Between Operators
  • LIKE and NOT LIKE Operators
  • UPDATE Statement & Conditions
  • DELETE & TRUNCATE Statements
  • Logged and Non-Logged Operations
  • Table Structure Modifications
  • ADD, ALTER and DROP Columns
  • Aliases and Batch Statements

Ch 12: JOBS, MAINTENENCE PLANS

  • SQL Server Agent Service & Agent XPs
  • SQL Agent Jobs - GUI, Script Creations
  • Job Steps - Creation, Edits and Parse
  • Job Executions, Disable/Enable Options
  • Job History Purge. Job Activity Monitor
  • Database Maintenance - Backup Jobs
  • Scheduling Database Maintenance Plans
  • Automated Job Creations using DMPs
  • Backup Cleanup & History Cleanup Jobs
  • Backup Strategies For Minimal Data Loss
  • Backup Options: Block Size, Transfer Size
  • DB Mail Configurations and Alert System
  • DB Mail Profiles, SMTP Email Accounts
  • Operators : Creation, Job Notifications

Ch 21: LOG SHIPPING (HA - DR)

  • Log Shipping Topology for HA and DR
  • Primary and Secondary: Recovery Plan
  • Log Shipping Monitor, Jobs and Alerts
  • NORECOVERY Mode - Configuration
  • STANDBY Mode Configuration & Jobs
  • Log Shipping Jobs and Manual Failover
  • Disconnections and Delay in Restores
  • Log Shipping Mode Changes - cautions
  • Re-Restoring Log Backups for Recovery
  • LSBackup, LSCopy & LSRestore Jobs
  • LS Job Audits, Dashboards (Reports)
  • TUF Files and Standby Options in LS
  • Broken Log Shipping Chains & Issues
  • Common Errors and Solutions

Ch 4: SCHEMAS, TEMP TABLES

  • 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
  • Session Level, Connection Level Use

Ch 13: SECURITY MANAGEMENT - 1

  • Authentication Types & Modifications
  • Windows Logins & SQL Server Logins
  • Logins - Users Mapping, DB Access
  • Server Roles & Database Roles - Usage
  • Object, Column and Schema Security
  • GRANT, WITH GRANT, DENY, REVOKE
  • CONTROL, OWNERSHIP, Authorization
  • Data Encryption: Keys and Certificates
  • Data Encryption with Stored Procedures
  • Job Security : Credentials and Proxies
  • Using Proxies for SSIS Jobs, Repl Jobs
  • Security Scripts and Documentation
  • DMVs for Security Audits, Orphan Users
  • Login, User, Server Principal Audits

Ch 22: DB MIRRORING (HA - DR)

  • DB Mirroring Architecture For HA & DR
  • Log Shipping Versus Database Mirroring
  • TCP Endpoints, TCP Network Security
  • Heartbeat and Polling Concepts in DM
  • Automatic Fail-Over Procedures, Tests
  • PARTNER OFFLINE Conditions, Options
  • DB Mirroring Monitors and Commit Loads
  • SYNCHRONOUS & ASYNCHRONOUS
  • DB Mirroring and Port Configurations
  • Mirroring Monitor, Stop/Resume Options
  • Need for Always-On & Higher Availability
  • DB Recovery without Witness. Failover
  • Mirroring Monitor Jobs - Real-time Usage
  • Common Errors and Solutions

Ch 5: 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 and DEFAULT
  • Candidate Keys and Identity Property
  • Database Diagrams and ER Models
  • Relationships Verification and Links
  • Indexes : Basic Types and Creation
  • Index Sort Options, Search Advantages
  • Clustered and NonClustered Indexes
  • Primary Key and Unique Key Indexes
  • Need for Indexes - working with Keys

Ch 14: MIGRATIONS & SECURITY - 2

  • Database Migration Precautions
  • Migration Methods & Comparisons
  • CDW: Copy Database Wizard @ SSMS
  • Database Detach and Attach Options
  • SMO Method and Database Scripting
  • Creating and Using Credentials
  • Creating and Using Agent Proxies
  • SSIS Job Subsystem with Proxies
  • Copy Database Wizard: SSIS Packages
  • Scheduling Database Migration Jobs
  • Transfer of Logins & Server Objects
  • Detecting and Resolving Orphan Users
  • Containment Databases Authentication
  • Login Failure Audits & Server Logs

Ch 23: Health Checks, Issues, Solutions

  • Alerts : Creation and Notifications
  • DB Suspect Event Alerts (023)
  • Important Perfmon Counters, Alerts
  • Log Space, Memory, Tempdb Alerts
  • DBCC CHECKDB : DB Health Checks
  • Allocation Errors, Consistency Errors
  • DBCC ShowContig, Extent Fragmentation
  • Trace Flags and EstimateOnly
  • DBCC Page: GAM, SGAM and PFS
  • Consistency Errors : Cause & Solutions
  • Allocation Errors : Cause and Solutions
  • Log Space Issues and Log Rebuilds
  • Memory & TempDB Issues, Solutions
  • DBCC ShrinkDB and Page Restores

Ch 6: JOINS & AUDITS

  • JOINS - Table Comparisons Queries
  • INNER JOINS For Matching Data
  • OUTER JOINS For (non) Match Data
  • Left Outer Joins with Example Queries
  • Right Outer Joins with Example Queries
  • FULL Outer Joins - Realtime Scenarios
  • Join Queries with "ON" Conditions
  • Join Unrelated Tables in SQL Server
  • NULL, IS NULL Operators in Joins
  • CROSS JOIN and CROSS APPLY
  • CROSS JOIN Versus CROSS APPLY
  • One-way, Two Way Data Comparisons
  • Important Join Queries in T-SQL
  • Join Options: Merge, Loop, Hash

Ch 15: Tuning 1 - Audits, Indexes

  • Audit Long Running Queries : DMV, DMF
  • Activity Monitor Tool, Server Dashboards
  • Logical I/O, Physical I/O, Database I/O
  • Recent Expensive Queries, Wait Time
  • Active Expensive Queries, Statistics
  • Plan Handle, Execution Time - Audits
  • CPU, IO, Memory Consumption Reports
  • Indexes: Architecture and Index Types
  • B Tree Structure, IAM Page [Root]
  • Clustered & NonClustered Indexes
  • Included, Columnstore, Online
  • Filtered, Covering, Indexed Views
  • Fill Factor and Pad Index Options
  • Query Store - Settings and Advantages

Ch 24: PATCHES, UPGRADES, CUs

  • Establishing Downtime For Maintenance
  • Precautions for Maintenance Activities
  • DB Backups, Scripting and Services
  • Service Packs and Patch/Hotfix Activities
  • Cumulative Updates (CU), Hotfix Process
  • Instance Selectivity for Updates, Cautions
  • Verifications, Smoke Test and Rollbacks
  • Multi Instance Updates & Port Changes
  • SERVER Upgrades & VERSION Changes
  • System Database REBUILDs using CMD
  • Silent Installation & Installation Repairs
  • SQLCMD Tool and Instance Connections
  • DAC : Dedicated Administration Console
  • Service Startup Issues and Solutions

Ch 7: View, Procedures, UDF Basics

  • Views : Types, Usage in Real-time
  • System Predefined Views and Audits
  • Listing Databases, Tables, Schemas
  • Functions : Types, Usage in Real-time
  • Scalar, Inline and Multi-Line Functions
  • System Predefined Functions, Audits
  • DBId, DBName, ObjectID, ObjectName
  • Date and Time Functions : Getdate()
  • Variables & Parameters in SQL Server
  • Procedures : Types, Usage in Real-time
  • User & System Predefined Procedures
  • Parameters and Dynamic SQL Queries
  • Sp_help, Sp_helpdb and sp_helptext
  • sp_pkeys, sp_rename and sp_help
  • sp_recompile, Perrformance Benefits
  • System Objects for Metadata Access

Ch 16: Tuning 2 - INDEX MANAGEMENT

  • PARTITIONS : Advantages, Performance
  • Partition Functions & Partition Schemes
  • Partitioning Un-partitioned Tables: GUI
  • Partition Compression : ROW and PAGE
  • Auditing Table Partitioned Structures
  • Statistics : Purpose, Auto Creation
  • Statistics : Audits and Updates
  • Working with Indexes and Partitions
  • Internal and External Fragmentation
  • Index Rebuilding Process and Audits
  • Database Maintenance Plans Jobs
  • Last Used, Page Count, Fragmentation
  • Index Page Count and Index Condition
  • Degree Of Parallelism [DOP] Settings
  • Resumable Indexes: ONLINE, RESUME
  • PAUSE & RESUME in Index Rebuilds

Ch 25: ALWAYS ON AVAILABILITY

  • Always-On: Non-Clustered Environment
  • Multi Database Replication with AOAG
  • Synchronous and Asynchronous Modes
  • Policy Based Management for AOAG
  • Facets and Conditions for Policies
  • Backup Preferences, Location Options
  • Synchronization, Automated Seeding
  • Data Synchronization for AOAG
  • Port Settings, Backup Strategies: AAG
  • AOAG Verifications and Dashboards
  • Adding Availability Replica, Database
  • Adding Availability Listeners and DNS
  • Automated Failovers, Manual Failovers
  • Always-On Availability Groups Health
  • NonClustered Environment Limitations
  • Clustering with Always-On in Ch 37,38

Ch 8: 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
  • Auditing Triggers and Real World Use
  • Transactions : Types, ACID Properties
  • Transaction Types and AutoCommit
  • EXPLICIT & IMPLICIT Transactions
  • COMMIT and ROLLBACK Statements
  • Open Transaction Scenarios & Cause
  • Query Blocking Scenarios @ Real-time
  • NOLOCK and READPAST Lock Hints

Ch 17: Tuning 3 - Tuning Tools

  • Tuning Tools: Workload Files, .trc Files
  • Profiler Tuning Template, SP Events
  • DTA, Profiler Trace : Recommendations
  • PDS: Physical Design Structures
  • Index, Stats, Partition Recommendations
  • DTA with Query Execution Cache
  • Perfmon Tool : Usage, Permon Counters
  • Real-time Tracking: CPU, Memory, IO
  • Execution Plan Analysis and Internals
  • Query Costs: IO Cost and CPU Cost
  • Query Costs: SubTree & Operator Cost
  • NUMA Nodes, Processor, IO Affinity
  • Thread Count, Degree of Parallelism
  • Stored Procedure Recompilations
  • Table Scan, Index Scan, Index Seek

Ch 26: SQL DBA PROJECT - Level 1

  • Audit Login Failures : Server Logs
  • Monitoring Connectivity Issues
  • Database Refresh and MSDTC
  • Adhoc Memory Dump Files
  • PLE (Page Life Expectancy) Issues
  • Object Refresh and Recompilations
  • Server Registrations and Operations
  • Lock Monitoring Operations
  • Index Management Options
  • Open Transactions, Blocking
  • Metadata Sync-up Issues
  • Stored Procedure Recompilations
  • Backup and HA-DR Strategies
  • Db Restores and DB Repairs
  • Health Checks, Issues, Solutions

Ch 9: Server, DB Architecture

  • Server Architecture and Protocols
  • Database Engine and Query Processor
  • Parser, Optimizer, SQL & DB Manager
  • Storage Engine Components, SQL OS
  • File Manager and Database Files
  • Transaction Services, Buffer Manager
  • Lock Manager, IO Manager, MDAC
  • CLR, WAL, Lazy Writer, Checkpoint
  • Database Architecture - Data Files
  • Primary (mdf), Secondary Files (ndf)
  • Filegroups; File Size, Location
  • Pages, Extents. Uniform, Mixed Extents
  • Transaction Log File [LDF], LSN, VLF
  • Linked Servers and Remote DB Access
  • RPC, RPC Out and Remote Data Access

Ch 18: Tuning 4 - Lock Management

  • LOCKS : Types and Isolation Levels
  • S, X, IX,U, MD, Sch-M and Sch-S
  • Lock Audits : SP_WHO2 & SP_LOCK
  • sysprocesses and Lock Waits : Audits
  • Open Transactions, Query Blocking
  • Lock Hints and Isolation Levels
  • Read Committed, Read Uncommitted
  • Serializable and Repeatable Read
  • Snapshot Isolation, Page Versioning
  • Read Committed Snapshot Row Version
  • Choosing Correct Isolation Level
  • Profiler Tool and Lock Templates
  • Profiler Filters, Column Selections
  • Deadlock Audits and Deadlock Graphs
  • XDL Files and Deadlocks Prevention

Ch 27: SQL DBA PROJECT - Level 2

  • Server Down Issues and Solutions
  • Database Down Issues and Solutions
  • Data Missing Issues and Solutions
  • Hot CPU and Resource Allocations
  • Port Level Issues and Solutions
  • Online & Offline Backups, Certificates
  • Ticketing Tools : SLA & OLA Concepts
  • Incident Management and Ticketing
  • Immediate, High and Medium Priority
  • Levels of Support for Production DBA
  • 3rd Party Tools and Real-time Use
  • Automated Backups, Log File Readers
  • Licensing and Pricing Options, CALs
  • Device CALs and User CALs
  • Multiplexing with Server Licenses

Job-Oriented Real-time Training @ SQL School Training Institute

SQL Server, SQL DBA, MSBI, Azure SQL Dev, Azure SQL DBA, Power BI Training

 

Other Trainings