Course outline for Data Analytics

Goal:

The goal of Data Analytics course is to examine large amounts of data to uncover hidden patterns, correlations and other insights.
Data analytics is the process of examining data in order to draw insights and inform business decisions. It involves the use of statistical and computational techniques to extract and analyze data, with the goal of identifying trends, patterns, and relationships that can be used to improve business operations. Data analytics can be applied to a wide range of fields, including finance, healthcare, marketing, and manufacturing, and it is an essential tool for businesses that want to make data-driven decisions.
Data science is a more broad field that encompasses data analytics, as well as many other techniques and approaches for working with data. Data science involves using statistical and computational techniques to extract insights and knowledge from data, and to communicate those findings to others. Data scientists may use a variety of tools and techniques from different fields, such as machine learning, statistics, and computer science, to analyze data and solve problems.
In summary, data analytics is focused on using data to answer specific questions and make decisions, while data science is concerned with using a wide range of techniques to extract knowledge and insights from data.

Audience:

This course is designed for any one willing to make career in Data Analytics .

Pre-requisites:

Any Graduate or Post-Graduate having affinity with Data, Information, Knowledge and Wisdom

Duration:

72 Hours

Introduction to Data Analytics

What Is Data Set?

Types Of Data

• Structure Data
• Un-structure Data
• Semi Structure Data

What Is Data Analytics

Difference between Data Analytics and Data Science

Types of Data Analytics

• Descriptive analytics
• Diagnostic analytics
• Predictive analytics
• Prescriptive analytics

Data Analytics using Excel

Introduction To Excel

• An overview of the screen, navigation and basic spreadsheet concepts
• Customizing the Ribbon
• Worksheets
• Format Cells
• Various selection techniques
• Protecting and un-protecting worksheets

Sorting and Filtering Data

• Sorting tables
• Using multiple-level sorting
• Using custom sorting
• Filtering data for selected view
• Using advanced filter options

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

Text Function

• Upper, Lower, Proper
• Left, Mid, Right,Trim, Len, Exact
• Concatenate

Function and 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
• Using V-lookup to consolidate Data from Multiple Sheets
• Sumlf, Countlf, Averagelf
• Date and Time Function

Pivot Tables

• Creating Simple Pivot Tables
• Basic and advanced value field setting
• Grouping Based on number and Dates
• Calculated field and Calculated items

Charts and Dashboards

• Formatting Charts
• Using 3D Graphs
• Using Bar and Line Chart together
• Using Secondary Axis in Graphs
• Sharing Charts with PowerPoint / MS Word, Dynamically

Working with Templates

• Designing the structure of a template
• Using templates for standardization of worksheets

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

Massage-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

Microsoft POWER BI

Introduction

• Introduction to Power BI
• Download the Training Data Files
• Introduction to Signing Up for Power
• Signing up for Power BI Preview
• Load Data into the Power BI Service Preview

The Power Bl Desktop

• Intro to Power BI Desktop Section
• Introduction to the Power BI Desktop Preview

DATA EXTRACTION/TRANSFORMATION – SHAPING and COMBINING DATA

• Data sources in Power BI - Formatting data
• Using files (excel, pdf, csv, etc.) as a data source
• Extracting data from folders, and databases
• Transformation of data
• Understanding of Data types
• Working with Parameters
• Merge Query
• Append Query
• Transpose of data
• Fill
• Pivot and Un-pivot of data
• Custom columns
• Conditional columns
• Replace data from the tables
• Split columns values
• Move columns and sorting of data
• Detect data type, count rows and reverse rows
• Promote rows as column headers
• M query

DATA MODELLING and DAX

• Introduction of relationships
• Creating relationships
• Cardinality
• Cross filter direction
• Use of inactive relationships
• Introduction of DAX
• Why DAX is used
• DAX syntax
• DAX functions
• Context in DAX
• Calculated columns using DAX
• Measures using DAX
• Calculated tables using DAX
• Learning about table, information, logical, text, iterator
• Time intelligence functions (YTD, QTD, MTD)
• Cumulative values, calculated tables, and ranking and rank over groups
• Date and time functions
• identify poorly performing measures, relationships, and visuals
• DAX advanced features

DATA VISUALIZATION

• How to Create a Map
• How to Change Background
• How to Create India Map
• How to Create Australia Map
• Table and Matrix
• Subtotal and Total in Matrix
• Other Charts in Power BI Desktop
• Cards and Filters
• Slicers in Power BI
• Advanced Charts in Power BI

POWER BI SERVICE

• Introduction to Power BI Service
• Introduction of workspaces
• Dashboard
• Creating and Configuring Dashboards
• Dashboard theme
• Sharing reports and dashboards

POWER BI Advance

• Introduction of Data Gateway
• Installation and Configuration of data gateways
• Introduction to embedded Power BI
• Introduction of Power BI API
• Introduction to Power BI Mobile
• Power BI USECASE

SQL(Structured Query Language)

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 object
• Data Query Language
• Select Statement
• Data Manipulation Language
• DCL And TCL
• Grant , Revoke And Transaction Statement
• Sql Data Types
• Numeric , date and time, LOB Types
• DML Commands
• Insert Update And Delete Statements
• DDL Commands
• Create And Drop Databases

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

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

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 Python

• Triggers
• Database Triggers
• Data Definition Language (DDL) Triggers
• Data Manipulation Language (DML) Triggers
• CLR Triggers
• Logon Triggers
• Triggers v/s Stored Procedures
• Configuration Information
• Python Database Access
• Databases Supported
• Libraries
• Read Operations
• Insert, Update and Delete
• Performing Transactions
• Handling Errors

Data Analytics using Python

INTRODUCTION TO Python

• Introduction to Python
• Python Installation
• Variables, Python Build in functions Modules ,Python Libraries installation using PIP
• Python Operators
• Flow Control Statements -If Statements -While Loops-For Loops
• Data and time modules in python
• Interfaces in Python
• Python custom functions-Lambda Function -Regular Expressions

Python modules for Data Analysis

• Data Analysis Life cycle - Numpy Module
• Data Analysis Life cycle - Pandas Module
• Data Analysis Life cycle - Matplotlib Module
• Web scrapping