The Business Intelligence Analyst Course 2020

Part 1 Introduction
What Does the Course Cover
Download All Resources

Intro to Data and Data Science – The Different Data Science Fields
Why Are There So Many Business and Data Science Buzzwords
Analysis vs Analytics
Intro to Business Analytics, Data Analytics, and Data Science
Adding Business Intelligence (BI), Machine Learning (ML), and AI to the Picture
An Overview of our Data Science Infographic

Intro to Data and Data Science – The Relationship between Different Fields
When are Traditional data, Big Data, BI, Traditional Data Science and ML applied

Intro to Data and Data Science – What is the Purpose of each Data Science Field
Why do we Need each of these Disciplines

Intro to Data and Data Science – Common Data Science Techniques
Traditional Data Techniques
Traditional Data Real-life Examples
Big Data Techniques
Big Data Real-life Examples
Business Intelligence (BI) Techniques
Business Intelligence (BI) Real-life Examples
Traditional Methods Techniques
Traditional Methods Real-life Examples
Machine Learning (ML) Techniques
Machine Learning (ML) Types of Machine Learning
Machine Learning (ML) Real-life Examples

Intro to Data and Data Science – Common Data Science Tools
Programming Languages & Software Employed in Data Science – All the Tools Needed

Intro to Data and Data Science – Data Science Career Paths
Data Science Job Positions What do they Involve and What to Look out for

Intro to Data and Data Science – Dispelling Common Misconceptions
Dispelling common Misconceptions

Part 2 Statistics – Population and Sample
Population vs sample
Population and Sample

Statistics – Descriptive Statistics
Types of Data
Levels of Measurement
Categorical Variables – Visualization Techniques
Categorical variables. Visualization Techniques
Categorical Variables Exercise
Numerical Variables – Frequency Distribution Table
Numerical variables. Using a frequency distribution table
Numerical Variables Exercise
The Histogram
Histogram Exercise
Cross Table and Scatter Plot
Cross Tables and Scatter Plots
Cross Tables and Scatter Plots Exercise
Mean, median and mode
Mean, Median and Mode Exercise
Skewness
Skewness Exercise
Variance
Variance Exercise
Standard Deviation and Coefficient of Variation
Standard deviation
Standard Deviation and Coefficient of Variation Exercise
Covariance
Covariance Exercise
Correlation Coefficient
Correlation Coefficient Exercise

Statistics – Practical Example Descriptive Statistics
Practical Example
Practical Example Exercise

Statistics – Inferential Statistics Fundamentals
Introduction
What is a Distribution
The Normal Distribution
The Standard Normal Distribution
The Standard Normal Distribution Exercise
Central Limit Theorem
Standard error
Estimators and Estimates

Statistics – Inferential Statistics Confidence Intervals
What are Confidence Intervals
Confidence Intervals; Population Variance Known; z-score
Confidence Intervals; Population Variance Known; z-score Exercise
Confidence interval clarifications
Student’s T Distribution
Confidence Intervals; Population Variance Unknown; t-score
Confidence Intervals; Population Variance Unknown; t-score Exercise
Margin of Error
Confidence intervals. Two means. Dependent samples
Confidence intervals. Two means. Dependent samples Exercise
Confidence intervals. Two means. Independent samples (Part 1)
Confidence intervals. Two means. Independent samples (Part 1) Exercise
Confidence intervals. Two means. Independent samples (Part 2)
Confidence intervals. Two means. Independent samples (Part 2) Exercise
Confidence intervals. Two means. Independent samples (Part 3)

Statistics – Practical Example Inferential Statistics
Practical Example Inferential Statistics
Practical Example Inferential Statistics Exercise

Statistics – Hypothesis Testing
The Null vs Alternative Hypothesis
Further Reading on Null and Alternative Hypothesis
The Null vs Alternative Hypothesis
Rejection Region and Significance Level
Type I Error and Type II Error
Test for the Mean. Population Variance Known
Test for the Mean. Population Variance Known Exercise
p-value
Test for the Mean. Population Variance Unknown
Test for the Mean. Population Variance Unknown Exercise
Test for the Mean. Dependent Samples
Test for the Mean. Dependent Samples Exercise
Test for the mean. Independent samples (Part 1)
Test for the mean. Independent samples (Part 1). Exercise
Test for the mean. Independent samples (Part 2)

Statistics – Practical Example Hypothesis Testing
Practical Example Hypothesis Testing
Practical Example Hypothesis Testing Exercise

Part 3 Relational Database Theory & Introduction to SQL
Why use SQL
Why use MySQL
Introducing Databases
Relational Database Fundamentals
Comparing Databases and Spreadsheets
Important Database Terminology
The Concept of Relational Schemas Primary Key
The Concept of Relational Schemas Foreign Key
The Concept of Relational Schemas Unique Key and Null Values
The Concept of Relational Schemas Unique Key
The Concept of Relational Schemas Relationships Between Tables

SQL – Install and get to know MySQL
Installing MySQL Workbench and Server
Installing Visual C
Installing MySQL on macOS and Unix systems
The Client-Server Model
Linking GUI with the MySQL Server
Read me!!!
Creating a New User and a New Connection to it
Familiarize Yourself with the MySQL Interface

SQL – Best SQL Practices
Coding Tips and Best Practices – I
Coding Tips and Best Practices – II

SQL – Loading the ’employees’ Database
Loading the ’employees’ Database

SQL – Practical Application of the SQL SELECT Statement
Using SELECT – FROM
Using SELECT – FROM – Exercise
Using SELECT – FROM – Solution
Using WHERE
Using WHERE – Exercise
Using WHERE – Solution
Using AND
Using AND – Exercise
Using AND – Solution
Using OR
Using OR – Exercise
Using OR – Solution
Operator Precedence and Logical Order
Operator Precedence and Logical Order – Exercise
Operator Precedence and Logical Order – Solution
Using IN – NOT IN
Using IN – NOT IN – Exercise 1
Using IN – NOT IN – Solution 1
Using IN – NOT IN – Exercise 2
Using IN – NOT IN – Solution 2
Using LIKE – NOT LIKE
Using LIKE – NOT LIKE – Exercise
Using LIKE – NOT LIKE – Solution
Using Wildcard Characters
Using Wildcard characters – Exercise
Using Wildcard characters – Solution
Using BETWEEN – AND
Using BETWEEN – AND – Exercise
Using BETWEEN – AND – Solution
Using IS NOT NULL – IS NULL
Using IS NOT NULL – IS NULL – Exercise
Using IS NOT NULL – IS NULL – Solution
Using Other Comparison Operators
Using Other Comparison Operators – Exercise
Using Other Comparison Operators – Solution
Using SELECT DISTINCT
Using SELECT DISTINCT – Exercise
Using SELECT DISTINCT – Solution
Getting to Know Aggregate Functions
Getting to Know Aggregate Functions – Exercise
Getting to Know Aggregate Functions – Solution
Using ORDER BY
Using ORDER BY – Exercise
Using ORDER BY – Solution
Using GROUP BY
Using Aliases (AS)
Using Aliases (AS) – Exercise
Using Aliases (AS) – Solution
Using HAVING
Using HAVING – Exercise
Using HAVING – Solution
Using WHERE vs HAVING – Part I
Using WHERE vs HAVING – Part II
Using WHERE vs HAVING – Part II – Exercise
Using WHERE vs HAVING – Part II – Solution
Using LIMIT
Using LIMIT – Exercise
Using LIMIT – Solution

SQL – Expanding on MySQL Aggregate Functions
Applying COUNT()
Applying COUNT() – Exercise
Applying COUNT() – Solution
Applying SUM()
Applying SUM() – Exercise
Applying SUM() – Solution
MIN() and MAX()
MIN() and MAX() – Exercise
MIN() and MAX() – Solution
Applying AVG()
Applying AVG() – Exercise
Applying AVG() – Solution
Rounding Numbers with ROUND()
Rounding Numbers with ROUND() – Exercise
Rounding Numbers with ROUND() – Solution

SQL – SQL JOINs
What are JOINs
What are JOINs – Exercise 1
What are JOINs – Exercise 2
The Functionality of INNER JOIN – Part I
The Functionality of INNER JOIN – Part II
The Functionality of INNER JOIN – PART II – Exercise
The Functionality of INNER JOIN – PART II – Solution
Extra Info on Using Joins
Duplicate Rows
The Functionality of LEFT JOIN – Part I
The Functionality of LEFT JOIN – Part II
The Functionality of LEFT JOIN – Part II – Exercise
The Functionality of LEFT JOIN – Part II – Solution
The Functionality of RIGHT JOIN
Differences between the New and the Old Join Syntax
Differences between the New and the Old Join Syntax – Exercise
Differences between the New and the Old Join Syntax – Solution
Using JOIN and WHERE Together
Important – Prevent Error Code 1055!
Using JOIN and WHERE Together – Exercise
Using JOIN and WHERE Together – Solution
The Functionality of CROSS JOIN
The Functionality of CROSS JOIN – Exercise 1
The Functionality of CROSS JOIN – Solution 1
The Functionality of CROSS JOIN – Exercise 2
The Functionality of CROSS JOIN – Solution 2
Combining Aggregate Functions with Joins
JOIN More than Two Tables
JOIN More than Two Tables – Exercise
JOIN More than Two Tables – Solution
Top Tips for Joins
Top Tips for Joins – Exercise
Top Tips for Joins – Solution
The Differences Between UNION and UNION ALL
The Differences Between UNION and UNION ALL – Exercise
The Differences Between UNION and UNION ALL – Solution

SQL – SQL Subqueries
SQL Subqueries with IN Embedded Inside WHERE
SQL Subqueries with IN Embedded Inside WHERE – Exercise
SQL Subqueries with IN Embedded Inside WHERE – Solution
SQL Subqueries with EXISTS-NOT EXISTS Embedded Inside WHERE
SQL Subqueries with EXISTS-NOT EXISTS Embedded Inside WHERE – Exercise
SQL Subqueries with EXISTS-NOT EXISTS Embedded Inside WHERE – Solution
SQL Subqueries Nested in SELECT and FROM
SQL Subqueries Embedded in SELECT and FROM – Exercise 1
SQL Subqueries Embedded in SELECT and FROM – Exercise 2
SQL Subqueries Nested in SELECT and FROM – Solution 2

SQL – Stored Routines
Defining Stored Routines
Create Stored Procedures with MySQL Syntax
An Example of Stored Procedures Part I
An Example of Stored Procedures Part II
An Example of Stored Procedures Part II – Exercise
An Example of Stored Procedures Part II – Solution
Creating a Procedure in MySQL Another Way
Create Stored Procedures with an Input Parameter
Create Stored Procedures with an Output Parameter
Create Stored Procedures with an Output Parameter – Exercise
Stored Procedures with an Output Parameter – Solution
SQL Variables
SQL Variables – Exercise
SQL Variables – Solution
The Benefit of User-Defined Functions in MySQL
Error Code 1418.
The Benefit of User-Defined Functions in MySQL – Exercise
The Benefit of User-Defined Functions in MySQL – Solution
Concluding Stored Routines

SQL – The CASE Statement
The SQL CASE Statement
The SQL CASE Statement – Exercise 1
THE SQL CASE Statement – Solution 1
THE SQL CASE Statement – Exercise 2
THE SQL CASE Statement – Solution 2
THE SQL CASE Statement – Exercise 3
THE SQL CASE Statement – Solution 3

Part 4 Introduction to Tableau
Why Use Tableau Make Your Data Make an Impact
Let’s Download Tableau Public
Connecting Data in Tableau
Exploring Tableau’s Interface
Let’s Create our first Chart in Tableau!

Tableau – Tableau functionalities
Duplicating a Sheet
Creating a Table
Creating Custom Fields
Creating a Custom Field and Adding Calculations to a Table
Adding Totals and Subtotals
Adding a Custom Calculation
Inserting a Filter
Working with Joins in Tableau

Tableau – The Tableau Exercise
Introduction to the Exercise
Let’s Create a Dashboard – Visualizing the Three Charts We Want to Create
Using Joins in Tableau
Performing a Numbers Check – Attempt #1
Blending Data in Tableau
Performing a Numbers Check – Attempt #2
First Chart
Second Chart
Third Chart
Creating and Formatting a Dashboard
Adding Interactive Filters for Improved Analysis
Interactive Filters – fix

Part 5 Combining SQL and Tableau – Introduction
Introduction to Software Integration
Combining SQL and Tableau
Loading the Database

Combining SQL and Tableau – Problem 1
Problem 1 Task
Problem 1 Task – Text
Important clarification!
Problem 1 Solution in SQL
Problem 1 Solution in SQL – Code
Exporting Your Output from SQL and Loading it in Tableau
Chart 1 Visualizing the Solution in Tableau – Part I
Chart 1 Visualizing the Solution in Tableau – Part II

Combining SQL and Tableau – Problem 2
Problem 2 Task
Problem 2 Task – Text
Problem 2 Solution in SQL
Problem 2 Solution in SQL – Code
Chart 2 Visualizing the Solution in Tableau

Combining SQL and Tableau – Problem 3
Problem 3 Task
Problem 3 Task – Text
Problem 3 Solution in SQL
Problem 3 Solution in SQL – Code
Chart 3 Visualizing the Solution in Tableau

Combining SQL and Tableau – Problem 4
Problem 4 Task
Problem 4 Task – Text
Problem 4 Solution in SQL
Problem 4 Solution in SQL – Code
Chart 4 Visualizing the Solution in Tableau

Combining SQL and Tableau – Problem 5
Problem 5 Organizing Charts 1-4 into a Beautiful Dashboard

Part 6 Introduction to Programming with Python
A 5-minute explanation of Programming
Why use Python
Why use Jupyter
How to Install Python and Jupyter
Understanding Jupyter’s Interface – Dashboard
Understanding Jupyter’s Interface – Prerequisites for Coding
Understanding Jupyter’s Interface
Python 2 vs Python 3

Python – Python Variables and Data Types
Python Variables
Understanding Numbers and Boolean Values
Strings

Python – Python Syntax Fundamentals
The Arithmetic Operators of Python
Using Arithmetic Operators in Python
What is the Double Equality Sign
How to Reassign Values
How to Add Comments
Understanding Line Continuation
How to Index Elements
How to Structure Your Code with Indentation

Python – Other Python Operators
Python’s Comparison Operators
Python’s Logical and Identity Operators

Python – Conditional Statements
Getting to know the IF Statement
Adding an ELSE statement
Else if, for Brief – ELIF
An Additional Explanation of Boolean Values

Python – Functions
How to Define a Function in Python
How to Create a Function with a Parameter
Define a Function in Another Way
How to use a Function within a Function
Use Conditional Statements and Functions Together
How to Create Functions Which Contain a Few Arguments
Built-In Functions in Python Worth Knowing
Python – Functions

Python – Python Sequences
Introduction to Lists
Using Methods in Python
What is List Slicing
Working with Tuples
Python Dictionaries

Python – Using Iterations
Using For Loops
For Loops
Using While Loops and Incrementing
Use the range() Function to Create Lists
Combine Conditional Statements and Loops
All In – Conditional Statements, Functions, and Loops
How to Iterate over Dictionaries

Python – Advanced Python tools
Introduction to Object Oriented Programming (OOP)
Using Modules and Packages
What is the Standard Library
How to Import Modules in Python

Part 7 Integration – Software Integration
Getting Started with Data, Servers, Clients, Requests, and Responses
Getting Started with Data Connectivity, APIs, and Endpoints
Become Better Acquainted with APIs
Communication through Text Files
What is Software Integration and How is it Applied

Integration – What is contained in this Course
Solving a Business Exercise with Python, SQL, and Tableau
Presenting the Task Absenteeism at Work
Presenting the Data Set

Integration – Data Preprocessing Step by Step
How is the Content in the Next Sections Organized
How to Import the Data Set in Python
Exploring the Data Set
Programming vs the Rest of the World
A Brief Summary of Regression Analysis
The Approach we will Take to Solve this Exercise
Dropping Variables We Don’t Need
EXERCISE – Dropping Variables We Don’t Need
A Deeper Look at the ‘Reasons for Absence’ Column
Splitting a Variable into Multiple Dummy Variables
EXERCISE – Splitting a Variable into Multiple Dummy Variables
How to Drop a Dummy Variable from the Data Set
A Statistical Perspective on Dummy Variables
Categorizing the Various Reasons for Absence
Concatenation in Python
EXERCISE – Concatenation in Python
How to Reorder Columns in a DataFrame in Python
EXERCISE – How to Reorder Columns in a DataFrame in Python
Using Checkpoints to Ease Your Work in Jupyter
EXERCISE – Using Checkpoints to Ease Your Work in Jupyter
Analyzing the Date Column
Retrieving the Month Value From the Date Column
Adding the Day of the Week Column
EXERCISE – Dropping Columns
Analysis of the Next 5 Columns in DF
Dealing with More Numerical Features which may Behave like Categorical Ones
A Final Note on this Section

Integration – Integrating Python and SQL
How to Use the ‘absenteeism module’ in Python – Part I
How to Use the ‘absenteeism module’ in Python – Part II
Creating the ‘predicted outputs’ Database in MySQL
Importing ‘pymysql’ in Python
Creating a Connection and Cursor
EXERCISE – Creating ‘df new obs’
Creating the ‘predicted outputs’ Table in MySQL
Executing and SQL SELECT Statement from Python
Sending Data from Jupyter to Workbench – Part I
Sending Data from Jupyter to Workbench – Part II
Sending Data from Jupyter to Workbench – Part III

Integration – Using Tableau to Analyze the Predicted Outputs
EXERCISE – Age vs Probability
Using Tableau to Analyze Age vs Probability
EXERCISE – Reasons vs Probability
Using Tableau to Analyze Reasons vs Probability
EXERCISE – Transportation Expense vs Probability
Using Tableau to Analyze Transportation Expense vs Probability
Completing 100%