Business Analyst

Topics To Be Covered

Business Statistics

Excel: Basics to Advanced

MySQL

Tableau

Power BI

SAS

R Basics

Python Basics

Business Statistics

Data Types, Measure Of central tendency, Measures of Dispersion

Graphical Techniques, Skewness & Kurtosis, Box Plot

Descriptive Stats

Random Variable, Probability, Probility Distribution, Normal Distribution, SND, Expected Value

Sampling Funnel, Sampling Variation, Central Limit Theorem, Confidence interval

Introduction to Hypothesis Testing

Hypothesis Testing (2 proportion test, 2 t sample t test)

Anova and Chisquare

Data Cleaning

Imputation Techniques

Scatter Diagram

Correlation Analysis

Advanced Excel

Advanced Excel –Basic

An overview of the screen, navigation and basic spreadsheet concepts

Customizing the Ribbon

Worksheets

Format Cells

Various selection techniques

Shortcuts Keys

Protecting and un-protecting worksheets

Advanced Excel -Sorting and Filtering Data

Sorting tables

Using multiple-level sorting

Using custom sorting

Filtering data for selected view (AutoFilter)

Using advanced filter options

Advanced Excel -Data Validations

Specifying a valid range of values for a cell

Specifying a list of valid values for a cell

Specifying custom validations based on formula for a cell

Advanced Excel -Text Function

Upper, Lower, Proper

Left, Mid, Right

Trim, Len, Exact

Concatenate

Advanced Excel -Function & Formula

Basic Function –Sum, Average, Max, Min, Count, Count A

Conditional Formatting

Logical functions (AND, OR, NOT)

Lookup and reference functions (VLOOKUP, HLOOKUP, MATCH, INDEX)

V-lookup with Exact Match, Approximate Match

Nested V-lookup with Exact Match

V-lookup with Tables, Dynamic Ranges

Nested V-V-lookup with Exact Match

Using V-lookup to consolidate Data from Multiple Sheets

Mathematical Functions

SumIf, CountIf, AverageIf etc

Date & Time Function

Advanced Excel -Pivot Tables

Creating Simple Pivot Tables

Basic and Advanced value field setting

Grouping Based on number and Dates

Calculated field and Calculated items

Advanced Excel –Charts & Slicers

Using Charts

Formatting Charts

Using 3D Graphs

Using Bar and Line Chart together

Using Secondary Axis in Graphs

Sharing Charts with PowerPoint / MS Word, Dynamically

Advanced Excel -Working with Templates

Designing the structure of a template

Using templates for standardization of worksheets

Advanced Excel -VBA-Macro

Introduction to VBA

What is VBA ?

What can you do with VBA ?

What can you do with VBA ?

Procedures and Function in VBA

Advanced Excel -Variable in VBA

What is Variables ?

Using Non-declared variables

Variable Data Types

Advanced Excel -Message-Box and Input-box functions

Customize Message-Box and Input-box

Reading cell values into messages

Various button groups in VBA

VBA Coding Advanced function

If and Select statement

Looping in VBA

Mail Function –send automated email

Automated report will be shown

MySQL

Introduction to Databases

Databases

Introduction to DBMS

Popular DBMS Software

Concepts of RDBMS

Tables

Tuples

Attributes

Normalization

First Normal Form

Second Normal Form

Third Normal Form

NoSQL Databases

Types of NOSQL

Comparision

SQL Commands

Types of SQL Commands

Data Definition Language

Create, Drop, Truncate, Alter and Rename Objects

Data Query Language

Select Statements

Data Manipulation Language

DCL and TCL

Grant, Revoke and transaction statements

SQL Data Types

Numeric, Date and TIme, LOB Types

DML Commands

Insert, Update and Delete Statements

DDL Commands

Create and Drop Databases

Database Constraints

Types of Constraints

Relational Integrity Constraints

Key Constraints

Domain Constraints

Referential Integrity

Types Of Constraints

Primary and Foreign Keys

Application of Indexes

Checking Constraints

Alter Tables

SQL Transactions

SQL Transactions

Examples

ACID Properties

TCL Statements

Start, Commit and Rollback Statements

Auto Commit

SavePoints

Identifier

Rollback and Release

Database Objects

Tables

Creating, Altering and dropping tables

Sequences

Auto Increments

Re-Sequencing

Views

Advantages

Creating and Dropping Views

Indexes

Types of Indexes

B-Tree and Hash Indexes

Creating and dropping Indexes

Stored Procedures and Functions

Stored Objects

Types of Stored Objects

Stored Procedures

Create, call and drop stored procedures

Using Variables

Handling Exceptions

Named Errors and Resignals

Programming

If-then-Else and Case Statements

Loops

Repeat and Leave Statements

Cursors

Operators and Functions

Joining Tables

Inner Join, Left Join, Right join

Advantages of Procedures

Database Triggers Accessing Database From R and Python

Triggers

Database Triggers

Data Definition Language (DDL) Triggers

Data Manipulation Language (DML) Triggers

CLR Triggers

Logon Triggers

Triggers v/s Stored Procedures

Accessing Database from R

Install R Packages

Configuration Information

Python Database Access

Databases Supported

Libraries

Read Operations

Insert, Update and Delete

Performing Transactions

Handling Errors

Tableau

Publishing our Workbooks in Tableau Server

Exploring publishing options using Tableau , Discussing sharing of workbooks, etc.

Connecting Tableau with Tableau Server

Overview of how to connect Tableau with Tableau server.

What is R?

R software, installation , R studio , Understanding basic interface of R .

Connecting Tableau with R

Rservepackage, using functions such as SCRIPT_REAL etc, Understanding with examples.

How to integrate Tableau with R?

Rservepackage, external connections, ports, Understanding with examples.

Basics Of R

Introduction of R

Data Types

Data Structures

Decision Making Statements

Conditional Loops

Flow Control Statements

If Statements

For loops

While Loops

Built Functions in R : Base, datasets, dplyrand ggplot

Basics Of Python

Python Basics -Webscrapping-Python custom functions -Lambda Function -Regular Expressions

Data science Life cycle -NumpyModule

Data science Life cycle -PandsModule

Data science Life cycle -MatplolibModule

Power BI

Introduction

Introduction to PowerBIPreview

Download the Training Data Files

Introduction to Signing Up for Power

Signing up for PowerBIPreview

Load Data into the Power BI Service Preview

Practical Activity

Power BI Query Editor

Introduction to Power BI Query Editor

Basic Transformations -Part 1

Basic Transformations -Part 2

Aggregating Data