Data Analytics

Topics Covered:

Course Duration – 120 hrs

Excel: Basics to Advanced MySQL

Tableau Power BI SSIS

Python Basics

Basic Business Statistics Resume Writing Interview Preparation Aptitude

Introduction to Statistical Analysis

  • Counting, Probability, and Probability Distributions
  • Sampling Distributions
  • Estimation and Hypothesis Testing
  • Scatter Diagram
  • Anova and Chisquare
  • Imputation Techniques
  • Data Cleaning
  • Correlation and Regression

   Introduction to Data Analytics

  • Data Analytics Overview
  • Importance of Data Analytics
  • Types of Data Analytics
  • Descriptive Analytics
  • Diagnostic Analytics
  • Predictive Analytics
  • Prescriptive Analytics
  • Benefits of Data Analytics
  • Data Visualization for Decision Making
  • Data Types, Measure Of central tendency, Measures of Dispersion
  • Graphical Techniques, Skewness & Kurtosis, Box Plot
  • Descriptive Stats
  • Sampling Funnel, Sampling Variation, Central Limit Theorem, Confidence interval

 Introduction 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

o    Basic operators(+,-,/,*,%,>,<,>=,<=,( ),{ },[ ],&,’ ‘, “” “”,!)

 Introduction 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

o

 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,T RIM
    • SECOND,MINUTE,HOUR,DAY,WEEK,MONTH,QUARTER,YEAR,WORKDAYI NTL
  • 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,U TC
    • DDL and DML and TCL commands
      • DDL:Create,Drop,Rename,Alter,Truncate,DML:Insert,Update,Delete,TCL:C ommit,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:

Introduction to Tableau 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 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
  • Gateway
Send Enquery