DATA ANALYTICS

DATA ANALYTICS

Data analytics is the process of examining data sets in order to draw conclusions about the information they contain, increasingly with the aid of specialized systems and software. Data Analytics allows you to view statistical information about unstructured data, such as files and emails. With this information, you can quickly assess the state of your big data sources, take actionable steps to retrieve valuable storage space, and mitigate the risk of compliance-related issues.

ADVANCED EXCEL

Unit – 1: Advanced Excel Course – Overview of the Basics of Excel
• Customizing common options in Excel
• Absolute and relative cells
• Protecting and un-protecting worksheets and cells

Unit – 2: Advanced Excel Course – Working with Functions
• Writing conditional expressions (using IF)
• Using logical functions (AND, OR, NOT)
• Using lookup and reference functions (VLOOKUP, HLOOKUP, MATCH, INDEX)
• VlookUP with Exact Match, Approximate Match
• Nested VlookUP with Exact Match
• VlookUP with Tables, Dynamic Ranges
• Nested VlookUP with Exact Match
• Using VLookUP to consolidate Data from Multiple Sheets

Unit – 3: Advanced Excel Course – 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

Unit – 4: Advanced Excel Course – Working with Templates
• Designing the structure of a template
• Using templates for standardization of worksheets

Unit – 5: Advanced Excel Course – Sorting and Filtering Data
• Sorting tables
• Using multiple-level sorting
• Using custom sorting
• Filtering data for selected view (AutoFilter)
• Using advanced filter options

Unit –6: Advanced Excel Course – Working with Reports
• Creating subtotals
• Multiple-level subtotals
• Creating Pivot tables
• Formatting and customizing Pivot tables
• Using advanced options of Pivot tables
• Pivot charts
• Consolidating data from multiple sheets and files using Pivot tables
• Using external data sources
• Using data consolidation feature to consolidate data
• Show Value as (% of Row, % of Column, Running Total, Compare with Specific Field)
• Viewing Subtotal under Pivot
• Creating Slicers

Unit – 7: Advanced Excel Course – More Functions
• Date and time functions
• Text functions
• Database functions
• Power Functions (CountIf, CountIFS, SumIF, SumIfS)

Unit – 8: Advanced Excel Course – Formatting
• Using auto formatting option for worksheets
• Using conditional formatting option for rows, columns and cells

Unit – 9: Advanced Excel Course – Macros
• Relative & Absolute Macros
• Editing Macro’s

Unit – 10: Advanced Excel Course – WhatIf Analysis
• Goal Seek
• Data Tables
• Scenario Manager

Unit – 11: Advanced Excel Course – Charts
• 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
• (Data Modified in Excel, Chart would automatically get updated)

MS POWER BI

INTRODUCTION TO POWER BI

• What is Power BI
• Why you should learn Power BI
• Power BI Architecture
• Power BI Components
• Download and Install BI Desktop
• Add, Rename, Duplicate, and Delete Pages
• Connect to SQL Server
• Connect to Multiple Excel Sheets
• Get Data from Excel Files
• Get Data from Text Files
• Load Data from Multiple Data Sources
• Remove Unwanted Columns from Tables

POWER BI TRANSFORMATIONS
• Change the Data type of a Column
• Combine Multiple Tables
• Clusters
• Enter data or Copy & Paste data from Clipboard
• Format Dates
• Groups
• Hierarchies
• Joins
• Pivot Table
• Query Groups
• Reorder or Remove Columns
• Rename Column Names
• Rename Table Names
• Split Columns
• UnPivot Table

POWER BI CHARTS TUTORIAL
• Area Chart
• Bar Chart
• Card
• Clustered Bar Chart
• Clustered Column Chart
• Column Chart
• Donut Chart
• Funnel Chart
• Heat Map
• Line Chart
• Clustered Column and Line Chart
• Line and Stacked Column Chart
• Matrix
• Multi-Row Card
• Pie Chart
• Ribbon Chart
• Stacked Area Chart
• Scatter Chart
• Stacked Bar Chart
• Stacked Column Chart
• Table
• Tree Map
• Waterfall Chart
• 100% Stacked Bar Chart
• 100% Stacked Column Chart
• Map
• Filled Map
• Format Area Chart
• Format Bar Chart
• Formatting Card
• Format Clustered Bar Chart
• Format Clustered Column Chart
• Formatting Column Chart
• Format Donut Chart
• Format Funnel Chart
• Formatting Line Chart
• Format Line & Clustered Column
• Format Line & Stacked Column
• Formatting Matrix
• Format Multi-Row Card
• Format Pie Chart
• Formatting Ribbon Chart
• Format Stacked Area Chart
• Formatting Scatter Chart
• Format Stacked Bar Chart
• Formatting Stacked Column Chart
• Format Table
• Format Tree Map
• Formatting Waterfall Chart
• R Script
• Format Map
• Format Filled Map

POWER BI FILTERS TUTORIAL
• Slicer
• Basic Filters
• Advanced Filters
• Top N Filters
• Filters on Measures
• Page Level Filters
• Report Level Filters
• Drill through Filters

POWER BI TUTORIAL ON CALCULATED FIELDS
• Calculated Columns
• Conditional Columns
• Calculated Measures
• Calculated Tables
• Custom Columns

POWER BI TUTORIAL ON DASHBOARDS
• Register to Pro Service
• Dashboard Introduction
• Connect Desktop with BI Service or Pro
• Publish Desktop Reports
• Create a Workspace
• Create a Dashboard
• Favorites

WORKING WITH POWER BI DASHBOARDS
• Dashboard Actions
• Add Reports to a Dashboard
• Add Title to Dashboard
• How to Add Image to Dashboard
• Add Video to Dashboard
• Add Web Content to Dashboard
• Dashboard Settings
• Delete a Dashboard
• Pin Report to a Dashboard

SHARING POWER BI WORK
This Power Bi tutorial section covers the sharing of Work. I means, sharing reports, Dashboards, apps, Workspaces etc.
• Share a Dashboard
• Share a Report
• Sharing Workspace
• Publish App
• View Published App

POWER BI DAX
• Aggregate Functions
• Date Functions
• Logical Functions
• Math Functions
• String Functions
• Trigonometric Functions

SQL

MODULE 1: SQL OVERVIEW

• Outlining SQL as the cornerstone of database activity
• Applying the ANSI/ISO standards
• Describing the fundamental building blocks: tables, columns, primary keys, and foreign keys

MODULE 2: BUILDING THE DATABASE SCHEMA

CREATING TABLES AND COLUMNS

• Building tables with CREATE TABLE
• Modifying table structure with ALTER TABLE
• Adding columns to an existing table
• Removing tables with DROP TABLE

PROTECTING DATA INTEGRITY WITH CONSTRAINTS

• Guaranteeing uniqueness with primary key constraints
• Enforcing integrity with foreign key constraints
• Imposing business rules with check constraints

IMPROVING PERFORMANCE WITH INDEXES

• Expediting data retrieval with indexes
• Recommending guidelines for index creation

MODULE 3: MANIPULATING DATA

MODIFYING TABLE CONTENTS

• Adding table rows with INSERT
• Changing row content with UPDATE
• Removing rows with DELETE

APPLYING TRANSACTIONS

• Controlling transactions with COMMIT and ROLLBACK
• Deploying BEGIN TRANSACTION in SQL Server

MODULE 4: WORKING WITH THE SELECT STATEMENT

WRITING SINGLE TABLE QUERIES

• Retrieving data with SELECT
• Specifying column expressions
• Sorting the result with ORDER BY
• Handling NULL values in expressions

RESTRICTING ROWS WITH THE WHERE FILTER

• Testing for equality or inequality
• Applying wildcard characters
• Avoiding NULL value pitfalls

MODULE 5: QUERYING MULTIPLE TABLES

APPLYING THE ANSI/ISO STANDARD JOIN SYNTAX

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN

COMBINING RESULTS WITH SET OPERATORS

• Stacking results with UNION
• Identifying matching rows with INTERSECT
• Utilizing EXCEPT to find nonmatching rows

MODULE 6: EMPLOYING FUNCTIONS IN DATA RETRIEVAL

PROCESSING DATA WITH ROW FUNCTIONS

• Conditional formatting with the CASE expression
• Utilizing the CASE expression to simulate IF tests
• Dealing with NULL values

PERFORMING ANALYSIS WITH AGGREGATE FUNCTIONS

• Summarizing data using SUM, AVG and COUNT
• Finding the highest/lowest values with MAX and MIN
• Defining the summary level with GROUP BY
• Applying filter conditions with HAVING

MODULE 7: CONSTRUCTING NESTED QUERIES

APPLYING SUBQUERIES IN FILTER CONDITIONS

• Creating correlated vs. noncorrelated subqueries
• Testing the existence of rows

INCLUDING SUBQUERIES IN EXPRESSIONS

• Placing subqueries in the column list
• Creating complex expressions containing subqueries
• Handling subqueries that return no rows

MODULE 8: DEVELOPING IN-LINE AND STORED VIEWS

BREAKING DOWN COMPLEX PROBLEMS

• Selecting data from a query result set
• Using subqueries in the FROM clause

VBA MACRO

Course Introduction and Overview
Unit – 1: Sheet related Activities
• Simple data
• If statements
• Tool box controls
• Loops
• Multiple loops
• Data filtering & populating to listbox
• Data filtering & populating to another column in the same sheet
• Font related activities
• Font color activities
• Cells formatting activities
• Cells fill color activities
• Sheets renaming, protecting
• Sheets sorting
• Data sorting
• Blank rows, columns deleting
• Hide, unhide rows, cols
• Workbook save, delete activities
• Range, cells controlling

Unit – 2: Multiple sheets data activities

Unit – 3: Chart related activities

Unit – 4: Msgbox related activities

Unit – 5: Other applications related activities

Unit – 6: Transferring data from excel to word, access, and pdf files

Unit – 7: Administrator related activities

Unit – 8: Pivot Tables and Pivot Chart Related Programs

Note: Each topics contains multiple programs and scenarios

DATA SCIENCE

Unit 1 – Introduction to Python
Unit 2 – Basic Python
Unit 3 – Working with Libraries Like NumPy, Pandas, Matplotlib, Seaborn, SciPy, Sklearn in Python
Unit 4 – Working Experience with Pandas In Python
Unit 5 – Working Experience with Matplotlib Library in Python
Unit 6 – To Work With Seaborn Library (High-Level Interface for Drawing Attractive and Informative Statistical Graphics) In Python
Unit 7 – Introduction to SciPy and Sklearn Libraries in Python
Unit 8 – Statistical Analysis
Unit 9 – Hypothesis Testing
Unit 10 – Linear Regression
Unit 11 – Logistic Regression
Unit 12 – Discrete Probability Distribution
Unit 13 – Advanced Regression
Unit 14 – Multinomial Regression
Unit 15 – Data Mining Unsupervised – Clustering
Unit 16 – Dimension Reduction
Unit 17 – Data Mining Unsupervised – Network Analytics
Unit 18 – Data Mining Unsupervised – Association Rules
Unit 19 – Data Mining Unsupervised – Recommender System
Unit 20 – Machine Learning Classifiers – KNN
Unit 21 – An Introduction to Data Visualization
Unit 22 – Tableau Products and Usage
Unit 23 – Basic Charts On Tableau
Unit 24 – Connecting Tableau with Multiple Sheets and Data Sources
Unit 25 – Tableau Filters and Visualization Interactivity
Unit 26 – Interaction and Grouping the Data
Unit 27 – Time Series Chart
Unit 28 – Maps and Images in Tableau
Unit 29 – Advanced Charts in Tableau and Analytical Techniques
Unit 30 – Calculations on Tableau
Unit 31 – Tableau Integration with Other Tools
Unit 32 – Understand the Business Problem
Unit 33 – Data Collection
Unit 34 – Data Cleansing / Exploratory Data Analysis / Feature Engineering
Unit 35 – Data Mining
Unit 36 – Model Deployment
Unit 37 – Introduction to Big Data
Unit 38 – Hadoop and Its Components
Unit 39 – Linux OS and Virtualization Software’s
Unit 40 – Apache Hive
Unit 41 – Apache SQOOP
Unit 42 – Apache Spark
Unit 43 – Classifier – Naive Bayes
Unit 44 – Bagging and Boosting
Unit 45 – Decision Tree and Random Forest
Unit 46 – Black Box Methods
Unit 47 – Text Mining
Unit 48 – Natural Language Processing
Unit 49 – Forecasting

MACHINE LEARNING

Unit 2 – Linear Regression and Feature Selection
Unit 3 – Linear Classification
Unit 4 – Support Vector Machines and Artificial Neural Networks
Unit 5 – Bayesian Learning and Decision Trees
Unit 6 – Evaluation Measures
Unit 7 – Hypothesis Testing
Unit 8 – Ensemble Methods
Unit 9 – Clustering
Unit 10 – Graphical Models
Unit 11 – Learning Theory and Expectation Maximization

PYTHON

CORE PYTHON

INTRODUCTION 

• History
• Features
• Setting up path
• Working with Python
• Basic Syntax
• Variable and Data Types
• Operator

CONDITIONAL STATEMENTS

• If
• If- else
• Nested if-else

LOOPING

• For
• While
• Nested loops

CONTROL STATEMENTS

• Break
• Continue
• Pass

STRING MANIPULATION

• Accessing Strings
• Basic Operations
• String slices
• Function and Methods

LISTS

• Introduction
• Accessing list
• Operations
• Working with lists
• Function and Methods

TUPLE

• Introduction
• Accessing tuples
• Operations
• Functions and Methods

DICTIONARIES

• Introduction
• Accessing values in dictionaries
• Working with dictionaries
• Properties
• Functions

FUNCTIONS

• Defining a function
• Calling a function
• Types of functions
• Function Arguments
• Anonymous functions
• Global and local variables

MODULES

• Importing module
• Math module
• Random module
• Packages
• Composition

INPUT-OUTPUT

• Printing on screen
• Reading data from keyboard
• Opening and closing file
• Reading and writing files
• Functions

OOPS CONCEPT

• Class and object
• Attributes
• Inheritance Overloading
• Overriding
• Data hiding

REGULAR EXPRESSIONS

• Match function
• Search function
• Matching VS Searching
• Modifiers
• Patterns

EXCEPTION HANDLING

• Exception Handling
• Except clause
• Try? Finally clause
• User Defined Exceptions

MULTI THREADING

• Thread
• Starting a thread
• Threading module
• Synchronizing threads
• Multithreaded Priority Queue

ADVANCED PYTHON

PYTHON DATA STORAGE – CSV FILE

• Read / Write – CSV File
• CSV Reader
• List into CSV File
• DICT into CSV File

PYTHON DATA STORAGE – JSON

• Import JSON
• List into JSON
• Strings into JSON
• Updating Data in List
• Searching JSON Data
• DICT Data into JSON
• Reading JSON File
• Writing JSON to CSV
• JSON to Excel

PYTHON DATA STORAGE – EXCEL

• XLWT / XLRD
• OPENPYXL / XLWRITER
• Creating Sheets – Excel
• Inserting Data Into Excel
• Formatting Data In Excel
• Setting Dimension
• Plotting Charts – Excel
• Line / Bar / Area / 3D / Pie
• List Data Into Chart
• Excel Functions in Python

PYTHON – MS WORD / PDF

• Python – Docx
• Python – PyPDF2
• Water Mark / Rotating

PYTHON – NUMPY

• Install / Import
• Numpy ItemSize / DType
• Shape / Size / Reshape
• Max / Min / Sum
• Arithmetic Operations
• List / Tuple To Numpy
• Broadcasting Numpy
• Numpy – Strings

PYTHON FILE HANDLING

• R / W / A / R+
• Write / Read / Append
• Split Function
• Read Data Line by Line
• Using Loop to Read Data
• Creating a New File

PYTHON – OS MODULE

• Rename / Delete a File
• Create / Remove DIR
• Exception Handling – File

PYTHON – PANDAS

• Importing PANDAS
• PANDAS Read_Excel
• PANDAS – Series & DF
• LIST / DICT Into
• PANDAS Numpy into PANDAS
• CSV Into PANDAS
• JSON Into PANDAS
• Data Frame – Excel
• Data Frame From Series

PYTHON – DATE / TIME

• Date Functions
• Time Functions
• Importing Date / Time
• Calculating Time Diff
• Calculating Date Diff

PYTHON – MATPLOTLIB

• Importing MATPLOTLIB
• Bar / Scatter Graph
• Plot Using Pandas & Matplotlib
• Numpy & Matplotlib
• Gantt Chart & Matplotlib
• Excel Data into Matplotlib
• List / Tuple Data into Matplotlib
• Axis / Labels / Titles
• Multiple Plots
• Using Pyplots / Subplots
• CSV Data into Matplotlib
• Formatting Plots
• Histogram / Pie Plots

 

TABLEAU

Unit 1: Introduction and Overview
• Why Tableau? Why Visualization?
• Level Setting – Terminology
• Getting Started – creating some powerful visualizations quickly
• The Tableau Product Line
• Things you should know about Tableau

Unit 2: Getting Started
• Connecting to Data and introduction to data source concept
• Working with data files versus database server
• Understanding the Tableau workspace
• Dimensions and Measures
• Using Show Me!
• Tour of Shelves (How shelves and marks work)
• Building Basic Views
• Help Menu and Samples
• Saving and sharing your work

Unit 3: Analysis
• Creating Views
• Marks
• Size and Transparency
• Highlighting
• Working with Dates
• Date aggregations and date parts
• Discrete versus Continuous
• Dual Axis / Multiple Measures
• Combo Charts with different mark types
• Geographic Map Page Trails
• Heat Map
• Density Chart
• Scatter Plots
• Pie Charts and Bar Charts
• Small Multiples
• Working with aggregate versus disaggregate data
• Analyzing
• Sorting & Grouping
• Aliases
• Filtering and Quick Filters
• Cross-Tabs (Pivot Tables)
• Totals and Subtotals Drilling and Drill Through
• Aggregation and Disaggregation
• Percent of Total
• Working with Statistics and Trend lines

Unit 4: Getting Started with Calculated Fields
• Working with String Functions
• Basic Arithmetic Calculations
• Date Math
• Working with Totals
• Custom Aggregations
• Logic Statements

Unit 5: Formatting
• Options in Formatting your Visualization
• Working with Labels and Annotations
• Effective Use of Titles and Captions
• Introduction to Visual Best Practices

Unit 6: Building Interactive Dashboard
• Combining multiple visualizations into a dashboard
• Making your worksheet interactive by using actions and filters
• An Introduction to Best Practices in Visualization

R PROGRAMMING

Unit 1: Essential to R programming
• An Introduction to R
• Introduction to the R language
• Programming statistical graphics
• Programming with R
• Simulation
• Computational linear algebra
• Numerical optimization

Unit 2: Data Manipulation Techniques using R programming
• Data in R
• Reading and Writing Data
• R and Databases
• Dates
• Factors
• Subscribing
• Character Manipulation
• Data Aggregation
• Reshaping Data

Unit 3: Statistical Applications using R programming
• Basics
• The R Environment
• Probability and distributions
• Descriptive statistics and graphics
• One- and two-sample tests
• Regression and correlation
• Analysis of variance and the Kruskal–Wallis test
• Tabular data
• Power and the computation of sample size
• Advanced data handling
• Multiple Regression
• Linear models
• Logistic regression
• Survival analysis
• Rates and Poisson regression
• Nonlinear curve fitting

SAS

Data Structures
• Introduction to SAS interface and library structure and definition
• Reading data using Datalines and importing and exporting datasets
• Infiles statement – reading raw data
• Formats and Informats
• Variable attributes and data modification using Data and Set statements

Data Management
• Using conditional statements to modify data – Where, If and Nested IF
• Appending and Merging datasets
• SAS Functions for data manipulation
• Loops and Arrays in SAS

Report Generation
• Basic Proc steps – like Proc Contents
• Proc Format, Proc Report and Proc Tabulate
• Proc steps for basic statistics – like Proc Univariate and Proc Means

Advanced SAS
Proc SQL
• Introduction to SQL – basic DBMS and RDBMS concepts
• Using SQL Procedures in SAS
• Using conditional statements in SQL and aggregate functions
• Data manipulation using Proc SQL

SAS Macros
• Introduction to Macros
• Local and Global declarations
• Using built-in macro procedures and functions