Master Bi, Ssis, Ssrs, Ssas & Mdx - Zero To Hero (2024)

Mastering Data Engineering: From SSIS Foundations to Advanced MSBI Techniques

What you'll learn

Data Integration Mastery: Master ETL tasks with SSIS for advanced data migration and workflow automation

Dynamic SSIS Configurations: Learn dynamic package configurations, event handling, and inter-package communication

Efficient Data Handling: Work with various data sources, transform, sort, merge, and join data effectively

MSBI Project Proficiency: Design databases, create ETL solutions, cubes with SSAS, and reports with SSRS

Reporting Skills: Develop dynamic reports using SSRS, learn matrix reporting, sorting, and expressions

Analytical Depth with SSAS: Analyze data using SSAS, mastering MDX queries, KPIs, and calculated members

Troubleshooting SSIS Packages: Gain skills in troubleshooting, debugging, and deploying SSIS solutions

SQL Server Expertise: Enhance your T-SQL skills for effective data interaction, extraction, and analysis

Multidimension expressions(MDX): Enhance your MDX skills for effective data interaction, extraction, and analysis

Data Exploration & Reporting: Explore data sources, extract and load modified data, and generate insightful reports

Requirements

No Prior SQL Server Experience Needed: Jump in without any previous knowledge of SQL Server. We'll guide you through installing the full Developer edition for free.

Basic SQL Knowledge Beneficial: While not mandatory, a foundational understanding of SQL will enhance your learning experience.

T-SQL Experience: Previous experience with T-SQL is helpful but not required for success in this course.

Computer and OS Requirements: A Windows computer is necessary. Mac users can participate using dual-boot into Windows or Parallel Desktop with Windows

Internet and Software: Ensure you have a reliable internet connection. The course includes guidance on downloading some free software

No BI or MSBI Knowledge Required: Beginners to BI or MSBI are welcome; this course is designed to start from the basics.

Description

If mastering MSBI is your goal, you've arrived at the definitive tutorial, I Mean it.Embark on a comprehensive journey into the realm of Microsoft SQL Server Business Intelligence (MSBI) with this meticulously designed course, tailored to transform beginners into adept professionals in data integration, analysis, and reporting. This course stands as a beacon for those aspiring to master the intricacies of MSBI, with a special emphasis on SQL Server Integration Services (SSIS), Analysis Services (SSAS), and Reporting Services (SSRS), ensuring a holistic understanding of business intelligence.Unlock the Power of MSBI: Delve into the core of MSBI through hands-on labs, each crafted to provide you with a practical understanding of critical concepts and techniques. From setting up your environment to deploying sophisticated BI solutions, this course covers every aspect needed to excel in the field.Master SSIS with Ease: Gain proficiency in ETL processes utilizing SSIS. Learn to adeptly manage data flow, control flow, error handling, and beyond. Master advanced functionalities including conditional splits, data conversion, and the seamless deployment of packages, equipping you with the skills to streamline data integration processes.Advance with SSAS: Explore the depths of SSAS, from crafting multidimensional cubes to understanding the nuances of time series and hierarchical dimensions. Unravel the complexities of KPIs, aggregations, and data quality management, enabling you to construct robust analytical models and enhance decision-making processes.Elevate Reporting with SSRS: Transition into an SSRS expert, capable of designing dynamic and interactive reports. This course takes you through the foundational elements of report creation to the intricacies of implementing matrix and tabular reports, along with advanced visualization techniques such as bar charts, gauges, and indicators.Students will learn how to :SSIS - SQL Server· ETL-Dataflow-Introduction· Conditional Split and Data Conversion· Setting Pipeline Dynamic - Loops – Debugging· Production Server Deployment Types· Different Type of Joins, Sort and Merge· Pivot Unpivot and Aggregate· Schema Design and Database Relationship Diagram· SCD Unicode and Lookup Concepts with Project· Transactional Grouping and Checkpoints Logging· Data Quality Check Using Profiling Component· Instrumentation at Production Server – Debugging· SQL Execute Task - Store Procedure Creation· Data Quality Services and DQS Component· CDC (Change Data Capture)- Enabling CDC - Bulk Load - Incremental Load· Script Component-Data Transformation· Buffer Size Optimization and Component Types· Sequential and Parallel Processing (Multithreading)· Pages Indexes and Reducing Page Splits for improved ETL LoadSSAS – SQL Server· Setting Up Cube from Scratch· Time Intelligence for SSAS and External BI tools Connection· Key Performance Indicator Setup (KPIs) and Dimension Relationships· Partitions and Storage Modes (MOLAP-ROLAP)· Perspectives and Reginal Language Setting· Measure Formula Types - Processing Cube in Production· Aggregations in Cube· Multidimension expressions (MDX) Query Language· Tabular Cube Setup and Difference between Tabular and MultidimensionSSRS – SQL Server· Introduction to Report Building and SSRS· Drilldown Report Building· Deploying Reports in Production· Creating Simple Dashboard in SSRS

Overview

Section 1: Introduction and Welcome to the Course

Lecture 1 Course Intro

Lecture 2 1.Introduction to Business Intelligence

Section 2: System Setup-Software Installation

Lecture 3 System Setup-Software Installation

Lecture 4 2.SQL server and SSMS

Lecture 5 3.Installing Visual Studio and SSDT tools

Lecture 6 4.DQS (Data Quality Services) - Installation and configuration

Lecture 7 5.SSRS-Install SQL Server Reporting Services

Lecture 8 6.Browse GitHub for Project Files

Section 3: (SSIS-SQL Server) ETL-Dataflow Introduction

Lecture 9 3.(SSIS-SQL Server) ETL-Dataflow-Introduction

Section 4: (SSIS-SQL Server) Conditional Split and Data Conversion

Lecture 10 1.Conditional Split and Data Conversion

Section 5: (SSIS-SQL Server) Setting Pipeline Dynamic - Loops - Debugging

Lecture 11 5.1.For Each Loop

Lecture 12 5.2.Variable Creation - Control Flow

Lecture 13 5.3.Debugging

Section 6: (SSIS-SQL Server) Parameters

Lecture 14 6.1.Parameters

Section 7: (SSIS-SQL Server) Production Server Deployment Types

Lecture 15 7.1.Deployment Types

Lecture 16 7.2.Project Deployment

Lecture 17 7.3.Package Execution

Lecture 18 7.4.SSIS Package Automation

Lecture 19 7.5.Global Parameters

Lecture 20 7.6.Package Deployment

Section 8: (SSIS-SQL Server) Different Type of Joins, Sort and Merge

Lecture 21 8.1 Sort And Merge

Lecture 22 8.2.InnerJoin

Lecture 23 8.3.LeftOuter Joint

Lecture 24 8.4.FullOuterJoin

Section 9: (SSIS-SQL Server) Pivot Unpivot and Aggregate

Lecture 25 9.1.Pivot and Aggregation

Lecture 26 9.2.Unpivot

Section 10: (SSIS-SQL Server) Schema Design and Database Relationship Diagram

Lecture 27 10.1.Different Schemas, Fact Table, Dimension Table Introduction.

Lecture 28 10.2.Tabel Creation and Relationship Diagram

Lecture 29 10.3.SSIS Project Setup

Section 11: (SSIS-SQL Server) SCD Unicode and Lookup Concepts with Project

Lecture 30 11.1.SCD Introduction

Lecture 31 11.2.Unicode and Non-Unicode Introduction

Lecture 32 11.3.SCD Type 1

Lecture 33 11.4.SCD Type 2 (Part-1) - Without Audit Trail

Lecture 34 11.4.1.SCD Type 2 (Part-2) - With Audit Trail

Lecture 35 11.5.Lookup - Fact Table Loading into the SQL Server

Section 12: (SSIS-SQL Server) Transactional Grouping and Checkpoints Logging

Lecture 36 12.1.Transaction Options (Grouping of Activities)

Lecture 37 12.2.Logging your runs (Checkpoints)

Section 13: (SSIS-SQL Server) Data Quality Check Using Profiling Component

Lecture 38 13.1.Data Profiling Task

Section 14: (SSIS-SQL Server) Instrumentation at Production Server - Debugging

Lecture 39 14.1.nstrumentation at Production Server

Section 15: (SSIS-SQL Server) SQL Execute Task - Store Procedure Creation

Lecture 40 15.1.SqlExecute - Direct Input

Lecture 41 15.2.SQLExecute-Directinput-Parametrizing-ProjectDeployment

Lecture 42 15.3.SQlExecuteTask-Direct input-Store Procedure

Lecture 43 15.4.SqlExecute-Variable-FileConnection

Section 16: (SSIS-SQL Server) Data Quality Services and DQS Component

Lecture 44 16.1.DQS (Data Quality Services) - Installation and configuration

Lecture 45 16.2.Data Quality Concepts and Process

Lecture 46 16.3.DQS Client UI and Project

Lecture 47 16.4.DQS Component in SSIS Project - with error

Section 17: (SSIS-SQL Server) CDC (Change Data Capture)-Enabling CDC on SQL Server Database

Lecture 48 17.1.Enable CDC on Database and Table

Lecture 49 17.2.CDC introduction and Concepts

Lecture 50 17.3.CDC Bulk Load

Lecture 51 17.4.CDC (Change Data Capture) - Incremental Load

Section 18: (SSIS-SQL Server) Script Component-Data Transformation

Lecture 52 18.1.Script Component-Transfrom Data using Scripting

Section 19: (SSIS-SQL Server) Buffer Size Optimization and Component Types

Lecture 53 19.1.Component Types (Blocking - Not Blocking - Semi Blocking)

Lecture 54 19.2.Buffer Size Basic Concepts

Lecture 55 19.3.Buffer Size Calculation and Optimization

Section 20: (SSIS-SQL Server) Sequential and Parallel Processing (Multithreading)

Lecture 56 20.1.Introduction to Sequential and Parallel Processing

Lecture 57 20.2.Parallel Processing at Component Level

Lecture 58 20.3.Parallel Processing using Select Query

Lecture 59 20.4.Parallel Processing-Multithreading at Control Flow

Lecture 60 20.5.Partitioning Table SQL Server for Parallel Processing

Section 21: (SSIS-SQL Server) Pages Indexes and Reducing Page Splits for improved ETL Load

Lecture 61 21.1.(SQL Pages) SQL Server internal Data Storage Structure

Lecture 62 21.2.Indexes for query performance

Lecture 63 21.3.Page Splits Monitor for Windows

Lecture 64 21.4.Improving ETL Load by Dropping Indexes

Section 22: (SSAS-SQL Server) Setting Up Cube from Scratch

Lecture 65 22.1.SSAS Introduction

Lecture 66 22.2.Data Sources and Data Views

Lecture 67 22.3.Defining Cube and Introduction

Lecture 68 22.4.Working with Cube

Lecture 69 22.5.Browser Tab UI-Named Query

Lecture 70 22.6.SSAS-SQL selection Decision for BI

Section 23: (SSAS-SQL Server) Time Intelligence for SSAS and External BI tools Connection

Lecture 71 23.1.Creating Time intelligent Query inside the Cube

Lecture 72 23.2.Connecting Cube with External Business Intelligence Tools

Section 24: (SSAS-SQL Server) Key Performance Indicator Setup (KPIs) and Dimension Relations

Lecture 73 24.1.Key Performance Indicator Setup (KPIs)

Lecture 74 24.2.Dimention Relationship (Reference ) inside Cube

Section 25: (SSAS-SQL Server) Partitions and Storage Modes (MOLAP-ROLAP)

Lecture 75 25.1. SSRS Partition Concepts

Lecture 76 25.2. Partitioning the Data in Cube (Hands-ON)

Lecture 77 25.3.Setting up Different Storage Modes (MOLAP-ROLAP-HOLAP)

Section 26: (SSAS-SQL Server) Perspectives and Reginal Language Creation

Lecture 78 26.1.Prespectives in Cube

Lecture 79 26.2.Regional Language in Cube(Translations)

Section 27: (SSAS-SQL Server) Measure Formula Types - Processing Cube in Production

Lecture 80 27.1.Measure Formula Types(Addition, Semi-Addition, Non-Addition)

Lecture 81 27.2.Processing SSAS Cube in Production Environment (Automation)

Section 28: (SSAS-SQL Server) Aggregations in Cube

Lecture 82 28.1 Aggregations Concepts

Lecture 83 28.2.Storage Mode Concepts(MOLAP-ROLAP-HOLAP)

Lecture 84 28.3.Aggregations -Understanding SQL Profiler (Pinpoint the Cube Performance)

Lecture 85 28.4. Create Default Aggregations

Lecture 86 28.5.Aggregation Types-Options (Error Resolving While Creating Aggregations)

Lecture 87 28.6.Aggregations Design Using Query Log

Section 29: (SSAS-SQL Server) Multidimension expressions (MDX) Query Language

Lecture 88 29.1.MDX Concepts

Lecture 89 29.2.MDX Datasets Used

Lecture 90 29.3.MDX Basics

Lecture 91 29.4.(MDX) Hierarchy and Siblings Navigation Functions

Lecture 92 29.5.MDX Level Navigation Functions

Lecture 93 29.6.MDX Set Based Functions

Lecture 94 29.7.MDX Axis and Position Functions

Lecture 95 29.8.MDX Time-Intelligence Functions

Lecture 96 29.9.MDX Statistical Functions

Lecture 97 29.10.MDX String Functions

Section 30: (SSAS-SQL Server) Tabular Cube Setup and Difference between Tabular and MultiDim

Lecture 98 30.1.Key Concept Query Language (MDX, DAX) , Tablular and Multidimension Models

Lecture 99 30.2.Introduction to Tabular (Tabular vs Multidimension)

Lecture 100 30.3.Basics of Creating and Deploy Tabular Model (Multidimension vs Tabular)

Lecture 101 30.4.Creating KPI in Tabular Model

Lecture 102 30.5.Creating Perspective in Tabular Model

Section 31: (SSRS-SQL Server) Introduction to Report Building and SSRS

Lecture 103 31.1.SSRS introduction

Lecture 104 31.2.Report Setup SSRS

Lecture 105 31.3.Report Basic Structure and Report Data with MDX Query Language

Lecture 106 31.4.Report Parameters (MDX Query Language)

Lecture 107 31.5.Report Expression and Conditional Formating using Expressions

Lecture 108 31.6.Sorting and Intractive Sorting

Lecture 109 31.7.Manual Report Builder with SQL Server

Lecture 110 31.8.Report Parameters (SQL Query Language)

Lecture 111 Drill Down Report Building

Section 32: (SSRS-SQL Server)-Deploying Reports in Production Environment

Lecture 112 32.1.SSRS-Creating Report Server Database

Lecture 113 32.2.SSRS-Deploy Reports in Production Enviornment

Section 33: (SSRS-SQL Server) Creating Simple Dashboard in SSRS

Lecture 114 33.1.SSRS-Simple DashBoard - Creating Charts

Lecture 115 33.2.SSRS-Simple Dashboard - Guage Visual

Lecture 116 33.3.SSRS-Simple Dashboard - Inserting Indicator

Section 34: Error Encounters

Lecture 117 1.Error-SQL Analysis Server-Multidimension mode

Lecture 118 2.Error- Microsoft Windows (11,10) Account Login problem in creating Cube for SS

Lecture 119 3.windows microsoft Account Login for SSAS Cube

Lecture 120 4.DQS-InvalidProgramException Common Language Runtime detected an invalid progr

Lecture 121 5.CDC-Error Could not load file or assembly 'Microsoft.SqlServer.DtsMsg

Lecture 122 6.Error Resolved LCID (8192)

Beginners to SSIS & DTS Developers: Ideal for novices wanting to learn SSIS or developers experienced in DTS/SSIS package development.,SSIS Curious Learners: If you're curious about SSIS, its automation capabilities, and gaining visibility into data processes, this course is for you,Certification Aspirants: Those aiming for certification will find this course beneficial towards their preparation,MSBI Enthusiasts: Perfect for developers eager to dive into Microsoft SQL Server Business Intelligence, including SSIS, SSAS, and SSRS,ETL and Data Warehousing Beginners: If you're starting your journey in ETL processes with SSIS, cube creation with SSAS, or reporting with SSRS, this course is tailored for you.,Data Professionals at the Start: Beginner Data Analysts, Data Warehouse Engineers, Data Scientists, and Data Engineers will gain foundational skills and knowledge in data handling and analysis,Emerging Data Engineers: Specifically designed for Data Engineers beginning their journey into the field of data engineering, providing essential skills and insights into data integration, management, and analytics