DATA ANALYTICS


Data Analytics course is designed to help you become a successful Data Analyst, this course is for those just starting their career in Analytics. It will teach you how to make powerful dashboards, macros, and SQL along with a strong focus on case studies to ensure hands-on learning. Once armed with the analytics, you will also learn the powerful Data visualization tool Tableau to present your analysis. We also conduct Excel Corporate training across the globe.
Course duration: 50 hours (50 hours live training + Practice and Self-study)

COURSES:

Advanced Excel, Excel Macro’s(VBA), SQL, Statistics Essentials, and Tableau

Report Visualization – Dashboard Designing


Advanced Excel

  • Explanation of data calculation in Excel
  • Use of Shortcuts
  • Formatting and Conditional Formatting
  • Working with Formulas – Logical and Text Functions
  • Understanding about Sorting, Filtering, and Data Validation
  • Data Analysis using Pivot Tables

Introduction to Charts and Functions

  • Understanding of Mathematical, Statistical Functions
  • Worksheet and Workbook Protection and Security
  • Understanding of Name Ranges
  • Introduction of Charts
  • Introduction of Form Controls
  • Understanding of Data Tools Panel
  • Basics of Macro Recording

Dashboard Designing

  • Overview of Dashboards
  • Deciding on Dashboards
  • Trends and Scenarios using charts
  • Advanced Charting Techniques using Thermometer, Doughnut, Pareto, Panel and Step Chart
  • Designing Sample Dashboard using Form Controls
  • Tips and Tricks to enhance dashboard designing

Statistics Essentials for Reporting

  • Scatter Plot
  • Quadrant Analysis
  • Histogram
  • Paret Chart
  • Normal Distribution
  • Calculation of Standard Deviation and Variance
  • Understanding of Least Square Error
  • Simple Linear Regression
  • Multi-Linear Regression
  • Logistic Regression
  • ANOVA
  • RFM Segmentation

Report Automation Using VBA and SQL

Excel Macros ( Visual Basic for Applications)

  • Working with VBE (Visual Basic Editor)
  • Introduction to Excel Object Model
  • Understanding of Sub and Function Procedures
  • Key Component of Programming Language
  • Understanding of If, Select Case, With End With Statements
  • Looping with VBA
  • User Defined Function
  • Some Commonly Used Macro Examples
  • Error Handling
  • Object and Memory Management in VBA
  • User Form Controls
  • ActiveX Controls
  • Communicating with Database MS Access through ADO – Exporting/Importing Data

DBMS – SQL

  • SQL Basics
  • Getting Started with SQL Management Studio
  • Understanding of RDBMS concept
  • Data Manipulation – Reading, Manipulating, and Functions
  • DDL,DML,DCL,DQL Commands
  • Data Aggregation & Summarization
  • Concept of Indexing
  • Partitioning, Decile creation, Reporting etc
  • Stored procedures
  • Work Optimization

Datat Visualization Using Tableau

  • What is Tableau? What does the Tableau product suite comprise of? How Does Tableau Work?
  • Tableau Architecture
  • What is My Tableau Repository?
  • Connecting to Data & Introduction to data source concepts
  • Understanding the Tableau workspace
  • Dimensions and Measures
  • Data Types & Default Properties
  • Tour of Shelves & Marks Card
  • Using Show Me!
  • Building basic views
  • Saving and Sharing your work-overview

Tableau: Building Views (Reports) – Basics

  • Date Aggregations and Date parts
  • Cross tab & Tabular charts
  • Totals & Subtotals
  • Bar Charts & Stacked Bars
  • Line Graphs with Date & Without Date
  • Tree maps
  • Scatter Plots
  • Individual Axes, Blended Axes, Dual Axes & Combination chart
  • Edit axis
  • Parts of Views
  • Sorting
  • Trend lines
  • Reference Lines
  • Forecasting
  • Filters
  • Context filters
  • Sets
  • In/Out Sets
  • Combined Sets
  • Grouping
  • Bins/Histograms
  • Drilling up/down – drill through
  • Hierarchies
  • View data
  • Actions (across sheets)

Tableau: Building Views (Reports) – Advanced Maps

  • Explain latitude and longitude
  • Default location/Edit locations
  • Symbol Map & Filled Map
  • Custom Geo Coding

Tableau: Calculated Fields

  • Working with aggregate versus disaggregate data
  • Explain – #Number of Rows
  • Basic Functions (String, Date, Numbers etc)
  • Usage of Logical conditions

  • Explain scope and direction
  • Percent of Total, Running / Cumulative calculations

Tableau: Parameters

  • Create What-If analysis
  • Using Parameters in
  • Calculated fields
  • Bins
  • Reference Lines
  • Filters/Sets
  • Display Options (Dynamic Dimension/Measure Selection)

Tableau: Building Interactive Dashboards- (Building & Customizing)

  • Combining multiple visualizations into a dashboard (overview)
  • Making your worksheet interactive by using actions
  • Filter
  • URL
  • Highlight

Tableau: Formatting

  • Options in Formatting your Visualization
  • Working with Labels and Annotations
  • Effective Use of Titles and Captions

Tableau: Working with Data

  • Multiple Table Join
  • Data Blending
  • Difference between joining and blending data, and when we should do each
  • Working with the Data Engine / Extracts
  • Working with Custom SQL
  • Toggle between to Direct Connection and Extracts

Tableau: Working with Tableau Server – Accessing reports through web

  • Publishing to Tableau Server – Overview of publishing
  • Server Administration – Managing Users, Projects & Object level and Data Security as per Users
  • User Filters

Case Studies:

1: E-Commerce Sales Dashboard (Excel)
Visualize e-commerce data into an interactive dashboard for revenue, cost, quantities, and profit KPI’s, which will initiate stack holders to take actions.
2: HR Analytics Dashboard (Excel)
In this case study, you will design HR dashboard to keep the track of employee attendances, their leaves, late marks and much more.
2: Financial Statement Automation (VBA)
Tool to prepare financial data from raw files into multiple workbooks.
3: Pareto Analysis Customized Add-In (VBA)
A Customize tool to perform Pareto analysis on any data set.
4: Customer Attrition Analysis Report (VBA-SQL)
Look for the customers those are on attrition side from the huge SQL/Access database.

Tableau Case Studies

  • Online Superstore Sales Dashboard
  • Pizza Store Sales Dashboard
  • Customer Case Study
  • Pharma Case Study