Data Warehousing & Visualization In Microsoft Bi & Power Bi

Learn ETL, Data Analysis and Visualization using Microsoft Excel, Microsoft Business Intelligence tools and Power BI

What you'll learn
Understand Data Warehousing Terminology
Understand the difference between OLTP and OLAP
Install and use MS SQL Server and SQL Server Management Studio
Install and use Business Intelligence tools (SSIS and SSRS)
Understand and use ETL via SSIS
Introduced to different Flat files
Clean CSV files using Microsoft Excel
Use simple and efficient ETL that avoid many of expecting errors
USE SSRS to create different types of business reports
Practice data analysis while creating SSRS reports
Requirements
Basic knowledge of SQL
Description
Data WarehousingLearning how to extract, clean and load data into a SQL database warehouse are highly required skills for data analysis field. You will learn in this course how to use Microsoft Excel to clean your data before loading them into a Microsoft SQL Server database. You will learn how to use SQL Server Integration Services (SSIS) which is one of Microsoft Business Intelligence tools to perform ETL process. You will learn a simple technique that save you a lot of time and help to avoid many possible errors during the ETL process. You will learn also how to use SQL Server Reporting Services (SSRS) to create business reports and  data analysis with SQL queries. This course is designed to be more practical by putting your hands on real projects with diverse business scenarios to learn by practice.  Learning via practice is the best way to get knowledge stuck in your mind because it is similar to acquire experience through work.  Power BIConverting raw data to insightful diagrams and charts to make informative decisions is a crucial analytical skill in data science. You will learn in this course how to create insightful and powerful charts and perform data analysis. First, you will understand data visualization, and why data visualization. After that you will understand Power BI services and the use of each of them. After you became familiar with these services you will learn how to install and navigate in Power BI Desktop. After that, you will learn how to use the advanced functions in Power BI Editor in data preparation and cleaning. You will learn appending and merging datasets to create one dataset. After that you will learn how to turn your datasets into insightful charts using many powerful functions. You will learn how to filter your data according to your business requirements. You will learn how to create measures and calculated columns for your own data analysis. You will have an introduction to DAX language where you can learn how to create new tables and columns according to your needs. After that you will learn how to take your projects in the Cloud where you can work with other users. You will learn how use Power BI Pro interface to create, edit and share reports with others.

Overview

Section 1: Introduction

Lecture 1 Introduction to course

Lecture 2 Course contents

Lecture 3 Control the pace of a video

Lecture 4 What is Data Warehousing

Lecture 5 OLTP and OLAP

Lecture 6 What is ETL

Lecture 7 What is Data Management

Lecture 8 Course Rating

Section 2: Database Management System

Lecture 9 What is Relational Database Management System (RDBMS)

Lecture 10 Install MS SQL Server and Management Studio

Lecture 11 Introduction to SQL Server Management Studio (SSMS)

Section 3: Introduction to Microsoft Business Intelligence

Lecture 12 Introduction to Microsoft Business Intelligence

Lecture 13 Install MS Business Intelligence package

Section 4: ETL Project 1 (Companies Expenses and Profits)

Lecture 14 Data types in MS SQL Server

Lecture 15 Overview on the data file

Lecture 16 Clean the data in MS Excel

Lecture 17 Create the database in SSMS

Lecture 18 Create the SSIS project in MS Visual Studio Shell for the ETL process - Part 1

Lecture 19 Create the SSIS project in MS Visual Studio Shell for the ETL process - Part 2

Lecture 20 Create the working table in SSMS for data analysis

Lecture 21 Course Rating

Section 5: ETL Project 2 (Car Sales)

Lecture 22 Overview on the data file

Lecture 23 Clean the data file in MS Excel

Lecture 24 Create the database in SSMS

Lecture 25 Create the SSIS project in MS Visual Studio Shell for the ETL process

Lecture 26 Create the working table in SSMS for data analysis

Lecture 27 Course Rating

Section 6: ETL Project 3 (Boston Crimes)

Lecture 28 Clean the data file in MS Excel

Lecture 29 Create the warehouse database in SSMS

Lecture 30 Create the SSIS project in MS Visual Studio Shell - Part 1

Lecture 31 Check the anomalies rows

Lecture 32 Create the working table in SSMS for data analysis

Lecture 33 Homework

Lecture 34 Homework Solution

Section 7: ETL Project 4 (Movies Data)

Lecture 35 Clean the data file in MS Excel

Lecture 36 Create the warehouse database in SSMS and ETL SSIS project

Lecture 37 Create the working table in SSMS for data analysis

Section 8: ETL Project 5 (Bank Customers Complaints)

Lecture 38 Clean the data file in MS Excel

Lecture 39 Create the warehouse database in SSMS and ETL SSIS project

Lecture 40 Create the working table in SSMS for data analysis - Part 1

Lecture 41 Create the working table in SSMS for data analysis - Part 2

Section 9: ETL Homework Project

Lecture 42 Introduction to homework

Section 10: SSRS Project1 (Companies Expenses and Profits)

Lecture 43 Create SSRS Project and Report

Lecture 44 Create Datasource, Dataset, and Table

Lecture 45 Add formatting to report

Section 11: SSRS Project 2 (Movies Data)

Lecture 46 Create SSRS project and a report

Lecture 47 Use Group by in the report

Lecture 48 Use visibility and hidden functions in the report

Lecture 49 Add calculated field to the report

Lecture 50 Add parameters to the report - Part 1

Lecture 51 Add parameters to the report - Part 2

Section 12: SSRS Project 3 (Employee Reviews)

Lecture 52 Create SSRS project and report

Lecture 53 Use visibility and hidden functions in report

Lecture 54 Use parameters to filter report data

Lecture 55 Use matrix in report

Lecture 56 Create a chart to visualize data

Section 13: SSRS Project 4 (Cars Sales)

Lecture 57 Create SSRS project and report

Lecture 58 Create a chart to visualize data

Lecture 59 Use matrix in report

Section 14: SSRS Project 5 (Boston Crimes)

Lecture 60 Create SSRS project and report

Lecture 61 Use Group by in report - Part 1

Lecture 62 Use Group by in report - Part 2

Lecture 63 Use Group by in report - Part 3

Lecture 64 Create a chart to visualize data

Section 15: SSRS Homework Project

Lecture 65 Introduction to homework

Section 16: ETL Homework Project Solution

Lecture 66 Clean dataset in MS Excel

Lecture 67 Implement ETL prcess - Part 1

Lecture 68 Implement ETL prcess - Part 2

Lecture 69 Create Work Table for data analysis in SSMS

Section 17: SSRS Homework Project Solution

Lecture 70 Number of issues

Lecture 71 Number of issues per year

Lecture 72 Products have most issues

Lecture 73 States have most issues

Lecture 74 Number of issues per bank(company)

Lecture 75 Number of issues that are not timely response

Lecture 76 Issues that are not closed yet

Lecture 77 Create a report using a matrix to address the number of issues per bank and year

Lecture 78 Create a chart showing number of issues in every month

Lecture 79 Final Course Rating

Section 18: Introduction to Data Visualization

Lecture 80 Introduction to Course

Lecture 81 Introduction to Section 1

Lecture 82 What is Power BI

Lecture 83 Install Power BI Desktop

Lecture 84 Change setting in Power BI Desktop

Lecture 85 Start Power BI Desktop

Section 19: Data Cleaning and Preparation

Lecture 86 Overview on the business dataset

Lecture 87 Connect to a data source

Lecture 88 Remove N/A rows and use first row as headers names

Lecture 89 Introduction to Pivot and Unpivot operators

Lecture 90 Unpivoting columns

Lecture 91 Split columns and change data types

Lecture 92 Other ways to split columns

Lecture 93 Introduction to M Language

Lecture 94 Remove empty rows from a dataset

Lecture 95 Identify the granular data level in the dataset

Lecture 96 Remove duplicated rows in a dataset

Lecture 97 Connect to another data source

Lecture 98 Introduction to Append operator

Lecture 99 Append queries in Power BI Query Editor

Lecture 100 Introduction to data types in Power BI

Lecture 101 Set proper data types in a dataset

Lecture 102 Fix a data conversion error

Lecture 103 Introduction to Star Schema Model

Lecture 104 Create a Star Schema Model in Power BI

Lecture 105 Create the Country dimension table

Lecture 106 Create the Product and Segment dimensions tables

Lecture 107 Create the Fact Sales table

Lecture 108 Introduction to the Merge operator

Lecture 109 Merge queries in Power Query Editor

Lecture 110 Organize datasets in groups

Lecture 111 More useful functions in Power Query Editor

Lecture 112 Disable loading unused files into Power BI

Lecture 113 Project 2 file in Power BI

Section 20: Data Visualization

Lecture 114 Introduction to Data Visualization

Lecture 115 Create the first chart

Lecture 116 Filter data in charts

Lecture 117 Use top N feature in filters

Lecture 118 Formatting charts part-1

Lecture 119 Formatting charts part-2

Lecture 120 Add analytical lines to charts

Lecture 121 Fix the merging problems between queries

Lecture 122 Create a TreeMap chart

Lecture 123 Advanced color formatting in charts

Lecture 124 Add Slicer to charts

Lecture 125 Create a Map chart

Lecture 126 Measure vs Calculated column

Lecture 127 Create a measure in Power BI Desktop

Lecture 128 Create a calculated column

Lecture 129 Create a Pie chart

Lecture 130 Prepare a time series dataset

Lecture 131 Create a Line chart

Lecture 132 Drilling in a Hierarchy dimensions

Lecture 133 Change themes of reports

Lecture 134 Introduction to DAX Language

Lecture 135 Arithmetic functions in DAX

Lecture 136 DAX operators

Lecture 137 Date functions in DAX

Lecture 138 Logical functions in DAX

Lecture 139 Text concatenation functions in DAX

Section 21: Work on Projects in the Cloud with Power BI Pro

Lecture 140 Introduction to Power BI Pro

Lecture 141 Login to Power BI Pro

Lecture 142 Quick overview on Power BI Pro interface

Lecture 143 Browse and edit projects in Power BI Pro

Lecture 144 Create a Dashboard in Power BI Pro

Lecture 145 Overview on My workspace in Power BI Pro

Lecture 146 Install a Gateway

Lecture 147 Overview on Workspaces in Power BI Pro

Lecture 148 Apps in Power BI Pro

Anyone wants to learn data warehousing, data analysis, and ETL from scratch using Microsoft BI