Azure Datafactory Video Training

This impeccable Azure BI Training course is carefully designed for aspiring BI Developers, Consultants and Architects. This Azure BI Training includes basic to advanced Business Intelligence, Data Warehouse (DWH) and Data Analytics (OLAP) concepts on SQL Server Integration Services (SSIS), Analysis Services (SSAS) and Reporting Services (SSRS). This Azure BI Training course also includes Azure Data Factory (ADF), Azure DataWarehouse (ADW), Azure Analysis Services for Big Data Analytics and Forecasts. This Azure BI Training also includes Power Query, DAX and Data Mining Concepts with Real-time Project.

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

Azure Data Factory Training Plans

  PLAN A PLAN B PLAN C
Description ADF SSIS
ADF
SSIS & Azure BI
Total Duration 10 Videos 22 Videos 27 Videos
Azure Data Factory, Data Pipelines Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
Azure Data Warehouse Concepts Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
Massively Parallel Processing Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
Copy Data Tool and ADF Portal Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
Self Hosted Integration Run Time Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
Using Procedures with ADF Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
ADF Lookups, Loops, Parameters Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
SSIS IR and Catalog Migrations Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
MSBI - SSIS For ETL, DWH Check-Symbol-for-Yes Check-Symbol-for-Yes
DWH - Dimension Table Design Check-Symbol-for-Yes Check-Symbol-for-Yes
DWH - Fact Table Design Check-Symbol-for-Yes Check-Symbol-for-Yes
Incremental Data Loads in SSIS Check-Symbol-for-Yes Check-Symbol-for-Yes
SSIS Events, Errors, Debugging Check-Symbol-for-Yes Check-Symbol-for-Yes
SSIS Catalog Databases Check-Symbol-for-Yes Check-Symbol-for-Yes
Azure Analysis Services Check-Symbol-for-Yes
ADF with Azure Analysis Services Check-Symbol-for-Yes
ADF with Azure Analysis Services Check-Symbol-for-Yes
ADW Data For Azure Analytics Check-Symbol-for-Yes
ADW Data with Power BI Check-Symbol-for-Yes
Total Course Fee INR 12,000 INR 18,000 INR 22,000

Trainer: Mr. Sai Phanindra T


ADF TRAINING HIGHLIGHTS :

✔ Basic to Adv. ETL ✔ Basic to Adv. DAX
✔ Data Modelling ✔ OLAP DB Design
✔ Power Query ✔ Basic to Adv. MDX
✔ Kimball BI Design ✔ Inmon BI Design
✔ Star, Snowflake ✔ Data Warehousing
✔ OLTP, File Data ✔ JSON, XML Data
✔ Resume Guidance ✔ MCSA MSBI Exam

All Session Are Completely Practical & Real Time

Azure Datafactory Training Course Contents:

Module I: AZURE DATA FACTORY - 1

Module II: AZURE DATA FACTORY - 2

Video 1: ADF INTRODUCTION

  • Introduction to Cloud and Advantages
  • Cloud Architecture : IaaS,PasS,SaaS
  • Microsoft Cloud Advantages : Azure
  • Azure Data Factory (ADF) : Need
  • Purpose of ADF & Advantages
  • ADF Components Overview, Use
  • Lab Plan : Azure Data Studio (ADS)
  • SQL Server Data Tools (SSDT)
  • Azure Data Factory Architecture
  • ADF : Data Processing Components
  • ADF : Data Movement Components
  • Data Pipelines and ADF Activities
  • Data Orchestration and Advantages
  • Validations, Deployments, Triggers
  • Need for Azure Data Warehouse
  • SSIS Lift and Shift with ADF
  • ADF with Azure Services, Resources

Video 6 : AZURE DATA FACTORY - 3

  • Incremental Data Loads with ETL
  • Incremental Loads in ADF Portal
  • Source Data & Watermark Column
  • Watermark Tables : Creation, Use
  • TimeStamp For Incremental Loads
  • Configuration Procedures for ETL
  • Using Lookup Activities in ADF
  • Lookup Transformation with ADF
  • Watermark Column Extraction
  • Activity Connections in ADF Portal
  • Lookup Output Row Level Audits
  • Expressions and Usage in ADF
  • Generating New Watermark Columns
  • Stored Procedures and Dynamic Values
  • Parameterized Values for Data Extraction
  • Using ADF Run IDs for Connections
  • Parameter Imports and Staging Options

Video 2: AZURE SQL CONFIG, DTU

  • Azure Registrations & Resources
  • Azure SQL Server (Logical Server)
  • Resource Groups and Azure Regions
  • Azure SQL Databases, Pricing Tiers
  • Static Pools and Elastic Pools
  • DTUs : Basic, Standard, Premium
  • DTUs : Provisioned, Serverless
  • General Purpose & Business Critical
  • Compute Generation : Gen 4, Gen 5
  • Firewall Rules and Virtual Networks
  • Azure SQL DB Access from SSDT
  • Azure SQL DB Access from SSMS
  • Azure SQL DB Access from ADS
  • DTU Concept and Automated Tuning
  • Azure Storage Resources, Purpose
  • Azure Storage Containers, Tools
  • LRS, GRS and RA-GRS Azure Types
  • Azure Storage Explorer Tool
  • Databse Migrations : Export/Import

Video 7 : AZURE DATA FACTORY - 4

  • Incremental Loads - Multiple Tables
  • Data Prep for Watermark Store
  • ForEach Activity and Real-time use
  • Lookup Activities Use in ADF
  • Linked Service Parameters, Use
  • Copy Data Activity for ETL in ADF
  • Stored Procedure Activity for ETL
  • Data Pipelines Parameters in ADF
  • Object Name Parameters, Iteractions
  • Using Self Hosted IR Engine in ADF
  • Incremental Loads with Azure Storage
  • Pipelines with Source Parameters
  • Dynamic Sources and Destinations
  • Data Preview & Dynamic Content
  • Linked Services & Datasets Creations
  • ADF Pipeline Executions, Run IDs
  • Dynamic File Formats and Connections
  • ADF Pipeline Monitoring Options
  • Trigger Monitoring and Execution Runs

Video 3: AZURE DATA WAREHOUSE

  • Azure Data Warehouse (ADW)
  • Key Benefits of ADWH, Creation
  • ADWH Resource Creation in SQL Server
  • Scaling Options, Compute Engine
  • ADW Connection from SSMS Tool
  • Data Warehouse Details: Name, Server
  • cDWU & DWU : Real-time Usage, Pricing
  • DWs and Scaling Options: DW Series
  • Massively Parallel Processing (MPP)
  • Sharding Benefits with MPP in DWH
  • Control Node and Compute Node
  • Data Distribution Components in ADW
  • DMS : Data Movement Service
  • Hash Functions, Distributed Tables
  • Round-robin Distributed Tables
  • Replicated Tables in Azure DWH
  • Queries Distribution, Nodes in ADW
  • Dynamic Management Views (DMV)

Video 8: AZURE DATA FACTORY - 5

  • Azure Data Lake Storage Environment
  • Azure Data Lake Gen 1 and Gen 2
  • Using ADF to get data from Data lake
  • ADF Integration with Azure Data Store
  • Scale-Out and Managed Data Transfer
  • Copy Data Performance Monitoring
  • Monitor, Activity Runs and Options
  • Data Partition Recommendations in ADL
  • Size Limitations and ADF Activities
  • Copy Activity Performance in ADF
  • ADF for Large Scale Data Migrations
  • Azure Data Lake and EDW Sources
  • Performance and Scalability Options
  • ADF Serverless Architecture with Lake
  • Data Size and Bandwidth Ccomparisons
  • ADF Copy Activity Scaling Options
  • ADF control flow, DIUs, Nodes Usage
  • Performance Tuning Steps in ADF

Video 4: AZURE DATA FACTORY - 1

  • Azure Data Factory (ADF) : Purpose
  • Creating ADF Resource in Azure, Use
  • Pipeline Creation and Usage Options
  • Copy Data Tool in ADF Portal, Use
  • Linked Service Creation in ADF
  • Dataset Creation, Connection Reuse
  • Staging Dataset with Azure Storage
  • ADF Pipeline Deployments, Triggers
  • Polybase Benefits for Data Staging
  • Using Azure Data Factory (ADF) Portal
  • Pipeline Creation, Resources in Portal
  • Pipeline Activities and Properties in ADF
  • Linked Service Creation and Datasets
  • ADF Pipeline Validations in Portal
  • Publish Options & Pipeline Deployment
  • Pipeline Monitoring and RUN IDs
  • Trigger Now and Monitoring Options

Video 9: AZURE DATA FACTORY - 6

  • Azure Data Factory (ADF) Management
  • Security and Audits. Monitoring Options
  • Data Format and Compressions in ADF
  • Data Movement Security & Encryptions
  • Data Movement Scenarios with ADF
  • Cloud and Hybrid Data Movement in ADF
  • RBAC Access Controls and Real-time Usage
  • Data Discovery & Classification in ADF
  • Credential Encryption, Security in ADF
  • DWH (Azure Data Warehouse) Management
  • Threat Detection, Advanced Threat Protection
  • Dynamic resource classes, ADW Queries
  • Resource Class Operations & Precedence
  • Backups and Restores with ADWH
  • Export and Import Options with ADWH
  • Security Management, Role Based Access
  • Azure Data Warehouse CheatSheet

Video 5: AZURE DATA FACTORY - 2

  • Copy Data From Azure SQL Database
  • Azure SQL Datawarehouse Sinks
  • DataSets Creation & ADF Data Pipelines
  • Activities : Move & Transform for ETL
  • Actions and View Activity Runs
  • ADF Pipeline Monitoring and Refresh
  • ADF Triggers - Manual, Automatic
  • Azure Database to Azure Data Warehouse
  • Staging Dataset with Polybase, BLOB
  • Data Integration Units [DIU] Usage
  • Azure Data Factory Runtime Engine
  • On-Premise Data Extraction and Loads
  • Self Hosted IR in Copy Data Tool
  • IR Engine Installation, Key Concepts
  • Data From Azure BLOB, Azure Warehouse
  • IR Connections & Authentication Methods
  • IR Tool Configuration and Monitoring

Video 10: AZURE DATA FACTORY - 7

  • Execute SSIS Packages in Azure
  • Azure-SSIS Integration Runtime, SSDT
  • Azure-SSIS IR Creation and Use
  • SSISDB Project Deployment Model
  • Azure Enabled SSIS Projects
  • SSIS Project Connections to ADF
  • ADF Connection Wizard From SSDT
  • Azure Storage for SSIS Packages
  • Execute SSIS packages in Azure
  • AzureDTExec Command Prompt Utility
  • AzureDTExec Runs from SSMS Tool
  • SSIS Package Migrations to ADF
  • SSIS Catalog DB with ADF
  • On-Premise SSIS Versus Azure
  • SSIS Lift and Shift with ADF
  • IR CPU & IR Memory Reports in ADF
  • Branching and Chaining in ADF

Module I: Basic SSIS Development & ETL - DWH

Module II: Advanced SSIS Development & Deployments

Video 1: SSIS INTRODUCTION, INSTALLATION & TOOLS

  • Need for SQL Server Integration Services (SSIS) & ETL / DWH
  • Advantages of SSIS for Data Loads, Operations, ETL, Warehouse
  • SSIS Tools : SSDT (SQL Server Data Tools), ETL Wizards
  • SSIS Design / Development and LIVE (Deployment) Environment
  • Understanding Data Warehouse (DWH) Design and ETL Process
  • DWH and ETL Structures, Implementations with MSBI SSIS
  • SSIS ETL Operations for Data Reads, Data Cleansing, DWH
  • Data Warehouse (DWH) Design Principles and Design Plan
  • SSIS 2016 and SSIS 2014 : SSIS Database Installations
  • Understanding SSIS Catalog DB Encryptions and CLR / Startup
  • SSIS Database and Catalog Folders - Purpose, Usage, Passwords
  • SSIS 2016 Configuration Options and DB Catalog Encryptions
  • SSIS Catalog Database (SSIS DB) : Creation, Verification
  • SSDT Tool: SQL Server Data Tools - SSDT / Visual Studio Shell
  • SSDT 2015 Installation and Verification of SSIS Templates
  • SSDT 2013 Installation and Verification of SSIS Templates
  • Understanding SSIS Developer Environment (SSDT) Interface
  • Basic Vocabulary - SSIS, ETL, DWH, Data Flow, Data Buffer
  • SSIS Package Environment and SSDT Project Creation
  • SSIS Solutions and Project Templates - Verifications
  • Version Control Options and SSIS Design Plan - Tools Usage
  • SSIS video Training - Lab Plan, Resources & Databases
  • SSIS video Training - Real-time Project, Certification (70-767)

Video 7: CHECKSUM TRANSFORMATION @ DWH DESIGN

  • Need for Checksum Transformation in ETL Data Loads
  • Configuring Checksum Transformation : 2014, 2016 Versions
  • Checksum Transformation Logic and Parity Checks (CRC) Codes
  • Working with CHECKSUM Transformation & Parity Bit Columns
  • Generating Checksum Values For Type I, Type II ETL Changes
  • DWH Dimension Tables With Checksum Transformation
  • Working with Parity Bits, Conditional Lookup Transformation
  • Row Redirection Options & Lookup Match, NonMatch Output
  • OLE DB Command: SQL Statements and Input Parameters
  • Parameter Mapping Options and Dynamic SQL for Row Updates
  • Cache Transformation with Memory Loads, CAW Files (VM)
  • Memory Connections and Reuse. Lookup with Cache Options
  • Tuning Lookup Transformation: Caching Options, Index Options
  • FULL CACHE : Usage Options. NO CACHE: Usage Options
  • PARTIAL CACHE : Precautions. Data Splits, Fast Load Options
  • Cache Connection Manager Issues, Performance Tuning @ ETL
  • Pre-ETL Data Load Operations with Memory Connections
  • Lookup with NOCACHE Options. Advantages and Precautions
  • Understanding Dependent Data Flow Tasks and Usage
  • Post ETL, Derived Column and Fuzzy Transformations
  • SSIS Package Internal Parameters and Query Updates
  • OLEDB Command with Conditional Splits, Multi Row Updates
  • Using non-Microsoft Transformations : Precautions

Video 2: SSIS ETL PACKAGES: EXTRACT,TRANSFORM,LOAD

  • Control Flow Tasks - Architecture, Purpose and Usage
  • Data Flow Tasks - Architecture, Purpose and Usage
  • Creating SSIS Packages For Basic Data Flow Operations
  • SSIS Solutions, Projects and Package Creation. Tasks
  • Need For Data Pipelines & Connections in Data Flow Tasks
  • Understanding SSIS Package (Program) Execution Process
  • Working with Data Flow Objects and Tasks in SSIS
  • Basic Data Extraction Drivers / Providers with Data Flow
  • Using OLE DB and SQL Server Connections - Usage
  • SSIS Package Creation Process - Using Control Flow Items
  • SSPI Interface Connections, Data Source, Initial Catalog
  • Using DTSX Files for SSIS Package Execution. Audits
  • SSIS Execution Context, SSIS Package Errors and Logs
  • Data Flow Transformation: Conditional Split, Expressions
  • Excel Connections, Sheets, & Memory References - Usage
  • Using Source Assistants, Destination Assistants @ SSIS
  • DAT File Imports, Data Flow Options, Adding Annotations
  • Common SSIS Package Errors & Solutions in Real-time
  • SSIS Project Configuration Options - Debugging, Bit Config
  • SSIS 64 Bit and 32 Bit Configuration Settings and Options
  • Error Outputs and Error Row Redirections in Data Flow Tasks
  • Data Flow Truncations and Error Pipelines. Transformation Types

Video 8: CHANGE DATA CAPTURE: DWH DIMENSIONS

  • DML Audits using Change Data Capture (SQL Server)
  • Understanding CDC Tables with SQL Server Connections
  • DML Audits: Inserts, Deletes, Values before/after Updates
  • CDC Source Connections - Usage, ADO.NET Integration
  • CDC Control Flow Tasks and CDC State Tasks. Values
  • INITIAL LOAD START/END. PRCESSING RANGE, MARKING
  • Using State Variables and Controls. Storing States
  • SSIS CDC Source Connections with Net Support Changes
  • SSIS CDC Control Tasks and Caching, State Variables
  • Understanding State Variables and SSIS Logging Tables
  • Initial and Incremental Dimension Loads with CDC
  • Dynamic CDC Connections, Parameter Mapping Row Updates
  • Integrating Control Flow and Data Flow for CDC @ ETL
  • CDC Splitter Transformation - Row Inserts and Updates
  • CDC Splitter Precautions, Input Types and Output Range
  • Connection Parameters, Master - Child Parameter Binding
  • Parent - Child Package Design in SSIS, Options
  • Master Packages and Child Packages, Derived Columns
  • Defining Local and Global Variables/Parameters
  • Local Connections - Package Level. Naming Conventions
  • Global Connections - Project Level. Naming Conventions
  • Parameter Bindings with Parent - Child Packages in SSIS

Video 3: SSIS FAST LOADS, MERGE, DATA CONVERSIONS

  • Understanding ETL and DWH Implementations. DWH Strategies
  • Kimball Method & Inmon Methods of BI Design & Modelling
  • Bulk Load Operations and Data Import Options in SSIS
  • Bypass Prepare and Execution Options with SQL Task
  • Historical Data Loads (Type II) and Incremental Updates
  • Debugging Controls in SSIS - Variants and Limitations
  • Data Flow Debugging Controls and Data Viewer Options
  • Synchronous & Asynchronous Transformations, Data Loads
  • Row and Partial Blocking Transformations. Buffer Reuse
  • Fully Blocking Transformations - Buffer Reuse Restrictions
  • Examples to Avoid Fully Blocking Transformation in SSIS
  • MERGE Transformation and UNION ALL Transformation
  • SORT Transformation, NOSORT Options, Advanced Sort
  • Data Conversion Transformation and SSIS Expressions
  • Data Flow Tuning with Query Locks and Performance
  • Tuning Data Flow Tasks with Fast Loads Options (OLE DB)
  • Fast Load Options for Data Flow in OLE DB Connections
  • Tabular Data Stream (TDS) Packet Sizing and Tuning
  • SSIS Package Tuning with Row Size Calculations, Batches
  • Rows Per Batch Calculations - Variants, Commit Size Options
  • Transactions and Batch Scoped Data Loads with SSIS Packages
  • Identity_Insert, Table Locks and Constraints in ETL
  • Comparing Regular Loads and Fast loads in ETL / DW

Video 9: XML, LOGGING & ERROR HANDLING

  • System.Data.SQLClient Managed Providers: Usage Options
  • Dynamic Connections and Loops with SSIS Expressions
  • SSIS Expressions with Variables For File Specifications
  • For Each Expressions - Dynamic File Types, Directories
  • Variable Mapping with Indexed Connections. Performance
  • XML Connections & Usage. Reading, Operating XML Data
  • OLE DB Connections with SQL Native Client (SNAC)
  • ODS Connection Files, Reusability Options (Dynamic)
  • Advanced Connection Options - XML, DT_NTEXT Stream
  • XML Queries, Options @ Sub Queries. XML Data Imports
  • RAW Files - DTSPipeline Wrapper & RAW Adapters
  • Row Sampling and Percentage Sampling Options
  • SSIS Logging - Text Files and sysssislog Tables @ Audits
  • Package Logging and Container Events for Execution
  • SSIS Logging Options and Connections. Verifications
  • SSIS Data Profiler Tool, ADO.NET Connections and XML
  • SQL Data Profile Viewer Tool - Usage with XML Files
  • Nullability Checks and Fast Load Options @ Row Size
  • ADO.NET Connections for SQL Profiler Tool, Options
  • Time-Out Options for Quick Profile, Candidate Keys
  • Value and Length Distribution, Surrogate Key Detection
  • XML File Imports for SQL Profiler Viewer Tool, Options
  • ADO.NET Connections For Data Audits, ETL DWH Design

Video 4: PIVOT, FUZZY LOOKUP, DATA CLEANSING, LOOPS

  • Importing Legacy Data, Need for Data Cleansing, Formatting
  • Understanding Denormalization and Keys. Need for OLTP
  • PIVOT Transformation Usage, Data Reads, Connection Assistant
  • Pivot Usage Values - Purpose and Implementation. Key Values
  • Lineage ID in SSIS - Purpose, Usage and Options. Data Mappings
  • Lineage IDs for Column Mapping and Pivot Keys
  • SSIS Input Columns, Mappings and Source Column Values
  • Data Exchange Operations with Pivot Keys and Pivot Values
  • Using Data Viewer (Debugging) for Data Transfer Verifications
  • Debugging with Detach/Attach, Memory Buffer & Row Counts
  • Fuzzy Lookup Transformation, Reference Table Connections
  • Exact, Fuzzy and Nomatch Data Cleansing with Conditional Split
  • Index Creation, Maintenance for Faster Lookup Transformation
  • Data Conversion Transformation - Usage. Derived Columns
  • Data Conversion Transformation For NVARHAR & VARCHAR Data
  • Threshold Values Selection, Search Delimiters and Options
  • Data Pipeline with _Similarity and _Confidence Columns. Usage
  • Explicit Data Type Conversions and Usage. Error Redirections
  • ForEach Loop Container Usage. File Level Connections, Variables
  • Defining Variables for Connections. DFT and Control Flow Links
  • Dynamic Connections with Variables. Connection String Properties
  • Connection Iterations, Connection Fetch and Index Mapping
  • MS Excel and OLE DB Jet Driver Connections For Data Cleasing

Video 10: SSIS CUSTOMIZATION WITH .NET SCRIPT

  • Script Task - Purpose and Working in SSIS Control Flow
  • Script Task - Usage with VB.NET Programs. Compilations
  • Script Task - Usage with C#.NET Programs. Compilations
  • Variables and Parameters with SSIS Script Task. Usage
  • Read Only and Read Write Variables. Expressions, Mapping
  • Namespace Options, Customizations : System.IO, DTS
  • Using VB.NET, C#.NET Scripting Programs with Parameters
  • Data Flow Limitations : Solutions with .NET Scripting
  • SSIS Expressions and Package Debugging, Break Points
  • SSIS Variables and Parameters - Mapping Expressions
  • Understanding Control Flow - Sequence Containers, Usage
  • Using Control Flow File System Tasks and Limitations
  • Using .Net Scripting for SQL Server Data Reads/Writes
  • SQLDataAdapters & System.Data.SQLClient Connections
  • Script Task for Data Writes and Row Level Formatting
  • Adding DTS Packages to SSIS Projects (Data Tools)
  • Package Refresh Options, Package Upgrade Properties
  • SSIS Configurations, Usage. Dynamic Connection Values
  • Using Variables and Parameters for SSIS Script Task
  • Script Component in Data Flow Task - Usage Options
  • Script Component - Input, Output and Transformations
  • Using Script Task for Control Flow, Data Flow Limitations
  • SSIS Package/Project Conversions - Process and Options

Video 5: SSIS with ETL & DWH. SCD TYPE 1, TYPE 2

  • A Sales Scenario for OLTP Database to Historical Data Loads
  • Introduction to Datawarehouse (DWH) - Purpose, Usage, Access
  • Technical Terms: Dimensions, Attributes, Members and Types
  • Dimension Tables, Fact Tables and Relations, Inferred Members
  • TYPE1 and TYPE2 Changes. ETL Implementation Techniques
  • Implementing Type I Changes (SCD) for DWH in a Sales Scenario
  • Dimensional Table Design for DWH using SCD - Type 1
  • Dimensional Table Design for DWH using SCD - Type 2
  • Initial Data Loads with Legacy Files (DAT, TXT, CSV), Data Marts
  • Working with Business Keys & non Identity Key Based Columns
  • Surrogate Keys and Alternate Business Keys - Purpose, Usage
  • Identity Columns & Key Values in DWH. Business Key Limitations
  • Cascading Source OLTP / Stage Changes to DWH Inactive Rows
  • Changes to Fixed Attributes, Changing Attributes. Observations
  • Changes to Historical Attributes. Inferred Member Updated
  • ETL Date Fields, Row Status Values and Transformations
  • Naming Conventions For SSIS Entities, Options & Reusability
  • Designing Dimensions for DW - Incremental and Historical Loads
  • Understanding OLE DB Connections for Incremental Data Loads
  • Identity Property and Attribute Key Types in SCD, Limitations
  • Historical Attributes and Data Delta Operations, Identification
  • SSIS Connection Assistants - Advantages, Package Reuse
  • SSIS SCD Transformation Limitations and Real-time Issues
  • Using SCD Transformation with OLE-DB Command Transformation

Video 11: FACT TABLE DESIGN, FACT LOADS (DWH)

  • Fact Table - Design and Implementation Rules, ER Model
  • Fact Table Design with STAR and SNOWFLAKE Schemas
  • Time Dimensions - Purpose, Usage and ETL Date / Time
  • Generating Time Dimensions for ETL, Load Identification
  • Linking Time Dimensions to Fact Loads, Lookup Operations
  • Caching Options in Lookup. Dimension Loads, Key Lookups
  • Parent-Child Packages for Dimensional Loads, Control Flow
  • Parent-Child Packages for Dimensions & Fact Table Loads
  • Master Package Design: Dimension, Fact Load Integration
  • Inferred Members and Status for NULLs in Dimensions
  • SCD Wizard for Multiple Dimensions and Fact Tables.
  • Parameter Mapping for ETL Updates, Component Properties
  • ETL Load Date IDs & Expressions for Dimension Keys
  • Error Handling & Event Handling with Master Packages
  • Text Qualifiers and Data Loads with Flat File Sources (.dat)
  • Project Connections, Parameters in Master-Child Packages
  • Key Based Dimension LOOKUPs and Indexes. Fast Loads
  • SCD Implementations with Legacy Data. ETL Load Types
  • Fact Load Design for Initial Loads, Incremental Updates
  • End-to-End Data Warehouse Design: Implementation
  • Direct Data Loads and Data Stage Operations for DWH DBs
  • Advantages of SSIS ETL/DWH Concepts. Precautions.
  • Column Statistics and Values for DWH Design Planning
  • STAR & SNOWFLAKE Schemas for DWH - Data Modeling

Video 6: CHECKPOINTS, TRANSACTIONS, EXECUTE SQL

  • Understanding Scripts for SSIS Control Flow. SQL Tasks
  • Execute SQL Task and OLE DB Queries - Connection Options
  • Transaction Options For SSIS Executables - Package Level
  • Precedence Constraints - Success / Failure / Completion Settings
  • Parallel and Sequential Task Executions. Options, Usability
  • SSIS Package Level Rollbacks and Restart Execution Options
  • SSIS Checkpoints - Purpose and Usage with Data Flow Tasks
  • Checkpoint Files and SSIS Logging Options - Package, Tasks
  • Transactions with Checkpoint File Usage in SSIS Packages
  • Checkpoint Options - Advantages, Usage and Limitations
  • SSIS Variables - Creation and Usage in ETL Data Flow Scripts
  • Working with Static and Dynamic Variables. Usage Options
  • Containers in SSIS. Usage Options & Advantages. Properties
  • SSIS Events - Validation Events, Execution and CleanUp Events
  • SSIS Package Level Parameters and Connection Properties
  • SSIS Project Level Parameters - Connection Options, Usage
  • SSIS Expressions, Default Values, and Data Types in Parameters
  • Linking Parameters and Variables with Expression Values
  • SSIS Parameters For Dynamic Control & Package Executions
  • Dynamic Connection Managers - Precedence Constraints
  • FailPackageOnFailure Options and Checkpoint Property Usage
  • Transaction Property : REQUIRED / SUPPORT, NOTSUPPORTED
  • Transaction Property Versus CHECKPOINT Files, Usage

Video 12: SSIS PROJECT DEPLOYMENT, UPGRADES

  • SSISDB Catalog Deployments - Process, ISPAC File Structure
  • SSIS Package Builds, Verification Techniques, Scripts
  • SSIS Project Deployment Wizard : Targets and Logging
  • SSIS Package Deployments - Need, Options and Tools
  • SSIS DB Catalog Folders - Creation, Usage. Properties
  • SSIS DB Catalog Projects - Creation, Usage. Properties
  • SSIS Package Executions - Scripts, Reports, Messages
  • Package Validations - Scripts, Reports, 32/64 bit Options
  • Package Configurations - Parameter Management, Scripts
  • SSIS Package Jobs from SQL Server Agent. SSIS Job Steps
  • SSIS Job Schedules and Notifications: Windows Event Logs
  • SQLISPackage130 Events and Error Logs. Package Monitoring
  • Package Security Management - SSISDB Logins and Users
  • ssis_admin, ssis_failover_monitoring_agent, ssis_logreader
  • Folder Level and Project Level Security Implementation
  • Execute/Write/Folder Security Content Management Roles
  • Business Intelligence Development Studio: SSIS BIDS 2008
  • Creating Packages, SMO Connection with BIDS. Project Builds
  • Adding DTS, SSIS 2012 Packages & Package UPGRADES
  • Project Migration Utilities in SSIS, SSIS Project Passwords
  • Importing ISPAC Files, SSIS Projects. Re-Engineering
  • Command-Line Deployments For SSIS, Execution Utilities
  • SSIS Packages for Database Migrations - Online/Offline

Video 13: REAL-TIME PROJECT IMPLEMENTATION

Real-time Project for MSBI SSIS
ECommerce Domain: STAR, SNOWFLAKE Schemas ETL Implementations for DWH. Fast Loads & Sizing Dimension Tables, Fact Tables. Connection Parameters Builds, ISPAC Files. Package & Project Deployments Security. Scripts. Utilities, Project Conversions End-to-End Implementation of SSIS Development.
 

WHERESPACERED and Other 3rd Party Tools 
Resume Preparation
Mock Interviews

Module I: AZURE DATA FACTORY - 1

Module II: AZURE DATA FACTORY - 2

Module III: AZURE ANALYSIS SERVICES

Video 1: ADF INTRODUCTION

  • Introduction to Cloud and Advantages
  • Cloud Architecture : IaaS,PasS,SaaS
  • Microsoft Cloud Advantages : Azure
  • Azure Data Factory (ADF) : Need
  • Purpose of ADF & Advantages
  • ADF Components Overview, Use
  • Lab Plan : Azure Data Studio (ADS)
  • SQL Server Data Tools (SSDT)
  • Azure Data Factory Architecture
  • ADF : Data Processing Components
  • ADF : Data Movement Components
  • Data Pipelines and ADF Activities
  • Data Orchestration and Advantages
  • Validations, Deployments, Triggers
  • Need for Azure Data Warehouse
  • SSIS Lift and Shift with ADF
  • ADF with Azure Services, Resources

Video 6 : AZURE DATA FACTORY - 3

  • Incremental Data Loads with ETL
  • Incremental Loads in ADF Portal
  • Source Data & Watermark Column
  • Watermark Tables : Creation, Use
  • TimeStamp For Incremental Loads
  • Configuration Procedures for ETL
  • Using Lookup Activities in ADF
  • Lookup Transformation with ADF
  • Watermark Column Extraction
  • Activity Connections in ADF Portal
  • Lookup Output Row Level Audits
  • Expressions and Usage in ADF
  • Generating New Watermark Columns
  • Stored Procedures and Dynamic Values
  • Parameterized Values for Data Extraction
  • Using ADF Run IDs for Connections
  • Parameter Imports and Staging Options

Video 11: AZURE ANALYSIS SERVICES

  • Introduction to Azure Analytics
  • Introducton to Azure Analysis Services
  • Need for Azure Analysis Services in PAAS
  • Various PAAS Components in Azure AS
  • Azure Analysis Vs On-Premise SSAS
  • Practical Limitations with On-Premise SSAS
  • Advantages of Tabular Mode in MSBI SSAS
  • Tabular Mode in Azure Analysis Services
  • Integraton of Azure Analysis Server
  • MS Excel, Power BI, Tableau Connections
  • Power BI Cloud Connections with Azure AS
  • Real-time Usage with Big Data Analytics
  • Azure Analysis Services : Pre-Requisites
  • SQL Server Data Tools (SSDT), SSMS
  • Data Model Development Tools for AAS
  • Integration with Azure Active Directory
  • Azure Analysis Services Security

Video 2: AZURE SQL CONFIG, DTU

  • Azure Registrations & Resources
  • Azure SQL Server (Logical Server)
  • Resource Groups and Azure Regions
  • Azure SQL Databases, Pricing Tiers
  • Static Pools and Elastic Pools
  • DTUs : Basic, Standard, Premium
  • DTUs : Provisioned, Serverless
  • General Purpose & Business Critical
  • Compute Generation : Gen 4, Gen 5
  • Firewall Rules and Virtual Networks
  • Azure SQL DB Access from SSDT
  • Azure SQL DB Access from SSMS
  • Azure SQL DB Access from ADS
  • DTU Concept and Automated Tuning
  • Azure Storage Resources, Purpose
  • Azure Storage Containers, Tools
  • LRS, GRS and RA-GRS Azure Types
  • Azure Storage Explorer Tool
  • Databse Migrations : Export/Import

Video 7 : AZURE DATA FACTORY - 4

  • Incremental Loads - Multiple Tables
  • Data Prep for Watermark Store
  • ForEach Activity and Real-time use
  • Lookup Activities Use in ADF
  • Linked Service Parameters, Use
  • Copy Data Activity for ETL in ADF
  • Stored Procedure Activity for ETL
  • Data Pipelines Parameters in ADF
  • Object Name Parameters, Iteractions
  • Using Self Hosted IR Engine in ADF
  • Incremental Loads with Azure Storage
  • Pipelines with Source Parameters
  • Dynamic Sources and Destinations
  • Data Preview & Dynamic Content
  • Linked Services & Datasets Creations
  • ADF Pipeline Executions, Run IDs
  • Dynamic File Formats and Connections
  • ADF Pipeline Monitoring Options
  • Trigger Monitoring and Execution Runs

Video 12: AZURE ANALYSIS SERVER CONFIG

  • Creating Azure Analysis Server
  • Azure Market Place, Resource Groups
  • Server Name, Location, Pricing Tier
  • Developer, Basic and Standard Tiers
  • Admin Options and Backup Settings
  • Associating Azure Storage Accounts
  • Server Name, Management Server Name
  • Active Directory - Universal with MFA
  • SSMS Connections : Azure Analyis Server
  • Tier Comparisions and Real-time Usage
  • Query Replica and SLA Options with AAS
  • Licensing Plans, QPUs & Memory Limits
  • D Band, B Band and S Band Selections
  • Region Availability & Supported Plans
  • Scaling: Scale-up and Scale-down
  • Pause, Resume Options - Costing Models
  • Tabular Mode Implementation in AAS
  • OLAP Cube Design and Cube Access
  • Azure Analysis with Azure Warehouse

Video 3: AZURE DATA WAREHOUSE

  • Azure Data Warehouse (ADW)
  • Key Benefits of ADWH, Creation
  • ADWH Resource Creation in SQL Server
  • Scaling Options, Compute Engine
  • ADW Connection from SSMS Tool
  • Data Warehouse Details: Name, Server
  • cDWU & DWU : Real-time Usage, Pricing
  • DWs and Scaling Options: DW Series
  • Massively Parallel Processing (MPP)
  • Sharding Benefits with MPP in DWH
  • Control Node and Compute Node
  • Data Distribution Components in ADW
  • DMS : Data Movement Service
  • Hash Functions, Distributed Tables
  • Round-robin Distributed Tables
  • Replicated Tables in Azure DWH
  • Queries Distribution, Nodes in ADW
  • Dynamic Management Views (DMV)

Video 8: AZURE DATA FACTORY - 5

  • Azure Data Lake Storage Environment
  • Azure Data Lake Gen 1 and Gen 2
  • Using ADF to get data from Data lake
  • ADF Integration with Azure Data Store
  • Scale-Out and Managed Data Transfer
  • Copy Data Performance Monitoring
  • Monitor, Activity Runs and Options
  • Data Partition Recommendations in ADL
  • Size Limitations and ADF Activities
  • Copy Activity Performance in ADF
  • ADF for Large Scale Data Migrations
  • Azure Data Lake and EDW Sources
  • Performance and Scalability Options
  • ADF Serverless Architecture with Lake
  • Data Size and Bandwidth Ccomparisons
  • ADF Copy Activity Scaling Options
  • ADF control flow, DIUs, Nodes Usage
  • Performance Tuning Steps in ADF

Video 13: COMPATABILITY & BUILTS

  • Azure Analysis Compatibility Options
  • Tabular Mode Support with TMSL, TOM
  • Partitions, Perspectives, RLS in AAS
  • Bi-Directional Relationships in AAS
  • Direct Query Mode, Azure SQL Connections
  • Azure SQL Server Connection with AAS
  • Azure SQL DWH Connection with AAS
  • DAX Formula Limitations with AAS
  • Distributed Denial of Services (DDoS)
  • Firewall Settings and Authentications
  • Data Security with Azure AS and BLOB
  • Role Based Authorization and Azure AD
  • Row Level Security with DAX Expressions
  • Object Level Security, Server Principals
  • Azure Governance and Tools For Access
  • Object Models and Scripting Tools
  • Visual Studio and VSIX Packages
  • Monitoring and Diagnostic Options

Video 4: AZURE DATA FACTORY - 1

  • Azure Data Factory (ADF) : Purpose
  • Creating ADF Resource in Azure, Use
  • Pipeline Creation and Usage Options
  • Copy Data Tool in ADF Portal, Use
  • Linked Service Creation in ADF
  • Dataset Creation, Connection Reuse
  • Staging Dataset with Azure Storage
  • ADF Pipeline Deployments, Triggers
  • Polybase Benefits for Data Staging
  • Using Azure Data Factory (ADF) Portal
  • Pipeline Creation, Resources in Portal
  • Pipeline Activities and Properties in ADF
  • Linked Service Creation and Datasets
  • ADF Pipeline Validations in Portal
  • Publish Options & Pipeline Deployment
  • Pipeline Monitoring and RUN IDs
  • Trigger Now and Monitoring Options

Video 9: AZURE DATA FACTORY - 6

  • Azure Data Factory (ADF) Management
  • Security and Audits. Monitoring Options
  • Data Format and Compressions in ADF
  • Data Movement Security & Encryptions
  • Data Movement Scenarios with ADF
  • Cloud and Hybrid Data Movement in ADF
  • RBAC Access Controls and Real-time Usage
  • Data Discovery & Classification in ADF
  • Credential Encryption, Security in ADF
  • DWH (Azure Data Warehouse) Management
  • Threat Detection, Advanced Threat Protection
  • Dynamic resource classes, ADW Queries
  • Resource Class Operations & Precedence
  • Backups and Restores with ADWH
  • Export and Import Options with ADWH
  • Security Management, Role Based Access
  • Azure Data Warehouse CheatSheet

Video 14: DATA MODELS IN AZURE AS

  • Adding Data Models in Azure Analysis Server
  • Data Sources & Data Adding. Cleanup Resources
  • Defining Model Database Administrator Roles
  • Users in Model Database Administrator Roles
  • XMLA Scripts for Role Memberships
  • CREATE, REPLACE with Query Editors
  • Using SSDT for Azure Analysis Server
  • Using Tabular Mode Templates in SSDT
  • Using Workspace Servers and Advantages
  • Relational Data Sources and Table Imports
  • Impersonation Settings, Security Options
  • Installing and Using Data Gateways
  • Enterprise Integrations, Gateway Security
  • BIM Models with Azure Analysis Services
  • OLAP DB and Cube Deployments in Azure
  • Azure Analysis Server Metrics, REST
  • Automation with Service Principals

Video 5: AZURE DATA FACTORY - 2

  • Copy Data From Azure SQL Database
  • Azure SQL Datawarehouse Sinks
  • DataSets Creation & ADF Data Pipelines
  • Activities : Move & Transform for ETL
  • Actions and View Activity Runs
  • ADF Pipeline Monitoring and Refresh
  • ADF Triggers - Manual, Automatic
  • Azure Database to Azure Data Warehouse
  • Staging Dataset with Polybase, BLOB
  • Data Integration Units [DIU] Usage
  • Azure Data Factory Runtime Engine
  • On-Premise Data Extraction and Loads
  • Self Hosted IR in Copy Data Tool
  • IR Engine Installation, Key Concepts
  • Data From Azure BLOB, Azure Warehouse
  • IR Connections & Authentication Methods
  • IR Tool Configuration and Monitoring

Video 10: AZURE DATA FACTORY - 7

  • Execute SSIS Packages in Azure
  • Azure-SSIS Integration Runtime, SSDT
  • Azure-SSIS IR Creation and Use
  • SSISDB Project Deployment Model
  • Azure Enabled SSIS Projects
  • SSIS Project Connections to ADF
  • ADF Connection Wizard From SSDT
  • Azure Storage for SSIS Packages
  • Execute SSIS packages in Azure
  • AzureDTExec Command Prompt Utility
  • AzureDTExec Runs from SSMS Tool
  • SSIS Package Migrations to ADF
  • SSIS Catalog DB with ADF
  • On-Premise SSIS Versus Azure
  • SSIS Lift and Shift with ADF
  • IR CPU & IR Memory Reports in ADF
  • Branching and Chaining in ADF

Video 15: DATA MODELS IN AZURE AS - 2

  • Creating and Using Hierarchies
  • Partitions and Query Store Operations
  • KPIs, Calculations and DAX Measures
  • Calculated Columns & Measures with DAX
  • Time Intelligence and Modelling Options
  • Using KPIs with Calcualted Measures
  • Data Modelling Advantages with Azure AS
  • Azure Virtual Network @ Analysis Serivces
  • Data Source Connections to On-Premise
  • Excel and Flat File Data Sources
  • Using Azure Anlysis Server Aliases
  • Using Azure Data Factory for AAS
  • Using Azure Data Warehouse & Models
  • Using Azure SQL Server [Logical Server]
  • Backup, Restore of Azure Analysis Server
  • OLAP DB Automations and Processing
  • Security Management and Scale-Out
LIVE ADF Online Training Course Curriculum : Brochure
 

SQL Server, SQL DBA, MSBI DWH Trainings :