Data Analyst

Topics To Be Covered

Business Statistics

Excel: Basics to Advanced

MySQL

Tableau

Power BI

SAS

R Basics

Python Basics

Business Statistics

Descriptive Statistics

Data Types, Measure Of central tendency, Measures of Dispersion

Graphical Techniques, Skewness & Kurtosis, Box Plot

Probability and Normal Distribution

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

Inferential Statistics

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 and Insights

Data Cleaning(Invalid cells,Blanks,Outliers,Null values)

Imputation Techniques(Mean and Median)

Scatter Diagram

Correlation Analysis

Advanced Excel

Intorduction to Excel:Quantum of Excel and Basics

Workbook,Types of workbooks and their uses(XLSX,XLS,CSV,XLSM and XLSB)

Common uses of Excel

Cell,Row,Column,Range/Array,Name box

Formatting of cells(Wrap Text,Number,Text,Cell formatting ,commenting,etc)

Ribbon,Formula bar,Status bar

Basic operators(+,-,/,*,%,>,<,>=,<=,( ),{ },[ ],&,' ', "" "",!)

Intorduction to Functions:Commonly used Excel Functions

What is syntax,arguments(Optional,Mandatory)Navigations using keyboard,shortcuts

Sum,Average,Max,Min,Product

CountBlank,CountA,CountIF,If,Now,Today

Cut,Copy,Paste,Paste Special

Anchoring data:Referencing,Named ranges and its uses

Absolute,Relative,Mixed referencing

Name Manager,Named ranges,Creating Tables

Create functions using named ranges AND/OR referencing

Referring data from different tables:Various types of Lookup,Nested IF

Lookup,Vlookup,Nested Vlookup,Hlookup,Index,Index with Match function

If,If with combination of AND/OR(multiple ways to get the output),IFERROR

Referring data from different tables:Advanced functions

RANK,RAND,RANDBETWEEN,INDIRECT with ADDRESS & MATCH,OFFSET

Data Handling:Data cleaning,Data type identification,Data restrictions

LEN,LEFT,RIGHT,MID,CONCATENATE,CONCAT,FIND,SUBSTITUTE,TEXT,TRIM

SECOND,MINUTE,HOUR,DAY,WEEK,MONTH,QUARTER,YEAR,WORKDAYINTL

ISNUMBER,ISNA,ISNONTEXT,ISEVEN,ISODD,ISFORMULA,ISERROR

Data validation,Depended drop down,Protecting cell,Array,range,sheet,Workbook

Data Handling:Formatting and Filtering

Conditional formatting(Icon sets/Highlighted color sets/Data bars/custom formatting),Sort,Advanced Sort,Filtering

Data Summerization:Advanced functions,Charts

Sum,Average,Max,Min with IF and IF'S,CountIF'S

Various types of Charts

Data Summerization:Pivots,Preparing the Dashboard

Pivot table,Slicers,Pivot charts,Calculated field,Calculated item, ADD/REMOVE/CHANGE data into the pivot table,Refreshing pivot data

Dashboard creation

Power query,power pivot

Cleaning data,extracting data from multiple sources

Transforming data,imputation techniques.Getting data from CSV files,databases,workbooks,webpages

Power query,power pivot,Use case discussion:Data Preparation,Project Summarization

Consolidating data from multiple sources,merging data from different workbooks/worksheets,relationships.

Use Data handling steps taught in the previous session,Use Data summarization techniques,Populate output in Excel,Combining multiple functions

Intro to Automation:Macros(Recorded /VBA)

How VBA works,Record a sample macro(Recording macros,Absolute mode,relative mode,different methods of executing macros)

VBA

If constructs,Select construct,User defined functions,input box, message box,procedures,automatic macros, methods to cleanup the codes

MySQL

Introduction to Databases, Software Installation

Introduction to RDBMS,Explain RDBMS through normalization,Different types of RDBMS

Types of SQL Commands; Data Types in SQL

DDL,DML,DQL,DCL,TCL,Datatypes:int,float,char,varchar,date,date&time,UTC

DDL and DML and TCL commands

DDL:Create,Drop,Rename,Alter,Truncate,DML:Insert,Update,Delete,TCL:Commit,rollback,savepoint

Database Constraints

Domain Constraint,Key Constraint,Referential Integrity Constraint,Primary key,Foreign Key

Operators in SQL

AND,OR,WHERE,IN,NOT IN,BETWEEN,EXIST,NULL

Grouping operations

Select query,Order by,Group by,Having Classes,Aggregating functions

Ranking functions,Analytical functions

Rank,Dense rank,row number,percentile rank,lead and lag functions

Joining Tables

Inner,Left,Right,Cross,Self Joins,Full outer join, Interview Scenarios

Views,Triggers

Simple views,Complex views,Different types of triggers

Introduction to subqueries, different types of subqueries

Explaintion of subqueries with interview scenarios

Indexing,Sequence Objects

B.Tree Index,Hash Index,Unique index,Advantage of Index,Creation of Sequence on primary key column

Stored procedures

Parameters in stored procedures,Exception handling in stored procedures

Tableau

Intro to Tableu Tool

What is data,Types of Data(Structured,Unstructured,Semi Structured),Visualization basics,Different visualizaion tools,Popularity of the tools, Licencing Cost,Different products of Tableau,Installation (student id), ,Connecting to Static files,Mysql

Data pane window

Live Vs Extract,Data source window,Navigating to work sheet,Data pane,Analytics pane,Dimensions,Measures,Auto generated fields,Data visualization window explaination,Data source window operations

Groups,Sets,Parameters

Hierarchy(In built hierarchy,Manual),Grouping ,Sets,Parameter with filters and Parameter with Sets,Usage of meausre names and Measure Values

Filters in Tableau

Dual axis,Blended axis,Dimension filters,Measure filters(Record level filters,summary level filters),Date filters,Cascading Filters,Context filters,Data source filters,Extract filters,

Calculated fields

Quick table calculations,Introduction to calculated fields,string calculated fields,Number calculated fields,date calculated fields,logical calculated fields,ZN Function

Data Blending and Joins

Mixing up of all calculated fields,Conditional Formatting in Tableau, Data blending,Data joins,Unions,Relationships,Basic Charts and use cases, Introduction to Show me,Development of In built charts part1,

Charts in Tableau

Development of inbuilt charts part2,Customized graphs(Donut, Waterfall,Bump,Barometer,Butterfly,Gauge meter,Basic Funnel, Advanced Funnel,Word cloud,Gantt Bar),Animated Chart

Reference lines,Bands,Distributions

Arbitary formatting,Explaination of Marks Card,Reference lines, Reference Bands,Reference Distribution

LOD's, Intro to Dashboard,Story

Forecasting,Introduction to Dashboard,Story board interfaces,LOD's(Fixed,Include,Exclude)

Creating a Dashboard

Creating of a Basic Dashboard with both Tiled,Floating layouts, Explaination of objects in the Dashboard interface,Action filters on Dashboards

Creating a Advanced Dashboard

Advanced level dashboard(Drill down dashboards),Designing of Basic Story board

Tableau public server

Publishing Dashboards on Tableau public server,Exposure to the websites which consists of real time data,Interview cracking resources,introduction to Tableau certification

Basics Of R

"Introduction to R,Installation of Rstudio,Data Types in R

Data types(Numeric,Char,Logical,Complex,Vector,List,Matrix,Factor,Array,Dataframe),Relational operators,Logical operators

Decision making statements,Loops,Functions

If,Ifelse,For loop,While loop,Repeat,Functions

Built in Functions in R,Joins,dplyr and ggplot2

Merging dataframes,Analyzing Iris Dataset using apply functions,dplyr package(Filter,Sel,Arrange),Data visualization using ggplot2,Scatterplot,Histogram,Boxplot

Basics Of Python

Anaconda Installation,Introduction to python,Data types,Opearators

Variables,data types(integer,Boolean,Float,List,tuple,string),Opearators in python

Data types Contd,Slicing the data,Inbuilt functions in python

Dictionaries,Sequence methods,Concatenate,Repetition,len,min,max functions,Index position,Addition and deletion of elements,Reverse,Sorting

Sets,Set Theory,Regular Expressions,Decision making statements

Sets,re module(findall,search,split,match),if,elifGetting input from user,Identity Operators

Loops,Functions,Lambda functions,Modules

For,While loops,Functions,Lambda functions,Math module,Calender module,Date & time module

Pandas,Numpy,Matplotlib,Seaborn

Data frame creation using different methods,Using Pandas anlysis on Universities,Salary data sets,Visualization using Matplotlib and Seaborn,Numpy introduction

Power BI

Power BI Introduction

Introduction to Power BI Desktop

Getting data (Excel and RDBMS, Web, SharePoint)

Naming for Q&A

Direct Query vs Import data

Modelling with Power BI

Introduction to Modelling

Set up and Manager relationships

Cardinality and cross filtering

Creating hierarchy in the model

Default summarization and sort by

Creating calculated columns

Creating measures and quick measures

Power BI Desktop Visualizations

Creating visuals

Colour and conditional formatting

Setting sort order

Scatter and bubble charts and play axis

Tool tips

Slicers, timeline Slicers and sync Slicers

Cross filtering and highlighting

Visual, Page and Report level filters

Drill down/up

Hierarchies

Constant Lines

Tables, Matrix and Table conditional formatting

KPI’s, Cards and Gauges

Map Visualizations

Custom visuals

DAX Expressions

Introduction to Dax (how to write Dax and basic functions in Power BI)

Important Dax used in Power BI along with its applications

Introduction to Dax (how to write Dax and basic functions in Power BI)

how to create calculated columns and measures in Power BI and difference in its application

Scenarios with Questions on Dax & explanation

Creating date dimension in Power BI using calendar functions and its importance

Publishing and Sharing

Sharing options

Publish from Power BI Desktop

Publish reports to Web

Sharing reports and Dashboards

Workspaces

Apps

Printing, PDF’s and exports

Row level Security

Exporting data from Visualizations Refreshing Datasets

Understanding data refresh

Gateways